Find feedback based on UUID
What does this MR do and why?
This MR was created to use the finding_uuid
field to connect a vulnerability feedback with a finding. The current usage of project_fingerprint
is problematic as it can associate multiple findings to the same feedback, resulting in bugs as described in Dismissal of single SAST IaC Pipeline Finding r... (#350683 - closed).
Screenshots or screen recordings
Before change
After Change
Query Plans
ee/app/finders/security/findings_finder.rb
existing_vulnerabilities
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9526/commands/33992
explain SELECT "vulnerabilities"."id" AS t0_r0, "vulnerabilities"."milestone_id" AS t0_r1, "vulnerabilities"."epic_id" AS t0_r2, "vulnerabilities"."project_id" AS t0_r3, "vulnerabilities"."author_id" AS t0_r4, "vulnerabilities"."updated_by_id" AS t0_r5, "vulnerabilities"."last_edited_by_id" AS t0_r6, "vulnerabilities"."start_date" AS t0_r7, "vulnerabilities"."due_date" AS t0_r8, "vulnerabilities"."last_edited_at" AS t0_r9, "vulnerabilities"."created_at" AS t0_r10, "vulnerabilities"."updated_at" AS t0_r11, "vulnerabilities"."title" AS t0_r12, "vulnerabilities"."title_html" AS t0_r13, "vulnerabilities"."description" AS t0_r14, "vulnerabilities"."description_html" AS t0_r15, "vulnerabilities"."start_date_sourcing_milestone_id" AS t0_r16, "vulnerabilities"."due_date_sourcing_milestone_id" AS t0_r17, "vulnerabilities"."state" AS t0_r18, "vulnerabilities"."severity" AS t0_r19, "vulnerabilities"."severity_overridden" AS t0_r20, "vulnerabilities"."confidence" AS t0_r21, "vulnerabilities"."confidence_overridden" AS t0_r22, "vulnerabilities"."resolved_by_id" AS t0_r23, "vulnerabilities"."resolved_at" AS t0_r24, "vulnerabilities"."report_type" AS t0_r25, "vulnerabilities"."cached_markdown_version" AS t0_r26, "vulnerabilities"."confirmed_by_id" AS t0_r27, "vulnerabilities"."confirmed_at" AS t0_r28, "vulnerabilities"."dismissed_at" AS t0_r29, "vulnerabilities"."dismissed_by_id" AS t0_r30, "vulnerabilities"."resolved_on_default_branch" AS t0_r31, "vulnerabilities"."present_on_default_branch" AS t0_r32, "vulnerabilities"."detected_at" AS t0_r33, "findings"."id" AS t1_r0, "findings"."created_at" AS t1_r1, "findings"."updated_at" AS t1_r2, "findings"."severity" AS t1_r3, "findings"."confidence" AS t1_r4, "findings"."report_type" AS t1_r5, "findings"."project_id" AS t1_r6, "findings"."scanner_id" AS t1_r7, "findings"."primary_identifier_id" AS t1_r8, "findings"."project_fingerprint" AS t1_r9, "findings"."location_fingerprint" AS t1_r10, "findings"."uuid" AS t1_r11, "findings"."name" AS t1_r12, "findings"."metadata_version" AS t1_r13, "findings"."raw_metadata" AS t1_r14, "findings"."vulnerability_id" AS t1_r15, "findings"."details" AS t1_r16, "findings"."description" AS t1_r17, "findings"."message" AS t1_r18, "findings"."solution" AS t1_r19, "findings"."cve" AS t1_r20, "findings"."location" AS t1_r21, "findings"."detection_method" AS t1_r22, "findings"."migrated_to_new_structure" AS t1_r23 FROM "vulnerabilities" LEFT OUTER JOIN "vulnerability_occurrences" "findings" ON "findings"."vulnerability_id" = "vulnerabilities"."id" WHERE "vulnerabilities"."project_id" = 23 AND "findings"."uuid" IN (SELECT "vulnerability_occurrences"."uuid" FROM "vulnerability_occurrences" WHERE "vulnerability_occurrences"."project_id" = 33851038)
Time: 1.808 s
- planning: 1.512 ms
- execution: 1.807 s
- I/O read: 1.791 s
- I/O write: 0.000 ms
Shared buffers:
- hits: 1401 (~10.90 MiB) from the buffer pool
- reads: 363 (~2.80 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
ee/app/models/security/finding.rb
undismissed
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9526/commands/33968
SELECT
"security_findings".*
FROM
"security_findings"
INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
WHERE
"security_scans"."pipeline_id" = 251
AND (NOT EXISTS (
SELECT
1
FROM
"security_scans"
INNER JOIN "projects" ON "projects"."id" = "security_scans"."project_id"
INNER JOIN "vulnerability_feedback" ON "vulnerability_feedback"."project_id" = "projects"."id"
WHERE (vulnerability_feedback.category = (security_scans.scan_type - 1))
AND "vulnerability_feedback"."feedback_type" = 0
AND (security_scans.id = security_findings.scan_id)
AND (vulnerability_feedback.finding_uuid = security_findings.uuid)))
Time: 25.149 ms
- planning: 22.899 ms
- execution: 2.250 ms
- I/O read: 2.042 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 4 (~32.00 KiB) from the buffer pool
- reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
ee/app/models/vulnerabilities/finding.rb
self.related_dismissal_feedback
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9526/commands/33969
SELECT
1
FROM
"vulnerability_feedback",
"vulnerability_occurrences"
WHERE (vulnerability_occurrences.uuid = vulnerability_feedback.finding_uuid::text)
AND "vulnerability_feedback"."feedback_type" = 0
Time: 1.366 min
- planning: 3.160 ms
- execution: 1.366 min
- I/O read: 3.923 min
- I/O write: 0.000 ms
Shared buffers:
- hits: 1284470 (~9.80 GiB) from the buffer pool
- reads: 199435 (~1.50 GiB) from the OS file cache, including disk I/O
- dirtied: 15113 (~118.10 MiB)
- writes: 0
load_feedback
NOTE: Because I had to pull the UUIDs from the DB in the query (where normally the uuids are provided already), it added additional time and complexity that wouldn't exist in the actual call from the method.
https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/9526/commands/33976
SELECT
"vulnerability_feedback".*
FROM
"vulnerability_feedback"
WHERE
"vulnerability_feedback"."finding_uuid" IN (
SELECT
uuid("vulnerability_occurrences"."uuid")
FROM
"vulnerability_occurrences"
WHERE
"vulnerability_occurrences"."project_id" = 33851038)
Time: 14.291 ms
- planning: 3.850 ms
- execution: 10.441 ms
- I/O read: 8.662 ms
- I/O write: 0.000 ms
Shared buffers:
- hits: 332 (~2.60 MiB) from the buffer pool
- reads: 312 (~2.40 MiB) from the OS file cache, including disk I/O
- dirtied: 0
- writes: 0
How to set up and validate locally
- Switch to
master
branch. - Import https://gitlab.com/gitlab-org/security-products/tests/ansible
- Simplify CI (simultaneously triggering new pipeline) https://gitlab.com/theoretick/ansible-goop/-/commit/a352885d42fc5535b587ecfb6d6d6da99eecfe00
- Once pipeline has completed, dismiss a single finding on pipeline security tab
- Reload page
- Note all findings have been dismissed
- Switch to
350683-dismissal-of-single-sast-iac-pipeline-finding-results-in-mass-dismissal
branch - Reload page
- Note only the previously dismissed finding has been dismissed
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.
Related to #350683 (closed)