Cache member roles assigned to users in groups

Goals

• Improve performance of UserMemberRolesInGroupsPreloader and UserMemberRolesInProjectsPreloader by eliminating the complex query used to calculate a user's custom abilities for a specific group/project. Instead, the query will simply fetch member_role_ids of the user for specific groups in user_group_member_roles and joining them with member_roles records to get the actual custom abilities.

  • This is one of the suggested corrective actions for Huge increase in DB primary CPU utilization (#507925 - closed) (see comment)

  • With user_group_member_roles the query in UserMemberRolesInGroupsPreloader is simplified to:

    After

    SELECT
      namespace_ids.namespace_id,
      custom_permissions.permissions
    FROM (VALUES (<group_id>, ARRAY[<group_id>, <parent_group_ids>])) AS namespace_ids (namespace_id, namespace_ids),
      LATERAL (
        SELECT
          member_roles.permissions
        FROM
          user_group_member_roles
          INNER JOIN member_roles ON member_roles.id = user_group_member_roles.member_role_id
        WHERE (user_group_member_roles.group_id IN (
                SELECT
                    UNNEST(namespace_ids) AS ids))
            AND user_group_member_roles.user_id = <user_id>
      ) AS custom_permissions

    Before

    SELECT
      namespace_ids.namespace_id, custom_permissions.permissions
    FROM (VALUES (24, ARRAY[24])) AS namespace_ids (namespace_id, namespace_ids),
      LATERAL (
        SELECT
          member_roles.permissions
        FROM
          members
          JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id
          JOIN member_roles ON member_roles.id = group_group_links.member_role_id
        WHERE
          members.user_id = 76
          AND (
            group_group_links.shared_group_id IN (
              SELECT
                UNNEST (namespace_ids) as ids
            )
          )
          AND (access_level > group_access)
    
        UNION ALL
    
        SELECT
          member_roles.permissions
        FROM
          members
          JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id
          JOIN member_roles ON member_roles.id = members.member_role_id
        WHERE
          members.user_id = 76
          AND (
            group_group_links.shared_group_id IN (
              SELECT
                UNNEST (namespace_ids) as ids
            )
          )
          AND (
            (access_level < group_access)
            OR (
              access_level = group_access
              AND group_group_links.member_role_id IS NOT NULL
            )
          )
    
        UNION ALL
    
        SELECT
          member_roles.permissions
        FROM
          members
          INNER JOIN member_roles ON member_roles.id = members.member_role_id
        WHERE
          members.user_id = 76
          AND members.source_type = 'Namespace'
          AND (
            members.source_id IN (
              SELECT
                UNNEST (namespace_ids) as ids
            )
          )
      ) AS custom_permissions

• Centralize the calculation logic for a user's effective member role in a shared group~~/project~~

  • This logic is complex (see https://docs.gitlab.com/user/custom_roles/#assign-a-custom-role-to-an-invited-group) and is currently duplicated across multiple areas (1, 2, 3) of our code base which increases the risk of inconsistencies when updates are made to one area but not the others.

Implementation plan

1. ✅ Create user_group_member_roles table
   • records a member role assigned to a user in a group through
     • group membership
     • membership in another group invited to the group
   • columns: user_id, group_id, member_role_id, shared_with_group_id
   • purpose of shared_with_group_id column:
     • exclude member roles assigned to a user via group sharing when feature flag is disabled
     • determine which tables to join with (e.g. when nil - member role is from group membership (direct or inherited), when present - member role is from group sharing) to determine the source of the member role
2. ✅ Implement caching triggers (feature flag: cache_user_group_member_roles)
   1. On group membership change: created, updated, destroyed
   2. On group group link change: created, updated
   3. On group group link destroyed
   4. On user accept invite e.g. email address is invited to a group with a member
   5. On user's access request granted
   6. On group membership activated (Users::ActivateService)
   7. On group membership created on SamlGroupLink sync
   8. On group membership created on LdapGroupLink sync role
3. ✅ Rollout caching (FF cache_user_group_member_roles )
4. ✅ Backfill user_group_member_roles table
5. ✅ Implement cache correctness checking to establish confidence in using user_group_member_roles table (and this diff tracking is gated behind a new FF track_user_group_member_roles_accuracy )
   1. By logging discrepancies in the new table user_group_member_roles against the existing query
6. ✅ Update the code of the 2 classes: UserMemberRolesInGroupsPreloader and UserMemberRolesInProjectsPreloader to optionally (feature flag: use_user_group_member_roles) use user_group_member_roles table
7. ✅ Rollout track_user_group_member_roles_accuracy
8. Rollout use_user_group_member_roles