Backend resolver for security policy eligible projects

Artur Fedorovrequested to merge
593858-policy-scope-eligible-projects-mr1-backend into master

Summary

Adds a dedicated securityPolicyEligibleProjects GraphQL resolver on ProjectType that returns all projects eligible for policy scope selection when a project is used as a Security Policy Project (SPP).

Currently, the policy scope "specific projects" dropdown only shows projects from descendant namespaces of each SPP-linked group. This resolver fixes that by collecting root ancestors of all linked groups and returning all projects under those root namespaces.

Part 1 of 2 for #593858

Changes

  • New finder: SecurityPolicyEligibleProjectsFinder — takes an SPP project, finds linked groups, extracts unique root ancestor IDs via traversal_ids[1], returns all projects under those root namespaces with visibility filtering
  • New resolver: SecurityPolicyEligibleProjectsResolver — exposes the finder as a connection_type field with optional search argument
  • Field registration: Adds security_policy_eligible_projects field to ProjectType
  • Tests: Finder and resolver specs covering sibling/parent visibility, deduplication, license checks, visibility filtering, search, pagination

How it works

SPP project
  → linked groups (e.g., subgroup-a, subgroup-b)
  → root ancestors via traversal_ids[1] (e.g., topgroup)
  → DISTINCT dedup
  → Project.in_namespace(root.self_and_descendant_ids)
  → .non_archived.without_deleted.public_or_visible_to_user

MR Dependencies

This MR (Backend) ──sequential──▶ MR2 (Frontend)

Database

Full query

The finder issues three queries. All scopes (in_namespace, non_archived, without_deleted, public_or_visible_to_user, search) and pagination (GraphQL connection_type, default_max_page_size = 100) are reflected below.

1. Collect root ancestor IDs of SPP-linked groups (Namespace.root_ids_for):

SELECT DISTINCT traversal_ids[1]
FROM namespaces
WHERE id IN (
  SELECT namespace_id
  FROM security_policy_project_linked_groups
  WHERE security_policy_project_id = $1
)
  AND traversal_ids[1] IS NOT NULL
LIMIT 1500;

The LIMIT 1500 is a safety cap inherited from Namespace.root_ids_for — it sits above the heaviest 100 requests observed in production (see #577678 (comment 2841315975)).

2. Expand roots to self_and_descendant_ids (Namespace.by_ids(root_ids).self_and_descendant_ids):

SELECT id
FROM namespaces
WHERE traversal_ids @> ARRAY[$1]::integer[]
   OR traversal_ids @> ARRAY[$2]::integer[]
   -- ... one clause per root id

This uses the GIN index on namespaces.traversal_ids.

3. Main project query (Project.in_namespace(...).non_archived.without_deleted.public_or_visible_to_user.search(...) plus GraphQL pagination):

Shown with two concrete variants (anonymous and authenticated) in ### Query plan. The ORM emits archived = FALSE, pending_delete = FALSE, the three-branch public_or_visible_to_user (PUBLIC / INTERNAL for non-external users / project_authorizations subquery for the current user), an optional name/path ILIKE $search, and ORDER BY id DESC LIMIT 101 for keyset pagination (the +1 probe is how GraphQL::Pagination::ActiveRecordRelationConnection detects hasNextPage).

Query plan

Two plans to cover both paths through public_or_visible_to_user — anonymous hits only the PUBLIC branch; an authenticated user exercises the project_authorizations subquery.

Main project query, anonymous viewer, using the gitlab-org root namespace (id 9970) on gitlab-production-main:

SELECT projects.*
FROM projects
WHERE projects.namespace_id IN (
  SELECT id FROM namespaces WHERE traversal_ids @> ARRAY[9970]::integer[]
)
  AND projects.archived = FALSE
  AND projects.pending_delete = FALSE
  AND projects.visibility_level IN (10, 20)
ORDER BY projects.id DESC
LIMIT 101;

Same query, authenticated viewer — exercises the project_authorizations branch. Run with user 10762712 (~12,195 authorizations under gitlab-org):

SELECT projects.*
FROM projects
WHERE projects.namespace_id IN (
  SELECT id FROM namespaces WHERE traversal_ids @> ARRAY[9970]::integer[]
)
  AND projects.archived = FALSE
  AND projects.pending_delete = FALSE
  AND (
    projects.visibility_level = 20
    OR projects.visibility_level = 10
    OR projects.id IN (
      SELECT project_id
      FROM project_authorizations
      WHERE user_id = 10762712
    )
  )
ORDER BY projects.id DESC
LIMIT 101;

Plan highlights — project_authorizations is probed as a hashed Index Only Scan on project_authorizations_pkey (~12k rows for this user); outer Parallel Index Scan Backward on projects terminates at LIMIT 101; namespace lookup is a Memoize-cached Index Scan on namespaces_pkey with the traversal_ids @> '{9970}' filter applied per cached row. Total cost 2486.26..227449.09.

EXPLAIN tree 
Limit  (cost=2486.26..227449.09 rows=101 width=746)
  ->  Gather Merge  (cost=2486.26..7156749.61 rows=3212 width=746)
        Workers Planned: 2
        ->  Nested Loop  (cost=1486.24..7155378.84 rows=1338 width=746)
              ->  Parallel Index Scan Backward using index_projects_on_id_and_archived_and_pending_delete on projects  (cost=1485.66..5883313.60 rows=11152829 width=746)
                    Filter: ((visibility_level = 20) OR (visibility_level = 10) OR (ANY (id = (hashed SubPlan 1).col1)))
                    SubPlan 1
                      ->  Index Only Scan using project_authorizations_pkey on project_authorizations  (cost=0.58..1454.61 rows=12195 width=4)
                            Index Cond: (user_id = 10762712)
              ->  Memoize  (cost=0.58..0.72 rows=1 width=4)
                    Cache Key: projects.namespace_id
                    Cache Mode: logical
                    ->  Index Scan using namespaces_pkey on namespaces  (cost=0.57..0.71 rows=1 width=4)
                          Index Cond: (id = projects.namespace_id)
                          Filter: (traversal_ids @> '{9970}'::integer[])

Database review

Requesting review from the Database team per the scopes and cross-table joins introduced in ee/app/finders/security/security_policy_eligible_projects_finder.rb.

Test plan

  • Finder returns projects from sibling groups of linked groups
  • Finder returns projects from parent groups of linked groups
  • Deduplicates when multiple linked groups share the same root ancestor
  • Returns Project.none when SPP has no linked groups
  • Returns Project.none when license is not available
  • Respects project visibility (only returns projects visible to current user)
  • Excludes archived and deleted projects
  • Search parameter filters results by project name/path
  • Pagination works correctly via connection_type

🤖 Generated with Claude Code

Closes #593858

Edited by Artur Fedorov

Merge request reports