Fix Vulnerabilities created manually with wrong scanner
What does this MR do and why?
Create a Background Migration which cycles through Security Findings that are incorrectly associated with scanners that do not belong to their owning project, and upsert an appropriately scoped scanner to re-associate the findings with.
Postgres.ai
Select Batch of Vulnerability Findings where their project ID does not match their Scanner Project ID
explain SELECT
"vulnerability_occurrences".*
FROM
"vulnerability_occurrences"
INNER JOIN "vulnerability_scanners" ON "vulnerability_scanners"."id" = "vulnerability_occurrences"."scanner_id"
WHERE (vulnerability_occurrences.project_id != vulnerability_scanners.project_id)
AND "vulnerability_occurrences"."id" BETWEEN 1 AND 5000
AND "vulnerability_occurrences"."id" >= 1
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/12110/commands/42967
Upsert Vulnerability Scanner with appropriate project ID but same attributes
explain INSERT INTO "vulnerability_scanners" ("created_at", "updated_at", "project_id", "external_id", "name", "vendor")
VALUES ('2022-09-08 15:40:10.915601', '2022-09-08 15:40:10.915601', 20, 'starboard', 'Starboard', 'GitLab')
ON CONFLICT ("project_id", "external_id")
DO UPDATE SET
"created_at" = excluded."created_at", "updated_at" = excluded."updated_at", "name" = excluded."name", "vendor" = excluded."vendor"
RETURNING
"id"
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12004/commands/42572
Associate Findings to appropriately related scanner record
explain UPDATE
"vulnerability_occurrences"
SET
"scanner_id" = 1
WHERE
"vulnerability_occurrences"."id" IN (1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12004/commands/42573
Update Vulnerability Read records accordingly
UPDATE
"vulnerability_reads"
SET
"scanner_id" = 1
WHERE
"vulnerability_reads"."uuid" IN ('427bf320-f300-50f8-b24a-183df6f6b63f', '0c1bc7a9-6f2f-5619-842c-3745fe9387a2', 'daa41cad-2d5f-560e-ae59-ba853fc660da', 'd67df856-9bb7-51e2-beea-be45153bbdbb', 'd56e836e-c5ae-598b-8a96-b80fac68b0b7', 'cebcb03a-6fc4-5a48-af99-5b2d4df46dcd', 'be8bdc60-0da3-5b1d-82f3-f76cd756c6a3', 'ab8b8280-6e0d-5e4b-b9f7-a0a611a4548e', '94cda4d9-d857-568d-b4a4-343c5ebca2b3', '6d5567a4-93da-5c79-bd49-51fde566ef3e')
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/12004/commands/42574
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Related to #355948 (closed)