Skip to content

GitLab Next

Merged
Created by Gosia Ksionek@mksionekDeveloper7 of 13 tasks completed7/13 tasks

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

User with minimal access: image

user with full access: image

Queries

For GroupMembersFinder

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

For MembersFinder

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

For group.group_members

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

For user.group_members

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

For Group.where(id: [9970]).with_selects_for_list

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

For group.members_with_parents

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

For User.find(1).authorized_groups

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

Availability and Testing

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)

Edited by Manoj M J