Ensure only projects from same org can be assigned to the runner

Narendranrequested to merge
narendran-kannan/538369/runner-assignable-for-same-org into master

What does this MR do and why?

Ensure only projects from same org can be assigned to the runner

References

Ensure only projects from the same organization... (#538369 - closed)

How to set up and validate locally

  • Create orgs A and B
  • Create groups and projects in both orgs
  • Create a runner in each project
  • Open project -> Settings -> CI/CD Settings,
  • Toggle the runner section and check the available runners
  • Runner created in org B should not be visible in org As project runners page and vice versa.

Query Plan:

old 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 (11226612, 12679731, 14217480, 14217893, 14523499, 14623925, 14695587, 15057438, 15134738, 15427193, 15769916, 15770122, 15770379, 15770690, 15770837, 15770869, 15771030, 15771336, 15772451, 15773709, 15775324, 15777412, 15779973, 15780180, 15826068, 15828772, 15863770, 15878590, 15878732, 15879151, 16013488, 16185257, 16188547, 16200710, 16975862, 17305822, 17383495, 17544888, 18112310, 18112343, 18134060, 18284261, 18284289, 18284299, 18284310, 18510783, 18578481, 18667783, 18765508, 18765525, 18765529, 18765535, 18765542, 19173867, 19173907, 19176112, 19197138, 19263978, 19521745, 19819978, 19919472, 20100943, 20184450, 20592692, 20592724, 20592744, 20602475, 20626099, 20628162, 20709698, 20725510, 21037187, 21350114, 21515097, 21528788, 21585500, 21684369, 21958622, 21959393, 21959591, 22066990, 22392728, 22419444, 22419459, 22439176, 22574449, 22649128, 23128733, 23266120, 23403511, 23455032, 23647300, 23747300, 23814244, 23920765, 23944429, 23950831, 24079831, 24322333, 24410388, 24455746, 24466000, 24514153, 24602592, 24727636, 24912821, 25065058, 25686755, 25865803, 25895941, 26070090, 26124889, 26450563, 26463678, 26547569, 26576194, 26576195, 26580506, 26612832, 26656597, 26719651, 26749071, 26803982, 26887563, 26895593, 26917273, 26921643, 27090372, 27263582, 27310658, 27315710, 27602319, 27682285, 27761329, 27871043, 28006149, 28084808, 28091091, 28174510, 28291441, 28333480, 28424309, 28744484, 28848404, 29133723, 29245686, 29852570, 29882836, 29982452, 30045672, 30298418, 30491325, 30712584, 30860308, 30890084, 31085396, 31188999, 31253271, 31337368, 31550445, 31551537, 31551539, 31731280, 31737403, 31913519, 31932016, 31932017, 31945820, 32677582, 32806442, 32881963, 32931557, 32933116, 32985147, 33075381, 33123833, 33124168, 33139772, 33158667, 33290085, 33303114, 33368699, 33401022, 34018065, 34103499, 34778693, 34911225, 35019847, 35081107, 35121884, 35394953, 35456802, 35522155, 35581880, 35671131, 35916167, 36052691, 36080153, 36199112, 36524458, 36643335, 37056586, 37063629, 37494361, 37854029, 37862588, 38548595, 38817835, 40939479, 41468891, 41983441, 42504162, 42744623, 43305959, 43455540, 43677511, 44110635, 44551578, 46269561, 46304952, 46520224, 47122419, 47122521, 47122559, 47275870, 47888711, 47981447, 48144981, 48238873, 48287907, 48289334, 48313495, 48575905, 48608619, 48608989, 48678463, 48811910, 48811931, 48874805, 48959458, 48959519, 48959539))
      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
INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
where  "ci_runner_projects"."project_id" NOT IN (11226611, 11226610, 11226609, 11226608, 11226607, 11226606, 11226605, 11226604)
ORDER BY "ci_runners"."id" ASC
LIMIT 20;

Query Plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/42450/commands/129967

new 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 (11226612, 12679731, 14217480, 14217893, 14523499, 14623925, 14695587, 15057438, 15134738, 15427193, 15769916, 15770122, 15770379, 15770690, 15770837, 15770869, 15771030, 15771336, 15772451, 15773709, 15775324, 15777412, 15779973, 15780180, 15826068, 15828772, 15863770, 15878590, 15878732, 15879151, 16013488, 16185257, 16188547, 16200710, 16975862, 17305822, 17383495, 17544888, 18112310, 18112343, 18134060, 18284261, 18284289, 18284299, 18284310, 18510783, 18578481, 18667783, 18765508, 18765525, 18765529, 18765535, 18765542, 19173867, 19173907, 19176112, 19197138, 19263978, 19521745, 19819978, 19919472, 20100943, 20184450, 20592692, 20592724, 20592744, 20602475, 20626099, 20628162, 20709698, 20725510, 21037187, 21350114, 21515097, 21528788, 21585500, 21684369, 21958622, 21959393, 21959591, 22066990, 22392728, 22419444, 22419459, 22439176, 22574449, 22649128, 23128733, 23266120, 23403511, 23455032, 23647300, 23747300, 23814244, 23920765, 23944429, 23950831, 24079831, 24322333, 24410388, 24455746, 24466000, 24514153, 24602592, 24727636, 24912821, 25065058, 25686755, 25865803, 25895941, 26070090, 26124889, 26450563, 26463678, 26547569, 26576194, 26576195, 26580506, 26612832, 26656597, 26719651, 26749071, 26803982, 26887563, 26895593, 26917273, 26921643, 27090372, 27263582, 27310658, 27315710, 27602319, 27682285, 27761329, 27871043, 28006149, 28084808, 28091091, 28174510, 28291441, 28333480, 28424309, 28744484, 28848404, 29133723, 29245686, 29852570, 29882836, 29982452, 30045672, 30298418, 30491325, 30712584, 30860308, 30890084, 31085396, 31188999, 31253271, 31337368, 31550445, 31551537, 31551539, 31731280, 31737403, 31913519, 31932016, 31932017, 31945820, 32677582, 32806442, 32881963, 32931557, 32933116, 32985147, 33075381, 33123833, 33124168, 33139772, 33158667, 33290085, 33303114, 33368699, 33401022, 34018065, 34103499, 34778693, 34911225, 35019847, 35081107, 35121884, 35394953, 35456802, 35522155, 35581880, 35671131, 35916167, 36052691, 36080153, 36199112, 36524458, 36643335, 37056586, 37063629, 37494361, 37854029, 37862588, 38548595, 38817835, 40939479, 41468891, 41983441, 42504162, 42744623, 43305959, 43455540, 43677511, 44110635, 44551578, 46269561, 46304952, 46520224, 47122419, 47122521, 47122559, 47275870, 47888711, 47981447, 48144981, 48238873, 48287907, 48289334, 48313495, 48575905, 48608619, 48608989, 48678463, 48811910, 48811931, 48874805, 48959458, 48959519, 48959539))
      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
INNER JOIN "ci_runner_projects" ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
where  "ci_runner_projects"."project_id" NOT IN (11226611, 11226610, 11226609, 11226608, 11226607, 11226606, 11226605, 11226604)
AND
    "ci_runners".organization_id = 1

ORDER BY "ci_runners"."id" ASC
LIMIT 20;

Query Plan: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/42450/commands/129968

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.

Edited by Narendran

Merge request reports