Allow users to access project runners through group invite

What does this MR do and why?

Allow users to access project runners through group invite

Changelog: fixed

References

• Indirect Members Cannot See Available Runners i... (#464475 - closed)
• Indirect maintainer permissions no longer suffi... (#502634 - closed)
• [FF] `optimize_ci_owned_project_runners_query` -- (#551320 - closed)

Screenshots:

Query in master branch:

Query in current branch without ff:

Query in current branch with ff:

Test Coverage:

How to set up and validate locally

Setup:

• Create two groups, G1 and G2
• Add a member X in G1 as a Maintainer
• Create a project P1 in group G2
• Invite the group G1 as a Maintainer to the project P1 (http://gdk.test:3000/g1/p1/-/project_members -> Invite a Group button)
• Create a project runner in the project P1

Validation:

user = User.find(x) # id of the user that we added to the group G
user.authorized_projects.map(&:name).include?('P1')
=> true

runner = user.authorized_projects.find_by(name: 'P1').runners.first # project runner that we created already

# Without the change in the current MR:
user.owns_runner?(runner)
=> false # Though the user is authorised to access the project as a maintainer, this still returns false.

# after applying the current changes in this MR:
user.owns_runner?(runner)
=> true # The user is able to access the runner on the project runners page and maintain it.

Event Tracking:

Database query Plan:

Existing Query

Query:

SELECT "ci_runners".*
FROM ((SELECT "ci_runners".*
       FROM "ci_runners"
                INNER JOIN "ci_runner_projects" "ci_runner_projects"
                           ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
       WHERE "ci_runner_projects"."project_id" IN
             (13083, 13686280, 17115351, 21924610, 35947644, 39791822, 42319945, 51613969,
              51634462))
      UNION
      (WITH "cte_namespace_ids" AS MATERIALIZED (SELECT "ci_namespace_mirrors"."namespace_id"
                                                 FROM ((SELECT "ci_namespace_mirrors".*
                                                        FROM "ci_namespace_mirrors"
                                                        WHERE (((traversal_ids[1])) IN
                                                               ((9970), (4909902), (7135830), (10450974), (14243611),
                                                                (14358022), (15547931),
                                                                (105979951), (105980080), (105987955), (105988036),
                                                                (104128816), (104128633), (104128639),
                                                                (104128641))))) ci_namespace_mirrors),
            "cte_project_ids" AS MATERIALIZED (SELECT "ci_project_mirrors"."project_id"
                                               FROM "ci_project_mirrors"
                                               WHERE (ci_project_mirrors.namespace_id IN
                                                      (SELECT namespace_id FROM cte_namespace_ids)))
       SELECT "ci_runners".*
       FROM "ci_runners"
                INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
       WHERE (ci_runner_projects.project_id IN (SELECT project_id FROM cte_project_ids)))
      UNION
      (WITH "cte_namespace_ids" AS MATERIALIZED (SELECT "ci_namespace_mirrors"."namespace_id"
                                                 FROM ((SELECT "ci_namespace_mirrors".*
                                                        FROM "ci_namespace_mirrors"
                                                        WHERE (((traversal_ids[1])) IN
                                                               ((9970), (4909902), (7135830), (10450974), (14243611),
                                                                (14358022), (15547931),
                                                                (105979951), (105980080), (105987955), (105988036),
                                                                (104128816), (104128633), (104128639),
                                                                (104128641))))) ci_namespace_mirrors)
       SELECT "ci_runners".*
       FROM "ci_runners"
                INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
       WHERE (ci_runner_namespaces.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)))) ci_runners
ORDER BY "ci_runners"."id" ASC
LIMIT 20;

Query Plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/40528/commands/124702

Updated Query

Query:

SELECT "ci_runners".*
FROM ((SELECT "ci_runners".*
       FROM "ci_runners"
                INNER JOIN "ci_runner_projects" "ci_runner_projects"
                           ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
       WHERE "ci_runner_projects"."project_id" IN
             (13083, 13686280, 17115351, 21924610, 35947644, 39791822, 42319945, 51613969,
              51634462))
      UNION
      (WITH "cte_namespace_ids" AS MATERIALIZED (SELECT "ci_namespace_mirrors"."namespace_id"
                                                 FROM ((SELECT "ci_namespace_mirrors".*
                                                        FROM "ci_namespace_mirrors"
                                                        WHERE (((traversal_ids[1])) IN
                                                               ((9970), (4909902), (7135830), (10450974), (14243611),
                                                                (14358022), (15547931),
                                                                (105979951), (105980080), (105987955), (105988036),
                                                                (104128816), (104128633), (104128639),
                                                                (104128641))))) ci_namespace_mirrors)
       SELECT "ci_runners".*
       FROM "ci_runners"
                INNER JOIN "ci_runner_namespaces" ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
       WHERE (ci_runner_namespaces.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)))) ci_runners
ORDER BY "ci_runners"."id" ASC
LIMIT 20;

Query Plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/40528/commands/124703

DB Query Observation and analysis:

• Removed complex CTE query for group-level project access
• All the project_ids are loaded upfront and queried on index gitlabhq_development_ci.public.ci_runner_projects.index_ci_runner_projects_on_project_id
• Only concern is: the number of project_ids may grow, but the existing query is working in the same way.
• Before: Multiple CTE scans and complex nested loops
• After: No expensive merge operations

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.