| -------------------------------------------------------------------------------- |
| -- Current implementation only supports SELECT and DELETE queries. |
| -- Foreign Scan / Dynamic Foreign Scan with row level security is not supported. |
| -- Any sort of index plans will not be generated by ORCA |
| -- for a relation having RLS enabled. |
| -- If the security quals contains sublinks then ORCA will fall back to planner. |
| -------------------------------------------------------------------------------- |
| -- NOTE: |
| -- The file rowsecurity.sql has a wider test coverage but the issue is most of |
| -- the queries in that file are falling back to planner because of not supported |
| -- features by ORCA such as non default collation, VIEW with WITH CHECK OPTION, |
| -- Queries on coordinator-only tables, Query has row level security enabled and |
| -- security quals contain sublinks etc. So rowsecurity.sql file is not providing |
| -- much help in testing ORCA side changes. |
| -- But on a high level some of the tests covered in rowsecurity.sql is |
| -- Table inheritance and RLS policy |
| -- There are tests related to partition relations but the test cases are |
| -- falling back due to various reasons like non default collation, VIEW with |
| -- WITH CHECK OPTION. |
| -- CTE with RLS |
| -- RLS with joins |
| -- View with WITH CHECK OPTION |
| -- Recursive Queries |
| -- Testing the output of queries by granting permissions to various roles |
| -- and groups and superuser database non-superuser with bypass privilege can |
| -- bypass RLS policy when disabled |
| --- RLS policy does not apply to table owner when RLS enabled. |
| -- RLS policy does not apply to table owner when RLS disabled. |
| -- only owner can change policies |
| -- FORCE ROW LEVEL SECURITY applies RLS to owners too |
| -- PREPARE statements |
| -- Duplicate policy names |
| -- Multiple policies on a table |
| -- INSERT in a table with no RLS by selecting from a relation having RLS |
| -- Default Deny policy |
| -- UPDATE/UPDATE with FROM clause/INSERT with Row-level security etc etc. |
| -- So the tests that have been covered in qp_rowsecurity are mostly similar to |
| -- that in rowsecurity.sql but since they are not falling back to planner, able |
| -- to verify the ORCA side changes. |
| -------------------------------------------------------------------------------- |
| ----- SELECT queries on relation having row level security enabled ------------- |
| -------------------------------------------------------------------------------- |
| set optimizer_trace_fallback to on; |
| create user new_user; |
| create table foo_rls ( a int, b int, c int) distributed by (a); |
| create table bar_rls ( p int, q int) distributed by (p); |
| insert into foo_rls select i,i*10,i*100 from generate_series(1,5)i; |
| insert into foo_rls values (NULL,50,500);; |
| insert into foo_rls values (NULL,60,NULL); |
| insert into foo_rls values (NULL,70,NULL); |
| insert into foo_rls values (NULL,NULL,600); |
| insert into foo_rls values (NULL,NULL,700); |
| vacuum analyze foo_rls; |
| grant select on foo_rls to new_user; |
| grant select on bar_rls to new_user; |
| alter table foo_rls enable row level security; |
| CREATE OR REPLACE FUNCTION leak_data(int) RETURNS boolean AS $$ |
| BEGIN |
| RAISE NOTICE 'Value of column is: %',$1; |
| RETURN true; |
| END; |
| $$ LANGUAGE plpgsql; |
| -- Permissive policy |
| create policy p1 on foo_rls using (a * 10 = b and c > 300); |
| set session authorization new_user; |
| -- Query plan will have the filter defined in the policy [(a * 10) = b) AND (c > 300)] |
| -- before query filter [leak_data(a)] |
| explain (costs off ) select * from foo_rls where leak_data(a); |
| QUERY PLAN |
| ----------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on foo_rls |
| Filter: ((c > 300) AND ((a * 10) = b) AND leak_data(a)) |
| Optimizer: Postgres-based planner |
| (4 rows) |
| |
| select * from foo_rls where leak_data(a); |
| NOTICE: Value of column is: 5 |
| NOTICE: Value of column is: 4 |
| a | b | c |
| ---+----+----- |
| 4 | 40 | 400 |
| 5 | 50 | 500 |
| (2 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_rls; |
| -- All permissive policies which are applicable to a given query |
| -- will be combined together using the Boolean “OR” operator |
| create policy p1 on foo_rls as permissive for select using (a * 10 >= 30 ); |
| create policy p2 on foo_rls as permissive for select |
| using (LEAST(a,b) >= 2); |
| set session authorization new_user; |
| -- The filter defined in the two permissive policies will be OR'ed together |
| explain (costs off )select * from foo_rls where leak_data(c); |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on foo_rls |
| Filter: (((LEAST(a, b) >= 2) OR ((a * 10) >= 30)) AND leak_data(c)) |
| Optimizer: Postgres-based planner |
| (4 rows) |
| |
| select * from foo_rls where leak_data(c); |
| NOTICE: Value of column is: 200 |
| NOTICE: Value of column is: 300 |
| NOTICE: Value of column is: 500 |
| NOTICE: Value of column is: 400 |
| NOTICE: Value of column is: 500 |
| NOTICE: Value of column is: <NULL> |
| NOTICE: Value of column is: <NULL> |
| a | b | c |
| ---+----+----- |
| 5 | 50 | 500 |
| 2 | 20 | 200 |
| 3 | 30 | 300 |
| 4 | 40 | 400 |
| | 50 | 500 |
| | 60 | |
| | 70 | |
| (7 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_rls; |
| drop policy p2 on foo_rls; |
| -- Restrictive policies |
| -- There needs to be at least one permissive policy to grant access to records |
| create policy p1 on foo_rls as restrictive for select using (a > 3); |
| create policy p2 on foo_rls as restrictive for select using (b > 35); |
| set session authorization new_user; |
| -- No permissive policies defined on the relation, so no records will be |
| -- returned |
| explain (costs off ) select * from foo_rls where leak_data(a); |
| QUERY PLAN |
| ----------------------------------- |
| Result |
| One-Time Filter: false |
| Optimizer: Postgres-based planner |
| (3 rows) |
| |
| reset session authorization ; |
| create policy p3 on foo_rls as permissive for select using (c % 100 = 0); |
| set session authorization new_user; |
| -- Now after creating a permissive policy, the query will return records and the |
| -- restrictive policies will be AND'ed with permissive policies |
| explain (costs off ) select * from foo_rls where leak_data(a); |
| QUERY PLAN |
| ----------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on foo_rls |
| Filter: ((a > 3) AND (b > 35) AND ((c % 100) = 0) AND leak_data(a)) |
| Optimizer: Postgres-based planner |
| (4 rows) |
| |
| select * from foo_rls where leak_data(a); |
| NOTICE: Value of column is: 4 |
| NOTICE: Value of column is: 5 |
| a | b | c |
| ---+----+----- |
| 5 | 50 | 500 |
| 4 | 40 | 400 |
| (2 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_rls; |
| drop policy p2 on foo_rls; |
| drop policy p3 on foo_rls; |
| -- The RTE of the relation containing security quals is present inside a |
| -- RTE_SUBQUERY rtekind |
| create policy p1 on foo_rls as permissive for select using (b + 1 = 4 ); |
| create policy p2 on foo_rls as permissive for select using (b is distinct |
| from c); |
| set session authorization new_user; |
| explain (costs off ) select * from (select * from foo_rls) t1, bar_rls where |
| t1.a=bar_rls.p and bar_rls.q is null and leak_data(c); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (bar_rls.p = foo_rls.a) |
| -> Seq Scan on bar_rls |
| Filter: (q IS NULL) |
| -> Hash |
| -> Seq Scan on foo_rls |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND leak_data(c)) |
| Optimizer: Postgres-based planner |
| (9 rows) |
| |
| -- The RTE of the relation containing security quals is present inside a |
| -- SUBLINK node |
| explain (costs off ) select * from bar_rls where bar_rls.p in (select a |
| from foo_rls where abs(a) >= 10 and leak_data(c)) and bar_rls.q is not null; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Semi Join |
| Hash Cond: (bar_rls.p = foo_rls.a) |
| -> Seq Scan on bar_rls |
| Filter: (q IS NOT NULL) |
| -> Hash |
| -> Seq Scan on foo_rls |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND (abs(a) >= 10) AND leak_data(c)) |
| Optimizer: Postgres-based planner |
| (9 rows) |
| |
| -- The RTE of the relation containing security quals is present inside a CTE |
| explain (costs off ) with cte as (select * from foo_rls where |
| a > LEAST(a,b)) select * from cte left join bar_rls on |
| cte.a=bar_rls.p and q = 10 and leak_data(cte.a); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Right Join |
| Hash Cond: (bar_rls.p = foo_rls.a) |
| Join Filter: leak_data(foo_rls.a) |
| -> Seq Scan on bar_rls |
| Filter: (q = 10) |
| -> Hash |
| -> Seq Scan on foo_rls |
| Filter: ((a > LEAST(a, b)) AND ((b IS DISTINCT FROM c) OR ((b + 1) = 4))) |
| Optimizer: Postgres-based planner |
| (10 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_rls; |
| drop policy p2 on foo_rls; |
| -- ORCA will fallback to planner if SUBLINK is present inside a security qual |
| create policy p1 on foo_rls as permissive for select using (a in (select p |
| from bar_rls)); |
| set session authorization new_user; |
| explain (costs off ) select * from foo_rls; |
| QUERY PLAN |
| ----------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on foo_rls |
| Filter: (hashed SubPlan 1) |
| SubPlan 1 |
| -> Broadcast Motion 3:3 (slice2; segments: 3) |
| -> Seq Scan on bar_rls |
| Optimizer: Postgres-based planner |
| (7 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_rls; |
| -- ORCA will not generate any index plans when row level security is enabled |
| -- for a relation |
| create policy p1 on foo_rls as permissive for select using (b = 10); |
| create index btindex on foo_rls using btree(a); |
| -- Index Scan is generated by ORCA when the owner of the table runs the query |
| set enable_seqscan to off; |
| explain (costs off) select * from foo_rls where a is null and b = 10; |
| QUERY PLAN |
| ------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Index Scan using btindex on foo_rls |
| Index Cond: (a IS NULL) |
| Filter: (b = 10) |
| Optimizer: Postgres-based planner |
| (5 rows) |
| |
| -- Index Scan is not generated by ORCA when RLS enabled by ORCA |
| set session authorization new_user; |
| explain (costs off) select * from foo_rls where a is null; |
| QUERY PLAN |
| ------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Index Scan using btindex on foo_rls |
| Index Cond: (a IS NULL) |
| Filter: (b = 10) |
| Optimizer: Postgres-based planner |
| (5 rows) |
| |
| reset session authorization; |
| -- Index Only Scan is generated by ORCA when the owner of the table runs the query |
| set optimizer_enable_indexscan to off; |
| explain (costs off) select a from foo_rls where a is null; |
| QUERY PLAN |
| ------------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Index Only Scan using btindex on foo_rls |
| Index Cond: (a IS NULL) |
| Optimizer: Postgres-based planner |
| (4 rows) |
| |
| -- Index Only Scan is not generated by ORCA when RLS enabled |
| set session authorization new_user; |
| explain (costs off) select a from foo_rls where a is null; |
| QUERY PLAN |
| ------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Index Scan using btindex on foo_rls |
| Index Cond: (a IS NULL) |
| Filter: (b = 10) |
| Optimizer: Postgres-based planner |
| (5 rows) |
| |
| reset session authorization; |
| set optimizer_enable_indexscan to on; |
| reset enable_seqscan; |
| drop policy p1 on foo_rls; |
| drop table foo_rls cascade; |
| drop table bar_rls cascade; |
| -------------------------------------------------------------------------------- |
| ----------- SELECT queries on partitioned tables with RLS enabled -------------- |
| -------------------------------------------------------------------------------- |
| create table foo_part_rls ( a int, b int, c int) distributed by (a) |
| partition by range(b) (start(0) end(100) every(20), default partition other_part); |
| create table bar_part_rls ( p int, q int) distributed by (p) partition by range(q) |
| (start(0) end(100) every(20)); |
| insert into foo_part_rls select i,i*10,i*100 from generate_series(1,5)i; |
| insert into foo_part_rls values (NULL,50,500);; |
| insert into foo_part_rls values (NULL,60,NULL); |
| insert into foo_part_rls values (NULL,70,NULL); |
| insert into foo_part_rls values (NULL,NULL,600); |
| insert into foo_part_rls values (NULL,NULL,700); |
| analyze foo_part_rls; |
| grant select on foo_part_rls to new_user; |
| grant select on bar_part_rls to new_user; |
| alter table foo_part_rls enable row level security; |
| -- Permissive policy |
| create policy p1 on foo_part_rls using (a * 10 = b and c > 300); |
| set session authorization new_user; |
| -- Query plan will have the filter defined in the policy [(a * 10) = b) AND (c > 300)] |
| -- before query filter [leak_data(a)] |
| explain (costs off ) select * from foo_part_rls where leak_data(a); |
| QUERY PLAN |
| ----------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Seq Scan on foo_part_rls_1_prt_2 foo_part_rls_1 |
| Filter: ((c > 300) AND ((a * 10) = b) AND leak_data(a)) |
| -> Seq Scan on foo_part_rls_1_prt_3 foo_part_rls_2 |
| Filter: ((c > 300) AND ((a * 10) = b) AND leak_data(a)) |
| -> Seq Scan on foo_part_rls_1_prt_4 foo_part_rls_3 |
| Filter: ((c > 300) AND ((a * 10) = b) AND leak_data(a)) |
| -> Seq Scan on foo_part_rls_1_prt_5 foo_part_rls_4 |
| Filter: ((c > 300) AND ((a * 10) = b) AND leak_data(a)) |
| -> Seq Scan on foo_part_rls_1_prt_6 foo_part_rls_5 |
| Filter: ((c > 300) AND ((a * 10) = b) AND leak_data(a)) |
| -> Seq Scan on foo_part_rls_1_prt_other_part foo_part_rls_6 |
| Filter: ((c > 300) AND ((a * 10) = b) AND leak_data(a)) |
| Optimizer: Postgres query optimizer |
| (15 rows) |
| |
| select * from foo_part_rls where leak_data(a); |
| NOTICE: Value of column is: 4 |
| NOTICE: Value of column is: 5 |
| a | b | c |
| ---+----+----- |
| 5 | 50 | 500 |
| 4 | 40 | 400 |
| (2 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_part_rls; |
| -- All permissive policies which are applicable to a given query |
| -- will be combined together using the Boolean “OR” operator |
| create policy p1 on foo_part_rls as permissive for select using (a * 10 >= 30 ); |
| create policy p2 on foo_part_rls as permissive for select |
| using (LEAST(a,b) >= 2); |
| set session authorization new_user; |
| -- The filter defined in the two permissive policies will be OR'ed together |
| explain (costs off )select * from foo_part_rls where leak_data(c); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Seq Scan on foo_part_rls_1_prt_2 foo_part_rls_1 |
| Filter: (((LEAST(a, b) >= 2) OR ((a * 10) >= 30)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_3 foo_part_rls_2 |
| Filter: (((LEAST(a, b) >= 2) OR ((a * 10) >= 30)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_4 foo_part_rls_3 |
| Filter: (((LEAST(a, b) >= 2) OR ((a * 10) >= 30)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_5 foo_part_rls_4 |
| Filter: (((LEAST(a, b) >= 2) OR ((a * 10) >= 30)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_6 foo_part_rls_5 |
| Filter: (((LEAST(a, b) >= 2) OR ((a * 10) >= 30)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_other_part foo_part_rls_6 |
| Filter: (((LEAST(a, b) >= 2) OR ((a * 10) >= 30)) AND leak_data(c)) |
| Optimizer: Postgres query optimizer |
| (15 rows) |
| |
| select * from foo_part_rls where leak_data(c); |
| NOTICE: Value of column is: 200 |
| NOTICE: Value of column is: 300 |
| NOTICE: Value of column is: 500 |
| NOTICE: Value of column is: 400 |
| NOTICE: Value of column is: 500 |
| NOTICE: Value of column is: <NULL> |
| NOTICE: Value of column is: <NULL> |
| a | b | c |
| ---+----+----- |
| 5 | 50 | 500 |
| 2 | 20 | 200 |
| 3 | 30 | 300 |
| 4 | 40 | 400 |
| | 50 | 500 |
| | 60 | |
| | 70 | |
| (7 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_part_rls; |
| drop policy p2 on foo_part_rls; |
| -- Restrictive policies |
| -- There needs to be at least one permissive policy to grant access to records |
| create policy p1 on foo_part_rls as restrictive for select using (a > 3); |
| create policy p2 on foo_part_rls as restrictive for select using (b > 35); |
| set session authorization new_user; |
| -- No permissive policies defined on the relation, so no records will be |
| -- returned |
| explain (costs off ) select * from foo_part_rls where leak_data(a); |
| QUERY PLAN |
| ----------------------------------- |
| Result |
| One-Time Filter: false |
| Optimizer: Postgres-based planner |
| (3 rows) |
| |
| reset session authorization ; |
| create policy p3 on foo_part_rls as permissive for select using (c % 100 = 0); |
| set session authorization new_user; |
| -- Now after creating a permissive policy, the query will return records and the |
| -- restrictive policies will be AND'ed with permissive policies |
| explain (costs off ) select * from foo_part_rls where leak_data(a); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Seq Scan on foo_part_rls_1_prt_3 foo_part_rls_1 |
| Filter: ((a > 3) AND (b > 35) AND ((c % 100) = 0) AND leak_data(a)) |
| -> Seq Scan on foo_part_rls_1_prt_4 foo_part_rls_2 |
| Filter: ((a > 3) AND (b > 35) AND ((c % 100) = 0) AND leak_data(a)) |
| -> Seq Scan on foo_part_rls_1_prt_5 foo_part_rls_3 |
| Filter: ((a > 3) AND (b > 35) AND ((c % 100) = 0) AND leak_data(a)) |
| -> Seq Scan on foo_part_rls_1_prt_6 foo_part_rls_4 |
| Filter: ((a > 3) AND (b > 35) AND ((c % 100) = 0) AND leak_data(a)) |
| -> Seq Scan on foo_part_rls_1_prt_other_part foo_part_rls_5 |
| Filter: ((a > 3) AND (b > 35) AND ((c % 100) = 0) AND leak_data(a)) |
| Optimizer: Postgres query optimizer |
| (13 rows) |
| |
| select * from foo_part_rls where leak_data(a); |
| NOTICE: Value of column is: 4 |
| NOTICE: Value of column is: 5 |
| a | b | c |
| ---+----+----- |
| 5 | 50 | 500 |
| 4 | 40 | 400 |
| (2 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_part_rls; |
| drop policy p2 on foo_part_rls; |
| drop policy p3 on foo_part_rls; |
| -- The RTE of the relation containing security quals is present inside a |
| -- RTE_SUBQUERY rtekind |
| create policy p1 on foo_part_rls as permissive for select using (b + 1 = 4 ); |
| create policy p2 on foo_part_rls as permissive for select using (b is distinct |
| from c); |
| set session authorization new_user; |
| explain (costs off ) select * from (select * from foo_part_rls) t1, bar_part_rls |
| where t1.a=bar_part_rls.p and bar_part_rls.q is not null and leak_data(c); |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Join |
| Hash Cond: (bar_part_rls.p = foo_part_rls.a) |
| -> Append |
| -> Seq Scan on bar_part_rls_1_prt_1 bar_part_rls_1 |
| Filter: (q IS NOT NULL) |
| -> Seq Scan on bar_part_rls_1_prt_2 bar_part_rls_2 |
| Filter: (q IS NOT NULL) |
| -> Seq Scan on bar_part_rls_1_prt_3 bar_part_rls_3 |
| Filter: (q IS NOT NULL) |
| -> Seq Scan on bar_part_rls_1_prt_4 bar_part_rls_4 |
| Filter: (q IS NOT NULL) |
| -> Seq Scan on bar_part_rls_1_prt_5 bar_part_rls_5 |
| Filter: (q IS NOT NULL) |
| -> Hash |
| -> Append |
| -> Seq Scan on foo_part_rls_1_prt_2 foo_part_rls_1 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_3 foo_part_rls_2 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_4 foo_part_rls_3 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_5 foo_part_rls_4 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_6 foo_part_rls_5 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_other_part foo_part_rls_6 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND leak_data(c)) |
| Optimizer: Postgres query optimizer |
| (29 rows) |
| |
| -- The RTE of the relation containing security quals is present inside a |
| -- SUBLINK node |
| explain (costs off ) select * from bar_part_rls where bar_part_rls.p in |
| (select a from foo_part_rls where abs(a) >= 10 and leak_data(c)) and |
| bar_part_rls.q is not null; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Semi Join |
| Hash Cond: (bar_part_rls.p = foo_part_rls.a) |
| -> Append |
| -> Seq Scan on bar_part_rls_1_prt_1 bar_part_rls_1 |
| Filter: (q IS NOT NULL) |
| -> Seq Scan on bar_part_rls_1_prt_2 bar_part_rls_2 |
| Filter: (q IS NOT NULL) |
| -> Seq Scan on bar_part_rls_1_prt_3 bar_part_rls_3 |
| Filter: (q IS NOT NULL) |
| -> Seq Scan on bar_part_rls_1_prt_4 bar_part_rls_4 |
| Filter: (q IS NOT NULL) |
| -> Seq Scan on bar_part_rls_1_prt_5 bar_part_rls_5 |
| Filter: (q IS NOT NULL) |
| -> Hash |
| -> Append |
| -> Seq Scan on foo_part_rls_1_prt_2 foo_part_rls_1 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND (abs(a) >= 10) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_3 foo_part_rls_2 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND (abs(a) >= 10) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_4 foo_part_rls_3 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND (abs(a) >= 10) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_5 foo_part_rls_4 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND (abs(a) >= 10) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_6 foo_part_rls_5 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND (abs(a) >= 10) AND leak_data(c)) |
| -> Seq Scan on foo_part_rls_1_prt_other_part foo_part_rls_6 |
| Filter: (((b IS DISTINCT FROM c) OR ((b + 1) = 4)) AND (abs(a) >= 10) AND leak_data(c)) |
| Optimizer: Postgres query optimizer |
| (29 rows) |
| |
| -- The RTE of the relation containing security quals is present inside a CTE |
| explain (costs off ) with cte as (select * from foo_part_rls where |
| a > LEAST(a,b)) select * from cte left join bar_part_rls on |
| cte.a=bar_part_rls.p and q = 10 and leak_data(cte.a); |
| QUERY PLAN |
| ----------------------------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Hash Right Join |
| Hash Cond: (bar_part_rls.p = foo_part_rls.a) |
| Join Filter: leak_data(foo_part_rls.a) |
| -> Seq Scan on bar_part_rls_1_prt_1 bar_part_rls |
| Filter: (q = 10) |
| -> Hash |
| -> Append |
| -> Seq Scan on foo_part_rls_1_prt_2 foo_part_rls_1 |
| Filter: ((a > LEAST(a, b)) AND ((b IS DISTINCT FROM c) OR ((b + 1) = 4))) |
| -> Seq Scan on foo_part_rls_1_prt_3 foo_part_rls_2 |
| Filter: ((a > LEAST(a, b)) AND ((b IS DISTINCT FROM c) OR ((b + 1) = 4))) |
| -> Seq Scan on foo_part_rls_1_prt_4 foo_part_rls_3 |
| Filter: ((a > LEAST(a, b)) AND ((b IS DISTINCT FROM c) OR ((b + 1) = 4))) |
| -> Seq Scan on foo_part_rls_1_prt_5 foo_part_rls_4 |
| Filter: ((a > LEAST(a, b)) AND ((b IS DISTINCT FROM c) OR ((b + 1) = 4))) |
| -> Seq Scan on foo_part_rls_1_prt_6 foo_part_rls_5 |
| Filter: ((a > LEAST(a, b)) AND ((b IS DISTINCT FROM c) OR ((b + 1) = 4))) |
| -> Seq Scan on foo_part_rls_1_prt_other_part foo_part_rls_6 |
| Filter: ((a > LEAST(a, b)) AND ((b IS DISTINCT FROM c) OR ((b + 1) = 4))) |
| Optimizer: Postgres query optimizer |
| (21 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_part_rls; |
| drop policy p2 on foo_part_rls; |
| -- ORCA will fallback to planner if SUBLINK is present inside a security qual |
| create policy p1 on foo_part_rls as permissive for select using (a in |
| (select p from bar_part_rls)); |
| set session authorization new_user; |
| explain (costs off ) select * from foo_part_rls where foo_part_rls.b <15; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Seq Scan on foo_part_rls_1_prt_2 foo_part_rls_1 |
| Filter: ((hashed SubPlan 1 (copy 2)) AND (b < 15)) |
| SubPlan 1 (copy 2) |
| -> Broadcast Motion 3:3 (slice2; segments: 3) |
| -> Append |
| -> Seq Scan on bar_part_rls_1_prt_1 bar_part_rls_1 |
| -> Seq Scan on bar_part_rls_1_prt_2 bar_part_rls_2 |
| -> Seq Scan on bar_part_rls_1_prt_3 bar_part_rls_3 |
| -> Seq Scan on bar_part_rls_1_prt_4 bar_part_rls_4 |
| -> Seq Scan on bar_part_rls_1_prt_5 bar_part_rls_5 |
| -> Seq Scan on foo_part_rls_1_prt_other_part foo_part_rls_2 |
| Filter: ((hashed SubPlan 1 (copy 3)) AND (b < 15)) |
| SubPlan 1 (copy 3) |
| -> Broadcast Motion 3:3 (slice3; segments: 3) |
| -> Append |
| -> Seq Scan on bar_part_rls_1_prt_1 bar_part_rls_7 |
| -> Seq Scan on bar_part_rls_1_prt_2 bar_part_rls_8 |
| -> Seq Scan on bar_part_rls_1_prt_3 bar_part_rls_9 |
| -> Seq Scan on bar_part_rls_1_prt_4 bar_part_rls_10 |
| -> Seq Scan on bar_part_rls_1_prt_5 bar_part_rls_11 |
| Optimizer: Postgres query optimizer |
| (23 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_part_rls; |
| -- ORCA will not generate any index plans when row level security is enabled |
| -- for a relation |
| create policy p1 on foo_part_rls as permissive for select using (b = 10); |
| create index btindex on foo_part_rls using btree(a); |
| -- Dynamic Index Scan is generated by ORCA when the owner of the table runs |
| -- the query |
| set enable_seqscan to off; |
| explain (costs off) select * from foo_part_rls where a is null; |
| QUERY PLAN |
| ------------------------------------------------------------------------------------------------------------------ |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Append |
| -> Index Scan using foo_part_rls_1_prt_2_a_idx on foo_part_rls_1_prt_2 foo_part_rls_1 |
| Index Cond: (a IS NULL) |
| -> Index Scan using foo_part_rls_1_prt_3_a_idx on foo_part_rls_1_prt_3 foo_part_rls_2 |
| Index Cond: (a IS NULL) |
| -> Index Scan using foo_part_rls_1_prt_4_a_idx on foo_part_rls_1_prt_4 foo_part_rls_3 |
| Index Cond: (a IS NULL) |
| -> Index Scan using foo_part_rls_1_prt_5_a_idx on foo_part_rls_1_prt_5 foo_part_rls_4 |
| Index Cond: (a IS NULL) |
| -> Index Scan using foo_part_rls_1_prt_6_a_idx on foo_part_rls_1_prt_6 foo_part_rls_5 |
| Index Cond: (a IS NULL) |
| -> Index Scan using foo_part_rls_1_prt_other_part_a_idx on foo_part_rls_1_prt_other_part foo_part_rls_6 |
| Index Cond: (a IS NULL) |
| Optimizer: Postgres query optimizer |
| (15 rows) |
| |
| -- Dynamic Index Scan is not generated by ORCA when RLS is enabled |
| set session authorization new_user; |
| explain (costs off) select * from foo_part_rls where a is null; |
| QUERY PLAN |
| ---------------------------------------------------------------------------------------- |
| Gather Motion 1:1 (slice1; segments: 1) |
| -> Index Scan using foo_part_rls_1_prt_2_a_idx on foo_part_rls_1_prt_2 foo_part_rls |
| Index Cond: (a IS NULL) |
| Filter: (b = 10) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| reset session authorization; |
| reset enable_seqscan; |
| drop policy p1 on foo_part_rls; |
| drop table foo_part_rls cascade; |
| drop table bar_part_rls cascade; |
| drop function leak_data(int); |
| -------------------------------------------------------------------------------- |
| ----------------- Creating policies on leaf partitions ------------------------- |
| -------------------------------------------------------------------------------- |
| create table rootpart (a int, b int) distributed by (a) partition by range(a) |
| (start(0) end(10) every(5)); |
| insert into rootpart select i,i from generate_series(0,9)i; |
| -- select permission is only for root partition and not for child partition |
| grant select on rootpart to new_user; |
| alter table rootpart_1_prt_2 enable row level security; |
| create policy p1 on rootpart_1_prt_2 using (a>=7); |
| set session authorization new_user; |
| explain (costs off) select * from rootpart; |
| QUERY PLAN |
| ----------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Seq Scan on rootpart_1_prt_1 rootpart_1 |
| -> Seq Scan on rootpart_1_prt_2 rootpart_2 |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| -- Issue #16961 |
| -- ORCA and planner should not generate a plan and show error permission denied |
| -- for leaf partition |
| explain (costs off) select * from rootpart_1_prt_2; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on rootpart_1_prt_2 |
| Filter: (a >= 7) |
| Optimizer: Postgres query optimizer |
| (4 rows) |
| |
| reset session authorization; |
| drop policy p1 on rootpart_1_prt_2; |
| drop table rootpart; |
| -------------------------------------------------------------------------------- |
| ----------------- Grant SELECT to only a leaf partition ------------------------ |
| -------------------------------------------------------------------------------- |
| create table rootpart (a int, b int) distributed by (a) partition by range(a) |
| (start(0) end(10) every(5)); |
| insert into rootpart select i,i from generate_series(0,9)i; |
| grant select on rootpart_1_prt_2 to new_user; |
| alter table rootpart_1_prt_2 enable row level security; |
| create policy p1 on rootpart_1_prt_2 using (a>=7); |
| set session authorization new_user; |
| -- Since the SELECT permission is only for a leaf partition [rootpart_1_prt_2], |
| -- querying the root will error out |
| explain (costs off) select * from rootpart; |
| ERROR: permission denied for table rootpart |
| explain (costs off) select * from rootpart_1_prt_1; |
| ERROR: permission denied for table rootpart_1_prt_1 |
| -- Below query will work fine as the user has SELECT permission on this root |
| -- partition |
| explain (costs off) select * from rootpart_1_prt_2; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on rootpart_1_prt_2 |
| Filter: (a >= 7) |
| Optimizer: Postgres-based planner |
| (4 rows) |
| |
| reset session authorization; |
| drop policy p1 on rootpart_1_prt_2; |
| drop table rootpart; |
| -------------------------------------------------------------------------------- |
| -------------------------- Multilevel Partitions ------------------------------- |
| -------------------------------------------------------------------------------- |
| create table rootTab(a int) distributed by (a) partition by range(a); |
| create table roottabmid1(a int) distributed by (a) partition by range(a); |
| create table roottableaf1(a int) distributed by (a); |
| create table roottableaf2(a int) distributed by (a); |
| alter table roottab attach partition roottabmid1 for values from (0) to (10); |
| alter table roottabmid1 attach partition roottableaf1 for values from (0) to (5); |
| alter table roottabmid1 attach partition roottableaf2 for values from (5) to (10); |
| insert into roottab select i from generate_series(0,9)i; |
| grant select on roottab to new_user; |
| alter table roottableaf2 enable row level security; |
| create policy p1 on roottableaf2 using (a>=7); |
| set session authorization new_user; |
| -- Querying the root will fallback to planner as ORCA doesn't supports |
| -- Multi-level partitioned tables |
| explain (costs off) select * from roottab; |
| QUERY PLAN |
| ------------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Seq Scan on roottableaf1 roottab_1 |
| -> Seq Scan on roottableaf2 roottab_2 |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| explain (costs off) select * from roottabmid1; |
| QUERY PLAN |
| ---------------------------------------------------- |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Seq Scan on roottableaf1 roottabmid1_1 |
| -> Seq Scan on roottableaf2 roottabmid1_2 |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| explain (costs off) select * from roottableaf1; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on roottableaf1 |
| Optimizer: Postgres-based planner |
| (3 rows) |
| |
| explain (costs off) select * from roottableaf2; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on roottableaf2 |
| Filter: (a >= 7) |
| Optimizer: Postgres-based planner |
| (4 rows) |
| |
| reset session authorization; |
| drop policy p1 on roottableaf2; |
| drop table roottab; |
| -------------------------------------------------------------------------------- |
| ------------------- Inherited Tables with RLS enabled -------------------------- |
| -------------------------------------------------------------------------------- |
| create table parent (a int, b int) distributed by (a); |
| create table child1 (c int) inherits (parent); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| create table child2 (d int) inherits (parent); |
| NOTICE: table has parent, setting distribution columns to match parent table |
| -- SELECT permission on parent |
| grant select on parent to new_user; |
| alter table parent enable row level security; |
| create policy p1 on parent using (a>=7); |
| set session authorization new_user; |
| -- Querying the parent will fallback to planner as ORCA doesn't supports |
| -- Inherited tables |
| explain (costs off) select * from parent; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Append |
| -> Seq Scan on parent parent_1 |
| Filter: (a >= 7) |
| -> Seq Scan on child1 parent_2 |
| Filter: (a >= 7) |
| -> Seq Scan on child2 parent_3 |
| Filter: (a >= 7) |
| Optimizer: Postgres-based planner |
| (9 rows) |
| |
| -- Below queries will error out as no SELECT permission for user on child |
| -- relations |
| explain (costs off) select * from child1; |
| ERROR: permission denied for table child1 |
| explain (costs off) select * from child2; |
| ERROR: permission denied for table child2 |
| reset session authorization; |
| grant select on child1 to new_user; |
| set session authorization new_user; |
| -- After granting SELECT permission the below query will work |
| explain (costs off) select * from child1; |
| QUERY PLAN |
| ------------------------------------------ |
| Gather Motion 3:1 (slice1; segments: 3) |
| -> Seq Scan on child1 |
| Optimizer: Postgres-based planner |
| (3 rows) |
| |
| reset session authorization; |
| drop policy p1 on parent; |
| drop table parent cascade; |
| NOTICE: drop cascades to 2 other objects |
| DETAIL: drop cascades to table child1 |
| drop cascades to table child2 |
| -------------------------------------------------------------------------------- |
| ----- INSERT queries on relation having row level security enabled ------------- |
| ------ ORCA will fallback to planner for INSERT with RLS anabled --------------- |
| -------------------------------------------------------------------------------- |
| create table foo_rls ( a int, b int, c int) distributed by (a); |
| insert into foo_rls select i,i*10,i*100 from generate_series(1,5)i; |
| analyze foo_rls; |
| grant insert on foo_rls to new_user; |
| alter table foo_rls enable row level security; |
| create policy p1 on foo_rls using (c>=3); |
| set session authorization new_user; |
| explain (costs off) insert into foo_rls values (1,1,1); |
| QUERY PLAN |
| ----------------------------------- |
| Insert on foo_rls |
| -> Result |
| Optimizer: Postgres-based planner |
| (3 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_rls; |
| drop table foo_rls cascade; |
| -------------------------------------------------------------------------------- |
| --------- INSERT queries on partitioned relation having RLS enabled ------------ |
| --------- ORCA will fallback to planner for INSERT with RLS anabled ------------ |
| -------------------------------------------------------------------------------- |
| create table foo_part_rls ( a int, b int, c int) distributed by (a) |
| partition by range(b) (start(0) end(100) every(20)); |
| insert into foo_part_rls select i,i*10,i*100 from generate_series(1,9)i; |
| analyze foo_part_rls; |
| grant insert on foo_part_rls to new_user; |
| alter table foo_part_rls enable row level security; |
| create policy p1 on foo_part_rls using (c>=500); |
| set session authorization new_user; |
| explain (costs off) insert into foo_part_rls values (-10,-10,-10); |
| QUERY PLAN |
| ----------------------------------- |
| Insert on foo_part_rls |
| -> Result |
| Optimizer: Postgres-based planner |
| (3 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_part_rls; |
| drop table foo_part_rls cascade; |
| -------------------------------------------------------------------------------- |
| ----- UPDATE queries on relation having row level security enabled ------------- |
| ------ ORCA will fallback to planner for UPDATE with RLS anabled --------------- |
| -------------------------------------------------------------------------------- |
| create table foo_rls ( a int, b int, c int) distributed by (a); |
| insert into foo_rls select i,i*10,i*100 from generate_series(1,5)i; |
| analyze foo_rls; |
| grant select,update on foo_rls to new_user; |
| alter table foo_rls enable row level security; |
| create policy p1 on foo_rls using (c>=3); |
| set session authorization new_user; |
| explain (costs off) update foo_rls set a=-10 where c=4; |
| QUERY PLAN |
| --------------------------------------------------------------- |
| Update on foo_rls |
| -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) |
| -> Split |
| -> Seq Scan on foo_rls |
| Filter: ((c >= 3) AND (c = 4)) |
| Optimizer: Postgres-based planner |
| (6 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_rls; |
| drop table foo_rls cascade; |
| -------------------------------------------------------------------------------- |
| --------- UPDATE queries on partitioned relation having RLS enabled ------------ |
| --------- ORCA will fallback to planner for UPDATE with RLS anabled ------------ |
| -------------------------------------------------------------------------------- |
| create table foo_part_rls ( a int, b int, c int) distributed by (a) |
| partition by range(b) (start(0) end(100) every(20)); |
| insert into foo_part_rls select i,i*10,i*100 from generate_series(1,9)i; |
| analyze foo_part_rls; |
| grant select,update on foo_part_rls to new_user; |
| alter table foo_part_rls enable row level security; |
| create policy p1 on foo_part_rls using (c>=500); |
| set session authorization new_user; |
| explain (costs off) update foo_part_rls set a=10 where foo_part_rls.c = 800; |
| QUERY PLAN |
| ------------------------------------------------------------------------- |
| Update on foo_part_rls |
| Update on foo_part_rls_1_prt_1 foo_part_rls_1 |
| Update on foo_part_rls_1_prt_2 foo_part_rls_2 |
| Update on foo_part_rls_1_prt_3 foo_part_rls_3 |
| Update on foo_part_rls_1_prt_4 foo_part_rls_4 |
| Update on foo_part_rls_1_prt_5 foo_part_rls_5 |
| -> Explicit Redistribute Motion 3:3 (slice1; segments: 3) |
| -> Split |
| -> Append |
| -> Seq Scan on foo_part_rls_1_prt_1 foo_part_rls_1 |
| Filter: ((c >= 500) AND (c = 800)) |
| -> Seq Scan on foo_part_rls_1_prt_2 foo_part_rls_2 |
| Filter: ((c >= 500) AND (c = 800)) |
| -> Seq Scan on foo_part_rls_1_prt_3 foo_part_rls_3 |
| Filter: ((c >= 500) AND (c = 800)) |
| -> Seq Scan on foo_part_rls_1_prt_4 foo_part_rls_4 |
| Filter: ((c >= 500) AND (c = 800)) |
| -> Seq Scan on foo_part_rls_1_prt_5 foo_part_rls_5 |
| Filter: ((c >= 500) AND (c = 800)) |
| Optimizer: Postgres query optimizer |
| (20 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_part_rls; |
| drop table foo_part_rls cascade; |
| -------------------------------------------------------------------------------- |
| ----- DELETE queries on relation having row level security enabled ------------- |
| -------------------------------------------------------------------------------- |
| create table foo_rls ( a int, b int, c int) distributed by (a); |
| insert into foo_rls select i,i*10,i*100 from generate_series(1,5)i; |
| analyze foo_rls; |
| grant select,delete on foo_rls to new_user; |
| alter table foo_rls enable row level security; |
| create policy p1 on foo_rls using (c>=300); |
| set session authorization new_user; |
| explain (costs off) delete from foo_rls where foo_rls.b=30; |
| QUERY PLAN |
| ------------------------------------------- |
| Delete on foo_rls |
| -> Seq Scan on foo_rls |
| Filter: ((c >= 300) AND (b = 30)) |
| Optimizer: Postgres-based planner |
| (4 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_rls; |
| drop table foo_rls cascade; |
| -------------------------------------------------------------------------------- |
| ----- DELETE queries on on partitioned relation having RLS enabled ------------- |
| -------------------------------------------------------------------------------- |
| create table foo_part_rls ( a int, b int, c int) distributed by (a) |
| partition by range(b) (start(0) end(100) every(20)); |
| insert into foo_part_rls select i,i*10,i*100 from generate_series(1,9)i; |
| analyze foo_part_rls; |
| grant select,delete on foo_part_rls to new_user; |
| alter table foo_part_rls enable row level security; |
| create policy p1 on foo_part_rls using (c>=300); |
| set session authorization new_user; |
| explain (costs off) delete from foo_part_rls where foo_part_rls.b=30; |
| QUERY PLAN |
| ------------------------------------------------------- |
| Delete on foo_part_rls |
| Delete on foo_part_rls_1_prt_2 foo_part_rls_1 |
| -> Seq Scan on foo_part_rls_1_prt_2 foo_part_rls_1 |
| Filter: ((c >= 300) AND (b = 30)) |
| Optimizer: Postgres query optimizer |
| (5 rows) |
| |
| reset session authorization; |
| drop policy p1 on foo_part_rls; |
| drop table foo_part_rls cascade; |
| drop user new_user; |
| reset optimizer_trace_fallback; |
