Update indexes and scopes for agent_id/cluster_id in Finding
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 by Sashi Kumar Kumaresan