Custom abilities for group memberships

What does this MR do and why?

• This enables read_vulnerability and admin_vulnerability for groups when a custom role exists on the group membership.
• #411851 (closed)

Database

See related MR where preloader was added for project custom roles: !100466 (merged)

SQL query

SQL generated by preloader:

::Preloaders::UserMemberRolesInGroupsPreloader.new(
  groups: groups,
  user: User.first
 ).execute

And then using some group IDs from production (test groups with custom roles and the gitlab-org group and my prod User ID)

SELECT
    namespace_ids.namespace_id,
    Bool_or(custom_permissions.read_vulnerability) AS read_vulnerability,
    Bool_or(custom_permissions.admin_vulnerability) AS admin_vulnerability
FROM (
    VALUES (60357594, ARRAY[60357594]::integer[]),
        (65094811, ARRAY[65094811]::integer[]),
        (60357923, ARRAY[60357923]::integer[]),
        (9970, ARRAY[9970]::integer[])) AS namespace_ids (namespace_id, namespace_ids),
    LATERAL ((
            SELECT
                read_vulnerability, admin_vulnerability
            FROM
                "members"
            LEFT OUTER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
    WHERE (members.source_type = 'Namespace'
        AND members.source_id = namespace_ids.namespace_id)
        AND "members"."user_id" = 11997412
        AND (member_roles.read_vulnerability = TRUE
            OR member_roles.admin_vulnerability = TRUE)
    LIMIT 1)
UNION ALL (
    SELECT
        read_vulnerability,
        admin_vulnerability
    FROM
        "members"
    LEFT OUTER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
WHERE (members.source_type = 'Namespace'
        AND members.source_id IN (
            SELECT
                unnest(namespace_ids) AS ids))
        AND "members"."user_id" = 11997412
        AND (member_roles.read_vulnerability = TRUE
            OR member_roles.admin_vulnerability = TRUE)
    LIMIT 1)
UNION ALL (
    SELECT
        FALSE AS read_vulnerability,
        FALSE AS admin_vulnerability)
LIMIT 1) AS custom_permissions
GROUP BY
    namespace_ids.namespace_id;

Explain plan for query

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/21161/commands/69050

Screenshots or screen recordings

Guest viewing group security dashboard in "Before" Custom Guest with admin_vulnerability: true and read_vulnerability: true viewing group security dashboard in "After"

Before	After

How to set up and validate locally

(These instructions assume you are running GDK with an Ultimate license. It uses the seeded flightjs group but you can use another group if you like)

1. As instance admin, make group Ultimate by visiting https://gdk.test:3443/admin/groups/flightjs as an admin
2. As instance admin or group owner, add another user as a regular guest https://gdk.test:3443/groups/flightjs/-/group_members

Confirm "before" behavior:

1. Log in as guest
2. Visit https://gdk.test:3443/groups/flightjs/-/security/dashboard
3. There should be nothing visible

Create "after" behavior:

1. In a rails console, make the custom role and assign it to the guest user you just added:

   Feature.enable(:custom_roles_on_groups)
   group = Group.find_by_name("Flightjs")
   MemberRole.create!(read_vulnerability: true, namespace: group, base_access_level: Gitlab::Access::GUEST)
   m = Member.last
   m.update!(member_role: MemberRole.last)

2. Custom guest can now view https://gdk.test:3443/groups/flightjs/-/security/vulnerabilities and it shows vulnerabilities
3. In a rails console, update the custom role so that it also enables the custom guest to admin vulnerabilities (usually custom roles cannot be updated after they have been assigned so we need to skip validations here)

   mr = MemberRole.last
   mr.admin_vulnerability = true
   mr.save!(validate: false)

4. Custom guest can now view https://gdk.test:3443/groups/flightjs/-/security/vulnerabilities and it shows vulnerabilities checkboxes to allow management of vulnerabilities.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.