Use user_group_member_roles table to fetch user custom role permissions

What does this MR do and why?

Add use_user_group_member_roles feature flag to toggle between using the existing query and the new query (introduced in !199106 (merged), fetches from user_group_member_roles table) in UserMemberRolesInGroupsPreloader and UserMemberRolesInProjecsPreloader.

References

Update UserMemberRolesIn*Preloader to read from... (#515216 - closed).

DB changes

UserMemberRolesInGroupsPreloader

New Query

Raw SQL

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
            "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" = 24586657) AS custom_permissions;

• EXPLAIN: https://console.postgres.ai/shared/1753df52-333a-48b1-9fcb-7690f7d5f844
• EXPLAIN (groups.count == 1_000): https://console.postgres.ai/shared/05b00626-2944-4b17-a9c1-c6efd491ade3

Existing Query

Raw SQL

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

EXPLAIN: https://console.postgres.ai/shared/59bf5b52-80c9-40e0-bac0-13fbafe7340c

UserMemberRolesInProjectsPreloader

New Query

Raw SQL

SELECT
    project_ids.project_id,
    custom_permissions.permissions
FROM (
    VALUES (62590724, ARRAY[79430960])) AS project_ids (project_id, namespace_ids),
    LATERAL (
        SELECT
            "member_roles"."permissions"
        FROM
            "members"
            INNER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
        WHERE
            "members"."user_id" = 154136
            AND "members"."source_type" = 'Project'
            AND (members.source_id = project_ids.project_id)
        UNION ALL
        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(project_ids.namespace_ids) AS ids))
            AND "user_group_member_roles"."user_id" = 154136) AS custom_permissions

• EXPLAIN: https://console.postgres.ai/shared/7df22334-9083-4538-b30d-d109782c5de4
• EXPLAIN (project.count == 1_000): https://console.postgres.ai/shared/7e24450b-7aba-4122-ba84-a76ceaed561d

Existing Query

Raw SQL

SELECT
    project_ids.project_id,
    custom_permissions.permissions
FROM (
    VALUES (62590724, ARRAY[79430960])) AS project_ids (project_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" = 154136
            AND (group_group_links.shared_group_id IN (
                    SELECT
                        UNNEST(project_ids.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" = 154136
                AND (group_group_links.shared_group_id IN (
                        SELECT
                            UNNEST(project_ids.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" = 154136
                    AND "members"."source_type" = 'Project'
                    AND (members.source_id = project_ids.project_id)
                UNION ALL
                SELECT
                    "member_roles"."permissions"
                FROM
                    "members"
                    INNER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
                WHERE
                    "members"."user_id" = 154136
                    AND "members"."source_type" = 'Namespace'
                    AND (members.source_id IN (
                            SELECT
                                UNNEST(project_ids.namespace_ids) AS ids))) AS custom_permissions

EXPLAIN: https://console.postgres.ai/shared/1ce82068-d63c-4c06-aba7-ef0d03e71f3c

Screenshots or screen recordings

Before	After

How to set up and validate locally

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.