Add sorting for the dependencies_resolver (!116976) · Merge requests · GitLab.org / GitLab

Zamir Martins requested to merge add_sorting_for_dependencies_resolver into master Apr 06, 2023

What does this MR do and why?

Add sorting for the dependencies_resolver. It allows sorting by component name and package name. Severity was left to a future iteration as currently there isn't an association between occurrences and vulnerability.

Related issue: #389607 (closed)

Queries

Order by name, asc https://console.postgres.ai/shared/9525b585-becc-4353-8adb-dd05382869ce

Click to expand

SELECT
    sbom_occurrences.id AS t0_r0,
    sbom_occurrences.created_at AS t0_r1,
    sbom_occurrences.updated_at AS t0_r2,
    sbom_occurrences.component_version_id AS t0_r3,
    sbom_occurrences.project_id AS t0_r4,
    sbom_occurrences.pipeline_id AS t0_r5,
    sbom_occurrences.source_id AS t0_r6,
    sbom_occurrences.commit_sha AS t0_r7,
    sbom_occurrences.component_id AS t0_r8,
    sbom_occurrences.uuid AS t0_r9,
    sbom_components.id AS t1_r0,
    sbom_components.created_at AS t1_r1,
    sbom_components.updated_at AS t1_r2,
    sbom_components.component_type AS t1_r3,
    sbom_components.name AS t1_r4,
    sbom_components.purl_type AS t1_r5
FROM
    sbom_occurrences
    LEFT JOIN sbom_components ON sbom_components.id = sbom_occurrences.component_id
WHERE
    sbom_occurrences.project_id = 278964
ORDER BY
    sbom_components.name ASC NULLS LAST,
    sbom_occurrences.id ASC;

 Gather Merge  (cost=10768.32..11078.20 rows=2656 width=174) (actual time=1532.466..1539.021 rows=3230 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=32303 read=3812 dirtied=2100
   I/O Timings: read=4070.033 write=0.000
   ->  Sort  (cost=9768.29..9771.61 rows=1328 width=174) (actual time=1523.104..1523.219 rows=1077 loops=3)
         Sort Key: sbom_components.name, sbom_occurrences.id
         Sort Method: quicksort  Memory: 350kB
         Buffers: shared hit=32303 read=3812 dirtied=2100
         I/O Timings: read=4070.033 write=0.000
         ->  Nested Loop Left Join  (cost=78.17..9699.40 rows=1328 width=174) (actual time=95.592..1516.265 rows=1077 loops=3)
               Buffers: shared hit=32267 read=3812 dirtied=2100
               I/O Timings: read=4070.033 write=0.000
               ->  Parallel Bitmap Heap Scan on public.sbom_occurrences  (cost=77.75..5941.77 rows=1328 width=121) (actual time=80.072..532.685 rows=1077 loops=3)
                     Buffers: shared hit=3 read=1229 dirtied=54
                     I/O Timings: read=1418.984 write=0.000
                     ->  Bitmap Index Scan using index_sbom_occurrences_on_project_id_and_id  (cost=0.00..76.95 rows=3186 width=0) (actual time=84.444..84.445 rows=3230 loops=1)
                           Index Cond: (sbom_occurrences.project_id = 278964)
                           Buffers: shared hit=3 read=99
                           I/O Timings: read=82.775 write=0.000
               ->  Index Scan using sbom_components_pkey on public.sbom_components  (cost=0.42..2.83 rows=1 width=53) (actual time=0.909..0.909 rows=1 loops=3230)
                     Index Cond: (sbom_components.id = sbom_occurrences.component_id)
                     Buffers: shared hit=30781 read=2583 dirtied=718
                     I/O Timings: read=2651.049 write=0.000

Time: 1.542 s  
  - planning: 2.225 ms  
  - execution: 1.539 s  
    - I/O read: 4.070 s  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 32303 (~252.40 MiB) from the buffer pool  
  - reads: 3812 (~29.80 MiB) from the OS file cache, including disk I/O  
  - dirtied: 2100 (~16.40 MiB)  
  - writes: 0

Order by name, desc https://console.postgres.ai/shared/5c0ca08c-1ca1-424a-84e7-5012291fe394

Click to expand

SELECT
    sbom_occurrences.id AS t0_r0,
    sbom_occurrences.created_at AS t0_r1,
    sbom_occurrences.updated_at AS t0_r2,
    sbom_occurrences.component_version_id AS t0_r3,
    sbom_occurrences.project_id AS t0_r4,
    sbom_occurrences.pipeline_id AS t0_r5,
    sbom_occurrences.source_id AS t0_r6,
    sbom_occurrences.commit_sha AS t0_r7,
    sbom_occurrences.component_id AS t0_r8,
    sbom_occurrences.uuid AS t0_r9,
    sbom_components.id AS t1_r0,
    sbom_components.created_at AS t1_r1,
    sbom_components.updated_at AS t1_r2,
    sbom_components.component_type AS t1_r3,
    sbom_components.name AS t1_r4,
    sbom_components.purl_type AS t1_r5
FROM
    sbom_occurrences
    LEFT JOIN sbom_components ON sbom_components.id = sbom_occurrences.component_id
WHERE
    sbom_occurrences.project_id = 278964
ORDER BY
    sbom_components.name DESC NULLS LAST,
    sbom_occurrences.id ASC;

 Gather Merge  (cost=10768.32..11078.20 rows=2656 width=174) (actual time=63.707..69.352 rows=3230 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   Buffers: shared hit=32316 read=3812 dirtied=2099
   I/O Timings: read=111.659 write=0.000
   ->  Sort  (cost=9768.29..9771.61 rows=1328 width=174) (actual time=58.950..59.027 rows=1077 loops=3)
         Sort Key: sbom_components.name DESC NULLS LAST, sbom_occurrences.id
         Sort Method: quicksort  Memory: 322kB
         Buffers: shared hit=32316 read=3812 dirtied=2099
         I/O Timings: read=111.659 write=0.000
         ->  Nested Loop Left Join  (cost=78.17..9699.40 rows=1328 width=174) (actual time=2.010..56.455 rows=1077 loops=3)
               Buffers: shared hit=32280 read=3812 dirtied=2099
               I/O Timings: read=111.659 write=0.000
               ->  Parallel Bitmap Heap Scan on public.sbom_occurrences  (cost=77.75..5941.77 rows=1328 width=121) (actual time=1.675..15.410 rows=1077 loops=3)
                     Buffers: shared hit=3 read=1229 dirtied=54
                     I/O Timings: read=39.047 write=0.000
                     ->  Bitmap Index Scan using index_sbom_occurrences_on_project_id_and_id  (cost=0.00..76.95 rows=3186 width=0) (actual time=4.337..4.337 rows=3230 loops=1)
                           Index Cond: (sbom_occurrences.project_id = 278964)
                           Buffers: shared hit=3 read=99
                           I/O Timings: read=3.258 write=0.000
               ->  Index Scan using sbom_components_pkey on public.sbom_components  (cost=0.42..2.83 rows=1 width=53) (actual time=0.037..0.037 rows=1 loops=3230)
                     Index Cond: (sbom_components.id = sbom_occurrences.component_id)
                     Buffers: shared hit=30794 read=2583 dirtied=718
                     I/O Timings: read=72.612 write=0.000

Time: 72.150 ms
  - planning: 2.503 ms
  - execution: 69.647 ms
    - I/O read: 111.659 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 32316 (~252.50 MiB) from the buffer pool
  - reads: 3812 (~29.80 MiB) from the OS file cache, including disk I/O
  - dirtied: 2099 (~16.40 MiB)
  - writes: 0

Order by packager, asc https://console.postgres.ai/shared/b45098bd-a31c-404f-bc15-fd660b968bde

Click to expand

SELECT
    sbom_occurrences.id AS t0_r0,
    sbom_occurrences.created_at AS t0_r1,
    sbom_occurrences.updated_at AS t0_r2,
    sbom_occurrences.component_version_id AS t0_r3,
    sbom_occurrences.project_id AS t0_r4,
    sbom_occurrences.pipeline_id AS t0_r5,
    sbom_occurrences.source_id AS t0_r6,
    sbom_occurrences.commit_sha AS t0_r7,
    sbom_occurrences.component_id AS t0_r8,
    sbom_occurrences.uuid AS t0_r9,
    sbom_sources.id AS t1_r0,
    sbom_sources.created_at AS t1_r1,
    sbom_sources.updated_at AS t1_r2,
    sbom_sources.source_type AS t1_r3,
    sbom_sources.source AS t1_r4
FROM
    sbom_occurrences
    LEFT JOIN sbom_sources ON sbom_sources.id = sbom_occurrences.source_id
WHERE
    sbom_occurrences.project_id = 278964
ORDER BY
    sbom_sources.source -> 'package_manager' -> 'name' ASC NULLS LAST,
    sbom_occurrences.id ASC;

 Sort  (cost=5094.93..5102.90 rows=3186 width=211) (actual time=9.482..9.652 rows=3230 loops=1)
   Sort Key: (((sbom_sources.source -> 'package_manager'::text) -> 'name'::text)), sbom_occurrences.id
   Sort Method: quicksort  Memory: 954kB
   Buffers: shared hit=1689 read=2 dirtied=1
   I/O Timings: read=1.278 write=0.000
   ->  Hash Left Join  (cost=44.56..4909.55 rows=3186 width=211) (actual time=1.767..7.338 rows=3230 loops=1)
         Hash Cond: (sbom_occurrences.source_id = sbom_sources.id)
         Buffers: shared hit=1681 read=2 dirtied=1
         I/O Timings: read=1.278 write=0.000
         ->  Index Scan using index_sbom_occurrences_on_project_id_and_id on public.sbom_occurrences  (cost=0.56..4841.25 rows=3186 width=121) (actual time=0.083..4.487 rows=3230 loops=1)
               Index Cond: (sbom_occurrences.project_id = 278964)
               Buffers: shared hit=1681
               I/O Timings: read=0.000 write=0.000
         ->  Hash  (cost=32.25..32.25 rows=940 width=58) (actual time=1.661..1.662 rows=6 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               Buffers: shared read=2 dirtied=1
               I/O Timings: read=1.278 write=0.000
               ->  Index Scan using sbom_sources_pkey on public.sbom_sources  (cost=0.15..32.25 rows=940 width=58) (actual time=1.449..1.649 rows=6 loops=1)
                     Buffers: shared read=2 dirtied=1
                     I/O Timings: read=1.278 write=0.000

Time: 10.520 ms
  - planning: 0.227 ms
  - execution: 10.293 ms
    - I/O read: 1.278 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1689 (~13.20 MiB) from the buffer pool
  - reads: 2 (~16.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 1 (~8.00 KiB)
  - writes: 0

Order by packager, desc https://console.postgres.ai/shared/433e99b5-71c0-414f-b952-4dc6764a566d

Click to expand

SELECT
    sbom_occurrences.id AS t0_r0,
    sbom_occurrences.created_at AS t0_r1,
    sbom_occurrences.updated_at AS t0_r2,
    sbom_occurrences.component_version_id AS t0_r3,
    sbom_occurrences.project_id AS t0_r4,
    sbom_occurrences.pipeline_id AS t0_r5,
    sbom_occurrences.source_id AS t0_r6,
    sbom_occurrences.commit_sha AS t0_r7,
    sbom_occurrences.component_id AS t0_r8,
    sbom_occurrences.uuid AS t0_r9,
    sbom_sources.id AS t1_r0,
    sbom_sources.created_at AS t1_r1,
    sbom_sources.updated_at AS t1_r2,
    sbom_sources.source_type AS t1_r3,
    sbom_sources.source AS t1_r4
FROM
    sbom_occurrences
    LEFT JOIN sbom_sources ON sbom_sources.id = sbom_occurrences.source_id
WHERE
    sbom_occurrences.project_id = 278964
ORDER BY
    sbom_sources.source -> 'package_manager' -> 'name' DESC NULLS LAST,
    sbom_occurrences.id ASC;

 Sort  (cost=5094.93..5102.90 rows=3186 width=211) (actual time=7.557..7.717 rows=3230 loops=1)
   Sort Key: (((sbom_sources.source -> 'package_manager'::text) -> 'name'::text)) DESC NULLS LAST, sbom_occurrences.id
   Sort Method: quicksort  Memory: 954kB
   Buffers: shared hit=1691
   I/O Timings: read=0.000 write=0.000
   ->  Hash Left Join  (cost=44.56..4909.55 rows=3186 width=211) (actual time=0.111..5.428 rows=3230 loops=1)
         Hash Cond: (sbom_occurrences.source_id = sbom_sources.id)
         Buffers: shared hit=1683
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using index_sbom_occurrences_on_project_id_and_id on public.sbom_occurrences  (cost=0.56..4841.25 rows=3186 width=121) (actual time=0.059..4.238 rows=3230 loops=1)
               Index Cond: (sbom_occurrences.project_id = 278964)
               Buffers: shared hit=1681
               I/O Timings: read=0.000 write=0.000
         ->  Hash  (cost=32.25..32.25 rows=940 width=58) (actual time=0.029..0.030 rows=6 loops=1)
               Buckets: 1024  Batches: 1  Memory Usage: 9kB
               Buffers: shared hit=2
               I/O Timings: read=0.000 write=0.000
               ->  Index Scan using sbom_sources_pkey on public.sbom_sources  (cost=0.15..32.25 rows=940 width=58) (actual time=0.018..0.022 rows=6 loops=1)
                     Buffers: shared hit=2
                     I/O Timings: read=0.000 write=0.000

Time: 8.578 ms
  - planning: 0.253 ms
  - execution: 8.325 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 1691 (~13.20 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

How to set up and validate locally

Create a new project with the following files:

.gitlab-ci.yml:

dependency_scanning:
  script:
    - echo "dependency_scanning"
  artifacts:
    reports:
      cyclonedx: gl-sbom-gem-bundler.cdx.json

gl-sbom-gem-bundler.cdx.json:

{
  "bomFormat": "CycloneDX",
  "specVersion": "1.4",
  "serialNumber": "urn:uuid:c05292f0-b65b-403f-8abf-4f3fa29292db",
  "version": 1,
  "metadata": {
    "timestamp": "2023-04-05T15:42:07Z",
    "tools": [
      {
        "vendor": "GitLab",
        "name": "Gemnasium",
        "version": "3.13.3"
      }
    ],
    "authors": [
      {
        "name": "GitLab",
        "email": "support@gitlab.com"
      }
    ],
    "properties": [
      {
        "name": "gitlab:dependency_scanning:input_file",
        "value": "Gemfile.lock"
      },
      {
        "name": "gitlab:dependency_scanning:package_manager",
        "value": "bundler"
      }
    ]
  },
  "components": [
    {
      "name": "From-Sbom-gem",
      "version": "3.2.1",
      "purl": "pkg:gem/From-Sbom-gem@3.2.1",
      "type": "library",
      "bom-ref": "pkg:gem/From-Sbom-gem@3.2.1"
    },
    {
      "name": "Another-Sbom-gem",
      "version": "3.2.2",
      "purl": "pkg:gem/Another-Sbom-gem@3.2.2",
      "type": "library",
      "bom-ref": "pkg:gem/Another-Sbom-gem@3.2.2"
    }
  ]
}

Merge those files into the default branch and make sure that a pipeline is triggered with the recent change.
Through graphql explorer (i.e., http://<GITLAB_HOST>/-/graphql-explorer) query the following endpoint and compare the results:

query {
  project(fullPath:<PROJECT_FULL_PATH>){
    dependencies(sort: NAME_ASC){
      nodes{
        name
        version
      }
    }
  }
}

Note Currently occurrences doesn't have source set. Therefore both PACKAGER_ASC and PACKAGER_DESC can only be tested when source is manually created via rails console.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

I have evaluated the MR acceptance checklist for this MR.

Edited Apr 07, 2023 by Brian Williams

Add sorting for the dependencies_resolver

What does this MR do and why?

Queries

How to set up and validate locally

MR acceptance checklist

Merge request reports