Migrate dismissals to vulnerabilities
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.
code
1. Find all projects that have vulnerabilities recordshttps://gitlab.slack.com/archives/CLJMDRD8C/p1587416589003900 (GitLab internal)
https://explain.dalibo.com/plan/N5c
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)
https://explain.dalibo.com/plan/l0
Time: 1.155 ms
explain
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?
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