Resolve vulnerabilities with scanner collision in child pipelines

What does this MR do and why?

This MR resolves vulnerabilities from child pipelines when there is a collision between two or more of the same scanners running from two or more child pipelines.

#411821 (closed) #393305 (closed)

WITH RECURSIVE base_and_descendants AS (
    (
        SELECT
            ci_pipelines.id,
            ci_pipelines.ref,
            ci_pipelines.sha,
            ci_pipelines.before_sha,
            ci_pipelines.created_at,
            ci_pipelines.updated_at,
            ci_pipelines.tag,
            ci_pipelines.yaml_errors,
            ci_pipelines.committed_at,
            ci_pipelines.project_id,
            ci_pipelines.status,
            ci_pipelines.started_at,
            ci_pipelines.finished_at,
            ci_pipelines.duration,
            ci_pipelines.user_id,
            ci_pipelines.lock_version,
            ci_pipelines.auto_canceled_by_id,
            ci_pipelines.pipeline_schedule_id,
            ci_pipelines.source,
            ci_pipelines.protected,
            ci_pipelines.config_source,
            ci_pipelines.failure_reason,
            ci_pipelines.iid,
            ci_pipelines.merge_request_id,
            ci_pipelines.source_sha,
            ci_pipelines.target_sha,
            ci_pipelines.external_pull_request_id,
            ci_pipelines.ci_ref_id,
            ci_pipelines.locked,
            ci_pipelines.partition_id
        FROM
            ci_pipelines
        WHERE
            ci_pipelines.id = 926680935
    )
    UNION
    (
        SELECT
            ci_pipelines.id,
            ci_pipelines.ref,
            ci_pipelines.sha,
            ci_pipelines.before_sha,
            ci_pipelines.created_at,
            ci_pipelines.updated_at,
            ci_pipelines.tag,
            ci_pipelines.yaml_errors,
            ci_pipelines.committed_at,
            ci_pipelines.project_id,
            ci_pipelines.status,
            ci_pipelines.started_at,
            ci_pipelines.finished_at,
            ci_pipelines.duration,
            ci_pipelines.user_id,
            ci_pipelines.lock_version,
            ci_pipelines.auto_canceled_by_id,
            ci_pipelines.pipeline_schedule_id,
            ci_pipelines.source,
            ci_pipelines.protected,
            ci_pipelines.config_source,
            ci_pipelines.failure_reason,
            ci_pipelines.iid,
            ci_pipelines.merge_request_id,
            ci_pipelines.source_sha,
            ci_pipelines.target_sha,
            ci_pipelines.external_pull_request_id,
            ci_pipelines.ci_ref_id,
            ci_pipelines.locked,
            ci_pipelines.partition_id
        FROM
            ci_pipelines,
            base_and_descendants,
            ci_sources_pipelines
        WHERE
            ci_sources_pipelines.pipeline_id = ci_pipelines.id AND
            ci_sources_pipelines.source_pipeline_id = base_and_descendants.id AND
            ci_sources_pipelines.source_project_id = ci_sources_pipelines.project_id
    )
)
SELECT
    ci_pipelines.id,
    ci_pipelines.ref,
    ci_pipelines.sha,
    ci_pipelines.before_sha,
    ci_pipelines.created_at,
    ci_pipelines.updated_at,
    ci_pipelines.tag,
    ci_pipelines.yaml_errors,
    ci_pipelines.committed_at,
    ci_pipelines.project_id,
    ci_pipelines.status,
    ci_pipelines.started_at,
    ci_pipelines.finished_at,
    ci_pipelines.duration,
    ci_pipelines.user_id,
    ci_pipelines.lock_version,
    ci_pipelines.auto_canceled_by_id,
    ci_pipelines.pipeline_schedule_id,
    ci_pipelines.source,
    ci_pipelines.protected,
    ci_pipelines.config_source,
    ci_pipelines.failure_reason,
    ci_pipelines.iid,
    ci_pipelines.merge_request_id,
    ci_pipelines.source_sha,
    ci_pipelines.target_sha,
    ci_pipelines.external_pull_request_id,
    ci_pipelines.ci_ref_id,
    ci_pipelines.locked,
    ci_pipelines.partition_id
FROM
    base_and_descendants AS ci_pipelines;

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/20373/commands/66554

Before:

SELECT "security_scans".*
FROM "security_scans"
WHERE "security_scans"."pipeline_id" = 926680935
AND (jsonb_array_length(COALESCE(info->'errors', '[]'::jsonb)) = 0)
AND "security_scans"."latest" = TRUE;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/20283/commands/66319

After:

SELECT "security_scans".*
FROM "security_scans"
WHERE "security_scans"."pipeline_id" IN (926680935, 926680953, 926680950)
AND (jsonb_array_length(COALESCE(info->'errors', '[]'::jsonb)) = 0) 
AND "security_scans"."latest" = TRUE;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/20283/commands/66314

Screenshots or screen recordings

Parent project vulnerability report.

Each of the vulnerability reports for the child projects only included vulnerabilities that originate from that project.

See #411821 (comment 1452112784) for more information.

How to set up and validate locally

1. Clone each of the repositories listed under https://gitlab.com/gitlab-org/govern/threat-insights-demos/verification-projects/verify-393305 to your local gdk
2. Trigger a pipeline in the project named "parent-of-multi-project".
3. Navigate to the vulnerability report for the "parent-of-multi-project" project.
4. Ensure that the vulnerability report for the "parent-of-multi-project" project is empty.
5. Navigate to the vulnerability report for the project named "child-01-of-multi-project" and ensure that there is 1 vulnerability.
6. Navigate to the vulnerability report for the project named "child-02-of-multi-project" and ensure that there is 1 vulnerability.

See screenshots for examples.

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.