Issue #26426 Drafting a solution for the retrieval of runners that belong to...

What does this MR do?

This MR implements the group ownership inheritance for runners so that information for runners belonging to children groups can be obtained by the owners of the parent projects through the API.

This merge request was created after deleting the branch that was holding these changes and rebasing it. It inherits the status of this merge request !19381 (closed)

Database changes needed

The main change in the code happens in the User model in the method that retrieves the ci owned runners. There, the namespace_id changes from owned_groups to owned_or_maintainers_groups taken from self and descendants:

This means, the database request changes from:

SELECT
    ci_runners.*
FROM
    "ci_runner_namespaces"
    INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_namespaces"."runner_id"
WHERE
    "ci_runner_namespaces"."namespace_id" IN (
        SELECT
            "namespaces"."id"
        FROM
            "namespaces"
            INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
        WHERE
            "members"."type" = $1
            AND "members"."source_type" = $2
            AND "namespaces"."type" = $3
            AND "members"."user_id" = $4
            AND "members"."requested_at" IS NULL
            AND "members"."access_level" = $5)

to

SELECT
    ci_runners.*
FROM
    "ci_runner_namespaces"
    INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_namespaces"."runner_id"
WHERE
    "ci_runner_namespaces"."namespace_id" IN ( WITH RECURSIVE "base_and_descendants" AS (
(
                SELECT
                    "namespaces".*
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."id" = 531)
            UNION (
                SELECT
                    "namespaces".*
                FROM
                    "namespaces",
                    "base_and_descendants"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND "namespaces"."parent_id" = "base_and_descendants"."id"))
        SELECT
            "namespaces"."id"
        FROM
            "base_and_descendants" AS "namespaces")

The Query plan changes complexity from:

QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=16.56..28.94 rows=10 width=330)
   ->  Nested Loop  (cost=16.42..27.08 rows=10 width=4)
         ->  HashAggregate  (cost=12.19..12.20 rows=1 width=8)
               Group Key: namespaces.id
               ->  Nested Loop  (cost=0.15..12.19 rows=1 width=8)
                     ->  Seq Scan on namespaces  (cost=0.00..4.00 rows=1 width=4)
                           Filter: ((type)::text = 'Group'::text)
                     ->  Index Scan using index_members_on_source_id_and_source_type on members  (cost=0.15..8.18 rows=1 width=4)
                           Index Cond: ((source_id = namespaces.id) AND ((source_type)::text = 'Namespace'::text))
                           Filter: ((requested_at IS NULL) AND ((type)::text = 'GroupMember'::text) AND (user_id = 469) AND (access_level = 50))
         ->  Bitmap Heap Scan on ci_runner_namespaces  (cost=4.23..14.79 rows=10 width=8)
               Recheck Cond: (namespace_id = namespaces.id)
               ->  Bitmap Index Scan on index_ci_runner_namespaces_on_namespace_id  (cost=0.00..4.23 rows=10 width=0)
                     Index Cond: (namespace_id = namespaces.id)
   ->  Index Scan using ci_runners_pkey on ci_runners  (cost=0.14..0.19 rows=1 width=330)
         Index Cond: (id = ci_runner_namespaces.runner_id)
(16 rows)

to

QUERY PLAN
---------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=62.24..99.54 rows=112 width=330)
   Hash Cond: (ci_runner_namespaces.runner_id = ci_runners.id)
   ->  Hash Semi Join  (cost=47.29..84.29 rows=112 width=4)
         Hash Cond: (ci_runner_namespaces.namespace_id = namespaces.id)
         ->  Seq Scan on ci_runner_namespaces  (cost=0.00..30.40 rows=2040 width=8)
         ->  Hash  (cost=47.15..47.15 rows=11 width=4)
               ->  CTE Scan on base_and_descendants namespaces  (cost=46.82..47.04 rows=11 width=4)
                     CTE base_and_descendants
                       ->  Recursive Union  (cost=0.00..46.82 rows=11 width=488)
                             ->  Seq Scan on namespaces namespaces_1  (cost=0.00..4.00 rows=1 width=488)
                                   Filter: (((type)::text = 'Group'::text) AND (id = 531))
                             ->  Hash Join  (cost=4.01..4.26 rows=1 width=488)
                                   Hash Cond: (base_and_descendants.id = namespaces_2.parent_id)
                                   ->  WorkTable Scan on base_and_descendants  (cost=0.00..0.20 rows=10 width=4)
                                   ->  Hash  (cost=4.00..4.00 rows=1 width=488)
                                         ->  Seq Scan on namespaces namespaces_2  (cost=0.00..4.00 rows=1 width=488)
                                               Filter: ((type)::text = 'Group'::text)
   ->  Hash  (cost=12.20..12.20 rows=220 width=330)
         ->  Seq Scan on ci_runners  (cost=0.00..12.20 rows=220 width=330)
(19 rows)