Skip to content

Fix filter_by_states clearing previously applied filters

What does this MR do and why?

filter_by_states assigns @vulnerabilities with vulnerable.vulnerability_reads. This doesn't have the previous scopes applied to it, so it ignores any previously applied filters. This causes incorrect vulnerability counts to be displayed on the instance security dashboard.

SQL Queries

Before: https://console.postgres.ai/shared/c03cefd9-bbce-4865-bddf-b434b2de95d4

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 = 1
            ) 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: https://console.postgres.ai/shared/db01ed66-1b3d-4e4f-adf0-7604fd3d999b

SELECT
    count(*) AS count_all,
    vulnerabilities.severity AS vulnerabilities_severity
FROM
    vulnerabilities
WHERE
    vulnerabilities.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
            projects.archived = false
    ) AND
    vulnerabilities.present_on_default_branch = true AND
    vulnerabilities.report_type = 7 AND
    vulnerabilities.id IN (
        SELECT
            vulnerability_reads.vulnerability_id
        FROM
            vulnerability_reads
        WHERE
            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
                    projects.archived = false
            ) AND
            vulnerability_reads.state IN ( 1, 4 )
    )
GROUP BY
    vulnerabilities.severity
ORDER BY
    vulnerabilities.severity DESC;

Screenshots or screen recordings

Screenshots are required for UI changes, and strongly recommended for all other merge requests.

Before After
Screenshot_2023-12-12_at_2.14.12_PM Screenshot_2023-12-12_at_2.13.09_PM

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

  1. Add projects with vulnerabilities to the security center (Note: You can seed a project with vulnerabilities)
  2. Go to <gdk_url>/-/security/vulnerabilities
  3. 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.

Edited by Brian Williams

Merge request reports

Loading