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 -- viewpoint from rls_regress_user2
105 SET SESSION AUTHORIZATION rls_regress_user2;
106 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
107 NOTICE: f_leak => my first novel
108 NOTICE: f_leak => my second novel
109 NOTICE: f_leak => my science fiction
110 NOTICE: f_leak => my first manga
111 NOTICE: f_leak => my second manga
112 NOTICE: f_leak => great science fiction
113 NOTICE: f_leak => great technology book
114 NOTICE: f_leak => great manga
115 did | cid | dlevel | dauthor | dtitle
116 -----+-----+--------+-------------------+-----------------------
117 1 | 11 | 1 | rls_regress_user1 | my first novel
118 2 | 11 | 2 | rls_regress_user1 | my second novel
119 3 | 22 | 2 | rls_regress_user1 | my science fiction
120 4 | 44 | 1 | rls_regress_user1 | my first manga
121 5 | 44 | 2 | rls_regress_user1 | my second manga
122 6 | 22 | 1 | rls_regress_user2 | great science fiction
123 7 | 33 | 2 | rls_regress_user2 | great technology book
124 8 | 44 | 1 | rls_regress_user2 | great manga
127 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
128 NOTICE: f_leak => my first novel
129 NOTICE: f_leak => my second novel
130 NOTICE: f_leak => my science fiction
131 NOTICE: f_leak => my first manga
132 NOTICE: f_leak => my second manga
133 NOTICE: f_leak => great science fiction
134 NOTICE: f_leak => great technology book
135 NOTICE: f_leak => great manga
136 cid | did | dlevel | dauthor | dtitle | cname
137 -----+-----+--------+-------------------+-----------------------+-----------------
138 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
139 11 | 2 | 2 | rls_regress_user1 | my second novel | novel
140 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
141 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
142 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
143 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
144 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
145 44 | 8 | 1 | rls_regress_user2 | great manga | manga
148 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
150 ----------------------------------------------------------
151 Subquery Scan on document
152 Filter: f_leak(document.dtitle)
153 -> Seq Scan on document document_1
154 Filter: (dlevel <= $0)
155 InitPlan 1 (returns $0)
156 -> Index Scan using uaccount_pkey on uaccount
157 Index Cond: (pguser = "current_user"())
160 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
162 ----------------------------------------------------------------------
164 Hash Cond: (category.cid = document.cid)
165 -> Seq Scan on category
167 -> Subquery Scan on document
168 Filter: f_leak(document.dtitle)
169 -> Seq Scan on document document_1
170 Filter: (dlevel <= $0)
171 InitPlan 1 (returns $0)
172 -> Index Scan using uaccount_pkey on uaccount
173 Index Cond: (pguser = "current_user"())
176 -- only owner can change policies
177 ALTER POLICY p1 ON document USING (true); --fail
178 ERROR: must be owner of relation document
179 DROP POLICY p1 ON document; --fail
180 ERROR: must be owner of relation document
181 SET SESSION AUTHORIZATION rls_regress_user0;
182 ALTER POLICY p1 ON document USING (dauthor = current_user);
183 -- viewpoint from rls_regress_user1 again
184 SET SESSION AUTHORIZATION rls_regress_user1;
185 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
186 NOTICE: f_leak => my first novel
187 NOTICE: f_leak => my second novel
188 NOTICE: f_leak => my science fiction
189 NOTICE: f_leak => my first manga
190 NOTICE: f_leak => my second manga
191 did | cid | dlevel | dauthor | dtitle
192 -----+-----+--------+-------------------+--------------------
193 1 | 11 | 1 | rls_regress_user1 | my first novel
194 2 | 11 | 2 | rls_regress_user1 | my second novel
195 3 | 22 | 2 | rls_regress_user1 | my science fiction
196 4 | 44 | 1 | rls_regress_user1 | my first manga
197 5 | 44 | 2 | rls_regress_user1 | my second manga
200 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
201 NOTICE: f_leak => my first novel
202 NOTICE: f_leak => my second novel
203 NOTICE: f_leak => my science fiction
204 NOTICE: f_leak => my first manga
205 NOTICE: f_leak => my second manga
206 cid | did | dlevel | dauthor | dtitle | cname
207 -----+-----+--------+-------------------+--------------------+-----------------
208 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
209 11 | 2 | 2 | rls_regress_user1 | my second novel | novel
210 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
211 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
212 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
215 -- viewpoint from rls_regres_user2 again
216 SET SESSION AUTHORIZATION rls_regress_user2;
217 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
218 NOTICE: f_leak => great science fiction
219 NOTICE: f_leak => great technology book
220 NOTICE: f_leak => great manga
221 did | cid | dlevel | dauthor | dtitle
222 -----+-----+--------+-------------------+-----------------------
223 6 | 22 | 1 | rls_regress_user2 | great science fiction
224 7 | 33 | 2 | rls_regress_user2 | great technology book
225 8 | 44 | 1 | rls_regress_user2 | great manga
228 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
229 NOTICE: f_leak => great science fiction
230 NOTICE: f_leak => great technology book
231 NOTICE: f_leak => great manga
232 cid | did | dlevel | dauthor | dtitle | cname
233 -----+-----+--------+-------------------+-----------------------+-----------------
234 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
235 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
236 44 | 8 | 1 | rls_regress_user2 | great manga | manga
239 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
241 ----------------------------------------------
242 Subquery Scan on document
243 Filter: f_leak(document.dtitle)
244 -> Seq Scan on document document_1
245 Filter: (dauthor = "current_user"())
248 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
250 ----------------------------------------------------
252 -> Subquery Scan on document
253 Filter: f_leak(document.dtitle)
254 -> Seq Scan on document document_1
255 Filter: (dauthor = "current_user"())
256 -> Index Scan using category_pkey on category
257 Index Cond: (cid = document.cid)
260 -- interaction of FK/PK constraints
261 SET SESSION AUTHORIZATION rls_regress_user0;
262 CREATE POLICY p2 ON category
263 USING (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33)
264 WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44)
266 ALTER TABLE category ENABLE ROW LEVEL SECURITY;
267 -- cannot delete PK referenced by invisible FK
268 SET SESSION AUTHORIZATION rls_regress_user1;
269 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
270 did | cid | dlevel | dauthor | dtitle | cid | cname
271 -----+-----+--------+-------------------+--------------------+-----+------------
272 2 | 11 | 2 | rls_regress_user1 | my second novel | 11 | novel
273 1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel
274 | | | | | 33 | technology
275 5 | 44 | 2 | rls_regress_user1 | my second manga | |
276 4 | 44 | 1 | rls_regress_user1 | my first manga | |
277 3 | 22 | 2 | rls_regress_user1 | my science fiction | |
280 DELETE FROM category WHERE cid = 33; -- fails with FK violation
281 ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"
282 DETAIL: Key (cid)=(33) is still referenced from table "document".
283 -- can insert FK referencing invisible PK
284 SET SESSION AUTHORIZATION rls_regress_user2;
285 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
286 did | cid | dlevel | dauthor | dtitle | cid | cname
287 -----+-----+--------+-------------------+-----------------------+-----+-----------------
288 6 | 22 | 1 | rls_regress_user2 | great science fiction | 22 | science fiction
289 8 | 44 | 1 | rls_regress_user2 | great manga | 44 | manga
290 7 | 33 | 2 | rls_regress_user2 | great technology book | |
293 INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');
294 -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
295 SET SESSION AUTHORIZATION rls_regress_user1;
296 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
297 ERROR: duplicate key value violates unique constraint "document_pkey"
298 SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
299 did | cid | dlevel | dauthor | dtitle
300 -----+-----+--------+---------+--------
303 -- database superuser does bypass RLS policy when enabled
304 RESET SESSION AUTHORIZATION;
305 SET row_security TO ON;
306 SELECT * FROM document;
307 did | cid | dlevel | dauthor | dtitle
308 -----+-----+--------+-------------------+-----------------------
309 1 | 11 | 1 | rls_regress_user1 | my first novel
310 2 | 11 | 2 | rls_regress_user1 | my second novel
311 3 | 22 | 2 | rls_regress_user1 | my science fiction
312 4 | 44 | 1 | rls_regress_user1 | my first manga
313 5 | 44 | 2 | rls_regress_user1 | my second manga
314 6 | 22 | 1 | rls_regress_user2 | great science fiction
315 7 | 33 | 2 | rls_regress_user2 | great technology book
316 8 | 44 | 1 | rls_regress_user2 | great manga
317 10 | 33 | 1 | rls_regress_user2 | hoge
320 SELECT * FROM category;
322 -----+-----------------
329 -- database superuser does not bypass RLS policy when FORCE enabled.
330 RESET SESSION AUTHORIZATION;
331 SET row_security TO FORCE;
332 SELECT * FROM document;
333 did | cid | dlevel | dauthor | dtitle
334 -----+-----+--------+---------+--------
337 SELECT * FROM category;
342 -- database superuser does bypass RLS policy when disabled
343 RESET SESSION AUTHORIZATION;
344 SET row_security TO OFF;
345 SELECT * FROM document;
346 did | cid | dlevel | dauthor | dtitle
347 -----+-----+--------+-------------------+-----------------------
348 1 | 11 | 1 | rls_regress_user1 | my first novel
349 2 | 11 | 2 | rls_regress_user1 | my second novel
350 3 | 22 | 2 | rls_regress_user1 | my science fiction
351 4 | 44 | 1 | rls_regress_user1 | my first manga
352 5 | 44 | 2 | rls_regress_user1 | my second manga
353 6 | 22 | 1 | rls_regress_user2 | great science fiction
354 7 | 33 | 2 | rls_regress_user2 | great technology book
355 8 | 44 | 1 | rls_regress_user2 | great manga
356 10 | 33 | 1 | rls_regress_user2 | hoge
359 SELECT * FROM category;
361 -----+-----------------
368 -- database non-superuser with bypass privilege can bypass RLS policy when disabled
369 SET SESSION AUTHORIZATION rls_regress_exempt_user;
370 SET row_security TO OFF;
371 SELECT * FROM document;
372 did | cid | dlevel | dauthor | dtitle
373 -----+-----+--------+-------------------+-----------------------
374 1 | 11 | 1 | rls_regress_user1 | my first novel
375 2 | 11 | 2 | rls_regress_user1 | my second novel
376 3 | 22 | 2 | rls_regress_user1 | my science fiction
377 4 | 44 | 1 | rls_regress_user1 | my first manga
378 5 | 44 | 2 | rls_regress_user1 | my second manga
379 6 | 22 | 1 | rls_regress_user2 | great science fiction
380 7 | 33 | 2 | rls_regress_user2 | great technology book
381 8 | 44 | 1 | rls_regress_user2 | great manga
382 10 | 33 | 1 | rls_regress_user2 | hoge
385 SELECT * FROM category;
387 -----+-----------------
394 -- RLS policy applies to table owner when FORCE enabled.
395 SET SESSION AUTHORIZATION rls_regress_user0;
396 SET row_security TO FORCE;
397 SELECT * FROM document;
398 did | cid | dlevel | dauthor | dtitle
399 -----+-----+--------+---------+--------
402 SELECT * FROM category;
407 -- RLS policy does not apply to table owner when RLS enabled.
408 SET SESSION AUTHORIZATION rls_regress_user0;
409 SET row_security TO ON;
410 SELECT * FROM document;
411 did | cid | dlevel | dauthor | dtitle
412 -----+-----+--------+-------------------+-----------------------
413 1 | 11 | 1 | rls_regress_user1 | my first novel
414 2 | 11 | 2 | rls_regress_user1 | my second novel
415 3 | 22 | 2 | rls_regress_user1 | my science fiction
416 4 | 44 | 1 | rls_regress_user1 | my first manga
417 5 | 44 | 2 | rls_regress_user1 | my second manga
418 6 | 22 | 1 | rls_regress_user2 | great science fiction
419 7 | 33 | 2 | rls_regress_user2 | great technology book
420 8 | 44 | 1 | rls_regress_user2 | great manga
421 10 | 33 | 1 | rls_regress_user2 | hoge
424 SELECT * FROM category;
426 -----+-----------------
433 -- RLS policy does not apply to table owner when RLS disabled.
434 SET SESSION AUTHORIZATION rls_regress_user0;
435 SET row_security TO OFF;
436 SELECT * FROM document;
437 did | cid | dlevel | dauthor | dtitle
438 -----+-----+--------+-------------------+-----------------------
439 1 | 11 | 1 | rls_regress_user1 | my first novel
440 2 | 11 | 2 | rls_regress_user1 | my second novel
441 3 | 22 | 2 | rls_regress_user1 | my science fiction
442 4 | 44 | 1 | rls_regress_user1 | my first manga
443 5 | 44 | 2 | rls_regress_user1 | my second manga
444 6 | 22 | 1 | rls_regress_user2 | great science fiction
445 7 | 33 | 2 | rls_regress_user2 | great technology book
446 8 | 44 | 1 | rls_regress_user2 | great manga
447 10 | 33 | 1 | rls_regress_user2 | hoge
450 SELECT * FROM category;
452 -----+-----------------
460 -- Table inheritance and RLS policy
462 SET SESSION AUTHORIZATION rls_regress_user0;
463 SET row_security TO ON;
464 CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS;
465 ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor
466 GRANT ALL ON t1 TO public;
467 COPY t1 FROM stdin WITH (oids);
468 CREATE TABLE t2 (c float) INHERITS (t1);
469 COPY t2 FROM stdin WITH (oids);
470 CREATE TABLE t3 (c text, b text, a int) WITH OIDS;
471 ALTER TABLE t3 INHERIT t1;
472 COPY t3(a,b,c) FROM stdin WITH (oids);
473 CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number
474 CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number
475 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
476 ALTER TABLE t2 ENABLE ROW LEVEL SECURITY;
477 SET SESSION AUTHORIZATION rls_regress_user1;
488 EXPLAIN (COSTS OFF) SELECT * FROM t1;
490 -------------------------------
493 Filter: ((a % 2) = 0)
495 Filter: ((a % 2) = 0)
497 Filter: ((a % 2) = 0)
500 SELECT * FROM t1 WHERE f_leak(b);
501 NOTICE: f_leak => bbb
502 NOTICE: f_leak => ddd
503 NOTICE: f_leak => bcd
504 NOTICE: f_leak => def
505 NOTICE: f_leak => yyy
515 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
517 -------------------------------------
521 -> Seq Scan on t1 t1_1
522 Filter: ((a % 2) = 0)
524 Filter: ((a % 2) = 0)
526 Filter: ((a % 2) = 0)
529 -- reference to system column
530 SELECT oid, * FROM t1;
540 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
542 -------------------------------
545 Filter: ((a % 2) = 0)
547 Filter: ((a % 2) = 0)
549 Filter: ((a % 2) = 0)
552 -- reference to whole-row reference
553 SELECT *, t1 FROM t1;
563 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
565 -------------------------------
568 Filter: ((a % 2) = 0)
570 Filter: ((a % 2) = 0)
572 Filter: ((a % 2) = 0)
575 -- for share/update lock
576 SELECT * FROM t1 FOR SHARE;
586 EXPLAIN (COSTS OFF) SELECT * FROM t1 FOR SHARE;
588 -------------------------------------------------------
590 -> Subquery Scan on t1
594 -> Seq Scan on t1 t1_1
595 Filter: ((a % 2) = 0)
597 Filter: ((a % 2) = 0)
599 Filter: ((a % 2) = 0)
602 SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
603 NOTICE: f_leak => bbb
604 NOTICE: f_leak => ddd
605 NOTICE: f_leak => bcd
606 NOTICE: f_leak => def
607 NOTICE: f_leak => yyy
617 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
619 -------------------------------------------------------
621 -> Subquery Scan on t1
626 -> Seq Scan on t1 t1_1
627 Filter: ((a % 2) = 0)
629 Filter: ((a % 2) = 0)
631 Filter: ((a % 2) = 0)
634 -- superuser is allowed to bypass RLS checks
635 RESET SESSION AUTHORIZATION;
636 SET row_security TO OFF;
637 SELECT * FROM t1 WHERE f_leak(b);
638 NOTICE: f_leak => aaa
639 NOTICE: f_leak => bbb
640 NOTICE: f_leak => ccc
641 NOTICE: f_leak => ddd
642 NOTICE: f_leak => abc
643 NOTICE: f_leak => bcd
644 NOTICE: f_leak => cde
645 NOTICE: f_leak => def
646 NOTICE: f_leak => xxx
647 NOTICE: f_leak => yyy
648 NOTICE: f_leak => zzz
664 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
666 ---------------------------
676 -- non-superuser with bypass privilege can bypass RLS policy when disabled
677 SET SESSION AUTHORIZATION rls_regress_exempt_user;
678 SET row_security TO OFF;
679 SELECT * FROM t1 WHERE f_leak(b);
680 NOTICE: f_leak => aaa
681 NOTICE: f_leak => bbb
682 NOTICE: f_leak => ccc
683 NOTICE: f_leak => ddd
684 NOTICE: f_leak => abc
685 NOTICE: f_leak => bcd
686 NOTICE: f_leak => cde
687 NOTICE: f_leak => def
688 NOTICE: f_leak => xxx
689 NOTICE: f_leak => yyy
690 NOTICE: f_leak => zzz
706 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
708 ---------------------------
718 ----- Dependencies -----
719 SET SESSION AUTHORIZATION rls_regress_user0;
720 SET row_security TO ON;
721 CREATE TABLE dependee (x integer, y integer);
722 CREATE TABLE dependent (x integer, y integer);
723 CREATE POLICY d1 ON dependent FOR ALL
725 USING (x = (SELECT d.x FROM dependee d WHERE d.y = y));
726 DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row security qual?
727 ERROR: cannot drop table dependee because other objects depend on it
728 DETAIL: policy d1 on table dependent depends on table dependee
729 HINT: Use DROP ... CASCADE to drop the dependent objects too.
730 DROP TABLE dependee CASCADE;
731 NOTICE: drop cascades to policy d1 on table dependent
732 EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified
734 -----------------------
735 Seq Scan on dependent
742 SET SESSION AUTHORIZATION rls_regress_user0;
743 CREATE TABLE rec1 (x integer, y integer);
744 CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y));
745 ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;
746 SET SESSION AUTHORIZATION rls_regress_user1;
747 SELECT * FROM rec1; -- fail, direct recursion
748 ERROR: infinite recursion detected in policy for relation "rec1"
752 SET SESSION AUTHORIZATION rls_regress_user0;
753 CREATE TABLE rec2 (a integer, b integer);
754 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y));
755 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b));
756 ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;
757 SET SESSION AUTHORIZATION rls_regress_user1;
758 SELECT * FROM rec1; -- fail, mutual recursion
759 ERROR: infinite recursion detected in policy for relation "rec1"
761 -- Mutual recursion via views
763 SET SESSION AUTHORIZATION rls_regress_user1;
764 CREATE VIEW rec1v AS SELECT * FROM rec1;
765 CREATE VIEW rec2v AS SELECT * FROM rec2;
766 SET SESSION AUTHORIZATION rls_regress_user0;
767 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
768 ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
769 SET SESSION AUTHORIZATION rls_regress_user1;
770 SELECT * FROM rec1; -- fail, mutual recursion via views
771 ERROR: infinite recursion detected in policy for relation "rec1"
773 -- Mutual recursion via .s.b views
775 SET SESSION AUTHORIZATION rls_regress_user1;
776 -- Suppress NOTICE messages when doing a cascaded drop.
777 SET client_min_messages TO 'warning';
778 DROP VIEW rec1v, rec2v CASCADE;
779 RESET client_min_messages;
780 CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
781 CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
782 SET SESSION AUTHORIZATION rls_regress_user0;
783 CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
784 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
785 SET SESSION AUTHORIZATION rls_regress_user1;
786 SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
787 ERROR: infinite recursion detected in policy for relation "rec1"
789 -- recursive RLS and VIEWs in policy
791 SET SESSION AUTHORIZATION rls_regress_user0;
792 CREATE TABLE s1 (a int, b text);
793 INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
794 CREATE TABLE s2 (x int, y text);
795 INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
796 GRANT SELECT ON s1, s2 TO rls_regress_user1;
797 CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
798 CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
799 CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1));
800 ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;
801 ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;
802 SET SESSION AUTHORIZATION rls_regress_user1;
803 CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
804 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
805 ERROR: infinite recursion detected in policy for relation "s1"
806 INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion)
807 ERROR: infinite recursion detected in policy for relation "s1"
808 SET SESSION AUTHORIZATION rls_regress_user0;
809 DROP POLICY p3 on s1;
810 ALTER POLICY p2 ON s2 USING (x % 2 = 0);
811 SET SESSION AUTHORIZATION rls_regress_user1;
812 SELECT * FROM s1 WHERE f_leak(b); -- OK
813 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
814 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
816 ---+----------------------------------
817 2 | c81e728d9d4c2f636f067f89cc14862c
818 4 | a87ff679a2f3e71d9181a67b7542122c
821 EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
823 ----------------------------------------------------------
827 Hash Cond: (s1_1.a = s2.x)
828 -> Seq Scan on s1 s1_1
832 -> Subquery Scan on s2
833 Filter: (s2.y ~~ '%2f%'::text)
834 -> Seq Scan on s2 s2_1
835 Filter: ((x % 2) = 0)
838 SET SESSION AUTHORIZATION rls_regress_user0;
839 ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy
840 SET SESSION AUTHORIZATION rls_regress_user1;
841 SELECT * FROM s1 WHERE f_leak(b); -- OK
842 NOTICE: f_leak => 0267aaf632e87a63288a08331f22c7c3
843 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
845 ----+----------------------------------
846 -4 | 0267aaf632e87a63288a08331f22c7c3
847 6 | 1679091c5a880faf6fb5e6087eb1b2dc
850 EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
852 ----------------------------------------------------------
856 Hash Cond: (s1_1.a = s2.x)
857 -> Seq Scan on s1 s1_1
861 -> Subquery Scan on s2
862 Filter: (s2.y ~~ '%af%'::text)
863 -> Seq Scan on s2 s2_1
864 Filter: ((x % 2) = 0)
867 SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
869 ----+----+----------------------------------
870 -6 | -6 | 596a3d04481816330f07e4f97510c28f
871 -4 | -4 | 0267aaf632e87a63288a08331f22c7c3
872 2 | 2 | c81e728d9d4c2f636f067f89cc14862c
875 EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
877 --------------------------------------------------------------------
879 Filter: (s2.y ~~ '%28%'::text)
880 -> Seq Scan on s2 s2_1
881 Filter: ((x % 2) = 0)
884 -> Subquery Scan on s1
885 -> Nested Loop Semi Join
886 Join Filter: (s1_1.a = s2_2.x)
887 -> Seq Scan on s1 s1_1
889 -> Subquery Scan on s2_2
890 Filter: (s2_2.y ~~ '%af%'::text)
891 -> Seq Scan on s2 s2_3
892 Filter: ((x % 2) = 0)
895 SET SESSION AUTHORIZATION rls_regress_user0;
896 ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%'));
897 SET SESSION AUTHORIZATION rls_regress_user1;
898 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
899 ERROR: infinite recursion detected in policy for relation "s1"
900 -- prepared statement with rls_regress_user0 privilege
901 PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
910 EXPLAIN (COSTS OFF) EXECUTE p1(2);
912 ----------------------------------------------
915 Filter: ((a <= 2) AND ((a % 2) = 0))
917 Filter: ((a <= 2) AND ((a % 2) = 0))
919 Filter: ((a <= 2) AND ((a % 2) = 0))
922 -- superuser is allowed to bypass RLS checks
923 RESET SESSION AUTHORIZATION;
924 SET row_security TO OFF;
925 SELECT * FROM t1 WHERE f_leak(b);
926 NOTICE: f_leak => aaa
927 NOTICE: f_leak => bbb
928 NOTICE: f_leak => ccc
929 NOTICE: f_leak => ddd
930 NOTICE: f_leak => abc
931 NOTICE: f_leak => bcd
932 NOTICE: f_leak => cde
933 NOTICE: f_leak => def
934 NOTICE: f_leak => xxx
935 NOTICE: f_leak => yyy
936 NOTICE: f_leak => zzz
952 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
954 ---------------------------
964 -- plan cache should be invalidated
976 EXPLAIN (COSTS OFF) EXECUTE p1(2);
978 --------------------------
988 PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1;
997 EXPLAIN (COSTS OFF) EXECUTE p2(2);
999 -------------------------
1009 -- also, case when privilege switch from superuser
1010 SET SESSION AUTHORIZATION rls_regress_user1;
1011 SET row_security TO ON;
1020 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1022 ---------------------------------------------
1025 Filter: ((a = 2) AND ((a % 2) = 0))
1027 Filter: ((a = 2) AND ((a % 2) = 0))
1029 Filter: ((a = 2) AND ((a % 2) = 0))
1033 -- UPDATE / DELETE and Row-level security
1035 SET SESSION AUTHORIZATION rls_regress_user1;
1036 EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
1038 -------------------------------------------
1040 -> Subquery Scan on t1
1041 Filter: f_leak(t1.b)
1043 -> Seq Scan on t1 t1_4
1044 Filter: ((a % 2) = 0)
1045 -> Subquery Scan on t1_1
1046 Filter: f_leak(t1_1.b)
1049 Filter: ((a % 2) = 0)
1050 -> Subquery Scan on t1_2
1051 Filter: f_leak(t1_2.b)
1054 Filter: ((a % 2) = 0)
1057 UPDATE t1 SET b = b || b WHERE f_leak(b);
1058 NOTICE: f_leak => bbb
1059 NOTICE: f_leak => ddd
1060 NOTICE: f_leak => bcd
1061 NOTICE: f_leak => def
1062 NOTICE: f_leak => yyy
1063 EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1065 -------------------------------------------
1067 -> Subquery Scan on t1
1068 Filter: f_leak(t1.b)
1070 -> Seq Scan on t1 t1_2
1071 Filter: ((a % 2) = 0)
1074 UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1075 NOTICE: f_leak => bbbbbb
1076 NOTICE: f_leak => dddddd
1077 -- returning clause with system column
1078 UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
1079 NOTICE: f_leak => bbbbbb_updt
1080 NOTICE: f_leak => dddddd_updt
1082 -----+---+-------------+-----------------
1083 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
1084 104 | 4 | dddddd_updt | (4,dddddd_updt)
1087 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
1088 NOTICE: f_leak => bbbbbb_updt
1089 NOTICE: f_leak => dddddd_updt
1090 NOTICE: f_leak => bcdbcd
1091 NOTICE: f_leak => defdef
1092 NOTICE: f_leak => yyyyyy
1102 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
1103 NOTICE: f_leak => bbbbbb_updt
1104 NOTICE: f_leak => dddddd_updt
1105 NOTICE: f_leak => bcdbcd
1106 NOTICE: f_leak => defdef
1107 NOTICE: f_leak => yyyyyy
1109 -----+---+-------------+-----------------
1110 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
1111 104 | 4 | dddddd_updt | (4,dddddd_updt)
1112 202 | 2 | bcdbcd | (2,bcdbcd)
1113 204 | 4 | defdef | (4,defdef)
1114 302 | 2 | yyyyyy | (2,yyyyyy)
1117 RESET SESSION AUTHORIZATION;
1118 SET row_security TO OFF;
1135 SET SESSION AUTHORIZATION rls_regress_user1;
1136 SET row_security TO ON;
1137 EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b);
1139 -------------------------------------------
1141 -> Subquery Scan on t1
1142 Filter: f_leak(t1.b)
1144 -> Seq Scan on t1 t1_2
1145 Filter: ((a % 2) = 0)
1148 EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
1150 -------------------------------------------
1152 -> Subquery Scan on t1
1153 Filter: f_leak(t1.b)
1155 -> Seq Scan on t1 t1_4
1156 Filter: ((a % 2) = 0)
1157 -> Subquery Scan on t1_1
1158 Filter: f_leak(t1_1.b)
1161 Filter: ((a % 2) = 0)
1162 -> Subquery Scan on t1_2
1163 Filter: f_leak(t1_2.b)
1166 Filter: ((a % 2) = 0)
1169 DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1;
1170 NOTICE: f_leak => bbbbbb_updt
1171 NOTICE: f_leak => dddddd_updt
1173 -----+---+-------------+-----------------
1174 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
1175 104 | 4 | dddddd_updt | (4,dddddd_updt)
1178 DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1;
1179 NOTICE: f_leak => bcdbcd
1180 NOTICE: f_leak => defdef
1181 NOTICE: f_leak => yyyyyy
1183 -----+---+--------+------------
1184 202 | 2 | bcdbcd | (2,bcdbcd)
1185 204 | 4 | defdef | (4,defdef)
1186 302 | 2 | yyyyyy | (2,yyyyyy)
1192 SET SESSION AUTHORIZATION rls_regress_user0;
1193 CREATE TABLE z1 (a int, b text);
1194 GRANT SELECT ON z1 TO rls_regress_group1, rls_regress_group2,
1195 rls_regress_user1, rls_regress_user2;
1196 INSERT INTO z1 VALUES
1201 CREATE POLICY p1 ON z1 TO rls_regress_group1 USING (a % 2 = 0);
1202 CREATE POLICY p2 ON z1 TO rls_regress_group2 USING (a % 2 = 1);
1203 ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
1204 SET SESSION AUTHORIZATION rls_regress_user1;
1205 SELECT * FROM z1 WHERE f_leak(b);
1206 NOTICE: f_leak => bbb
1207 NOTICE: f_leak => ddd
1214 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1216 -------------------------------
1218 Filter: f_leak(z1.b)
1219 -> Seq Scan on z1 z1_1
1220 Filter: ((a % 2) = 0)
1223 SET ROLE rls_regress_group1;
1224 SELECT * FROM z1 WHERE f_leak(b);
1225 NOTICE: f_leak => bbb
1226 NOTICE: f_leak => ddd
1233 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1235 -------------------------------
1237 Filter: f_leak(z1.b)
1238 -> Seq Scan on z1 z1_1
1239 Filter: ((a % 2) = 0)
1242 SET SESSION AUTHORIZATION rls_regress_user2;
1243 SELECT * FROM z1 WHERE f_leak(b);
1244 NOTICE: f_leak => aaa
1245 NOTICE: f_leak => ccc
1252 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1254 -------------------------------
1256 Filter: f_leak(z1.b)
1257 -> Seq Scan on z1 z1_1
1258 Filter: ((a % 2) = 1)
1261 SET ROLE rls_regress_group2;
1262 SELECT * FROM z1 WHERE f_leak(b);
1263 NOTICE: f_leak => aaa
1264 NOTICE: f_leak => ccc
1271 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1273 -------------------------------
1275 Filter: f_leak(z1.b)
1276 -> Seq Scan on z1 z1_1
1277 Filter: ((a % 2) = 1)
1281 -- Views should follow policy for view owner.
1283 -- View and Table owner are the same.
1284 SET SESSION AUTHORIZATION rls_regress_user0;
1285 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
1286 GRANT SELECT ON rls_view TO rls_regress_user1;
1287 -- Query as role that is not owner of view or table. Should return all records.
1288 SET SESSION AUTHORIZATION rls_regress_user1;
1289 SELECT * FROM rls_view;
1290 NOTICE: f_leak => aaa
1291 NOTICE: f_leak => bbb
1292 NOTICE: f_leak => ccc
1293 NOTICE: f_leak => ddd
1302 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1304 ---------------------
1309 -- Query as view/table owner. Should return all records.
1310 SET SESSION AUTHORIZATION rls_regress_user0;
1311 SELECT * FROM rls_view;
1312 NOTICE: f_leak => aaa
1313 NOTICE: f_leak => bbb
1314 NOTICE: f_leak => ccc
1315 NOTICE: f_leak => ddd
1324 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1326 ---------------------
1332 -- View and Table owners are different.
1333 SET SESSION AUTHORIZATION rls_regress_user1;
1334 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
1335 GRANT SELECT ON rls_view TO rls_regress_user0;
1336 -- Query as role that is not owner of view but is owner of table.
1337 -- Should return records based on view owner policies.
1338 SET SESSION AUTHORIZATION rls_regress_user0;
1339 SELECT * FROM rls_view;
1340 NOTICE: f_leak => bbb
1341 NOTICE: f_leak => ddd
1348 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1350 -------------------------------
1352 Filter: f_leak(z1.b)
1353 -> Seq Scan on z1 z1_1
1354 Filter: ((a % 2) = 0)
1357 -- Query as role that is not owner of table but is owner of view.
1358 -- Should return records based on view owner policies.
1359 SET SESSION AUTHORIZATION rls_regress_user1;
1360 SELECT * FROM rls_view;
1361 NOTICE: f_leak => bbb
1362 NOTICE: f_leak => ddd
1369 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1371 -------------------------------
1373 Filter: f_leak(z1.b)
1374 -> Seq Scan on z1 z1_1
1375 Filter: ((a % 2) = 0)
1378 -- Query as role that is not the owner of the table or view without permissions.
1379 SET SESSION AUTHORIZATION rls_regress_user2;
1380 SELECT * FROM rls_view; --fail - permission denied.
1381 ERROR: permission denied for relation rls_view
1382 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
1383 ERROR: permission denied for relation rls_view
1384 -- Query as role that is not the owner of the table or view with permissions.
1385 SET SESSION AUTHORIZATION rls_regress_user1;
1386 GRANT SELECT ON rls_view TO rls_regress_user2;
1387 SELECT * FROM rls_view;
1388 NOTICE: f_leak => bbb
1389 NOTICE: f_leak => ddd
1396 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1398 -------------------------------
1400 Filter: f_leak(z1.b)
1401 -> Seq Scan on z1 z1_1
1402 Filter: ((a % 2) = 0)
1405 SET SESSION AUTHORIZATION rls_regress_user1;
1410 SET SESSION AUTHORIZATION rls_regress_user0;
1411 CREATE TABLE x1 (a int, b text, c text);
1412 GRANT ALL ON x1 TO PUBLIC;
1413 INSERT INTO x1 VALUES
1414 (1, 'abc', 'rls_regress_user1'),
1415 (2, 'bcd', 'rls_regress_user1'),
1416 (3, 'cde', 'rls_regress_user2'),
1417 (4, 'def', 'rls_regress_user2'),
1418 (5, 'efg', 'rls_regress_user1'),
1419 (6, 'fgh', 'rls_regress_user1'),
1420 (7, 'fgh', 'rls_regress_user2'),
1421 (8, 'fgh', 'rls_regress_user2');
1422 CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
1423 CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
1424 CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1);
1425 CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0);
1426 CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
1427 ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
1428 SET SESSION AUTHORIZATION rls_regress_user1;
1429 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
1430 NOTICE: f_leak => abc
1431 NOTICE: f_leak => bcd
1432 NOTICE: f_leak => def
1433 NOTICE: f_leak => efg
1434 NOTICE: f_leak => fgh
1435 NOTICE: f_leak => fgh
1437 ---+-----+-------------------
1438 1 | abc | rls_regress_user1
1439 2 | bcd | rls_regress_user1
1440 4 | def | rls_regress_user2
1441 5 | efg | rls_regress_user1
1442 6 | fgh | rls_regress_user1
1443 8 | fgh | rls_regress_user2
1446 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
1447 NOTICE: f_leak => abc
1448 NOTICE: f_leak => bcd
1449 NOTICE: f_leak => def
1450 NOTICE: f_leak => efg
1451 NOTICE: f_leak => fgh
1452 NOTICE: f_leak => fgh
1454 ---+----------+-------------------
1455 1 | abc_updt | rls_regress_user1
1456 2 | bcd_updt | rls_regress_user1
1457 4 | def_updt | rls_regress_user2
1458 5 | efg_updt | rls_regress_user1
1459 6 | fgh_updt | rls_regress_user1
1460 8 | fgh_updt | rls_regress_user2
1463 SET SESSION AUTHORIZATION rls_regress_user2;
1464 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
1465 NOTICE: f_leak => cde
1466 NOTICE: f_leak => fgh
1467 NOTICE: f_leak => bcd_updt
1468 NOTICE: f_leak => def_updt
1469 NOTICE: f_leak => fgh_updt
1470 NOTICE: f_leak => fgh_updt
1472 ---+----------+-------------------
1473 2 | bcd_updt | rls_regress_user1
1474 3 | cde | rls_regress_user2
1475 4 | def_updt | rls_regress_user2
1476 6 | fgh_updt | rls_regress_user1
1477 7 | fgh | rls_regress_user2
1478 8 | fgh_updt | rls_regress_user2
1481 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
1482 NOTICE: f_leak => cde
1483 NOTICE: f_leak => fgh
1484 NOTICE: f_leak => bcd_updt
1485 NOTICE: f_leak => def_updt
1486 NOTICE: f_leak => fgh_updt
1487 NOTICE: f_leak => fgh_updt
1489 ---+---------------+-------------------
1490 3 | cde_updt | rls_regress_user2
1491 7 | fgh_updt | rls_regress_user2
1492 2 | bcd_updt_updt | rls_regress_user1
1493 4 | def_updt_updt | rls_regress_user2
1494 6 | fgh_updt_updt | rls_regress_user1
1495 8 | fgh_updt_updt | rls_regress_user2
1498 DELETE FROM x1 WHERE f_leak(b) RETURNING *;
1499 NOTICE: f_leak => abc_updt
1500 NOTICE: f_leak => efg_updt
1501 NOTICE: f_leak => cde_updt
1502 NOTICE: f_leak => fgh_updt
1503 NOTICE: f_leak => bcd_updt_updt
1504 NOTICE: f_leak => def_updt_updt
1505 NOTICE: f_leak => fgh_updt_updt
1506 NOTICE: f_leak => fgh_updt_updt
1508 ---+---------------+-------------------
1509 1 | abc_updt | rls_regress_user1
1510 5 | efg_updt | rls_regress_user1
1511 3 | cde_updt | rls_regress_user2
1512 7 | fgh_updt | rls_regress_user2
1513 2 | bcd_updt_updt | rls_regress_user1
1514 4 | def_updt_updt | rls_regress_user2
1515 6 | fgh_updt_updt | rls_regress_user1
1516 8 | fgh_updt_updt | rls_regress_user2
1520 -- Duplicate Policy Names
1522 SET SESSION AUTHORIZATION rls_regress_user0;
1523 CREATE TABLE y1 (a int, b text);
1524 CREATE TABLE y2 (a int, b text);
1525 GRANT ALL ON y1, y2 TO rls_regress_user1;
1526 CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
1527 CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
1528 CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
1529 ERROR: policy "p1" for relation "y1" already exists
1530 CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK
1531 ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;
1532 ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
1534 -- Expression structure with SBV
1536 -- Create view as table owner. RLS should NOT be applied.
1537 SET SESSION AUTHORIZATION rls_regress_user0;
1538 CREATE VIEW rls_sbv WITH (security_barrier) AS
1539 SELECT * FROM y1 WHERE f_leak(b);
1540 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
1542 -----------------------------------
1544 Filter: (f_leak(b) AND (a = 1))
1548 -- Create view as role that does not own table. RLS should be applied.
1549 SET SESSION AUTHORIZATION rls_regress_user1;
1550 CREATE VIEW rls_sbv WITH (security_barrier) AS
1551 SELECT * FROM y1 WHERE f_leak(b);
1552 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
1554 ----------------------------------------------------------
1556 Filter: f_leak(y1.b)
1557 -> Seq Scan on y1 y1_1
1558 Filter: ((a = 1) AND ((a > 2) OR ((a % 2) = 0)))
1563 -- Expression structure
1565 SET SESSION AUTHORIZATION rls_regress_user0;
1566 INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
1567 CREATE POLICY p2 ON y2 USING (a % 3 = 0);
1568 CREATE POLICY p3 ON y2 USING (a % 4 = 0);
1569 SET SESSION AUTHORIZATION rls_regress_user1;
1570 SELECT * FROM y2 WHERE f_leak(b);
1571 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
1572 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
1573 NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
1574 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1575 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1576 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
1577 NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
1578 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
1579 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
1580 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
1581 NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
1582 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
1583 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
1584 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
1586 ----+----------------------------------
1587 0 | cfcd208495d565ef66e7dff9f98764da
1588 2 | c81e728d9d4c2f636f067f89cc14862c
1589 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
1590 4 | a87ff679a2f3e71d9181a67b7542122c
1591 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1592 8 | c9f0f895fb98ab9159f51fd0297e236d
1593 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
1594 10 | d3d9446802a44259755d38e6d163e820
1595 12 | c20ad4d76fe97759aa27a0c99bff6710
1596 14 | aab3238922bcc25a6f606eb525ffdc56
1597 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
1598 16 | c74d97b01eae257e44aa9d5bade97baf
1599 18 | 6f4922f45568161a8cdf4ad2299f6d23
1600 20 | 98f13708210194c475687be6106a3b84
1603 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
1605 -------------------------------------------------------------------
1607 Filter: f_leak(y2.b)
1608 -> Seq Scan on y2 y2_1
1609 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
1613 -- Plancache invalidate on user change.
1615 RESET SESSION AUTHORIZATION;
1616 -- Suppress NOTICE messages when doing a cascaded drop.
1617 SET client_min_messages TO 'warning';
1618 DROP TABLE t1 CASCADE;
1619 RESET client_min_messages;
1620 CREATE TABLE t1 (a integer);
1621 GRANT SELECT ON t1 TO rls_regress_user1, rls_regress_user2;
1622 CREATE POLICY p1 ON t1 TO rls_regress_user1 USING ((a % 2) = 0);
1623 CREATE POLICY p2 ON t1 TO rls_regress_user2 USING ((a % 4) = 0);
1624 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
1625 SET ROLE rls_regress_user1;
1626 PREPARE role_inval AS SELECT * FROM t1;
1627 EXPLAIN (COSTS OFF) EXECUTE role_inval;
1629 -------------------------
1631 Filter: ((a % 2) = 0)
1634 SET ROLE rls_regress_user2;
1635 EXPLAIN (COSTS OFF) EXECUTE role_inval;
1637 -------------------------
1639 Filter: ((a % 4) = 0)
1645 RESET SESSION AUTHORIZATION;
1646 DROP TABLE t1 CASCADE;
1647 CREATE TABLE t1 (a integer, b text);
1648 CREATE POLICY p1 ON t1 USING (a % 2 = 0);
1649 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
1650 GRANT ALL ON t1 TO rls_regress_user1;
1651 INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
1652 SET SESSION AUTHORIZATION rls_regress_user1;
1653 WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
1654 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
1655 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
1656 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1657 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1658 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
1659 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
1660 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
1661 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
1662 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
1663 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
1664 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
1666 ----+----------------------------------
1667 0 | cfcd208495d565ef66e7dff9f98764da
1668 2 | c81e728d9d4c2f636f067f89cc14862c
1669 4 | a87ff679a2f3e71d9181a67b7542122c
1670 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1671 8 | c9f0f895fb98ab9159f51fd0297e236d
1672 10 | d3d9446802a44259755d38e6d163e820
1673 12 | c20ad4d76fe97759aa27a0c99bff6710
1674 14 | aab3238922bcc25a6f606eb525ffdc56
1675 16 | c74d97b01eae257e44aa9d5bade97baf
1676 18 | 6f4922f45568161a8cdf4ad2299f6d23
1677 20 | 98f13708210194c475687be6106a3b84
1680 EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
1682 ---------------------------------------
1685 -> Subquery Scan on t1
1686 Filter: f_leak(t1.b)
1687 -> Seq Scan on t1 t1_1
1688 Filter: ((a % 2) = 0)
1691 WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
1692 ERROR: new row violates WITH CHECK OPTION for "t1"
1693 WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
1695 ----+----------------------------------
1696 0 | cfcd208495d565ef66e7dff9f98764da
1697 2 | c81e728d9d4c2f636f067f89cc14862c
1698 4 | a87ff679a2f3e71d9181a67b7542122c
1699 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1700 8 | c9f0f895fb98ab9159f51fd0297e236d
1701 10 | d3d9446802a44259755d38e6d163e820
1702 12 | c20ad4d76fe97759aa27a0c99bff6710
1703 14 | aab3238922bcc25a6f606eb525ffdc56
1704 16 | c74d97b01eae257e44aa9d5bade97baf
1705 18 | 6f4922f45568161a8cdf4ad2299f6d23
1706 20 | 98f13708210194c475687be6106a3b84
1709 WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail
1710 ERROR: new row violates WITH CHECK OPTION for "t1"
1711 WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok
1720 RESET SESSION AUTHORIZATION;
1721 ALTER POLICY p1 ON t1 RENAME TO p1; --fail
1722 ERROR: policy "p1" for table "t1" already exists
1723 SELECT polname, relname
1725 JOIN pg_class pc ON (pc.oid = pol.polrelid)
1726 WHERE relname = 't1';
1732 ALTER POLICY p1 ON t1 RENAME TO p2; --ok
1733 SELECT polname, relname
1735 JOIN pg_class pc ON (pc.oid = pol.polrelid)
1736 WHERE relname = 't1';
1743 -- Check INSERT SELECT
1745 SET SESSION AUTHORIZATION rls_regress_user1;
1746 CREATE TABLE t2 (a integer, b text);
1747 INSERT INTO t2 (SELECT * FROM t1);
1748 EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
1750 -------------------------------
1753 Filter: ((a % 2) = 0)
1758 ----+----------------------------------
1759 0 | cfcd208495d565ef66e7dff9f98764da
1760 2 | c81e728d9d4c2f636f067f89cc14862c
1761 4 | a87ff679a2f3e71d9181a67b7542122c
1762 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1763 8 | c9f0f895fb98ab9159f51fd0297e236d
1764 10 | d3d9446802a44259755d38e6d163e820
1765 12 | c20ad4d76fe97759aa27a0c99bff6710
1766 14 | aab3238922bcc25a6f606eb525ffdc56
1767 16 | c74d97b01eae257e44aa9d5bade97baf
1768 18 | 6f4922f45568161a8cdf4ad2299f6d23
1769 20 | 98f13708210194c475687be6106a3b84
1773 EXPLAIN (COSTS OFF) SELECT * FROM t2;
1779 CREATE TABLE t3 AS SELECT * FROM t1;
1782 ----+----------------------------------
1783 0 | cfcd208495d565ef66e7dff9f98764da
1784 2 | c81e728d9d4c2f636f067f89cc14862c
1785 4 | a87ff679a2f3e71d9181a67b7542122c
1786 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1787 8 | c9f0f895fb98ab9159f51fd0297e236d
1788 10 | d3d9446802a44259755d38e6d163e820
1789 12 | c20ad4d76fe97759aa27a0c99bff6710
1790 14 | aab3238922bcc25a6f606eb525ffdc56
1791 16 | c74d97b01eae257e44aa9d5bade97baf
1792 18 | 6f4922f45568161a8cdf4ad2299f6d23
1793 20 | 98f13708210194c475687be6106a3b84
1797 SELECT * INTO t4 FROM t1;
1800 ----+----------------------------------
1801 0 | cfcd208495d565ef66e7dff9f98764da
1802 2 | c81e728d9d4c2f636f067f89cc14862c
1803 4 | a87ff679a2f3e71d9181a67b7542122c
1804 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1805 8 | c9f0f895fb98ab9159f51fd0297e236d
1806 10 | d3d9446802a44259755d38e6d163e820
1807 12 | c20ad4d76fe97759aa27a0c99bff6710
1808 14 | aab3238922bcc25a6f606eb525ffdc56
1809 16 | c74d97b01eae257e44aa9d5bade97baf
1810 18 | 6f4922f45568161a8cdf4ad2299f6d23
1811 20 | 98f13708210194c475687be6106a3b84
1818 SET SESSION AUTHORIZATION rls_regress_user0;
1819 CREATE TABLE blog (id integer, author text, post text);
1820 CREATE TABLE comment (blog_id integer, message text);
1821 GRANT ALL ON blog, comment TO rls_regress_user1;
1822 CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
1823 ALTER TABLE blog ENABLE ROW LEVEL SECURITY;
1824 INSERT INTO blog VALUES
1825 (1, 'alice', 'blog #1'),
1826 (2, 'bob', 'blog #1'),
1827 (3, 'alice', 'blog #2'),
1828 (4, 'alice', 'blog #3'),
1829 (5, 'john', 'blog #1');
1830 INSERT INTO comment VALUES
1837 SET SESSION AUTHORIZATION rls_regress_user1;
1838 -- Check RLS JOIN with Non-RLS.
1839 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
1840 id | author | message
1841 ----+--------+-------------
1843 2 | bob | who did it?
1846 -- Check Non-RLS JOIN with RLS.
1847 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
1848 id | author | message
1849 ----+--------+-------------
1851 2 | bob | who did it?
1854 SET SESSION AUTHORIZATION rls_regress_user0;
1855 CREATE POLICY comment_1 ON comment USING (blog_id < 4);
1856 ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
1857 SET SESSION AUTHORIZATION rls_regress_user1;
1858 -- Check RLS JOIN RLS
1859 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
1860 id | author | message
1861 ----+--------+-------------
1862 2 | bob | who did it?
1865 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
1866 id | author | message
1867 ----+--------+-------------
1868 2 | bob | who did it?
1871 SET SESSION AUTHORIZATION rls_regress_user0;
1872 DROP TABLE blog, comment;
1874 -- Default Deny Policy
1876 RESET SESSION AUTHORIZATION;
1877 DROP POLICY p2 ON t1;
1878 ALTER TABLE t1 OWNER TO rls_regress_user0;
1879 -- Check that default deny does not apply to superuser.
1880 RESET SESSION AUTHORIZATION;
1883 ----+----------------------------------
1884 1 | c4ca4238a0b923820dcc509a6f75849b
1885 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
1886 5 | e4da3b7fbbce2345d7772b0674a318d5
1887 7 | 8f14e45fceea167a5a36dedd4bea2543
1888 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
1889 11 | 6512bd43d9caa6e02c990b0a82652dca
1890 13 | c51ce410c124a10e0db5e4b97fc2af39
1891 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
1892 17 | 70efdf2ec9b086079795c442636b55fb
1893 19 | 1f0e3dad99908345f7439f8ffabdffc4
1894 0 | cfcd208495d565ef66e7dff9f98764da
1895 2 | c81e728d9d4c2f636f067f89cc14862c
1896 4 | a87ff679a2f3e71d9181a67b7542122c
1897 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1898 8 | c9f0f895fb98ab9159f51fd0297e236d
1899 10 | d3d9446802a44259755d38e6d163e820
1900 12 | c20ad4d76fe97759aa27a0c99bff6710
1901 14 | aab3238922bcc25a6f606eb525ffdc56
1902 16 | c74d97b01eae257e44aa9d5bade97baf
1903 18 | 6f4922f45568161a8cdf4ad2299f6d23
1904 20 | 98f13708210194c475687be6106a3b84
1908 EXPLAIN (COSTS OFF) SELECT * FROM t1;
1914 -- Check that default deny does not apply to table owner.
1915 SET SESSION AUTHORIZATION rls_regress_user0;
1918 ----+----------------------------------
1919 1 | c4ca4238a0b923820dcc509a6f75849b
1920 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
1921 5 | e4da3b7fbbce2345d7772b0674a318d5
1922 7 | 8f14e45fceea167a5a36dedd4bea2543
1923 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
1924 11 | 6512bd43d9caa6e02c990b0a82652dca
1925 13 | c51ce410c124a10e0db5e4b97fc2af39
1926 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
1927 17 | 70efdf2ec9b086079795c442636b55fb
1928 19 | 1f0e3dad99908345f7439f8ffabdffc4
1929 0 | cfcd208495d565ef66e7dff9f98764da
1930 2 | c81e728d9d4c2f636f067f89cc14862c
1931 4 | a87ff679a2f3e71d9181a67b7542122c
1932 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1933 8 | c9f0f895fb98ab9159f51fd0297e236d
1934 10 | d3d9446802a44259755d38e6d163e820
1935 12 | c20ad4d76fe97759aa27a0c99bff6710
1936 14 | aab3238922bcc25a6f606eb525ffdc56
1937 16 | c74d97b01eae257e44aa9d5bade97baf
1938 18 | 6f4922f45568161a8cdf4ad2299f6d23
1939 20 | 98f13708210194c475687be6106a3b84
1943 EXPLAIN (COSTS OFF) SELECT * FROM t1;
1949 -- Check that default deny does apply to superuser when RLS force.
1950 SET row_security TO FORCE;
1951 RESET SESSION AUTHORIZATION;
1957 EXPLAIN (COSTS OFF) SELECT * FROM t1;
1959 --------------------------
1961 One-Time Filter: false
1964 -- Check that default deny does apply to table owner when RLS force.
1965 SET SESSION AUTHORIZATION rls_regress_user0;
1971 EXPLAIN (COSTS OFF) SELECT * FROM t1;
1973 --------------------------
1975 One-Time Filter: false
1978 -- Check that default deny applies to non-owner/non-superuser when RLS on.
1979 SET SESSION AUTHORIZATION rls_regress_user1;
1980 SET row_security TO ON;
1986 EXPLAIN (COSTS OFF) SELECT * FROM t1;
1988 --------------------------
1990 One-Time Filter: false
1993 SET SESSION AUTHORIZATION rls_regress_user1;
1999 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2001 --------------------------
2003 One-Time Filter: false
2009 RESET SESSION AUTHORIZATION;
2010 DROP TABLE copy_t CASCADE;
2011 ERROR: table "copy_t" does not exist
2012 CREATE TABLE copy_t (a integer, b text);
2013 CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
2014 ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
2015 GRANT ALL ON copy_t TO rls_regress_user1, rls_regress_exempt_user;
2016 INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
2017 -- Check COPY TO as Superuser/owner.
2018 RESET SESSION AUTHORIZATION;
2019 SET row_security TO OFF;
2020 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
2021 0,cfcd208495d565ef66e7dff9f98764da
2022 1,c4ca4238a0b923820dcc509a6f75849b
2023 2,c81e728d9d4c2f636f067f89cc14862c
2024 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
2025 4,a87ff679a2f3e71d9181a67b7542122c
2026 5,e4da3b7fbbce2345d7772b0674a318d5
2027 6,1679091c5a880faf6fb5e6087eb1b2dc
2028 7,8f14e45fceea167a5a36dedd4bea2543
2029 8,c9f0f895fb98ab9159f51fd0297e236d
2030 9,45c48cce2e2d7fbdea1afc51c7c6ad26
2031 10,d3d9446802a44259755d38e6d163e820
2032 SET row_security TO ON;
2033 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
2034 0,cfcd208495d565ef66e7dff9f98764da
2035 1,c4ca4238a0b923820dcc509a6f75849b
2036 2,c81e728d9d4c2f636f067f89cc14862c
2037 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
2038 4,a87ff679a2f3e71d9181a67b7542122c
2039 5,e4da3b7fbbce2345d7772b0674a318d5
2040 6,1679091c5a880faf6fb5e6087eb1b2dc
2041 7,8f14e45fceea167a5a36dedd4bea2543
2042 8,c9f0f895fb98ab9159f51fd0297e236d
2043 9,45c48cce2e2d7fbdea1afc51c7c6ad26
2044 10,d3d9446802a44259755d38e6d163e820
2045 SET row_security TO FORCE;
2046 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
2047 0,cfcd208495d565ef66e7dff9f98764da
2048 2,c81e728d9d4c2f636f067f89cc14862c
2049 4,a87ff679a2f3e71d9181a67b7542122c
2050 6,1679091c5a880faf6fb5e6087eb1b2dc
2051 8,c9f0f895fb98ab9159f51fd0297e236d
2052 10,d3d9446802a44259755d38e6d163e820
2053 -- Check COPY TO as user with permissions.
2054 SET SESSION AUTHORIZATION rls_regress_user1;
2055 SET row_security TO OFF;
2056 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
2057 ERROR: insufficient privilege to bypass row security.
2058 SET row_security TO ON;
2059 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2060 0,cfcd208495d565ef66e7dff9f98764da
2061 2,c81e728d9d4c2f636f067f89cc14862c
2062 4,a87ff679a2f3e71d9181a67b7542122c
2063 6,1679091c5a880faf6fb5e6087eb1b2dc
2064 8,c9f0f895fb98ab9159f51fd0297e236d
2065 10,d3d9446802a44259755d38e6d163e820
2066 SET row_security TO FORCE;
2067 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2068 0,cfcd208495d565ef66e7dff9f98764da
2069 2,c81e728d9d4c2f636f067f89cc14862c
2070 4,a87ff679a2f3e71d9181a67b7542122c
2071 6,1679091c5a880faf6fb5e6087eb1b2dc
2072 8,c9f0f895fb98ab9159f51fd0297e236d
2073 10,d3d9446802a44259755d38e6d163e820
2074 -- Check COPY TO as user with permissions and BYPASSRLS
2075 SET SESSION AUTHORIZATION rls_regress_exempt_user;
2076 SET row_security TO OFF;
2077 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2078 0,cfcd208495d565ef66e7dff9f98764da
2079 1,c4ca4238a0b923820dcc509a6f75849b
2080 2,c81e728d9d4c2f636f067f89cc14862c
2081 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
2082 4,a87ff679a2f3e71d9181a67b7542122c
2083 5,e4da3b7fbbce2345d7772b0674a318d5
2084 6,1679091c5a880faf6fb5e6087eb1b2dc
2085 7,8f14e45fceea167a5a36dedd4bea2543
2086 8,c9f0f895fb98ab9159f51fd0297e236d
2087 9,45c48cce2e2d7fbdea1afc51c7c6ad26
2088 10,d3d9446802a44259755d38e6d163e820
2089 SET row_security TO ON;
2090 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2091 0,cfcd208495d565ef66e7dff9f98764da
2092 2,c81e728d9d4c2f636f067f89cc14862c
2093 4,a87ff679a2f3e71d9181a67b7542122c
2094 6,1679091c5a880faf6fb5e6087eb1b2dc
2095 8,c9f0f895fb98ab9159f51fd0297e236d
2096 10,d3d9446802a44259755d38e6d163e820
2097 SET row_security TO FORCE;
2098 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2099 0,cfcd208495d565ef66e7dff9f98764da
2100 2,c81e728d9d4c2f636f067f89cc14862c
2101 4,a87ff679a2f3e71d9181a67b7542122c
2102 6,1679091c5a880faf6fb5e6087eb1b2dc
2103 8,c9f0f895fb98ab9159f51fd0297e236d
2104 10,d3d9446802a44259755d38e6d163e820
2105 -- Check COPY TO as user without permissions.SET row_security TO OFF;
2106 SET SESSION AUTHORIZATION rls_regress_user2;
2107 SET row_security TO OFF;
2108 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
2109 ERROR: insufficient privilege to bypass row security.
2110 SET row_security TO ON;
2111 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
2112 ERROR: permission denied for relation copy_t
2113 SET row_security TO FORCE;
2114 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
2115 ERROR: permission denied for relation copy_t
2116 -- Check COPY FROM as Superuser/owner.
2117 RESET SESSION AUTHORIZATION;
2118 SET row_security TO OFF;
2119 COPY copy_t FROM STDIN; --ok
2120 SET row_security TO ON;
2121 COPY copy_t FROM STDIN; --ok
2122 SET row_security TO FORCE;
2123 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2124 ERROR: COPY FROM not supported with row level security.
2125 HINT: Use direct INSERT statements instead.
2126 -- Check COPY FROM as user with permissions.
2127 SET SESSION AUTHORIZATION rls_regress_user1;
2128 SET row_security TO OFF;
2129 COPY copy_t FROM STDIN; --fail - insufficient privilege to bypass rls.
2130 ERROR: insufficient privilege to bypass row security.
2131 SET row_security TO ON;
2132 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2133 ERROR: COPY FROM not supported with row level security.
2134 HINT: Use direct INSERT statements instead.
2135 SET row_security TO FORCE;
2136 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2137 ERROR: COPY FROM not supported with row level security.
2138 HINT: Use direct INSERT statements instead.
2139 -- Check COPY TO as user with permissions and BYPASSRLS
2140 SET SESSION AUTHORIZATION rls_regress_exempt_user;
2141 SET row_security TO OFF;
2142 COPY copy_t FROM STDIN; --ok
2143 SET row_security TO ON;
2144 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2145 ERROR: COPY FROM not supported with row level security.
2146 HINT: Use direct INSERT statements instead.
2147 SET row_security TO FORCE;
2148 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2149 ERROR: COPY FROM not supported with row level security.
2150 HINT: Use direct INSERT statements instead.
2151 -- Check COPY FROM as user without permissions.
2152 SET SESSION AUTHORIZATION rls_regress_user2;
2153 SET row_security TO OFF;
2154 COPY copy_t FROM STDIN; --fail - permission denied.
2155 ERROR: permission denied for relation copy_t
2156 SET row_security TO ON;
2157 COPY copy_t FROM STDIN; --fail - permission denied.
2158 ERROR: permission denied for relation copy_t
2159 SET row_security TO FORCE;
2160 COPY copy_t FROM STDIN; --fail - permission denied.
2161 ERROR: permission denied for relation copy_t
2162 RESET SESSION AUTHORIZATION;
2167 RESET SESSION AUTHORIZATION;
2168 -- Suppress NOTICE messages when doing a cascaded drop.
2169 SET client_min_messages TO 'warning';
2170 DROP SCHEMA rls_regress_schema CASCADE;
2171 RESET client_min_messages;
2172 DROP USER rls_regress_user0;
2173 DROP USER rls_regress_user1;
2174 DROP USER rls_regress_user2;
2175 DROP USER rls_regress_exempt_user;
2176 DROP ROLE rls_regress_group1;
2177 DROP ROLE rls_regress_group2;