Skip to content

Add archived project filter for Vulnerabilities

Michael Becker requested to merge 213144-add-filter into master

What does this MR do and why?

We have issue 213144 open to stop including archived project vulnerabilities in various places in the UI

As step 1 for this change, we added the for_projects scope to the Vulnerability and Vulnerability::Read models.

In this MR we use utilize that new scope in changing the associated finders to have different behavior with regards to archived projects

We want the default behavior for all finders to be:

Do not include vulnerability` or `vulnerability_read` objects in the
result-set if the project they are associated with is currently
`archived`

At the same time we want to, in a followup commit, provide API access
to toggle on /off the inclusion of `archived` objects

follow-up MRs will use this field to:

  • expose this as a nullable argument in the VulnerabilitiesResolver

Changelog: fixed EE: true

Raw SQL

SQL derived from:

  • tailing the logs: 
    tail -f log/*.log | grep --line-buffered -E ".*FROM \"vulnerability\_reads\".*"
  • loading vulnerabilities dashboard: e.g. http://gdk.test:3000/groups/flightjs/-/security/vulnerabilities/
original 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
WHERE
    "vulnerability_reads"."vulnerability_id" IN (
        SELECT
            "vulnerability_reads"."vulnerability_id"
        FROM
            unnest(ARRAY (
                    SELECT
                        "namespaces"."id"
                    FROM "namespaces"
                    WHERE
                        "namespaces"."type" = 'Group'
                        AND (traversal_ids @> ('{33}')))::bigint[]) AS "namespace_ids" ("namespace_id"),
            unnest('{6,2,5,3,1,0,4,99}'::smallint[]) AS "report_types" ("report_type"),
            unnest('{1,4}'::smallint[]) AS "states" ("state"),
            LATERAL (
                SELECT
                    "vulnerability_reads"."namespace_id",
                    "vulnerability_reads"."report_type",
                    "vulnerability_reads"."state",
                    "vulnerability_reads"."severity",
                    "vulnerability_reads"."vulnerability_id"
                FROM
                    "vulnerability_reads"
                WHERE (vulnerability_reads."namespace_id" = "namespace_ids"."namespace_id")
                AND (vulnerability_reads."report_type" = "report_types"."report_type")
                AND (vulnerability_reads."state" = "states"."state")
            ORDER BY
                "vulnerability_reads"."severity" DESC,
                "vulnerability_reads"."vulnerability_id" DESC
            LIMIT 21) AS vulnerability_reads
    ORDER BY
        "vulnerability_reads"."severity" DESC,
        "vulnerability_reads"."vulnerability_id" DESC
    LIMIT 21)
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 21
updated 
SELECT
    "vulnerability_reads".*
FROM
    "vulnerability_reads"
    INNER JOIN "projects" ON "projects"."id" = "vulnerability_reads"."project_id"
WHERE
    "vulnerability_reads"."namespace_id" IN (
        SELECT
            "namespaces"."id"
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'Group'
            AND (traversal_ids @> ('{33}')))
    AND "projects"."archived" = FALSE
    AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99)
    AND "vulnerability_reads"."state" IN (1, 4)
ORDER BY
    "vulnerability_reads"."severity" DESC,
    "vulnerability_reads"."vulnerability_id" DESC
LIMIT 21

explain queries

Screenshots or screen recordings

Before After
before after

How to set up and validate locally

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

MR acceptance checklist

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

Related to #213144 (closed)

Edited by Michael Becker

Merge request reports