Skip to content

Resolve cross-database join in EE::Group#vulnerability_scanners

Problem to solve

EE::Group#vulnerability_scanners causes a cross-database join involving gitlab_sec and the main gitlab schema. This is because it joins vulnerability_scanners with projects and namespaces to list scanners in a group and its subgroups.

See #486216 (closed) for detailed explanations.

Proposal

Update EE::Group#vulnerability_scanners, and leverage the new columns introduced in Add traversal_ids column to vulnerability_stati... (#512592 - closed), and the scopes introduced in Resolve cross-join in ProjectsGrades.grades_for... (#503387 - closed) (to solve similar cross-joins in .grades_for).

  • Join vulnerability_scanners with vulnerability_statistics.
  • Filter on a group and its subgroup using vulnerability_statistics.traversal_ids. We can leverage the by_group scope to be introduced as part of #508631 (closed), and the corresponding DB index.
  • Filter out projects such as vulnerability_statistics.archived is true.

⚠️ This work can be done in parallel of Resolve cross-join in ProjectsGrades.grades_for... (#503387 - closed), but coordination is very much needed b/w the two issues, to reuse the same scopes and DB index.

Relevant links

Non-functional requirements

  • Documentation
  • Feature flag: To be put behind a feature flag which can be enabled once we've completed the backfill. See Backfill vulnerability_statistics.traversal_ids... (#513030 - closed). TODO: Create a feature flag rollout issue that depends on #513030 (closed).
  • Performance: Performance should be tested in the context of Group.vulnerabilityScanners (GraphQL resource field) for a large group such as gitlab-org.
  • Testing: Same.

Implementation plan

  • Introduce a feature flag.
  • Change #vulnerability_scanners to that it selects scanners for projects that have statistics in the group, and that are archived. This is behind the feature flag.
  • Update specs to cover the case when the flag is disabled. Also, take into account that projects marked for deletion are no longer ignored, when the flag is disabled.

Verification steps

Enable the feature flag for a large group such as gitlab-org, and query Group.vulnerabilityScanners using the GraphQL API. This should return the same scanners as before in a comparable time.

GraphQL query 
query groupSpecificScanners($fullPath: ID!) {
  group(fullPath: $fullPath) {
    id
    vulnerabilityScanners {
      nodes {
        ...VulnerabilityScanner
      }
    }
  }
}

fragment VulnerabilityScanner on VulnerabilityScanner {
  id
  externalId
  name
  reportType
  vendor
}
{
  "fullPath": "gitlab-com"
}

See https://gitlab.com/gitlab-org/gitlab/-/blob/58b52a50c8fb984aacc772eed40e766e6989d2ff/ee/app/assets/javascripts/security_dashboard/graphql/queries/group_specific_scanners.query.graphql#L3-12 and https://gitlab.com/gitlab-org/gitlab/-/blob/58b52a50c8fb984aacc772eed40e766e6989d2ff/ee/app/assets/javascripts/security_dashboard/graphql/fragments/vulnerability_scanner.fragment.graphql#L1-7

Edited by Fabien Catteau