Skip to content

Unique Index for Vulnerability LInks

What does this MR do?

This MR adds a unique key index for vulnerability links to prevent duplicate insertion

Screenshots or Screencasts (strongly suggested)

How to setup and validate locally (strongly suggested)

  1. Start on master branch.
  2. Enable the finding link storage
    Feature.enable(:vulnerability_finding_replace_metadata)
  3. Import https://gitlab.com/gitlab-examples/security/security-reports into your local environment.
  4. Run the pipeline twice.
  5. Find a vulnerability that has links, usually a Dependency Scanning vulnerability will have some.
  6. Confirm that there are 2 links for the vulnerability finding
    Vulnerability.find(<vulnerability_id>).finding.finding_links.count
  7. Clear findings table
    Vulnerabilities::FindingLink.all.each { |l| l.destroy }
  8. Go to the 330882-dependency-scanning-vulnerability-reports-a-lot-of-duplicate-links-index branch and run the database migration (rails db:migrate)
  9. Run the pipeline twice.
  10. Confirm that there is only 1 link for the vulnerability finding
    Vulnerability.find(<vulnerability_id>).finding.finding_links.count

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Database

Migration

== 20210811193033 AddUniqueIndexToVulnerabilityFindingLinks: migrating ========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_finding_links, [:vulnerability_occurrence_id, :name, :url], {:unique=>true, :name=>"finding_link_name_url_idx", :algorithm=>:concurrently})
   -> 0.0039s
-- execute("SET statement_timeout TO 0")
   -> 0.0005s
-- add_index(:vulnerability_finding_links, [:vulnerability_occurrence_id, :name, :url], {:unique=>true, :name=>"finding_link_name_url_idx", :algorithm=>:concurrently})
   -> 0.0033s
-- execute("RESET statement_timeout")
   -> 0.0004s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_finding_links, [:vulnerability_occurrence_id, :url], {:unique=>true, :where=>"name is null", :name=>"finding_link_url_idx", :algorithm=>:concurrently})
   -> 0.0017s
-- add_index(:vulnerability_finding_links, [:vulnerability_occurrence_id, :url], {:unique=>true, :where=>"name is null", :name=>"finding_link_url_idx", :algorithm=>:concurrently})
   -> 0.0020s
== 20210811193033 AddUniqueIndexToVulnerabilityFindingLinks: migrated (0.0162s) 

Rollback

== 20210811193033 AddUniqueIndexToVulnerabilityFindingLinks: reverting ========
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_finding_links, [:vulnerability_occurrence_id, :name, :url], {:name=>"finding_link_name_url_idx", :algorithm=>:concurrently})
   -> 0.0031s
-- execute("SET statement_timeout TO 0")
   -> 0.0006s
-- remove_index(:vulnerability_finding_links, {:name=>"finding_link_name_url_idx", :algorithm=>:concurrently, :column=>[:vulnerability_occurrence_id, :name, :url]})
   -> 0.0082s
-- execute("RESET statement_timeout")
   -> 0.0006s
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_finding_links, [:vulnerability_occurrence_id, :url], {:name=>"finding_link_url_idx", :algorithm=>:concurrently})
   -> 0.0015s
-- remove_index(:vulnerability_finding_links, {:name=>"finding_link_url_idx", :algorithm=>:concurrently, :column=>[:vulnerability_occurrence_id, :url]})
   -> 0.0032s
== 20210811193033 AddUniqueIndexToVulnerabilityFindingLinks: reverted (0.0248s) 

Related to #330882 (closed)

Edited by Jonathan Schafer

Merge request reports