POC: Find vulnerabilities on SBOM ingestion

What does this MR do and why?

• ::Sbom::SbomChangeWorker subscribes to ::Sbom::SbomIngestedEvent.
• Sbom::SbomChangeWorker calls Sbom::FindVulnerabilitiesService providing the pipeline_id in the event
• Sbom::FindVulnerabilitiesService calls ::Gitlab::VulnerabilityScanning::SbomScanner which in turn finds all occurrences mapping to the specified pipeline_id from the pm_occurrences table. Then it creates an array of Hashie::Mash containing component name, version and purl type and calls PackageAdvisories class to find which of these occurrences are actually affected and fetch the component data along with the advisories that are affecting them.

Future work

What is not in this MR is storing the findings.

Relates to

Draft: Add service to match SBOM components and... (!126954 - closed)

CVS on SBOM change (#464575) • Zamir Martins • 17.4

TODO

• Reduce the amount of data being fetched by narrowing down the columns being fetched.
• Add new specs and update the existing ones.

Production data

Average occurrences per pipeline

gitlabhq_dblab=# select AVG(count) from (select pipeline_id, count(*) as count from sbom_occurrences group by pipeline_id) temp_table;
         avg
----------------------
 450.7552445814277192
(1 row)

Occurrences per pipeline (Top 50)

gitlabhq_dblab=# select pipeline_id, count(*) as count from sbom_occurrences group by pipeline_id order by count DESC limit 50;
 pipeline_id | count
-------------+--------
  1277855757 | 108621
  1184525610 | 108043
             |  85009
  1329918131 |  63137
  1299496639 |  44517
  1264848515 |  43448
  1329751619 |  39542
   966397386 |  32097
  1212729974 |  24599
  1203990053 |  23609
   930890991 |  22766
  1314273384 |  20844
   840056690 |  18517
  1329086237 |  18135
  1319454547 |  18117
  1324744333 |  17863
  1327605444 |  17382
  1310959978 |  17059
  1324855050 |  17034
  1197399961 |  16815
   929589770 |  16544
  1329481955 |  16483
  1329112417 |  16220
  1209492697 |  16105
  1330395855 |  15678
  1330376930 |  15666
  1165363814 |  15651
  1140577291 |  14260
   954705448 |  14093
  1083521952 |  13408
  1322561482 |  13207
  1203436422 |  13192
   925716480 |  12849
  1318446429 |  12607
  1055596144 |  12140
  1328588954 |  12070
  1328698967 |  12059
  1304473286 |  11962
  1005537272 |  11838
  1330009830 |  11650
  1329893708 |  11641
  1264364818 |  11525
  1322178985 |  11466
  1213972211 |  11310
  1328700300 |  11303
  1208239958 |  11294
  1327557464 |  11178
  1329905067 |  10932
   957808850 |  10786
  1303753961 |  10072
(50 rows)

Affected packages per advisory (top 50)

gitlabhq_dblab=# select pm_advisory_id, count(*) as count from pm_affected_packages group by pm_advisory_id order by count DESC limit 50;
 pm_advisory_id | count
----------------+-------
        1049272 |  1657
        1473215 |  1528
        1036342 |  1442
        1059425 |  1310
        1059431 |  1307
        1059427 |  1305
        1059379 |  1304
        1402699 |  1116
        1184854 |  1112
        1351543 |  1089
        1035624 |  1057
        1035408 |  1056
        1035407 |  1056
        1035409 |  1056
        1695405 |  1018
        1695404 |  1018
        1036526 |   971
        1184855 |   931
        1351436 |   917
        1057715 |   898
        1490805 |   878
        1490807 |   877
        1490806 |   877
        1490808 |   877
        1360492 |   816
        1038249 |   780
        1359946 |   775
        1359928 |   775
        1359929 |   775
        1359947 |   770
        1359948 |   770
        1132435 |   736
        1201006 |   735
        1132436 |   731
        1058672 |   714
        1058809 |   710
        1058656 |   709
        1058305 |   708
        1523020 |   671
        1517725 |   665
        1074609 |   657
        1056808 |   640
        1057010 |   639
        1351377 |   620
        1061683 |   612
        1079881 |   611
        1683985 |   610
        1072954 |   576
        1254394 |   576
        1036436 |   574
(50 rows)

Average affected packages by advisory

gitlabhq_dblab=# select AVG(count) from (select pm_advisory_id, count(*) as count from pm_affected_packages group by pm_advisory_id) temp_table;
         avg
---------------------
 13.4815332960268607
(1 row)

DB Migration/Rollback

$ bundle exec rails db:migrate
main: == [advisory_lock_connection] object_id: 128120, pg_backend_pid: 85275
main: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: migrating
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0111s
main: -- index_exists?(:pm_affected_packages, [:purl_type, :package_name], {:name=>"index_purl_type_package_name_on_pm_affected_packages", :algorithm=>:concurrently})
main:    -> 0.0024s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- add_index(:pm_affected_packages, [:purl_type, :package_name], {:name=>"index_purl_type_package_name_on_pm_affected_packages", :algorithm=>:concurrently})
main:    -> 0.4807s
main: -- execute("RESET statement_timeout")
main:    -> 0.0004s
main: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: migrated (0.5118s)

main: == [advisory_lock_connection] object_id: 128120, pg_backend_pid: 85275
ci: == [advisory_lock_connection] object_id: 128420, pg_backend_pid: 85279
ci: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: migrating
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0005s
ci: -- index_exists?(:pm_affected_packages, [:purl_type, :package_name], {:name=>"index_purl_type_package_name_on_pm_affected_packages", :algorithm=>:concurrently})
ci:    -> 0.0028s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:pm_affected_packages, [:purl_type, :package_name], {:name=>"index_purl_type_package_name_on_pm_affected_packages", :algorithm=>:concurrently})
ci:    -> 0.0025s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: migrated (0.0219s)

$ bundle exec rails db:migrate:down:main VERSION=20240628141027
main: == [advisory_lock_connection] object_id: 127740, pg_backend_pid: 93484
main: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: reverting
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0102s
main: -- indexes(:pm_affected_packages)
main:    -> 0.0030s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:pm_affected_packages, {:algorithm=>:concurrently, :name=>"index_purl_type_package_name_on_pm_affected_packages"})
main:    -> 0.0146s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: reverted (0.0411s)

$ bundle exec rails db:migrate:down:ci VERSION=20240628141027
ci: == [advisory_lock_connection] object_id: 127740, pg_backend_pid: 93970
ci: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: reverting
ci: -- transaction_open?(nil)
ci:    -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci:    -> 0.0244s
ci: -- indexes(:pm_affected_packages)
ci:    -> 0.0038s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0003s
ci: -- remove_index(:pm_affected_packages, {:algorithm=>:concurrently, :name=>"index_purl_type_package_name_on_pm_affected_packages"})
ci:    -> 0.0016s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0003s
ci: == 20240628141027 CreateIndexPurlTypeAndPackageNameOnAffectedPackages: reverted (0.0512s)