Skip to content

Migrate `IngestOccurrencesVulnerabilities` off of `vulnerability_finding_pipeline`

Michael Becker requested to merge 480671-migrate-sbom-ingestion-tasks-ingestoccurrencesvulnerabilities-off-of-the into master

What does this MR do and why?

As part of the epic to delete the vulnerability_finding_pipelines table, we need to migrate any application code using that table to a new query

The Sbom::Ingestion::Tasks::IngestOccurrencesVulnerabilities task ends up using this table when it calls occurrence_map.vulnerability_ids.

This MR adds an SQL agg on the id column so we can migrate off of the table in a way that leaves the downstream dependencies unaffected.

A more detailed description is available in the linked issue.

Spec changes

before after
image image

SQL

Current Query

WITH occurrence_maps (
    name,
    version,
    path
) AS (
    VALUES ('sidekiq', '6.5.12', 'Gemfile.lock'))
SELECT
    occurrence_maps.name,
    occurrence_maps.version,
    occurrence_maps.path,
    MAX(vulnerability_occurrences.severity) AS highest_severity,
    COUNT(vulnerability_occurrences.id) AS vulnerability_count
FROM
    "vulnerability_occurrences"
    JOIN occurrence_maps ON occurrence_maps.name = (vulnerability_occurrences.location -> 'dependency' -> 'package' ->> 'name')::text
        AND occurrence_maps.version = (vulnerability_occurrences.location -> 'dependency' ->> 'version')::text
        AND occurrence_maps.path = COALESCE(vulnerability_occurrences.location ->> 'file', vulnerability_occurrences.location ->> 'image')::text
WHERE
    "vulnerability_occurrences"."report_type" IN (2, 1)
        AND "vulnerability_occurrences"."project_id" = 278964
    GROUP BY
        occurrence_maps.name,
        occurrence_maps.version,
        occurrence_maps.path

Updated Query

WITH occurrence_maps (
    name,
    version,
    path
) AS (
    VALUES ('sidekiq', '6.5.12', 'Gemfile.lock'))
SELECT
    occurrence_maps.name,
    occurrence_maps.version,
    occurrence_maps.path,
    array_agg(vulnerability_occurrences.vulnerability_id) AS vulnerability_ids,
    MAX(vulnerability_occurrences.severity) AS highest_severity,
    COUNT(vulnerability_occurrences.id) AS vulnerability_count
FROM
    "vulnerability_occurrences"
    JOIN occurrence_maps ON occurrence_maps.name = (vulnerability_occurrences.location -> 'dependency' -> 'package' ->> 'name')::text
        AND occurrence_maps.version = (vulnerability_occurrences.location -> 'dependency' ->> 'version')::text
        AND occurrence_maps.path = COALESCE(vulnerability_occurrences.location ->> 'file', vulnerability_occurrences.location ->> 'image')::text
WHERE
    "vulnerability_occurrences"."report_type" IN (2, 1)
        AND "vulnerability_occurrences"."project_id" = 278964
    GROUP BY
        occurrence_maps.name,
        occurrence_maps.version,
        occurrence_maps.path

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Changelog: changed
EE: true
epic: &11241
resolves: #480671 (closed)

Edited by Michael Becker

Merge request reports