Vulnerabilities scope to filter by scanner ID (!55909) · Merge requests · GitLab.org / GitLab

Jonathan Schafer requested to merge 293843-expand-filtering-functionality-of-vulnerabilities-graphql-endpoint-to-support-vendor into master Mar 05, 2021

What does this MR do?

This MR adds the capability to filter Vulnerabilities by scanner ID. This MR is related to #293843 (closed).

Screenshots (strongly suggested)

Database

New Query:

explain SELECT “vulnerabilities”.* FROM “vulnerabilities”
INNER JOIN “vulnerability_occurrences”
  ON “vulnerability_occurrences”.“vulnerability_id” = “vulnerabilities”.“id”
WHERE “vulnerability_occurrences”.“scanner_id” = 1

Summary (cold cache):

Time: 122.682 ms
  - planning: 2.624 ms
  - execution: 120.058 ms
    - I/O read: 118.816 ms
    - I/O write: N/A

Shared buffers:
  - hits: 142 (~1.10 MiB) from the buffer pool
  - reads: 89 (~712.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Execution Plan (cold cache):

 Nested Loop  (cost=0.86..605.92 rows=130 width=281) (actual time=10.967..119.955 rows=45 loops=1)
   Buffers: shared hit=142 read=89
   I/O Timings: read=118.816
   ->  Index Scan using index_vulnerability_occurrences_on_scanner_id on public.vulnerability_occurrences  (cost=0.43..157.42 rows=130 width=8) (actual time=3.832..57.098 rows=45 loops=1)
         Index Cond: (vulnerability_occurrences.scanner_id = 1)
         Buffers: shared hit=3 read=48
         I/O Timings: read=56.723
   ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.43..3.45 rows=1 width=281) (actual time=1.393..1.393 rows=1 loops=45)
         Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
         Buffers: shared hit=139 read=41
         I/O Timings: read=62.094

Summary (warm cache):

Time: 1.122 ms
  - planning: 0.644 ms
  - execution: 0.478 ms
    - I/O read: N/A
    - I/O write: N/A

Shared buffers:
  - hits: 231 (~1.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Execution Plan (warm cache):

 Nested Loop  (cost=0.86..605.92 rows=130 width=281) (actual time=0.071..0.405 rows=45 loops=1)
   Buffers: shared hit=231
   ->  Index Scan using index_vulnerability_occurrences_on_scanner_id on public.vulnerability_occurrences  (cost=0.43..157.42 rows=130 width=8) (actual time=0.059..0.189 rows=45 loops=1)
         Index Cond: (vulnerability_occurrences.scanner_id = 1)
         Buffers: shared hit=51
   ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.43..3.45 rows=1 width=281) (actual time=0.004..0.004 rows=1 loops=45)
         Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
         Buffers: shared hit=180

New query with additional filters:

explain SELECT “vulnerabilities”.* FROM “vulnerabilities”
INNER JOIN “vulnerability_occurrences”
  ON “vulnerability_occurrences”.“vulnerability_id” = “vulnerabilities”.“id”
WHERE “vulnerability_occurrences”.“scanner_id” = 1
  AND “vulnerabilities”.“severity” IN (5, 6, 7)
  AND “vulnerabilities”.“state” IN (0, 1)

Summary (cold cache):

Time: 146.203 ms
  - planning: 3.735 ms
  - execution: 142.468 ms
    - I/O read: 140.349 ms
    - I/O write: N/A

Shared buffers:
  - hits: 138 (~1.10 MiB) from the buffer pool
  - reads: 90 (~720.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Execution Plan (cold cache):

 Nested Loop  (cost=0.86..606.73 rows=37 width=281) (actual time=23.726..142.295 rows=40 loops=1)
   Buffers: shared hit=138 read=90
   I/O Timings: read=140.349
   ->  Index Scan using index_vulnerability_occurrences_on_scanner_id on public.vulnerability_occurrences  (cost=0.43..157.42 rows=130 width=8) (actual time=3.940..64.304 rows=45 loops=1)
         Index Cond: (vulnerability_occurrences.scanner_id = 1)
         Buffers: shared hit=3 read=48
         I/O Timings: read=63.709
   ->  Index Scan using tmp_index_on_vulnerabilities_non_dismissed on public.vulnerabilities  (cost=0.43..3.46 rows=1 width=281) (actual time=1.726..1.726 rows=1 loops=45)
         Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
         Filter: ((vulnerabilities.state = ANY ('{0,1}'::integer[])) AND (vulnerabilities.severity = ANY ('{5,6,7}'::integer[])))
         Rows Removed by Filter: 0
         Buffers: shared hit=135 read=42
         I/O Timings: read=76.639

Summary (warm cache):

Time: 1.162 ms
  - planning: 0.677 ms
  - execution: 0.485 ms
    - I/O read: N/A
    - I/O write: N/A

Shared buffers:
  - hits: 228 (~1.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Execution Plan (warm cache):

 Nested Loop  (cost=0.86..606.73 rows=37 width=281) (actual time=0.139..0.419 rows=40 loops=1)
   Buffers: shared hit=228
   ->  Index Scan using index_vulnerability_occurrences_on_scanner_id on public.vulnerability_occurrences  (cost=0.43..157.42 rows=130 width=8) (actual time=0.104..0.208 rows=45 loops=1)
         Index Cond: (vulnerability_occurrences.scanner_id = 1)
         Buffers: shared hit=51
   ->  Index Scan using tmp_index_on_vulnerabilities_non_dismissed on public.vulnerabilities  (cost=0.43..3.46 rows=1 width=281) (actual time=0.004..0.004 rows=1 loops=45)
         Index Cond: (vulnerabilities.id = vulnerability_occurrences.vulnerability_id)
         Filter: ((vulnerabilities.state = ANY ('{0,1}'::integer[])) AND (vulnerabilities.severity = ANY ('{5,6,7}'::integer[])))
         Rows Removed by Filter: 0
         Buffers: shared hit=177

Existing query for comparison:

explain SELECT “vulnerabilities”.* FROM “vulnerabilities”
WHERE “vulnerabilities”.“severity” IN (5, 6, 7)
  AND “vulnerabilities”.“state” IN (0, 1)

Full query:

explain SELECT “vulnerabilities”.* FROM “vulnerabilities”
INNER JOIN “vulnerability_occurrences”
  ON “vulnerability_occurrences”.“vulnerability_id” = “vulnerabilities”.“id”
WHERE “vulnerabilities”.“project_id” = 278964
  AND “vulnerabilities”.“report_type” IN (0, 3)
  AND “vulnerability_occurrences”.“scanner_id” IN (1, 2)
  AND “vulnerabilities”.“resolved_on_default_branch” = TRUE
  AND (EXISTS (SELECT 1 FROM “vulnerability_issue_links”
                 WHERE “vulnerability_issue_links”.“vulnerability_id” = “vulnerabilities”.“id”))
ORDER BY “vulnerabilities”.“severity” DESC, “vulnerabilities”.“id” DESC

Summary (cold cache):

Time: 1.453 s
  - planning: 4.844 ms
  - execution: 1.448 s
    - I/O read: 1.425 s
    - I/O write: N/A

Shared buffers:
  - hits: 167 (~1.30 MiB) from the buffer pool
  - reads: 767 (~6.00 MiB) from the OS file cache, including disk I/O
  - dirtied: 10 (~80.00 KiB)
  - writes: 0

Execution Plan (cold cache):

 Sort  (cost=531.53..531.54 rows=1 width=281) (actual time=1447.443..1447.448 rows=11 loops=1)
   Sort Key: vulnerabilities.severity DESC, vulnerabilities.id DESC
   Sort Method: quicksort  Memory: 27kB
   Buffers: shared hit=167 read=767 dirtied=10
   I/O Timings: read=1425.002
   ->  Nested Loop  (cost=456.55..531.52 rows=1 width=281) (actual time=1373.956..1447.368 rows=11 loops=1)
         Buffers: shared hit=161 read=767 dirtied=10
         I/O Timings: read=1425.002
         ->  Hash Join  (cost=456.11..528.17 rows=1 width=16) (actual time=1366.706..1369.114 rows=16 loops=1)
               Hash Cond: (vulnerability_issue_links.vulnerability_id = vulnerability_occurrences.vulnerability_id)
               Buffers: shared hit=134 read=729 dirtied=10
               I/O Timings: read=1347.334
               ->  HashAggregate  (cost=138.62..191.02 rows=5240 width=8) (actual time=46.104..47.669 rows=5257 loops=1)
                     Group Key: vulnerability_issue_links.vulnerability_id
                     Buffers: shared hit=124 read=33 dirtied=7
                     I/O Timings: read=37.015
                     ->  Index Only Scan using idx_vulnerability_issue_links_on_vulnerability_id_and_issue_id on public.vulnerability_issue_links  (cost=0.28..125.50 rows=5248 width=8) (actual time=0.015..43.209 rows=5266 loops=1)
                           Heap Fetches: 446
                           Buffers: shared hit=124 read=33 dirtied=7
                           I/O Timings: read=37.015
               ->  Hash  (cost=314.25..314.25 rows=259 width=8) (actual time=1320.346..1320.347 rows=558 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 30kB
                     Buffers: shared hit=10 read=696 dirtied=3
                     I/O Timings: read=1310.319
                     ->  Index Scan using index_vulnerability_occurrences_on_scanner_id on public.vulnerability_occurrences  (cost=0.43..314.25 rows=259 width=8) (actual time=6.437..1318.767 rows=734 loops=1)
                           Index Cond: (vulnerability_occurrences.scanner_id = ANY ('{1,2}'::bigint[]))
                           Buffers: shared hit=10 read=696 dirtied=3
                           I/O Timings: read=1310.319
         ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.43..3.34 rows=1 width=281) (actual time=4.886..4.886 rows=1 loops=16)
               Index Cond: (vulnerabilities.id = vulnerability_issue_links.vulnerability_id)
               Filter: (vulnerabilities.resolved_on_default_branch AND (vulnerabilities.report_type = ANY ('{0,3}'::integer[])) AND (vulnerabilities.project_id = 278964))
               Rows Removed by Filter: 0
               Buffers: shared hit=27 read=38
               I/O Timings: read=77.668

Summary (warm cache):

Time: 9.488 ms
  - planning: 2.126 ms
  - execution: 7.362 ms
    - I/O read: N/A
    - I/O write: N/A

Shared buffers:
  - hits: 934 (~7.30 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Execution Plan (warm cache):

 Sort  (cost=531.53..531.54 rows=1 width=281) (actual time=6.973..6.976 rows=11 loops=1)
   Sort Key: vulnerabilities.severity DESC, vulnerabilities.id DESC
   Sort Method: quicksort  Memory: 27kB
   Buffers: shared hit=934
   ->  Nested Loop  (cost=456.55..531.52 rows=1 width=281) (actual time=5.052..6.878 rows=11 loops=1)
         Buffers: shared hit=928
         ->  Hash Join  (cost=456.11..528.17 rows=1 width=16) (actual time=4.983..6.622 rows=16 loops=1)
               Hash Cond: (vulnerability_issue_links.vulnerability_id = vulnerability_occurrences.vulnerability_id)
               Buffers: shared hit=863
               ->  HashAggregate  (cost=138.62..191.02 rows=5240 width=8) (actual time=2.585..3.620 rows=5257 loops=1)
                     Group Key: vulnerability_issue_links.vulnerability_id
                     Buffers: shared hit=157
                     ->  Index Only Scan using idx_vulnerability_issue_links_on_vulnerability_id_and_issue_id on public.vulnerability_issue_links  (cost=0.28..125.50 rows=5248 width=8) (actual time=0.014..1.063 rows=5266 loops=1)
                           Heap Fetches: 443
                           Buffers: shared hit=157
               ->  Hash  (cost=314.25..314.25 rows=259 width=8) (actual time=2.217..2.217 rows=558 loops=1)
                     Buckets: 1024  Batches: 1  Memory Usage: 30kB
                     Buffers: shared hit=706
                     ->  Index Scan using index_vulnerability_occurrences_on_scanner_id on public.vulnerability_occurrences  (cost=0.43..314.25 rows=259 width=8) (actual time=0.054..2.077 rows=734 loops=1)
                           Index Cond: (vulnerability_occurrences.scanner_id = ANY ('{1,2}'::bigint[]))
                           Buffers: shared hit=706
         ->  Index Scan using vulnerabilities_pkey on public.vulnerabilities  (cost=0.43..3.34 rows=1 width=281) (actual time=0.013..0.013 rows=1 loops=16)
               Index Cond: (vulnerabilities.id = vulnerability_issue_links.vulnerability_id)
               Filter: (vulnerabilities.resolved_on_default_branch AND (vulnerabilities.report_type = ANY ('{0,3}'::integer[])) AND (vulnerabilities.project_id = 278964))
               Rows Removed by Filter: 0
               Buffers: shared hit=65

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
- [-] I have not included a changelog entry because _____.
[-] Documentation (if required)
Code review guidelines
Merge request performance guidelines
Style guides
Database guides
Separation of EE specific content

Availability and Testing

Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process.
Tested in all supported browsers
[-] Informed Infrastructure department of a default or new setting change, if applicable per definition of done

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

[-] Label as security and @ mention @gitlab-com/gl-security/appsec
[-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
[-] Security reports checked/validated by a reviewer from the AppSec team

Edited Mar 18, 2021 by Jonathan Schafer

Vulnerabilities scope to filter by scanner ID

What does this MR do?

Screenshots (strongly suggested)

Database

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Merge request reports