Resolve "Create "No Access" Role" - user facing changes
What does this MR do?
By this MR I want to add additional role - Minimal access
. This role is supposed to give user membership of a group, but in the same time not allow user to see any resources. It can be used for groups with SSO, so newly logged-in users wouldn't have access to all the resources.
This is the part with all user-facing changes. Backend work is here
Screenshots
Queries
GroupMembersFinder
For GroupMembersFinder.new(group).execute.to_sql
For a group with no parent:
Group is: https://gitlab.com/gitlab-org
, ID: 9970
Old (CE & EE)
explain SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 9970 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL
https://explain.depesz.com/s/ZuXo
New - EE (ie, feature enabled)
explain SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 9970 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL
https://explain.depesz.com/s/ZuXo
New - CE (ie, feature disabled)
explain SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 9970 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5
https://explain.depesz.com/s/f9Hb
For a group with parent:
group is: https://gitlab.com/gitlab-org/growth
, ID: 5754519
Old (CE & EE):
explain SELECT "members".* FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 5754519 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL) UNION (SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" IN (WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "id" FROM "base_and_ancestors" AS "namespaces") AND "members"."user_id" NOT IN (SELECT "users"."id" FROM "users" INNER JOIN "members" ON "users"."id" = "members"."user_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 5754519 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL))) members WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace'
https://explain.depesz.com/s/398P
New - EE (ie, feature enabled)
explain SELECT "members".* FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 5754519 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5) UNION (SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" IN (WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "id" FROM "base_and_ancestors" AS "namespaces") AND "members"."user_id" NOT IN (SELECT "users"."id" FROM "users" INNER JOIN "members" ON "users"."id" = "members"."user_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 5754519 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5) AND (members.access_level > 5))) members WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace'
https://explain.depesz.com/s/sZsn
New - CE (ie, feature disabled)
explain SELECT "members".* FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 5754519 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5) UNION (SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" IN (WITH RECURSIVE "base_and_ancestors" AS ((SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970) UNION (SELECT "namespaces".* FROM "namespaces", "base_and_ancestors" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = "base_and_ancestors"."parent_id")) SELECT "id" FROM "base_and_ancestors" AS "namespaces") AND "members"."user_id" NOT IN (SELECT "users"."id" FROM "users" INNER JOIN "members" ON "users"."id" = "members"."user_id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 5754519 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5) AND (members.access_level > 5))) members WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace'
https://explain.depesz.com/s/sZsn
MembersFinder
For MembersFinder.new(Project.find(278964), User.find(1)).execute.to_sql
project_id = 278964
Old:
explain SELECT "members".* FROM (SELECT DISTINCT ON (user_id, invite_email) member_union.id,COALESCE(project_authorizations.access_level, member_union.access_level) access_level,member_union.source_id,member_union.source_type,member_union.user_id,member_union.notification_level,member_union.type,member_union.created_at,member_union.updated_at,member_union.created_by_id,member_union.invite_email,member_union.invite_token,member_union.invite_accepted_at,member_union.requested_at,member_union.expires_at,member_union.ldap,member_union.override FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 22 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL)UNION ALL(SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 278964 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL)) AS member_union LEFT JOIN users on users.id = member_union.user_id LEFT JOIN project_authorizations on project_authorizations.user_id = users.id AND project_authorizations.project_id = 278964 ORDER BY user_id, invite_email, CASE WHEN type = 'ProjectMember' THEN 278964 WHEN type = 'GroupMember' THEN 2 ELSE 3 END) AS members
https://explain.depesz.com/s/9jan
New:
explain SELECT "members".* FROM (SELECT DISTINCT ON (user_id, invite_email) member_union.id,COALESCE(project_authorizations.access_level, member_union.access_level) access_level,member_union.source_id,member_union.source_type,member_union.user_id,member_union.notification_level,member_union.type,member_union.created_at,member_union.updated_at,member_union.created_by_id,member_union.invite_email,member_union.invite_token,member_union.invite_accepted_at,member_union.requested_at,member_union.expires_at,member_union.ldap,member_union.override FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 22 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5))UNION ALL(SELECT "members".* FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 278964 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL)) AS member_union LEFT JOIN users on users.id = member_union.user_id LEFT JOIN project_authorizations on project_authorizations.user_id = users.id AND project_authorizations.project_id = 278964 ORDER BY user_id, invite_email, CASE WHEN type = 'ProjectMember' THEN 278964 WHEN type = 'GroupMember' THEN 2 ELSE 3 END) AS members
https://explain.depesz.com/s/IDbs
group.group_members
For New:
explain SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 9970 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."access_level" != 5
https://explain.depesz.com/s/oNAi
Old:
explain SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_id" = 9970 AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL
https://explain.depesz.com/s/uXqR
user.group_members
For New:
explain SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" = 'GroupMember' AND "members"."user_id" = 1 AND "members"."requested_at" IS NULL AND (access_level >= 10)
https://explain.depesz.com/s/XQhT
Old:
explain SELECT "members".* FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."type" = 'GroupMember' AND "members"."user_id" = 1 AND "members"."requested_at" IS NULL
https://explain.depesz.com/s/RykI
Group.where(id: [9970]).with_selects_for_list
For New:
explain SELECT namespaces.*, (SELECT COUNT(*) AS preloaded_project_count FROM "projects" WHERE "projects"."namespace_id" = "namespaces"."id" AND "projects"."archived" != TRUE) AS preloaded_project_count, (SELECT COUNT(*) AS preloaded_member_count FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."source_id" = "namespaces"."id" AND "members"."requested_at" IS NULL AND "members"."access_level" > 5) AS preloaded_member_count, (SELECT COUNT(*) AS preloaded_subgroup_count FROM "namespaces" "children" WHERE "children"."parent_id" = "namespaces"."id") AS preloaded_subgroup_count FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970
https://explain.depesz.com/s/rIbiO
Old:
explain SELECT namespaces.*, (SELECT COUNT(*) AS preloaded_project_count FROM "projects" WHERE "projects"."namespace_id" = "namespaces"."id" AND "projects"."archived" != TRUE) AS preloaded_project_count, (SELECT COUNT(*) AS preloaded_member_count FROM "members" WHERE "members"."source_type" = 'Namespace' AND "members"."source_id" = "namespaces"."id" AND "members"."requested_at" IS NULL) AS preloaded_member_count, (SELECT COUNT(*) AS preloaded_subgroup_count FROM "namespaces" "children" WHERE "children"."parent_id" = "namespaces"."id") AS preloaded_subgroup_count FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 9970
https://explain.depesz.com/s/KsE6
group.members_with_parents
For New:
explain SELECT "members".* FROM ((SELECT "members".* FROM "members" LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "users"."state" = 'active' AND "members"."requested_at" IS NULL AND "members"."invite_token" IS NULL AND (members.access_level > 5) AND (members.access_level > 5) AND "members"."source_id" = 9970) UNION (WITH "group_group_links_cte" AS (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 9970) 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" FROM "members", "group_group_links_cte" AS "group_group_links" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" = "group_group_links"."shared_with_group_id" AND "members"."source_type" = 'Namespace' AND (members.access_level > 5))) members WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace'
https://explain.depesz.com/s/haF6
Old:
explain SELECT "members".* FROM ((SELECT "members".* FROM "members" LEFT OUTER JOIN "users" ON "members"."user_id" = "users"."id" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "users"."state" = 'active' AND "members"."requested_at" IS NULL AND "members"."source_id" = 9970) UNION (WITH "group_group_links_cte" AS (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 9970) 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" FROM "members", "group_group_links_cte" AS "group_group_links" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."requested_at" IS NULL AND "members"."source_id" = "group_group_links"."shared_with_group_id" AND "members"."source_type" = 'Namespace')) members WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace'
https://explain.depesz.com/s/N3IF
User.find(1).authorized_groups
For new:
explain 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" = 1 AND "members"."requested_at" IS NULL AND (access_level >= 10)) 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" = 1 AND "members"."access_level" = 5) 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" = 1)) namespaces WHERE "namespaces"."type" = 'Group'
https://explain.depesz.com/s/6Uq9
old:
explain 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" = 1 AND "members"."requested_at" IS NULL) 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" = 1)) namespaces WHERE "namespaces"."type" = 'Group'
https://explain.depesz.com/s/HkK7
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec
-
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team
Closes #220203 (closed)