Skip to content

Resolve "Enable VulnerabilityQuery Filtering by Dismissal Reason"

Gregory Havenga (is on PTO from 19 July to 30 July) requested to merge 411570-enable-vulnerabilityquery-filtering-by-dismissal-reason into master

What does this MR do and why?

This MR enables the filtering of the general vulnerability query with dismissal reasons using a lateral join between the vulnerability_reads table and the vulnerability_state_transitions table to get only the latest relevant record.

Query

I'm expecting we will need to put additional effort into optimising this query as it's currently reading a ridiculous amount of data to execute. This said, if additional filters are applied, the is significantly paired down, but my concern is that this query stands to grow slower with greater data volumes. Partitioning could work, and denormalisation might be a good idea.

SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
    JOIN LATERAL (
        SELECT
            "vulnerability_state_transitions".*
        FROM
            "vulnerability_state_transitions"
        WHERE
            "vulnerability_state_transitions"."dismissal_reason" = 0
            AND (vulnerability_state_transitions.vulnerability_id = vulnerability_reads.vulnerability_id)
        ORDER BY
            "vulnerability_state_transitions"."id" DESC
        LIMIT 1) subquery ON TRUE
WHERE
    "vulnerability_reads"."project_id" = 278964
    AND "vulnerability_reads"."state" = 2
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 101
Time: 177.425 ms  
  - planning: 0.543 ms  
  - execution: 176.882 ms  
    - I/O read: 0.000 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 250854 (~1.90 GiB) from the buffer pool  
  - reads: 0 from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/18719/commands/62145

With one additional filter (severity: critical)

https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/18719/commands/62155

Time: 47.255 ms  
  - planning: 0.559 ms  
  - execution: 46.696 ms  
    - I/O read: 24.151 ms  
    - I/O write: 0.000 ms  
  
Shared buffers:  
  - hits: 25113 (~196.20 MiB) from the buffer pool  
  - reads: 29 (~232.00 KiB) from the OS file cache, including disk I/O  
  - dirtied: 0  
  - writes: 0

How to set up and validate locally

  1. Configure a project with vulnerabilities
  2. Dismiss one with the "acceptable_risk" dismissal_reason
  3. Check that the following query returns it in the graphql explorer:
query {
  project(fullPath: "testing-group/security_reports") {
    vulnerabilities(dismissalReasons: [ACCEPTABLE_RISK]) {
      nodes {
        id
      }
    }
  }
}

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Related to #411570 (closed)

Edited by Gregory Havenga (is on PTO from 19 July to 30 July)

Merge request reports