Add a method to obtain effective access_level of members in a project (!60693) · Merge requests · GitLab.org / GitLab

Manoj M J requested to merge 223851-calculate-effective-member-access-level-for-projects into master Apr 30, 2021

What does this MR do?

This is the first step towards implementing #223851 (closed). (UPDATE: I now have a draft MR with this new service class being used for specialized project authorization update during a project-group share delete: !60904 (merged) )

Currently, we do not have a means to obtain the effective access levels of members of a particular project in one go. The newly introduced finder, ie, Projects::Members::EffectiveAccessLevelFinder solves this problem.

It takes into account all possible ways that a user can have membership in a project and calculates the effective access level in one go.

Possible means of membership into a project are

by being a direct member of the project
by being a direct member of any of the ancestor groups of the project
if any of the group ancestors of the project has other groups shared with them - if so the members from the shared group gains access to this project.
by being an owner of a personal project (owner always get Maintainer access to the project they created)
by project-group shares - when the project is shared with any other groups, those members in the group gain access to the project.

SQL queries generated

I have collected the SQL queries generated for the final UNION on different types of projects, from the staging console

Queries generated

# In staging console
ActiveRecord::Base.logger = Logger.new(STDOUT)

# For the project that has the highest number of project-group shares (14 shares)

project = Project.find(1527797)

events = []
callback = lambda do |*args|  
  event = ActiveSupport::Notifications::Event.new(*args)
  events << event
end


ActiveSupport::Notifications.subscribed(callback, "sql.active_record") do
  Projects::Members::EffectiveAccessLevelFinder.new(project).execute
end

p events.sum(&:duration) #=> 163ms

SELECT "projects".* FROM "projects" WHERE "projects"."id" = 1527797 LIMIT 1

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" = 795488 AND "namespaces"."type" = 'Group' LIMIT 1

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."id" = 795488 LIMIT 1

SELECT "application_settings".* FROM "application_settings" ORDER BY "application_settings"."id" DESC LIMIT 1

SELECT 1 AS one FROM "project_group_links" WHERE "project_group_links"."project_id" = 1527797 LIMIT 1

SELECT "project_group_links"."id" FROM "project_group_links" WHERE "project_group_links"."project_id" = 1527797 ORDER BY "project_group_links"."id" ASC LIMIT 1

SELECT "project_group_links"."id" FROM "project_group_links" WHERE "project_group_links"."project_id" = 1527797 AND "project_group_links"."id" >= 19448 ORDER BY "project_group_links"."id" ASC LIMIT 1 OFFSET 5

SELECT "project_group_links".* FROM "project_group_links" WHERE "project_group_links"."project_id" = 1527797 AND "project_group_links"."id" >= 19448 AND "project_group_links"."id" < 19456

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (964978, 964505, 898546, 878603, 872594)

SELECT "project_group_links"."id" FROM "project_group_links" WHERE "project_group_links"."project_id" = 1527797 AND "project_group_links"."id" >= 19456 ORDER BY "project_group_links"."id" ASC LIMIT 1 OFFSET 5

SELECT "project_group_links".* FROM "project_group_links" WHERE "project_group_links"."project_id" = 1527797 AND "project_group_links"."id" >= 19456 AND "project_group_links"."id" < 19462

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (847274, 841877, 841841, 834114, 806309)

SELECT "project_group_links"."id" FROM "project_group_links" WHERE "project_group_links"."project_id" = 1527797 AND "project_group_links"."id" >= 19462 ORDER BY "project_group_links"."id" ASC LIMIT 1 OFFSET 5

SELECT "project_group_links".* FROM "project_group_links" WHERE "project_group_links"."project_id" = 1527797 AND "project_group_links"."id" >= 19462

SELECT "namespaces".* FROM "namespaces" WHERE "namespaces"."type" = 'Group' AND "namespaces"."id" IN (806156, 797508, 795196, 663614)


SELECT "members".* FROM ((SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 964978)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 964978) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 964505)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 964505) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 898546)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 898546) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 878603)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 878603) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 872594)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 872594) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))) members


SELECT "members".* FROM ((SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 847274)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 847274) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 841877)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 841877) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 841841)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 841841) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 834114)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 834114) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 806309)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 806309) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))) members


SELECT "members".* FROM ((SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 806156)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 806156) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 797508)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 797508) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 795196)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 795196) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))
UNION
(SELECT "members"."user_id", LEAST(30, "members"."access_level") AS access_level FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 663614)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 663614) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5))) members


SELECT "members"."user_id", "members"."access_level" FROM "members" WHERE "members"."type" = 'ProjectMember' AND "members"."source_id" = 1527797 AND "members"."source_type" = 'Project' AND "members"."requested_at" IS NULL AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5)


SELECT "members"."user_id", "members"."access_level" FROM ((SELECT "members".* FROM "members" WHERE "members"."type" = 'GroupMember' AND "members"."source_type" = 'Namespace' AND "members"."source_id" = 795488)
UNION
(WITH "group_group_links_cte" AS MATERIALIZED (SELECT "group_group_links".* FROM "group_group_links" WHERE "group_group_links"."shared_group_id" = 795488) 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' AND "members"."user_id" IS NOT NULL AND "members"."requested_at" IS NULL AND (members.access_level > 5)


SELECT "members"."user_id", MAX(access_level) AS access_level FROM (VALUES (732929, 20),(561254, 30),(564997, 30),(561257, 30),(588456, 30),(559953, 40),(560155, 30),(723068, 30),(560066, 30),(560028, 30),(659378, 50),(559953, 50),(561398, 40),(563031, 30),(564997, 30),(561398, 20),(566616, 30),(732929, 20),(1210742, 30),(561257, 30),(1269700, 30),(659378, 30),(1187649, 30),(569538, 20),(567525, 30),(559953, 30),(563032, 20),(588456, 30),(561254, 30),(561257, 30),(564997, 30),(659378, 30),(567525, 30),(561398, 30),(559953, 30),(566616, 30),(563031, 30),(564997, 30),(560066, 30),(559688, 30),(566616, 30),(559898, 30),(561257, 30),(588475, 30),(659378, 30),(649930, 30),(567525, 30),(563032, 30),(561398, 30),(559953, 30),(560155, 30),(563058, 30),(560028, 30),(588456, 30),(561254, 30)) members (user_id, access_level) GROUP BY "members"."user_id"

How do we make sure of the correctness of this solution?

This is the question that concerned me the most, and thankfully I found a solution to verify the correctness of the new solution based on the already existing ProjectAuthorization records that we have. So like,

# in staging Rails console

# Step 1: Copy over all the code in `Projects::Members::EffectiveAccessLevelFinder` class and paste it in console
# Step 2: Copy over the following code

class ProjectGroupLink < ApplicationRecord
  include EachBatch
end

class Member < ApplicationRecord
  scope :authorizable, -> do
    where.not(user_id: nil)
      .non_request
      .non_minimal_access
  end
end

class Group < Namespace
  def authorizable_members_with_parents
    source_ids =
      if has_parent?
        self_and_ancestors.reorder(nil).select(:id)
      else
        id
      end

    group_hierarchy_members = GroupMember.where(source_id: source_ids)

    GroupMember.from_union([group_hierarchy_members,
                            members_from_self_and_ancestor_group_shares]).authorizable
  end
end

# Step 3: Code for comparing new and existing project authorizations

def compare(limit, offset)
  mismatched = []

  Project.limit(limit).offset(offset).find_each do |project|
    puts "comparing for project_id #{project.id}"

    current = ProjectAuthorization.where(project_id: project.id).pluck(:user_id, :access_level).sort
    fresh = Projects::Members::EffectiveAccessLevelFinder.new(project).execute.map {|pa| [pa.user_id, pa.access_level]}.sort
    
    if current != fresh
      mismatched << project.id
    end
  end
  
  mismatched
end

# Step 4: Call the `compare` with appropriate limit and offset

compare(1000, 0)

# Step 5: Ensure `mismatched` is empty array - if this is empty, it means that new service returned the right values.
# I did this for ~5000 groups on staging and there were no mismatches.

Screenshots (strongly suggested)

Does this MR meet the acceptance criteria?

Conformity

📋 Does this MR need a changelog?
- I have included a changelog entry.
- I have not included a changelog entry because this is only introducing a new Service, and also calls to this service aren't made anywhere yet.
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

Edited May 11, 2021 by Manoj M J

Add a method to obtain effective access_level of members in a project