Skip to content

Fix subgroup support for approval rules

Vasilii Iakliushinrequested to merge
371106_fix_approver_rules_for_subgroups into master

What does this MR do and why?

Problem

We had a problem with inherited permissions for subgroups. Users couldn't see appovers for groups if they don't have a direct membership in them. It was fixed in scope of !91598 (merged).

However, there is a similar invalid check that filters subgroups with inherited permissions out.

Solution

public_or_visible_to_user method doesn't correctly support subgroup permissions. Instead we can verify if user has an access to the group.

Database

Before

Click to expand 
SELECT
    "namespaces"."id"
FROM
    "namespaces"
WHERE
    "namespaces"."type" = 'Group'
    AND "namespaces"."id" IN (9970, 4955423)
    AND ("namespaces"."visibility_level" IN (0, 10, 20)
        OR EXISTS (
            SELECT
                1
            FROM (
                SELECT
                    "namespaces".*
                FROM (( WITH "direct_groups" AS MATERIALIZED (
                            SELECT
                                "namespaces".*
                            FROM ((
                                    SELECT
                                        "namespaces".*
                                    FROM
                                        "namespaces"
                                        INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
                                    WHERE
                                        "members"."type" = 'GroupMember'
                                        AND "members"."source_type" = 'Namespace'
                                        AND "namespaces"."type" = 'Group'
                                        AND "members"."user_id" = 421631
                                        AND "members"."requested_at" IS NULL
                                        AND (
                                            access_level >= 10
)
)
                                UNION (
                                    SELECT
                                        "namespaces".*
                                    FROM
                                        "projects"
                                        INNER JOIN "project_authorizations" ON "projects"."id" = "project_authorizations"."project_id"
                                        INNER JOIN "namespaces" ON "namespaces"."id" = "projects"."namespace_id"
                                    WHERE
                                        "project_authorizations"."user_id" = 421631
)
) namespaces
                            WHERE
                                "namespaces"."type" = 'Group'
)
                            SELECT
                                "namespaces".*
                            FROM ((
                                    SELECT
                                        "namespaces".*
                                    FROM
                                        "direct_groups" "namespaces"
                                    WHERE
                                        "namespaces"."type" = 'Group')
                                UNION (
                                    SELECT
                                        "namespaces".*
                                    FROM
                                        "namespaces"
                                        INNER JOIN "group_group_links" ON "group_group_links"."shared_group_id" = "namespaces"."id"
                                    WHERE
                                        "namespaces"."type" = 'Group'
                                        AND "group_group_links"."shared_with_group_id" IN (
                                            SELECT
                                                "namespaces"."id"
                                            FROM
                                                "direct_groups" "namespaces"
                                            WHERE
                                                "namespaces"."type" = 'Group'))) namespaces
                                WHERE
                                    "namespaces"."type" = 'Group')
                            UNION (
                                SELECT
                                    "namespaces".*
                                FROM
                                    "namespaces"
                                    INNER JOIN "members" ON "namespaces"."id" = "members"."source_id"
                                WHERE
                                    "members"."type" = 'GroupMember'
                                    AND "members"."source_type" = 'Namespace'
                                    AND "namespaces"."type" = 'Group'
                                    AND "members"."user_id" = 421631
                                    AND "members"."access_level" = 5)) namespaces
                        WHERE
                            "namespaces"."type" = 'Group') authorized
                    WHERE
                        authorized. "id" = "namespaces"."id"))

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11789/commands/41802

After

I rely on group preload logic introduced here - !73121 (merged)

Click to expand 
SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (9970, 4955423)

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11789/commands/41804

Click to expand 
SELECT
    namespaces.*,
    root_query.id AS source_id
FROM
    "namespaces"
    INNER JOIN (
        SELECT
            id,
            traversal_ids[1] AS root_id
        FROM
            "namespaces"
        WHERE
            "namespaces"."type" = 'Group'
            AND "namespaces"."id" IN (9970, 4955423)) AS root_query ON root_query.root_id = namespaces.id

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11789/commands/41806

Click to expand 
SELECT
    MAX("members"."access_level") AS maximum_access_level,
    "hierarchy"."id" AS hierarchy_id
FROM
    "members"
    LEFT OUTER JOIN "users" ON "users"."id" = "members"."user_id"
INNER JOIN (
    SELECT
        id,
        unnest(traversal_ids) AS traversal_id
    FROM
        "namespaces"
    WHERE
        "namespaces"."id" IN (9970, 4955423)) AS hierarchy ON members.source_id = hierarchy.traversal_id
WHERE
    "members"."type" = 'GroupMember'
    AND "members"."source_type" = 'Namespace'
    AND "users"."state" = 'active'
    AND "members"."state" = 0
    AND "members"."requested_at" IS NULL
    AND "members"."invite_token" IS NULL
    AND (members.access_level > 5)
    AND "members"."user_id" = 421631
GROUP BY
    "hierarchy"."id"

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11789/commands/41807

Click to expand 
SELECT
    1 AS one
FROM ((
        SELECT
            "projects".*
        FROM
            "projects"
        WHERE
            "projects"."namespace_id" IN (
                SELECT
                    "namespaces"."id"
                FROM
                    "namespaces"
                WHERE
                    "namespaces"."type" = 'Group'
                    AND (traversal_ids @> ('{9970, 4955423}'))))) projects
WHERE (EXISTS (
        SELECT
            1
        FROM
            "project_authorizations"
        WHERE
            "project_authorizations"."user_id" = 421631
            AND (project_authorizations.project_id = projects.id))
        OR projects.visibility_level IN (10, 20))
AND "projects"."hidden" = FALSE
LIMIT 1

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/11789/commands/41818

Screenshots or screen recordings

Before After
Screenshot_2022-08-31_at_11.29.57 Screenshot_2022-08-31_at_11.30.44

How to set up and validate locally

  1. Check the great walk-through video: #371106 (comment 1083294024)

MR acceptance checklist

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

Edited by Vasilii Iakliushin

Merge request reports