Use VulnerabilityReadsFinder for InstanceSecurityDashboard counts
What does this MR do and why?
Use VulnerabilityReadsFinder
when retrieving counts for the instance security dashboard.
This is more performant as vulnerability_reads
is optimized for reading and can scan only one table.
Additionally, VulnerabilitiesFinder
has a bug where
it clears previous scopes when filtering by state, as it overwrites @vulnerabilities
with
vulnerable.vulnerability_reads
, which does not have the previously applied scopes.
Relates to: #435064 (closed)
SQL Queries
Before: https://console.postgres.ai/shared/7f6cf21b-d73b-4ce8-b0c8-3e2c99807f67
SELECT
count(*) AS count_all,
vulnerability_reads.severity AS vulnerability_reads_severity
FROM
vulnerability_reads
JOIN projects ON projects.id = vulnerability_reads.project_id
WHERE
projects.archived = false AND
vulnerability_reads.project_id IN (
SELECT
projects.id
FROM
projects
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE
projects.id IN (
SELECT
users_security_dashboard_projects.project_id
FROM
users_security_dashboard_projects
WHERE
users_security_dashboard_projects.user_id = 8953999
) AND
(
project_features.security_and_compliance_access_level > 0 OR
project_features.security_and_compliance_access_level IS NULL
)
) AND
vulnerability_reads.state IN ( 1, 4 )
GROUP BY
vulnerability_reads.severity
ORDER BY
vulnerability_reads.severity DESC;
After: (now has report_type) https://console.postgres.ai/shared/4d746ac6-0920-43e1-9ad8-cb6688ab27b7
SELECT
count(*) AS count_all,
vulnerability_reads.severity AS vulnerability_reads_severity
FROM
vulnerability_reads
JOIN projects ON projects.id = vulnerability_reads.project_id
WHERE
projects.archived = false AND
vulnerability_reads.project_id IN (
SELECT
projects.id
FROM
projects
LEFT JOIN project_features ON projects.id = project_features.project_id
WHERE
projects.id IN (
SELECT
users_security_dashboard_projects.project_id
FROM
users_security_dashboard_projects
WHERE
users_security_dashboard_projects.user_id = 8953999
) AND
(
project_features.security_and_compliance_access_level > 0 OR
project_features.security_and_compliance_access_level IS NULL
)
) AND
vulnerability_reads.report_type = 7 AND
vulnerability_reads.state IN ( 1, 4 )
GROUP BY
vulnerability_reads.severity
ORDER BY
vulnerability_reads.severity DESC;
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Before | After |
---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
- Add projects with vulnerabilities to the security center (Note: You can seed a project with vulnerabilities)
- Go to
<gdk_url>/-/security/vulnerabilities
- Operational vulnerability count should be 0.
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.