Split out standalone into user and instance boundaries

Alex Buijsrequested to merge
refactor-authz-boundary-class into master

What does this MR do and why?

Split out standalone into user and instance boundaries for authorizing API endpoints with explicit access check.

References

Issue: #583042

New queries

Rails

Query using the new for_namespaces scope 

PersonalAccessToken.last.permitted_for_boundary?(Authz::Boundary.for(Group.first(2)), :read_job)

Query using the new for_standalone scope 

PersonalAccessToken.last.permitted_for_boundary?(Authz::Boundary.for(:instance), :read_job)

Raw SQL

Namespace boundary 

SELECT DISTINCT
	jsonb_array_elements_text(permissions)
FROM
	"granular_scopes"
	INNER JOIN "personal_access_token_granular_scopes" ON "granular_scopes"."id" = "personal_access_token_granular_scopes"."granular_scope_id"
WHERE
	"personal_access_token_granular_scopes"."personal_access_token_id" = (
		SELECT
			"personal_access_tokens".id
		FROM
			"personal_access_tokens"
		ORDER BY
			"personal_access_tokens"."id" DESC
		LIMIT 1)
	AND("granular_scopes"."namespace_id" IN((
			SELECT
				id FROM namespaces
			WHERE
				namespaces.type = 'Group'
			LIMIT 2))
	AND "granular_scopes"."access" IN(0, 2)
	OR "granular_scopes"."namespace_id" IS NULL
	AND "granular_scopes"."access" = 1);

Standalone boundary 

SELECT DISTINCT
	jsonb_array_elements_text(permissions)
FROM
	"granular_scopes"
	INNER JOIN "personal_access_token_granular_scopes" ON "granular_scopes"."id" = "personal_access_token_granular_scopes"."granular_scope_id"
WHERE
	"personal_access_token_granular_scopes"."personal_access_token_id" = (
		SELECT
			"personal_access_tokens".id
		FROM
			"personal_access_tokens"
		ORDER BY
			"personal_access_tokens"."id" DESC
		LIMIT 1)
	AND "granular_scopes"."namespace_id" IS NULL
	AND "granular_scopes"."access" = 4
	AND "granular_scopes"."access" != 1

Query plan

Namespace boundary

Postgresql.ai: https://console.postgres.ai/shared/8432adbe-207e-40fd-8e71-38c0f0a5b2e8

 HashAggregate  (cost=338.71..339.96 rows=100 width=32) (actual time=5.313..5.315 rows=1 loops=1)
   Group Key: jsonb_array_elements_text(granular_scopes.permissions)
   Buffers: shared hit=306 read=5
   I/O Timings: read=4.487 write=0.000
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.44..0.46 rows=1 width=4) (actual time=0.025..0.026 rows=1 loops=1)
           Buffers: shared hit=5
           I/O Timings: read=0.000 write=0.000
           ->  Index Only Scan Backward using personal_access_tokens_pkey on public.personal_access_tokens  (cost=0.44..753579.94 rows=29526974 width=4) (actual time=0.024..0.024 rows=1 loops=1)
                 Heap Fetches: 1
                 Buffers: shared hit=5
                 I/O Timings: read=0.000 write=0.000
   ->  ProjectSet  (cost=0.79..329.50 rows=3500 width=32) (actual time=4.658..5.302 rows=28 loops=1)
         Buffers: shared hit=306 read=5
         I/O Timings: read=4.487 write=0.000
         ->  Nested Loop  (cost=0.79..311.74 rows=35 width=17) (actual time=4.636..5.260 rows=28 loops=1)
               Buffers: shared hit=306 read=5
               I/O Timings: read=4.487 write=0.000
               ->  Seq Scan on public.personal_access_token_granular_scopes  (cost=0.00..5.25 rows=100 width=8) (actual time=0.030..0.044 rows=100 loops=1)
                     Filter: (personal_access_token_granular_scopes.personal_access_token_id = $0)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=6
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using granular_scopes_pkey on public.granular_scopes  (cost=0.79..3.06 rows=1 width=25) (actual time=0.052..0.052 rows=0 loops=100)
                     Index Cond: (granular_scopes.id = personal_access_token_granular_scopes.granular_scope_id)
                     Filter: (((hashed SubPlan 2) AND (granular_scopes.access = ANY ('{0,2}'::integer[]))) OR ((granular_scopes.namespace_id IS NULL) AND (granular_scopes.access = 1)))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=300 read=5
                     I/O Timings: read=4.487 write=0.000
                     SubPlan 2
                       ->  Limit  (cost=0.43..0.50 rows=2 width=4) (actual time=3.905..4.548 rows=2 loops=1)
                             Buffers: shared read=5
                             I/O Timings: read=4.487 write=0.000
                             ->  Index Only Scan using index_groups_on_parent_id_id on public.namespaces  (cost=0.43..273332.10 rows=8764295 width=4) (actual time=3.905..4.547 rows=2 loops=1)
                                   Heap Fetches: 0
                                   Buffers: shared read=5
                                   I/O Timings: read=4.487 write=0.000
Settings: effective_cache_size = '472585MB', seq_page_cost = '4', work_mem = '100MB', random_page_cost = '1.5', jit = 'off'

Standalone boundary

Postgresql.ai: https://console.postgres.ai/shared/9d170180-0eaf-4759-81b8-3406ecd879c6

 HashAggregate  (cost=273.08..274.33 rows=100 width=32) (actual time=0.772..0.773 rows=1 loops=1)
   Group Key: jsonb_array_elements_text(granular_scopes.permissions)
   Buffers: shared hit=306
   I/O Timings: read=0.000 write=0.000
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.44..0.46 rows=1 width=4) (actual time=0.025..0.025 rows=1 loops=1)
           Buffers: shared hit=5
           I/O Timings: read=0.000 write=0.000
           ->  Index Only Scan Backward using personal_access_tokens_pkey on public.personal_access_tokens  (cost=0.44..753579.94 rows=29526974 width=4) (actual time=0.024..0.024 rows=1 loops=1)
                 Heap Fetches: 1
                 Buffers: shared hit=5
                 I/O Timings: read=0.000 write=0.000
   ->  ProjectSet  (cost=0.29..268.86 rows=1500 width=32) (actual time=0.076..0.759 rows=38 loops=1)
         Buffers: shared hit=306
         I/O Timings: read=0.000 write=0.000
         ->  Nested Loop  (cost=0.29..261.25 rows=15 width=17) (actual time=0.059..0.718 rows=38 loops=1)
               Buffers: shared hit=306
               I/O Timings: read=0.000 write=0.000
               ->  Seq Scan on public.personal_access_token_granular_scopes  (cost=0.00..5.25 rows=100 width=8) (actual time=0.030..0.040 rows=100 loops=1)
                     Filter: (personal_access_token_granular_scopes.personal_access_token_id = $0)
                     Rows Removed by Filter: 0
                     Buffers: shared hit=6
                     I/O Timings: read=0.000 write=0.000
               ->  Index Scan using granular_scopes_pkey on public.granular_scopes  (cost=0.29..2.56 rows=1 width=25) (actual time=0.006..0.006 rows=0 loops=100)
                     Index Cond: (granular_scopes.id = personal_access_token_granular_scopes.granular_scope_id)
                     Filter: ((granular_scopes.namespace_id IS NULL) AND (granular_scopes.access <> 1) AND (granular_scopes.access = 4))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=300
                     I/O Timings: read=0.000 write=0.000
Settings: work_mem = '100MB', random_page_cost = '1.5', jit = 'off', effective_cache_size = '472585MB', seq_page_cost = '4'

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Alex Buijs

Merge request reports