Skip to content

Migrate member role abilities

Alex Buijs requested to merge migrate-member-role-abilities into master

What does this MR do and why?

This migrates individual custom role boolean permission columns to a single JSONB permissions column.

The individual permission columns are ignored here and will be removed in 16.11.

Issue: #441449 (closed)

Database

MemberRoleAbilityLoader Query

Rails query:

Preloaders::UserMemberRolesInGroupsPreloader.new(groups: [Group.find(60357923)], user: User.find(4018056)).execute
Before
Raw SQL 
SELECT
	namespace_ids.namespace_id,
	bool_or(custom_permissions.admin_cicd_variables) AS admin_cicd_variables,
	bool_or(custom_permissions.admin_group_member) AS admin_group_member,
	bool_or(custom_permissions.admin_vulnerability) AS admin_vulnerability,
	bool_or(custom_permissions.manage_group_access_tokens) AS manage_group_access_tokens,
	bool_or(custom_permissions.read_dependency) AS read_dependency,
	bool_or(custom_permissions.read_vulnerability) AS read_vulnerability,
	bool_or(custom_permissions.remove_group) AS remove_group
FROM (
	VALUES(60357923, ARRAY [60357594,60357923]::integer [])) AS namespace_ids (namespace_id,
		namespace_ids),
	LATERAL ((
			SELECT
				admin_cicd_variables,
				admin_group_member,
				admin_vulnerability,
				manage_group_access_tokens,
				read_dependency,
				read_vulnerability,
				remove_group
			FROM
				"members"
			LEFT OUTER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
	WHERE (members.source_type = 'Namespace'
		AND members.source_id = namespace_ids.namespace_id)
	AND "members"."user_id" = 4018056
	AND(member_roles.admin_cicd_variables = TRUE
		OR member_roles.admin_group_member = TRUE
		OR member_roles.admin_vulnerability = TRUE
		OR member_roles.manage_group_access_tokens = TRUE
		OR member_roles.read_dependency = TRUE
		OR member_roles.read_vulnerability = TRUE
		OR member_roles.remove_group = TRUE))
UNION ALL (
	SELECT
		admin_cicd_variables,
		admin_group_member,
		admin_vulnerability,
		manage_group_access_tokens,
		read_dependency,
		read_vulnerability,
		remove_group
	FROM
		"members"
	LEFT OUTER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
WHERE (members.source_type = 'Namespace'
	AND members.source_id IN(
		SELECT
			UNNEST(namespace_ids) AS ids))
AND "members"."user_id" = 4018056
AND(member_roles.admin_cicd_variables = TRUE
	OR member_roles.admin_group_member = TRUE
	OR member_roles.admin_vulnerability = TRUE
	OR member_roles.manage_group_access_tokens = TRUE
	OR member_roles.read_dependency = TRUE
	OR member_roles.read_vulnerability = TRUE
	OR member_roles.remove_group = TRUE))
UNION ALL (
	SELECT
		FALSE AS admin_cicd_variables,
		FALSE AS admin_group_member,
		FALSE AS admin_vulnerability,
		FALSE AS manage_group_access_tokens,
		FALSE AS read_dependency,
		FALSE AS read_vulnerability,
		FALSE AS remove_group)) AS custom_permissions
GROUP BY
	namespace_ids.namespace_id;
Plan Postgres.ai 
 Aggregate  (cost=0.84..14.08 rows=1 width=11) (actual time=9.225..9.240 rows=1 loops=1)
   Group Key: 60357923
   Buffers: shared hit=9 read=4
   I/O Timings: read=8.928 write=0.000
   ->  Result  (cost=0.84..14.01 rows=3 width=11) (actual time=9.215..9.230 rows=1 loops=1)
         Buffers: shared hit=9 read=4
         I/O Timings: read=8.928 write=0.000
         ->  Append  (cost=0.84..13.98 rows=3 width=7) (actual time=9.213..9.227 rows=1 loops=1)
               Buffers: shared hit=9 read=4
               I/O Timings: read=8.928 write=0.000
               ->  Nested Loop  (cost=0.84..6.89 rows=1 width=7) (actual time=6.825..6.835 rows=0 loops=1)
                     Buffers: shared hit=1 read=3
                     I/O Timings: read=6.723 write=0.000
                     ->  Index Only Scan using idx_members_on_user_and_source_and_source_type_and_member_role on public.members  (cost=0.56..3.59 rows=1 width=8) (actual time=6.823..6.824 rows=0 loops=1)
                           Index Cond: ((members.user_id = 971158) AND (members.source_id = 60357923) AND (members.source_type = 'Namespace'::text))
                           Heap Fetches: 0
                           Buffers: shared hit=1 read=3
                           I/O Timings: read=6.723 write=0.000
                     ->  Index Scan using member_roles_pkey on public.member_roles  (cost=0.28..3.29 rows=1 width=15) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: (member_roles.id = members.member_role_id)
                           Filter: (member_roles.admin_cicd_variables OR member_roles.admin_group_member OR member_roles.admin_vulnerability OR member_roles.manage_group_access_tokens OR member_roles.read_dependency OR member_roles.read_vulnerability OR member_roles.remove_group)
                           Rows Removed by Filter: 0
                           I/O Timings: read=0.000 write=0.000
               ->  Nested Loop  (cost=0.90..7.05 rows=1 width=7) (actual time=2.381..2.383 rows=0 loops=1)
                     Buffers: shared hit=8 read=1
                     I/O Timings: read=2.205 write=0.000
                     ->  Merge Semi Join  (cost=0.62..4.00 rows=1 width=8) (actual time=2.318..2.324 rows=1 loops=1)
                           Merge Cond: (members_1.source_id = (unnest('{60357594,60357923}'::integer[])))
                           Buffers: shared hit=5 read=1
                           I/O Timings: read=2.205 write=0.000
                           ->  Index Only Scan using idx_members_on_user_and_source_and_source_type_and_member_role on public.members members_1  (cost=0.56..3.90 rows=6 width=12) (actual time=2.253..2.256 rows=2 loops=1)
                                 Index Cond: ((members_1.user_id = 971158) AND (members_1.source_type = 'Namespace'::text))
                                 Heap Fetches: 0
                                 Buffers: shared hit=5 read=1
                                 I/O Timings: read=2.205 write=0.000
                           ->  Sort  (cost=0.06..0.06 rows=2 width=4) (actual time=0.058..0.059 rows=2 loops=1)
                                 Sort Key: (unnest('{60357594,60357923}'::integer[]))
                                 Sort Method: quicksort  Memory: 25kB
                                 I/O Timings: read=0.000 write=0.000
                                 ->  ProjectSet  (cost=0.00..0.03 rows=2 width=4) (actual time=0.010..0.011 rows=2 loops=1)
                                       I/O Timings: read=0.000 write=0.000
                                       ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=1 loops=1)
                                             I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using member_roles_pkey on public.member_roles member_roles_1  (cost=0.28..3.04 rows=1 width=15) (actual time=0.053..0.053 rows=0 loops=1)
                           Index Cond: (member_roles_1.id = members_1.member_role_id)
                           Filter: (member_roles_1.admin_cicd_variables OR member_roles_1.admin_group_member OR member_roles_1.admin_vulnerability OR member_roles_1.manage_group_access_tokens OR member_roles_1.read_dependency OR member_roles_1.read_vulnerability OR member_roles_1.remove_group)
                           Rows Removed by Filter: 1
                           Buffers: shared hit=3
                           I/O Timings: read=0.000 write=0.000
               ->  Result  (cost=0.00..0.01 rows=1 width=7) (actual time=0.002..0.002 rows=1 loops=1)
                     I/O Timings: read=0.000 write=0.000
After
Raw SQL 
SELECT
	namespace_ids.namespace_id,
	bool_or(custom_permissions.admin_cicd_variables) AS admin_cicd_variables,
	bool_or(custom_permissions.admin_group_member) AS admin_group_member,
	bool_or(custom_permissions.admin_vulnerability) AS admin_vulnerability,
	bool_or(custom_permissions.manage_group_access_tokens) AS manage_group_access_tokens,
	bool_or(custom_permissions.read_dependency) AS read_dependency,
	bool_or(custom_permissions.read_vulnerability) AS read_vulnerability,
	bool_or(custom_permissions.remove_group) AS remove_group
FROM (
	VALUES(60357923, ARRAY [60357594,60357923]::integer [])) AS namespace_ids (namespace_id,
		namespace_ids),
	LATERAL ((
			SELECT
				(member_roles.permissions -> 'admin_cicd_variables')::BOOLEAN AS admin_cicd_variables,
				(member_roles.permissions -> 'admin_group_member')::BOOLEAN AS admin_group_member,
				(member_roles.permissions -> 'admin_vulnerability')::BOOLEAN AS admin_vulnerability,
				(member_roles.permissions -> 'manage_group_access_tokens')::BOOLEAN AS manage_group_access_tokens,
				(member_roles.permissions -> 'read_dependency')::BOOLEAN AS read_dependency,
				(member_roles.permissions -> 'read_vulnerability')::BOOLEAN AS read_vulnerability,
				(member_roles.permissions -> 'remove_group')::BOOLEAN AS remove_group
			FROM
				"members"
			LEFT OUTER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
	WHERE (members.source_type = 'Namespace'
		AND members.source_id = namespace_ids.namespace_id)
	AND "members"."user_id" = 4018056
	AND(member_roles.permissions @> ('{"admin_cicd_variables":true}')::jsonb
		OR member_roles.permissions @> ('{"admin_group_member":true}')::jsonb
		OR member_roles.permissions @> ('{"admin_vulnerability":true}')::jsonb
		OR member_roles.permissions @> ('{"manage_group_access_tokens":true}')::jsonb
		OR member_roles.permissions @> ('{"read_dependency":true}')::jsonb
		OR member_roles.permissions @> ('{"read_vulnerability":true}')::jsonb
		OR member_roles.permissions @> ('{"remove_group":true}')::jsonb))
UNION ALL (
	SELECT
		(member_roles.permissions -> 'admin_cicd_variables')::BOOLEAN AS admin_cicd_variables,
		(member_roles.permissions -> 'admin_group_member')::BOOLEAN AS admin_group_member,
		(member_roles.permissions -> 'admin_vulnerability')::BOOLEAN AS admin_vulnerability,
		(member_roles.permissions -> 'manage_group_access_tokens')::BOOLEAN AS manage_group_access_tokens,
		(member_roles.permissions -> 'read_dependency')::BOOLEAN AS read_dependency,
		(member_roles.permissions -> 'read_vulnerability')::BOOLEAN AS read_vulnerability,
		(member_roles.permissions -> 'remove_group')::BOOLEAN AS remove_group
	FROM
		"members"
	LEFT OUTER JOIN "member_roles" ON "member_roles"."id" = "members"."member_role_id"
WHERE (members.source_type = 'Namespace'
	AND members.source_id IN(
		SELECT
			UNNEST(namespace_ids) AS ids))
AND "members"."user_id" = 4018056
AND(member_roles.permissions @> ('{"admin_cicd_variables":true}')::jsonb
	OR member_roles.permissions @> ('{"admin_group_member":true}')::jsonb
	OR member_roles.permissions @> ('{"admin_vulnerability":true}')::jsonb
	OR member_roles.permissions @> ('{"manage_group_access_tokens":true}')::jsonb
	OR member_roles.permissions @> ('{"read_dependency":true}')::jsonb
	OR member_roles.permissions @> ('{"read_vulnerability":true}')::jsonb
	OR member_roles.permissions @> ('{"remove_group":true}')::jsonb))
UNION ALL (
	SELECT
		FALSE AS admin_cicd_variables,
		FALSE AS admin_group_member,
		FALSE AS admin_vulnerability,
		FALSE AS manage_group_access_tokens,
		FALSE AS read_dependency,
		FALSE AS read_vulnerability,
		FALSE AS remove_group)) AS custom_permissions
GROUP BY
	namespace_ids.namespace_id;
Plan Postgres.ai 
 Aggregate  (cost=0.84..14.18 rows=1 width=11) (actual time=0.144..0.147 rows=1 loops=1)
   Group Key: 60357923
   Buffers: shared hit=13
   I/O Timings: read=0.000 write=0.000
   ->  Result  (cost=0.84..14.11 rows=3 width=11) (actual time=0.136..0.139 rows=1 loops=1)
         Buffers: shared hit=13
         I/O Timings: read=0.000 write=0.000
         ->  Append  (cost=0.84..14.08 rows=3 width=7) (actual time=0.136..0.138 rows=1 loops=1)
               Buffers: shared hit=13
               I/O Timings: read=0.000 write=0.000
               ->  Nested Loop  (cost=0.84..6.94 rows=1 width=7) (actual time=0.036..0.037 rows=0 loops=1)
                     Buffers: shared hit=4
                     I/O Timings: read=0.000 write=0.000
                     ->  Index Only Scan using idx_members_on_user_and_source_and_source_type_and_member_role on public.members  (cost=0.56..3.59 rows=1 width=8) (actual time=0.036..0.036 rows=0 loops=1)
                           Index Cond: ((members.user_id = 971158) AND (members.source_id = 60357923) AND (members.source_type = 'Namespace'::text))
                           Heap Fetches: 0
                           Buffers: shared hit=4
                           I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using member_roles_pkey on public.member_roles  (cost=0.28..3.31 rows=1 width=354) (actual time=0.000..0.000 rows=0 loops=0)
                           Index Cond: (member_roles.id = members.member_role_id)
                           Filter: ((member_roles.permissions @> '{"admin_cicd_variables": true}'::jsonb) OR (member_roles.permissions @> '{"admin_group_member": true}'::jsonb) OR (member_roles.permissions @> '{"admin_vulnerability": true}'::jsonb) OR (member_roles.permissions @> '{"manage_group_access_tokens": true}'::jsonb) OR (member_roles.permissions @> '{"read_dependency": true}'::jsonb) OR (member_roles.permissions @> '{"read_vulnerability": true}'::jsonb) OR (member_roles.permissions @> '{"remove_group": true}'::jsonb))
                           Rows Removed by Filter: 0
                           I/O Timings: read=0.000 write=0.000
               ->  Nested Loop  (cost=0.90..7.10 rows=1 width=7) (actual time=0.097..0.098 rows=0 loops=1)
                     Buffers: shared hit=9
                     I/O Timings: read=0.000 write=0.000
                     ->  Merge Semi Join  (cost=0.62..4.00 rows=1 width=8) (actual time=0.047..0.050 rows=1 loops=1)
                           Merge Cond: (members_1.source_id = (unnest('{60357594,60357923}'::integer[])))
                           Buffers: shared hit=6
                           I/O Timings: read=0.000 write=0.000
                           ->  Index Only Scan using idx_members_on_user_and_source_and_source_type_and_member_role on public.members members_1  (cost=0.56..3.90 rows=6 width=12) (actual time=0.018..0.019 rows=2 loops=1)
                                 Index Cond: ((members_1.user_id = 971158) AND (members_1.source_type = 'Namespace'::text))
                                 Heap Fetches: 0
                                 Buffers: shared hit=6
                                 I/O Timings: read=0.000 write=0.000
                           ->  Sort  (cost=0.06..0.06 rows=2 width=4) (actual time=0.027..0.028 rows=2 loops=1)
                                 Sort Key: (unnest('{60357594,60357923}'::integer[]))
                                 Sort Method: quicksort  Memory: 25kB
                                 I/O Timings: read=0.000 write=0.000
                                 ->  ProjectSet  (cost=0.00..0.03 rows=2 width=4) (actual time=0.003..0.004 rows=2 loops=1)
                                       I/O Timings: read=0.000 write=0.000
                                       ->  Result  (cost=0.00..0.01 rows=1 width=0) (actual time=0.000..0.001 rows=1 loops=1)
                                             I/O Timings: read=0.000 write=0.000
                     ->  Index Scan using member_roles_pkey on public.member_roles member_roles_1  (cost=0.28..3.06 rows=1 width=354) (actual time=0.046..0.046 rows=0 loops=1)
                           Index Cond: (member_roles_1.id = members_1.member_role_id)
                           Filter: ((member_roles_1.permissions @> '{"admin_cicd_variables": true}'::jsonb) OR (member_roles_1.permissions @> '{"admin_group_member": true}'::jsonb) OR (member_roles_1.permissions @> '{"admin_vulnerability": true}'::jsonb) OR (member_roles_1.permissions @> '{"manage_group_access_tokens": true}'::jsonb) OR (member_roles_1.permissions @> '{"read_dependency": true}'::jsonb) OR (member_roles_1.permissions @> '{"read_vulnerability": true}'::jsonb) OR (member_roles_1.permissions @> '{"remove_group": true}'::jsonb))
                           Rows Removed by Filter: 1
                           Buffers: shared hit=3
                           I/O Timings: read=0.000 write=0.000
               ->  Result  (cost=0.00..0.01 rows=1 width=7) (actual time=0.001..0.001 rows=1 loops=1)
                     I/O Timings: read=0.000 write=0.000

Conclusion: the plans are similar, except for the filter condition on the index scan (performed in loop).

Note there is a maximum of 10 member roles per namespace and there are currently < 1000 records in GitLab.com production database.

How to set up and validate locally

Before checking out this branch

  1. Create a MemberRole with read_code permissions in the console 
     MemberRole.create(base_access_level: 10, read_code: true)
  2. Check out this branch and run the migrations
  3. Select the latest MemberRole in the console and verify the read_code permission is enabled 
    MemberRole.last.read_code == true
Edited by Alex Buijs

Merge request reports