Update vulnerability_reads trigger to set has_issues

What does this MR do and why?

Describe in detail what your merge request does and why.

Relates to: #386434 (closed)

We currently have postgres triggers on the vulnerability_issue_links table which set the has_issues column on a vulnerability_reads record when an issue link is created or updated. However, vulnerability_reads records are only created when the vulnerability becomes present on the default branch. If an issue link is created before the vulnerability becomes present on the default branch, then the vulnerability_issue_links triggers will do nothing since the vulnerability_reads record does not yet exist. To account for this case, we need to check if the vulnerability has issue links when the vulnerability_reads record is created, and set has_issues accordingly. vulnerability_reads records are created via postgres triggers on the vulnerabilities and vulnerability_occurrences tables.

Migrations

Up

$ bin/rails db:migrate:up:main VERSION=20230216142836
main: == 20230216142836 UpdateVulnerabilityReadsTriggerToSetHasIssue: migrating =====
main: -- execute("CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads()\n  RETURNS trigger\n  LANGUAGE plpgsql\nAS $$\nDECLARE\n  severity smallint;\n  state smallint;\n  report_type smallint;\n  resolved_on_default_branch boolean;\n  present_on_default_branch boolean;\n  namespace_id bigint;\n  has_issues boolean;\nBEGIN\n  IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN\n    RETURN NULL;\n  END IF;\n\n  IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN\n    RETURN NULL;\n  END IF;\n\n  SELECT\n    vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch, vulnerabilities.present_on_default_branch\n  INTO\n    severity, state, report_type, resolved_on_default_branch, present_on_default_branch\n  FROM\n    vulnerabilities\n  WHERE\n    vulnerabilities.id = NEW.vulnerability_id;\n\n  IF present_on_default_branch IS NOT true THEN\n    RETURN NULL;\n  END IF;\n\n  SELECT\n    projects.namespace_id\n  INTO\n    namespace_id\n  FROM\n    projects\n  WHERE\n    projects.id = NEW.project_id;\n\n  SELECT\n    EXISTS (SELECT 1 FROM vulnerability_issue_links WHERE vulnerability_issue_links.vulnerability_id = NEW.vulnerability_id)\n  INTO\n    has_issues;\n\n  INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, has_issues)\n    VALUES (NEW.vulnerability_id, namespace_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id', CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint), has_issues)\n    ON CONFLICT(vulnerability_id) DO NOTHING;\n  RETURN NULL;\nEND\n$$\n")
main:    -> 0.0029s
main: -- execute("CREATE OR REPLACE FUNCTION insert_vulnerability_reads_from_vulnerability()\n  RETURNS trigger\n  LANGUAGE plpgsql\nAS $$\nDECLARE\n  scanner_id bigint;\n  uuid uuid;\n  location_image text;\n  cluster_agent_id text;\n  casted_cluster_agent_id bigint;\n  namespace_id bigint;\n  has_issues boolean;\nBEGIN\n  SELECT\n    v_o.scanner_id, v_o.uuid, v_o.location->>'image', v_o.location->'kubernetes_resource'->>'agent_id', CAST(v_o.location->'kubernetes_resource'->>'agent_id' AS bigint), projects.namespace_id\n  INTO\n    scanner_id, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, namespace_id\n  FROM\n    vulnerability_occurrences v_o\n  INNER JOIN projects ON projects.id = v_o.project_id\n  WHERE\n    v_o.vulnerability_id = NEW.id\n  LIMIT 1;\n\n  SELECT\n    EXISTS (SELECT 1 FROM vulnerability_issue_links WHERE vulnerability_issue_links.vulnerability_id = NEW.id)\n  INTO\n    has_issues;\n\n  INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, has_issues)\n    VALUES (NEW.id, namespace_id, NEW.project_id, scanner_id, NEW.report_type, NEW.severity, NEW.state, NEW.resolved_on_default_branch, uuid::uuid, location_image, cluster_agent_id, casted_cluster_agent_id, has_issues)\n    ON CONFLICT(vulnerability_id) DO NOTHING;\n  RETURN NULL;\nEND\n$$\n")
main:    -> 0.0005s
main: == 20230216142836 UpdateVulnerabilityReadsTriggerToSetHasIssue: migrated (0.0078s) 

Down

$ bin/rails db:migrate:down:main VERSION=20230216142836
main: == 20230216142836 UpdateVulnerabilityReadsTriggerToSetHasIssue: reverting =====
main: -- execute("CREATE OR REPLACE FUNCTION insert_or_update_vulnerability_reads() RETURNS trigger\n    LANGUAGE plpgsql\n    AS $$\nDECLARE\n  severity smallint;\n  state smallint;\n  report_type smallint;\n  resolved_on_default_branch boolean;\n  present_on_default_branch boolean;\n  namespace_id bigint;\nBEGIN\n  IF (NEW.vulnerability_id IS NULL AND (TG_OP = 'INSERT' OR TG_OP = 'UPDATE')) THEN\n    RETURN NULL;\n  END IF;\n\n  IF (TG_OP = 'UPDATE' AND OLD.vulnerability_id IS NOT NULL AND NEW.vulnerability_id IS NOT NULL) THEN\n    RETURN NULL;\n  END IF;\n\n  SELECT\n    vulnerabilities.severity, vulnerabilities.state, vulnerabilities.report_type, vulnerabilities.resolved_on_default_branch, vulnerabilities.present_on_default_branch\n  INTO\n    severity, state, report_type, resolved_on_default_branch, present_on_default_branch\n  FROM\n     vulnerabilities\n  WHERE\n    vulnerabilities.id = NEW.vulnerability_id;\n\n  IF present_on_default_branch IS NOT true THEN\n    RETURN NULL;\n  END IF;\n\n  SELECT\n    projects.namespace_id\n  INTO\n    namespace_id\n  FROM\n    projects\n  WHERE\n    projects.id = NEW.project_id;\n\n  INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id)\n    VALUES (NEW.vulnerability_id, namespace_id, NEW.project_id, NEW.scanner_id, report_type, severity, state, resolved_on_default_branch, NEW.uuid::uuid, NEW.location->>'image', NEW.location->'kubernetes_resource'->>'agent_id', CAST(NEW.location->'kubernetes_resource'->>'agent_id' AS bigint))\n    ON CONFLICT(vulnerability_id) DO NOTHING;\n  RETURN NULL;\nEND\n$$;\n")
main:    -> 0.0024s
main: -- execute("CREATE OR REPLACE FUNCTION insert_vulnerability_reads_from_vulnerability() RETURNS trigger\n    LANGUAGE plpgsql\n    AS $$\nDECLARE\n  scanner_id bigint;\n  uuid uuid;\n  location_image text;\n  cluster_agent_id text;\n  casted_cluster_agent_id bigint;\n  namespace_id bigint;\nBEGIN\n  SELECT\n    v_o.scanner_id, v_o.uuid, v_o.location->>'image', v_o.location->'kubernetes_resource'->>'agent_id', CAST(v_o.location->'kubernetes_resource'->>'agent_id' AS bigint), projects.namespace_id\n  INTO\n    scanner_id, uuid, location_image, cluster_agent_id, casted_cluster_agent_id, namespace_id\n  FROM\n    vulnerability_occurrences v_o\n  INNER JOIN projects ON projects.id = v_o.project_id\n  WHERE\n    v_o.vulnerability_id = NEW.id\n  LIMIT 1;\n\n  INSERT INTO vulnerability_reads (vulnerability_id, namespace_id, project_id, scanner_id, report_type, severity, state, resolved_on_default_branch, uuid, location_image, cluster_agent_id, casted_cluster_agent_id)\n    VALUES (NEW.id, namespace_id, NEW.project_id, scanner_id, NEW.report_type, NEW.severity, NEW.state, NEW.resolved_on_default_branch, uuid::uuid, location_image, cluster_agent_id, casted_cluster_agent_id)\n    ON CONFLICT(vulnerability_id) DO NOTHING;\n  RETURN NULL;\nEND\n$$;\n")
main:    -> 0.0019s
main: == 20230216142836 UpdateVulnerabilityReadsTriggerToSetHasIssue: reverted (0.0085s) 

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

1. Ensure that you have an EE license

2. Setup gitlab runner

3. Create a repository and initialize it with a readme.

4. Clone the repository

5. Checkout a new branch

6. Add a new .gitlab-ci.yml that adds a security report. For example:

   $ cat .gitlab-ci.yml
   image: alpine:latest
   
   sast-spotbugs:
     script:
       - echo NOOP
     artifacts:
       reports:
         sast: samples/sast-spotbugs.json
   $ mkdir samples && curl -O https://gitlab.com/gitlab-examples/security/security-reports/-/raw/master/samples/sast-spotbugs.json
   $ cd ..
   $ git add .
   $ git commit -m 'Add spotbugs'
   $ git push -u -o merge_request.create origin <your_branch_name>

7. Go to the pipeline for the branch you pushed

8. Click on the security tab

9. Create an issue for one of the vulnerabilities

   

10. Merge the MR

11. Go to Security and Compliance -> Vulnerability Report

12. Use the activity filter to show only vulnerabilities which have issues

    

13. The vulnerability that you created an issue for should appear in the list when the filter is active (prior to this change, it would not).

    

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.