Skip to content

Adjust findings finder to remove feedback dependency based on feature flag

Mehmet Emin INAC requested to merge 377584_adjust_undismissed_scope_to_depend_on_vulnerability into master

What does this MR do and why?

With this change, we will determine the undismissed findings by the related vulnerability record if the deprecate_vulnerabilities_feedback feature flag is turned on.

Database review

Here is an example query generated by using the scope introduced in this MR 
SELECT
    "security_findings"."id" AS t0_r0,
    "security_findings"."scan_id" AS t0_r1,
    "security_findings"."scanner_id" AS t0_r2,
    "security_findings"."severity" AS t0_r3,
    "security_findings"."confidence" AS t0_r4,
    "security_findings"."project_fingerprint" AS t0_r5,
    "security_findings"."deduplicated" AS t0_r6,
    "security_findings"."uuid" AS t0_r7,
    "security_findings"."overridden_uuid" AS t0_r8,
    "security_findings"."partition_number" AS t0_r9,
    "security_findings"."finding_data" AS t0_r10,
    "scans_security_findings"."id" AS t1_r0,
    "scans_security_findings"."created_at" AS t1_r1,
    "scans_security_findings"."updated_at" AS t1_r2,
    "scans_security_findings"."build_id" AS t1_r3,
    "scans_security_findings"."scan_type" AS t1_r4,
    "scans_security_findings"."info" AS t1_r5,
    "scans_security_findings"."project_id" AS t1_r6,
    "scans_security_findings"."pipeline_id" AS t1_r7,
    "scans_security_findings"."latest" AS t1_r8,
    "scans_security_findings"."status" AS t1_r9,
    "scans_security_findings"."findings_partition_number" AS t1_r10,
    "vulnerability_scanners"."id" AS t2_r0,
    "vulnerability_scanners"."created_at" AS t2_r1,
    "vulnerability_scanners"."updated_at" AS t2_r2,
    "vulnerability_scanners"."project_id" AS t2_r3,
    "vulnerability_scanners"."external_id" AS t2_r4,
    "vulnerability_scanners"."name" AS t2_r5,
    "vulnerability_scanners"."vendor" AS t2_r6
FROM
    "security_findings"
    INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
    INNER JOIN "security_scans" "scans_security_findings" ON "scans_security_findings"."id" = "security_findings"."scan_id"
    LEFT OUTER JOIN "vulnerability_scanners" ON "vulnerability_scanners"."id" = "security_findings"."scanner_id"
WHERE
    "security_scans"."pipeline_id" = 704391911
    AND "security_findings"."deduplicated" = TRUE
    AND "security_scans"."latest" = TRUE
    AND "security_scans"."status" = 1
    AND (NOT EXISTS (
            SELECT
                1
            FROM
                "vulnerabilities"
                INNER JOIN "vulnerability_occurrences" ON "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
            WHERE
                "vulnerabilities"."state" = 2
                AND (vulnerability_occurrences.uuid = security_findings.uuid::text)))
ORDER BY
    "security_findings"."severity" DESC,
    "security_findings"."confidence" DESC,
    "security_findings"."id" ASC
LIMIT 20 OFFSET 0

And its execution plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13505/commands/47434

The old query(runs when the FF is turned off) 
SELECT
    "security_findings"."id" AS t0_r0,
    "security_findings"."scan_id" AS t0_r1,
    "security_findings"."scanner_id" AS t0_r2,
    "security_findings"."severity" AS t0_r3,
    "security_findings"."confidence" AS t0_r4,
    "security_findings"."project_fingerprint" AS t0_r5,
    "security_findings"."deduplicated" AS t0_r6,
    "security_findings"."uuid" AS t0_r7,
    "security_findings"."overridden_uuid" AS t0_r8,
    "security_findings"."partition_number" AS t0_r9,
    "security_findings"."finding_data" AS t0_r10,
    "scans_security_findings"."id" AS t1_r0,
    "scans_security_findings"."created_at" AS t1_r1,
    "scans_security_findings"."updated_at" AS t1_r2,
    "scans_security_findings"."build_id" AS t1_r3,
    "scans_security_findings"."scan_type" AS t1_r4,
    "scans_security_findings"."info" AS t1_r5,
    "scans_security_findings"."project_id" AS t1_r6,
    "scans_security_findings"."pipeline_id" AS t1_r7,
    "scans_security_findings"."latest" AS t1_r8,
    "scans_security_findings"."status" AS t1_r9,
    "scans_security_findings"."findings_partition_number" AS t1_r10,
    "vulnerability_scanners"."id" AS t2_r0,
    "vulnerability_scanners"."created_at" AS t2_r1,
    "vulnerability_scanners"."updated_at" AS t2_r2,
    "vulnerability_scanners"."project_id" AS t2_r3,
    "vulnerability_scanners"."external_id" AS t2_r4,
    "vulnerability_scanners"."name" AS t2_r5,
    "vulnerability_scanners"."vendor" AS t2_r6
FROM
    "security_findings"
    INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
    INNER JOIN "security_scans" "scans_security_findings" ON "scans_security_findings"."id" = "security_findings"."scan_id"
    LEFT OUTER JOIN "vulnerability_scanners" ON "vulnerability_scanners"."id" = "security_findings"."scanner_id"
WHERE
    "security_scans"."pipeline_id" = 704391911
    AND "security_findings"."deduplicated" = TRUE
    AND "security_scans"."latest" = TRUE
    AND "security_scans"."status" = 1
    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)))
ORDER BY
    "security_findings"."severity" DESC,
    "security_findings"."confidence" DESC,
    "security_findings"."id" ASC
LIMIT 20 OFFSET 0

And the execution plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/13563/commands/47610

Related to Adjust `FindingsFinder` not to depend on dismis... (#377584 - closed).

MR acceptance checklist

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

Edited by Mehmet Emin INAC

Merge request reports