Calculate correct access level of shared group member
What does this MR do and why?
Go through the description of the issue to understand more about the error #377922 (closed).
When getting the access level of the shared group members, it should be the least of the following two:
- Access level with which the invited group was added to the shared group:
- If Member1 has Maintainer access in the Invited group but the Invited group is added to the Shared group with Developer access, then Member1 should have Developer access in the Shared group because group sharing access was lower than the access in its original group.
- Access level of the member in the invited group:
- If Member2 has Guest access in the Invited group but the Invited group is added to the Shared group with Developer access, then Member2 should have Guest access in the Shared group because it should not have access more than it has in its original group.
We're doing the same by getting the least access level from the group_group_links and members table.
Query plan
Before
Raw query
SELECT "members".*
FROM (SELECT DISTINCT ON (user_id, invite_email) *
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN (SELECT "namespaces"."id"
FROM ((SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 397)
UNION
(SELECT "namespaces".*
FROM "namespaces"
INNER JOIN "group_group_links"
ON "group_group_links"."shared_with_group_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "group_group_links"."shared_group_id" IN (SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 397)
AND ("namespaces"."visibility_level" IN (10, 20) OR EXISTS(
SELECT 1
FROM (SELECT "namespaces".*
FROM ((WITH "direct_groups"
AS MATERIALIZED (SELECT "namespaces".*
FROM ((SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
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" = 11
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
UNION
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
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" = 11)) 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" = 11
AND "members"."access_level" = 5)) namespaces
WHERE "namespaces"."type" = 'Group') authorized
WHERE authorized."id" = "namespaces"."id")))) namespaces
WHERE "namespaces"."type" = 'Group')
ORDER BY user_id, invite_email, access_level DESC, expires_at DESC, created_at ASC) members
WHERE "members"."type" = 'GroupMember'
AND "members"."invite_token" IS NULL
AND "members"."requested_at" IS NULL
ORDER BY "members"."id" ASC
LIMIT 20 OFFSET 0https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/17592/commands/58774
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/17659/commands/58955
After
Raw query
SELECT "members".*
FROM (SELECT DISTINCT ON (user_id, invite_email) *
FROM ((SELECT "members"."id",
"members"."access_level",
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."state",
"members"."invite_email_success",
"members"."member_namespace_id",
"members"."member_role_id"
FROM "members"
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN (SELECT "namespaces"."id"
FROM ((SELECT "namespaces".*
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 397)) namespaces
WHERE "namespaces"."type" = 'Group'))
UNION
(SELECT "members"."id",
LEAST("group_group_links"."group_access", "members"."access_level") AS access_level,
"members"."source_id",
"members"."source_type",
"members"."user_id",
"members"."notification_level",
"members"."type",
"members"."created_at",
"members"."updated_at",
"members"."created_by_id",
"members"."invite_email",
"members"."invite_token",
"members"."invite_accepted_at",
"members"."requested_at",
"members"."expires_at",
"members"."ldap",
"members"."override",
"members"."state",
"members"."invite_email_success",
"members"."member_namespace_id",
"members"."member_role_id"
FROM "members"
LEFT OUTER JOIN group_group_links ON members.source_id = group_group_links.shared_with_group_id
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
AND "members"."requested_at" IS NULL
AND "members"."source_id" IN (SELECT "namespaces"."id"
FROM "namespaces"
INNER JOIN "group_group_links"
ON "group_group_links"."shared_with_group_id" = "namespaces"."id"
WHERE "namespaces"."type" = 'Group'
AND "group_group_links"."shared_group_id" IN (SELECT "namespaces"."id"
FROM "namespaces"
WHERE "namespaces"."type" = 'Group'
AND "namespaces"."id" = 397)
AND ("namespaces"."visibility_level" IN (10, 20) OR EXISTS(
SELECT 1
FROM (SELECT "namespaces".*
FROM ((WITH "direct_groups"
AS MATERIALIZED (SELECT "namespaces".*
FROM ((SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
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" = 11
AND "members"."requested_at" IS NULL
AND (access_level >= 10))
UNION
(SELECT "namespaces"."id",
"namespaces"."name",
"namespaces"."path",
"namespaces"."owner_id",
"namespaces"."created_at",
"namespaces"."updated_at",
"namespaces"."type",
"namespaces"."description",
"namespaces"."avatar",
"namespaces"."membership_lock",
"namespaces"."share_with_group_lock",
"namespaces"."visibility_level",
"namespaces"."request_access_enabled",
"namespaces"."ldap_sync_status",
"namespaces"."ldap_sync_error",
"namespaces"."ldap_sync_last_update_at",
"namespaces"."ldap_sync_last_successful_update_at",
"namespaces"."ldap_sync_last_sync_at",
"namespaces"."description_html",
"namespaces"."lfs_enabled",
"namespaces"."parent_id",
"namespaces"."shared_runners_minutes_limit",
"namespaces"."repository_size_limit",
"namespaces"."require_two_factor_authentication",
"namespaces"."two_factor_grace_period",
"namespaces"."cached_markdown_version",
"namespaces"."project_creation_level",
"namespaces"."runners_token",
"namespaces"."file_template_project_id",
"namespaces"."saml_discovery_token",
"namespaces"."runners_token_encrypted",
"namespaces"."custom_project_templates_group_id",
"namespaces"."auto_devops_enabled",
"namespaces"."extra_shared_runners_minutes_limit",
"namespaces"."last_ci_minutes_notification_at",
"namespaces"."last_ci_minutes_usage_notification_level",
"namespaces"."subgroup_creation_level",
"namespaces"."emails_disabled",
"namespaces"."max_pages_size",
"namespaces"."max_artifacts_size",
"namespaces"."mentions_disabled",
"namespaces"."default_branch_protection",
"namespaces"."unlock_membership_to_ldap",
"namespaces"."max_personal_access_token_lifetime",
"namespaces"."push_rule_id",
"namespaces"."shared_runners_enabled",
"namespaces"."allow_descendants_override_disabled_shared_runners",
"namespaces"."traversal_ids"
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" = 11)) 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" = 11
AND "members"."access_level" = 5)) namespaces
WHERE "namespaces"."type" = 'Group') authorized
WHERE authorized."id" = "namespaces"."id"))))) members
WHERE "members"."type" = 'GroupMember'
AND "members"."source_type" = 'Namespace'
ORDER BY user_id, invite_email, access_level DESC, expires_at DESC, created_at ASC) members
WHERE "members"."type" = 'GroupMember'
AND "members"."invite_token" IS NULL
AND "members"."requested_at" IS NULL
ORDER BY "members"."id" ASC
LIMIT 20 OFFSET 0;https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/17592/commands/58778
https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/17660/commands/58958
How to set up and validate locally
Follow the steps in the description #377922 (closed) and the API should return Member1 with Guest access.
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.
Related to #377922 (closed)
Edited by Abdul Wadood