Skip to content
Snippets Groups Projects

Migrate dismissals to vulnerabilities

Merged 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)

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

Availability and Testing

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

Merge request reports

Loading
Loading

Activity

Filter activity
  • Approvals
  • Assignees & reviewers
  • Comments (from bots)
  • Comments (from users)
  • Commits & branches
  • Edits
  • Labels
  • Lock status
  • Mentions
  • Merge request status
  • Tracking
  • rossfuhrman added 1 commit

    added 1 commit

    • f27d5221 - Migrate dismissals to vulnerabilities

    Compare with previous version

  • @rossfuhrman one comment for your consideration.

  • Jonathan Schafer approved this merge request

    approved this merge request

  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Loading
  • Please register or sign in to reply
    Loading