Skip to content

Migrate erroneously shared vulnerability scanners

Dominic Bauer requested to merge 357659-migrate-erroneously-shared-vulnerability-scanners into master

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.

Related to #357659 (closed)

Edited by Dominic Bauer

Merge request reports