Migrate `IngestOccurrencesVulnerabilities` off of `vulnerability_finding_pipeline`
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 |
---|---|
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