Resolve project roles from direct and inherited memberships

Ian Andersonrequested to merge
632453-user-project-grants-memberships into master

What does this MR do and why?

  • This is an experiment behind the resolve_project_access_from_memberships feature flag (per user, default off). It only changes direct access checks, where we check one user's access to a specific project. Searches and lists that decide which projects a user can see still use project_authorizations.
  • Adds the first half of a resolver that finds every role a user holds on given projects, read directly from memberships: direct project memberships, memberships of the project's group and its ancestors, and personal projects.
  • This avoids project_authorizations, which keeps only the highest role per user and project and lags behind membership changes.
  • It only loads memberships that can grant access to the projects being checked, so cost doesn't grow with how many memberships a user has elsewhere. It uses the existing members index on user_id, source_id, source_type.
  • Shares come in the next MR. Nothing calls this yet.
  • Measured on a production clone (whole resolver): about 0-0.6 ms and 1-2 queries per project check for typical users. Users with 21k-108k memberships take 2.5 ms (1.6-1.8 ms today). Results matched project_authorizations in every comparison.
Query plans

Captured on a production database clone (Postgres 17) with EXPLAIN (ANALYZE, BUFFERS), warm. Long ID arrays are shortened.

Typical user (11 memberships), 20 projects: memberships

SELECT "members"."source_type", "members"."source_id", "members"."access_level", "members"."member_role_id" FROM "members" INNER JOIN unnest('{1749956,3612867,3810183,14620845,9264743,4261336,2700836,4450053,8382450,14676935,13339203,9183526,3593291,6585375,16079718,17393294,14677479,3647796,15306598,7104990,6336350,42356399}'::bigint[], '{22 types}'::text[]) AS wanted(source_id, source_type) ON wanted.source_id = members.source_id AND wanted.source_type = members.source_type WHERE "members"."user_id" = 502448 AND "members"."requested_at" IS NULL AND "members"."state" = 0
Nested Loop  (cost=0.57..79.48 rows=1 width=24) (actual time=0.027..0.074 rows=11 loops=1)
  Buffers: shared hit=99
  ->  Function Scan on wanted  (cost=0.01..0.23 rows=22 width=40) (actual time=0.007..0.009 rows=22 loops=1)
  ->  Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members  (cost=0.57..3.59 rows=1 width=24) (actual time=0.003..0.003 rows=0 loops=22)
        Index Cond: ((user_id = 502448) AND (source_id = wanted.source_id) AND ((source_type)::text = wanted.source_type))
        Filter: ((requested_at IS NULL) AND (state = 0))
        Buffers: shared hit=99
Planning Time: 0.190 ms
Execution Time: 0.093 ms

User with 108,218 memberships, gitlab-org/gitlab: memberships

SELECT "members"."source_type", "members"."source_id", "members"."access_level", "members"."member_role_id" FROM "members" INNER JOIN unnest('{147 ids}'::bigint[], '{147 types}'::text[]) AS wanted(source_id, source_type) ON wanted.source_id = members.source_id AND wanted.source_type = members.source_type WHERE "members"."user_id" = 36397453 AND "members"."requested_at" IS NULL AND "members"."state" = 0
Nested Loop  (cost=0.57..531.04 rows=1 width=24) (actual time=0.255..0.256 rows=0 loops=1)
  Buffers: shared hit=588
  ->  Function Scan on wanted  (cost=0.01..1.47 rows=147 width=40) (actual time=0.017..0.031 rows=147 loops=1)
  ->  Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members  (cost=0.57..3.59 rows=1 width=24) (actual time=0.001..0.001 rows=0 loops=147)
        Index Cond: ((user_id = 36397453) AND (source_id = wanted.source_id) AND ((source_type)::text = wanted.source_type))
        Filter: ((requested_at IS NULL) AND (state = 0))
        Buffers: shared hit=588
Planning Time: 0.221 ms
Execution Time: 0.278 ms

Member of gitlab-org/gitlab (147 relevant sources): memberships

SELECT "members"."source_type", "members"."source_id", "members"."access_level", "members"."member_role_id" FROM "members" INNER JOIN unnest('{147 ids}'::bigint[], '{147 types}'::text[]) AS wanted(source_id, source_type) ON wanted.source_id = members.source_id AND wanted.source_type = members.source_type WHERE "members"."user_id" = 1 AND "members"."requested_at" IS NULL AND "members"."state" = 0
Hash Join  (cost=4.25..116.16 rows=1 width=24) (actual time=0.091..0.345 rows=2 loops=1)
  Hash Cond: ((members.source_id = wanted.source_id) AND ((members.source_type)::text = wanted.source_type))
  Buffers: shared hit=253
  ->  Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members  (cost=0.57..111.84 rows=84 width=24) (actual time=0.018..0.260 rows=257 loops=1)
        Index Cond: (user_id = 1)
        Filter: ((requested_at IS NULL) AND (state = 0))
        Buffers: shared hit=253
  ->  Hash  (cost=1.47..1.47 rows=147 width=40) (actual time=0.055..0.056 rows=147 loops=1)
        Buckets: 1024  Batches: 1  Memory Usage: 16kB
        ->  Function Scan on wanted  (cost=0.01..1.47 rows=147 width=40) (actual time=0.018..0.034 rows=147 loops=1)
Planning Time: 0.201 ms
Execution Time: 0.367 ms

References

Screenshots or screen recordings

Not applicable, no UI changes.

How to set up and validate locally

  1. Open a Rails console and pick a user and a few projects without shares.
  2. Run Authz::UserProjectGrants.new(user.id).for_projects(ids).
  3. Run Authz::UserProjectGrants.new(user.id).max_access_levels(ids).
  4. Compare with user.project_authorizations.where(project_id: ids).pluck(:project_id, :access_level). The highest level per project should match.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Edited by Ian Anderson

Merge request reports