Model changes to support vulnerability report grouping
What does this MR do and why?
Related to #425783, to support GraphQL changes planned in #425786
Database
The intended query to be used by GraphQL API on the vulnerability report page is:
result = vulnerabilities(args).grouped_by(grouping).counted_by_severity(vulnerable)
The resultant query is of the form
1. For Project report
a. grouped_by(:severity)
SELECT COUNT(*) AS "count_all", "vulnerability_reads"."severity" AS "vulnerability_reads_severity" FROM "vulnerability_reads" WHERE "vulnerability_reads"."project_id" = 278964 AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99) AND "vulnerability_reads"."state" IN (1, 4) GROUP BY "vulnerability_reads"."severity" ORDER BY "vulnerability_reads"."severity" DESC
Plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/23699/commands/76104
b. grouped_by(:status)
SELECT COUNT(*) AS "count_all", "vulnerability_reads"."state" AS "vulnerability_reads_state", "vulnerability_reads"."severity" AS "vulnerability_reads_severity" FROM "vulnerability_reads" WHERE "vulnerability_reads"."project_id" = 278964 AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99) GROUP BY "vulnerability_reads"."state", "vulnerability_reads"."severity" ORDER BY "vulnerability_reads"."severity" DESC
Plan: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/23699/commands/76109
For group report (Ignore as handling large groups is discussed in #430715 (closed))
2. We are limiting to groups with less then 25 projects. For a group with 107 projects, we observed it not to perform well. https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/23699/commands/76110
For comparisons we are taking group 11511173
, narrowed down from the logs as it is active for the last 10 days as observed in logs for a related grouping graphql api.
grouped_by(:severity)
a. SELECT COUNT(*) AS "count_all", "vulnerability_reads"."severity" AS "vulnerability_reads_severity" 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 @> ('{11511173}'))) 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) GROUP BY "vulnerability_reads"."severity" ORDER BY "vulnerability_reads"."severity" DESC
Plan: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/23699/commands/76114
grouped_by(:status)
b. SELECT COUNT(*) AS "count_all", "vulnerability_reads"."state" AS "vulnerability_reads_state", "vulnerability_reads"."severity" AS "vulnerability_reads_severity" 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 @> ('{11511173}'))) AND "projects"."archived" = FALSE AND "vulnerability_reads"."report_type" IN (6, 2, 5, 3, 1, 0, 4, 99) GROUP BY "vulnerability_reads"."state", "vulnerability_reads"."severity" ORDER BY "vulnerability_reads"."severity" DESC
Plan: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/23699/commands/76115
How to set up and validate locally
In Rails console should be able to run
# This should return a hash with severities key
> Project.first.vulnerability_reads.grouped_by(:severity).count
=> {"medium"=>4, "low"=>3, "unknown"=>4, "high"=>7, "critical"=>6, "info"=>6}
# This also should return a hash with severities as key and counts as value and it should match the above results.
> Project.first.vulnerability_reads.counted_by_severity(Project.first)
=> {"critical"=>6, "high"=>7, "medium"=>4, "low"=>3, "unknown"=>4, "info"=>6}
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.
Related to #425783