1LOAD 'test_rls_hooks'; 2CREATE TABLE rls_test_permissive ( 3 username name, 4 supervisor name, 5 data integer 6); 7-- initial test data 8INSERT INTO rls_test_permissive VALUES ('regress_r1','regress_s1',4); 9INSERT INTO rls_test_permissive VALUES ('regress_r2','regress_s2',5); 10INSERT INTO rls_test_permissive VALUES ('regress_r3','regress_s3',6); 11CREATE TABLE rls_test_restrictive ( 12 username name, 13 supervisor name, 14 data integer 15); 16-- At least one permissive policy must exist, otherwise 17-- the default deny policy will be applied. For 18-- testing the only-restrictive-policies from the hook, 19-- create a simple 'allow all' policy. 20CREATE POLICY p1 ON rls_test_restrictive USING (true); 21-- initial test data 22INSERT INTO rls_test_restrictive VALUES ('regress_r1','regress_s1',1); 23INSERT INTO rls_test_restrictive VALUES ('regress_r2','regress_s2',2); 24INSERT INTO rls_test_restrictive VALUES ('regress_r3','regress_s3',3); 25CREATE TABLE rls_test_both ( 26 username name, 27 supervisor name, 28 data integer 29); 30-- initial test data 31INSERT INTO rls_test_both VALUES ('regress_r1','regress_s1',7); 32INSERT INTO rls_test_both VALUES ('regress_r2','regress_s2',8); 33INSERT INTO rls_test_both VALUES ('regress_r3','regress_s3',9); 34ALTER TABLE rls_test_permissive ENABLE ROW LEVEL SECURITY; 35ALTER TABLE rls_test_restrictive ENABLE ROW LEVEL SECURITY; 36ALTER TABLE rls_test_both ENABLE ROW LEVEL SECURITY; 37CREATE ROLE regress_r1; 38CREATE ROLE regress_s1; 39GRANT SELECT,INSERT ON rls_test_permissive TO regress_r1; 40GRANT SELECT,INSERT ON rls_test_restrictive TO regress_r1; 41GRANT SELECT,INSERT ON rls_test_both TO regress_r1; 42GRANT SELECT,INSERT ON rls_test_permissive TO regress_s1; 43GRANT SELECT,INSERT ON rls_test_restrictive TO regress_s1; 44GRANT SELECT,INSERT ON rls_test_both TO regress_s1; 45SET ROLE regress_r1; 46-- With only the hook's policies, permissive 47-- hook's policy is current_user = username 48EXPLAIN (costs off) SELECT * FROM rls_test_permissive; 49 QUERY PLAN 50----------------------------------------- 51 Seq Scan on rls_test_permissive 52 Filter: ("current_user"() = username) 53(2 rows) 54 55SELECT * FROM rls_test_permissive; 56 username | supervisor | data 57------------+------------+------ 58 regress_r1 | regress_s1 | 4 59(1 row) 60 61-- success 62INSERT INTO rls_test_permissive VALUES ('regress_r1','regress_s1',10); 63-- failure 64INSERT INTO rls_test_permissive VALUES ('regress_r4','regress_s4',10); 65ERROR: new row violates row-level security policy for table "rls_test_permissive" 66SET ROLE regress_s1; 67-- With only the hook's policies, restrictive 68-- hook's policy is current_user = supervisor 69EXPLAIN (costs off) SELECT * FROM rls_test_restrictive; 70 QUERY PLAN 71------------------------------------------- 72 Seq Scan on rls_test_restrictive 73 Filter: ("current_user"() = supervisor) 74(2 rows) 75 76SELECT * FROM rls_test_restrictive; 77 username | supervisor | data 78------------+------------+------ 79 regress_r1 | regress_s1 | 1 80(1 row) 81 82-- success 83INSERT INTO rls_test_restrictive VALUES ('regress_r1','regress_s1',10); 84-- failure 85INSERT INTO rls_test_restrictive VALUES ('regress_r4','regress_s4',10); 86ERROR: new row violates row-level security policy "extension policy" for table "rls_test_restrictive" 87SET ROLE regress_s1; 88-- With only the hook's policies, both 89-- permissive hook's policy is current_user = username 90-- restrictive hook's policy is current_user = superuser 91-- combined with AND, results in nothing being allowed 92EXPLAIN (costs off) SELECT * FROM rls_test_both; 93 QUERY PLAN 94------------------------------------------------------------------------------- 95 Seq Scan on rls_test_both 96 Filter: ((supervisor = "current_user"()) AND (username = "current_user"())) 97(2 rows) 98 99SELECT * FROM rls_test_both; 100 username | supervisor | data 101----------+------------+------ 102(0 rows) 103 104-- failure 105INSERT INTO rls_test_both VALUES ('regress_r1','regress_s1',10); 106ERROR: new row violates row-level security policy for table "rls_test_both" 107-- failure 108INSERT INTO rls_test_both VALUES ('regress_r4','regress_s1',10); 109ERROR: new row violates row-level security policy for table "rls_test_both" 110-- failure 111INSERT INTO rls_test_both VALUES ('regress_r4','regress_s4',10); 112ERROR: new row violates row-level security policy for table "rls_test_both" 113RESET ROLE; 114-- Create "internal" policies, to check that the policies from 115-- the hooks are combined correctly. 116CREATE POLICY p1 ON rls_test_permissive USING (data % 2 = 0); 117-- Remove the original allow-all policy 118DROP POLICY p1 ON rls_test_restrictive; 119CREATE POLICY p1 ON rls_test_restrictive USING (data % 2 = 0); 120CREATE POLICY p1 ON rls_test_both USING (data % 2 = 0); 121SET ROLE regress_r1; 122-- With both internal and hook policies, permissive 123EXPLAIN (costs off) SELECT * FROM rls_test_permissive; 124 QUERY PLAN 125--------------------------------------------------------------- 126 Seq Scan on rls_test_permissive 127 Filter: (((data % 2) = 0) OR ("current_user"() = username)) 128(2 rows) 129 130SELECT * FROM rls_test_permissive; 131 username | supervisor | data 132------------+------------+------ 133 regress_r1 | regress_s1 | 4 134 regress_r3 | regress_s3 | 6 135 regress_r1 | regress_s1 | 10 136(3 rows) 137 138-- success 139INSERT INTO rls_test_permissive VALUES ('regress_r1','regress_s1',7); 140-- success 141INSERT INTO rls_test_permissive VALUES ('regress_r3','regress_s3',10); 142-- failure 143INSERT INTO rls_test_permissive VALUES ('regress_r4','regress_s4',7); 144ERROR: new row violates row-level security policy for table "rls_test_permissive" 145SET ROLE regress_s1; 146-- With both internal and hook policies, restrictive 147EXPLAIN (costs off) SELECT * FROM rls_test_restrictive; 148 QUERY PLAN 149------------------------------------------------------------------ 150 Seq Scan on rls_test_restrictive 151 Filter: (("current_user"() = supervisor) AND ((data % 2) = 0)) 152(2 rows) 153 154SELECT * FROM rls_test_restrictive; 155 username | supervisor | data 156------------+------------+------ 157 regress_r1 | regress_s1 | 10 158(1 row) 159 160-- success 161INSERT INTO rls_test_restrictive VALUES ('regress_r1','regress_s1',8); 162-- failure 163INSERT INTO rls_test_restrictive VALUES ('regress_r3','regress_s3',10); 164ERROR: new row violates row-level security policy "extension policy" for table "rls_test_restrictive" 165-- failure 166INSERT INTO rls_test_restrictive VALUES ('regress_r1','regress_s1',7); 167ERROR: new row violates row-level security policy for table "rls_test_restrictive" 168-- failure 169INSERT INTO rls_test_restrictive VALUES ('regress_r4','regress_s4',7); 170ERROR: new row violates row-level security policy for table "rls_test_restrictive" 171-- With both internal and hook policies, both permissive 172-- and restrictive hook policies 173EXPLAIN (costs off) SELECT * FROM rls_test_both; 174 QUERY PLAN 175----------------------------------------------------------------------------------------------------- 176 Seq Scan on rls_test_both 177 Filter: (("current_user"() = supervisor) AND (((data % 2) = 0) OR ("current_user"() = username))) 178(2 rows) 179 180SELECT * FROM rls_test_both; 181 username | supervisor | data 182----------+------------+------ 183(0 rows) 184 185-- success 186INSERT INTO rls_test_both VALUES ('regress_r1','regress_s1',8); 187-- failure 188INSERT INTO rls_test_both VALUES ('regress_r3','regress_s3',10); 189ERROR: new row violates row-level security policy "extension policy" for table "rls_test_both" 190-- failure 191INSERT INTO rls_test_both VALUES ('regress_r1','regress_s1',7); 192ERROR: new row violates row-level security policy for table "rls_test_both" 193-- failure 194INSERT INTO rls_test_both VALUES ('regress_r4','regress_s4',7); 195ERROR: new row violates row-level security policy for table "rls_test_both" 196RESET ROLE; 197DROP TABLE rls_test_restrictive; 198DROP TABLE rls_test_permissive; 199DROP TABLE rls_test_both; 200DROP ROLE regress_r1; 201DROP ROLE regress_s1; 202