Skip to content

Add cluster_agent_id to vulnerability_reads

Sashi Kumar Kumaresan requested to merge sk/335326-add-cluster-id into master

What does this MR do and why?

Part of #335326 (closed)

!76640 (merged) introduced a conditional index on agent_id in location json column in vulnerability_occurrences. Ad a part of epic &6903 (closed), we are introducing vulnerability_reads table to store the ids for faster lookup of vulnerabilities.

!74733 (merged) added the vulnerability_reads table. This MR adds the cluster_agent_id column which is used to filter vulnerabilities(!76342 (merged))

The type of the column is string and not int because currently the agent_id is stored as string in location column in vulnerability_occurrences table.

cluster_agent_id will only be present for vulnerabilities with report_type cluster_image_scanning (7) and hence we are adding a condition for the index.

Migrate Up

== 20211216133107 AddClusterAgentIdToVulnerabilityReads: migrating ============
-- add_column(:vulnerability_reads, :cluster_agent_id, :text)
   -> 0.0023s
== 20211216133107 AddClusterAgentIdToVulnerabilityReads: migrated (0.0024s) ===

== 20211216134134 AddTextLimitToVulnerabilityReadsClusterAgentId: migrating ===
-- transaction_open?()
   -> 0.0000s
-- current_schema()
   -> 0.0002s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE vulnerability_reads\nADD CONSTRAINT check_a105eb825a\nCHECK ( char_length(cluster_agent_id) <= 10 )\nNOT VALID;\n")
   -> 0.0010s
-- current_schema()
   -> 0.0001s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- execute("ALTER TABLE vulnerability_reads VALIDATE CONSTRAINT check_a105eb825a;")
   -> 0.0007s
-- execute("RESET statement_timeout")
   -> 0.0004s
== 20211216134134 AddTextLimitToVulnerabilityReadsClusterAgentId: migrated (0.0136s) 

== 20211216135651 AddIndexToClusterAgentId: migrating =========================
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_reads, :cluster_agent_id, {:where=>"report_type = 7", :name=>"index_vulnerability_reads_on_cluster_agent_id", :algorithm=>:concurrently})
   -> 0.0041s
-- add_index(:vulnerability_reads, :cluster_agent_id, {:where=>"report_type = 7", :name=>"index_vulnerability_reads_on_cluster_agent_id", :algorithm=>:concurrently})
   -> 0.0027s
== 20211216135651 AddIndexToClusterAgentId: migrated (0.0094s) ================

Migrate Down

== 20211216135651 AddIndexToClusterAgentId: reverting =========================
-- transaction_open?()
   -> 0.0000s
-- indexes(:vulnerability_reads)
   -> 0.0035s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- remove_index(:vulnerability_reads, {:algorithm=>:concurrently, :name=>"index_vulnerability_reads_on_cluster_agent_id"})
   -> 0.0041s
-- execute("RESET statement_timeout")
   -> 0.0005s
== 20211216135651 AddIndexToClusterAgentId: reverted (0.0152s) ================

== 20211216134134 AddTextLimitToVulnerabilityReadsClusterAgentId: reverting ===
-- transaction_open?()
   -> 0.0000s
-- transaction_open?()
   -> 0.0000s
-- execute("ALTER TABLE vulnerability_reads\nDROP CONSTRAINT IF EXISTS check_a105eb825a\n")
   -> 0.0013s
== 20211216134134 AddTextLimitToVulnerabilityReadsClusterAgentId: reverted (0.0130s) 

== 20211216133107 AddClusterAgentIdToVulnerabilityReads: reverting ============
-- remove_column(:vulnerability_reads, :cluster_agent_id, :text)
   -> 0.0037s
== 20211216133107 AddClusterAgentIdToVulnerabilityReads: reverted (0.0081s) ===

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 Sashi Kumar Kumaresan

Merge request reports