Skip to content

Add vulnerabilities to sbom_occurrences ingestion

Zamir Martins requested to merge add_vulnerabilities_to_sbom_occurrences_ingestion into master

What does this MR do and why?

Related issue: #426122 (closed)

Add vulnerabilities to sbom_occurrences ingestion.

EE: true Changelog: added

Query plan

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23083/commands/74311

SELECT "vulnerability_occurrences"."id", "vulnerability_occurrences"."created_at", "vulnerability_occurrences"."updated_at", "vulnerability_occurrences"."severity", "vulnerability_occurrences"."confidence", "vulnerability_occurrences"."report_type", "vulnerability_occurrences"."project_id","vulnerability_occurrences"."scanner_id", "vulnerability_occurrences"."primary_identifier_id", "vulnerability_occurrences"."project_fingerprint","vulnerability_occurrences"."location_fingerprint", "vulnerability_occurrences"."name", "vulnerability_occurrences"."metadata_version","vulnerability_occurrences"."raw_metadata", "vulnerability_occurrences"."vulnerability_id", "vulnerability_occurrences"."details","vulnerability_occurrences"."description", "vulnerability_occurrences"."solution", "vulnerability_occurrences"."cve", "vulnerability_occurrences"."location", "vulnerability_occurrences"."detection_method", "vulnerability_occurrences"."uuid"
FROM "vulnerability_occurrences"
INNER JOIN "vulnerability_occurrence_pipelines" ON "vulnerability_occurrences"."id" = "vulnerability_occurrence_pipelines"."occurrence_id"
WHERE "vulnerability_occurrence_pipelines"."pipeline_id" = 929047842
AND "vulnerability_occurrences"."report_type" IN (2, 1)
ORDER BY "vulnerability_occurrences"."severity" DESC, "vulnerability_occurrences"."confidence" DESC, "vulnerability_occurrences"."id" ASC;
Time: 57.564 ms
  - planning: 8.825 ms
  - execution: 48.739 ms
    - I/O read: 46.378 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 88 (~704.00 KiB) from the buffer pool
  - reads: 15 (~120.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Bulk insert(IngestOccurrencesVulnerabilities)

Query plan for a sample of 10 records: link

INSERT INTO "sbom_occurrences_vulnerabilities" ("sbom_occurrence_id","vulnerability_id","created_at","updated_at") VALUES (2989727, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244'), (2989728, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244'), (2989729, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244'), (2989730, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244'), (2989731, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244'), (2989732, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244'), (2989733, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244'), (2989734, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244'), (2989735, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244'), (2989736, 95284240, '2023-11-21 22:53:42.144244', '2023-11-21 22:53:42.144244') ON CONFLICT ("sbom_occurrence_id","vulnerability_id") DO UPDATE SET "updated_at"=excluded."updated_at" RETURNING "sbom_occurrence_id","vulnerability_id";

Bulk insert(IngestOccurrences)

Query plan for a sample of 10 records: link

INSERT INTO "sbom_occurrences" ("project_id","pipeline_id","component_id","component_version_id","source_id","commit_sha","uuid","package_manager","input_file_path","licenses","component_name","vulnerability_count","highest_severity","created_at","updated_at")
 VALUES
 (44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633860', '88810776-7bc3-55a3-8599-b7c5fc136e40', 'bundler', 'qa/Gemfile.lock', '[]', 'yajl-ruby', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119'),
 (44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633861', 'b3e88674-7174-57c2-b8ab-8177fb48bcd1', 'bundler', 'qa/Gemfile.lock', '[]', 'pyu-ruby-sasl', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119'),
 (44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633862', 'cae749ee-bee8-5b18-8328-3196a26956f2', 'bundler', 'qa/Gemfile.lock', '[]', 'puma', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119'),
 (44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633863', '88810776-7bc3-55a3-8599-b7c5fc136e43', 'bundler', 'qa/Gemfile.lock', '[]', 'public_suffix', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119'),
 (44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633864', 'b3e88674-7174-57c2-b8ab-8177fb48bcd4', 'bundler', 'qa/Gemfile.lock', '[]', 'pry-shell', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119'),
 (44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633865', 'cae749ee-bee8-5b18-8328-3196a26956f5', 'bundler', 'qa/Gemfile.lock', '[]', 'pry-rails', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119'),
(44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633866', 'b3e88674-7174-57c2-b8ab-8177fb48bcd6', 'bundler', 'qa/Gemfile.lock', '[]', 'yard', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119'),
 (44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633867', 'cae749ee-bee8-5b18-8328-3196a26956f7', 'bundler', 'qa/Gemfile.lock', '[]', 'zeitwerk', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119'),
 (44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633868', 'b3e88674-7174-57c2-b8ab-8177fb48bcd8', 'bundler', 'qa/Gemfile.lock', '[]', 'yard1', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119'),
 (44098500, 1083952445, 803, 614252, 55, '\x66326330613930313139303862383133653263336631323632393236336164383134636332633869', 'cae749ee-bee8-5b18-8328-3196a26956f9', 'bundler', 'qa/Gemfile.lock', '[]', 'zeitwerk2', 1, 5, '2023-11-24 14:38:58.155119', '2023-11-24 14:38:58.155119') ON CONFLICT ("uuid") DO UPDATE SET "project_id"=excluded."project_id","pipeline_id"=excluded."pipeline_id","component_id"=excluded."component_id","component_version_id"=excluded."component_version_id","source_id"=excluded."source_id","commit_sha"=excluded."commit_sha","package_manager"=excluded."package_manager","input_file_path"=excluded."input_file_path","licenses"=excluded."licenses","component_name"=excluded."component_name","vulnerability_count"=excluded."vulnerability_count","highest_severity"=excluded."highest_severity","updated_at"=excluded."updated_at" RETURNING id;

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 Zamir Martins

Merge request reports