Migrate dismissals to vulnerabilities

rossfuhrman requested to merge rf-migrate-vulnerability-dismissals into master

What does this MR do?

Adds a background migration to update the state of vulnerabilities records for all projects to be dismissed where the corresponding vulnerability_occurrences record has been dismissed.

There are roughly 3_227 projects that need to be migrated. With a two minute delay between scheduling them, that comes up to roughly 4.4 days to run.

All of the below queries were ran against our #database-lab slack channel.

1. Find all projects that have vulnerabilities records code

https://gitlab.slack.com/archives/CLJMDRD8C/p1587416589003900 (GitLab internal)


Time: 527.164 ms

explain SELECT "vulnerabilities"."project_id" FROM "vulnerabilities" GROUP BY "vulnerabilities"."project_id" ORDER BY "vulnerabilities"."project_id"

2. Set the state of vulnerabilities to dismissed for a project

https://gitlab.slack.com/archives/CLJMDRD8C/p1586457259428200 (GitLab internal)


Time: 1.155 ms

UPDATE vulnerabilities
            SET state = 2
            FROM vulnerability_occurrences
            WHERE vulnerability_occurrences.vulnerability_id = "vulnerabilities"."id"
              AND vulnerabilities.state = 1
              AND (
                EXISTS (
                  SELECT 1
                  FROM vulnerability_feedback
                  WHERE vulnerability_occurrences.report_type = vulnerability_feedback.category
                    AND vulnerability_occurrences.project_id = vulnerability_feedback.project_id
                    AND ENCODE(vulnerability_occurrences.project_fingerprint, 'HEX') = vulnerability_feedback.project_fingerprint
                    AND vulnerability_feedback.feedback_type = 0
              AND vulnerability_occurrences.project_id = 14492286;

Does this MR meet the acceptance criteria?


Availability and Testing


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
Edited by Mayra Cabrera

Merge request reports