Draft: [skip ci] Resolve team access levels from memberships

What does this MR do and why?

Features that look up many users on one project (notifications, mentions, approvers, code owners, assignee rendering) still read project_authorizations. A user with resolve_project_access_from_memberships on could therefore get a different answer there than in their own permission checks.

Behind the same per-user flag, those users are now resolved from memberships and shares in one query, however many users there are. Users without the flag keep the current lookup. When the flag is fully on, the per-user flag check is skipped.

  • Adds Authz::ProjectTeamGrants. It maps the groups that can grant access to the project once, then looks up only each user's memberships in those groups. It follows the same rules as Authz::UserProjectGrants, and its spec checks every rule against both UserProjectGrants and Gitlab::ProjectAuthorizations.
  • The membership query looks each user up on their own. An earlier version joined the users list to the groups list, and Postgres sometimes picked plans that read every membership of the groups, or every membership of the users: 22.6 s for 10 users with 9k–118k memberships each, and 340 ms for 100 users on a project whose group has 215 subgroups.

Typical lookups are small. On a production clone I sampled 400 recent comments on issues and merge requests, and took the users a lookup there checks (note authors, the author and assignees). The median lookup checks 2 users on a project with 3 groups that can grant access; the 99th percentile is 8 users and 27 groups.

Database time, 400 sampled lookups Mean p50 p99
Today (project_authorizations) 0.19 ms 0.18 ms 0.26 ms
This MR 0.28 ms 0.27 ms 0.49 ms
The earlier join 0.47 ms 0.39 ms 1.9 ms

Large lookups, database time on the clone (warm, planning plus execution):

Lookup Today (project_authorizations) This MR
gitlab-org/gitlab (151 groups grant access), 10 random users 0.26 ms 0.43 ms
gitlab-org/gitlab, 100 random users 0.87 ms 1.7 ms
gitlab-org/gitlab, 1,000 random users 6.9 ms 12.7 ms
Project in one large group (215 groups, about 29k memberships), 100 users 0.9 ms 1.7 ms
The 10 members of gitlab-org/gitlab with the most memberships 0.05 ms 1.4 ms

The cost grows by about 12 µs per user, so lookups of about 1,000 users are slower than today. When the project's groups and shares aren't cached for the request yet, the lookup also runs the 3 project queries UserProjectGrants already uses. On the clone, the results matched project_authorizations for every user at 10, 100 and 1,000 users on two projects, including users without access.

Designs that were measured and rejected
  • Joining users and groups (this MR's previous version): fine on most projects, but Postgres can pick the plans above. Even its good plan reads every membership of the groups: about 9 ms on gitlab-org/gitlab for 100 users.
  • Resolving each user through Authz::UserProjectGrants instead of a second copy of the rules: about 14 times slower at 1,000 users locally, because each user re-walks the project's groups and shares.
  • Covering indexes on members: one starting from the group made 100 and 1,000 users faster (about 3 ms), but made 10 users slower (about 3 ms instead of 0.6 ms), because Postgres switched plans. One starting from the user saved about 20%. Neither is worth an extra 3 GB index on members.
Known gaps
  • Preloaders::UsersMaxAccessLevelByProjectPreloader (used for DAST profiles) still writes project_authorizations values into the same cache.
Query plans

Captured on a production database clone (Postgres 17) with EXPLAIN (ANALYZE, BUFFERS), warm. ID arrays are shortened. Only the membership query is new; the project queries are the ones Authz::UserProjectGrants already runs.

SELECT "members"."user_id", "members"."source_type", "members"."source_id", "members"."access_level" FROM unnest('{user ids}'::bigint[]) AS wanted_users(id) CROSS JOIN LATERAL (SELECT "members"."user_id", "members"."source_type", "members"."source_id", "members"."access_level" FROM "members" WHERE "members"."requested_at" IS NULL AND "members"."state" = 0 AND (members.user_id = wanted_users.id) AND (members.source_id = ANY((SELECT '{sorted source ids}'::bigint[])::bigint[])) OFFSET 0) members

10 users on gitlab-org/gitlab (151 source ids)

Nested Loop (actual time=0.051..0.332 rows=43 loops=1)
  Buffers: shared hit=119
  ->  Function Scan on unnest wanted_users (actual time=0.006..0.007 rows=10 loops=1)
  ->  Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members (actual time=0.015..0.031 rows=4 loops=10)
        Index Cond: ((user_id = wanted_users.id) AND (source_id = ANY ((InitPlan 1).col1)))
        Filter: ((requested_at IS NULL) AND (state = 0))
        Buffers: shared hit=119
        InitPlan 1
          ->  Result (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.262 ms
Execution Time: 0.370 ms

100 users on gitlab-org/gitlab (151 source ids)

Nested Loop (actual time=0.040..2.144 rows=380 loops=1)
  Buffers: shared hit=1061
  ->  Function Scan on unnest wanted_users (actual time=0.008..0.016 rows=100 loops=1)
  ->  Index Scan using idx_members_on_user_and_source_and_source_type_and_member_role on members (actual time=0.012..0.021 rows=4 loops=100)
        Index Cond: ((user_id = wanted_users.id) AND (source_id = ANY ((InitPlan 1).col1)))
        Filter: ((requested_at IS NULL) AND (state = 0))
        Buffers: shared hit=1061
        InitPlan 1
          ->  Result (actual time=0.001..0.001 rows=1 loops=1)
Planning Time: 0.191 ms
Execution Time: 2.191 ms

References

Screenshots or screen recordings

Not applicable, no UI changes.

How to set up and validate locally

  1. Enable the flag for a user in a Rails console: Feature.enable(:resolve_project_access_from_memberships, user).
  2. Mention that user on an issue, or open a merge request page that lists them, and check their access level matches their memberships.
  3. In the console, compare project.team.max_member_access_for_user_ids(ids) with the flag on and off for the same users.

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 Ian Anderson

Merge request reports

Loading
Loading