Add VulnerabilitiesFinder for scan result policies
What does this MR do and why?
This MR adds a finder that replaces Security::ScanResultPolicies::VulnerabilitiesCountService to find the vulnerabilities that matches the conditions in scan result policy. The difference between this finder and the existing VulnerabilitiesCountService is that the existing service uses the uuids from the pipeline security findings to count the vulnerabilities. But, as a part of the bug: #425482 (closed), we want to change the behaviour to find the vulnerabilities even before the pipeline is complete for pre-existing states.
The MR that uses this finder is Add worker to sync approval rules with preexist... (!141095 - merged)
Database queries
Query to find vulnerabilities
SELECT
"vulnerabilities"."id",
"vulnerabilities"."project_id",
"vulnerabilities"."author_id",
"vulnerabilities"."created_at",
"vulnerabilities"."updated_at",
"vulnerabilities"."title",
"vulnerabilities"."title_html",
"vulnerabilities"."description",
"vulnerabilities"."description_html",
"vulnerabilities"."state",
"vulnerabilities"."severity",
"vulnerabilities"."severity_overridden",
"vulnerabilities"."confidence",
"vulnerabilities"."confidence_overridden",
"vulnerabilities"."resolved_by_id",
"vulnerabilities"."resolved_at",
"vulnerabilities"."report_type",
"vulnerabilities"."cached_markdown_version",
"vulnerabilities"."confirmed_by_id",
"vulnerabilities"."confirmed_at",
"vulnerabilities"."dismissed_at",
"vulnerabilities"."dismissed_by_id",
"vulnerabilities"."resolved_on_default_branch",
"vulnerabilities"."present_on_default_branch",
"vulnerabilities"."detected_at",
"vulnerabilities"."finding_id",
"vulnerabilities"."cvss"
FROM
"vulnerabilities"
INNER JOIN
"vulnerability_occurrences"
ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE
"vulnerabilities"."project_id" = 278964
AND "vulnerabilities"."present_on_default_branch" = TRUE
AND "vulnerabilities"."state" IN (
2, 1
)
AND "vulnerabilities"."severity" IN (
5, 6
)
AND "vulnerabilities"."report_type" IN (
0, 3, 2
)
AND "vulnerabilities"."created_at" <= '2023-10-12 13:24:33.531316'
AND (
solution IS NULL
)
AND (
NOT EXISTS (
SELECT
1
FROM
"vulnerability_findings_remediations"
WHERE
"vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id"
)
)
AND (
NOT EXISTS (
SELECT
1
FROM
"vulnerability_flags"
WHERE
"vulnerability_flags"."flag_type" = 0
AND "vulnerability_flags"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id"
)
) LIMIT 6 Limit (cost=1.85..54.53 rows=6 width=429) (actual time=1.151..5.704 rows=6 loops=1)
Buffers: shared hit=51 read=46 dirtied=3
I/O Timings: read=5.272 write=0.000
-> Nested Loop Anti Join (cost=1.85..178059.44 rows=20279 width=429) (actual time=1.149..5.693 rows=6 loops=1)
Buffers: shared hit=51 read=46 dirtied=3
I/O Timings: read=5.272 write=0.000
-> Nested Loop Anti Join (cost=1.56..171822.00 rows=20284 width=437) (actual time=1.121..5.583 rows=6 loops=1)
Buffers: shared hit=40 read=45 dirtied=3
I/O Timings: read=5.225 write=0.000
-> Nested Loop (cost=1.14..162753.60 rows=20371 width=437) (actual time=0.932..5.249 rows=6 loops=1)
Buffers: shared hit=27 read=40 dirtied=3
I/O Timings: read=4.976 write=0.000
-> Index Scan using index_vulnerabilities_common_finder_query_on_default_branch on public.vulnerabilities (cost=0.57..50233.78 rows=32035 width=429) (actual time=0.632..1.394 rows=6 loops=1)
Index Cond: ((vulnerabilities.project_id = 278964) AND (vulnerabilities.state = ANY ('{2,1}'::integer[])) AND (vulnerabilities.report_type = ANY ('{0,3,2}'::integer[])) AND (vulnerabilities.present_on_default_branch = true) AND (vulnerabilities.severity = ANY ('{5,6}'::integer[])))
Filter: (vulnerabilities.created_at <= '2023-10-12 13:24:33.531316+00'::timestamp with time zone)
Rows Removed by Filter: 0
Buffers: shared hit=9 read=10
I/O Timings: read=1.284 write=0.000
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences (cost=0.57..3.50 rows=1 width=16) (actual time=0.613..0.639 rows=1 loops=6)
Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
Filter: (vulnerability_occurrences.solution IS NULL)
Rows Removed by Filter: 0
Buffers: shared hit=18 read=30 dirtied=3
I/O Timings: read=3.691 write=0.000
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on public.vulnerability_findings_remediations (cost=0.43..0.50 rows=4 width=8) (actual time=0.052..0.052 rows=0 loops=6)
Index Cond: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
Heap Fetches: 0
Buffers: shared hit=13 read=5
I/O Timings: read=0.250 write=0.000
-> Index Only Scan using index_vulnerability_flags_on_unique_columns on public.vulnerability_flags (cost=0.29..0.31 rows=1 width=8) (actual time=0.016..0.016 rows=0 loops=6)
Index Cond: ((vulnerability_flags.vulnerability_occurrence_id = vulnerability_occurrences.id) AND (vulnerability_flags.flag_type = 0))
Heap Fetches: 0
Buffers: shared hit=11 read=1
I/O Timings: read=0.046 write=0.000
Time: 15.239 ms
- planning: 9.304 ms
- execution: 5.935 ms
- I/O read: 5.272 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 51 (~408.00 KiB) from the buffer pool
- reads: 46 (~368.00 KiB) from the OS file cache, including disk I/O
- dirtied: 3 (~24.00 KiB)
- writes: 0Query to get count of vulnerabilities
This query will be used in Add worker to sync approval rules with preexist... (!141095 - merged)
SELECT
COUNT(*)
FROM
(SELECT
1 AS one
FROM
"vulnerabilities"
INNER JOIN
"vulnerability_occurrences"
ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
WHERE
"vulnerabilities"."project_id" = 278964
AND "vulnerabilities"."present_on_default_branch" = TRUE
AND "vulnerabilities"."state" IN (
2, 1
)
AND "vulnerabilities"."severity" IN (
5, 6
)
AND "vulnerabilities"."report_type" IN (
0, 3, 2
)
AND "vulnerabilities"."created_at" <= '2023-12-12 17:28:28.829831'
AND (
solution IS NULL
)
AND (
NOT EXISTS (
SELECT
1
FROM
"vulnerability_findings_remediations"
WHERE
"vulnerability_findings_remediations"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id"
)
)
AND (
NOT EXISTS (
SELECT
1
FROM
"vulnerability_flags"
WHERE
"vulnerability_flags"."flag_type" = 0
AND "vulnerability_flags"."vulnerability_occurrence_id" = "vulnerability_occurrences"."id"
)
) LIMIT 6
) subquery_for_count Aggregate (cost=53.65..53.66 rows=1 width=8) (actual time=93.700..93.704 rows=1 loops=1)
Buffers: shared hit=51 read=46 dirtied=3
I/O Timings: read=92.933 write=0.000
-> Limit (cost=1.85..53.58 rows=6 width=4) (actual time=17.040..93.686 rows=6 loops=1)
Buffers: shared hit=51 read=46 dirtied=3
I/O Timings: read=92.933 write=0.000
-> Nested Loop Anti Join (cost=1.85..186206.81 rows=21599 width=4) (actual time=17.038..93.678 rows=6 loops=1)
Buffers: shared hit=51 read=46 dirtied=3
I/O Timings: read=92.933 write=0.000
-> Nested Loop Anti Join (cost=1.56..179563.46 rows=21604 width=8) (actual time=16.995..92.821 rows=6 loops=1)
Buffers: shared hit=40 read=45 dirtied=3
I/O Timings: read=92.187 write=0.000
-> Nested Loop (cost=1.14..169904.77 rows=21697 width=8) (actual time=14.709..87.030 rows=6 loops=1)
Buffers: shared hit=27 read=40 dirtied=3
I/O Timings: read=86.538 write=0.000
-> Index Scan using index_vulnerabilities_common_finder_query_on_default_branch on public.vulnerabilities (cost=0.57..50233.78 rows=34121 width=8) (actual time=7.591..18.831 rows=6 loops=1)
Index Cond: ((vulnerabilities.project_id = 278964) AND (vulnerabilities.state = ANY ('{2,1}'::integer[])) AND (vulnerabilities.report_type = ANY ('{0,3,2}'::integer[])) AND (vulnerabilities.present_on_default_branch = true) AND (vulnerabilities.severity = ANY ('{5,6}'::integer[])))
Filter: (vulnerabilities.created_at <= '2023-12-12 17:28:28.829831+00'::timestamp with time zone)
Rows Removed by Filter: 0
Buffers: shared hit=9 read=10
I/O Timings: read=18.664 write=0.000
-> Index Scan using index_vulnerability_occurrences_on_vulnerability_id on public.vulnerability_occurrences (cost=0.57..3.50 rows=1 width=16) (actual time=10.629..11.360 rows=1 loops=6)
Index Cond: (vulnerability_occurrences.vulnerability_id = vulnerabilities.id)
Filter: (vulnerability_occurrences.solution IS NULL)
Rows Removed by Filter: 0
Buffers: shared hit=18 read=30 dirtied=3
I/O Timings: read=67.874 write=0.000
-> Index Only Scan using index_vulnerability_findings_remediations_on_unique_keys on public.vulnerability_findings_remediations (cost=0.43..0.50 rows=4 width=8) (actual time=0.959..0.959 rows=0 loops=6)
Index Cond: (vulnerability_findings_remediations.vulnerability_occurrence_id = vulnerability_occurrences.id)
Heap Fetches: 0
Buffers: shared hit=13 read=5
I/O Timings: read=5.649 write=0.000
-> Index Only Scan using index_vulnerability_flags_on_unique_columns on public.vulnerability_flags (cost=0.29..0.31 rows=1 width=8) (actual time=0.139..0.139 rows=0 loops=6)
Index Cond: ((vulnerability_flags.vulnerability_occurrence_id = vulnerability_occurrences.id) AND (vulnerability_flags.flag_type = 0))
Heap Fetches: 0
Buffers: shared hit=11 read=1
I/O Timings: read=0.745 write=0.000
Time: 101.523 ms
- planning: 7.614 ms
- execution: 93.909 ms
- I/O read: 92.933 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 51 (~408.00 KiB) from the buffer pool
- reads: 46 (~368.00 KiB) from the OS file cache, including disk I/O
- dirtied: 3 (~24.00 KiB)
- writes: 0MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Addresses #425482 (closed)
Edited by Sashi Kumar Kumaresan