Report malware on the group dependency list JSON

Bala Kumarrequested to merge
feat/malware-field-group-controller into master

What does this MR do and why?

The group dependency list JSON doesn't report malware findings. Two separate bugs cause this:

  1. Sbom::AggregationsFinder collapses each component version to one representative occurrence (MIN(id)). If a malware (GLAM) finding sits on a different occurrence of the same component elsewhere in the group, preloading vulnerabilities on the representative never sees it.
  2. DependencyEntity#malware_field_enabled? checked request.try(:project) and returned false when that was absent. Group requests never carry a project, so the malware key was dropped from group JSON entirely.

Fix:

  • New Sbom::MalwareStatusPreloader (ee/app/models/sbom/malware_status_preloader.rb) resolves malware_status for a page. across_group looks across every occurrence of a component version in the group and its descendants; per_occurrence answers rows that already represent a single occurrence.
  • New Sbom::Occurrence.vulnerability_ids_by_component_version(namespace, component_version_ids) backs it with one grouped query.
  • New Vulnerability.with_vulnerability_read scope (preload(:vulnerability_read)).
  • New attr_writer :malware_status on Sbom::Occurrence, alongside the existing policy_dismissals writer. The preloader sets this computed boolean directly and leaves the vulnerabilities association alone, so occurrence.vulnerabilities keeps its normal meaning everywhere.
  • Groups::DependenciesController runs the preloader in the JSON branch of index, only when the user can read security resources on the group and the flag is on. Unflagged requests see no query count change. Filtering by project_ids drops the aggregation, so those rows take per_occurrence and agree with what the project dependency list reports for the same package.
  • DependencyEntity#malware_field_enabled? now gates on subject (request.try(:project) || request.try(:group)).

Feature flag

One flag, dependency_malware_detection — the same one the dependency list UI already reads for the badge and filter token. There is no separate backend field flag; !251948 (merged) folded that gating into this one and has merged.

Flag Type Default Gates
dependency_malware_detection beta off Backend malware field, plus the UI badge and filter token

The check goes through Group#dependency_malware_detection_feature_flag_enabled?, which walks up the namespace hierarchy. Enabling the flag on a group reaches its subgroups and projects.

Database review

Per page of the aggregated list, only when the flag is on and the user can read security resources on the group: one grouped lookup (query 1), then queries 2 and 3 repeated per batch of at most MalwareStatusPreloader::BATCH_SIZE (5,000) vulnerability ids. The heaviest page in group 9970 needs 14 batches; a component version already flagged is skipped, so in practice it is fewer.

The project_ids-filtered list is not aggregated and takes the per_occurrence path instead, which is a single preload of vulnerabilities and their reads.

Plans captured on Database Lab against gitlab-production-sec, group 9970 (gitlab-org), session 55454.

Two pages were measured. The typical page is the real AggregationsFinder first page. The worst-case page is the 20 component versions in the group with the most sbom_occurrences_vulnerabilities rows, the heaviest of which has 13,420 on its own.

Buffers rather than timings, since Database Lab is a thin clone with a cold cache and the timings below are dominated by first-touch I/O against it. One buffer is 8 KiB.

Typical first page Worst-case page
Occurrences scanned 568 1,997
Join rows produced 0 68,559
Rows returned 0 19
sbom_occurrences index scan 832 buffers (6.5 MiB) 3,120 buffers (24.4 MiB)
sbom_occurrences_vulnerabilities index only scan 2,272 buffers (17.8 MiB) 25,814 buffers (201.7 MiB)
Total 3,107 buffers (24.3 MiB) 28,937 buffers (226.1 MiB)
Execution time, indicative only 1.06 s 4.24 s

Both plans use the intended indexes with no sequential scans, so this is not an index-selection problem. It is volume: the worst-case page touches 226 MiB of buffers to return 19 rows.

Query 1 — grouped vulnerability ids per component version

SELECT sbom_occurrences.component_version_id,
       ARRAY_AGG(DISTINCT sbom_occurrences_vulnerabilities.vulnerability_id)
FROM sbom_occurrences
INNER JOIN sbom_occurrences_vulnerabilities
  ON sbom_occurrences_vulnerabilities.sbom_occurrence_id = sbom_occurrences.id
WHERE (sbom_occurrences.traversal_ids >= '{9970}' AND '{9971}' > sbom_occurrences.traversal_ids)
  AND sbom_occurrences.archived = FALSE
  AND sbom_occurrences.component_version_id IN (/* 20 ids, one page */)
GROUP BY sbom_occurrences.component_version_id;

Bounded by at most 20 component version ids per page (Sbom::AggregationsFinder::MAX_PAGE_SIZE), times the occurrences sharing them in the group.

Plan, typical page (1.06 s, 0 rows) — session 
 GroupAggregate  (cost=29.31..9893.20 rows=352 width=40) (actual time=1063.886..1063.888 rows=0 loops=1)
   Group Key: sbom_occurrences.component_version_id
   Buffers: shared hit=1694 read=1413 dirtied=89
   I/O Timings: read=1053.642 write=0.000
   ->  Incremental Sort  (cost=29.31..9887.04 rows=352 width=16) (actual time=1063.885..1063.886 rows=0 loops=1)
         Sort Key: sbom_occurrences.component_version_id, sbom_occurrences_vulnerabilities.vulnerability_id
         Presorted Key: sbom_occurrences.component_version_id
         ->  Nested Loop  (cost=1.26..9871.20 rows=352 width=16) (actual time=1063.857..1063.858 rows=0 loops=1)
               ->  Index Scan using index_unarchived_occurrences_on_version_id_and_traversal_ids on public.sbom_occurrences  (cost=0.69..675.92 rows=427 width=16) (actual time=6.221..770.083 rows=568 loops=1)
                     Index Searches: 20
                     Buffers: shared hit=81 read=751 dirtied=89
                     I/O Timings: read=764.076
               ->  Index Only Scan using i_sbom_occ_vulns_on_occ_id_vuln_id_and_project_id on public.sbom_occurrences_vulnerabilities  (cost=0.57..17.90 rows=363 width=16) (actual time=0.516..0.516 rows=0 loops=568)
                     Index Cond: (sbom_occurrences_vulnerabilities.sbom_occurrence_id = sbom_occurrences.id)
                     Heap Fetches: 0
                     Index Searches: 568
                     Buffers: shared hit=1610 read=662
                     I/O Timings: read=289.566
Plan, worst-case page (4.24 s, 68,559 join rows) — session 
 GroupAggregate  (cost=29.04..13387.92 rows=481 width=40) (actual time=1501.147..4238.398 rows=19 loops=1)
   Group Key: sbom_occurrences.component_version_id
   Buffers: shared hit=18859 read=10078 dirtied=3045
   WAL: records=3390 fpi=3045 bytes=23239495
   I/O Timings: read=4133.974 write=0.000
   ->  Incremental Sort  (cost=29.04..13379.50 rows=481 width=16) (actual time=1201.002..4230.987 rows=68559 loops=1)
         Sort Key: sbom_occurrences.component_version_id, sbom_occurrences_vulnerabilities.vulnerability_id
         Presorted Key: sbom_occurrences.component_version_id
         Full-sort Groups: 19  Sort Method: quicksort  Average Memory: 27kB  Peak Memory: 27kB
         Pre-sorted Groups: 19  Sort Method: quicksort  Average Memory: 723kB  Peak Memory: 804kB
         ->  Nested Loop  (cost=1.26..13357.86 rows=481 width=16) (actual time=14.068..4213.728 rows=68559 loops=1)
               ->  Index Scan using index_unarchived_occurrences_on_version_id_and_traversal_ids on public.sbom_occurrences  (cost=0.69..906.98 rows=583 width=16) (actual time=11.777..2035.020 rows=1997 loops=1)
                     Index Searches: 20
                     Buffers: shared hit=228 read=2892 dirtied=622
                     I/O Timings: read=2019.062
               ->  Index Only Scan using i_sbom_occ_vulns_on_occ_id_vuln_id_and_project_id on public.sbom_occurrences_vulnerabilities  (cost=0.57..17.73 rows=363 width=16) (actual time=0.343..1.087 rows=34.33 loops=1997)
                     Index Cond: (sbom_occurrences_vulnerabilities.sbom_occurrence_id = sbom_occurrences.id)
                     Heap Fetches: 33055
                     Index Searches: 1997
                     Buffers: shared hit=18628 read=7186 dirtied=2423
                     I/O Timings: read=2114.912

Query 2 — load the matched vulnerabilities

SELECT vulnerabilities.* FROM vulnerabilities WHERE vulnerabilities.id IN (/* ids from query 1 */);

Index vulnerabilities_pkey. Measured on a 200-id sample: 272 buffers (2.1 MiB) for 200 rows, or 1.36 buffers per row. Session.

Batching caps this statement at 5,000 ids, so the sampled plan is the same shape as what runs, an order of magnitude apart rather than the ~350x it would have been against a single unbounded list.

Query 3 — preload the vulnerability reads

SELECT vulnerability_reads.* FROM vulnerability_reads WHERE vulnerability_reads.vulnerability_id IN (/* same ids */);

Index index_vulnerability_reads_on_vulnerability_id (unique). Measured on the same 200-id sample: 230 buffers (1.8 MiB) for 200 rows, or 1.15 buffers per row, with Index Searches: 9. Session. Batched identically to query 2.

What this means

The tail does not look good. The typical page is unremarkable. The heaviest page in group 9970 reads 226 MiB of buffers to return 19 rows, before queries 2 and 3 add an estimated 1.3 GiB on top. Batching bounds each statement and caps what is resident in memory, but it does not reduce that total: the same rows are still read, now across 14 pairs of queries instead of two. A group whose dependency list sits in the p99 to p100 band of this distribution will look far closer to the worst-case column than the typical one, and gitlab-org is a real customer-shaped namespace, not a synthetic one.

To be precise about what was measured: the worst-case page is the top 20 component versions by sbom_occurrences_vulnerabilities rows, so it bounds the tail rather than being a p99 point estimate. I did not sample the distribution, so I cannot give a p99 figure, only the ceiling.

Things a reviewer should weigh, none of which are index problems.

1. The worst-case page reads 226 MiB of buffers to return 19 rows. The dominant step is the index only scan on sbom_occurrences_vulnerabilities at 201.7 MiB. That is below the 512 MiB single-step figure the explain plan guidelines give as an example of "a lot", but it is the same order of magnitude, for one page of a user-facing list. Even the typical page reads 24.3 MiB to return nothing at all.

Given the above, there is an alternate plan to rework this rather than tune it. !252166 (merged) removes all three queries by deriving malware status from the advisory data directly, which makes the tail flat instead of trying to make it cheaper. We first want to go with this approach to unblock the UI and customer zero trials as we have the feature flag and in the next week implement 252166.

Alternate implementation plan

!252166 (merged) removes these three queries entirely by deriving malware status from pm_malware_affected_packages keyed on (purl_type, name, version), which loads no vulnerabilities at all and reduces the page to a single indexed lookup bounded by 20 package names. Given the measurements above, that is the stronger option and this MR may be closed in its favour.

How to set up and validate locally

Local testing steps

Scenario: a group with two projects, both holding an SBOM occurrence of the same component version. Only project B's occurrence has a vulnerability whose vulnerability_read.identifier_names starts with GLAM-. The aggregation picks project A's occurrence as the representative.

  1. Build the data in rails console (or use the seeded demo group https://gitlab.com/gitlab-org/govern/threat-insights-demos/verification-projects/bala-test-group/):

    group     = Group.find_by_full_path('your-group')
project_a = create(:project, group: group)
project_b = create(:project, group: group)

cv = Sbom::ComponentVersion.first
Sbom::Occurrence.create!(project: project_a, component_version: cv, ...)
occurrence_b = Sbom::Occurrence.create!(project: project_b, component_version: cv, ...)

vuln = Vulnerability.find(<some vuln in project_b>)
vuln.vulnerability_read.update!(identifier_names: ['GLAM-2026-0001'])
Sbom::OccurrencesVulnerability.create!(occurrence: occurrence_b, vulnerability: vuln)

  2. Feature.enable(:dependency_malware_detection, group)

  3. Request the JSON:

    curl --header "PRIVATE-TOKEN: <token>" \
  "http://gdk.test:3000/groups/<group-path>/-/dependencies.json" \
  | jq '[.dependencies[] | {name, malware}]'

Before, on master: the malware key is absent from every entry, because malware_field_enabled? returns false whenever the request carries no project, which is always true for group requests.

After: the key is present, and the shared component reports true.

Fixing only the gate without the group-wide preload would report false here, since the representative occurrence in project A has no GLAM finding of its own — both halves of the fix are needed.

GLAM finding in group Flag enabled malware in JSON
yes yes true
no yes false
yes no omitted
no no omitted

Merge order

This MR must merge before !251903. Both target master and carry the same shared model and preloader changes (Sbom::MalwareStatusPreloader, Sbom::Occurrence.vulnerability_ids_by_component_version, Vulnerability.with_vulnerability_read), byte for byte, so those hunks drop out of the GraphQL MR when it rebases.

Related

Edited by Bala Kumar

Merge request reports