Calculate and store vulnerability statistics on database
What does this MR do?
This MR calculates the difference on vulnerability statistics whenever there is a change on vulnerability state and either creates a new record or updates the already existing one.
It creates the new record in case if there is no vulnerability_statistic
record for the project or it updates the the existing one by using the PostgreSQL UPSERT functionality by depending on the unique index on project_id column of the table.
The reason for choosing the UPSERT is that, UPSERT query is atomic which means if there is no record for a project and 2 different threads try to insert a record for the same project, one of them will fail with conflict and do the update accordingly. This will allow us to skip using explicit locks in database level.
Related to #217811 (closed) and #213623 (closed).
For Database Review
There is no migrations and since the vulnerability_statistics
table is empty, sharing the query plan wouldn't help.
And example UPSERT query we run is;
INSERT INTO vulnerability_statistics AS target (project_id, total, critical, letter_grade, created_at, updated_at)
VALUES (1, 1, 1, 4, now(), now())
ON CONFLICT (project_id)
DO UPDATE SET
total = GREATEST (TARGET.total + 1, 0), critical = GREATEST (TARGET.critical + 1, 0), letter_grade = (
CASE WHEN TARGET.critical + EXCLUDED.critical > 0 THEN
4
WHEN TARGET.high + TARGET.unknown + EXCLUDED.high + EXCLUDED.unknown > 0 THEN
3
WHEN TARGET.medium + EXCLUDED.medium > 0 THEN
2
WHEN TARGET.low + EXCLUDED.low > 0 THEN
1
ELSE
0
END), updated_at = now()
Screenshots
Does this MR meet the acceptance criteria?
Conformity
- [-] Changelog entry
- [-] Documentation (if required)
-
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
- [-] Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
- [-] Tested in all supported browsers
- [-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
- [-] Label as security and @ mention
@gitlab-com/gl-security/appsec
- [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
- [-] Security reports checked/validated by a reviewer from the AppSec team