Use security findings to count vulnerabilities (!53116) · Merge requests · GitLab.org / GitLab

What does this MR do?

To get the number of Vulnerabilities for a given pipeline, the Security::VulnerabilityCountingService downloads all the secure artifacts associated with that pipeline, parses them, then counts them.

This MR replaces that functionality by counting the vulnerabilities associated with the Pipeline in the database.

This MR relates to #238951 (closed)

Database Query:

The new model call

Ci::Pipeline.last.security_findings.deduplicated.by_report_types(['dast', 'sast']).count_by_scan_type

results in two DB queries

SELECT
    "ci_pipelines".*
FROM
    "ci_pipelines"
ORDER BY
    "ci_pipelines"."id" DESC
LIMIT 1;

SELECT
    COUNT(*) AS count_all,
    security_scans.scan_type AS security_scans_scan_type
FROM
    "security_findings"
    INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id"
    INNER JOIN "ci_builds" ON "security_scans"."build_id" = "ci_builds"."id"
    INNER JOIN "security_scans" "scans_security_findings" ON "scans_security_findings"."id" = "security_findings"."scan_id"
WHERE
    "ci_builds"."type" = 'Ci::Build'
    AND "ci_builds"."commit_id" = 130
    AND "security_findings"."deduplicated" = TRUE
    AND "security_scans"."scan_type" IN (4, 1)
GROUP BY
    security_scans.scan_type

Query Plan: I'm uncertain how to get the commit_id to run an explain on #database-lab so I can it locally. The project has a DAST and SAST scan run against it and the query returns:

 count_id | security_scans_scan_type
----------+--------------------------
      301 |                        1
      117 |                        4

 GroupAggregate  (cost=8.87..8.93 rows=3 width=10)
   Group Key: security_scans.scan_type
   ->  Sort  (cost=8.87..8.88 rows=3 width=2)
         Sort Key: security_scans.scan_type
         ->  Nested Loop  (cost=0.84..8.85 rows=3 width=2)
               ->  Nested Loop  (cost=0.56..4.68 rows=1 width=18)
                     ->  Nested Loop  (cost=0.42..4.47 rows=1 width=10)
                           ->  Index Scan using index_ci_builds_on_commit_id_and_type_and_ref on ci_builds  (cost=0.28..2.29 rows=1 width=4)
                                 Index Cond: ((commit_id = 130) AND ((type)::text = 'Ci::Build'::text))
                           ->  Index Scan using idx_security_scans_on_build_and_scan_type on security_scans  (cost=0.14..2.16 rows=1 width=18)
                                 Index Cond: (build_id = ci_builds.id)
                                 Filter: (scan_type = ANY ('{4,1}'::integer[]))
                     ->  Index Only Scan using security_scans_pkey on security_scans scans_security_findings  (cost=0.14..0.21 rows=1 width=8)
                           Index Cond: (id = security_scans.id)
               ->  Index Only Scan using index_security_findings_on_scan_id_and_deduplicated on security_findings  (cost=0.28..3.09 rows=108 width=8)
                     Index Cond: ((scan_id = security_scans.id) AND (deduplicated = true))
                     Filter: deduplicated
(17 rows)

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

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

Use security findings to count vulnerabilities