Skip to content

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:

  1. Inserts a new vulnerability_state_transitions record, containing the current state and the new resolved state.

  2. Updates the vulnerabilities record and sets the state to resolved.

    There's a trigger on the vulnerabilities table which automatically updates the vulnerability_reads table after the vulnerabilities table is updated, so we don't need to explicitly add an SQL call to do this for us.

  3. Updates the vulnerabilities_reads table and sets the dismissal_reason to null.

  4. Inserts a new record into the notes table with a comment explaining why the vulnerability was resolved.

  5. Inserts a new system_note_metadata record indicating that the vulnerability was resolved.

  6. Deletes records from the vulnerability_user_mentions table matching the vulnerability.id and the note.id for the note record created in step 4 above.

    We don't implement this operation in this MR, since it's not possible for an existing vulnerability_user_mentions record to match the note.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).

  7. Updates the vulnerability_statistics entry for the project_id related to the vulnerability 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

  1. 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')

    query plan

    Time: 231.944 ms
      - planning: 0.065 ms
      - execution: 231.879 ms
        - I/O read: 83.134 ms
        - I/O write: 0.000 ms
  2. Update the vulnerabilities record and set the state to resolved using the resolve function:

    UPDATE "vulnerabilities" SET "state" = 3, "resolved_by_id" = 7849066,
    "resolved_at" = now() WHERE "vulnerabilities"."id" = 109162985

    query plan

    Time: 102.260 ms
      - planning: 2.059 ms
      - execution: 100.201 ms
        - I/O read: 89.512 ms
        - I/O write: 0.000 ms
  3. Update the vulnerabilities_reads table and set the dismissal_reason to null using the remove_dismissal_reason function:

    UPDATE "vulnerability_reads" SET "dismissal_reason" = NULL
    WHERE "vulnerability_reads"."id" = 109162985

    query plan

    Time: 188.465 ms
      - planning: 3.507 ms
      - execution: 184.958 ms
        - I/O read: 181.534 ms
        - I/O write: 0.000 ms
  4. 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"

    query plan

    Time: 128.224 ms
      - planning: 0.101 ms
      - execution: 128.123 ms
        - I/O read: 121.046 ms
        - I/O write: 0.000 ms
  5. Insert a new record into the system_note_metadata table with a vulnerability_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)

    query plan

    Time: 94.777 ms
      - planning: 0.113 ms
      - execution: 94.664 ms
        - I/O read: 30.212 ms
        - I/O write: 0.000 ms
  6. Update the vulnerability_statistics entry for the project_id related to the vulnerability 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()

    query plan

    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

Edited by Adam Cohen

Merge request reports