Skip to content

Model changes to support vulnerability report grouping

Bala Kumar requested to merge 425783-be-model-changes into master

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

2. For group report (Ignore as handling large groups is discussed in #430715 (closed))

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.

a. grouped_by(:severity) 

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

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" 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.

Related to #425783

Edited by Bala Kumar

Merge request reports