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:

  1. Create a top-level group top-group and a subgroup invited-sub under it.
  2. Create a project directly under top-group.
  3. Add a test user as Guest to top-group.
  4. Add the same user as Developer to invited-sub.
  5. Invite invited-sub to the project via Project > Members > Invite a group with max role Developer.
  6. Visit the project's Members page.
  7. Before fix: the user row shows "Inherited from top-group" as the source.
  8. After fix: the user row shows "invited-sub" as the source.

Invited group's max role is lower than the inherited role:

  1. Using a second test user, add them as Developer to top-group.
  2. Add the same user as Maintainer to invited-sub.
  3. Change the invited-sub share'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).
  4. 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:

  1. Create an unrelated group shared-parent with two subgroups high-link-group and low-link-group.
  2. Using a third test user, add them as Developer to shared-parent and as Reporter to top-group.
  3. Invite high-link-group to the project with max role Maintainer and low-link-group with max role Guest.
  4. 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:

  1. Using a fourth test user, add them as Developer to top-group.
  2. 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)
  3. Invite invited-sub to the project with max role Maintainer.
  4. 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):

  1. Cap invited-group access_level at selection time in member_columns_for_invited_members (called from project_invited_groups). Previously, rows from invited groups entered the union with their raw members.access_level, so a plain access_level DESC tie-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, via Member.shared_members) are already capped by LEAST(group_group_links.group_access, members.access_level). The fix adds a correlated subquery that resolves the maximum project_group_links.group_access for the links connecting the member's source group (or a descendant of it) to the project, using traversal_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 how project_authorizations computes it. This mirrors Member.shared_members / member_columns_for_shared_members.

  2. Prefer active, then highest access in distinct_on: after the type CASE, order by member_union.state ASC (active before awaiting) and then member_union.access_level DESC (now on a uniform scale). The state key is needed because awaiting rows (STATE_AWAITING = 1) carry no project_authorizations; letting one win over an active row (STATE_ACTIVE = 0) would show a misleading source and pending state.

  3. Stable id tie-break: among rows with equal type, state and access_level, the lowest member_union.id is 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:

  1. member_columns_for_invited_members (called from project_invited_groups): adds a correlated subquery to cap access_level by project_group_links.group_access.
  2. distinct_on: adds member_union.state ASC, member_union.access_level DESC, member_union.id ASC to the ORDER BY clause.

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
  END

Raw 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-break

Nature 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.

Edited by Ben Bodenmiller

Merge request reports

Loading
Loading