List owned runners via :admin_runners permission

mo khanrequested to merge
mokhax/442851/ci-owner-runners-via-group-admin-runners into master

What does this MR do and why?

This change updates the list of runners owned by a user to include any runners accessible via group memberships that are associated with a custom role that has the admin_runners permission enabled. This change was extracted from !159657 (closed). This change does not include runners that are accessible through direct project memberships via a custom role. I will submit a change with that update in a follow-up MR. Also, note that this change is gated behind a feature flag.

Related to:

Before:

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]),(traversal_ids [2])) IN ((91486547, 91486554)))
    )
  ) 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))
Time: 3.746 ms
  - planning: 3.505 ms
  - execution: 0.241 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/30588/commands/94849

After:

WITH cte_namespace_ids AS MATERIALIZED (
  SELECT ci_namespace_mirrors.*
  FROM (
    (
      SELECT ci_namespace_mirrors.namespace_id
      FROM (
        (
          SELECT ci_namespace_mirrors.*
          FROM ci_namespace_mirrors
          WHERE (((traversal_ids [1]),(traversal_ids [2])) IN ((91486547, 91486554)))
        )
      ) ci_namespace_mirrors
    )
    UNION
    (
      SELECT ci_namespace_mirrors.namespace_id
      FROM (
        (
          SELECT ci_namespace_mirrors.*
          FROM ci_namespace_mirrors
          WHERE (((traversal_ids [1])) IN ((60357594)))
        )
      ) ci_namespace_mirrors
    )
  ) 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))
Time: 8.052 ms
  - planning: 3.301 ms
  - execution: 4.751 ms
    - I/O read: 4.302 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 56 (~448.00 KiB) from the buffer pool
  - reads: 3 (~24.00 KiB) from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/30588/commands/94848

MR acceptance checklist

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

Screenshots or screen recordings

Before:

モ curl -H "PRIVATE-TOKEN: $GITLAB_TOKEN" http://gdk.test:3000/api/v4/runners | jq '.'
[]

After:

モ curl -H "PRIVATE-TOKEN: $GITLAB_TOKEN" http://gdk.test:3000/api/v4/runners | jq '.'
[
  {
    "id": 26,
    "description": "example",
    "ip_address": null,
    "active": false,
    "paused": true,
    "is_shared": false,
    "runner_type": "project_type",
    "name": null,
    "online": null,
    "status": "stale"
  },
  {
    "id": 27,
    "description": "example",
    "ip_address": null,
    "active": false,
    "paused": true,
    "is_shared": false,
    "runner_type": "project_type",
    "name": null,
    "online": null,
    "status": "stale"
  },
  {
    "id": 32,
    "description": "",
    "ip_address": null,
    "active": true,
    "paused": false,
    "is_shared": false,
    "runner_type": "group_type",
    "name": null,
    "online": null,
    "status": "stale"
  },
  {
    "id": 39,
    "description": null,
    "ip_address": null,
    "active": true,
    "paused": false,
    "is_shared": false,
    "runner_type": "project_type",
    "name": null,
    "online": null,
    "status": "stale"
  },
  {
    "id": 40,
    "description": null,
    "ip_address": null,
    "active": true,
    "paused": false,
    "is_shared": false,
    "runner_type": "group_type",
    "name": null,
    "online": null,
    "status": "stale"
  }
]

Production

before:

user = User.find_by(username: "xlgmokha")
user.send(:ci_owned_group_runners).count
[ gprd ] production> user = User.find_by(username: "xlgmokha")
[ gprd ] production> user.send(:ci_owned_group_runners).count
=> 0

after:

user = User.find_by(username: "xlgmokha")
permission = :admin_runners

cte_namespace_ids = ::Gitlab::SQL::CTE.new(
  :cte_namespace_ids,
  ::Ci::NamespaceMirror.from_union([
    user.send(:ci_namespace_mirrors_for_group_members, ::Gitlab::Access::OWNER).select(:namespace_id),
    ::Ci::NamespaceMirror.contains_traversal_ids(
      user.authorized_groups.where(
        'traversal_ids[1] IN (?)',
        user.group_members
          .joins(:member_role)
          .merge(::MemberRole.permissions_where(permission => true))
          .select('member_roles.namespace_id')
      ).shortest_traversal_ids_prefixes
    ).select(:namespace_id)
  ])
)

::Ci::Runner
  .with(cte_namespace_ids.to_arel)
  .joins(:runner_namespaces)
  .where('ci_runner_namespaces.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)')
  .count
[ gprd ] production> user = User.find_by(username: "xlgmokha")
=> #<User id:971158 @xlgmokha>
[ gprd ] production> permission = :admin_runners
=> :admin_runners
[ gprd ] production>
?> cte_namespace_ids = ::Gitlab::SQL::CTE.new(
?>   :cte_namespace_ids,
?>   ::Ci::NamespaceMirror.from_union([
?>     user.send(:ci_namespace_mirrors_for_group_members, ::Gitlab::Access::OWNER).select(:namespace_id),
?>     ::Ci::NamespaceMirror.contains_traversal_ids(
?>       user.authorized_groups.where(
?>         'traversal_ids[1] IN (?)',
?>         user.group_members
[ gprd ] production>           .joins(:member_role)
[ gprd ] production>           .merge(::MemberRole.permissions_where(permission => true))
[ gprd ] production>           .select('member_roles.namespace_id')
[ gprd ] production>       ).shortest_traversal_ids_prefixes
[ gprd ] production>     ).select(:namespace_id)
[ gprd ] production>   ])
[ gprd ] production> )
=>
#<Gitlab::SQL::CTE:0x00007fd0d3f93a70
...
[ gprd ] production>
[ gprd ] production> ::Ci::Runner
[ gprd ] production>   .with(cte_namespace_ids.to_arel)
[ gprd ] production>   .joins(:runner_namespaces)
[ gprd ] production>   .where('ci_runner_namespaces.namespace_id IN (SELECT namespace_id FROM cte_namespace_ids)')
[ gprd ] production>   .count
=> 2

How to set up and validate locally

  1. In rails console enable the experiment fully

    Feature.enable(:custom_ability_admin_runners)

  2. Visit any root group

  3. Create group runners at different levels of the group hierarchy.

  4. Create project runners for different projects in and outside of the chosen group hierarchy.

  5. Create a new role role with the base role of Guest and the :admin_runners permission.

  6. Assign the role to a new user.

  7. Generate a PAT for the new user.

  8. Make a curl request to runners API

    $ curl --header "PRIVATE-TOKEN: <your_access_token>" "https://gitlab.example.com/api/v4/runners"

  9. Verify that all the expected (through group membership) runners are returned in the JSON response.

Edited by mo khan

Merge request reports