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