Fix wrong membership source shown for project members who have access through an invited group
What does this MR do and why?
On a project's Members page (and in the project members API), every member has a Source: a direct membership, a membership inherited from a parent group, or a group that was invited to the project.
When a user has access to the same project in more than one way, for example as Guest through the parent group and as Developer through an invited subgroup, GitLab shows the correct role (Developer) but could show the wrong source ("Inherited from parent group" instead of the invited subgroup). Which source was shown depended on database row order, so it could even differ between page loads.
After this change:
- For members whose access comes through groups (inherited from a parent group, or through an invited group), the source shown is the membership that grants the user's effective role in the project.
- A direct project membership is still always shown as the source when one exists, as before.
- When two group memberships grant the same role, the result is stable across requests.
- A membership that is still awaiting approval no longer takes precedence over an active membership when choosing the source. Users whose only membership is awaiting approval are still listed with the Pending badge.
References
Closes #545196
How to set up and validate locally
Scenario from the issue:
- Create a top-level group
top-groupand a subgroupinvited-subunder it. - Create a project directly under
top-group. - Add a test user as Guest to
top-group. - Add the same user as Developer to
invited-sub. - Invite
invited-subto the project via Project > Members > Invite a group with max role Developer. - Visit the project's Members page.
- Before fix: the user row shows "Inherited from top-group" as the source.
- After fix: the user row shows "invited-sub" as the source.
Invited group's max role is lower than the inherited role:
- Using a second test user, add them as Developer to
top-group. - Add the same user as Maintainer to
invited-sub. - Change the
invited-subshare's max role to Guest (this also changes the first user's row from the previous scenario; use a fresh project if you want to keep both visible). - After fix: the user row shows "top-group" as the source with Developer access. Through the invited group the user would only be Guest, so the inherited Developer membership is the effective one.
Same parent group invited through two subgroups with different max roles:
- Create an unrelated group
shared-parentwith two subgroupshigh-link-groupandlow-link-group. - Using a third test user, add them as Developer to
shared-parentand as Reporter totop-group. - Invite
high-link-groupto the project with max role Maintainer andlow-link-groupwith max role Guest. - After fix: the user row shows "shared-parent" as the source with Developer access. The best link (Maintainer) caps the Developer membership to Developer, which beats the inherited Reporter membership.
Awaiting membership with higher access:
- Using a fourth test user, add them as Developer to
top-group. - Add the same user as Maintainer to
invited-sub, then mark that membership as awaiting approval from the Rails console:GroupMember.find_by(user: user, source: invited_sub).update!(state: Member::STATE_AWAITING) - Invite
invited-subto the project with max role Maintainer. - After fix: the user row shows "top-group" as the source with Developer access and is not shown as pending. Awaiting memberships grant no access, so the active inherited membership is the effective one.
Technical details
Root cause: MembersFinder#distinct_on builds a SELECT DISTINCT ON (user_id, invite_email) over a union of membership rows. The ORDER BY only
broke ties by member type (ProjectMember < GroupMember < other). When two
competing rows are both GroupMember (e.g. inherited Guest from the top-level
group AND Developer via an invited subgroup), PostgreSQL could pick either row
non-deterministically. The access_level column is coalesced from
project_authorizations, so the displayed role was correct, but the source
(source_id / source_type) came from whichever row was selected -- often the
lower-access inherited row.
Fix (three parts):
-
Cap invited-group
access_levelat selection time inmember_columns_for_invited_members(called fromproject_invited_groups). Previously, rows from invited groups entered the union with their rawmembers.access_level, so a plainaccess_level DESCtie-breaker would have compared values on different scales: inherited rows carry the effective access, and rows for groups shared into the project's ancestor groups (shared_into_ancestors, viaMember.shared_members) are already capped byLEAST(group_group_links.group_access, members.access_level). The fix adds a correlated subquery that resolves the maximumproject_group_links.group_accessfor the links connecting the member's source group (or a descendant of it) to the project, usingtraversal_ids @> ARRAY[source_id]. MAX is correct because a member of an ancestor group is inherited into every invited descendant group, and the user's effective access via sharing is the best available link, matching howproject_authorizationscomputes it. This mirrorsMember.shared_members/member_columns_for_shared_members. -
Prefer active, then highest access in
distinct_on: after the typeCASE, order bymember_union.state ASC(active before awaiting) and thenmember_union.access_level DESC(now on a uniform scale). Thestatekey is needed because awaiting rows (STATE_AWAITING = 1) carry noproject_authorizations; letting one win over an active row (STATE_ACTIVE = 0) would show a misleading source and pending state. -
Stable
idtie-break: among rows with equal type, state and access_level, the lowestmember_union.idis selected, making the result fully deterministic.
Not changed here: the EE custom-role variant of the group-share branch
(EE::Member.shared_members_with_member_role) selects the raw
members.access_level without the group_group_links.group_access cap. That is
pre-existing and only affects shared_into_ancestors rows on instances with
custom roles; it is out of scope for this fix.
Database review
This MR modifies MembersFinder in two places:
member_columns_for_invited_members(called fromproject_invited_groups): adds a correlated subquery to capaccess_levelbyproject_group_links.group_access.distinct_on: addsmember_union.state ASC, member_union.access_level DESC, member_union.id ASCto theORDER BYclause.
What changed
File: app/finders/members_finder.rb
Change 1 -- member_columns_for_invited_members: The access_level column
expression now wraps members.access_level in
LEAST(<correlated subquery>, members.access_level). The subquery finds the
maximum group_access from project_group_links where the linked group is a
descendant-or-self of the member's source group (via
traversal_ids @> ARRAY[source_id]; both namespaces.traversal_ids and
members.source_id are bigint). LEAST ignores NULLs, so a row whose source
has no matching link keeps its raw access_level.
Change 2 -- distinct_on: The ORDER BY inside the SELECT DISTINCT ON
query gained three additional sort keys after the type CASE:
member_union.state ASC (active before awaiting), member_union.access_level DESC, and member_union.id ASC.
Raw SQL -- before
-- project_invited_groups branch of the union (simplified):
SELECT
id, source_id, source_type, user_id, type,
access_level, -- raw members.access_level, NOT capped
NULL AS member_role_id,
...other columns...
FROM members
WHERE source_id IN (<invited_groups_including_ancestors>)
AND type = 'GroupMember'
AND access_level != 5
AND requested_at IS NULL
-- outer DISTINCT ON query:
SELECT DISTINCT ON (user_id, invite_email)
member_union.id,
COALESCE(project_authorizations.access_level, member_union.access_level) AS access_level,
member_union.source_id, member_union.source_type, member_union.user_id,
...other columns...
FROM (<union of all branches>) AS member_union
LEFT JOIN project_authorizations
ON project_authorizations.user_id = member_union.user_id
AND project_authorizations.project_id = <project_id>
ORDER BY
user_id,
invite_email,
CASE
WHEN type = 'ProjectMember' THEN 1
WHEN type = 'GroupMember' THEN 2
ELSE 3
ENDRaw SQL -- after (this MR)
-- project_invited_groups branch of the union (simplified):
SELECT
id, source_id, source_type, user_id, type,
LEAST(
(SELECT MAX(project_group_links.group_access)
FROM project_group_links
JOIN namespaces link_ns ON link_ns.id = project_group_links.group_id
WHERE project_group_links.project_id = <project_id>
AND link_ns.traversal_ids @> ARRAY[members.source_id]),
members.access_level
) AS access_level, -- capped by best link group_access
NULL AS member_role_id,
...other columns...
FROM members
WHERE source_id IN (<invited_groups_including_ancestors>)
AND type = 'GroupMember'
AND access_level != 5
AND requested_at IS NULL
-- outer DISTINCT ON query:
SELECT DISTINCT ON (user_id, invite_email)
member_union.id,
COALESCE(project_authorizations.access_level, member_union.access_level) AS access_level,
member_union.source_id, member_union.source_type, member_union.user_id,
...other columns...
FROM (<union of all branches>) AS member_union
LEFT JOIN project_authorizations
ON project_authorizations.user_id = member_union.user_id
AND project_authorizations.project_id = <project_id>
ORDER BY
user_id,
invite_email,
CASE
WHEN type = 'ProjectMember' THEN 1
WHEN type = 'GroupMember' THEN 2
ELSE 3
END,
member_union.state ASC, -- NEW: active before awaiting
member_union.access_level DESC, -- NEW: prefer higher capped access_level
member_union.id ASC -- NEW: stable tie-breakNature of the change
Correlated subquery in the union branch: The subquery executes once per row
in the invited-groups branch, so its total cost scales with the number of
members in the shared groups (including their ancestors), not with the number
of links. Each execution is bounded by the project's project_group_links rows
(typically single-digit). It is expected to be driven by
index_project_group_links_on_project_id plus the namespaces primary-key
join; index_namespaces_on_traversal_ids (GIN) is not expected to be used for
the containment check.
If preferred, the per-row subquery can be replaced by a single LATERAL join
aggregated per source_id; the semantics would be identical.
ORDER BY additions: state ASC, access_level DESC, and id ASC are
applied on the materialised result of the union subquery (an in-memory/temp-file
sort). No additional table access is required.
Query plan analysis
Expected plan shape (static prediction, not yet measured): the outer query is
unchanged, a Unique / Sort over a Hash Left Join between the union
Append and project_authorizations. The invited-groups union branch gains a
correlated SubPlan per row.
The author is a community contributor without postgres.ai / Database Lab
access. Database reviewer: please run EXPLAIN (ANALYZE, BUFFERS) for the
before and after queries against a project shared with a large group (for
example within the gitlab-org namespace, project ID 278964 or 13083) so
the invited-groups branch is non-trivial, and check the concerns below.
Potential concerns
| Concern | Likelihood | Check |
|---|---|---|
Sequential scan on members |
Low -- inner branches filtered by source_id with existing indexes |
EXPLAIN |
| Correlated subquery cost | Medium for projects shared with very large groups -- one execution per invited-member row | EXPLAIN on a large shared group; LATERAL alternative available |
| Sort spill to disk | Low -- result set bounded by project membership count | work_mem adequate |
| Execution time regression | Low -- subquery is small; sort adds three integer comparisons | < 100 ms |
Plan flip on .count / .exists? |
Callers (members page, API) paginate and count over this relation | Plan stable for both the paginated SELECT and the COUNT(*) wrapper |
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.