Migrate erroneously shared vulnerability scanners
What does this MR do and why?
Introduces a background migration that fixes vulnerability findings with erroneously associated vulnerability scanners.
Due to a bug, Vulnerabilities::Scanner
records were erroneously reused across projects. This causes scanner: null
GraphQL responses when querying project vulnerabilities due to lack of read permission.
Specifically, for concerned rows, the vulnerability_occurrences.project_id
column mismatches the associated vulnerability_scanners.project_id
.
See:
Concerned rows
There are ~170k rows concerned (postgres.ai).
These rows are in the the following ID range (source):
min | max
----------+----------
23658505 | 204428752
The upper limit row was created at 2022-04-05.
Database
Temporary index
All query plans rely on the following temporary index from !91488 (merged):
CREATE INDEX tmp_index_vulnerability_occurrences_on_id_and_scanner_id ON vulnerability_occurrences USING btree (id, scanner_id) WHERE (report_type = ANY (ARRAY[7, 99]));
Queries
each_batch find start ID
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10813/commands/38928
SELECT
"vulnerability_occurrences"."id"
FROM
"vulnerability_occurrences"
INNER JOIN "vulnerability_scanners" ON "vulnerability_scanners"."id" = "vulnerability_occurrences"."scanner_id"
WHERE
"vulnerability_occurrences"."report_type" IN(7, 99)
AND(vulnerability_occurrences.project_id != vulnerability_scanners.project_id)
AND "vulnerability_occurrences"."id" BETWEEN 136598400 AND 136599400
ORDER BY
"vulnerability_occurrences"."id" ASC
LIMIT 1;
each_batch find end ID
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10813/commands/38929
SELECT
"vulnerability_occurrences"."id"
FROM
"vulnerability_occurrences"
INNER JOIN "vulnerability_scanners" ON "vulnerability_scanners"."id" = "vulnerability_occurrences"."scanner_id"
WHERE
"vulnerability_occurrences"."report_type" IN(7, 99)
AND(vulnerability_occurrences.project_id != vulnerability_scanners.project_id)
AND "vulnerability_occurrences"."id" BETWEEN 136598400 AND 136599400
AND "vulnerability_occurrences"."id" >= 136598474
ORDER BY
"vulnerability_occurrences"."id" ASC
LIMIT 1 OFFSET 100;
each_batch load iteration
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10813/commands/38930
SELECT
"vulnerability_occurrences"."uuid"
FROM
"vulnerability_occurrences"
INNER JOIN "vulnerability_scanners" ON "vulnerability_scanners"."id" = "vulnerability_occurrences"."scanner_id"
WHERE
"vulnerability_occurrences"."report_type" IN(7, 99)
AND(vulnerability_occurrences.project_id != vulnerability_scanners.project_id)
AND "vulnerability_occurrences"."id" BETWEEN 136598400 AND 136599400
-- there are only 56 rows included in this batch,
-- and the sub batch size is 100, hence the lack
-- of an upper bound
AND "vulnerability_occurrences"."id" >= 136598474; -- AND "vulnerability_occurrences"."id" < $UPPER_BOUND;
Load scanner
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10813/commands/38932
SELECT
"vulnerability_scanners".*
FROM
"vulnerability_scanners"
WHERE
"vulnerability_scanners"."id" = 485
LIMIT 1;
Upsert scanner
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10813/commands/38933
INSERT INTO "vulnerability_scanners" ("created_at", "updated_at", "project_id", "external_id", "name", "vendor")
VALUES('2022-06-29 14:15:31.264702', '2022-06-29 14:15:31.344999', 437, 'starboard_trivy', 'Trivy (via Starboard Operator)', '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";
Update scanner_id
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10813/commands/38934
UPDATE
"vulnerability_occurrences"
SET
"scanner_id" = 486
WHERE
"vulnerability_occurrences"."id" IN(
136598474, 136598475, 136598476,
136598478, 136598529, 136598530,
136598531, 136598556, 136598607,
136598608, 136598659, 136598710,
136598761, 136598762, 136598763,
136598797, 136598802, 136598803,
136598804, 136598805, 136598806,
136598807, 136598808, 136598809,
136598810, 136598811, 136598812,
136598813, 136598814, 136598815,
136598816, 136598817, 136598819,
136598820, 136598822, 136598823,
136598824, 136598826, 136598831,
136598832, 136598833, 136598834,
136598835, 136598836, 136598837,
136598838, 136598839, 136598840,
136598841, 136598842, 136598843,
136598844, 136598845, 136598846,
136598848, 136598477
);
Update vulnerability reads
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/10813/commands/38935
UPDATE
"vulnerability_reads"
SET
"scanner_id" = 486
WHERE
"vulnerability_reads"."uuid" IN (
'159e076e-a4d1-5bc9-87a2-774743aeb2df',
'613ba812-c199-5e82-bcdb-7554f09fe0f7',
'3b798a8e-fdef-53d7-abca-9957607f7254',
'3b2c1547-3116-5790-85b6-226822207bcf',
'a4f17a63-815b-5c9d-9b4f-f5101bc56b78',
'2dd38430-cfcd-5758-885a-23163a36d65e',
'f5a5b625-fa15-53a5-ade5-482d0bbce57c',
'46e4971d-7dea-54b4-b917-e9a23e14dd19',
'c3043abb-f954-5d18-868c-740906dac3c9',
'dc08e72f-5fe9-5575-ac90-6225414c4799',
'97aee1e1-e0da-5f32-baa9-818ffb3a9adc',
'd06c48eb-0e4d-5e37-a28e-af5c94a0dfb8',
'9af66de3-56b1-56c7-b4c1-ac71fd908cc2',
'9b1622f8-7e84-5cf9-9b95-5bb506270f63',
'ddd5be9a-8aa6-5cbc-96ab-c4c8d589912b',
'90ccb529-1f89-5940-83d6-94e94bcff9dd',
'416144f2-d9da-5201-8af9-5897b0658fdd',
'60decd84-8305-5965-a752-e1ed055aff59',
'fac1b29a-357a-50f6-844e-e55902a566c8',
'5aba8db6-5579-5f4a-befb-65c5b473b9d4',
'faf94bee-40cb-51f0-a3a8-4a4f754824d3',
'70de8a70-845f-57f8-a2c2-7e4823c4f4e4',
'05faa224-2323-538c-88f5-de256a939e1a',
'722f549f-2356-5304-8260-42051690b878',
'cd47c865-db72-5b06-aa4f-10558cabf373',
'6fe2de19-0fa8-5070-9185-b5748ac3034d',
'5289e032-dd1a-54f7-b445-4aebfd09ebc7',
'4892e476-cc1d-5c38-b22e-30c3620cccf4',
'2ab980d6-7969-58c5-829b-31791dc7d60f',
'6d1ff62a-260f-5d0a-84f3-0cdcc335ffb5',
'7f164888-626a-55f8-8f28-7e1f1193c63f',
'd219a3b1-6258-5aa0-ae07-c5e08db4ac9a',
'03b3d8ea-2dc9-5690-98b3-c94a44eae6f6',
'2973da1b-55d2-5c2c-87ab-0bdca552b5cf',
'edbf5097-9b15-5cba-85a9-b01df97f88f1',
'bad6a864-3fff-5771-9982-557ec7f6d759',
'7d0c8ad3-9b45-5c29-bb45-a199fdfc9d5a',
'8ef56f95-7d7a-5d36-911a-f69c2eebb690',
'acb2c0de-8e2a-525e-8bf0-2abc2cf1656c',
'ae27e6c0-2f57-5b9f-af87-cc150e915d80',
'f7366ec5-b7d2-5f43-88b0-07635e30baeb',
'25e95945-c84f-59a6-b42e-ed0a6b34608b',
'7daa215b-d317-5f6a-9ad8-2f5e49a7282e',
'0984b4a7-8adf-5016-8ec4-96a4a3080f3a',
'96acd225-19da-577e-b234-9e736b0f4771',
'3f7cedcf-facf-5633-82c9-70a08bb54937',
'33b11edd-321e-5483-8cdd-d0d7a36ca3ef',
'be62de13-c491-5c94-af23-f551934fc78d',
'0b45cdb7-8514-5fb8-97e8-ee23a914246a',
'bc2d419d-8622-5c29-a557-3552b070974e',
'97102d14-53f8-500f-939f-fb07edf8ca8e',
'bf6142b3-10de-5f1b-bda8-bdd356089d7d',
'4d3b16dd-9708-5883-8b2f-ad6c7f432a1f',
'f8737eaa-b629-59bd-bd91-3922dfdba395',
'fd55cdcf-64de-5646-a0c1-a7be91dc71cb',
'0bfc89ff-3e06-5b82-b166-92a3f8dcf586'
);
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 #357659 (closed)