Skip to content

Remove join between ci_runner and main tables in ci_owned_runners

Adrien Kohlbecker requested to merge ak/ci-owned-runners into master

What does this MR do?

See #336436

As part of the initiative to extract the ci_* tables from the main database, we need to rework queries that join between the two. The User#ci_owned_runners method is currently joining ci_* with projects.

This MR reworks the query to first get the IDs of the runner belonging to a group or project, then returns a relation querying the ci_runner table.

Before/After

Before

SELECT "ci_runners".*
FROM (
        (SELECT ci_runners.*
         FROM "ci_runner_projects"
         INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
         WHERE "ci_runner_projects"."project_id" IN
             (SELECT "projects"."id"
              FROM "projects"
              INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
              WHERE "project_authorizations"."user_id" = 1
                AND (project_authorizations.access_level >= 40)))
      UNION
        (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"
                                                           INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
                                                           WHERE "members"."type" = 'GroupMember'
                                                             AND "members"."source_type" = 'Namespace'
                                                             AND "namespaces"."type" = 'Group'
                                                             AND "members"."user_id" = 1
                                                             AND "members"."requested_at" IS NULL
                                                             AND (access_level >= 10)
                                                             AND "members"."access_level" = 50)
                                                        UNION
                                                          (SELECT "namespaces".*
                                                           FROM "namespaces",
                                                                "base_and_descendants"
                                                           WHERE "namespaces"."type" = 'Group'
                                                             AND "namespaces"."parent_id" = "base_and_descendants"."id")) SELECT "id"
              FROM "base_and_descendants" AS "namespaces")))

After

SELECT "projects"."id"
FROM "projects"
INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
WHERE "project_authorizations"."user_id" = 1
  AND (project_authorizations.access_level >= 40);
WITH RECURSIVE "base_and_descendants" AS (
                                            (SELECT "namespaces".*
                                             FROM "namespaces"
                                             INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
                                             WHERE "members"."type" = 'GroupMember'
                                               AND "members"."source_type" = 'Namespace'
                                               AND "namespaces"."type" = 'Group'
                                               AND "members"."user_id" = 1
                                               AND "members"."requested_at" IS NULL
                                               AND (access_level >= 10)
                                               AND "members"."access_level" = 50)
                                          UNION
                                            (SELECT "namespaces".*
                                             FROM "namespaces",
                                                  "base_and_descendants"
                                             WHERE "namespaces"."type" = 'Group'
                                               AND "namespaces"."parent_id" = "base_and_descendants"."id"))
SELECT id
FROM "base_and_descendants" AS "namespaces";
SELECT "ci_runners".*
FROM (
        (SELECT ci_runners.*
         FROM "ci_runner_projects"
         INNER JOIN "ci_runners" ON "ci_runners"."id" = "ci_runner_projects"."runner_id"
         WHERE "ci_runner_projects"."project_id" IN (2,
                                                     3,
                                                     4,
                                                     1,
                                                     5,
                                                     7,
                                                     8,
                                                     6,
                                                     19))
      UNION
        (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 (22,
                                                         23,
                                                         24,
                                                         25,
                                                         26,
                                                         28,
                                                         52))) ci_runners

Screenshots or Screencasts (strongly suggested)

How to setup and validate locally (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

Does this MR contain changes to processing or storing of credentials or tokens, authorization and authentication methods or other items described in the security review guidelines? If not, then delete this Security section.

  • Label as security and @ mention @gitlab-com/gl-security/appsec
  • The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • Security reports checked/validated by a reviewer from the AppSec team
Edited by Adrien Kohlbecker

Merge request reports

Loading