Skip to content

Resolve vulnerabilities with scanner collision in child pipelines

mo khan requested to merge mokhax/411821/spike-child-pipelines into master

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.

image

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

image image

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.

Edited by mo khan

Merge request reports