2 -- Test of Row-level security feature
4 -- Clean up in case a prior regression run failed
5 -- Suppress NOTICE messages when users/groups don't exist
6 SET client_min_messages TO 'warning';
7 DROP USER IF EXISTS rls_regress_user0;
8 DROP USER IF EXISTS rls_regress_user1;
9 DROP USER IF EXISTS rls_regress_user2;
10 DROP USER IF EXISTS rls_regress_exempt_user;
11 DROP ROLE IF EXISTS rls_regress_group1;
12 DROP ROLE IF EXISTS rls_regress_group2;
13 DROP SCHEMA IF EXISTS rls_regress_schema CASCADE;
14 RESET client_min_messages;
16 CREATE USER rls_regress_user0;
17 CREATE USER rls_regress_user1;
18 CREATE USER rls_regress_user2;
19 CREATE USER rls_regress_exempt_user BYPASSRLS;
20 CREATE ROLE rls_regress_group1 NOLOGIN;
21 CREATE ROLE rls_regress_group2 NOLOGIN;
22 GRANT rls_regress_group1 TO rls_regress_user1;
23 GRANT rls_regress_group2 TO rls_regress_user2;
24 CREATE SCHEMA rls_regress_schema;
25 GRANT ALL ON SCHEMA rls_regress_schema to public;
26 SET search_path = rls_regress_schema;
27 -- setup of malicious function
28 CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
29 COST 0.0000001 LANGUAGE plpgsql
30 AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
31 GRANT EXECUTE ON FUNCTION f_leak(text) TO public;
32 -- BASIC Row-Level Security Scenario
33 SET SESSION AUTHORIZATION rls_regress_user0;
34 CREATE TABLE uaccount (
35 pguser name primary key,
38 GRANT SELECT ON uaccount TO public;
39 INSERT INTO uaccount VALUES
40 ('rls_regress_user0', 99),
41 ('rls_regress_user1', 1),
42 ('rls_regress_user2', 2),
43 ('rls_regress_user3', 3);
44 CREATE TABLE category (
48 GRANT ALL ON category TO public;
49 INSERT INTO category VALUES
51 (22, 'science fiction'),
54 CREATE TABLE document (
56 cid int references category(cid),
61 GRANT ALL ON document TO public;
62 INSERT INTO document VALUES
63 ( 1, 11, 1, 'rls_regress_user1', 'my first novel'),
64 ( 2, 11, 2, 'rls_regress_user1', 'my second novel'),
65 ( 3, 22, 2, 'rls_regress_user1', 'my science fiction'),
66 ( 4, 44, 1, 'rls_regress_user1', 'my first manga'),
67 ( 5, 44, 2, 'rls_regress_user1', 'my second manga'),
68 ( 6, 22, 1, 'rls_regress_user2', 'great science fiction'),
69 ( 7, 33, 2, 'rls_regress_user2', 'great technology book'),
70 ( 8, 44, 1, 'rls_regress_user2', 'great manga');
71 ALTER TABLE document ENABLE ROW LEVEL SECURITY;
72 -- user's security level must be higher than or equal to document's
73 CREATE POLICY p1 ON document
74 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
75 -- viewpoint from rls_regress_user1
76 SET SESSION AUTHORIZATION rls_regress_user1;
77 SET row_security TO ON;
78 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
79 NOTICE: f_leak => my first novel
80 NOTICE: f_leak => my first manga
81 NOTICE: f_leak => great science fiction
82 NOTICE: f_leak => great manga
83 did | cid | dlevel | dauthor | dtitle
84 -----+-----+--------+-------------------+-----------------------
85 1 | 11 | 1 | rls_regress_user1 | my first novel
86 4 | 44 | 1 | rls_regress_user1 | my first manga
87 6 | 22 | 1 | rls_regress_user2 | great science fiction
88 8 | 44 | 1 | rls_regress_user2 | great manga
91 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
92 NOTICE: f_leak => my first novel
93 NOTICE: f_leak => my first manga
94 NOTICE: f_leak => great science fiction
95 NOTICE: f_leak => great manga
96 cid | did | dlevel | dauthor | dtitle | cname
97 -----+-----+--------+-------------------+-----------------------+-----------------
98 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
99 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
100 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
101 44 | 8 | 1 | rls_regress_user2 | great manga | manga
104 SELECT * FROM document TABLESAMPLE BERNOULLI (50) REPEATABLE(1) WHERE f_leak(dtitle) ORDER BY did;
105 NOTICE: f_leak => my first novel
106 NOTICE: f_leak => my first manga
107 NOTICE: f_leak => great science fiction
108 did | cid | dlevel | dauthor | dtitle
109 -----+-----+--------+-------------------+-----------------------
110 1 | 11 | 1 | rls_regress_user1 | my first novel
111 4 | 44 | 1 | rls_regress_user1 | my first manga
112 6 | 22 | 1 | rls_regress_user2 | great science fiction
115 -- viewpoint from rls_regress_user2
116 SET SESSION AUTHORIZATION rls_regress_user2;
117 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
118 NOTICE: f_leak => my first novel
119 NOTICE: f_leak => my second novel
120 NOTICE: f_leak => my science fiction
121 NOTICE: f_leak => my first manga
122 NOTICE: f_leak => my second manga
123 NOTICE: f_leak => great science fiction
124 NOTICE: f_leak => great technology book
125 NOTICE: f_leak => great manga
126 did | cid | dlevel | dauthor | dtitle
127 -----+-----+--------+-------------------+-----------------------
128 1 | 11 | 1 | rls_regress_user1 | my first novel
129 2 | 11 | 2 | rls_regress_user1 | my second novel
130 3 | 22 | 2 | rls_regress_user1 | my science fiction
131 4 | 44 | 1 | rls_regress_user1 | my first manga
132 5 | 44 | 2 | rls_regress_user1 | my second manga
133 6 | 22 | 1 | rls_regress_user2 | great science fiction
134 7 | 33 | 2 | rls_regress_user2 | great technology book
135 8 | 44 | 1 | rls_regress_user2 | great manga
138 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
139 NOTICE: f_leak => my first novel
140 NOTICE: f_leak => my second novel
141 NOTICE: f_leak => my science fiction
142 NOTICE: f_leak => my first manga
143 NOTICE: f_leak => my second manga
144 NOTICE: f_leak => great science fiction
145 NOTICE: f_leak => great technology book
146 NOTICE: f_leak => great manga
147 cid | did | dlevel | dauthor | dtitle | cname
148 -----+-----+--------+-------------------+-----------------------+-----------------
149 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
150 11 | 2 | 2 | rls_regress_user1 | my second novel | novel
151 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
152 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
153 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
154 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
155 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
156 44 | 8 | 1 | rls_regress_user2 | great manga | manga
159 SELECT * FROM document TABLESAMPLE BERNOULLI (50) REPEATABLE(1) WHERE f_leak(dtitle) ORDER BY did;
160 NOTICE: f_leak => my first novel
161 NOTICE: f_leak => my second novel
162 NOTICE: f_leak => my first manga
163 NOTICE: f_leak => great science fiction
164 NOTICE: f_leak => great technology book
165 did | cid | dlevel | dauthor | dtitle
166 -----+-----+--------+-------------------+-----------------------
167 1 | 11 | 1 | rls_regress_user1 | my first novel
168 2 | 11 | 2 | rls_regress_user1 | my second novel
169 4 | 44 | 1 | rls_regress_user1 | my first manga
170 6 | 22 | 1 | rls_regress_user2 | great science fiction
171 7 | 33 | 2 | rls_regress_user2 | great technology book
174 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
176 ----------------------------------------------------------
177 Subquery Scan on document
178 Filter: f_leak(document.dtitle)
179 -> Seq Scan on document document_1
180 Filter: (dlevel <= $0)
181 InitPlan 1 (returns $0)
182 -> Index Scan using uaccount_pkey on uaccount
183 Index Cond: (pguser = "current_user"())
186 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
188 ----------------------------------------------------------------------
190 Hash Cond: (category.cid = document.cid)
191 -> Seq Scan on category
193 -> Subquery Scan on document
194 Filter: f_leak(document.dtitle)
195 -> Seq Scan on document document_1
196 Filter: (dlevel <= $0)
197 InitPlan 1 (returns $0)
198 -> Index Scan using uaccount_pkey on uaccount
199 Index Cond: (pguser = "current_user"())
202 -- only owner can change policies
203 ALTER POLICY p1 ON document USING (true); --fail
204 ERROR: must be owner of relation document
205 DROP POLICY p1 ON document; --fail
206 ERROR: must be owner of relation document
207 SET SESSION AUTHORIZATION rls_regress_user0;
208 ALTER POLICY p1 ON document USING (dauthor = current_user);
209 -- viewpoint from rls_regress_user1 again
210 SET SESSION AUTHORIZATION rls_regress_user1;
211 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
212 NOTICE: f_leak => my first novel
213 NOTICE: f_leak => my second novel
214 NOTICE: f_leak => my science fiction
215 NOTICE: f_leak => my first manga
216 NOTICE: f_leak => my second manga
217 did | cid | dlevel | dauthor | dtitle
218 -----+-----+--------+-------------------+--------------------
219 1 | 11 | 1 | rls_regress_user1 | my first novel
220 2 | 11 | 2 | rls_regress_user1 | my second novel
221 3 | 22 | 2 | rls_regress_user1 | my science fiction
222 4 | 44 | 1 | rls_regress_user1 | my first manga
223 5 | 44 | 2 | rls_regress_user1 | my second manga
226 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
227 NOTICE: f_leak => my first novel
228 NOTICE: f_leak => my second novel
229 NOTICE: f_leak => my science fiction
230 NOTICE: f_leak => my first manga
231 NOTICE: f_leak => my second manga
232 cid | did | dlevel | dauthor | dtitle | cname
233 -----+-----+--------+-------------------+--------------------+-----------------
234 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
235 11 | 2 | 2 | rls_regress_user1 | my second novel | novel
236 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
237 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
238 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
241 -- viewpoint from rls_regres_user2 again
242 SET SESSION AUTHORIZATION rls_regress_user2;
243 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
244 NOTICE: f_leak => great science fiction
245 NOTICE: f_leak => great technology book
246 NOTICE: f_leak => great manga
247 did | cid | dlevel | dauthor | dtitle
248 -----+-----+--------+-------------------+-----------------------
249 6 | 22 | 1 | rls_regress_user2 | great science fiction
250 7 | 33 | 2 | rls_regress_user2 | great technology book
251 8 | 44 | 1 | rls_regress_user2 | great manga
254 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
255 NOTICE: f_leak => great science fiction
256 NOTICE: f_leak => great technology book
257 NOTICE: f_leak => great manga
258 cid | did | dlevel | dauthor | dtitle | cname
259 -----+-----+--------+-------------------+-----------------------+-----------------
260 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
261 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
262 44 | 8 | 1 | rls_regress_user2 | great manga | manga
265 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
267 ----------------------------------------------
268 Subquery Scan on document
269 Filter: f_leak(document.dtitle)
270 -> Seq Scan on document document_1
271 Filter: (dauthor = "current_user"())
274 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
276 ----------------------------------------------------
278 -> Subquery Scan on document
279 Filter: f_leak(document.dtitle)
280 -> Seq Scan on document document_1
281 Filter: (dauthor = "current_user"())
282 -> Index Scan using category_pkey on category
283 Index Cond: (cid = document.cid)
286 -- interaction of FK/PK constraints
287 SET SESSION AUTHORIZATION rls_regress_user0;
288 CREATE POLICY p2 ON category
289 USING (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33)
290 WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44)
292 ALTER TABLE category ENABLE ROW LEVEL SECURITY;
293 -- cannot delete PK referenced by invisible FK
294 SET SESSION AUTHORIZATION rls_regress_user1;
295 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
296 did | cid | dlevel | dauthor | dtitle | cid | cname
297 -----+-----+--------+-------------------+--------------------+-----+------------
298 2 | 11 | 2 | rls_regress_user1 | my second novel | 11 | novel
299 1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel
300 | | | | | 33 | technology
301 5 | 44 | 2 | rls_regress_user1 | my second manga | |
302 4 | 44 | 1 | rls_regress_user1 | my first manga | |
303 3 | 22 | 2 | rls_regress_user1 | my science fiction | |
306 DELETE FROM category WHERE cid = 33; -- fails with FK violation
307 ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"
308 DETAIL: Key (cid)=(33) is still referenced from table "document".
309 -- can insert FK referencing invisible PK
310 SET SESSION AUTHORIZATION rls_regress_user2;
311 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
312 did | cid | dlevel | dauthor | dtitle | cid | cname
313 -----+-----+--------+-------------------+-----------------------+-----+-----------------
314 6 | 22 | 1 | rls_regress_user2 | great science fiction | 22 | science fiction
315 8 | 44 | 1 | rls_regress_user2 | great manga | 44 | manga
316 7 | 33 | 2 | rls_regress_user2 | great technology book | |
319 INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');
320 -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
321 SET SESSION AUTHORIZATION rls_regress_user1;
322 INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user1', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
323 ERROR: duplicate key value violates unique constraint "document_pkey"
324 SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
325 did | cid | dlevel | dauthor | dtitle
326 -----+-----+--------+---------+--------
329 -- RLS policies are checked before constraints
330 INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user2', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation
331 ERROR: new row violates row level security policy for "document"
332 UPDATE document SET did = 8, dauthor = 'rls_regress_user2' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation
333 ERROR: new row violates row level security policy for "document"
334 -- database superuser does bypass RLS policy when enabled
335 RESET SESSION AUTHORIZATION;
336 SET row_security TO ON;
337 SELECT * FROM document;
338 did | cid | dlevel | dauthor | dtitle
339 -----+-----+--------+-------------------+-----------------------
340 1 | 11 | 1 | rls_regress_user1 | my first novel
341 2 | 11 | 2 | rls_regress_user1 | my second novel
342 3 | 22 | 2 | rls_regress_user1 | my science fiction
343 4 | 44 | 1 | rls_regress_user1 | my first manga
344 5 | 44 | 2 | rls_regress_user1 | my second manga
345 6 | 22 | 1 | rls_regress_user2 | great science fiction
346 7 | 33 | 2 | rls_regress_user2 | great technology book
347 8 | 44 | 1 | rls_regress_user2 | great manga
348 10 | 33 | 1 | rls_regress_user2 | hoge
351 SELECT * FROM category;
353 -----+-----------------
360 -- database superuser does not bypass RLS policy when FORCE enabled.
361 RESET SESSION AUTHORIZATION;
362 SET row_security TO FORCE;
363 SELECT * FROM document;
364 did | cid | dlevel | dauthor | dtitle
365 -----+-----+--------+---------+--------
368 SELECT * FROM category;
373 -- database superuser does bypass RLS policy when disabled
374 RESET SESSION AUTHORIZATION;
375 SET row_security TO OFF;
376 SELECT * FROM document;
377 did | cid | dlevel | dauthor | dtitle
378 -----+-----+--------+-------------------+-----------------------
379 1 | 11 | 1 | rls_regress_user1 | my first novel
380 2 | 11 | 2 | rls_regress_user1 | my second novel
381 3 | 22 | 2 | rls_regress_user1 | my science fiction
382 4 | 44 | 1 | rls_regress_user1 | my first manga
383 5 | 44 | 2 | rls_regress_user1 | my second manga
384 6 | 22 | 1 | rls_regress_user2 | great science fiction
385 7 | 33 | 2 | rls_regress_user2 | great technology book
386 8 | 44 | 1 | rls_regress_user2 | great manga
387 10 | 33 | 1 | rls_regress_user2 | hoge
390 SELECT * FROM category;
392 -----+-----------------
399 -- database non-superuser with bypass privilege can bypass RLS policy when disabled
400 SET SESSION AUTHORIZATION rls_regress_exempt_user;
401 SET row_security TO OFF;
402 SELECT * FROM document;
403 did | cid | dlevel | dauthor | dtitle
404 -----+-----+--------+-------------------+-----------------------
405 1 | 11 | 1 | rls_regress_user1 | my first novel
406 2 | 11 | 2 | rls_regress_user1 | my second novel
407 3 | 22 | 2 | rls_regress_user1 | my science fiction
408 4 | 44 | 1 | rls_regress_user1 | my first manga
409 5 | 44 | 2 | rls_regress_user1 | my second manga
410 6 | 22 | 1 | rls_regress_user2 | great science fiction
411 7 | 33 | 2 | rls_regress_user2 | great technology book
412 8 | 44 | 1 | rls_regress_user2 | great manga
413 10 | 33 | 1 | rls_regress_user2 | hoge
416 SELECT * FROM category;
418 -----+-----------------
425 -- RLS policy applies to table owner when FORCE enabled.
426 SET SESSION AUTHORIZATION rls_regress_user0;
427 SET row_security TO FORCE;
428 SELECT * FROM document;
429 did | cid | dlevel | dauthor | dtitle
430 -----+-----+--------+---------+--------
433 SELECT * FROM category;
438 -- RLS policy does not apply to table owner when RLS enabled.
439 SET SESSION AUTHORIZATION rls_regress_user0;
440 SET row_security TO ON;
441 SELECT * FROM document;
442 did | cid | dlevel | dauthor | dtitle
443 -----+-----+--------+-------------------+-----------------------
444 1 | 11 | 1 | rls_regress_user1 | my first novel
445 2 | 11 | 2 | rls_regress_user1 | my second novel
446 3 | 22 | 2 | rls_regress_user1 | my science fiction
447 4 | 44 | 1 | rls_regress_user1 | my first manga
448 5 | 44 | 2 | rls_regress_user1 | my second manga
449 6 | 22 | 1 | rls_regress_user2 | great science fiction
450 7 | 33 | 2 | rls_regress_user2 | great technology book
451 8 | 44 | 1 | rls_regress_user2 | great manga
452 10 | 33 | 1 | rls_regress_user2 | hoge
455 SELECT * FROM category;
457 -----+-----------------
464 -- RLS policy does not apply to table owner when RLS disabled.
465 SET SESSION AUTHORIZATION rls_regress_user0;
466 SET row_security TO OFF;
467 SELECT * FROM document;
468 did | cid | dlevel | dauthor | dtitle
469 -----+-----+--------+-------------------+-----------------------
470 1 | 11 | 1 | rls_regress_user1 | my first novel
471 2 | 11 | 2 | rls_regress_user1 | my second novel
472 3 | 22 | 2 | rls_regress_user1 | my science fiction
473 4 | 44 | 1 | rls_regress_user1 | my first manga
474 5 | 44 | 2 | rls_regress_user1 | my second manga
475 6 | 22 | 1 | rls_regress_user2 | great science fiction
476 7 | 33 | 2 | rls_regress_user2 | great technology book
477 8 | 44 | 1 | rls_regress_user2 | great manga
478 10 | 33 | 1 | rls_regress_user2 | hoge
481 SELECT * FROM category;
483 -----+-----------------
491 -- Table inheritance and RLS policy
493 SET SESSION AUTHORIZATION rls_regress_user0;
494 SET row_security TO ON;
495 CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS;
496 ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor
497 GRANT ALL ON t1 TO public;
498 COPY t1 FROM stdin WITH (oids);
499 CREATE TABLE t2 (c float) INHERITS (t1);
500 GRANT ALL ON t2 TO public;
501 COPY t2 FROM stdin WITH (oids);
502 CREATE TABLE t3 (c text, b text, a int) WITH OIDS;
503 ALTER TABLE t3 INHERIT t1;
504 GRANT ALL ON t3 TO public;
505 COPY t3(a,b,c) FROM stdin WITH (oids);
506 CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number
507 CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number
508 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
509 ALTER TABLE t2 ENABLE ROW LEVEL SECURITY;
510 SET SESSION AUTHORIZATION rls_regress_user1;
521 EXPLAIN (COSTS OFF) SELECT * FROM t1;
523 -------------------------------
526 Filter: ((a % 2) = 0)
528 Filter: ((a % 2) = 0)
530 Filter: ((a % 2) = 0)
533 SELECT * FROM t1 WHERE f_leak(b);
534 NOTICE: f_leak => bbb
535 NOTICE: f_leak => ddd
536 NOTICE: f_leak => bcd
537 NOTICE: f_leak => def
538 NOTICE: f_leak => yyy
548 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
550 -------------------------------------
554 -> Seq Scan on t1 t1_1
555 Filter: ((a % 2) = 0)
557 Filter: ((a % 2) = 0)
559 Filter: ((a % 2) = 0)
562 -- reference to system column
563 SELECT oid, * FROM t1;
573 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
575 -------------------------------
578 Filter: ((a % 2) = 0)
580 Filter: ((a % 2) = 0)
582 Filter: ((a % 2) = 0)
585 -- reference to whole-row reference
586 SELECT *, t1 FROM t1;
596 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
598 -------------------------------
601 Filter: ((a % 2) = 0)
603 Filter: ((a % 2) = 0)
605 Filter: ((a % 2) = 0)
608 -- for share/update lock
609 SELECT * FROM t1 FOR SHARE;
619 EXPLAIN (COSTS OFF) SELECT * FROM t1 FOR SHARE;
621 -------------------------------------------------------
623 -> Subquery Scan on t1
627 -> Seq Scan on t1 t1_1
628 Filter: ((a % 2) = 0)
630 Filter: ((a % 2) = 0)
632 Filter: ((a % 2) = 0)
635 SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
636 NOTICE: f_leak => bbb
637 NOTICE: f_leak => ddd
638 NOTICE: f_leak => bcd
639 NOTICE: f_leak => def
640 NOTICE: f_leak => yyy
650 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
652 -------------------------------------------------------
654 -> Subquery Scan on t1
659 -> Seq Scan on t1 t1_1
660 Filter: ((a % 2) = 0)
662 Filter: ((a % 2) = 0)
664 Filter: ((a % 2) = 0)
667 -- superuser is allowed to bypass RLS checks
668 RESET SESSION AUTHORIZATION;
669 SET row_security TO OFF;
670 SELECT * FROM t1 WHERE f_leak(b);
671 NOTICE: f_leak => aaa
672 NOTICE: f_leak => bbb
673 NOTICE: f_leak => ccc
674 NOTICE: f_leak => ddd
675 NOTICE: f_leak => abc
676 NOTICE: f_leak => bcd
677 NOTICE: f_leak => cde
678 NOTICE: f_leak => def
679 NOTICE: f_leak => xxx
680 NOTICE: f_leak => yyy
681 NOTICE: f_leak => zzz
697 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
699 ---------------------------
709 -- non-superuser with bypass privilege can bypass RLS policy when disabled
710 SET SESSION AUTHORIZATION rls_regress_exempt_user;
711 SET row_security TO OFF;
712 SELECT * FROM t1 WHERE f_leak(b);
713 NOTICE: f_leak => aaa
714 NOTICE: f_leak => bbb
715 NOTICE: f_leak => ccc
716 NOTICE: f_leak => ddd
717 NOTICE: f_leak => abc
718 NOTICE: f_leak => bcd
719 NOTICE: f_leak => cde
720 NOTICE: f_leak => def
721 NOTICE: f_leak => xxx
722 NOTICE: f_leak => yyy
723 NOTICE: f_leak => zzz
739 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
741 ---------------------------
751 ----- Dependencies -----
752 SET SESSION AUTHORIZATION rls_regress_user0;
753 SET row_security TO ON;
754 CREATE TABLE dependee (x integer, y integer);
755 CREATE TABLE dependent (x integer, y integer);
756 CREATE POLICY d1 ON dependent FOR ALL
758 USING (x = (SELECT d.x FROM dependee d WHERE d.y = y));
759 DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row security qual?
760 ERROR: cannot drop table dependee because other objects depend on it
761 DETAIL: policy d1 on table dependent depends on table dependee
762 HINT: Use DROP ... CASCADE to drop the dependent objects too.
763 DROP TABLE dependee CASCADE;
764 NOTICE: drop cascades to policy d1 on table dependent
765 EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified
767 -----------------------
768 Seq Scan on dependent
775 SET SESSION AUTHORIZATION rls_regress_user0;
776 CREATE TABLE rec1 (x integer, y integer);
777 CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y));
778 ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;
779 SET SESSION AUTHORIZATION rls_regress_user1;
780 SELECT * FROM rec1; -- fail, direct recursion
781 ERROR: infinite recursion detected in policy for relation "rec1"
785 SET SESSION AUTHORIZATION rls_regress_user0;
786 CREATE TABLE rec2 (a integer, b integer);
787 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y));
788 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b));
789 ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;
790 SET SESSION AUTHORIZATION rls_regress_user1;
791 SELECT * FROM rec1; -- fail, mutual recursion
792 ERROR: infinite recursion detected in policy for relation "rec1"
794 -- Mutual recursion via views
796 SET SESSION AUTHORIZATION rls_regress_user1;
797 CREATE VIEW rec1v AS SELECT * FROM rec1;
798 CREATE VIEW rec2v AS SELECT * FROM rec2;
799 SET SESSION AUTHORIZATION rls_regress_user0;
800 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
801 ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
802 SET SESSION AUTHORIZATION rls_regress_user1;
803 SELECT * FROM rec1; -- fail, mutual recursion via views
804 ERROR: infinite recursion detected in policy for relation "rec1"
806 -- Mutual recursion via .s.b views
808 SET SESSION AUTHORIZATION rls_regress_user1;
809 -- Suppress NOTICE messages when doing a cascaded drop.
810 SET client_min_messages TO 'warning';
811 DROP VIEW rec1v, rec2v CASCADE;
812 RESET client_min_messages;
813 CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
814 CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
815 SET SESSION AUTHORIZATION rls_regress_user0;
816 CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
817 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
818 SET SESSION AUTHORIZATION rls_regress_user1;
819 SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
820 ERROR: infinite recursion detected in policy for relation "rec1"
822 -- recursive RLS and VIEWs in policy
824 SET SESSION AUTHORIZATION rls_regress_user0;
825 CREATE TABLE s1 (a int, b text);
826 INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
827 CREATE TABLE s2 (x int, y text);
828 INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
829 GRANT SELECT ON s1, s2 TO rls_regress_user1;
830 CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
831 CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
832 CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1));
833 ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;
834 ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;
835 SET SESSION AUTHORIZATION rls_regress_user1;
836 CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
837 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
838 ERROR: infinite recursion detected in policy for relation "s1"
839 INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion)
840 ERROR: infinite recursion detected in policy for relation "s1"
841 SET SESSION AUTHORIZATION rls_regress_user0;
842 DROP POLICY p3 on s1;
843 ALTER POLICY p2 ON s2 USING (x % 2 = 0);
844 SET SESSION AUTHORIZATION rls_regress_user1;
845 SELECT * FROM s1 WHERE f_leak(b); -- OK
846 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
847 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
849 ---+----------------------------------
850 2 | c81e728d9d4c2f636f067f89cc14862c
851 4 | a87ff679a2f3e71d9181a67b7542122c
854 EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
856 ----------------------------------------------------------
860 Hash Cond: (s1_1.a = s2.x)
861 -> Seq Scan on s1 s1_1
865 -> Subquery Scan on s2
866 Filter: (s2.y ~~ '%2f%'::text)
867 -> Seq Scan on s2 s2_1
868 Filter: ((x % 2) = 0)
871 SET SESSION AUTHORIZATION rls_regress_user0;
872 ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy
873 SET SESSION AUTHORIZATION rls_regress_user1;
874 SELECT * FROM s1 WHERE f_leak(b); -- OK
875 NOTICE: f_leak => 0267aaf632e87a63288a08331f22c7c3
876 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
878 ----+----------------------------------
879 -4 | 0267aaf632e87a63288a08331f22c7c3
880 6 | 1679091c5a880faf6fb5e6087eb1b2dc
883 EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
885 ----------------------------------------------------------
889 Hash Cond: (s1_1.a = s2.x)
890 -> Seq Scan on s1 s1_1
894 -> Subquery Scan on s2
895 Filter: (s2.y ~~ '%af%'::text)
896 -> Seq Scan on s2 s2_1
897 Filter: ((x % 2) = 0)
900 SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
902 ----+----+----------------------------------
903 -6 | -6 | 596a3d04481816330f07e4f97510c28f
904 -4 | -4 | 0267aaf632e87a63288a08331f22c7c3
905 2 | 2 | c81e728d9d4c2f636f067f89cc14862c
908 EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
910 --------------------------------------------------------------------
912 Filter: (s2.y ~~ '%28%'::text)
913 -> Seq Scan on s2 s2_1
914 Filter: ((x % 2) = 0)
917 -> Subquery Scan on s1
918 -> Nested Loop Semi Join
919 Join Filter: (s1_1.a = s2_2.x)
920 -> Seq Scan on s1 s1_1
922 -> Subquery Scan on s2_2
923 Filter: (s2_2.y ~~ '%af%'::text)
924 -> Seq Scan on s2 s2_3
925 Filter: ((x % 2) = 0)
928 SET SESSION AUTHORIZATION rls_regress_user0;
929 ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%'));
930 SET SESSION AUTHORIZATION rls_regress_user1;
931 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
932 ERROR: infinite recursion detected in policy for relation "s1"
933 -- prepared statement with rls_regress_user0 privilege
934 PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
943 EXPLAIN (COSTS OFF) EXECUTE p1(2);
945 ----------------------------------------------
948 Filter: ((a <= 2) AND ((a % 2) = 0))
950 Filter: ((a <= 2) AND ((a % 2) = 0))
952 Filter: ((a <= 2) AND ((a % 2) = 0))
955 -- superuser is allowed to bypass RLS checks
956 RESET SESSION AUTHORIZATION;
957 SET row_security TO OFF;
958 SELECT * FROM t1 WHERE f_leak(b);
959 NOTICE: f_leak => aaa
960 NOTICE: f_leak => bbb
961 NOTICE: f_leak => ccc
962 NOTICE: f_leak => ddd
963 NOTICE: f_leak => abc
964 NOTICE: f_leak => bcd
965 NOTICE: f_leak => cde
966 NOTICE: f_leak => def
967 NOTICE: f_leak => xxx
968 NOTICE: f_leak => yyy
969 NOTICE: f_leak => zzz
985 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
987 ---------------------------
997 -- plan cache should be invalidated
1009 EXPLAIN (COSTS OFF) EXECUTE p1(2);
1011 --------------------------
1021 PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1;
1030 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1032 -------------------------
1042 -- also, case when privilege switch from superuser
1043 SET SESSION AUTHORIZATION rls_regress_user1;
1044 SET row_security TO ON;
1053 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1055 ---------------------------------------------
1058 Filter: ((a = 2) AND ((a % 2) = 0))
1060 Filter: ((a = 2) AND ((a % 2) = 0))
1062 Filter: ((a = 2) AND ((a % 2) = 0))
1066 -- UPDATE / DELETE and Row-level security
1068 SET SESSION AUTHORIZATION rls_regress_user1;
1069 EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
1071 -------------------------------------------
1076 -> Subquery Scan on t1
1077 Filter: f_leak(t1.b)
1079 -> Seq Scan on t1 t1_4
1080 Filter: ((a % 2) = 0)
1081 -> Subquery Scan on t1_1
1082 Filter: f_leak(t1_1.b)
1085 Filter: ((a % 2) = 0)
1086 -> Subquery Scan on t1_2
1087 Filter: f_leak(t1_2.b)
1090 Filter: ((a % 2) = 0)
1093 UPDATE t1 SET b = b || b WHERE f_leak(b);
1094 NOTICE: f_leak => bbb
1095 NOTICE: f_leak => ddd
1096 NOTICE: f_leak => bcd
1097 NOTICE: f_leak => def
1098 NOTICE: f_leak => yyy
1099 EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1101 -------------------------------------------
1103 -> Subquery Scan on t1
1104 Filter: f_leak(t1.b)
1106 -> Seq Scan on t1 t1_2
1107 Filter: ((a % 2) = 0)
1110 UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1111 NOTICE: f_leak => bbbbbb
1112 NOTICE: f_leak => dddddd
1113 -- returning clause with system column
1114 UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
1115 NOTICE: f_leak => bbbbbb_updt
1116 NOTICE: f_leak => dddddd_updt
1118 -----+---+-------------+-----------------
1119 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
1120 104 | 4 | dddddd_updt | (4,dddddd_updt)
1123 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
1124 NOTICE: f_leak => bbbbbb_updt
1125 NOTICE: f_leak => dddddd_updt
1126 NOTICE: f_leak => bcdbcd
1127 NOTICE: f_leak => defdef
1128 NOTICE: f_leak => yyyyyy
1138 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
1139 NOTICE: f_leak => bbbbbb_updt
1140 NOTICE: f_leak => dddddd_updt
1141 NOTICE: f_leak => bcdbcd
1142 NOTICE: f_leak => defdef
1143 NOTICE: f_leak => yyyyyy
1145 -----+---+-------------+-----------------
1146 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
1147 104 | 4 | dddddd_updt | (4,dddddd_updt)
1148 202 | 2 | bcdbcd | (2,bcdbcd)
1149 204 | 4 | defdef | (4,defdef)
1150 302 | 2 | yyyyyy | (2,yyyyyy)
1153 -- updates with from clause
1154 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3
1155 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1157 ---------------------------------------------------------------
1160 -> Subquery Scan on t2
1161 Filter: f_leak(t2.b)
1163 -> Seq Scan on t2 t2_2
1164 Filter: ((a = 3) AND ((a % 2) = 1))
1166 Filter: (f_leak(b) AND (a = 2))
1169 UPDATE t2 SET b=t2.b FROM t3
1170 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1171 NOTICE: f_leak => cde
1172 NOTICE: f_leak => xxx
1173 NOTICE: f_leak => zzz
1174 NOTICE: f_leak => yyyyyy
1175 EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2
1176 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1178 ---------------------------------------------------------------
1184 -> Subquery Scan on t1
1185 Filter: f_leak(t1.b)
1187 -> Seq Scan on t1 t1_4
1188 Filter: ((a = 3) AND ((a % 2) = 0))
1189 -> Subquery Scan on t2
1190 Filter: f_leak(t2.b)
1191 -> Seq Scan on t2 t2_3
1192 Filter: ((a = 3) AND ((a % 2) = 1))
1194 -> Subquery Scan on t1_1
1195 Filter: f_leak(t1_1.b)
1197 -> Seq Scan on t2 t2_4
1198 Filter: ((a = 3) AND ((a % 2) = 0))
1199 -> Subquery Scan on t2_1
1200 Filter: f_leak(t2_1.b)
1201 -> Seq Scan on t2 t2_5
1202 Filter: ((a = 3) AND ((a % 2) = 1))
1204 -> Subquery Scan on t1_2
1205 Filter: f_leak(t1_2.b)
1208 Filter: ((a = 3) AND ((a % 2) = 0))
1209 -> Subquery Scan on t2_2
1210 Filter: f_leak(t2_2.b)
1211 -> Seq Scan on t2 t2_6
1212 Filter: ((a = 3) AND ((a % 2) = 1))
1215 UPDATE t1 SET b=t1.b FROM t2
1216 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1217 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1
1218 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1220 ---------------------------------------------------------------------
1223 -> Subquery Scan on t2
1224 Filter: f_leak(t2.b)
1226 -> Seq Scan on t2 t2_2
1227 Filter: ((a = 3) AND ((a % 2) = 1))
1228 -> Subquery Scan on t1
1229 Filter: f_leak(t1.b)
1232 -> Seq Scan on t1 t1_1
1233 Filter: ((a = 3) AND ((a % 2) = 0))
1234 -> Seq Scan on t2 t2_3
1235 Filter: ((a = 3) AND ((a % 2) = 0))
1237 Filter: ((a = 3) AND ((a % 2) = 0))
1240 UPDATE t2 SET b=t2.b FROM t1
1241 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1242 NOTICE: f_leak => cde
1243 -- updates with from clause self join
1244 EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1245 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1246 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1248 ---------------------------------------------------------------
1251 Join Filter: (t2_1.b = t2_2.b)
1252 -> Subquery Scan on t2_1
1253 Filter: f_leak(t2_1.b)
1255 -> Seq Scan on t2 t2_1_2
1256 Filter: ((a = 3) AND ((a % 2) = 1))
1257 -> Subquery Scan on t2_2
1258 Filter: f_leak(t2_2.b)
1259 -> Seq Scan on t2 t2_2_1
1260 Filter: ((a = 3) AND ((a % 2) = 1))
1263 UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1264 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1265 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1266 NOTICE: f_leak => cde
1267 NOTICE: f_leak => cde
1268 a | b | c | a | b | c | t2_1 | t2_2
1269 ---+-----+-----+---+-----+-----+-------------+-------------
1270 3 | cde | 3.3 | 3 | cde | 3.3 | (3,cde,3.3) | (3,cde,3.3)
1273 EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1274 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1275 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1277 ---------------------------------------------------------------
1283 Join Filter: (t1_1.b = t1_2.b)
1284 -> Subquery Scan on t1_1
1285 Filter: f_leak(t1_1.b)
1287 -> Seq Scan on t1 t1_1_4
1288 Filter: ((a = 4) AND ((a % 2) = 0))
1289 -> Subquery Scan on t1_2
1290 Filter: f_leak(t1_2.b)
1292 -> Seq Scan on t1 t1_2_3
1293 Filter: ((a = 4) AND ((a % 2) = 0))
1294 -> Seq Scan on t2 t1_2_4
1295 Filter: ((a = 4) AND ((a % 2) = 0))
1296 -> Seq Scan on t3 t1_2_5
1297 Filter: ((a = 4) AND ((a % 2) = 0))
1299 Join Filter: (t1_1_1.b = t1_2_1.b)
1300 -> Subquery Scan on t1_1_1
1301 Filter: f_leak(t1_1_1.b)
1303 -> Seq Scan on t2 t1_1_5
1304 Filter: ((a = 4) AND ((a % 2) = 0))
1305 -> Subquery Scan on t1_2_1
1306 Filter: f_leak(t1_2_1.b)
1308 -> Seq Scan on t1 t1_2_6
1309 Filter: ((a = 4) AND ((a % 2) = 0))
1310 -> Seq Scan on t2 t1_2_7
1311 Filter: ((a = 4) AND ((a % 2) = 0))
1312 -> Seq Scan on t3 t1_2_8
1313 Filter: ((a = 4) AND ((a % 2) = 0))
1315 Join Filter: (t1_1_2.b = t1_2_2.b)
1316 -> Subquery Scan on t1_1_2
1317 Filter: f_leak(t1_1_2.b)
1319 -> Seq Scan on t3 t1_1_6
1320 Filter: ((a = 4) AND ((a % 2) = 0))
1321 -> Subquery Scan on t1_2_2
1322 Filter: f_leak(t1_2_2.b)
1324 -> Seq Scan on t1 t1_2_9
1325 Filter: ((a = 4) AND ((a % 2) = 0))
1326 -> Seq Scan on t2 t1_2_10
1327 Filter: ((a = 4) AND ((a % 2) = 0))
1328 -> Seq Scan on t3 t1_2_11
1329 Filter: ((a = 4) AND ((a % 2) = 0))
1332 UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1333 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1334 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1335 NOTICE: f_leak => dddddd_updt
1336 NOTICE: f_leak => dddddd_updt
1337 NOTICE: f_leak => defdef
1338 NOTICE: f_leak => defdef
1339 NOTICE: f_leak => dddddd_updt
1340 NOTICE: f_leak => defdef
1341 a | b | a | b | t1_1 | t1_2
1342 ---+-------------+---+-------------+-----------------+-----------------
1343 4 | dddddd_updt | 4 | dddddd_updt | (4,dddddd_updt) | (4,dddddd_updt)
1344 4 | defdef | 4 | defdef | (4,defdef) | (4,defdef)
1347 RESET SESSION AUTHORIZATION;
1348 SET row_security TO OFF;
1349 SELECT * FROM t1 ORDER BY a,b;
1365 SET SESSION AUTHORIZATION rls_regress_user1;
1366 SET row_security TO ON;
1367 EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b);
1369 -------------------------------------------
1371 -> Subquery Scan on t1
1372 Filter: f_leak(t1.b)
1374 -> Seq Scan on t1 t1_2
1375 Filter: ((a % 2) = 0)
1378 EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
1380 -------------------------------------------
1385 -> Subquery Scan on t1
1386 Filter: f_leak(t1.b)
1388 -> Seq Scan on t1 t1_4
1389 Filter: ((a % 2) = 0)
1390 -> Subquery Scan on t1_1
1391 Filter: f_leak(t1_1.b)
1394 Filter: ((a % 2) = 0)
1395 -> Subquery Scan on t1_2
1396 Filter: f_leak(t1_2.b)
1399 Filter: ((a % 2) = 0)
1402 DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1;
1403 NOTICE: f_leak => bbbbbb_updt
1404 NOTICE: f_leak => dddddd_updt
1406 -----+---+-------------+-----------------
1407 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
1408 104 | 4 | dddddd_updt | (4,dddddd_updt)
1411 DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1;
1412 NOTICE: f_leak => bcdbcd
1413 NOTICE: f_leak => defdef
1414 NOTICE: f_leak => yyyyyy
1416 -----+---+--------+------------
1417 202 | 2 | bcdbcd | (2,bcdbcd)
1418 204 | 4 | defdef | (4,defdef)
1419 302 | 2 | yyyyyy | (2,yyyyyy)
1423 -- S.b. view on top of Row-level security
1425 SET SESSION AUTHORIZATION rls_regress_user0;
1426 CREATE TABLE b1 (a int, b text);
1427 INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
1428 CREATE POLICY p1 ON b1 USING (a % 2 = 0);
1429 ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
1430 GRANT ALL ON b1 TO rls_regress_user1;
1431 SET SESSION AUTHORIZATION rls_regress_user1;
1432 CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
1433 GRANT ALL ON bv1 TO rls_regress_user2;
1434 SET SESSION AUTHORIZATION rls_regress_user2;
1435 EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
1437 ---------------------------------------------
1438 Subquery Scan on bv1
1439 Filter: f_leak(bv1.b)
1441 Filter: ((a > 0) AND ((a % 2) = 0))
1444 SELECT * FROM bv1 WHERE f_leak(b);
1445 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
1446 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1447 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1448 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
1449 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
1451 ----+----------------------------------
1452 2 | c81e728d9d4c2f636f067f89cc14862c
1453 4 | a87ff679a2f3e71d9181a67b7542122c
1454 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1455 8 | c9f0f895fb98ab9159f51fd0297e236d
1456 10 | d3d9446802a44259755d38e6d163e820
1459 INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO
1460 ERROR: new row violates row level security policy for "b1"
1461 INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check
1462 ERROR: new row violates row level security policy for "b1"
1463 INSERT INTO bv1 VALUES (12, 'xxx'); -- ok
1464 EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1466 ---------------------------------------------------------------------------
1468 -> Subquery Scan on b1
1469 Filter: f_leak(b1.b)
1470 -> Subquery Scan on b1_2
1472 -> Seq Scan on b1 b1_3
1473 Filter: ((a > 0) AND (a = 4) AND ((a % 2) = 0))
1476 UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1477 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1478 EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1480 ---------------------------------------------------------------------------
1482 -> Subquery Scan on b1
1483 Filter: f_leak(b1.b)
1484 -> Subquery Scan on b1_2
1486 -> Seq Scan on b1 b1_3
1487 Filter: ((a > 0) AND (a = 6) AND ((a % 2) = 0))
1490 DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1491 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1492 SET SESSION AUTHORIZATION rls_regress_user0;
1495 -----+----------------------------------
1496 -10 | 1b0fd9efa5279c4203b7c70233f86dbf
1497 -9 | 252e691406782824eec43d7eadc3d256
1498 -8 | a8d2ec85eaf98407310b72eb73dda247
1499 -7 | 74687a12d3915d3c4d83f1af7b3683d5
1500 -6 | 596a3d04481816330f07e4f97510c28f
1501 -5 | 47c1b025fa18ea96c33fbb6718688c0f
1502 -4 | 0267aaf632e87a63288a08331f22c7c3
1503 -3 | b3149ecea4628efd23d2f86e5a723472
1504 -2 | 5d7b9adcbe1c629ec722529dd12e5129
1505 -1 | 6bb61e3b7bce0931da574d19d1d82c88
1506 0 | cfcd208495d565ef66e7dff9f98764da
1507 1 | c4ca4238a0b923820dcc509a6f75849b
1508 2 | c81e728d9d4c2f636f067f89cc14862c
1509 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
1510 5 | e4da3b7fbbce2345d7772b0674a318d5
1511 7 | 8f14e45fceea167a5a36dedd4bea2543
1512 8 | c9f0f895fb98ab9159f51fd0297e236d
1513 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
1514 10 | d3d9446802a44259755d38e6d163e820
1520 -- INSERT ... ON CONFLICT DO UPDATE and Row-level security
1522 SET SESSION AUTHORIZATION rls_regress_user0;
1523 DROP POLICY p1 ON document;
1524 CREATE POLICY p1 ON document FOR SELECT USING (true);
1525 CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
1526 CREATE POLICY p3 ON document FOR UPDATE
1527 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
1528 WITH CHECK (dauthor = current_user);
1529 SET SESSION AUTHORIZATION rls_regress_user1;
1531 SELECT * FROM document WHERE did = 2;
1532 did | cid | dlevel | dauthor | dtitle
1533 -----+-----+--------+-------------------+-----------------
1534 2 | 11 | 2 | rls_regress_user1 | my second novel
1537 -- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
1538 -- alternative UPDATE path happens to be taken):
1539 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
1540 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor;
1541 ERROR: new row violates row level security policy for "document"
1542 -- Violates USING qual for UPDATE policy p3.
1544 -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
1545 -- updated is not a "novel"/cid 11 (row is not leaked, even though we have
1546 -- SELECT privileges sufficient to see the row in this instance):
1547 INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction'); -- preparation for next statement
1548 INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path
1549 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
1550 ERROR: new row violates row level security policy (USING expression) for "document"
1551 -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
1553 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
1554 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
1555 did | cid | dlevel | dauthor | dtitle
1556 -----+-----+--------+-------------------+----------------
1557 2 | 11 | 2 | rls_regress_user1 | my first novel
1560 -- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated):
1561 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
1562 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
1563 did | cid | dlevel | dauthor | dtitle
1564 -----+-----+--------+-------------------+-----------------------
1565 78 | 11 | 1 | rls_regress_user1 | some technology novel
1568 -- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the
1569 -- case in respect of *existing* tuple):
1570 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
1571 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
1572 did | cid | dlevel | dauthor | dtitle
1573 -----+-----+--------+-------------------+-----------------------
1574 78 | 33 | 1 | rls_regress_user1 | some technology novel
1577 -- Same query a third time, but now fails due to existing tuple finally not
1579 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
1580 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
1581 ERROR: new row violates row level security policy (USING expression) for "document"
1582 -- Don't fail just because INSERT doesn't satisfy WITH CHECK option that
1583 -- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE
1584 -- path *isn't* taken, and so UPDATE-related policy does not apply:
1585 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
1586 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
1587 did | cid | dlevel | dauthor | dtitle
1588 -----+-----+--------+-------------------+----------------------------------
1589 79 | 33 | 1 | rls_regress_user1 | technology book, can only insert
1592 -- But this time, the same statement fails, because the UPDATE path is taken,
1593 -- and updating the row just inserted falls afoul of security barrier qual
1594 -- (enforced as WCO) -- what we might have updated target tuple to is
1595 -- irrelevant, in fact.
1596 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
1597 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
1598 ERROR: new row violates row level security policy (USING expression) for "document"
1599 -- Test default USING qual enforced as WCO
1600 SET SESSION AUTHORIZATION rls_regress_user0;
1601 DROP POLICY p1 ON document;
1602 DROP POLICY p2 ON document;
1603 DROP POLICY p3 ON document;
1604 CREATE POLICY p3_with_default ON document FOR UPDATE
1605 USING (cid = (SELECT cid from category WHERE cname = 'novel'));
1606 SET SESSION AUTHORIZATION rls_regress_user1;
1607 -- Just because WCO-style enforcement of USING quals occurs with
1608 -- existing/target tuple does not mean that the implementation can be allowed
1609 -- to fail to also enforce this qual against the final tuple appended to
1610 -- relation (since in the absence of an explicit WCO, this is also interpreted
1611 -- as an UPDATE/ALL WCO in general).
1613 -- UPDATE path is taken here (fails due to existing tuple). Note that this is
1614 -- not reported as a "USING expression", because it's an RLS UPDATE check that originated as
1615 -- a USING qual for the purposes of RLS in general, as opposed to an explicit
1616 -- USING qual that is ordinarily a security barrier. We leave it up to the
1617 -- UPDATE to make this fail:
1618 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
1619 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
1620 ERROR: new row violates row level security policy for "document"
1621 -- UPDATE path is taken here. Existing tuple passes, since it's cid
1622 -- corresponds to "novel", but default USING qual is enforced against
1623 -- post-UPDATE tuple too (as always when updating with a policy that lacks an
1624 -- explicit WCO), and so this fails:
1625 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'my first novel')
1626 ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *;
1627 ERROR: new row violates row level security policy for "document"
1628 SET SESSION AUTHORIZATION rls_regress_user0;
1629 DROP POLICY p3_with_default ON document;
1631 -- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE
1634 CREATE POLICY p3_with_all ON document FOR ALL
1635 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
1636 WITH CHECK (dauthor = current_user);
1637 SET SESSION AUTHORIZATION rls_regress_user1;
1638 -- Fails, since ALL WCO is enforced in insert path:
1639 INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
1640 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33;
1641 ERROR: new row violates row level security policy for "document"
1642 -- Fails, since ALL policy USING qual is enforced (existing, target tuple is in
1643 -- violation, since it has the "manga" cid):
1644 INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
1645 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
1646 ERROR: new row violates row level security policy (USING expression) for "document"
1647 -- Fails, since ALL WCO are enforced:
1648 INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
1649 ON CONFLICT (did) DO UPDATE SET dauthor = 'rls_regress_user2';
1650 ERROR: new row violates row level security policy for "document"
1654 SET SESSION AUTHORIZATION rls_regress_user0;
1655 CREATE TABLE z1 (a int, b text);
1656 GRANT SELECT ON z1 TO rls_regress_group1, rls_regress_group2,
1657 rls_regress_user1, rls_regress_user2;
1658 INSERT INTO z1 VALUES
1663 CREATE POLICY p1 ON z1 TO rls_regress_group1 USING (a % 2 = 0);
1664 CREATE POLICY p2 ON z1 TO rls_regress_group2 USING (a % 2 = 1);
1665 ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
1666 SET SESSION AUTHORIZATION rls_regress_user1;
1667 SELECT * FROM z1 WHERE f_leak(b);
1668 NOTICE: f_leak => bbb
1669 NOTICE: f_leak => ddd
1676 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1678 -------------------------------
1680 Filter: f_leak(z1.b)
1681 -> Seq Scan on z1 z1_1
1682 Filter: ((a % 2) = 0)
1685 SET ROLE rls_regress_group1;
1686 SELECT * FROM z1 WHERE f_leak(b);
1687 NOTICE: f_leak => bbb
1688 NOTICE: f_leak => ddd
1695 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1697 -------------------------------
1699 Filter: f_leak(z1.b)
1700 -> Seq Scan on z1 z1_1
1701 Filter: ((a % 2) = 0)
1704 SET SESSION AUTHORIZATION rls_regress_user2;
1705 SELECT * FROM z1 WHERE f_leak(b);
1706 NOTICE: f_leak => aaa
1707 NOTICE: f_leak => ccc
1714 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1716 -------------------------------
1718 Filter: f_leak(z1.b)
1719 -> Seq Scan on z1 z1_1
1720 Filter: ((a % 2) = 1)
1723 SET ROLE rls_regress_group2;
1724 SELECT * FROM z1 WHERE f_leak(b);
1725 NOTICE: f_leak => aaa
1726 NOTICE: f_leak => ccc
1733 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1735 -------------------------------
1737 Filter: f_leak(z1.b)
1738 -> Seq Scan on z1 z1_1
1739 Filter: ((a % 2) = 1)
1743 -- Views should follow policy for view owner.
1745 -- View and Table owner are the same.
1746 SET SESSION AUTHORIZATION rls_regress_user0;
1747 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
1748 GRANT SELECT ON rls_view TO rls_regress_user1;
1749 -- Query as role that is not owner of view or table. Should return all records.
1750 SET SESSION AUTHORIZATION rls_regress_user1;
1751 SELECT * FROM rls_view;
1752 NOTICE: f_leak => aaa
1753 NOTICE: f_leak => bbb
1754 NOTICE: f_leak => ccc
1755 NOTICE: f_leak => ddd
1764 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1766 ---------------------
1771 -- Query as view/table owner. Should return all records.
1772 SET SESSION AUTHORIZATION rls_regress_user0;
1773 SELECT * FROM rls_view;
1774 NOTICE: f_leak => aaa
1775 NOTICE: f_leak => bbb
1776 NOTICE: f_leak => ccc
1777 NOTICE: f_leak => ddd
1786 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1788 ---------------------
1794 -- View and Table owners are different.
1795 SET SESSION AUTHORIZATION rls_regress_user1;
1796 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
1797 GRANT SELECT ON rls_view TO rls_regress_user0;
1798 -- Query as role that is not owner of view but is owner of table.
1799 -- Should return records based on view owner policies.
1800 SET SESSION AUTHORIZATION rls_regress_user0;
1801 SELECT * FROM rls_view;
1802 NOTICE: f_leak => bbb
1803 NOTICE: f_leak => ddd
1810 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1812 -------------------------------
1814 Filter: f_leak(z1.b)
1815 -> Seq Scan on z1 z1_1
1816 Filter: ((a % 2) = 0)
1819 -- Query as role that is not owner of table but is owner of view.
1820 -- Should return records based on view owner policies.
1821 SET SESSION AUTHORIZATION rls_regress_user1;
1822 SELECT * FROM rls_view;
1823 NOTICE: f_leak => bbb
1824 NOTICE: f_leak => ddd
1831 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1833 -------------------------------
1835 Filter: f_leak(z1.b)
1836 -> Seq Scan on z1 z1_1
1837 Filter: ((a % 2) = 0)
1840 -- Query as role that is not the owner of the table or view without permissions.
1841 SET SESSION AUTHORIZATION rls_regress_user2;
1842 SELECT * FROM rls_view; --fail - permission denied.
1843 ERROR: permission denied for relation rls_view
1844 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
1845 ERROR: permission denied for relation rls_view
1846 -- Query as role that is not the owner of the table or view with permissions.
1847 SET SESSION AUTHORIZATION rls_regress_user1;
1848 GRANT SELECT ON rls_view TO rls_regress_user2;
1849 SELECT * FROM rls_view;
1850 NOTICE: f_leak => bbb
1851 NOTICE: f_leak => ddd
1858 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1860 -------------------------------
1862 Filter: f_leak(z1.b)
1863 -> Seq Scan on z1 z1_1
1864 Filter: ((a % 2) = 0)
1867 SET SESSION AUTHORIZATION rls_regress_user1;
1872 SET SESSION AUTHORIZATION rls_regress_user0;
1873 CREATE TABLE x1 (a int, b text, c text);
1874 GRANT ALL ON x1 TO PUBLIC;
1875 INSERT INTO x1 VALUES
1876 (1, 'abc', 'rls_regress_user1'),
1877 (2, 'bcd', 'rls_regress_user1'),
1878 (3, 'cde', 'rls_regress_user2'),
1879 (4, 'def', 'rls_regress_user2'),
1880 (5, 'efg', 'rls_regress_user1'),
1881 (6, 'fgh', 'rls_regress_user1'),
1882 (7, 'fgh', 'rls_regress_user2'),
1883 (8, 'fgh', 'rls_regress_user2');
1884 CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
1885 CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
1886 CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1);
1887 CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0);
1888 CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
1889 ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
1890 SET SESSION AUTHORIZATION rls_regress_user1;
1891 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
1892 NOTICE: f_leak => abc
1893 NOTICE: f_leak => bcd
1894 NOTICE: f_leak => def
1895 NOTICE: f_leak => efg
1896 NOTICE: f_leak => fgh
1897 NOTICE: f_leak => fgh
1899 ---+-----+-------------------
1900 1 | abc | rls_regress_user1
1901 2 | bcd | rls_regress_user1
1902 4 | def | rls_regress_user2
1903 5 | efg | rls_regress_user1
1904 6 | fgh | rls_regress_user1
1905 8 | fgh | rls_regress_user2
1908 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
1909 NOTICE: f_leak => abc
1910 NOTICE: f_leak => bcd
1911 NOTICE: f_leak => def
1912 NOTICE: f_leak => efg
1913 NOTICE: f_leak => fgh
1914 NOTICE: f_leak => fgh
1916 ---+----------+-------------------
1917 1 | abc_updt | rls_regress_user1
1918 2 | bcd_updt | rls_regress_user1
1919 4 | def_updt | rls_regress_user2
1920 5 | efg_updt | rls_regress_user1
1921 6 | fgh_updt | rls_regress_user1
1922 8 | fgh_updt | rls_regress_user2
1925 SET SESSION AUTHORIZATION rls_regress_user2;
1926 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
1927 NOTICE: f_leak => cde
1928 NOTICE: f_leak => fgh
1929 NOTICE: f_leak => bcd_updt
1930 NOTICE: f_leak => def_updt
1931 NOTICE: f_leak => fgh_updt
1932 NOTICE: f_leak => fgh_updt
1934 ---+----------+-------------------
1935 2 | bcd_updt | rls_regress_user1
1936 3 | cde | rls_regress_user2
1937 4 | def_updt | rls_regress_user2
1938 6 | fgh_updt | rls_regress_user1
1939 7 | fgh | rls_regress_user2
1940 8 | fgh_updt | rls_regress_user2
1943 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
1944 NOTICE: f_leak => cde
1945 NOTICE: f_leak => fgh
1946 NOTICE: f_leak => bcd_updt
1947 NOTICE: f_leak => def_updt
1948 NOTICE: f_leak => fgh_updt
1949 NOTICE: f_leak => fgh_updt
1951 ---+---------------+-------------------
1952 3 | cde_updt | rls_regress_user2
1953 7 | fgh_updt | rls_regress_user2
1954 2 | bcd_updt_updt | rls_regress_user1
1955 4 | def_updt_updt | rls_regress_user2
1956 6 | fgh_updt_updt | rls_regress_user1
1957 8 | fgh_updt_updt | rls_regress_user2
1960 DELETE FROM x1 WHERE f_leak(b) RETURNING *;
1961 NOTICE: f_leak => abc_updt
1962 NOTICE: f_leak => efg_updt
1963 NOTICE: f_leak => cde_updt
1964 NOTICE: f_leak => fgh_updt
1965 NOTICE: f_leak => bcd_updt_updt
1966 NOTICE: f_leak => def_updt_updt
1967 NOTICE: f_leak => fgh_updt_updt
1968 NOTICE: f_leak => fgh_updt_updt
1970 ---+---------------+-------------------
1971 1 | abc_updt | rls_regress_user1
1972 5 | efg_updt | rls_regress_user1
1973 3 | cde_updt | rls_regress_user2
1974 7 | fgh_updt | rls_regress_user2
1975 2 | bcd_updt_updt | rls_regress_user1
1976 4 | def_updt_updt | rls_regress_user2
1977 6 | fgh_updt_updt | rls_regress_user1
1978 8 | fgh_updt_updt | rls_regress_user2
1982 -- Duplicate Policy Names
1984 SET SESSION AUTHORIZATION rls_regress_user0;
1985 CREATE TABLE y1 (a int, b text);
1986 CREATE TABLE y2 (a int, b text);
1987 GRANT ALL ON y1, y2 TO rls_regress_user1;
1988 CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
1989 CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
1990 CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
1991 ERROR: policy "p1" for relation "y1" already exists
1992 CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK
1993 ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;
1994 ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
1996 -- Expression structure with SBV
1998 -- Create view as table owner. RLS should NOT be applied.
1999 SET SESSION AUTHORIZATION rls_regress_user0;
2000 CREATE VIEW rls_sbv WITH (security_barrier) AS
2001 SELECT * FROM y1 WHERE f_leak(b);
2002 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
2004 -----------------------------------
2006 Filter: (f_leak(b) AND (a = 1))
2010 -- Create view as role that does not own table. RLS should be applied.
2011 SET SESSION AUTHORIZATION rls_regress_user1;
2012 CREATE VIEW rls_sbv WITH (security_barrier) AS
2013 SELECT * FROM y1 WHERE f_leak(b);
2014 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
2016 ----------------------------------------------------------
2018 Filter: f_leak(y1.b)
2019 -> Seq Scan on y1 y1_1
2020 Filter: ((a = 1) AND ((a > 2) OR ((a % 2) = 0)))
2025 -- Expression structure
2027 SET SESSION AUTHORIZATION rls_regress_user0;
2028 INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
2029 CREATE POLICY p2 ON y2 USING (a % 3 = 0);
2030 CREATE POLICY p3 ON y2 USING (a % 4 = 0);
2031 SET SESSION AUTHORIZATION rls_regress_user1;
2032 SELECT * FROM y2 WHERE f_leak(b);
2033 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2034 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2035 NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
2036 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2037 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2038 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2039 NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
2040 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2041 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2042 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2043 NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
2044 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2045 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2046 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2048 ----+----------------------------------
2049 0 | cfcd208495d565ef66e7dff9f98764da
2050 2 | c81e728d9d4c2f636f067f89cc14862c
2051 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2052 4 | a87ff679a2f3e71d9181a67b7542122c
2053 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2054 8 | c9f0f895fb98ab9159f51fd0297e236d
2055 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2056 10 | d3d9446802a44259755d38e6d163e820
2057 12 | c20ad4d76fe97759aa27a0c99bff6710
2058 14 | aab3238922bcc25a6f606eb525ffdc56
2059 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2060 16 | c74d97b01eae257e44aa9d5bade97baf
2061 18 | 6f4922f45568161a8cdf4ad2299f6d23
2062 20 | 98f13708210194c475687be6106a3b84
2065 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
2067 -------------------------------------------------------------------
2069 Filter: f_leak(y2.b)
2070 -> Seq Scan on y2 y2_1
2071 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
2075 -- Qual push-down of leaky functions, when not referring to table
2077 SELECT * FROM y2 WHERE f_leak('abc');
2078 NOTICE: f_leak => abc
2079 NOTICE: f_leak => abc
2080 NOTICE: f_leak => abc
2081 NOTICE: f_leak => abc
2082 NOTICE: f_leak => abc
2083 NOTICE: f_leak => abc
2084 NOTICE: f_leak => abc
2085 NOTICE: f_leak => abc
2086 NOTICE: f_leak => abc
2087 NOTICE: f_leak => abc
2088 NOTICE: f_leak => abc
2089 NOTICE: f_leak => abc
2090 NOTICE: f_leak => abc
2091 NOTICE: f_leak => abc
2092 NOTICE: f_leak => abc
2093 NOTICE: f_leak => abc
2094 NOTICE: f_leak => abc
2095 NOTICE: f_leak => abc
2096 NOTICE: f_leak => abc
2097 NOTICE: f_leak => abc
2098 NOTICE: f_leak => abc
2100 ----+----------------------------------
2101 0 | cfcd208495d565ef66e7dff9f98764da
2102 2 | c81e728d9d4c2f636f067f89cc14862c
2103 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2104 4 | a87ff679a2f3e71d9181a67b7542122c
2105 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2106 8 | c9f0f895fb98ab9159f51fd0297e236d
2107 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2108 10 | d3d9446802a44259755d38e6d163e820
2109 12 | c20ad4d76fe97759aa27a0c99bff6710
2110 14 | aab3238922bcc25a6f606eb525ffdc56
2111 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2112 16 | c74d97b01eae257e44aa9d5bade97baf
2113 18 | 6f4922f45568161a8cdf4ad2299f6d23
2114 20 | 98f13708210194c475687be6106a3b84
2117 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc');
2119 ---------------------------------------------------------------------------------------
2121 Filter: (f_leak('abc'::text) AND (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)))
2124 CREATE TABLE test_qual_pushdown (
2127 INSERT INTO test_qual_pushdown VALUES ('abc'),('def');
2128 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
2129 NOTICE: f_leak => abc
2130 NOTICE: f_leak => def
2135 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
2137 -------------------------------------------------------------------------
2139 Hash Cond: (test_qual_pushdown.abc = y2.b)
2140 -> Seq Scan on test_qual_pushdown
2144 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
2147 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
2148 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2149 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2150 NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
2151 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2152 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2153 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2154 NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
2155 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2156 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2157 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2158 NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
2159 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2160 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2161 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2166 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
2168 -------------------------------------------------------------------------------
2170 Hash Cond: (test_qual_pushdown.abc = y2.b)
2171 -> Seq Scan on test_qual_pushdown
2173 -> Subquery Scan on y2
2174 Filter: f_leak(y2.b)
2175 -> Seq Scan on y2 y2_1
2176 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
2179 DROP TABLE test_qual_pushdown;
2181 -- Plancache invalidate on user change.
2183 RESET SESSION AUTHORIZATION;
2184 -- Suppress NOTICE messages when doing a cascaded drop.
2185 SET client_min_messages TO 'warning';
2186 DROP TABLE t1 CASCADE;
2187 RESET client_min_messages;
2188 CREATE TABLE t1 (a integer);
2189 GRANT SELECT ON t1 TO rls_regress_user1, rls_regress_user2;
2190 CREATE POLICY p1 ON t1 TO rls_regress_user1 USING ((a % 2) = 0);
2191 CREATE POLICY p2 ON t1 TO rls_regress_user2 USING ((a % 4) = 0);
2192 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
2193 SET ROLE rls_regress_user1;
2194 PREPARE role_inval AS SELECT * FROM t1;
2195 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2197 -------------------------
2199 Filter: ((a % 2) = 0)
2202 SET ROLE rls_regress_user2;
2203 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2205 -------------------------
2207 Filter: ((a % 4) = 0)
2213 RESET SESSION AUTHORIZATION;
2214 DROP TABLE t1 CASCADE;
2215 CREATE TABLE t1 (a integer, b text);
2216 CREATE POLICY p1 ON t1 USING (a % 2 = 0);
2217 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
2218 GRANT ALL ON t1 TO rls_regress_user1;
2219 INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
2220 SET SESSION AUTHORIZATION rls_regress_user1;
2221 WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
2222 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2223 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2224 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2225 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2226 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2227 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2228 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2229 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2230 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2231 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2232 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2234 ----+----------------------------------
2235 0 | cfcd208495d565ef66e7dff9f98764da
2236 2 | c81e728d9d4c2f636f067f89cc14862c
2237 4 | a87ff679a2f3e71d9181a67b7542122c
2238 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2239 8 | c9f0f895fb98ab9159f51fd0297e236d
2240 10 | d3d9446802a44259755d38e6d163e820
2241 12 | c20ad4d76fe97759aa27a0c99bff6710
2242 14 | aab3238922bcc25a6f606eb525ffdc56
2243 16 | c74d97b01eae257e44aa9d5bade97baf
2244 18 | 6f4922f45568161a8cdf4ad2299f6d23
2245 20 | 98f13708210194c475687be6106a3b84
2248 EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
2250 ---------------------------------------
2253 -> Subquery Scan on t1
2254 Filter: f_leak(t1.b)
2255 -> Seq Scan on t1 t1_1
2256 Filter: ((a % 2) = 0)
2259 WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
2260 ERROR: new row violates row level security policy for "t1"
2261 WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
2263 ----+----------------------------------
2264 0 | cfcd208495d565ef66e7dff9f98764da
2265 2 | c81e728d9d4c2f636f067f89cc14862c
2266 4 | a87ff679a2f3e71d9181a67b7542122c
2267 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2268 8 | c9f0f895fb98ab9159f51fd0297e236d
2269 10 | d3d9446802a44259755d38e6d163e820
2270 12 | c20ad4d76fe97759aa27a0c99bff6710
2271 14 | aab3238922bcc25a6f606eb525ffdc56
2272 16 | c74d97b01eae257e44aa9d5bade97baf
2273 18 | 6f4922f45568161a8cdf4ad2299f6d23
2274 20 | 98f13708210194c475687be6106a3b84
2277 WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail
2278 ERROR: new row violates row level security policy for "t1"
2279 WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok
2288 RESET SESSION AUTHORIZATION;
2289 ALTER POLICY p1 ON t1 RENAME TO p1; --fail
2290 ERROR: policy "p1" for table "t1" already exists
2291 SELECT polname, relname
2293 JOIN pg_class pc ON (pc.oid = pol.polrelid)
2294 WHERE relname = 't1';
2300 ALTER POLICY p1 ON t1 RENAME TO p2; --ok
2301 SELECT polname, relname
2303 JOIN pg_class pc ON (pc.oid = pol.polrelid)
2304 WHERE relname = 't1';
2311 -- Check INSERT SELECT
2313 SET SESSION AUTHORIZATION rls_regress_user1;
2314 CREATE TABLE t2 (a integer, b text);
2315 INSERT INTO t2 (SELECT * FROM t1);
2316 EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
2318 -------------------------------
2321 Filter: ((a % 2) = 0)
2326 ----+----------------------------------
2327 0 | cfcd208495d565ef66e7dff9f98764da
2328 2 | c81e728d9d4c2f636f067f89cc14862c
2329 4 | a87ff679a2f3e71d9181a67b7542122c
2330 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2331 8 | c9f0f895fb98ab9159f51fd0297e236d
2332 10 | d3d9446802a44259755d38e6d163e820
2333 12 | c20ad4d76fe97759aa27a0c99bff6710
2334 14 | aab3238922bcc25a6f606eb525ffdc56
2335 16 | c74d97b01eae257e44aa9d5bade97baf
2336 18 | 6f4922f45568161a8cdf4ad2299f6d23
2337 20 | 98f13708210194c475687be6106a3b84
2341 EXPLAIN (COSTS OFF) SELECT * FROM t2;
2347 CREATE TABLE t3 AS SELECT * FROM t1;
2350 ----+----------------------------------
2351 0 | cfcd208495d565ef66e7dff9f98764da
2352 2 | c81e728d9d4c2f636f067f89cc14862c
2353 4 | a87ff679a2f3e71d9181a67b7542122c
2354 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2355 8 | c9f0f895fb98ab9159f51fd0297e236d
2356 10 | d3d9446802a44259755d38e6d163e820
2357 12 | c20ad4d76fe97759aa27a0c99bff6710
2358 14 | aab3238922bcc25a6f606eb525ffdc56
2359 16 | c74d97b01eae257e44aa9d5bade97baf
2360 18 | 6f4922f45568161a8cdf4ad2299f6d23
2361 20 | 98f13708210194c475687be6106a3b84
2365 SELECT * INTO t4 FROM t1;
2368 ----+----------------------------------
2369 0 | cfcd208495d565ef66e7dff9f98764da
2370 2 | c81e728d9d4c2f636f067f89cc14862c
2371 4 | a87ff679a2f3e71d9181a67b7542122c
2372 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2373 8 | c9f0f895fb98ab9159f51fd0297e236d
2374 10 | d3d9446802a44259755d38e6d163e820
2375 12 | c20ad4d76fe97759aa27a0c99bff6710
2376 14 | aab3238922bcc25a6f606eb525ffdc56
2377 16 | c74d97b01eae257e44aa9d5bade97baf
2378 18 | 6f4922f45568161a8cdf4ad2299f6d23
2379 20 | 98f13708210194c475687be6106a3b84
2386 SET SESSION AUTHORIZATION rls_regress_user0;
2387 CREATE TABLE blog (id integer, author text, post text);
2388 CREATE TABLE comment (blog_id integer, message text);
2389 GRANT ALL ON blog, comment TO rls_regress_user1;
2390 CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
2391 ALTER TABLE blog ENABLE ROW LEVEL SECURITY;
2392 INSERT INTO blog VALUES
2393 (1, 'alice', 'blog #1'),
2394 (2, 'bob', 'blog #1'),
2395 (3, 'alice', 'blog #2'),
2396 (4, 'alice', 'blog #3'),
2397 (5, 'john', 'blog #1');
2398 INSERT INTO comment VALUES
2405 SET SESSION AUTHORIZATION rls_regress_user1;
2406 -- Check RLS JOIN with Non-RLS.
2407 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
2408 id | author | message
2409 ----+--------+-------------
2411 2 | bob | who did it?
2414 -- Check Non-RLS JOIN with RLS.
2415 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
2416 id | author | message
2417 ----+--------+-------------
2419 2 | bob | who did it?
2422 SET SESSION AUTHORIZATION rls_regress_user0;
2423 CREATE POLICY comment_1 ON comment USING (blog_id < 4);
2424 ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
2425 SET SESSION AUTHORIZATION rls_regress_user1;
2426 -- Check RLS JOIN RLS
2427 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
2428 id | author | message
2429 ----+--------+-------------
2430 2 | bob | who did it?
2433 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
2434 id | author | message
2435 ----+--------+-------------
2436 2 | bob | who did it?
2439 SET SESSION AUTHORIZATION rls_regress_user0;
2440 DROP TABLE blog, comment;
2442 -- Default Deny Policy
2444 RESET SESSION AUTHORIZATION;
2445 DROP POLICY p2 ON t1;
2446 ALTER TABLE t1 OWNER TO rls_regress_user0;
2447 -- Check that default deny does not apply to superuser.
2448 RESET SESSION AUTHORIZATION;
2451 ----+----------------------------------
2452 1 | c4ca4238a0b923820dcc509a6f75849b
2453 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2454 5 | e4da3b7fbbce2345d7772b0674a318d5
2455 7 | 8f14e45fceea167a5a36dedd4bea2543
2456 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2457 11 | 6512bd43d9caa6e02c990b0a82652dca
2458 13 | c51ce410c124a10e0db5e4b97fc2af39
2459 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2460 17 | 70efdf2ec9b086079795c442636b55fb
2461 19 | 1f0e3dad99908345f7439f8ffabdffc4
2462 0 | cfcd208495d565ef66e7dff9f98764da
2463 2 | c81e728d9d4c2f636f067f89cc14862c
2464 4 | a87ff679a2f3e71d9181a67b7542122c
2465 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2466 8 | c9f0f895fb98ab9159f51fd0297e236d
2467 10 | d3d9446802a44259755d38e6d163e820
2468 12 | c20ad4d76fe97759aa27a0c99bff6710
2469 14 | aab3238922bcc25a6f606eb525ffdc56
2470 16 | c74d97b01eae257e44aa9d5bade97baf
2471 18 | 6f4922f45568161a8cdf4ad2299f6d23
2472 20 | 98f13708210194c475687be6106a3b84
2476 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2482 -- Check that default deny does not apply to table owner.
2483 SET SESSION AUTHORIZATION rls_regress_user0;
2486 ----+----------------------------------
2487 1 | c4ca4238a0b923820dcc509a6f75849b
2488 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2489 5 | e4da3b7fbbce2345d7772b0674a318d5
2490 7 | 8f14e45fceea167a5a36dedd4bea2543
2491 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2492 11 | 6512bd43d9caa6e02c990b0a82652dca
2493 13 | c51ce410c124a10e0db5e4b97fc2af39
2494 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2495 17 | 70efdf2ec9b086079795c442636b55fb
2496 19 | 1f0e3dad99908345f7439f8ffabdffc4
2497 0 | cfcd208495d565ef66e7dff9f98764da
2498 2 | c81e728d9d4c2f636f067f89cc14862c
2499 4 | a87ff679a2f3e71d9181a67b7542122c
2500 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2501 8 | c9f0f895fb98ab9159f51fd0297e236d
2502 10 | d3d9446802a44259755d38e6d163e820
2503 12 | c20ad4d76fe97759aa27a0c99bff6710
2504 14 | aab3238922bcc25a6f606eb525ffdc56
2505 16 | c74d97b01eae257e44aa9d5bade97baf
2506 18 | 6f4922f45568161a8cdf4ad2299f6d23
2507 20 | 98f13708210194c475687be6106a3b84
2511 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2517 -- Check that default deny does apply to superuser when RLS force.
2518 SET row_security TO FORCE;
2519 RESET SESSION AUTHORIZATION;
2525 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2527 --------------------------
2529 One-Time Filter: false
2532 -- Check that default deny does apply to table owner when RLS force.
2533 SET SESSION AUTHORIZATION rls_regress_user0;
2539 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2541 --------------------------
2543 One-Time Filter: false
2546 -- Check that default deny applies to non-owner/non-superuser when RLS on.
2547 SET SESSION AUTHORIZATION rls_regress_user1;
2548 SET row_security TO ON;
2554 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2556 --------------------------
2558 One-Time Filter: false
2561 SET SESSION AUTHORIZATION rls_regress_user1;
2567 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2569 --------------------------
2571 One-Time Filter: false
2577 RESET SESSION AUTHORIZATION;
2578 DROP TABLE copy_t CASCADE;
2579 ERROR: table "copy_t" does not exist
2580 CREATE TABLE copy_t (a integer, b text);
2581 CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
2582 ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
2583 GRANT ALL ON copy_t TO rls_regress_user1, rls_regress_exempt_user;
2584 INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
2585 -- Check COPY TO as Superuser/owner.
2586 RESET SESSION AUTHORIZATION;
2587 SET row_security TO OFF;
2588 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
2589 0,cfcd208495d565ef66e7dff9f98764da
2590 1,c4ca4238a0b923820dcc509a6f75849b
2591 2,c81e728d9d4c2f636f067f89cc14862c
2592 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
2593 4,a87ff679a2f3e71d9181a67b7542122c
2594 5,e4da3b7fbbce2345d7772b0674a318d5
2595 6,1679091c5a880faf6fb5e6087eb1b2dc
2596 7,8f14e45fceea167a5a36dedd4bea2543
2597 8,c9f0f895fb98ab9159f51fd0297e236d
2598 9,45c48cce2e2d7fbdea1afc51c7c6ad26
2599 10,d3d9446802a44259755d38e6d163e820
2600 SET row_security TO ON;
2601 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
2602 0,cfcd208495d565ef66e7dff9f98764da
2603 1,c4ca4238a0b923820dcc509a6f75849b
2604 2,c81e728d9d4c2f636f067f89cc14862c
2605 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
2606 4,a87ff679a2f3e71d9181a67b7542122c
2607 5,e4da3b7fbbce2345d7772b0674a318d5
2608 6,1679091c5a880faf6fb5e6087eb1b2dc
2609 7,8f14e45fceea167a5a36dedd4bea2543
2610 8,c9f0f895fb98ab9159f51fd0297e236d
2611 9,45c48cce2e2d7fbdea1afc51c7c6ad26
2612 10,d3d9446802a44259755d38e6d163e820
2613 SET row_security TO FORCE;
2614 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
2615 0,cfcd208495d565ef66e7dff9f98764da
2616 2,c81e728d9d4c2f636f067f89cc14862c
2617 4,a87ff679a2f3e71d9181a67b7542122c
2618 6,1679091c5a880faf6fb5e6087eb1b2dc
2619 8,c9f0f895fb98ab9159f51fd0297e236d
2620 10,d3d9446802a44259755d38e6d163e820
2621 -- Check COPY TO as user with permissions.
2622 SET SESSION AUTHORIZATION rls_regress_user1;
2623 SET row_security TO OFF;
2624 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
2625 ERROR: insufficient privilege to bypass row security.
2626 SET row_security TO ON;
2627 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2628 0,cfcd208495d565ef66e7dff9f98764da
2629 2,c81e728d9d4c2f636f067f89cc14862c
2630 4,a87ff679a2f3e71d9181a67b7542122c
2631 6,1679091c5a880faf6fb5e6087eb1b2dc
2632 8,c9f0f895fb98ab9159f51fd0297e236d
2633 10,d3d9446802a44259755d38e6d163e820
2634 SET row_security TO FORCE;
2635 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2636 0,cfcd208495d565ef66e7dff9f98764da
2637 2,c81e728d9d4c2f636f067f89cc14862c
2638 4,a87ff679a2f3e71d9181a67b7542122c
2639 6,1679091c5a880faf6fb5e6087eb1b2dc
2640 8,c9f0f895fb98ab9159f51fd0297e236d
2641 10,d3d9446802a44259755d38e6d163e820
2642 -- Check COPY TO as user with permissions and BYPASSRLS
2643 SET SESSION AUTHORIZATION rls_regress_exempt_user;
2644 SET row_security TO OFF;
2645 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2646 0,cfcd208495d565ef66e7dff9f98764da
2647 1,c4ca4238a0b923820dcc509a6f75849b
2648 2,c81e728d9d4c2f636f067f89cc14862c
2649 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
2650 4,a87ff679a2f3e71d9181a67b7542122c
2651 5,e4da3b7fbbce2345d7772b0674a318d5
2652 6,1679091c5a880faf6fb5e6087eb1b2dc
2653 7,8f14e45fceea167a5a36dedd4bea2543
2654 8,c9f0f895fb98ab9159f51fd0297e236d
2655 9,45c48cce2e2d7fbdea1afc51c7c6ad26
2656 10,d3d9446802a44259755d38e6d163e820
2657 SET row_security TO ON;
2658 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2659 0,cfcd208495d565ef66e7dff9f98764da
2660 2,c81e728d9d4c2f636f067f89cc14862c
2661 4,a87ff679a2f3e71d9181a67b7542122c
2662 6,1679091c5a880faf6fb5e6087eb1b2dc
2663 8,c9f0f895fb98ab9159f51fd0297e236d
2664 10,d3d9446802a44259755d38e6d163e820
2665 SET row_security TO FORCE;
2666 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2667 0,cfcd208495d565ef66e7dff9f98764da
2668 2,c81e728d9d4c2f636f067f89cc14862c
2669 4,a87ff679a2f3e71d9181a67b7542122c
2670 6,1679091c5a880faf6fb5e6087eb1b2dc
2671 8,c9f0f895fb98ab9159f51fd0297e236d
2672 10,d3d9446802a44259755d38e6d163e820
2673 -- Check COPY TO as user without permissions.SET row_security TO OFF;
2674 SET SESSION AUTHORIZATION rls_regress_user2;
2675 SET row_security TO OFF;
2676 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
2677 ERROR: insufficient privilege to bypass row security.
2678 SET row_security TO ON;
2679 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
2680 ERROR: permission denied for relation copy_t
2681 SET row_security TO FORCE;
2682 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
2683 ERROR: permission denied for relation copy_t
2684 -- Check COPY FROM as Superuser/owner.
2685 RESET SESSION AUTHORIZATION;
2686 SET row_security TO OFF;
2687 COPY copy_t FROM STDIN; --ok
2688 SET row_security TO ON;
2689 COPY copy_t FROM STDIN; --ok
2690 SET row_security TO FORCE;
2691 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2692 ERROR: COPY FROM not supported with row level security.
2693 HINT: Use direct INSERT statements instead.
2694 -- Check COPY FROM as user with permissions.
2695 SET SESSION AUTHORIZATION rls_regress_user1;
2696 SET row_security TO OFF;
2697 COPY copy_t FROM STDIN; --fail - insufficient privilege to bypass rls.
2698 ERROR: insufficient privilege to bypass row security.
2699 SET row_security TO ON;
2700 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2701 ERROR: COPY FROM not supported with row level security.
2702 HINT: Use direct INSERT statements instead.
2703 SET row_security TO FORCE;
2704 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2705 ERROR: COPY FROM not supported with row level security.
2706 HINT: Use direct INSERT statements instead.
2707 -- Check COPY TO as user with permissions and BYPASSRLS
2708 SET SESSION AUTHORIZATION rls_regress_exempt_user;
2709 SET row_security TO OFF;
2710 COPY copy_t FROM STDIN; --ok
2711 SET row_security TO ON;
2712 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2713 ERROR: COPY FROM not supported with row level security.
2714 HINT: Use direct INSERT statements instead.
2715 SET row_security TO FORCE;
2716 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2717 ERROR: COPY FROM not supported with row level security.
2718 HINT: Use direct INSERT statements instead.
2719 -- Check COPY FROM as user without permissions.
2720 SET SESSION AUTHORIZATION rls_regress_user2;
2721 SET row_security TO OFF;
2722 COPY copy_t FROM STDIN; --fail - permission denied.
2723 ERROR: permission denied for relation copy_t
2724 SET row_security TO ON;
2725 COPY copy_t FROM STDIN; --fail - permission denied.
2726 ERROR: permission denied for relation copy_t
2727 SET row_security TO FORCE;
2728 COPY copy_t FROM STDIN; --fail - permission denied.
2729 ERROR: permission denied for relation copy_t
2730 RESET SESSION AUTHORIZATION;
2735 RESET SESSION AUTHORIZATION;
2736 -- Suppress NOTICE messages when doing a cascaded drop.
2737 SET client_min_messages TO 'warning';
2738 DROP SCHEMA rls_regress_schema CASCADE;
2739 RESET client_min_messages;
2740 DROP USER rls_regress_user0;
2741 DROP USER rls_regress_user1;
2742 DROP USER rls_regress_user2;
2743 DROP USER rls_regress_exempt_user;
2744 DROP ROLE rls_regress_group1;
2745 DROP ROLE rls_regress_group2;