Update indexes and scopes for agent_id/cluster_id in Finding (!77073) · Merge requests · GitLab.org / GitLab

Alan (Maciej) Paruszewski requested to merge 342662-modify-index-and-scopes-for-kubernetes-resource-vulns into master Dec 17, 2021

What does this MR do and why?

In !74300 (merged) and based on the updates in https://gitlab.com/gitlab-org/security-products/security-report-schemas/-/blob/f0032f4a5f094d3aed93499dd07cc9e198375bfe/src/cluster-image-scanning-report-format.json#L57-104 and cluster-image-scanning analyzer we have changed where we are storing cluster_id and agent_id in Finding#location. We were always properly parsing this data, however we are incorrectly querying for findings which results in empty response in GraphQL API.

This MR fixes that problem.

Migrations

⋊> ~/D/g/gitlab on master ⨯ env VERBOSE=true bundle exec rake db:migrate:down VERSION=20211217120000
== 20211217120000 ModifyKubernetesResourceLocationIndexToVulnerabilityOccurrences: reverting 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrences, "(location -> 'cluster_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_cluster_id", :algorithm=>:concurrently})
   -> 0.0156s
-- execute("SET statement_timeout TO 0")
   -> 0.0022s
-- add_index(:vulnerability_occurrences, "(location -> 'cluster_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_cluster_id", :algorithm=>:concurrently})
   -> 0.0437s
-- execute("RESET statement_timeout")
   -> 0.0017s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrences, "(location -> 'agent_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_agent_id", :algorithm=>:concurrently})
   -> 0.0132s
-- add_index(:vulnerability_occurrences, "(location -> 'agent_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_agent_id", :algorithm=>:concurrently})
   -> 0.0211s
-- transaction_open?()
   -> 0.0000s
-- indexes(:vulnerability_occurrences)
   -> 0.0117s
-- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_location_k8s_cluster_id"})
   -> 0.0083s
-- transaction_open?()
   -> 0.0000s
-- indexes(:vulnerability_occurrences)
   -> 0.0100s
-- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_location_k8s_agent_id"})
   -> 0.0056s
== 20211217120000 ModifyKubernetesResourceLocationIndexToVulnerabilityOccurrences: reverted (0.1508s)

⋊> ~/D/g/gitlab on master ⨯ env VERBOSE=true bundle exec rake db:migrate:up VERSION=20211217120000
== 20211217120000 ModifyKubernetesResourceLocationIndexToVulnerabilityOccurrences: migrating 
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrences, "(location -> 'kubernetes_resource' -> 'cluster_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_k8s_cluster_id", :algorithm=>:concurrently})
   -> 0.0123s
-- execute("SET statement_timeout TO 0")
   -> 0.0015s
-- add_index(:vulnerability_occurrences, "(location -> 'kubernetes_resource' -> 'cluster_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_k8s_cluster_id", :algorithm=>:concurrently})
   -> 0.0401s
-- execute("RESET statement_timeout")
   -> 0.0017s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrences, "(location -> 'kubernetes_resource' -> 'agent_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_k8s_agent_id", :algorithm=>:concurrently})
   -> 0.0094s
-- add_index(:vulnerability_occurrences, "(location -> 'kubernetes_resource' -> 'agent_id')", {:using=>"GIN", :where=>"report_type = 7", :name=>"index_vulnerability_occurrences_on_location_k8s_agent_id", :algorithm=>:concurrently})
   -> 0.0222s
-- transaction_open?()
   -> 0.0000s
-- indexes(:vulnerability_occurrences)
   -> 0.0094s
-- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_location_cluster_id"})
   -> 0.0075s
-- transaction_open?()
   -> 0.0000s
-- indexes(:vulnerability_occurrences)
   -> 0.0112s
-- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_location_agent_id"})
   -> 0.0048s
== 20211217120000 ModifyKubernetesResourceLocationIndexToVulnerabilityOccurrences: migrated (0.1368s)

Query Analysis

Agent ID

    SELECT
        vulnerabilities.* 
    FROM
        vulnerabilities 
    INNER JOIN
        vulnerability_occurrences 
            ON vulnerability_occurrences.vulnerability_id = vulnerabilities.id 
    WHERE
        vulnerabilities.project_id = 32199269 
        AND vulnerability_occurrences.report_type = 7 
        AND (
            vulnerability_occurrences.location -> 'kubernetes_resource' -> 'agent_id' ?| array['3533']
        )

Explain Result

 Nested Loop  (cost=7.95..176.97 rows=1 width=367) (actual time=0.758..17.678 rows=1726 loops=1)
   Buffers: shared hit=15383
   I/O Timings: read=0.000 write=0.000
   ->  Bitmap Heap Scan on public.vulnerability_occurrences  (cost=7.51..59.40 rows=34 width=8) (actual time=0.736..8.357 rows=3452 loops=1)
         Buffers: shared hit=1575
         I/O Timings: read=0.000 write=0.000
         ->  Bitmap Index Scan using index_vulnerability_occurrences_on_location_k8s_agent_id  (cost=0.00..7.50 rows=34 width=0) (actual time=0.525..0.526 rows=3452 loops=1)
               Index Cond: (((vulnerability_occurrences.location -> 'kubernetes_resource'::text) -> 'agent_id'::text) ?| '{3533}'::text[])
               Buffers: shared hit=3
               I/O Timings: read=0.000 write=0.000
   ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.44..3.46 rows=1 width=367) (actual time=0.002..0.002 rows=0 loops=3452)
         Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
         Filter: (vulnerabilities.project_id = 32199269)
         Rows Removed by Filter: 0
         Buffers: shared hit=13808
         I/O Timings: read=0.000 write=0.000

Cluster ID

    SELECT
        vulnerabilities.* 
    FROM
        vulnerabilities 
    INNER JOIN
        vulnerability_occurrences 
            ON vulnerability_occurrences.vulnerability_id = vulnerabilities.id 
    WHERE
        vulnerabilities.project_id = 32199269 
        AND vulnerability_occurrences.report_type = 7 
        AND (
            vulnerability_occurrences.location -> 'kubernetes_resource' -> 'cluster_id' ?| array['153813']
        )

Explain Result

 Nested Loop  (cost=7.95..176.97 rows=1 width=367) (actual time=0.800..25.108 rows=1726 loops=1)
   Buffers: shared hit=15383
   I/O Timings: read=0.000 write=0.000
   ->  Bitmap Heap Scan on public.vulnerability_occurrences  (cost=7.51..59.40 rows=34 width=8) (actual time=0.773..12.373 rows=3452 loops=1)
         Buffers: shared hit=1575
         I/O Timings: read=0.000 write=0.000
         ->  Bitmap Index Scan using index_vulnerability_occurrences_on_location_k8s_cluster_id  (cost=0.00..7.50 rows=34 width=0) (actual time=0.584..0.585 rows=3452 loops=1)
               Index Cond: (((vulnerability_occurrences.location -> 'kubernetes_resource'::text) -> 'cluster_id'::text) ?| '{153813}'::text[])
               Buffers: shared hit=3
               I/O Timings: read=0.000 write=0.000
   ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.44..3.46 rows=1 width=367) (actual time=0.003..0.003 rows=0 loops=3452)
         Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
         Filter: (vulnerabilities.project_id = 32199269)
         Rows Removed by Filter: 0
         Buffers: shared hit=13808
         I/O Timings: read=0.000 write=0.000

How to set up and validate locally

Import new project from https://gitlab.com/gitlab-org/protect/demos/sandbox/test-cis-with-agentid
Run pipeline
Go to GraphQL Explorer and query. You should see results in the response:

query {
  project(fullPath: "gitlab-org/protect/demos/sandbox/test-cis-with-agentid") {
    vulnerabilities(clusterAgentId: ["gid://gitlab/Clusters::Agent/3533"]) {
      nodes {
        id
        location {
          ... on VulnerabilityLocationClusterImageScanning {
            dependency {
              version
            }
            image
            kubernetesResource {
              agent {
                id
              }
              clusterId
              containerName
            }
          }
        }
      }
    }
  }
}

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.

Related to #342662 (closed)

Edited Dec 29, 2021 by Sashi Kumar Kumaresan

Update indexes and scopes for agent_id/cluster_id in Finding