Refactor removing vulnerabilities from projects logic (!165809) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

This MR refactors the existing Vulnerabilities::Removal::RemoveFromProjectService and fixes the following problems with the already existing implementation;

Current implementation doesn't update the project_statistics after removing the vulnerabilities
Current implementation tries to remove all the records associated with a project in a single transaction
Current implementation relies on deleting the following resources by FK constraint which can cause statement timeout if there are too many records to delete;
- Vulnerabilities::Finding::Evidence
- Vulnerabilities::Flag
- Vulnerabilities::FindingSignature
Current implementation deletes the scanners associated with the given project which can cause cascading deletion of hundreds of thousands of security_findings records

This refactoring introduces more code than the existing implementation though the abstraction is necessary to have a common way of deleting the resources. This would make it easier to follow the logic and debug once the reader understands the abstraction.

After this MR gets merged, I will create another MR to add a filtering option to delete only the vulnerabilities by the resolved_on_default_branch attribute.

Related to #474793+.

Database review

Here are the queries introduced in this MR;

Batching through vulnerabilities

SELECT
    "vulnerabilities"."id"
FROM
    "vulnerabilities"
WHERE
    "vulnerabilities"."project_id" = 278964
    AND "vulnerabilities"."id" >= 11668
ORDER BY
    "vulnerabilities"."id" ASC
LIMIT 1 OFFSET 100

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31421/commands/97551

Selecting the vulnerability and finding IDs of the batch

SELECT
    "vulnerabilities"."id",
    "vulnerabilities"."finding_id"
FROM
    "vulnerabilities"
WHERE
    "vulnerabilities"."project_id" = 278964
    AND "vulnerabilities"."id" >= 11668
    AND "vulnerabilities"."id" < 438949

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97693

Deleting finding records from vulnerability_finding_evidences

DELETE FROM "vulnerability_finding_evidences"
WHERE "vulnerability_finding_evidences"."id" IN (
        SELECT
            "vulnerability_finding_evidences"."id"
        FROM
            "vulnerability_finding_evidences"
        WHERE
            "vulnerability_finding_evidences"."vulnerability_occurrence_id" IN (1969955,1991693,1999237,1999313,1999314,1999325,1999327,1999328,1999329,1999334,1999335,1999343,1999344,1999376,1999379,1999380,1999381,1999382,1999395,1999447,1999460,1999464,1999473,1999474,1999475,1999476,1999477,1999485,1999488,1999489,1999497,1999499,1999500,1999501,1999502,1999503,1999512,1999520,1999521,1999531,1999532,1999535,1999536,1999543,1999569,1999574,1999575,1999576,1999581,1999582,1999601,1999602,1999609,1999610,1999611,1999621,1999640,1999641,1999642,1999643,1999644,1999645,1999690,1999691,1999692,1999693,1999694,1999702,1999703,1999707,1999708,1999709,1999710,1999711,1999718,1999719,1999720,1999725,2001846,2001852,846687,2053532,2117283,2117284,2117287,2120729,2125296,2125297,2138997,2141260,534840,2142765,2144463,2148174,2157510,2158235,2159272,2159274,2163634,2169567)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97694

Deleting records from vulnerability_flags

DELETE FROM "vulnerability_flags"
WHERE "vulnerability_flags"."id" IN (
        SELECT
            "vulnerability_flags"."id"
        FROM
            "vulnerability_flags"
        WHERE
            "vulnerability_flags"."vulnerability_occurrence_id" IN (1969955,1991693,1999237,1999313,1999314,1999325,1999327,1999328,1999329,1999334,1999335,1999343,1999344,1999376,1999379,1999380,1999381,1999382,1999395,1999447,1999460,1999464,1999473,1999474,1999475,1999476,1999477,1999485,1999488,1999489,1999497,1999499,1999500,1999501,1999502,1999503,1999512,1999520,1999521,1999531,1999532,1999535,1999536,1999543,1999569,1999574,1999575,1999576,1999581,1999582,1999601,1999602,1999609,1999610,1999611,1999621,1999640,1999641,1999642,1999643,1999644,1999645,1999690,1999691,1999692,1999693,1999694,1999702,1999703,1999707,1999708,1999709,1999710,1999711,1999718,1999719,1999720,1999725,2001846,2001852,846687,2053532,2117283,2117284,2117287,2120729,2125296,2125297,2138997,2141260,534840,2142765,2144463,2148174,2157510,2158235,2159272,2159274,2163634,2169567)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97695

Deleting records from vulnerability_occurrence_identifiers

DELETE FROM vulnerability_occurrence_identifiers
WHERE id IN (
        SELECT
            "vulnerability_occurrence_identifiers"."id"
        FROM
            "vulnerability_occurrence_identifiers"
        WHERE
            "vulnerability_occurrence_identifiers"."occurrence_id" IN (1969955,1991693,1999237,1999313,1999314,1999325,1999327,1999328,1999329,1999334,1999335,1999343,1999344,1999376,1999379,1999380,1999381,1999382,1999395,1999447,1999460,1999464,1999473,1999474,1999475,1999476,1999477,1999485,1999488,1999489,1999497,1999499,1999500,1999501,1999502,1999503,1999512,1999520,1999521,1999531,1999532,1999535,1999536,1999543,1999569,1999574,1999575,1999576,1999581,1999582,1999601,1999602,1999609,1999610,1999611,1999621,1999640,1999641,1999642,1999643,1999644,1999645,1999690,1999691,1999692,1999693,1999694,1999702,1999703,1999707,1999708,1999709,1999710,1999711,1999718,1999719,1999720,1999725,2001846,2001852,846687,2053532,2117283,2117284,2117287,2120729,2125296,2125297,2138997,2141260,534840,2142765,2144463,2148174,2157510,2158235,2159272,2159274,2163634,2169567)
        LIMIT 100)
RETURNING
    identifier_id

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97696

Deleting records from vulnerability_identifiers

DELETE FROM vulnerability_identifiers
WHERE id IN (2900,8,1210630841,8,442365,442366,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539,279357,1577539)
    AND NOT EXISTS (
        SELECT
            1
        FROM
            vulnerability_occurrence_identifiers
        WHERE
            vulnerability_occurrence_identifiers.identifier_id = vulnerability_identifiers.id)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97697

Deleting records from vulnerability_finding_links

DELETE FROM "vulnerability_finding_links"
WHERE "vulnerability_finding_links"."id" IN (
        SELECT
            "vulnerability_finding_links"."id"
        FROM
            "vulnerability_finding_links"
        WHERE
            "vulnerability_finding_links"."vulnerability_occurrence_id" IN (1969955,1991693,1999237,1999313,1999314,1999325,1999327,1999328,1999329,1999334,1999335,1999343,1999344,1999376,1999379,1999380,1999381,1999382,1999395,1999447,1999460,1999464,1999473,1999474,1999475,1999476,1999477,1999485,1999488,1999489,1999497,1999499,1999500,1999501,1999502,1999503,1999512,1999520,1999521,1999531,1999532,1999535,1999536,1999543,1999569,1999574,1999575,1999576,1999581,1999582,1999601,1999602,1999609,1999610,1999611,1999621,1999640,1999641,1999642,1999643,1999644,1999645,1999690,1999691,1999692,1999693,1999694,1999702,1999703,1999707,1999708,1999709,1999710,1999711,1999718,1999719,1999720,1999725,2001846,2001852,846687,2053532,2117283,2117284,2117287,2120729,2125296,2125297,2138997,2141260,534840,2142765,2144463,2148174,2157510,2158235,2159272,2159274,2163634,2169567)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97698

Deleting records from vulnerability_occurrence_pipelines

DELETE FROM "vulnerability_occurrence_pipelines"
WHERE "vulnerability_occurrence_pipelines"."id" IN (
        SELECT
            "vulnerability_occurrence_pipelines"."id"
        FROM
            "vulnerability_occurrence_pipelines"
        WHERE
            "vulnerability_occurrence_pipelines"."occurrence_id" IN (1969955,1991693,1999237,1999313,1999314,1999325,1999327,1999328,1999329,1999334,1999335,1999343,1999344,1999376,1999379,1999380,1999381,1999382,1999395,1999447,1999460,1999464,1999473,1999474,1999475,1999476,1999477,1999485,1999488,1999489,1999497,1999499,1999500,1999501,1999502,1999503,1999512,1999520,1999521,1999531,1999532,1999535,1999536,1999543,1999569,1999574,1999575,1999576,1999581,1999582,1999601,1999602,1999609,1999610,1999611,1999621,1999640,1999641,1999642,1999643,1999644,1999645,1999690,1999691,1999692,1999693,1999694,1999702,1999703,1999707,1999708,1999709,1999710,1999711,1999718,1999719,1999720,1999725,2001846,2001852,846687,2053532,2117283,2117284,2117287,2120729,2125296,2125297,2138997,2141260,534840,2142765,2144463,2148174,2157510,2158235,2159272,2159274,2163634,2169567)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97699

Deleting records from vulnerability_findings_remediations

DELETE FROM "vulnerability_findings_remediations"
WHERE "vulnerability_findings_remediations"."id" IN (
        SELECT
            "vulnerability_findings_remediations"."id"
        FROM
            "vulnerability_findings_remediations"
        WHERE
            "vulnerability_findings_remediations"."vulnerability_occurrence_id" IN (1969955,1991693,1999237,1999313,1999314,1999325,1999327,1999328,1999329,1999334,1999335,1999343,1999344,1999376,1999379,1999380,1999381,1999382,1999395,1999447,1999460,1999464,1999473,1999474,1999475,1999476,1999477,1999485,1999488,1999489,1999497,1999499,1999500,1999501,1999502,1999503,1999512,1999520,1999521,1999531,1999532,1999535,1999536,1999543,1999569,1999574,1999575,1999576,1999581,1999582,1999601,1999602,1999609,1999610,1999611,1999621,1999640,1999641,1999642,1999643,1999644,1999645,1999690,1999691,1999692,1999693,1999694,1999702,1999703,1999707,1999708,1999709,1999710,1999711,1999718,1999719,1999720,1999725,2001846,2001852,846687,2053532,2117283,2117284,2117287,2120729,2125296,2125297,2138997,2141260,534840,2142765,2144463,2148174,2157510,2158235,2159272,2159274,2163634,2169567)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97700

Deleting records from vulnerability_finding_signatures

DELETE FROM "vulnerability_finding_signatures"
WHERE "vulnerability_finding_signatures"."id" IN (
        SELECT
            "vulnerability_finding_signatures"."id"
        FROM
            "vulnerability_finding_signatures"
        WHERE
            "vulnerability_finding_signatures"."finding_id" IN (1969955,1991693,1999237,1999313,1999314,1999325,1999327,1999328,1999329,1999334,1999335,1999343,1999344,1999376,1999379,1999380,1999381,1999382,1999395,1999447,1999460,1999464,1999473,1999474,1999475,1999476,1999477,1999485,1999488,1999489,1999497,1999499,1999500,1999501,1999502,1999503,1999512,1999520,1999521,1999531,1999532,1999535,1999536,1999543,1999569,1999574,1999575,1999576,1999581,1999582,1999601,1999602,1999609,1999610,1999611,1999621,1999640,1999641,1999642,1999643,1999644,1999645,1999690,1999691,1999692,1999693,1999694,1999702,1999703,1999707,1999708,1999709,1999710,1999711,1999718,1999719,1999720,1999725,2001846,2001852,846687,2053532,2117283,2117284,2117287,2120729,2125296,2125297,2138997,2141260,534840,2142765,2144463,2148174,2157510,2158235,2159272,2159274,2163634,2169567)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97701

Deleting records from vulnerability_external_issue_links

DELETE FROM "vulnerability_external_issue_links"
WHERE "vulnerability_external_issue_links"."id" IN (
        SELECT
            "vulnerability_external_issue_links"."id"
        FROM
            "vulnerability_external_issue_links"
        WHERE
            "vulnerability_external_issue_links"."vulnerability_id" IN (79991,177788,186899,187671,187672,187683,187685,187686,187687,187692,187693,187701,187702,187734,187737,187738,187739,187740,187753,187805,187818,187822,187831,187832,187833,187834,187835,187843,187846,187847,187855,187857,187858,187859,187860,187861,187870,187878,187879,187889,187890,187893,187894,187901,187927,187932,187933,187934,187939,187940,187959,187960,187967,187968,187969,187979,187998,187999,188000,188001,188002,188003,188048,188049,188050,188051,188052,188060,188061,188065,188066,188067,188068,188069,188076,188077,188078,188083,191030,191036,269214,275389,344284,344286,344291,348059,352726,352727,367155,403856,404919,405775,407529,411249,421111,421888,423007,423009,427384,433952)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97702

Deleting records from vulnerability_issue_links

DELETE FROM "vulnerability_issue_links"
WHERE "vulnerability_issue_links"."id" IN (
        SELECT
            "vulnerability_issue_links"."id"
        FROM
            "vulnerability_issue_links"
        WHERE
            "vulnerability_issue_links"."vulnerability_id" IN (79991,177788,186899,187671,187672,187683,187685,187686,187687,187692,187693,187701,187702,187734,187737,187738,187739,187740,187753,187805,187818,187822,187831,187832,187833,187834,187835,187843,187846,187847,187855,187857,187858,187859,187860,187861,187870,187878,187879,187889,187890,187893,187894,187901,187927,187932,187933,187934,187939,187940,187959,187960,187967,187968,187969,187979,187998,187999,188000,188001,188002,188003,188048,188049,188050,188051,188052,188060,188061,188065,188066,188067,188068,188069,188076,188077,188078,188083,191030,191036,269214,275389,344284,344286,344291,348059,352726,352727,367155,403856,404919,405775,407529,411249,421111,421888,423007,423009,427384,433952)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97703

Deleting records from vulnerability_merge_request_links

DELETE FROM "vulnerability_merge_request_links"
WHERE "vulnerability_merge_request_links"."id" IN (
        SELECT
            "vulnerability_merge_request_links"."id"
        FROM
            "vulnerability_merge_request_links"
        WHERE
            "vulnerability_merge_request_links"."vulnerability_id" IN (79991,177788,186899,187671,187672,187683,187685,187686,187687,187692,187693,187701,187702,187734,187737,187738,187739,187740,187753,187805,187818,187822,187831,187832,187833,187834,187835,187843,187846,187847,187855,187857,187858,187859,187860,187861,187870,187878,187879,187889,187890,187893,187894,187901,187927,187932,187933,187934,187939,187940,187959,187960,187967,187968,187969,187979,187998,187999,188000,188001,188002,188003,188048,188049,188050,188051,188052,188060,188061,188065,188066,188067,188068,188069,188076,188077,188078,188083,191030,191036,269214,275389,344284,344286,344291,348059,352726,352727,367155,403856,404919,405775,407529,411249,421111,421888,423007,423009,427384,433952)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97704

Deleting records from vulnerability_reads

DELETE FROM "vulnerability_reads"
WHERE "vulnerability_reads"."vulnerability_id" IN (
        SELECT
            "vulnerability_reads"."vulnerability_id"
        FROM
            "vulnerability_reads"
        WHERE
            "vulnerability_reads"."vulnerability_id" IN (79991,177788,186899,187671,187672,187683,187685,187686,187687,187692,187693,187701,187702,187734,187737,187738,187739,187740,187753,187805,187818,187822,187831,187832,187833,187834,187835,187843,187846,187847,187855,187857,187858,187859,187860,187861,187870,187878,187879,187889,187890,187893,187894,187901,187927,187932,187933,187934,187939,187940,187959,187960,187967,187968,187969,187979,187998,187999,188000,188001,188002,188003,188048,188049,188050,188051,188052,188060,188061,188065,188066,188067,188068,188069,188076,188077,188078,188083,191030,191036,269214,275389,344284,344286,344291,348059,352726,352727,367155,403856,404919,405775,407529,411249,421111,421888,423007,423009,427384,433952)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97705

Deleting records from vulnerability_state_transitions

DELETE FROM "vulnerability_state_transitions"
WHERE "vulnerability_state_transitions"."id" IN (
        SELECT
            "vulnerability_state_transitions"."id"
        FROM
            "vulnerability_state_transitions"
        WHERE
            "vulnerability_state_transitions"."vulnerability_id" IN (79991,177788,186899,187671,187672,187683,187685,187686,187687,187692,187693,187701,187702,187734,187737,187738,187739,187740,187753,187805,187818,187822,187831,187832,187833,187834,187835,187843,187846,187847,187855,187857,187858,187859,187860,187861,187870,187878,187879,187889,187890,187893,187894,187901,187927,187932,187933,187934,187939,187940,187959,187960,187967,187968,187969,187979,187998,187999,188000,188001,188002,188003,188048,188049,188050,188051,188052,188060,188061,188065,188066,188067,188068,188069,188076,188077,188078,188083,191030,191036,269214,275389,344284,344286,344291,348059,352726,352727,367155,403856,404919,405775,407529,411249,421111,421888,423007,423009,427384,433952)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97706

Deleting records from vulnerability_user_mentions

DELETE FROM "vulnerability_user_mentions"
WHERE "vulnerability_user_mentions"."id" IN (
        SELECT
            "vulnerability_user_mentions"."id"
        FROM
            "vulnerability_user_mentions"
        WHERE
            "vulnerability_user_mentions"."vulnerability_id" IN (79991,177788,186899,187671,187672,187683,187685,187686,187687,187692,187693,187701,187702,187734,187737,187738,187739,187740,187753,187805,187818,187822,187831,187832,187833,187834,187835,187843,187846,187847,187855,187857,187858,187859,187860,187861,187870,187878,187879,187889,187890,187893,187894,187901,187927,187932,187933,187934,187939,187940,187959,187960,187967,187968,187969,187979,187998,187999,188000,188001,188002,188003,188048,188049,188050,188051,188052,188060,188061,188065,188066,188067,188068,188069,188076,188077,188078,188083,191030,191036,269214,275389,344284,344286,344291,348059,352726,352727,367155,403856,404919,405775,407529,411249,421111,421888,423007,423009,427384,433952)
        LIMIT 100)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97707

Deleting records from vulnerabilities

DELETE FROM "vulnerabilities"
WHERE "vulnerabilities"."id" IN (79991,177788,186899,187671,187672,187683,187685,187686,187687,187692,187693,187701,187702,187734,187737,187738,187739,187740,187753,187805,187818,187822,187831,187832,187833,187834,187835,187843,187846,187847,187855,187857,187858,187859,187860,187861,187870,187878,187879,187889,187890,187893,187894,187901,187927,187932,187933,187934,187939,187940,187959,187960,187967,187968,187969,187979,187998,187999,188000,188001,188002,188003,188048,188049,188050,188051,188052,188060,188061,188065,188066,188067,188068,188069,188076,188077,188078,188083,191030,191036,269214,275389,344284,344286,344291,348059,352726,352727,367155,403856,404919,405775,407529,411249,421111,421888,423007,423009,427384,433952)

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97708

Deleting records from vulnerability_occurrences

DELETE FROM "vulnerability_occurrences"
WHERE "vulnerability_occurrences"."id" IN (1969955,1991693,1999237,1999313,1999314,1999325,1999327,1999328,1999329,1999334,1999335,1999343,1999344,1999376,1999379,1999380,1999381,1999382,1999395,1999447,1999460,1999464,1999473,1999474,1999475,1999476,1999477,1999485,1999488,1999489,1999497,1999499,1999500,1999501,1999502,1999503,1999512,1999520,1999521,1999531,1999532,1999535,1999536,1999543,1999569,1999574,1999575,1999576,1999581,1999582,1999601,1999602,1999609,1999610,1999611,1999621,1999640,1999641,1999642,1999643,1999644,1999645,1999690,1999691,1999692,1999693,1999694,1999702,1999703,1999707,1999708,1999709,1999710,1999711,1999718,1999719,1999720,1999725,2001846,2001852,846687,2053532,2117283,2117284,2117287,2120729,2125296,2125297,2138997,2141260,534840,2142765,2144463,2148174,2157510,2158235,2159272,2159274,2163634,2169567)

This query takes too long because it triggers cascading delete on DB Lab but we are already removing all those records before running this delete query with this MR.

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97710

Updating the project statistics

UPDATE "project_statistics" SET vulnerability_count = vulnerability_count - 100 WHERE "project_statistics"."id" = 278964

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/31465/commands/97711

Edited Sep 12, 2024 by Mehmet Emin INAC

Refactor removing vulnerabilities from projects logic

What does this MR do and why?

Database review

Merge request reports