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 -- try a sampled version
105 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
106 WHERE f_leak(dtitle) ORDER BY did;
107 NOTICE: f_leak => my first manga
108 NOTICE: f_leak => great science fiction
109 NOTICE: f_leak => great manga
110 did | cid | dlevel | dauthor | dtitle
111 -----+-----+--------+-------------------+-----------------------
112 4 | 44 | 1 | rls_regress_user1 | my first manga
113 6 | 22 | 1 | rls_regress_user2 | great science fiction
114 8 | 44 | 1 | rls_regress_user2 | great manga
117 -- viewpoint from rls_regress_user2
118 SET SESSION AUTHORIZATION rls_regress_user2;
119 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
120 NOTICE: f_leak => my first novel
121 NOTICE: f_leak => my second novel
122 NOTICE: f_leak => my science fiction
123 NOTICE: f_leak => my first manga
124 NOTICE: f_leak => my second manga
125 NOTICE: f_leak => great science fiction
126 NOTICE: f_leak => great technology book
127 NOTICE: f_leak => great manga
128 did | cid | dlevel | dauthor | dtitle
129 -----+-----+--------+-------------------+-----------------------
130 1 | 11 | 1 | rls_regress_user1 | my first novel
131 2 | 11 | 2 | rls_regress_user1 | my second novel
132 3 | 22 | 2 | rls_regress_user1 | my science fiction
133 4 | 44 | 1 | rls_regress_user1 | my first manga
134 5 | 44 | 2 | rls_regress_user1 | my second manga
135 6 | 22 | 1 | rls_regress_user2 | great science fiction
136 7 | 33 | 2 | rls_regress_user2 | great technology book
137 8 | 44 | 1 | rls_regress_user2 | great manga
140 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
141 NOTICE: f_leak => my first novel
142 NOTICE: f_leak => my second novel
143 NOTICE: f_leak => my science fiction
144 NOTICE: f_leak => my first manga
145 NOTICE: f_leak => my second manga
146 NOTICE: f_leak => great science fiction
147 NOTICE: f_leak => great technology book
148 NOTICE: f_leak => great manga
149 cid | did | dlevel | dauthor | dtitle | cname
150 -----+-----+--------+-------------------+-----------------------+-----------------
151 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
152 11 | 2 | 2 | rls_regress_user1 | my second novel | novel
153 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
154 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
155 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
156 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
157 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
158 44 | 8 | 1 | rls_regress_user2 | great manga | manga
161 -- try a sampled version
162 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
163 WHERE f_leak(dtitle) ORDER BY did;
164 NOTICE: f_leak => my first manga
165 NOTICE: f_leak => my second manga
166 NOTICE: f_leak => great science fiction
167 NOTICE: f_leak => great manga
168 did | cid | dlevel | dauthor | dtitle
169 -----+-----+--------+-------------------+-----------------------
170 4 | 44 | 1 | rls_regress_user1 | my first manga
171 5 | 44 | 2 | rls_regress_user1 | my second manga
172 6 | 22 | 1 | rls_regress_user2 | great science fiction
173 8 | 44 | 1 | rls_regress_user2 | great manga
176 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
178 ----------------------------------------------------------
179 Subquery Scan on document
180 Filter: f_leak(document.dtitle)
181 -> Seq Scan on document document_1
182 Filter: (dlevel <= $0)
183 InitPlan 1 (returns $0)
184 -> Index Scan using uaccount_pkey on uaccount
185 Index Cond: (pguser = "current_user"())
188 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
190 ----------------------------------------------------------------------
192 Hash Cond: (category.cid = document.cid)
193 -> Seq Scan on category
195 -> Subquery Scan on document
196 Filter: f_leak(document.dtitle)
197 -> Seq Scan on document document_1
198 Filter: (dlevel <= $0)
199 InitPlan 1 (returns $0)
200 -> Index Scan using uaccount_pkey on uaccount
201 Index Cond: (pguser = "current_user"())
204 -- only owner can change policies
205 ALTER POLICY p1 ON document USING (true); --fail
206 ERROR: must be owner of relation document
207 DROP POLICY p1 ON document; --fail
208 ERROR: must be owner of relation document
209 SET SESSION AUTHORIZATION rls_regress_user0;
210 ALTER POLICY p1 ON document USING (dauthor = current_user);
211 -- viewpoint from rls_regress_user1 again
212 SET SESSION AUTHORIZATION rls_regress_user1;
213 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
214 NOTICE: f_leak => my first novel
215 NOTICE: f_leak => my second novel
216 NOTICE: f_leak => my science fiction
217 NOTICE: f_leak => my first manga
218 NOTICE: f_leak => my second manga
219 did | cid | dlevel | dauthor | dtitle
220 -----+-----+--------+-------------------+--------------------
221 1 | 11 | 1 | rls_regress_user1 | my first novel
222 2 | 11 | 2 | rls_regress_user1 | my second novel
223 3 | 22 | 2 | rls_regress_user1 | my science fiction
224 4 | 44 | 1 | rls_regress_user1 | my first manga
225 5 | 44 | 2 | rls_regress_user1 | my second manga
228 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
229 NOTICE: f_leak => my first novel
230 NOTICE: f_leak => my second novel
231 NOTICE: f_leak => my science fiction
232 NOTICE: f_leak => my first manga
233 NOTICE: f_leak => my second manga
234 cid | did | dlevel | dauthor | dtitle | cname
235 -----+-----+--------+-------------------+--------------------+-----------------
236 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
237 11 | 2 | 2 | rls_regress_user1 | my second novel | novel
238 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
239 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
240 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
243 -- viewpoint from rls_regres_user2 again
244 SET SESSION AUTHORIZATION rls_regress_user2;
245 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
246 NOTICE: f_leak => great science fiction
247 NOTICE: f_leak => great technology book
248 NOTICE: f_leak => great manga
249 did | cid | dlevel | dauthor | dtitle
250 -----+-----+--------+-------------------+-----------------------
251 6 | 22 | 1 | rls_regress_user2 | great science fiction
252 7 | 33 | 2 | rls_regress_user2 | great technology book
253 8 | 44 | 1 | rls_regress_user2 | great manga
256 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
257 NOTICE: f_leak => great science fiction
258 NOTICE: f_leak => great technology book
259 NOTICE: f_leak => great manga
260 cid | did | dlevel | dauthor | dtitle | cname
261 -----+-----+--------+-------------------+-----------------------+-----------------
262 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
263 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
264 44 | 8 | 1 | rls_regress_user2 | great manga | manga
267 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
269 ----------------------------------------------
270 Subquery Scan on document
271 Filter: f_leak(document.dtitle)
272 -> Seq Scan on document document_1
273 Filter: (dauthor = "current_user"())
276 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
278 ----------------------------------------------------
280 -> Subquery Scan on document
281 Filter: f_leak(document.dtitle)
282 -> Seq Scan on document document_1
283 Filter: (dauthor = "current_user"())
284 -> Index Scan using category_pkey on category
285 Index Cond: (cid = document.cid)
288 -- interaction of FK/PK constraints
289 SET SESSION AUTHORIZATION rls_regress_user0;
290 CREATE POLICY p2 ON category
291 USING (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33)
292 WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44)
294 ALTER TABLE category ENABLE ROW LEVEL SECURITY;
295 -- cannot delete PK referenced by invisible FK
296 SET SESSION AUTHORIZATION rls_regress_user1;
297 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
298 did | cid | dlevel | dauthor | dtitle | cid | cname
299 -----+-----+--------+-------------------+--------------------+-----+------------
300 2 | 11 | 2 | rls_regress_user1 | my second novel | 11 | novel
301 1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel
302 | | | | | 33 | technology
303 5 | 44 | 2 | rls_regress_user1 | my second manga | |
304 4 | 44 | 1 | rls_regress_user1 | my first manga | |
305 3 | 22 | 2 | rls_regress_user1 | my science fiction | |
308 DELETE FROM category WHERE cid = 33; -- fails with FK violation
309 ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"
310 DETAIL: Key (cid)=(33) is still referenced from table "document".
311 -- can insert FK referencing invisible PK
312 SET SESSION AUTHORIZATION rls_regress_user2;
313 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
314 did | cid | dlevel | dauthor | dtitle | cid | cname
315 -----+-----+--------+-------------------+-----------------------+-----+-----------------
316 6 | 22 | 1 | rls_regress_user2 | great science fiction | 22 | science fiction
317 8 | 44 | 1 | rls_regress_user2 | great manga | 44 | manga
318 7 | 33 | 2 | rls_regress_user2 | great technology book | |
321 INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');
322 -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
323 SET SESSION AUTHORIZATION rls_regress_user1;
324 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
325 ERROR: duplicate key value violates unique constraint "document_pkey"
326 SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
327 did | cid | dlevel | dauthor | dtitle
328 -----+-----+--------+---------+--------
331 -- RLS policies are checked before constraints
332 INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user2', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation
333 ERROR: new row violates row level security policy for "document"
334 UPDATE document SET did = 8, dauthor = 'rls_regress_user2' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation
335 ERROR: new row violates row level security policy for "document"
336 -- database superuser does bypass RLS policy when enabled
337 RESET SESSION AUTHORIZATION;
338 SET row_security TO ON;
339 SELECT * FROM document;
340 did | cid | dlevel | dauthor | dtitle
341 -----+-----+--------+-------------------+-----------------------
342 1 | 11 | 1 | rls_regress_user1 | my first novel
343 2 | 11 | 2 | rls_regress_user1 | my second novel
344 3 | 22 | 2 | rls_regress_user1 | my science fiction
345 4 | 44 | 1 | rls_regress_user1 | my first manga
346 5 | 44 | 2 | rls_regress_user1 | my second manga
347 6 | 22 | 1 | rls_regress_user2 | great science fiction
348 7 | 33 | 2 | rls_regress_user2 | great technology book
349 8 | 44 | 1 | rls_regress_user2 | great manga
350 10 | 33 | 1 | rls_regress_user2 | hoge
353 SELECT * FROM category;
355 -----+-----------------
362 -- database superuser does not bypass RLS policy when FORCE enabled.
363 RESET SESSION AUTHORIZATION;
364 SET row_security TO FORCE;
365 SELECT * FROM document;
366 did | cid | dlevel | dauthor | dtitle
367 -----+-----+--------+---------+--------
370 SELECT * FROM category;
375 -- database superuser does bypass RLS policy when disabled
376 RESET SESSION AUTHORIZATION;
377 SET row_security TO OFF;
378 SELECT * FROM document;
379 did | cid | dlevel | dauthor | dtitle
380 -----+-----+--------+-------------------+-----------------------
381 1 | 11 | 1 | rls_regress_user1 | my first novel
382 2 | 11 | 2 | rls_regress_user1 | my second novel
383 3 | 22 | 2 | rls_regress_user1 | my science fiction
384 4 | 44 | 1 | rls_regress_user1 | my first manga
385 5 | 44 | 2 | rls_regress_user1 | my second manga
386 6 | 22 | 1 | rls_regress_user2 | great science fiction
387 7 | 33 | 2 | rls_regress_user2 | great technology book
388 8 | 44 | 1 | rls_regress_user2 | great manga
389 10 | 33 | 1 | rls_regress_user2 | hoge
392 SELECT * FROM category;
394 -----+-----------------
401 -- database non-superuser with bypass privilege can bypass RLS policy when disabled
402 SET SESSION AUTHORIZATION rls_regress_exempt_user;
403 SET row_security TO OFF;
404 SELECT * FROM document;
405 did | cid | dlevel | dauthor | dtitle
406 -----+-----+--------+-------------------+-----------------------
407 1 | 11 | 1 | rls_regress_user1 | my first novel
408 2 | 11 | 2 | rls_regress_user1 | my second novel
409 3 | 22 | 2 | rls_regress_user1 | my science fiction
410 4 | 44 | 1 | rls_regress_user1 | my first manga
411 5 | 44 | 2 | rls_regress_user1 | my second manga
412 6 | 22 | 1 | rls_regress_user2 | great science fiction
413 7 | 33 | 2 | rls_regress_user2 | great technology book
414 8 | 44 | 1 | rls_regress_user2 | great manga
415 10 | 33 | 1 | rls_regress_user2 | hoge
418 SELECT * FROM category;
420 -----+-----------------
427 -- RLS policy applies to table owner when FORCE enabled.
428 SET SESSION AUTHORIZATION rls_regress_user0;
429 SET row_security TO FORCE;
430 SELECT * FROM document;
431 did | cid | dlevel | dauthor | dtitle
432 -----+-----+--------+---------+--------
435 SELECT * FROM category;
440 -- RLS policy does not apply to table owner when RLS enabled.
441 SET SESSION AUTHORIZATION rls_regress_user0;
442 SET row_security TO ON;
443 SELECT * FROM document;
444 did | cid | dlevel | dauthor | dtitle
445 -----+-----+--------+-------------------+-----------------------
446 1 | 11 | 1 | rls_regress_user1 | my first novel
447 2 | 11 | 2 | rls_regress_user1 | my second novel
448 3 | 22 | 2 | rls_regress_user1 | my science fiction
449 4 | 44 | 1 | rls_regress_user1 | my first manga
450 5 | 44 | 2 | rls_regress_user1 | my second manga
451 6 | 22 | 1 | rls_regress_user2 | great science fiction
452 7 | 33 | 2 | rls_regress_user2 | great technology book
453 8 | 44 | 1 | rls_regress_user2 | great manga
454 10 | 33 | 1 | rls_regress_user2 | hoge
457 SELECT * FROM category;
459 -----+-----------------
466 -- RLS policy does not apply to table owner when RLS disabled.
467 SET SESSION AUTHORIZATION rls_regress_user0;
468 SET row_security TO OFF;
469 SELECT * FROM document;
470 did | cid | dlevel | dauthor | dtitle
471 -----+-----+--------+-------------------+-----------------------
472 1 | 11 | 1 | rls_regress_user1 | my first novel
473 2 | 11 | 2 | rls_regress_user1 | my second novel
474 3 | 22 | 2 | rls_regress_user1 | my science fiction
475 4 | 44 | 1 | rls_regress_user1 | my first manga
476 5 | 44 | 2 | rls_regress_user1 | my second manga
477 6 | 22 | 1 | rls_regress_user2 | great science fiction
478 7 | 33 | 2 | rls_regress_user2 | great technology book
479 8 | 44 | 1 | rls_regress_user2 | great manga
480 10 | 33 | 1 | rls_regress_user2 | hoge
483 SELECT * FROM category;
485 -----+-----------------
493 -- Table inheritance and RLS policy
495 SET SESSION AUTHORIZATION rls_regress_user0;
496 SET row_security TO ON;
497 CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS;
498 ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor
499 GRANT ALL ON t1 TO public;
500 COPY t1 FROM stdin WITH (oids);
501 CREATE TABLE t2 (c float) INHERITS (t1);
502 GRANT ALL ON t2 TO public;
503 COPY t2 FROM stdin WITH (oids);
504 CREATE TABLE t3 (c text, b text, a int) WITH OIDS;
505 ALTER TABLE t3 INHERIT t1;
506 GRANT ALL ON t3 TO public;
507 COPY t3(a,b,c) FROM stdin WITH (oids);
508 CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number
509 CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number
510 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
511 ALTER TABLE t2 ENABLE ROW LEVEL SECURITY;
512 SET SESSION AUTHORIZATION rls_regress_user1;
523 EXPLAIN (COSTS OFF) SELECT * FROM t1;
525 -------------------------------
528 Filter: ((a % 2) = 0)
530 Filter: ((a % 2) = 0)
532 Filter: ((a % 2) = 0)
535 SELECT * FROM t1 WHERE f_leak(b);
536 NOTICE: f_leak => bbb
537 NOTICE: f_leak => ddd
538 NOTICE: f_leak => bcd
539 NOTICE: f_leak => def
540 NOTICE: f_leak => yyy
550 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
552 -------------------------------------
556 -> Seq Scan on t1 t1_1
557 Filter: ((a % 2) = 0)
559 Filter: ((a % 2) = 0)
561 Filter: ((a % 2) = 0)
564 -- reference to system column
565 SELECT oid, * FROM t1;
575 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
577 -------------------------------
580 Filter: ((a % 2) = 0)
582 Filter: ((a % 2) = 0)
584 Filter: ((a % 2) = 0)
587 -- reference to whole-row reference
588 SELECT *, t1 FROM t1;
598 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
600 -------------------------------
603 Filter: ((a % 2) = 0)
605 Filter: ((a % 2) = 0)
607 Filter: ((a % 2) = 0)
610 -- for share/update lock
611 SELECT * FROM t1 FOR SHARE;
621 EXPLAIN (COSTS OFF) SELECT * FROM t1 FOR SHARE;
623 -------------------------------------------------------
625 -> Subquery Scan on t1
629 -> Seq Scan on t1 t1_1
630 Filter: ((a % 2) = 0)
632 Filter: ((a % 2) = 0)
634 Filter: ((a % 2) = 0)
637 SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
638 NOTICE: f_leak => bbb
639 NOTICE: f_leak => ddd
640 NOTICE: f_leak => bcd
641 NOTICE: f_leak => def
642 NOTICE: f_leak => yyy
652 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
654 -------------------------------------------------------
656 -> Subquery Scan on t1
661 -> Seq Scan on t1 t1_1
662 Filter: ((a % 2) = 0)
664 Filter: ((a % 2) = 0)
666 Filter: ((a % 2) = 0)
669 -- superuser is allowed to bypass RLS checks
670 RESET SESSION AUTHORIZATION;
671 SET row_security TO OFF;
672 SELECT * FROM t1 WHERE f_leak(b);
673 NOTICE: f_leak => aaa
674 NOTICE: f_leak => bbb
675 NOTICE: f_leak => ccc
676 NOTICE: f_leak => ddd
677 NOTICE: f_leak => abc
678 NOTICE: f_leak => bcd
679 NOTICE: f_leak => cde
680 NOTICE: f_leak => def
681 NOTICE: f_leak => xxx
682 NOTICE: f_leak => yyy
683 NOTICE: f_leak => zzz
699 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
701 ---------------------------
711 -- non-superuser with bypass privilege can bypass RLS policy when disabled
712 SET SESSION AUTHORIZATION rls_regress_exempt_user;
713 SET row_security TO OFF;
714 SELECT * FROM t1 WHERE f_leak(b);
715 NOTICE: f_leak => aaa
716 NOTICE: f_leak => bbb
717 NOTICE: f_leak => ccc
718 NOTICE: f_leak => ddd
719 NOTICE: f_leak => abc
720 NOTICE: f_leak => bcd
721 NOTICE: f_leak => cde
722 NOTICE: f_leak => def
723 NOTICE: f_leak => xxx
724 NOTICE: f_leak => yyy
725 NOTICE: f_leak => zzz
741 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
743 ---------------------------
753 ----- Dependencies -----
754 SET SESSION AUTHORIZATION rls_regress_user0;
755 SET row_security TO ON;
756 CREATE TABLE dependee (x integer, y integer);
757 CREATE TABLE dependent (x integer, y integer);
758 CREATE POLICY d1 ON dependent FOR ALL
760 USING (x = (SELECT d.x FROM dependee d WHERE d.y = y));
761 DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row security qual?
762 ERROR: cannot drop table dependee because other objects depend on it
763 DETAIL: policy d1 on table dependent depends on table dependee
764 HINT: Use DROP ... CASCADE to drop the dependent objects too.
765 DROP TABLE dependee CASCADE;
766 NOTICE: drop cascades to policy d1 on table dependent
767 EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified
769 -----------------------
770 Seq Scan on dependent
777 SET SESSION AUTHORIZATION rls_regress_user0;
778 CREATE TABLE rec1 (x integer, y integer);
779 CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y));
780 ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;
781 SET SESSION AUTHORIZATION rls_regress_user1;
782 SELECT * FROM rec1; -- fail, direct recursion
783 ERROR: infinite recursion detected in policy for relation "rec1"
787 SET SESSION AUTHORIZATION rls_regress_user0;
788 CREATE TABLE rec2 (a integer, b integer);
789 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y));
790 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b));
791 ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;
792 SET SESSION AUTHORIZATION rls_regress_user1;
793 SELECT * FROM rec1; -- fail, mutual recursion
794 ERROR: infinite recursion detected in policy for relation "rec1"
796 -- Mutual recursion via views
798 SET SESSION AUTHORIZATION rls_regress_user1;
799 CREATE VIEW rec1v AS SELECT * FROM rec1;
800 CREATE VIEW rec2v AS SELECT * FROM rec2;
801 SET SESSION AUTHORIZATION rls_regress_user0;
802 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
803 ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
804 SET SESSION AUTHORIZATION rls_regress_user1;
805 SELECT * FROM rec1; -- fail, mutual recursion via views
806 ERROR: infinite recursion detected in policy for relation "rec1"
808 -- Mutual recursion via .s.b views
810 SET SESSION AUTHORIZATION rls_regress_user1;
811 -- Suppress NOTICE messages when doing a cascaded drop.
812 SET client_min_messages TO 'warning';
813 DROP VIEW rec1v, rec2v CASCADE;
814 RESET client_min_messages;
815 CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
816 CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
817 SET SESSION AUTHORIZATION rls_regress_user0;
818 CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
819 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
820 SET SESSION AUTHORIZATION rls_regress_user1;
821 SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
822 ERROR: infinite recursion detected in policy for relation "rec1"
824 -- recursive RLS and VIEWs in policy
826 SET SESSION AUTHORIZATION rls_regress_user0;
827 CREATE TABLE s1 (a int, b text);
828 INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
829 CREATE TABLE s2 (x int, y text);
830 INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
831 GRANT SELECT ON s1, s2 TO rls_regress_user1;
832 CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
833 CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
834 CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1));
835 ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;
836 ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;
837 SET SESSION AUTHORIZATION rls_regress_user1;
838 CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
839 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
840 ERROR: infinite recursion detected in policy for relation "s1"
841 INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion)
842 ERROR: infinite recursion detected in policy for relation "s1"
843 SET SESSION AUTHORIZATION rls_regress_user0;
844 DROP POLICY p3 on s1;
845 ALTER POLICY p2 ON s2 USING (x % 2 = 0);
846 SET SESSION AUTHORIZATION rls_regress_user1;
847 SELECT * FROM s1 WHERE f_leak(b); -- OK
848 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
849 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
851 ---+----------------------------------
852 2 | c81e728d9d4c2f636f067f89cc14862c
853 4 | a87ff679a2f3e71d9181a67b7542122c
856 EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
858 ----------------------------------------------------------
862 Hash Cond: (s1_1.a = s2.x)
863 -> Seq Scan on s1 s1_1
867 -> Subquery Scan on s2
868 Filter: (s2.y ~~ '%2f%'::text)
869 -> Seq Scan on s2 s2_1
870 Filter: ((x % 2) = 0)
873 SET SESSION AUTHORIZATION rls_regress_user0;
874 ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy
875 SET SESSION AUTHORIZATION rls_regress_user1;
876 SELECT * FROM s1 WHERE f_leak(b); -- OK
877 NOTICE: f_leak => 0267aaf632e87a63288a08331f22c7c3
878 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
880 ----+----------------------------------
881 -4 | 0267aaf632e87a63288a08331f22c7c3
882 6 | 1679091c5a880faf6fb5e6087eb1b2dc
885 EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
887 ----------------------------------------------------------
891 Hash Cond: (s1_1.a = s2.x)
892 -> Seq Scan on s1 s1_1
896 -> Subquery Scan on s2
897 Filter: (s2.y ~~ '%af%'::text)
898 -> Seq Scan on s2 s2_1
899 Filter: ((x % 2) = 0)
902 SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
904 ----+----+----------------------------------
905 -6 | -6 | 596a3d04481816330f07e4f97510c28f
906 -4 | -4 | 0267aaf632e87a63288a08331f22c7c3
907 2 | 2 | c81e728d9d4c2f636f067f89cc14862c
910 EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
912 --------------------------------------------------------------------
914 Filter: (s2.y ~~ '%28%'::text)
915 -> Seq Scan on s2 s2_1
916 Filter: ((x % 2) = 0)
919 -> Subquery Scan on s1
920 -> Nested Loop Semi Join
921 Join Filter: (s1_1.a = s2_2.x)
922 -> Seq Scan on s1 s1_1
924 -> Subquery Scan on s2_2
925 Filter: (s2_2.y ~~ '%af%'::text)
926 -> Seq Scan on s2 s2_3
927 Filter: ((x % 2) = 0)
930 SET SESSION AUTHORIZATION rls_regress_user0;
931 ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%'));
932 SET SESSION AUTHORIZATION rls_regress_user1;
933 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
934 ERROR: infinite recursion detected in policy for relation "s1"
935 -- prepared statement with rls_regress_user0 privilege
936 PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
945 EXPLAIN (COSTS OFF) EXECUTE p1(2);
947 ----------------------------------------------
950 Filter: ((a <= 2) AND ((a % 2) = 0))
952 Filter: ((a <= 2) AND ((a % 2) = 0))
954 Filter: ((a <= 2) AND ((a % 2) = 0))
957 -- superuser is allowed to bypass RLS checks
958 RESET SESSION AUTHORIZATION;
959 SET row_security TO OFF;
960 SELECT * FROM t1 WHERE f_leak(b);
961 NOTICE: f_leak => aaa
962 NOTICE: f_leak => bbb
963 NOTICE: f_leak => ccc
964 NOTICE: f_leak => ddd
965 NOTICE: f_leak => abc
966 NOTICE: f_leak => bcd
967 NOTICE: f_leak => cde
968 NOTICE: f_leak => def
969 NOTICE: f_leak => xxx
970 NOTICE: f_leak => yyy
971 NOTICE: f_leak => zzz
987 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
989 ---------------------------
999 -- plan cache should be invalidated
1011 EXPLAIN (COSTS OFF) EXECUTE p1(2);
1013 --------------------------
1023 PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1;
1032 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1034 -------------------------
1044 -- also, case when privilege switch from superuser
1045 SET SESSION AUTHORIZATION rls_regress_user1;
1046 SET row_security TO ON;
1055 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1057 ---------------------------------------------
1060 Filter: ((a = 2) AND ((a % 2) = 0))
1062 Filter: ((a = 2) AND ((a % 2) = 0))
1064 Filter: ((a = 2) AND ((a % 2) = 0))
1068 -- UPDATE / DELETE and Row-level security
1070 SET SESSION AUTHORIZATION rls_regress_user1;
1071 EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
1073 -------------------------------------------
1078 -> Subquery Scan on t1
1079 Filter: f_leak(t1.b)
1081 -> Seq Scan on t1 t1_4
1082 Filter: ((a % 2) = 0)
1083 -> Subquery Scan on t1_1
1084 Filter: f_leak(t1_1.b)
1087 Filter: ((a % 2) = 0)
1088 -> Subquery Scan on t1_2
1089 Filter: f_leak(t1_2.b)
1092 Filter: ((a % 2) = 0)
1095 UPDATE t1 SET b = b || b WHERE f_leak(b);
1096 NOTICE: f_leak => bbb
1097 NOTICE: f_leak => ddd
1098 NOTICE: f_leak => bcd
1099 NOTICE: f_leak => def
1100 NOTICE: f_leak => yyy
1101 EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1103 -------------------------------------------
1105 -> Subquery Scan on t1
1106 Filter: f_leak(t1.b)
1108 -> Seq Scan on t1 t1_2
1109 Filter: ((a % 2) = 0)
1112 UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1113 NOTICE: f_leak => bbbbbb
1114 NOTICE: f_leak => dddddd
1115 -- returning clause with system column
1116 UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
1117 NOTICE: f_leak => bbbbbb_updt
1118 NOTICE: f_leak => dddddd_updt
1120 -----+---+-------------+-----------------
1121 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
1122 104 | 4 | dddddd_updt | (4,dddddd_updt)
1125 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
1126 NOTICE: f_leak => bbbbbb_updt
1127 NOTICE: f_leak => dddddd_updt
1128 NOTICE: f_leak => bcdbcd
1129 NOTICE: f_leak => defdef
1130 NOTICE: f_leak => yyyyyy
1140 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
1141 NOTICE: f_leak => bbbbbb_updt
1142 NOTICE: f_leak => dddddd_updt
1143 NOTICE: f_leak => bcdbcd
1144 NOTICE: f_leak => defdef
1145 NOTICE: f_leak => yyyyyy
1147 -----+---+-------------+-----------------
1148 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
1149 104 | 4 | dddddd_updt | (4,dddddd_updt)
1150 202 | 2 | bcdbcd | (2,bcdbcd)
1151 204 | 4 | defdef | (4,defdef)
1152 302 | 2 | yyyyyy | (2,yyyyyy)
1155 -- updates with from clause
1156 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3
1157 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1159 ---------------------------------------------------------------
1162 -> Subquery Scan on t2
1163 Filter: f_leak(t2.b)
1165 -> Seq Scan on t2 t2_2
1166 Filter: ((a = 3) AND ((a % 2) = 1))
1168 Filter: (f_leak(b) AND (a = 2))
1171 UPDATE t2 SET b=t2.b FROM t3
1172 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1173 NOTICE: f_leak => cde
1174 NOTICE: f_leak => xxx
1175 NOTICE: f_leak => zzz
1176 NOTICE: f_leak => yyyyyy
1177 EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2
1178 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1180 ---------------------------------------------------------------
1186 -> Subquery Scan on t1
1187 Filter: f_leak(t1.b)
1189 -> Seq Scan on t1 t1_4
1190 Filter: ((a = 3) AND ((a % 2) = 0))
1191 -> Subquery Scan on t2
1192 Filter: f_leak(t2.b)
1193 -> Seq Scan on t2 t2_3
1194 Filter: ((a = 3) AND ((a % 2) = 1))
1196 -> Subquery Scan on t1_1
1197 Filter: f_leak(t1_1.b)
1199 -> Seq Scan on t2 t2_4
1200 Filter: ((a = 3) AND ((a % 2) = 0))
1201 -> Subquery Scan on t2_1
1202 Filter: f_leak(t2_1.b)
1203 -> Seq Scan on t2 t2_5
1204 Filter: ((a = 3) AND ((a % 2) = 1))
1206 -> Subquery Scan on t1_2
1207 Filter: f_leak(t1_2.b)
1210 Filter: ((a = 3) AND ((a % 2) = 0))
1211 -> Subquery Scan on t2_2
1212 Filter: f_leak(t2_2.b)
1213 -> Seq Scan on t2 t2_6
1214 Filter: ((a = 3) AND ((a % 2) = 1))
1217 UPDATE t1 SET b=t1.b FROM t2
1218 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1219 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1
1220 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1222 ---------------------------------------------------------------------
1225 -> Subquery Scan on t2
1226 Filter: f_leak(t2.b)
1228 -> Seq Scan on t2 t2_2
1229 Filter: ((a = 3) AND ((a % 2) = 1))
1230 -> Subquery Scan on t1
1231 Filter: f_leak(t1.b)
1234 -> Seq Scan on t1 t1_1
1235 Filter: ((a = 3) AND ((a % 2) = 0))
1236 -> Seq Scan on t2 t2_3
1237 Filter: ((a = 3) AND ((a % 2) = 0))
1239 Filter: ((a = 3) AND ((a % 2) = 0))
1242 UPDATE t2 SET b=t2.b FROM t1
1243 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1244 NOTICE: f_leak => cde
1245 -- updates with from clause self join
1246 EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1247 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1248 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1250 ---------------------------------------------------------------
1253 Join Filter: (t2_1.b = t2_2.b)
1254 -> Subquery Scan on t2_1
1255 Filter: f_leak(t2_1.b)
1257 -> Seq Scan on t2 t2_1_2
1258 Filter: ((a = 3) AND ((a % 2) = 1))
1259 -> Subquery Scan on t2_2
1260 Filter: f_leak(t2_2.b)
1261 -> Seq Scan on t2 t2_2_1
1262 Filter: ((a = 3) AND ((a % 2) = 1))
1265 UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1266 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1267 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1268 NOTICE: f_leak => cde
1269 NOTICE: f_leak => cde
1270 a | b | c | a | b | c | t2_1 | t2_2
1271 ---+-----+-----+---+-----+-----+-------------+-------------
1272 3 | cde | 3.3 | 3 | cde | 3.3 | (3,cde,3.3) | (3,cde,3.3)
1275 EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1276 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1277 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1279 ---------------------------------------------------------------
1285 Join Filter: (t1_1.b = t1_2.b)
1286 -> Subquery Scan on t1_1
1287 Filter: f_leak(t1_1.b)
1289 -> Seq Scan on t1 t1_1_4
1290 Filter: ((a = 4) AND ((a % 2) = 0))
1291 -> Subquery Scan on t1_2
1292 Filter: f_leak(t1_2.b)
1294 -> Seq Scan on t1 t1_2_3
1295 Filter: ((a = 4) AND ((a % 2) = 0))
1296 -> Seq Scan on t2 t1_2_4
1297 Filter: ((a = 4) AND ((a % 2) = 0))
1298 -> Seq Scan on t3 t1_2_5
1299 Filter: ((a = 4) AND ((a % 2) = 0))
1301 Join Filter: (t1_1_1.b = t1_2_1.b)
1302 -> Subquery Scan on t1_1_1
1303 Filter: f_leak(t1_1_1.b)
1305 -> Seq Scan on t2 t1_1_5
1306 Filter: ((a = 4) AND ((a % 2) = 0))
1307 -> Subquery Scan on t1_2_1
1308 Filter: f_leak(t1_2_1.b)
1310 -> Seq Scan on t1 t1_2_6
1311 Filter: ((a = 4) AND ((a % 2) = 0))
1312 -> Seq Scan on t2 t1_2_7
1313 Filter: ((a = 4) AND ((a % 2) = 0))
1314 -> Seq Scan on t3 t1_2_8
1315 Filter: ((a = 4) AND ((a % 2) = 0))
1317 Join Filter: (t1_1_2.b = t1_2_2.b)
1318 -> Subquery Scan on t1_1_2
1319 Filter: f_leak(t1_1_2.b)
1321 -> Seq Scan on t3 t1_1_6
1322 Filter: ((a = 4) AND ((a % 2) = 0))
1323 -> Subquery Scan on t1_2_2
1324 Filter: f_leak(t1_2_2.b)
1326 -> Seq Scan on t1 t1_2_9
1327 Filter: ((a = 4) AND ((a % 2) = 0))
1328 -> Seq Scan on t2 t1_2_10
1329 Filter: ((a = 4) AND ((a % 2) = 0))
1330 -> Seq Scan on t3 t1_2_11
1331 Filter: ((a = 4) AND ((a % 2) = 0))
1334 UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1335 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1336 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1337 NOTICE: f_leak => dddddd_updt
1338 NOTICE: f_leak => dddddd_updt
1339 NOTICE: f_leak => defdef
1340 NOTICE: f_leak => defdef
1341 NOTICE: f_leak => dddddd_updt
1342 NOTICE: f_leak => defdef
1343 a | b | a | b | t1_1 | t1_2
1344 ---+-------------+---+-------------+-----------------+-----------------
1345 4 | dddddd_updt | 4 | dddddd_updt | (4,dddddd_updt) | (4,dddddd_updt)
1346 4 | defdef | 4 | defdef | (4,defdef) | (4,defdef)
1349 RESET SESSION AUTHORIZATION;
1350 SET row_security TO OFF;
1351 SELECT * FROM t1 ORDER BY a,b;
1367 SET SESSION AUTHORIZATION rls_regress_user1;
1368 SET row_security TO ON;
1369 EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b);
1371 -------------------------------------------
1373 -> Subquery Scan on t1
1374 Filter: f_leak(t1.b)
1376 -> Seq Scan on t1 t1_2
1377 Filter: ((a % 2) = 0)
1380 EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
1382 -------------------------------------------
1387 -> Subquery Scan on t1
1388 Filter: f_leak(t1.b)
1390 -> Seq Scan on t1 t1_4
1391 Filter: ((a % 2) = 0)
1392 -> Subquery Scan on t1_1
1393 Filter: f_leak(t1_1.b)
1396 Filter: ((a % 2) = 0)
1397 -> Subquery Scan on t1_2
1398 Filter: f_leak(t1_2.b)
1401 Filter: ((a % 2) = 0)
1404 DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1;
1405 NOTICE: f_leak => bbbbbb_updt
1406 NOTICE: f_leak => dddddd_updt
1408 -----+---+-------------+-----------------
1409 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
1410 104 | 4 | dddddd_updt | (4,dddddd_updt)
1413 DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1;
1414 NOTICE: f_leak => bcdbcd
1415 NOTICE: f_leak => defdef
1416 NOTICE: f_leak => yyyyyy
1418 -----+---+--------+------------
1419 202 | 2 | bcdbcd | (2,bcdbcd)
1420 204 | 4 | defdef | (4,defdef)
1421 302 | 2 | yyyyyy | (2,yyyyyy)
1425 -- S.b. view on top of Row-level security
1427 SET SESSION AUTHORIZATION rls_regress_user0;
1428 CREATE TABLE b1 (a int, b text);
1429 INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
1430 CREATE POLICY p1 ON b1 USING (a % 2 = 0);
1431 ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
1432 GRANT ALL ON b1 TO rls_regress_user1;
1433 SET SESSION AUTHORIZATION rls_regress_user1;
1434 CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
1435 GRANT ALL ON bv1 TO rls_regress_user2;
1436 SET SESSION AUTHORIZATION rls_regress_user2;
1437 EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
1439 ---------------------------------------------
1440 Subquery Scan on bv1
1441 Filter: f_leak(bv1.b)
1443 Filter: ((a > 0) AND ((a % 2) = 0))
1446 SELECT * FROM bv1 WHERE f_leak(b);
1447 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
1448 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1449 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1450 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
1451 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
1453 ----+----------------------------------
1454 2 | c81e728d9d4c2f636f067f89cc14862c
1455 4 | a87ff679a2f3e71d9181a67b7542122c
1456 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1457 8 | c9f0f895fb98ab9159f51fd0297e236d
1458 10 | d3d9446802a44259755d38e6d163e820
1461 INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO
1462 ERROR: new row violates row level security policy for "b1"
1463 INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check
1464 ERROR: new row violates row level security policy for "b1"
1465 INSERT INTO bv1 VALUES (12, 'xxx'); -- ok
1466 EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1468 ---------------------------------------------------------------------------
1470 -> Subquery Scan on b1
1471 Filter: f_leak(b1.b)
1472 -> Subquery Scan on b1_2
1474 -> Seq Scan on b1 b1_3
1475 Filter: ((a > 0) AND (a = 4) AND ((a % 2) = 0))
1478 UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1479 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1480 EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1482 ---------------------------------------------------------------------------
1484 -> Subquery Scan on b1
1485 Filter: f_leak(b1.b)
1486 -> Subquery Scan on b1_2
1488 -> Seq Scan on b1 b1_3
1489 Filter: ((a > 0) AND (a = 6) AND ((a % 2) = 0))
1492 DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1493 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1494 SET SESSION AUTHORIZATION rls_regress_user0;
1497 -----+----------------------------------
1498 -10 | 1b0fd9efa5279c4203b7c70233f86dbf
1499 -9 | 252e691406782824eec43d7eadc3d256
1500 -8 | a8d2ec85eaf98407310b72eb73dda247
1501 -7 | 74687a12d3915d3c4d83f1af7b3683d5
1502 -6 | 596a3d04481816330f07e4f97510c28f
1503 -5 | 47c1b025fa18ea96c33fbb6718688c0f
1504 -4 | 0267aaf632e87a63288a08331f22c7c3
1505 -3 | b3149ecea4628efd23d2f86e5a723472
1506 -2 | 5d7b9adcbe1c629ec722529dd12e5129
1507 -1 | 6bb61e3b7bce0931da574d19d1d82c88
1508 0 | cfcd208495d565ef66e7dff9f98764da
1509 1 | c4ca4238a0b923820dcc509a6f75849b
1510 2 | c81e728d9d4c2f636f067f89cc14862c
1511 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
1512 5 | e4da3b7fbbce2345d7772b0674a318d5
1513 7 | 8f14e45fceea167a5a36dedd4bea2543
1514 8 | c9f0f895fb98ab9159f51fd0297e236d
1515 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
1516 10 | d3d9446802a44259755d38e6d163e820
1522 -- INSERT ... ON CONFLICT DO UPDATE and Row-level security
1524 SET SESSION AUTHORIZATION rls_regress_user0;
1525 DROP POLICY p1 ON document;
1526 CREATE POLICY p1 ON document FOR SELECT USING (true);
1527 CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
1528 CREATE POLICY p3 ON document FOR UPDATE
1529 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
1530 WITH CHECK (dauthor = current_user);
1531 SET SESSION AUTHORIZATION rls_regress_user1;
1533 SELECT * FROM document WHERE did = 2;
1534 did | cid | dlevel | dauthor | dtitle
1535 -----+-----+--------+-------------------+-----------------
1536 2 | 11 | 2 | rls_regress_user1 | my second novel
1539 -- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
1540 -- alternative UPDATE path happens to be taken):
1541 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
1542 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor;
1543 ERROR: new row violates row level security policy for "document"
1544 -- Violates USING qual for UPDATE policy p3.
1546 -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
1547 -- updated is not a "novel"/cid 11 (row is not leaked, even though we have
1548 -- SELECT privileges sufficient to see the row in this instance):
1549 INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction'); -- preparation for next statement
1550 INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path
1551 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
1552 ERROR: new row violates row level security policy (USING expression) for "document"
1553 -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
1555 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
1556 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
1557 did | cid | dlevel | dauthor | dtitle
1558 -----+-----+--------+-------------------+----------------
1559 2 | 11 | 2 | rls_regress_user1 | my first novel
1562 -- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated):
1563 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
1564 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
1565 did | cid | dlevel | dauthor | dtitle
1566 -----+-----+--------+-------------------+-----------------------
1567 78 | 11 | 1 | rls_regress_user1 | some technology novel
1570 -- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the
1571 -- case in respect of *existing* tuple):
1572 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
1573 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
1574 did | cid | dlevel | dauthor | dtitle
1575 -----+-----+--------+-------------------+-----------------------
1576 78 | 33 | 1 | rls_regress_user1 | some technology novel
1579 -- Same query a third time, but now fails due to existing tuple finally not
1581 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
1582 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
1583 ERROR: new row violates row level security policy (USING expression) for "document"
1584 -- Don't fail just because INSERT doesn't satisfy WITH CHECK option that
1585 -- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE
1586 -- path *isn't* taken, and so UPDATE-related policy does not apply:
1587 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
1588 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
1589 did | cid | dlevel | dauthor | dtitle
1590 -----+-----+--------+-------------------+----------------------------------
1591 79 | 33 | 1 | rls_regress_user1 | technology book, can only insert
1594 -- But this time, the same statement fails, because the UPDATE path is taken,
1595 -- and updating the row just inserted falls afoul of security barrier qual
1596 -- (enforced as WCO) -- what we might have updated target tuple to is
1597 -- irrelevant, in fact.
1598 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
1599 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
1600 ERROR: new row violates row level security policy (USING expression) for "document"
1601 -- Test default USING qual enforced as WCO
1602 SET SESSION AUTHORIZATION rls_regress_user0;
1603 DROP POLICY p1 ON document;
1604 DROP POLICY p2 ON document;
1605 DROP POLICY p3 ON document;
1606 CREATE POLICY p3_with_default ON document FOR UPDATE
1607 USING (cid = (SELECT cid from category WHERE cname = 'novel'));
1608 SET SESSION AUTHORIZATION rls_regress_user1;
1609 -- Just because WCO-style enforcement of USING quals occurs with
1610 -- existing/target tuple does not mean that the implementation can be allowed
1611 -- to fail to also enforce this qual against the final tuple appended to
1612 -- relation (since in the absence of an explicit WCO, this is also interpreted
1613 -- as an UPDATE/ALL WCO in general).
1615 -- UPDATE path is taken here (fails due to existing tuple). Note that this is
1616 -- not reported as a "USING expression", because it's an RLS UPDATE check that originated as
1617 -- a USING qual for the purposes of RLS in general, as opposed to an explicit
1618 -- USING qual that is ordinarily a security barrier. We leave it up to the
1619 -- UPDATE to make this fail:
1620 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
1621 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
1622 ERROR: new row violates row level security policy for "document"
1623 -- UPDATE path is taken here. Existing tuple passes, since it's cid
1624 -- corresponds to "novel", but default USING qual is enforced against
1625 -- post-UPDATE tuple too (as always when updating with a policy that lacks an
1626 -- explicit WCO), and so this fails:
1627 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'my first novel')
1628 ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *;
1629 ERROR: new row violates row level security policy for "document"
1630 SET SESSION AUTHORIZATION rls_regress_user0;
1631 DROP POLICY p3_with_default ON document;
1633 -- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE
1636 CREATE POLICY p3_with_all ON document FOR ALL
1637 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
1638 WITH CHECK (dauthor = current_user);
1639 SET SESSION AUTHORIZATION rls_regress_user1;
1640 -- Fails, since ALL WCO is enforced in insert path:
1641 INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
1642 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33;
1643 ERROR: new row violates row level security policy for "document"
1644 -- Fails, since ALL policy USING qual is enforced (existing, target tuple is in
1645 -- violation, since it has the "manga" cid):
1646 INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
1647 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
1648 ERROR: new row violates row level security policy (USING expression) for "document"
1649 -- Fails, since ALL WCO are enforced:
1650 INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
1651 ON CONFLICT (did) DO UPDATE SET dauthor = 'rls_regress_user2';
1652 ERROR: new row violates row level security policy for "document"
1656 SET SESSION AUTHORIZATION rls_regress_user0;
1657 CREATE TABLE z1 (a int, b text);
1658 GRANT SELECT ON z1 TO rls_regress_group1, rls_regress_group2,
1659 rls_regress_user1, rls_regress_user2;
1660 INSERT INTO z1 VALUES
1665 CREATE POLICY p1 ON z1 TO rls_regress_group1 USING (a % 2 = 0);
1666 CREATE POLICY p2 ON z1 TO rls_regress_group2 USING (a % 2 = 1);
1667 ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
1668 SET SESSION AUTHORIZATION rls_regress_user1;
1669 SELECT * FROM z1 WHERE f_leak(b);
1670 NOTICE: f_leak => bbb
1671 NOTICE: f_leak => ddd
1678 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1680 -------------------------------
1682 Filter: f_leak(z1.b)
1683 -> Seq Scan on z1 z1_1
1684 Filter: ((a % 2) = 0)
1687 SET ROLE rls_regress_group1;
1688 SELECT * FROM z1 WHERE f_leak(b);
1689 NOTICE: f_leak => bbb
1690 NOTICE: f_leak => ddd
1697 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1699 -------------------------------
1701 Filter: f_leak(z1.b)
1702 -> Seq Scan on z1 z1_1
1703 Filter: ((a % 2) = 0)
1706 SET SESSION AUTHORIZATION rls_regress_user2;
1707 SELECT * FROM z1 WHERE f_leak(b);
1708 NOTICE: f_leak => aaa
1709 NOTICE: f_leak => ccc
1716 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1718 -------------------------------
1720 Filter: f_leak(z1.b)
1721 -> Seq Scan on z1 z1_1
1722 Filter: ((a % 2) = 1)
1725 SET ROLE rls_regress_group2;
1726 SELECT * FROM z1 WHERE f_leak(b);
1727 NOTICE: f_leak => aaa
1728 NOTICE: f_leak => ccc
1735 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
1737 -------------------------------
1739 Filter: f_leak(z1.b)
1740 -> Seq Scan on z1 z1_1
1741 Filter: ((a % 2) = 1)
1745 -- Views should follow policy for view owner.
1747 -- View and Table owner are the same.
1748 SET SESSION AUTHORIZATION rls_regress_user0;
1749 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
1750 GRANT SELECT ON rls_view TO rls_regress_user1;
1751 -- Query as role that is not owner of view or table. Should return all records.
1752 SET SESSION AUTHORIZATION rls_regress_user1;
1753 SELECT * FROM rls_view;
1754 NOTICE: f_leak => aaa
1755 NOTICE: f_leak => bbb
1756 NOTICE: f_leak => ccc
1757 NOTICE: f_leak => ddd
1766 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1768 ---------------------
1773 -- Query as view/table owner. Should return all records.
1774 SET SESSION AUTHORIZATION rls_regress_user0;
1775 SELECT * FROM rls_view;
1776 NOTICE: f_leak => aaa
1777 NOTICE: f_leak => bbb
1778 NOTICE: f_leak => ccc
1779 NOTICE: f_leak => ddd
1788 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1790 ---------------------
1796 -- View and Table owners are different.
1797 SET SESSION AUTHORIZATION rls_regress_user1;
1798 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
1799 GRANT SELECT ON rls_view TO rls_regress_user0;
1800 -- Query as role that is not owner of view but is owner of table.
1801 -- Should return records based on view owner policies.
1802 SET SESSION AUTHORIZATION rls_regress_user0;
1803 SELECT * FROM rls_view;
1804 NOTICE: f_leak => bbb
1805 NOTICE: f_leak => ddd
1812 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1814 -------------------------------
1816 Filter: f_leak(z1.b)
1817 -> Seq Scan on z1 z1_1
1818 Filter: ((a % 2) = 0)
1821 -- Query as role that is not owner of table but is owner of view.
1822 -- Should return records based on view owner policies.
1823 SET SESSION AUTHORIZATION rls_regress_user1;
1824 SELECT * FROM rls_view;
1825 NOTICE: f_leak => bbb
1826 NOTICE: f_leak => ddd
1833 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1835 -------------------------------
1837 Filter: f_leak(z1.b)
1838 -> Seq Scan on z1 z1_1
1839 Filter: ((a % 2) = 0)
1842 -- Query as role that is not the owner of the table or view without permissions.
1843 SET SESSION AUTHORIZATION rls_regress_user2;
1844 SELECT * FROM rls_view; --fail - permission denied.
1845 ERROR: permission denied for relation rls_view
1846 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
1847 ERROR: permission denied for relation rls_view
1848 -- Query as role that is not the owner of the table or view with permissions.
1849 SET SESSION AUTHORIZATION rls_regress_user1;
1850 GRANT SELECT ON rls_view TO rls_regress_user2;
1851 SELECT * FROM rls_view;
1852 NOTICE: f_leak => bbb
1853 NOTICE: f_leak => ddd
1860 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
1862 -------------------------------
1864 Filter: f_leak(z1.b)
1865 -> Seq Scan on z1 z1_1
1866 Filter: ((a % 2) = 0)
1869 SET SESSION AUTHORIZATION rls_regress_user1;
1874 SET SESSION AUTHORIZATION rls_regress_user0;
1875 CREATE TABLE x1 (a int, b text, c text);
1876 GRANT ALL ON x1 TO PUBLIC;
1877 INSERT INTO x1 VALUES
1878 (1, 'abc', 'rls_regress_user1'),
1879 (2, 'bcd', 'rls_regress_user1'),
1880 (3, 'cde', 'rls_regress_user2'),
1881 (4, 'def', 'rls_regress_user2'),
1882 (5, 'efg', 'rls_regress_user1'),
1883 (6, 'fgh', 'rls_regress_user1'),
1884 (7, 'fgh', 'rls_regress_user2'),
1885 (8, 'fgh', 'rls_regress_user2');
1886 CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
1887 CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
1888 CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1);
1889 CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0);
1890 CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
1891 ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
1892 SET SESSION AUTHORIZATION rls_regress_user1;
1893 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
1894 NOTICE: f_leak => abc
1895 NOTICE: f_leak => bcd
1896 NOTICE: f_leak => def
1897 NOTICE: f_leak => efg
1898 NOTICE: f_leak => fgh
1899 NOTICE: f_leak => fgh
1901 ---+-----+-------------------
1902 1 | abc | rls_regress_user1
1903 2 | bcd | rls_regress_user1
1904 4 | def | rls_regress_user2
1905 5 | efg | rls_regress_user1
1906 6 | fgh | rls_regress_user1
1907 8 | fgh | rls_regress_user2
1910 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
1911 NOTICE: f_leak => abc
1912 NOTICE: f_leak => bcd
1913 NOTICE: f_leak => def
1914 NOTICE: f_leak => efg
1915 NOTICE: f_leak => fgh
1916 NOTICE: f_leak => fgh
1918 ---+----------+-------------------
1919 1 | abc_updt | rls_regress_user1
1920 2 | bcd_updt | rls_regress_user1
1921 4 | def_updt | rls_regress_user2
1922 5 | efg_updt | rls_regress_user1
1923 6 | fgh_updt | rls_regress_user1
1924 8 | fgh_updt | rls_regress_user2
1927 SET SESSION AUTHORIZATION rls_regress_user2;
1928 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
1929 NOTICE: f_leak => cde
1930 NOTICE: f_leak => fgh
1931 NOTICE: f_leak => bcd_updt
1932 NOTICE: f_leak => def_updt
1933 NOTICE: f_leak => fgh_updt
1934 NOTICE: f_leak => fgh_updt
1936 ---+----------+-------------------
1937 2 | bcd_updt | rls_regress_user1
1938 3 | cde | rls_regress_user2
1939 4 | def_updt | rls_regress_user2
1940 6 | fgh_updt | rls_regress_user1
1941 7 | fgh | rls_regress_user2
1942 8 | fgh_updt | rls_regress_user2
1945 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
1946 NOTICE: f_leak => cde
1947 NOTICE: f_leak => fgh
1948 NOTICE: f_leak => bcd_updt
1949 NOTICE: f_leak => def_updt
1950 NOTICE: f_leak => fgh_updt
1951 NOTICE: f_leak => fgh_updt
1953 ---+---------------+-------------------
1954 3 | cde_updt | rls_regress_user2
1955 7 | fgh_updt | rls_regress_user2
1956 2 | bcd_updt_updt | rls_regress_user1
1957 4 | def_updt_updt | rls_regress_user2
1958 6 | fgh_updt_updt | rls_regress_user1
1959 8 | fgh_updt_updt | rls_regress_user2
1962 DELETE FROM x1 WHERE f_leak(b) RETURNING *;
1963 NOTICE: f_leak => abc_updt
1964 NOTICE: f_leak => efg_updt
1965 NOTICE: f_leak => cde_updt
1966 NOTICE: f_leak => fgh_updt
1967 NOTICE: f_leak => bcd_updt_updt
1968 NOTICE: f_leak => def_updt_updt
1969 NOTICE: f_leak => fgh_updt_updt
1970 NOTICE: f_leak => fgh_updt_updt
1972 ---+---------------+-------------------
1973 1 | abc_updt | rls_regress_user1
1974 5 | efg_updt | rls_regress_user1
1975 3 | cde_updt | rls_regress_user2
1976 7 | fgh_updt | rls_regress_user2
1977 2 | bcd_updt_updt | rls_regress_user1
1978 4 | def_updt_updt | rls_regress_user2
1979 6 | fgh_updt_updt | rls_regress_user1
1980 8 | fgh_updt_updt | rls_regress_user2
1984 -- Duplicate Policy Names
1986 SET SESSION AUTHORIZATION rls_regress_user0;
1987 CREATE TABLE y1 (a int, b text);
1988 CREATE TABLE y2 (a int, b text);
1989 GRANT ALL ON y1, y2 TO rls_regress_user1;
1990 CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
1991 CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
1992 CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
1993 ERROR: policy "p1" for table "y1" already exists
1994 CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK
1995 ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;
1996 ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
1998 -- Expression structure with SBV
2000 -- Create view as table owner. RLS should NOT be applied.
2001 SET SESSION AUTHORIZATION rls_regress_user0;
2002 CREATE VIEW rls_sbv WITH (security_barrier) AS
2003 SELECT * FROM y1 WHERE f_leak(b);
2004 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
2006 -----------------------------------
2008 Filter: (f_leak(b) AND (a = 1))
2012 -- Create view as role that does not own table. RLS should be applied.
2013 SET SESSION AUTHORIZATION rls_regress_user1;
2014 CREATE VIEW rls_sbv WITH (security_barrier) AS
2015 SELECT * FROM y1 WHERE f_leak(b);
2016 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
2018 ----------------------------------------------------------
2020 Filter: f_leak(y1.b)
2021 -> Seq Scan on y1 y1_1
2022 Filter: ((a = 1) AND ((a > 2) OR ((a % 2) = 0)))
2027 -- Expression structure
2029 SET SESSION AUTHORIZATION rls_regress_user0;
2030 INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
2031 CREATE POLICY p2 ON y2 USING (a % 3 = 0);
2032 CREATE POLICY p3 ON y2 USING (a % 4 = 0);
2033 SET SESSION AUTHORIZATION rls_regress_user1;
2034 SELECT * FROM y2 WHERE f_leak(b);
2035 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2036 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2037 NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
2038 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2039 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2040 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2041 NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
2042 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2043 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2044 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2045 NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
2046 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2047 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2048 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2050 ----+----------------------------------
2051 0 | cfcd208495d565ef66e7dff9f98764da
2052 2 | c81e728d9d4c2f636f067f89cc14862c
2053 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2054 4 | a87ff679a2f3e71d9181a67b7542122c
2055 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2056 8 | c9f0f895fb98ab9159f51fd0297e236d
2057 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2058 10 | d3d9446802a44259755d38e6d163e820
2059 12 | c20ad4d76fe97759aa27a0c99bff6710
2060 14 | aab3238922bcc25a6f606eb525ffdc56
2061 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2062 16 | c74d97b01eae257e44aa9d5bade97baf
2063 18 | 6f4922f45568161a8cdf4ad2299f6d23
2064 20 | 98f13708210194c475687be6106a3b84
2067 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
2069 -------------------------------------------------------------------
2071 Filter: f_leak(y2.b)
2072 -> Seq Scan on y2 y2_1
2073 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
2077 -- Qual push-down of leaky functions, when not referring to table
2079 SELECT * FROM y2 WHERE 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
2099 NOTICE: f_leak => abc
2100 NOTICE: f_leak => abc
2102 ----+----------------------------------
2103 0 | cfcd208495d565ef66e7dff9f98764da
2104 2 | c81e728d9d4c2f636f067f89cc14862c
2105 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2106 4 | a87ff679a2f3e71d9181a67b7542122c
2107 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2108 8 | c9f0f895fb98ab9159f51fd0297e236d
2109 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2110 10 | d3d9446802a44259755d38e6d163e820
2111 12 | c20ad4d76fe97759aa27a0c99bff6710
2112 14 | aab3238922bcc25a6f606eb525ffdc56
2113 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2114 16 | c74d97b01eae257e44aa9d5bade97baf
2115 18 | 6f4922f45568161a8cdf4ad2299f6d23
2116 20 | 98f13708210194c475687be6106a3b84
2119 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc');
2121 ---------------------------------------------------------------------------------------
2123 Filter: (f_leak('abc'::text) AND (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)))
2126 CREATE TABLE test_qual_pushdown (
2129 INSERT INTO test_qual_pushdown VALUES ('abc'),('def');
2130 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
2131 NOTICE: f_leak => abc
2132 NOTICE: f_leak => def
2137 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
2139 -------------------------------------------------------------------------
2141 Hash Cond: (test_qual_pushdown.abc = y2.b)
2142 -> Seq Scan on test_qual_pushdown
2146 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
2149 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
2150 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2151 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2152 NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
2153 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2154 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2155 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2156 NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
2157 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2158 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2159 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2160 NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
2161 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2162 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2163 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2168 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
2170 -------------------------------------------------------------------------------
2172 Hash Cond: (test_qual_pushdown.abc = y2.b)
2173 -> Seq Scan on test_qual_pushdown
2175 -> Subquery Scan on y2
2176 Filter: f_leak(y2.b)
2177 -> Seq Scan on y2 y2_1
2178 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
2181 DROP TABLE test_qual_pushdown;
2183 -- Plancache invalidate on user change.
2185 RESET SESSION AUTHORIZATION;
2186 -- Suppress NOTICE messages when doing a cascaded drop.
2187 SET client_min_messages TO 'warning';
2188 DROP TABLE t1 CASCADE;
2189 RESET client_min_messages;
2190 CREATE TABLE t1 (a integer);
2191 GRANT SELECT ON t1 TO rls_regress_user1, rls_regress_user2;
2192 CREATE POLICY p1 ON t1 TO rls_regress_user1 USING ((a % 2) = 0);
2193 CREATE POLICY p2 ON t1 TO rls_regress_user2 USING ((a % 4) = 0);
2194 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
2195 SET ROLE rls_regress_user1;
2196 PREPARE role_inval AS SELECT * FROM t1;
2197 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2199 -------------------------
2201 Filter: ((a % 2) = 0)
2204 SET ROLE rls_regress_user2;
2205 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2207 -------------------------
2209 Filter: ((a % 4) = 0)
2215 RESET SESSION AUTHORIZATION;
2216 DROP TABLE t1 CASCADE;
2217 CREATE TABLE t1 (a integer, b text);
2218 CREATE POLICY p1 ON t1 USING (a % 2 = 0);
2219 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
2220 GRANT ALL ON t1 TO rls_regress_user1;
2221 INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
2222 SET SESSION AUTHORIZATION rls_regress_user1;
2223 WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
2224 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2225 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2226 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2227 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2228 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2229 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2230 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2231 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2232 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2233 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2234 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2236 ----+----------------------------------
2237 0 | cfcd208495d565ef66e7dff9f98764da
2238 2 | c81e728d9d4c2f636f067f89cc14862c
2239 4 | a87ff679a2f3e71d9181a67b7542122c
2240 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2241 8 | c9f0f895fb98ab9159f51fd0297e236d
2242 10 | d3d9446802a44259755d38e6d163e820
2243 12 | c20ad4d76fe97759aa27a0c99bff6710
2244 14 | aab3238922bcc25a6f606eb525ffdc56
2245 16 | c74d97b01eae257e44aa9d5bade97baf
2246 18 | 6f4922f45568161a8cdf4ad2299f6d23
2247 20 | 98f13708210194c475687be6106a3b84
2250 EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
2252 ---------------------------------------
2255 -> Subquery Scan on t1
2256 Filter: f_leak(t1.b)
2257 -> Seq Scan on t1 t1_1
2258 Filter: ((a % 2) = 0)
2261 WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
2262 ERROR: new row violates row level security policy for "t1"
2263 WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
2265 ----+----------------------------------
2266 0 | cfcd208495d565ef66e7dff9f98764da
2267 2 | c81e728d9d4c2f636f067f89cc14862c
2268 4 | a87ff679a2f3e71d9181a67b7542122c
2269 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2270 8 | c9f0f895fb98ab9159f51fd0297e236d
2271 10 | d3d9446802a44259755d38e6d163e820
2272 12 | c20ad4d76fe97759aa27a0c99bff6710
2273 14 | aab3238922bcc25a6f606eb525ffdc56
2274 16 | c74d97b01eae257e44aa9d5bade97baf
2275 18 | 6f4922f45568161a8cdf4ad2299f6d23
2276 20 | 98f13708210194c475687be6106a3b84
2279 WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail
2280 ERROR: new row violates row level security policy for "t1"
2281 WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok
2290 RESET SESSION AUTHORIZATION;
2291 ALTER POLICY p1 ON t1 RENAME TO p1; --fail
2292 ERROR: policy "p1" for table "t1" already exists
2293 SELECT polname, relname
2295 JOIN pg_class pc ON (pc.oid = pol.polrelid)
2296 WHERE relname = 't1';
2302 ALTER POLICY p1 ON t1 RENAME TO p2; --ok
2303 SELECT polname, relname
2305 JOIN pg_class pc ON (pc.oid = pol.polrelid)
2306 WHERE relname = 't1';
2313 -- Check INSERT SELECT
2315 SET SESSION AUTHORIZATION rls_regress_user1;
2316 CREATE TABLE t2 (a integer, b text);
2317 INSERT INTO t2 (SELECT * FROM t1);
2318 EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
2320 -------------------------------
2323 Filter: ((a % 2) = 0)
2328 ----+----------------------------------
2329 0 | cfcd208495d565ef66e7dff9f98764da
2330 2 | c81e728d9d4c2f636f067f89cc14862c
2331 4 | a87ff679a2f3e71d9181a67b7542122c
2332 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2333 8 | c9f0f895fb98ab9159f51fd0297e236d
2334 10 | d3d9446802a44259755d38e6d163e820
2335 12 | c20ad4d76fe97759aa27a0c99bff6710
2336 14 | aab3238922bcc25a6f606eb525ffdc56
2337 16 | c74d97b01eae257e44aa9d5bade97baf
2338 18 | 6f4922f45568161a8cdf4ad2299f6d23
2339 20 | 98f13708210194c475687be6106a3b84
2343 EXPLAIN (COSTS OFF) SELECT * FROM t2;
2349 CREATE TABLE t3 AS SELECT * FROM t1;
2352 ----+----------------------------------
2353 0 | cfcd208495d565ef66e7dff9f98764da
2354 2 | c81e728d9d4c2f636f067f89cc14862c
2355 4 | a87ff679a2f3e71d9181a67b7542122c
2356 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2357 8 | c9f0f895fb98ab9159f51fd0297e236d
2358 10 | d3d9446802a44259755d38e6d163e820
2359 12 | c20ad4d76fe97759aa27a0c99bff6710
2360 14 | aab3238922bcc25a6f606eb525ffdc56
2361 16 | c74d97b01eae257e44aa9d5bade97baf
2362 18 | 6f4922f45568161a8cdf4ad2299f6d23
2363 20 | 98f13708210194c475687be6106a3b84
2367 SELECT * INTO t4 FROM t1;
2370 ----+----------------------------------
2371 0 | cfcd208495d565ef66e7dff9f98764da
2372 2 | c81e728d9d4c2f636f067f89cc14862c
2373 4 | a87ff679a2f3e71d9181a67b7542122c
2374 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2375 8 | c9f0f895fb98ab9159f51fd0297e236d
2376 10 | d3d9446802a44259755d38e6d163e820
2377 12 | c20ad4d76fe97759aa27a0c99bff6710
2378 14 | aab3238922bcc25a6f606eb525ffdc56
2379 16 | c74d97b01eae257e44aa9d5bade97baf
2380 18 | 6f4922f45568161a8cdf4ad2299f6d23
2381 20 | 98f13708210194c475687be6106a3b84
2388 SET SESSION AUTHORIZATION rls_regress_user0;
2389 CREATE TABLE blog (id integer, author text, post text);
2390 CREATE TABLE comment (blog_id integer, message text);
2391 GRANT ALL ON blog, comment TO rls_regress_user1;
2392 CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
2393 ALTER TABLE blog ENABLE ROW LEVEL SECURITY;
2394 INSERT INTO blog VALUES
2395 (1, 'alice', 'blog #1'),
2396 (2, 'bob', 'blog #1'),
2397 (3, 'alice', 'blog #2'),
2398 (4, 'alice', 'blog #3'),
2399 (5, 'john', 'blog #1');
2400 INSERT INTO comment VALUES
2407 SET SESSION AUTHORIZATION rls_regress_user1;
2408 -- Check RLS JOIN with Non-RLS.
2409 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
2410 id | author | message
2411 ----+--------+-------------
2413 2 | bob | who did it?
2416 -- Check Non-RLS JOIN with RLS.
2417 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
2418 id | author | message
2419 ----+--------+-------------
2421 2 | bob | who did it?
2424 SET SESSION AUTHORIZATION rls_regress_user0;
2425 CREATE POLICY comment_1 ON comment USING (blog_id < 4);
2426 ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
2427 SET SESSION AUTHORIZATION rls_regress_user1;
2428 -- Check RLS JOIN RLS
2429 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
2430 id | author | message
2431 ----+--------+-------------
2432 2 | bob | who did it?
2435 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
2436 id | author | message
2437 ----+--------+-------------
2438 2 | bob | who did it?
2441 SET SESSION AUTHORIZATION rls_regress_user0;
2442 DROP TABLE blog, comment;
2444 -- Default Deny Policy
2446 RESET SESSION AUTHORIZATION;
2447 DROP POLICY p2 ON t1;
2448 ALTER TABLE t1 OWNER TO rls_regress_user0;
2449 -- Check that default deny does not apply to superuser.
2450 RESET SESSION AUTHORIZATION;
2453 ----+----------------------------------
2454 1 | c4ca4238a0b923820dcc509a6f75849b
2455 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2456 5 | e4da3b7fbbce2345d7772b0674a318d5
2457 7 | 8f14e45fceea167a5a36dedd4bea2543
2458 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2459 11 | 6512bd43d9caa6e02c990b0a82652dca
2460 13 | c51ce410c124a10e0db5e4b97fc2af39
2461 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2462 17 | 70efdf2ec9b086079795c442636b55fb
2463 19 | 1f0e3dad99908345f7439f8ffabdffc4
2464 0 | cfcd208495d565ef66e7dff9f98764da
2465 2 | c81e728d9d4c2f636f067f89cc14862c
2466 4 | a87ff679a2f3e71d9181a67b7542122c
2467 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2468 8 | c9f0f895fb98ab9159f51fd0297e236d
2469 10 | d3d9446802a44259755d38e6d163e820
2470 12 | c20ad4d76fe97759aa27a0c99bff6710
2471 14 | aab3238922bcc25a6f606eb525ffdc56
2472 16 | c74d97b01eae257e44aa9d5bade97baf
2473 18 | 6f4922f45568161a8cdf4ad2299f6d23
2474 20 | 98f13708210194c475687be6106a3b84
2478 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2484 -- Check that default deny does not apply to table owner.
2485 SET SESSION AUTHORIZATION rls_regress_user0;
2488 ----+----------------------------------
2489 1 | c4ca4238a0b923820dcc509a6f75849b
2490 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2491 5 | e4da3b7fbbce2345d7772b0674a318d5
2492 7 | 8f14e45fceea167a5a36dedd4bea2543
2493 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2494 11 | 6512bd43d9caa6e02c990b0a82652dca
2495 13 | c51ce410c124a10e0db5e4b97fc2af39
2496 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2497 17 | 70efdf2ec9b086079795c442636b55fb
2498 19 | 1f0e3dad99908345f7439f8ffabdffc4
2499 0 | cfcd208495d565ef66e7dff9f98764da
2500 2 | c81e728d9d4c2f636f067f89cc14862c
2501 4 | a87ff679a2f3e71d9181a67b7542122c
2502 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2503 8 | c9f0f895fb98ab9159f51fd0297e236d
2504 10 | d3d9446802a44259755d38e6d163e820
2505 12 | c20ad4d76fe97759aa27a0c99bff6710
2506 14 | aab3238922bcc25a6f606eb525ffdc56
2507 16 | c74d97b01eae257e44aa9d5bade97baf
2508 18 | 6f4922f45568161a8cdf4ad2299f6d23
2509 20 | 98f13708210194c475687be6106a3b84
2513 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2519 -- Check that default deny does apply to superuser when RLS force.
2520 SET row_security TO FORCE;
2521 RESET SESSION AUTHORIZATION;
2527 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2529 --------------------------
2531 One-Time Filter: false
2534 -- Check that default deny does apply to table owner when RLS force.
2535 SET SESSION AUTHORIZATION rls_regress_user0;
2541 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2543 --------------------------
2545 One-Time Filter: false
2548 -- Check that default deny applies to non-owner/non-superuser when RLS on.
2549 SET SESSION AUTHORIZATION rls_regress_user1;
2550 SET row_security TO ON;
2556 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2558 --------------------------
2560 One-Time Filter: false
2563 SET SESSION AUTHORIZATION rls_regress_user1;
2569 EXPLAIN (COSTS OFF) SELECT * FROM t1;
2571 --------------------------
2573 One-Time Filter: false
2579 RESET SESSION AUTHORIZATION;
2580 DROP TABLE copy_t CASCADE;
2581 ERROR: table "copy_t" does not exist
2582 CREATE TABLE copy_t (a integer, b text);
2583 CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
2584 ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
2585 GRANT ALL ON copy_t TO rls_regress_user1, rls_regress_exempt_user;
2586 INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
2587 -- Check COPY TO as Superuser/owner.
2588 RESET SESSION AUTHORIZATION;
2589 SET row_security TO OFF;
2590 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
2591 0,cfcd208495d565ef66e7dff9f98764da
2592 1,c4ca4238a0b923820dcc509a6f75849b
2593 2,c81e728d9d4c2f636f067f89cc14862c
2594 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
2595 4,a87ff679a2f3e71d9181a67b7542122c
2596 5,e4da3b7fbbce2345d7772b0674a318d5
2597 6,1679091c5a880faf6fb5e6087eb1b2dc
2598 7,8f14e45fceea167a5a36dedd4bea2543
2599 8,c9f0f895fb98ab9159f51fd0297e236d
2600 9,45c48cce2e2d7fbdea1afc51c7c6ad26
2601 10,d3d9446802a44259755d38e6d163e820
2602 SET row_security TO ON;
2603 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
2604 0,cfcd208495d565ef66e7dff9f98764da
2605 1,c4ca4238a0b923820dcc509a6f75849b
2606 2,c81e728d9d4c2f636f067f89cc14862c
2607 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
2608 4,a87ff679a2f3e71d9181a67b7542122c
2609 5,e4da3b7fbbce2345d7772b0674a318d5
2610 6,1679091c5a880faf6fb5e6087eb1b2dc
2611 7,8f14e45fceea167a5a36dedd4bea2543
2612 8,c9f0f895fb98ab9159f51fd0297e236d
2613 9,45c48cce2e2d7fbdea1afc51c7c6ad26
2614 10,d3d9446802a44259755d38e6d163e820
2615 SET row_security TO FORCE;
2616 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
2617 0,cfcd208495d565ef66e7dff9f98764da
2618 2,c81e728d9d4c2f636f067f89cc14862c
2619 4,a87ff679a2f3e71d9181a67b7542122c
2620 6,1679091c5a880faf6fb5e6087eb1b2dc
2621 8,c9f0f895fb98ab9159f51fd0297e236d
2622 10,d3d9446802a44259755d38e6d163e820
2623 -- Check COPY TO as user with permissions.
2624 SET SESSION AUTHORIZATION rls_regress_user1;
2625 SET row_security TO OFF;
2626 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
2627 ERROR: insufficient privilege to bypass row security.
2628 SET row_security TO ON;
2629 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2630 0,cfcd208495d565ef66e7dff9f98764da
2631 2,c81e728d9d4c2f636f067f89cc14862c
2632 4,a87ff679a2f3e71d9181a67b7542122c
2633 6,1679091c5a880faf6fb5e6087eb1b2dc
2634 8,c9f0f895fb98ab9159f51fd0297e236d
2635 10,d3d9446802a44259755d38e6d163e820
2636 SET row_security TO FORCE;
2637 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2638 0,cfcd208495d565ef66e7dff9f98764da
2639 2,c81e728d9d4c2f636f067f89cc14862c
2640 4,a87ff679a2f3e71d9181a67b7542122c
2641 6,1679091c5a880faf6fb5e6087eb1b2dc
2642 8,c9f0f895fb98ab9159f51fd0297e236d
2643 10,d3d9446802a44259755d38e6d163e820
2644 -- Check COPY TO as user with permissions and BYPASSRLS
2645 SET SESSION AUTHORIZATION rls_regress_exempt_user;
2646 SET row_security TO OFF;
2647 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2648 0,cfcd208495d565ef66e7dff9f98764da
2649 1,c4ca4238a0b923820dcc509a6f75849b
2650 2,c81e728d9d4c2f636f067f89cc14862c
2651 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
2652 4,a87ff679a2f3e71d9181a67b7542122c
2653 5,e4da3b7fbbce2345d7772b0674a318d5
2654 6,1679091c5a880faf6fb5e6087eb1b2dc
2655 7,8f14e45fceea167a5a36dedd4bea2543
2656 8,c9f0f895fb98ab9159f51fd0297e236d
2657 9,45c48cce2e2d7fbdea1afc51c7c6ad26
2658 10,d3d9446802a44259755d38e6d163e820
2659 SET row_security TO ON;
2660 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2661 0,cfcd208495d565ef66e7dff9f98764da
2662 2,c81e728d9d4c2f636f067f89cc14862c
2663 4,a87ff679a2f3e71d9181a67b7542122c
2664 6,1679091c5a880faf6fb5e6087eb1b2dc
2665 8,c9f0f895fb98ab9159f51fd0297e236d
2666 10,d3d9446802a44259755d38e6d163e820
2667 SET row_security TO FORCE;
2668 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
2669 0,cfcd208495d565ef66e7dff9f98764da
2670 2,c81e728d9d4c2f636f067f89cc14862c
2671 4,a87ff679a2f3e71d9181a67b7542122c
2672 6,1679091c5a880faf6fb5e6087eb1b2dc
2673 8,c9f0f895fb98ab9159f51fd0297e236d
2674 10,d3d9446802a44259755d38e6d163e820
2675 -- Check COPY TO as user without permissions.SET row_security TO OFF;
2676 SET SESSION AUTHORIZATION rls_regress_user2;
2677 SET row_security TO OFF;
2678 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
2679 ERROR: insufficient privilege to bypass row security.
2680 SET row_security TO ON;
2681 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
2682 ERROR: permission denied for relation copy_t
2683 SET row_security TO FORCE;
2684 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
2685 ERROR: permission denied for relation copy_t
2686 -- Check COPY FROM as Superuser/owner.
2687 RESET SESSION AUTHORIZATION;
2688 SET row_security TO OFF;
2689 COPY copy_t FROM STDIN; --ok
2690 SET row_security TO ON;
2691 COPY copy_t FROM STDIN; --ok
2692 SET row_security TO FORCE;
2693 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2694 ERROR: COPY FROM not supported with row level security.
2695 HINT: Use direct INSERT statements instead.
2696 -- Check COPY FROM as user with permissions.
2697 SET SESSION AUTHORIZATION rls_regress_user1;
2698 SET row_security TO OFF;
2699 COPY copy_t FROM STDIN; --fail - insufficient privilege to bypass rls.
2700 ERROR: insufficient privilege to bypass row security.
2701 SET row_security TO ON;
2702 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2703 ERROR: COPY FROM not supported with row level security.
2704 HINT: Use direct INSERT statements instead.
2705 SET row_security TO FORCE;
2706 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2707 ERROR: COPY FROM not supported with row level security.
2708 HINT: Use direct INSERT statements instead.
2709 -- Check COPY TO as user with permissions and BYPASSRLS
2710 SET SESSION AUTHORIZATION rls_regress_exempt_user;
2711 SET row_security TO OFF;
2712 COPY copy_t FROM STDIN; --ok
2713 SET row_security TO ON;
2714 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2715 ERROR: COPY FROM not supported with row level security.
2716 HINT: Use direct INSERT statements instead.
2717 SET row_security TO FORCE;
2718 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
2719 ERROR: COPY FROM not supported with row level security.
2720 HINT: Use direct INSERT statements instead.
2721 -- Check COPY FROM as user without permissions.
2722 SET SESSION AUTHORIZATION rls_regress_user2;
2723 SET row_security TO OFF;
2724 COPY copy_t FROM STDIN; --fail - permission denied.
2725 ERROR: permission denied for relation copy_t
2726 SET row_security TO ON;
2727 COPY copy_t FROM STDIN; --fail - permission denied.
2728 ERROR: permission denied for relation copy_t
2729 SET row_security TO FORCE;
2730 COPY copy_t FROM STDIN; --fail - permission denied.
2731 ERROR: permission denied for relation copy_t
2732 RESET SESSION AUTHORIZATION;
2734 -- Check WHERE CURRENT OF
2735 SET SESSION AUTHORIZATION rls_regress_user0;
2736 CREATE TABLE current_check (currentid int, payload text, rlsuser text);
2737 GRANT ALL ON current_check TO PUBLIC;
2738 INSERT INTO current_check VALUES
2739 (1, 'abc', 'rls_regress_user1'),
2740 (2, 'bcd', 'rls_regress_user1'),
2741 (3, 'cde', 'rls_regress_user1'),
2742 (4, 'def', 'rls_regress_user1');
2743 CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
2744 CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
2745 CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user);
2746 ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
2747 SET SESSION AUTHORIZATION rls_regress_user1;
2748 -- Can SELECT even rows
2749 SELECT * FROM current_check;
2750 currentid | payload | rlsuser
2751 -----------+---------+-------------------
2752 2 | bcd | rls_regress_user1
2753 4 | def | rls_regress_user1
2756 -- Cannot UPDATE row 2
2757 UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *;
2758 currentid | payload | rlsuser
2759 -----------+---------+---------
2763 DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check;
2764 -- Returns rows that can be seen according to SELECT policy, like plain SELECT
2765 -- above (even rows)
2766 FETCH ABSOLUTE 1 FROM current_check_cursor;
2767 currentid | payload | rlsuser
2768 -----------+---------+-------------------
2769 2 | bcd | rls_regress_user1
2772 -- Still cannot UPDATE row 2 through cursor
2773 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
2774 currentid | payload | rlsuser
2775 -----------+---------+---------
2778 -- Can update row 4 through cursor, which is the next visible row
2779 FETCH RELATIVE 1 FROM current_check_cursor;
2780 currentid | payload | rlsuser
2781 -----------+---------+-------------------
2782 4 | def | rls_regress_user1
2785 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
2786 currentid | payload | rlsuser
2787 -----------+---------+-------------------
2788 4 | def_new | rls_regress_user1
2791 SELECT * FROM current_check;
2792 currentid | payload | rlsuser
2793 -----------+---------+-------------------
2794 2 | bcd | rls_regress_user1
2795 4 | def_new | rls_regress_user1
2798 -- Plan should be a subquery TID scan
2799 EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor;
2801 ---------------------------------------------------------------
2802 Update on current_check current_check_1
2803 -> Subquery Scan on current_check
2805 -> Tid Scan on current_check current_check_2
2806 TID Cond: CURRENT OF current_check_cursor
2807 Filter: (currentid = 4)
2810 -- Similarly can only delete row 4
2811 FETCH ABSOLUTE 1 FROM current_check_cursor;
2812 currentid | payload | rlsuser
2813 -----------+---------+-------------------
2814 2 | bcd | rls_regress_user1
2817 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
2818 currentid | payload | rlsuser
2819 -----------+---------+---------
2822 FETCH RELATIVE 1 FROM current_check_cursor;
2823 currentid | payload | rlsuser
2824 -----------+---------+-------------------
2825 4 | def | rls_regress_user1
2828 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
2829 currentid | payload | rlsuser
2830 -----------+---------+-------------------
2831 4 | def_new | rls_regress_user1
2834 SELECT * FROM current_check;
2835 currentid | payload | rlsuser
2836 -----------+---------+-------------------
2837 2 | bcd | rls_regress_user1
2842 -- Collation support
2845 SET row_security = force;
2846 CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
2847 CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
2848 ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
2849 SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
2855 SELECT * FROM coll_t;
2865 RESET SESSION AUTHORIZATION;
2866 -- Suppress NOTICE messages when doing a cascaded drop.
2867 SET client_min_messages TO 'warning';
2868 DROP SCHEMA rls_regress_schema CASCADE;
2869 RESET client_min_messages;
2870 DROP USER rls_regress_user0;
2871 DROP USER rls_regress_user1;
2872 DROP USER rls_regress_user2;
2873 DROP USER rls_regress_exempt_user;
2874 DROP ROLE rls_regress_group1;
2875 DROP ROLE rls_regress_group2;