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
Merge request reports
Activity
changed milestone to %12.10
added database databasereview pending labels
1 Warning This MR has a Changelog file outside ee/
, but code changes inee/
. Consider moving the Changelog file intoee/
.Reviewer roulette
Changes that require review have been detected! A merge request is normally reviewed by both a reviewer and a maintainer in its primary category (e.g. frontend or backend), and by a maintainer in all other categories.
To spread load more evenly across eligible reviewers, Danger has randomly picked a candidate for each review slot. Feel free to override this selection if you think someone else would be better-suited, or the chosen person is unavailable.
To read more on how to use the reviewer roulette, please take a look at the Engineering workflow and code review guidelines.
Once you've decided who will review this merge request, mention them as you normally would! Danger does not (yet?) automatically notify them for you.
Category Reviewer Maintainer database Yannis Roussos ( @iroussos
)Mayra Cabrera ( @mayra-cabrera
)backend Matthias Käppler ( @mkaeppler
)Igor Drozdov ( @igor.drozdov
)Generated by
DangerEdited by 🤖 GitLab Bot 🤖added backend devopssecure groupthreat insights labels
added fcv12.10 label
mentioned in merge request !26226 (closed)
Ref: #207447 (closed)
assigned to @jschafer
This replaces !26226 (closed) which has too big of a scope and is too complex accordingly (and poses an unacceptable risk for database stability).
- Resolved by Wayne Haber
@jschafer Can you give this a review? Please let me know if you have any questions or if I can do anything to help.
- Resolved by Jonathan Schafer
@rossfuhrman one comment for your consideration.
added workflowin review label