POC: Add database schema for malware advisories and SBOM vulnerability engine integration for CI & CVS

Bala Kumarrequested to merge
poc/malware-advisories-schema-589555 into master

Summary

This is a proof-of-concept MR for the database schema and SBOM vulnerability engine integration proposed in #589555 (closed).

It introduces two new tables — pm_malware_advisories and pm_malware_affected_packages — modelled after the existing pm_advisories / pm_affected_packages pair, and wires them into the existing SBOM vulnerability scanning engine with minimal new code.

The exported advisory format this schema is based on is defined in #587162 (comment 3064070424).

For a full example of how the exported JSON maps to the DB columns, see the JSON to DB Mapping comment.

Database Schema

pm_malware_advisories

Stores one row per malware advisory ingested from GLAM.

Column Type Constraints Notes
id bigint PK
advisory_xid text NOT NULL, max 36 e.g. GLAM-2025-04-00005
source_xid smallint NOT NULL enum — initially only glam: 0
title text max 256
description text max 8192
published_date date NOT NULL
withdrawn_date date nullable presence = advisory was retracted
identifiers jsonb NOT NULL, default [] array of {type, name, value, url}
urls text[] NOT NULL, default [] optional reference URLs from the GLAM v3 schema
created_at datetime NOT NULL
updated_at datetime NOT NULL

Indexes:

  • Unique on (advisory_xid, source_xid) — used for upsert on sync

pm_malware_affected_packages

One row per (advisory, purl_type, package_name) combination. Holds the version range used for matching against sbom_occurrences.

Column Type Constraints Notes
id bigint PK
pm_malware_advisory_id bigint NOT NULL, FK → pm_malware_advisories.id CASCADE
purl_type smallint NOT NULL same enum as pm_affected_packages
package_name text NOT NULL, max 256
affected_range text NOT NULL, max 512 semver NPM-style, e.g. >=0, >=1.0.0 <2.0.0
created_at datetime NOT NULL
updated_at datetime NOT NULL

Indexes:

  • Unique on (pm_malware_advisory_id, purl_type, package_name) — used for upsert on sync

identifiers JSON schema

Each element in the identifiers jsonb array conforms to:

{
  "type":  "GLAM",
  "name":  "GLAM-2025-04-00005",
  "value": "GLAM-2025-04-00005",
  "url":   "https://gitlab.com/gitlab-org/secure/vulnerability-research/pocs/malwares/-/blob/main/advisories/2025/04/00/GLAD-2025-04-00005.json"
}

Secondary identifiers (e.g. MAL-) can be appended as additional array elements.

Entity Relationship

pm_malware_advisories
  id PK
  advisory_xid (unique with source_xid)
  source_xid
  title
  description
  published_date
  withdrawn_date
  identifiers (jsonb)
  urls (text[])
        |
        | 1 : N
        |
pm_malware_affected_packages
  id PK
  pm_malware_advisory_id FK
  purl_type
  package_name
  affected_range

Key Design Decisions vs pm_advisories

pm_advisories pm_malware_advisories Reason
cvss_v2 / cvss_v3 ✅ ❌ Malware is always critical; severity set in Rails
solution / fixed_versions ✅ ❌ No "upgrade to fix" concept for malware
distro_version ✅ (OS packages) ❌ Ecosystem-level only, never OS-distro specific
withdrawn_date ❌ ✅ Replaces hard-deletion; triggers downstream resolution
Identifiers CVE, Gemnasium… GLAM, MAL… Different identifier namespace
source_xid enum glad, trivy-db glam Separate enum, extensible

JSON to DB Mapping Example

The following shows how a PMDB exported advisory JSON maps to the two database tables.

Exported PMDB advisory JSON (input to Rails sync)

{
  "malware_advisory": {
    "id": "GLAM-2025-04-00005",
    "source": "glam",
    "title": "Malicious code in @baywa-re-lusy/nsemea-address-lib-poc (npm)",
    "description": "\n---\n_-= Per source ....",
    "published_date": "2025-04-29",
    "identifiers": [
      {
        "type":  "GLAM",
        "name":  "GLAM-2025-04-00005",
        "value": "GLAM-2025-04-00005",
        "url":   "https://gitlab.com/gitlab-org/secure/vulnerability-research/pocs/malwares/-/blob/main/advisories/2025/04/00/GLAD-2025-04-00005.json"
      },
      {
        "type":  "MAL",
        "name":  "MAL-2025-3512",
        "value": "MAL-2025-3512",
        "url":   ""
      }
    ],
    "urls": [
      "https://github.com/advisories/GHSA-xxxx-yyyy-zzzz"
    ]
  },
  "packages": [
    {
      "name":           "@baywa-re-lusy/nsemea-address-lib-poc",
      "purl_type":      "npm",
      "affected_range": ">=0"
    }
  ]
}

Field mapping to DB columns

JSON field DB table DB column Stored value
malware_advisory.id pm_malware_advisories advisory_xid "GLAM-2025-04-00005"
malware_advisory.source pm_malware_advisories source_xid 0 (enum glam)
malware_advisory.title pm_malware_advisories title "Malicious code in ..."
malware_advisory.description pm_malware_advisories description "\n---\n_-= Per source ..."
malware_advisory.published_date pm_malware_advisories published_date 2025-04-29
malware_advisory.withdrawn (absent) pm_malware_advisories withdrawn_date NULL
malware_advisory.identifiers pm_malware_advisories identifiers jsonb array (both GLAM + MAL objects)
malware_advisory.urls (optional) pm_malware_advisories urls text[] of reference URLs; empty array when the field is absent
packages[0].name pm_malware_affected_packages package_name "@baywa-re-lusy/nsemea-address-lib-poc"
packages[0].purl_type pm_malware_affected_packages purl_type 5 (enum npm)
packages[0].affected_range pm_malware_affected_packages affected_range ">=0"

Note on source_xid: The source field in the JSON ("glam") maps to the source_xid smallint enum defined in the AR model as { glam: 0 }. The integer 0 is stored in the DB; Rails exposes it as the symbol :glam / string "glam" in application code. This is intentionally a separate enum from pm_advisories (which uses glad: 1, trivy-db: 2) since malware advisories come from a different source pipeline. The source_xid value is also forwarded to the Advisory value object in MalwareAdvisoryScanner and used downstream for Snowplow telemetry and error tracking, the same way "glad" is used for regular advisories.

SBOM Vulnerability Engine Integration

The integration reuses the entire existing scanning engine unchanged. Only a thin adapter layer is added.

How pm_advisories maps to sbom_occurrences (existing flow)

The existing flow for pm_advisories works as follows:

  1. A new advisory is synced into pm_advisories + pm_affected_packages.
  2. PackageMetadata::IngestedAdvisoryEvent is published with the advisory_id.
  3. PackageMetadata::GlobalAdvisoryScanWorker (subscribed via EventStore) picks it up.
  4. It calls PackageMetadata::AdvisoryScanService → Gitlab::VulnerabilityScanning::AdvisoryScanner.
  5. For each pm_affected_package, Sbom::PossiblyAffectedOccurrencesFinder queries sbom_occurrences by (purl_type, package_name) — matching via Sbom::Component (libraries) or Sbom::SourcePackage (OS packages).
  6. For each matching occurrence, AdvisoryUtils#occurrence_is_affected? checks whether occurrence.version falls within affected_range using SemverDialects.
  7. If affected, BuildFindingMapService + CreateVulnerabilityService create the vulnerability.

New malware advisory flow (this MR)

The malware flow is identical, with a parallel set of thin adapter classes:

Existing (pm_advisories) New (pm_malware_advisories)
IngestedAdvisoryEvent IngestedMalwareAdvisoryEvent
GlobalAdvisoryScanWorker GlobalMalwareAdvisoryScanWorker
AdvisoryScanService MalwareAdvisoryScanService
AdvisoryScanner MalwareAdvisoryScanner

PossiblyAffectedOccurrencesFinder, AdvisoryUtils#occurrence_is_affected?, BuildFindingMapService, and CreateVulnerabilityService are reused without modification.

The only behavioural difference in MalwareAdvisoryScanner vs AdvisoryScanner:

  • Passes distro: nil to occurrence_is_affected? (malware is never OS-distro specific).
  • No CVSS, no solution text — the Advisory value object is built with cvss_v2: nil, cvss_v3: nil, solution: nil.

Flow Diagram

flowchart TD
    A[GLAM advisory synced\ninto pm_malware_advisories\n+ pm_malware_affected_packages] --> B[IngestedMalwareAdvisoryEvent\npublished with advisory_id]

    B --> C[GlobalMalwareAdvisoryScanWorker\nhandle_event]

    C --> AD{Malware protection\nadd-on available?}
    AD -- No --> AE[Skip & stop\nadd-on not active]
    AD -- Yes --> D{Advisory found?}

    D -- No --> E[Log error & stop]
    D -- Yes --> F[MalwareAdvisoryScanService.execute]

    F --> G[MalwareAdvisoryScanner.execute]

    G --> H[For each\npm_malware_affected_package]

    H --> I[Build Advisory value object\nxid, title, description,\nidentifiers, source_xid\ncvss=nil, solution=nil]

    H --> J[PossiblyAffectedOccurrencesFinder\npurl_type + package_name]

    J --> K[Lookup Sbom::Component\nor Sbom::SourcePackage]

    K --> L[Fetch sbom_occurrences\nin batches of 100]

    L --> DED{ExistingDsVulnerabilityChecker\ncovered? - active DS vuln\nalready exists?}

    DED -- Yes --> SKIP[Skip occurrence\navoid duplicate]
    DED -- No --> M{occurrence_is_affected?\nSemverDialects range check\ndistro=nil}

    M -- No --> N[Skip occurrence]
    M -- Yes --> O[PossiblyAffectedComponent\n.from_sbom_occurrence]

    O --> P[BuildFindingMapService.execute\nadvisory + component + pipeline\n+ project + scanner]

    P --> Q[CreateVulnerabilityService.execute\nbulk upsert vulnerabilities]

    style AE fill:#f90,color:#fff
    style E fill:#f66,color:#fff
    style Q fill:#0a0,color:#fff
    style N fill:#aaa,color:#fff
    style SKIP fill:#aaa,color:#fff

TODO: The add-on availability check in GlobalMalwareAdvisoryScanWorker is not yet implemented. See the TODO comment in the worker for details.

Deduplication: How Duplicate Vulnerabilities Are Avoided

GlobalMalwareAdvisoryScanWorker and GlobalAdvisoryScanWorker run independently via the EventStore with no sequencing guarantee. A malware advisory could be ingested before or after a regular advisory for the same package, risking duplicate vulnerabilities.

MalwareAdvisoryScanner handles this via ExistingDsVulnerabilityChecker, an inner class that issues a single CTE SQL query per (project, batch) joining vulnerability_occurrences on (name, version, file_path, project_id), filtered to active and unresolved dependency_scanning vulnerabilities. Results are stored in a Set for O(1) per-occurrence lookups — no N+1 queries.

# MalwareAdvisoryScanner#bulk_vulnerability_ingestion
ds_checker = ExistingDsVulnerabilityChecker.new(project_occurrences, project)

project_occurrences.filter_map do |occurrence|
  next if ds_checker.covered?(occurrence)  # ← skip if regular DS vuln exists
  next unless affected_occurrence?(...)
  ...
end

The with_resolution(false) filter ensures that if a regular advisory vulnerability was resolved or dismissed, a malware vulnerability can still be created for the same package.

Scenario Result
Regular advisory ingested before malware advisory (GlobalAdvisoryScanWorker ran first) ds_checker.covered? → true → skipped
Regular advisory vulnerability exists from a previous pipeline run ds_checker.covered? → true → skipped
Regular advisory vulnerability was resolved or dismissed with_resolution(false) → false → malware vuln created
No regular advisory covers the package ds_checker.covered? → false → malware vuln created normally

NOTE: ExistingDsVulnerabilityChecker will be refactored into a shared Gitlab::VulnerabilityScanning::ExistingVulnerabilityChecker class (accepting a report_type: parameter) when the malware CI pipeline integration MR lands on top of this one.

Files Changed

Database

  • db/migrate/20260309000001_create_pm_malware_advisories.rb
  • db/migrate/20260309000002_create_pm_malware_affected_packages.rb
  • db/docs/pm_malware_advisories.yml
  • db/docs/pm_malware_affected_packages.yml

Models

  • ee/app/models/package_metadata/malware_advisory.rb
  • ee/app/models/package_metadata/malware_affected_package.rb
  • ee/app/validators/json_schemas/pm_malware_advisory_identifiers.json

SBOM Engine Integration

  • app/events/package_metadata/ingested_malware_advisory_event.rb
  • ee/lib/gitlab/vulnerability_scanning/malware_advisory_scanner.rb
  • ee/app/services/package_metadata/malware_advisory_scan_service.rb
  • ee/app/workers/package_metadata/global_malware_advisory_scan_worker.rb
  • ee/lib/ee/gitlab/event_store.rb (wires worker to event)

Tests & Factories

  • ee/spec/factories/package_metadata/pm_malware_advisories.rb
  • ee/spec/factories/package_metadata/pm_malware_affected_packages.rb
  • ee/spec/models/package_metadata/malware_advisory_spec.rb
  • ee/spec/models/package_metadata/malware_affected_package_spec.rb
  • ee/spec/lib/gitlab/vulnerability_scanning/malware_advisory_scanner_spec.rb
  • ee/spec/services/package_metadata/malware_advisory_scan_service_spec.rb
  • ee/spec/workers/package_metadata/global_malware_advisory_scan_worker_spec.rb

Closes #589555 (closed)

Edited by Bala Kumar

Merge request reports