Skip to content

Use new query to associate SBOM occurrences and vulnerabilities

Michael Becker requested to merge 472760-fetch_vulnerability_findings_for_sbom_occurrences_from_new_query into master

What does this MR do and why?

As part of the epic to drop the vulnerability_finding_pipelines table, we need to migrate the SBOM ingestion flow off of using the table.

Currently, during SBOM ingestion, we use the vulnerability_finding_pipelines table to fetch all existing vulnerability findings associated with a given pipeline, and then we build up an association map in memory mapping every dependency to existing vulnerability ids, and keeping track of some info, like the highest_severity of the vulnerabilities associated with a given dependency.1

We determined in this thread that we don't actually have a reason to scope this query to a given pipeline.

This change implements a new query that:

  • queries the vulnerability_occurrences table directly
  • uses the dependency attributes to filter in-database as opposed to in-memory
  • doesn't yet remove any of the original code paths to give us any easy rollback 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.

SQL

Current Query

SELECT
    "vulnerability_occurrences".*
FROM
    "vulnerability_occurrences"
    INNER JOIN "vulnerability_occurrence_pipelines" ON "vulnerability_occurrences"."id" = "vulnerability_occurrence_pipelines"."occurrence_id"
WHERE
    "vulnerability_occurrence_pipelines"."pipeline_id" = 1388466688
    AND "vulnerability_occurrences"."report_type" IN (2, 1)

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,
    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: !162713 (merged)
related to: #472760 (closed)
Changelog: changed
EE: true

  1. https://gitlab.com/gitlab-org/gitlab/-/blob/1622588ac4f4c860036b8e9ae9efc5dab0d519a8/ee/app/services/sbom/ingestion/vulnerabilities.rb#L47 ↩

Edited by Michael Becker

Merge request reports