src/test/regress/sql/qp_rowsecurity.sql

--------------------------------------------------------------------------------
-- 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);
select * from foo_rls where leak_data(a);
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);
select * from foo_rls where leak_data(c);
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);
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);
select * from foo_rls where leak_data(a);
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);

-- 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;

-- 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);

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;
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;
-- 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;
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;
-- 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;
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);
select * from foo_part_rls where leak_data(a);
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);
select * from foo_part_rls where leak_data(c);
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);
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);
select * from foo_part_rls where leak_data(a);
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);

-- 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;

-- 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);

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;
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;
-- 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;
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;
-- 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;
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;
explain (costs off) select * from 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;
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;
explain (costs off) select * from roottabmid1;
explain (costs off) select * from roottableaf1;
explain (costs off) select * from roottableaf2;
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);
create table child2 (d int) inherits (parent);
-- 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;
-- Below queries will error out as no SELECT permission for user on child
-- relations
explain (costs off) select * from child1;
explain (costs off) select * from 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;
reset session authorization;
drop policy p1 on parent;
drop table parent cascade;


--------------------------------------------------------------------------------
----- 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);
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);
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;
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;
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;
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;
reset session authorization;
drop policy p1 on foo_part_rls;
drop table foo_part_rls cascade;
drop user new_user;
reset optimizer_trace_fallback;