Skip to content

Add severity sorting for sbom_occurrences

Zamir Martins requested to merge add_severity_sorting_for_sbom into master

What does this MR do and why?

Add severity sorting for sbom_occurrences so this resolver can have parity with the existing project level features.

EE: true

Related issue: #426126 (closed)

Sorted by SEVERITY_ASC

query {
  project(fullPath:"top-group/project-2"){
    dependencies(sort: SEVERITY_ASC, first:1){
      nodes{
        name
        packager
        version
        id
      }
    }
  }
}
response 
{
  "data": {
    "project": {
      "dependencies": {
        "nodes": [
          {
            "name": "sync",
            "packager": "BUNDLER",
            "version": "0.5.0",
            "id": "gid://gitlab/Sbom::Occurrence/14761"
          }
        ]
      }
    }
  }
}
$ Sbom::Occurrence.find(14761).slice(:highest_severity, :vulnerability_count)
  Sbom::Occurrence Load (0.3ms)  SELECT "sbom_occurrences"."id", "sbom_occurrences"."created_at", "sbom_occurrences"."updated_at", "sbom_occurrences"."component_version_id", "sbom_occurrences"."project_id", "sbom_occurrences"."pipeline_id", "sbom_occurrences"."source_id", "sbom_occurrences"."commit_sha", "sbom_occurrences"."component_id", "sbom_occurrences"."uuid", "sbom_occurrences"."package_manager", "sbom_occurrences"."component_name", "sbom_occurrences"."input_file_path", "sbom_occurrences"."spdx_identifiers", "sbom_occurrences"."licenses", "sbom_occurrences"."highest_severity", "sbom_occurrences"."vulnerability_count" FROM "sbom_occurrences" WHERE "sbom_occurrences"."id" = 14761 LIMIT 1 /*application:console,db_config_name:main,console_hostname:Zamirs-MBP-2,console_username:zamir,line:(pry):6:in `__pry__'*/
=> {"highest_severity"=>nil, "vulnerability_count"=>0}

Sorted by SEVERITY_DESC

query {
  project(fullPath:"top-group/project-2"){
    dependencies(sort: SEVERITY_DESC, first:1){
      nodes{
        name
        packager
        version
        id
      }
    }
  }
}
response 
{
  "data": {
    "project": {
      "dependencies": {
        "nodes": [
          {
            "name": "rugged",
            "packager": "BUNDLER",
            "version": "1.5.1",
            "id": "gid://gitlab/Sbom::Occurrence/14708"
          }
        ]
      }
    }
  }
}
$ Sbom::Occurrence.find(14708).slice(:highest_severity, :vulnerability_count)
  Sbom::Occurrence Load (0.7ms)  SELECT "sbom_occurrences"."id", "sbom_occurrences"."created_at", "sbom_occurrences"."updated_at", "sbom_occurrences"."component_version_id", "sbom_occurrences"."project_id", "sbom_occurrences"."pipeline_id", "sbom_occurrences"."source_id", "sbom_occurrences"."commit_sha", "sbom_occurrences"."component_id", "sbom_occurrences"."uuid", "sbom_occurrences"."package_manager", "sbom_occurrences"."component_name", "sbom_occurrences"."input_file_path", "sbom_occurrences"."spdx_identifiers", "sbom_occurrences"."licenses", "sbom_occurrences"."highest_severity", "sbom_occurrences"."vulnerability_count" FROM "sbom_occurrences" WHERE "sbom_occurrences"."id" = 14708 LIMIT 1 /*application:console,db_config_name:main,console_hostname:Zamirs-MBP-2,console_username:zamir,line:(pry):7:in `__pry__'*/
=> {"highest_severity"=>"critical", "vulnerability_count"=>1}

Query plan

Link to query plan

SELECT "sbom_occurrences"."id", "sbom_occurrences"."created_at", "sbom_occurrences"."updated_at", "sbom_occurrences"."component_version_id", "sbom_occurrences"."project_id", "sbom_occurrences"."pipeline_id", "sbom_occurrences"."source_id", "sbom_occurrences"."commit_sha", "sbom_occurrences"."component_id", "sbom_occurrences"."uuid", "sbom_occurrences"."package_manager", "sbom_occurrences"."component_name", "sbom_occurrences"."input_file_path","sbom_occurrences"."licenses", "sbom_occurrences"."highest_severity"
FROM "sbom_occurrences"
WHERE "sbom_occurrences"."project_id" = 45218763
ORDER BY "sbom_occurrences"."highest_severity" DESC NULLS LAST
LIMIT 100
OFFSET 0;

Link to query plan

SELECT "sbom_occurrences"."id", "sbom_occurrences"."created_at", "sbom_occurrences"."updated_at", "sbom_occurrences"."component_version_id", "sbom_occurrences"."project_id", "sbom_occurrences"."pipeline_id", "sbom_occurrences"."source_id", "sbom_occurrences"."commit_sha", "sbom_occurrences"."component_id", "sbom_occurrences"."uuid", "sbom_occurrences"."package_manager", "sbom_occurrences"."component_name", "sbom_occurrences"."input_file_path","sbom_occurrences"."licenses", "sbom_occurrences"."highest_severity"
FROM "sbom_occurrences"
WHERE "sbom_occurrences"."project_id" = 45218763
ORDER BY "sbom_occurrences"."highest_severity" ASC NULLS FIRST
LIMIT 100
OFFSET 0;

Migration

$ bundle exec rails db:migrate:redo:main VERSION=20231122114135
main: == [advisory_lock_connection] object_id: 182280, pg_backend_pid: 53667
main: == 20231122114135 AddIndexOnSbomOccurrencesHighestSeverity: reverting =========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0814s
main: -- indexes(:sbom_occurrences)
main:    -> 0.0045s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:sbom_occurrences, {:algorithm=>:concurrently, :name=>"index_sbom_occurrences_on_highest_severity"})
main:    -> 0.0018s
main: -- execute("RESET statement_timeout")
main:    -> 0.0002s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0005s
main: -- index_exists?(:sbom_occurrences, :project_id, {:name=>"index_sbom_occurrences_on_project_id", :algorithm=>:concurrently})
main:    -> 0.0042s
main: -- add_index(:sbom_occurrences, :project_id, {:name=>"index_sbom_occurrences_on_project_id", :algorithm=>:concurrently})
main:    -> 0.0037s
main: == 20231122114135 AddIndexOnSbomOccurrencesHighestSeverity: reverted (0.1114s)

main: == [advisory_lock_connection] object_id: 182280, pg_backend_pid: 53667
main: == [advisory_lock_connection] object_id: 213140, pg_backend_pid: 53938
main: == 20231122114135 AddIndexOnSbomOccurrencesHighestSeverity: migrating =========
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0009s
main: -- index_exists?(:sbom_occurrences, [:project_id, :highest_severity], {:order=>{:highest_severity=>"DESC NULLS LAST"}, :name=>"index_sbom_occurrences_on_highest_severity", :algorithm=>:concurrently})
main:    -> 0.0151s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0003s
main: -- add_index(:sbom_occurrences, [:project_id, :highest_severity], {:order=>{:highest_severity=>"DESC NULLS LAST"}, :name=>"index_sbom_occurrences_on_highest_severity", :algorithm=>:concurrently})
main:    -> 0.0050s
main: -- execute("RESET statement_timeout")
main:    -> 0.0026s
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0018s
main: -- indexes(:sbom_occurrences)
main:    -> 0.0122s
main: -- remove_index(:sbom_occurrences, {:algorithm=>:concurrently, :name=>"index_sbom_occurrences_on_project_id"})
main:    -> 0.0040s
main: == 20231122114135 AddIndexOnSbomOccurrencesHighestSeverity: migrated (0.0628s)

main: == [advisory_lock_connection] object_id: 213140, pg_backend_pid: 53938

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