Skip to content

Cache member roles assigned to users in projects

Goals

  • Create a table to record custom role assignment to users in projects through direct membership and project sharing (i.e. a group the user is a member of is added as member with a custom role to a project)

  • Enable UserMemberRolesInProjectsPreloader to also return a user's custom abilities in projects granted through project sharing using a simpler query compared to the current query used in UserMemberRolesInGroupsPreloader (shown below).

    Query to calculate user's custom abilities in groups through direct membership and group sharing 
    def union_query
  union_queries = []

  member = Member.select('member_roles.permissions').with_user(user)

  group_member = member
    .joins(:member_role)
    .where(source_type: 'Namespace')
    .where('members.source_id IN (SELECT UNNEST(namespace_ids) as ids)')
    .to_sql

  if custom_role_for_group_link_enabled?
    group_link_join = member
      .joins('JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id')
      .where('group_group_links.shared_group_id IN (SELECT UNNEST(namespace_ids) as ids)')

    invited_member_role = group_link_join
      .joins('JOIN member_roles ON member_roles.id = group_group_links.member_role_id')
      .where('access_level > group_access')
      .to_sql

    # when both roles are custom roles with the same base access level,
    # choose the source role as the max role
    source_member_role = group_link_join
      .joins('JOIN member_roles ON member_roles.id = members.member_role_id')
      .where('(access_level < group_access) OR ' \
        '(access_level = group_access AND group_group_links.member_role_id IS NOT NULL)')
      .to_sql

    union_queries.push(invited_member_role, source_member_role)
  end

  union_queries.push(group_member)

  union_queries.join(" UNION ALL ")
end
    SELECT
    namespace_ids.namespace_id,
    custom_permissions.permissions
FROM (
    VALUES (67345893, ARRAY[65957873, 67345893]),
        (76931177, ARRAY[65957873, 76931177])) 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" = 24586657
            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" = 24586657
            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" = 24586657
            AND "members"."source_type" = 'Namespace'
            AND (members.source_id IN (
                    SELECT
                        UNNEST(namespace_ids) AS ids))
    ) AS custom_permissions

    The new query will fetch member_role_ids of the user for specific projects from user_project_member_roles table and joining them with member_roles records to get the actual custom abilities.

  • Follows the same caching implementation applied for UserMemberRolesInGroupsPreloader in Cache member roles assigned to users in groups (#513033) to prevent recurrence of Huge increase in DB primary CPU utilization (#507925 - closed) when Assign custom role when sharing a project to a ... (#468329) feature is globally enabled.

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

Implementation plan

  1. Create user_project_member_roles table
    • records a member role assigned to a user in a project through
      • project membership
      • membership in another group invited to the project
    • columns: user_id, project_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 project 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_project_member_roles)
    1. On project membership change: created, updated, destroyed
    2. On project group link change: created, updated
    3. On project group link destroyed
    4. On user accept invite e.g. email address is invited to a project with a member
    5. On user's access request granted
  3. Rollout caching
  4. Backfill user_project_member_roles table
  5. Update UserMemberRolesInProjectsPreloader to use user_project_member_roles table

References

  1. assign_custom_roles_to_project_links_saas - feature flag that enables assigning custom role to invited group in a project
Edited by Eugie Limpin