Skip to content

Fix Vulnerabilities created manually with wrong scanner

Michał Zając requested to merge 355948-fix-manually-created-scanner-data into master

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.

Related to #355948 (closed)

Edited by Gregory Havenga

Merge request reports