Resolve vulnerability scanners model cross join issues

Summary

The decomposition of vulnerability scanners has resulted in cross join issues emanating from the Vulnerabilities::Scanner model. These should be resolvable once all the appropriate tables have been moved and the mirror tables for Projects and Namespaces have been implemented such that the currently existing queries for those tables can be utilised with minimal model changes.

Further details

The following scopes of Vulnerabilities::Scanner might result in a cross-database join for which an exception is currently in place.

    scope :for_projects, ->(project_ids) { where(project_id: project_ids).allow_cross_joins_across_databases(url: 'https://gitlab.com/gitlab-org/gitlab/-/issues/478017') }
    scope :by_projects, ->(values) { where(project_id: values).allow_cross_joins_across_databases(url: 'https://gitlab.com/gitlab-org/gitlab/-/issues/478017') }

These scopes don't necessarily cause cross-joins. They do if the argument is a relation, but they don't if the argument is a list of project IDs.

These two scopes do the exact same thing by the way.

by_projects doesn't seem to be used.

for_projects is used in two different context.

• In MarkAsResolvedService it's called with a single project ID. This shouldn't cause any cross-join. ✅

     def cvs_scanner_id
       ::Vulnerabilities::Scanner.for_projects(project.id)
         .with_external_id(CVS_SCANNER_EXTERNAL_ID)
         .pluck_primary_key
     end

• In InstanceSecurityDashboard it's called with projects, which is a relation. This should cause a cross-join. ❌

    def projects
      Project.where(id: visible_users_security_dashboard_projects)
             .with_feature_available_for_user(:security_and_compliance, user)
             .allow_cross_joins_across_databases(url: 'https://gitlab.com/gitlab-org/gitlab/-/issues/485658')
    end
  
    def vulnerability_scanners
      return Vulnerabilities::Scanner.none if projects.empty?
  
      Vulnerabilities::Scanner.for_projects(projects)
    end

Related issue: Resolve cross DB issues in ee/app/models/instan... (#485658 - closed)

Proposal

• Update InstanceSecurityDashboard::vulnerability_scanners to pluck and pass project IDs to Vulnerabilities::Scanner.for_projects.
• Remove allow_cross_joins_across_databases from Vulnerabilities::Scanner.for_projects.
• Remove Scanner.by_projects.