Add migration to resolve vulnerabilities from removed analyzers
What does this MR do and why?
This MR resolves vulnerabilities for the following Category:SAST analyzers that have been removed in GitLab %15.4, %16.0, and %17.0:
Removed in | Project | Scanner ID | Number of vulnerabilities |
---|---|---|---|
%15.4 | eslint | eslint | 8,544,825 |
gosec | gosec | 164,092 | |
bandit | bandit | 592,730 | |
%16.0 | security-code-scan | security_code_scan | 6,496 |
%17.0 | Brakeman | brakeman | 57,562 |
flawfinder | flawfinder | 2,715,293 | |
MobSF | mobsf | 65,568 | |
PHPCS Security Audit | phpcs_security_audit | 2,497,080 | |
NodeJS Scan | njsscan | 174,302 | |
nodejs-scan | 89,901 | ||
nodejs_scan | 12,577 | ||
Total Vulnerabilities to be resolved: | 14,920,426 | ||
Total Vulnerabilities with detected state: |
75,103,676 | ||
Total Vulnerabilities with detected state and archived: false |
69,092,451 | ||
Total Vulnerabilities: | 101,440,370 |
Vulnerability resolution process
Vulnerabilities are normally resolved using Vulnerabilities::ResolveService, which does the following:
-
Inserts a new
vulnerability_state_transitions
record, containing the current state and the new resolved state. -
Updates the
vulnerabilities
record and sets the state to resolved.There's a trigger on the
vulnerabilities
table which automatically updates thevulnerability_reads
table after thevulnerabilities
table is updated, so we don't need to explicitly add an SQL call to do this for us. -
Updates the
vulnerabilities_reads
table and sets thedismissal_reason
tonull
. -
Inserts a new record into the
notes
table with a comment explaining why the vulnerability was resolved. -
Inserts a new
system_note_metadata
record indicating that the vulnerability was resolved. -
Deletes records from the
vulnerability_user_mentions
table matching thevulnerability.id
and thenote.id
for thenote
record created in step4
above.We don't implement this operation in this MR, since it's not possible for an existing
vulnerability_user_mentions
record to match thenote.id
we just created. (TODO: Apply this same optimization to Vulnerabilities::ResolveService so that we can save a query - need to create an issue for this). -
Updates the
vulnerability_statistics
entry for theproject_id
related to thevulnerability
being resolved.
So every time a vulnerability is resolved, there are 7
SQL statements executed, 8
if we count the trigger
action for updating the vulnerability_reads
table.
This batched background migration loops through the vulnerability_reads
records where state: detected
, and attempts to resolve each vulnerability if it was created by one of the analyzers that have been removed.
Query plans
-
Insert a new
vulnerability_state_transitions
record using the create_state_transition function:INSERT INTO "vulnerability_state_transitions" ("vulnerability_id", "to_state", "from_state", "created_at", "updated_at", "author_id", "comment") VALUES (109162985, 3, 1, now(), now(), 1, 'RESOLVED_VULNERABILITY_COMMENT')
Time: 231.944 ms - planning: 0.065 ms - execution: 231.879 ms - I/O read: 83.134 ms - I/O write: 0.000 ms
-
Update the
vulnerabilities
record and set the state toresolved
using the resolve function:UPDATE "vulnerabilities" SET "state" = 3, "resolved_by_id" = 7849066, "resolved_at" = now() WHERE "vulnerabilities"."id" = 109162985
Time: 102.260 ms - planning: 2.059 ms - execution: 100.201 ms - I/O read: 89.512 ms - I/O write: 0.000 ms
-
Update the
vulnerabilities_reads
table and set thedismissal_reason
tonull
using the remove_dismissal_reason function:UPDATE "vulnerability_reads" SET "dismissal_reason" = NULL WHERE "vulnerability_reads"."id" = 109162985
Time: 188.465 ms - planning: 3.507 ms - execution: 184.958 ms - I/O read: 181.534 ms - I/O write: 0.000 ms
-
Insert a new record into the
notes
table with a comment explaining why the vulnerability was resolved using the create_system_note_with_metadata function:INSERT INTO "notes" ("note", "noteable_type", "author_id", "created_at", "updated_at", "project_id", "noteable_id", "system", "discussion_id") VALUES ('comment', 'Vulnerability', 7849066, now(), now(), 9450192, 109162985, TRUE, '560a0b0577e778ad9f8a19e59f240f35f2855468') RETURNING "id"
Time: 128.224 ms - planning: 0.101 ms - execution: 128.123 ms - I/O read: 121.046 ms - I/O write: 0.000 ms
-
Insert a new record into the
system_note_metadata
table with avulnerability_resolved
action using the create_system_note_with_metadata function:INSERT INTO "system_note_metadata" ("action", "created_at", "updated_at", "note_id") VALUES ('vulnerability_resolved', now(), now(), 1982507457)
Time: 94.777 ms - planning: 0.113 ms - execution: 94.664 ms - I/O read: 30.212 ms - I/O write: 0.000 ms
-
Update the
vulnerability_statistics
entry for theproject_id
related to thevulnerability
being resolved, using the update_vulnerability_statistics function:INSERT INTO vulnerability_statistics AS target (project_id, "total", "critical", letter_grade, created_at, updated_at) VALUES (9450192, -1, -1, 0, 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()
Time: 39.745 ms - planning: 0.285 ms - execution: 39.460 ms - I/O read: 38.396 ms - I/O write: 0.000 ms
Related to #444926