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 regress_rls_alice;
8 DROP USER IF EXISTS regress_rls_bob;
9 DROP USER IF EXISTS regress_rls_carol;
10 DROP USER IF EXISTS regress_rls_dave;
11 DROP USER IF EXISTS regress_rls_exempt_user;
12 DROP ROLE IF EXISTS regress_rls_group1;
13 DROP ROLE IF EXISTS regress_rls_group2;
14 DROP SCHEMA IF EXISTS regress_rls_schema CASCADE;
15 RESET client_min_messages;
17 CREATE USER regress_rls_alice NOLOGIN;
18 CREATE USER regress_rls_bob NOLOGIN;
19 CREATE USER regress_rls_carol NOLOGIN;
20 CREATE USER regress_rls_dave NOLOGIN;
21 CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
22 CREATE ROLE regress_rls_group1 NOLOGIN;
23 CREATE ROLE regress_rls_group2 NOLOGIN;
24 GRANT regress_rls_group1 TO regress_rls_bob;
25 GRANT regress_rls_group2 TO regress_rls_carol;
26 CREATE SCHEMA regress_rls_schema;
27 GRANT ALL ON SCHEMA regress_rls_schema to public;
28 SET search_path = regress_rls_schema;
29 -- setup of malicious function
30 CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
31 COST 0.0000001 LANGUAGE plpgsql
32 AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
33 GRANT EXECUTE ON FUNCTION f_leak(text) TO public;
34 -- BASIC Row-Level Security Scenario
35 SET SESSION AUTHORIZATION regress_rls_alice;
36 CREATE TABLE uaccount (
37 pguser name primary key,
40 GRANT SELECT ON uaccount TO public;
41 INSERT INTO uaccount VALUES
42 ('regress_rls_alice', 99),
43 ('regress_rls_bob', 1),
44 ('regress_rls_carol', 2),
45 ('regress_rls_dave', 3);
46 CREATE TABLE category (
50 GRANT ALL ON category TO public;
51 INSERT INTO category VALUES
53 (22, 'science fiction'),
56 CREATE TABLE document (
58 cid int references category(cid),
63 GRANT ALL ON document TO public;
64 INSERT INTO document VALUES
65 ( 1, 11, 1, 'regress_rls_bob', 'my first novel'),
66 ( 2, 11, 2, 'regress_rls_bob', 'my second novel'),
67 ( 3, 22, 2, 'regress_rls_bob', 'my science fiction'),
68 ( 4, 44, 1, 'regress_rls_bob', 'my first manga'),
69 ( 5, 44, 2, 'regress_rls_bob', 'my second manga'),
70 ( 6, 22, 1, 'regress_rls_carol', 'great science fiction'),
71 ( 7, 33, 2, 'regress_rls_carol', 'great technology book'),
72 ( 8, 44, 1, 'regress_rls_carol', 'great manga'),
73 ( 9, 22, 1, 'regress_rls_dave', 'awesome science fiction'),
74 (10, 33, 2, 'regress_rls_dave', 'awesome technology book');
75 ALTER TABLE document ENABLE ROW LEVEL SECURITY;
76 -- user's security level must be higher than or equal to document's
77 CREATE POLICY p1 ON document AS PERMISSIVE
78 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
79 -- try to create a policy of bogus type
80 CREATE POLICY p1 ON document AS UGLY
81 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
82 ERROR: unrecognized row security option "ugly"
83 LINE 1: CREATE POLICY p1 ON document AS UGLY
85 HINT: Only PERMISSIVE or RESTRICTIVE policies are supported currently.
86 -- but Dave isn't allowed to anything at cid 50 or above
87 -- this is to make sure that we sort the policies by name first
88 -- when applying WITH CHECK, a later INSERT by Dave should fail due
90 CREATE POLICY p2r ON document AS RESTRICTIVE TO regress_rls_dave
91 USING (cid <> 44 AND cid < 50);
92 -- and Dave isn't allowed to see manga documents
93 CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
97 Schema | Name | Type | Access privileges | Column privileges | Policies
98 --------------------+----------+-------+---------------------------------------------+-------------------+--------------------------------------------
99 regress_rls_schema | category | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
100 | | | =arwdDxt/regress_rls_alice | |
101 regress_rls_schema | document | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p1: +
102 | | | =arwdDxt/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv +
103 | | | | | FROM uaccount +
104 | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+
105 | | | | | p2r (RESTRICTIVE): +
106 | | | | | (u): ((cid <> 44) AND (cid < 50)) +
107 | | | | | to: regress_rls_dave +
108 | | | | | p1r (RESTRICTIVE): +
109 | | | | | (u): (cid <> 44) +
110 | | | | | to: regress_rls_dave
111 regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
112 | | | =r/regress_rls_alice | |
116 Table "regress_rls_schema.document"
117 Column | Type | Collation | Nullable | Default
118 ---------+---------+-----------+----------+---------
119 did | integer | | not null |
121 dlevel | integer | | not null |
125 "document_pkey" PRIMARY KEY, btree (did)
126 Foreign-key constraints:
127 "document_cid_fkey" FOREIGN KEY (cid) REFERENCES category(cid)
130 USING ((dlevel <= ( SELECT uaccount.seclv
132 WHERE (uaccount.pguser = CURRENT_USER))))
133 POLICY "p1r" AS RESTRICTIVE
136 POLICY "p2r" AS RESTRICTIVE
138 USING (((cid <> 44) AND (cid < 50)))
140 SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname;
141 schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
142 --------------------+-----------+------------+-------------+--------------------+-----+--------------------------------------------+------------
143 regress_rls_schema | document | p1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
144 | | | | | | FROM uaccount +|
145 | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
146 regress_rls_schema | document | p1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 44) |
147 regress_rls_schema | document | p2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 44) AND (cid < 50)) |
150 -- viewpoint from regress_rls_bob
151 SET SESSION AUTHORIZATION regress_rls_bob;
152 SET row_security TO ON;
153 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
154 NOTICE: f_leak => my first novel
155 NOTICE: f_leak => my first manga
156 NOTICE: f_leak => great science fiction
157 NOTICE: f_leak => great manga
158 NOTICE: f_leak => awesome science fiction
159 did | cid | dlevel | dauthor | dtitle
160 -----+-----+--------+-------------------+-------------------------
161 1 | 11 | 1 | regress_rls_bob | my first novel
162 4 | 44 | 1 | regress_rls_bob | my first manga
163 6 | 22 | 1 | regress_rls_carol | great science fiction
164 8 | 44 | 1 | regress_rls_carol | great manga
165 9 | 22 | 1 | regress_rls_dave | awesome science fiction
168 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
169 NOTICE: f_leak => my first novel
170 NOTICE: f_leak => my first manga
171 NOTICE: f_leak => great science fiction
172 NOTICE: f_leak => great manga
173 NOTICE: f_leak => awesome science fiction
174 cid | did | dlevel | dauthor | dtitle | cname
175 -----+-----+--------+-------------------+-------------------------+-----------------
176 11 | 1 | 1 | regress_rls_bob | my first novel | novel
177 44 | 4 | 1 | regress_rls_bob | my first manga | manga
178 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
179 44 | 8 | 1 | regress_rls_carol | great manga | manga
180 22 | 9 | 1 | regress_rls_dave | awesome science fiction | science fiction
183 -- try a sampled version
184 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
185 WHERE f_leak(dtitle) ORDER BY did;
186 NOTICE: f_leak => my first manga
187 NOTICE: f_leak => great science fiction
188 NOTICE: f_leak => great manga
189 NOTICE: f_leak => awesome science fiction
190 did | cid | dlevel | dauthor | dtitle
191 -----+-----+--------+-------------------+-------------------------
192 4 | 44 | 1 | regress_rls_bob | my first manga
193 6 | 22 | 1 | regress_rls_carol | great science fiction
194 8 | 44 | 1 | regress_rls_carol | great manga
195 9 | 22 | 1 | regress_rls_dave | awesome science fiction
198 -- viewpoint from regress_rls_carol
199 SET SESSION AUTHORIZATION regress_rls_carol;
200 SELECT * FROM document 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 NOTICE: f_leak => great science fiction
207 NOTICE: f_leak => great technology book
208 NOTICE: f_leak => great manga
209 NOTICE: f_leak => awesome science fiction
210 NOTICE: f_leak => awesome technology book
211 did | cid | dlevel | dauthor | dtitle
212 -----+-----+--------+-------------------+-------------------------
213 1 | 11 | 1 | regress_rls_bob | my first novel
214 2 | 11 | 2 | regress_rls_bob | my second novel
215 3 | 22 | 2 | regress_rls_bob | my science fiction
216 4 | 44 | 1 | regress_rls_bob | my first manga
217 5 | 44 | 2 | regress_rls_bob | my second manga
218 6 | 22 | 1 | regress_rls_carol | great science fiction
219 7 | 33 | 2 | regress_rls_carol | great technology book
220 8 | 44 | 1 | regress_rls_carol | great manga
221 9 | 22 | 1 | regress_rls_dave | awesome science fiction
222 10 | 33 | 2 | regress_rls_dave | awesome technology book
225 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
226 NOTICE: f_leak => my first novel
227 NOTICE: f_leak => my second novel
228 NOTICE: f_leak => my science fiction
229 NOTICE: f_leak => my first manga
230 NOTICE: f_leak => my second manga
231 NOTICE: f_leak => great science fiction
232 NOTICE: f_leak => great technology book
233 NOTICE: f_leak => great manga
234 NOTICE: f_leak => awesome science fiction
235 NOTICE: f_leak => awesome technology book
236 cid | did | dlevel | dauthor | dtitle | cname
237 -----+-----+--------+-------------------+-------------------------+-----------------
238 11 | 1 | 1 | regress_rls_bob | my first novel | novel
239 11 | 2 | 2 | regress_rls_bob | my second novel | novel
240 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction
241 44 | 4 | 1 | regress_rls_bob | my first manga | manga
242 44 | 5 | 2 | regress_rls_bob | my second manga | manga
243 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
244 33 | 7 | 2 | regress_rls_carol | great technology book | technology
245 44 | 8 | 1 | regress_rls_carol | great manga | manga
246 22 | 9 | 1 | regress_rls_dave | awesome science fiction | science fiction
247 33 | 10 | 2 | regress_rls_dave | awesome technology book | technology
250 -- try a sampled version
251 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
252 WHERE f_leak(dtitle) ORDER BY did;
253 NOTICE: f_leak => my first manga
254 NOTICE: f_leak => my second manga
255 NOTICE: f_leak => great science fiction
256 NOTICE: f_leak => great manga
257 NOTICE: f_leak => awesome science fiction
258 did | cid | dlevel | dauthor | dtitle
259 -----+-----+--------+-------------------+-------------------------
260 4 | 44 | 1 | regress_rls_bob | my first manga
261 5 | 44 | 2 | regress_rls_bob | my second manga
262 6 | 22 | 1 | regress_rls_carol | great science fiction
263 8 | 44 | 1 | regress_rls_carol | great manga
264 9 | 22 | 1 | regress_rls_dave | awesome science fiction
267 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
269 ----------------------------------------------------
271 Filter: ((dlevel <= $0) AND f_leak(dtitle))
272 InitPlan 1 (returns $0)
273 -> Index Scan using uaccount_pkey on uaccount
274 Index Cond: (pguser = CURRENT_USER)
277 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
279 -----------------------------------------------------------
281 Hash Cond: (category.cid = document.cid)
282 InitPlan 1 (returns $0)
283 -> Index Scan using uaccount_pkey on uaccount
284 Index Cond: (pguser = CURRENT_USER)
285 -> Seq Scan on category
287 -> Seq Scan on document
288 Filter: ((dlevel <= $0) AND f_leak(dtitle))
291 -- viewpoint from regress_rls_dave
292 SET SESSION AUTHORIZATION regress_rls_dave;
293 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
294 NOTICE: f_leak => my first novel
295 NOTICE: f_leak => my second novel
296 NOTICE: f_leak => my science fiction
297 NOTICE: f_leak => great science fiction
298 NOTICE: f_leak => great technology book
299 NOTICE: f_leak => awesome science fiction
300 NOTICE: f_leak => awesome technology book
301 did | cid | dlevel | dauthor | dtitle
302 -----+-----+--------+-------------------+-------------------------
303 1 | 11 | 1 | regress_rls_bob | my first novel
304 2 | 11 | 2 | regress_rls_bob | my second novel
305 3 | 22 | 2 | regress_rls_bob | my science fiction
306 6 | 22 | 1 | regress_rls_carol | great science fiction
307 7 | 33 | 2 | regress_rls_carol | great technology book
308 9 | 22 | 1 | regress_rls_dave | awesome science fiction
309 10 | 33 | 2 | regress_rls_dave | awesome technology book
312 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
313 NOTICE: f_leak => my first novel
314 NOTICE: f_leak => my second novel
315 NOTICE: f_leak => my science fiction
316 NOTICE: f_leak => great science fiction
317 NOTICE: f_leak => great technology book
318 NOTICE: f_leak => awesome science fiction
319 NOTICE: f_leak => awesome technology book
320 cid | did | dlevel | dauthor | dtitle | cname
321 -----+-----+--------+-------------------+-------------------------+-----------------
322 11 | 1 | 1 | regress_rls_bob | my first novel | novel
323 11 | 2 | 2 | regress_rls_bob | my second novel | novel
324 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction
325 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
326 33 | 7 | 2 | regress_rls_carol | great technology book | technology
327 22 | 9 | 1 | regress_rls_dave | awesome science fiction | science fiction
328 33 | 10 | 2 | regress_rls_dave | awesome technology book | technology
331 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
333 ----------------------------------------------------------------------------------------------
335 Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= $0) AND f_leak(dtitle))
336 InitPlan 1 (returns $0)
337 -> Index Scan using uaccount_pkey on uaccount
338 Index Cond: (pguser = CURRENT_USER)
341 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
343 ----------------------------------------------------------------------------------------------------------
345 Hash Cond: (category.cid = document.cid)
346 InitPlan 1 (returns $0)
347 -> Index Scan using uaccount_pkey on uaccount
348 Index Cond: (pguser = CURRENT_USER)
349 -> Seq Scan on category
351 -> Seq Scan on document
352 Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= $0) AND f_leak(dtitle))
355 -- 44 would technically fail for both p2r and p1r, but we should get an error
356 -- back from p1r for this because it sorts first
357 INSERT INTO document VALUES (100, 44, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail
358 ERROR: new row violates row-level security policy "p1r" for table "document"
359 -- Just to see a p2r error
360 INSERT INTO document VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail
361 ERROR: new row violates row-level security policy "p2r" for table "document"
362 -- only owner can change policies
363 ALTER POLICY p1 ON document USING (true); --fail
364 ERROR: must be owner of table document
365 DROP POLICY p1 ON document; --fail
366 ERROR: must be owner of relation document
367 SET SESSION AUTHORIZATION regress_rls_alice;
368 ALTER POLICY p1 ON document USING (dauthor = current_user);
369 -- viewpoint from regress_rls_bob again
370 SET SESSION AUTHORIZATION regress_rls_bob;
371 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
372 NOTICE: f_leak => my first novel
373 NOTICE: f_leak => my second novel
374 NOTICE: f_leak => my science fiction
375 NOTICE: f_leak => my first manga
376 NOTICE: f_leak => my second manga
377 did | cid | dlevel | dauthor | dtitle
378 -----+-----+--------+-----------------+--------------------
379 1 | 11 | 1 | regress_rls_bob | my first novel
380 2 | 11 | 2 | regress_rls_bob | my second novel
381 3 | 22 | 2 | regress_rls_bob | my science fiction
382 4 | 44 | 1 | regress_rls_bob | my first manga
383 5 | 44 | 2 | regress_rls_bob | my second manga
386 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
387 NOTICE: f_leak => my first novel
388 NOTICE: f_leak => my second novel
389 NOTICE: f_leak => my science fiction
390 NOTICE: f_leak => my first manga
391 NOTICE: f_leak => my second manga
392 cid | did | dlevel | dauthor | dtitle | cname
393 -----+-----+--------+-----------------+--------------------+-----------------
394 11 | 1 | 1 | regress_rls_bob | my first novel | novel
395 11 | 2 | 2 | regress_rls_bob | my second novel | novel
396 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction
397 44 | 4 | 1 | regress_rls_bob | my first manga | manga
398 44 | 5 | 2 | regress_rls_bob | my second manga | manga
401 -- viewpoint from rls_regres_carol again
402 SET SESSION AUTHORIZATION regress_rls_carol;
403 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
404 NOTICE: f_leak => great science fiction
405 NOTICE: f_leak => great technology book
406 NOTICE: f_leak => great manga
407 did | cid | dlevel | dauthor | dtitle
408 -----+-----+--------+-------------------+-----------------------
409 6 | 22 | 1 | regress_rls_carol | great science fiction
410 7 | 33 | 2 | regress_rls_carol | great technology book
411 8 | 44 | 1 | regress_rls_carol | great manga
414 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
415 NOTICE: f_leak => great science fiction
416 NOTICE: f_leak => great technology book
417 NOTICE: f_leak => great manga
418 cid | did | dlevel | dauthor | dtitle | cname
419 -----+-----+--------+-------------------+-----------------------+-----------------
420 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
421 33 | 7 | 2 | regress_rls_carol | great technology book | technology
422 44 | 8 | 1 | regress_rls_carol | great manga | manga
425 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
427 ---------------------------------------------------------
429 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
432 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
434 ---------------------------------------------------------------
436 -> Seq Scan on document
437 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
438 -> Index Scan using category_pkey on category
439 Index Cond: (cid = document.cid)
442 -- interaction of FK/PK constraints
443 SET SESSION AUTHORIZATION regress_rls_alice;
444 CREATE POLICY p2 ON category
445 USING (CASE WHEN current_user = 'regress_rls_bob' THEN cid IN (11, 33)
446 WHEN current_user = 'regress_rls_carol' THEN cid IN (22, 44)
448 ALTER TABLE category ENABLE ROW LEVEL SECURITY;
449 -- cannot delete PK referenced by invisible FK
450 SET SESSION AUTHORIZATION regress_rls_bob;
451 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid;
452 did | cid | dlevel | dauthor | dtitle | cid | cname
453 -----+-----+--------+-----------------+--------------------+-----+------------
454 1 | 11 | 1 | regress_rls_bob | my first novel | 11 | novel
455 2 | 11 | 2 | regress_rls_bob | my second novel | 11 | novel
456 3 | 22 | 2 | regress_rls_bob | my science fiction | |
457 4 | 44 | 1 | regress_rls_bob | my first manga | |
458 5 | 44 | 2 | regress_rls_bob | my second manga | |
459 | | | | | 33 | technology
462 DELETE FROM category WHERE cid = 33; -- fails with FK violation
463 ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"
464 DETAIL: Key is still referenced from table "document".
465 -- can insert FK referencing invisible PK
466 SET SESSION AUTHORIZATION regress_rls_carol;
467 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid;
468 did | cid | dlevel | dauthor | dtitle | cid | cname
469 -----+-----+--------+-------------------+-----------------------+-----+-----------------
470 6 | 22 | 1 | regress_rls_carol | great science fiction | 22 | science fiction
471 7 | 33 | 2 | regress_rls_carol | great technology book | |
472 8 | 44 | 1 | regress_rls_carol | great manga | 44 | manga
475 INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge');
476 -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
477 SET SESSION AUTHORIZATION regress_rls_bob;
478 INSERT INTO document VALUES (8, 44, 1, 'regress_rls_bob', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
479 ERROR: duplicate key value violates unique constraint "document_pkey"
480 SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
481 did | cid | dlevel | dauthor | dtitle
482 -----+-----+--------+---------+--------
485 -- RLS policies are checked before constraints
486 INSERT INTO document VALUES (8, 44, 1, 'regress_rls_carol', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation
487 ERROR: new row violates row-level security policy for table "document"
488 UPDATE document SET did = 8, dauthor = 'regress_rls_carol' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation
489 ERROR: new row violates row-level security policy for table "document"
490 -- database superuser does bypass RLS policy when enabled
491 RESET SESSION AUTHORIZATION;
492 SET row_security TO ON;
493 SELECT * FROM document;
494 did | cid | dlevel | dauthor | dtitle
495 -----+-----+--------+-------------------+-------------------------
496 1 | 11 | 1 | regress_rls_bob | my first novel
497 2 | 11 | 2 | regress_rls_bob | my second novel
498 3 | 22 | 2 | regress_rls_bob | my science fiction
499 4 | 44 | 1 | regress_rls_bob | my first manga
500 5 | 44 | 2 | regress_rls_bob | my second manga
501 6 | 22 | 1 | regress_rls_carol | great science fiction
502 7 | 33 | 2 | regress_rls_carol | great technology book
503 8 | 44 | 1 | regress_rls_carol | great manga
504 9 | 22 | 1 | regress_rls_dave | awesome science fiction
505 10 | 33 | 2 | regress_rls_dave | awesome technology book
506 11 | 33 | 1 | regress_rls_carol | hoge
509 SELECT * FROM category;
511 -----+-----------------
518 -- database superuser does bypass RLS policy when disabled
519 RESET SESSION AUTHORIZATION;
520 SET row_security TO OFF;
521 SELECT * FROM document;
522 did | cid | dlevel | dauthor | dtitle
523 -----+-----+--------+-------------------+-------------------------
524 1 | 11 | 1 | regress_rls_bob | my first novel
525 2 | 11 | 2 | regress_rls_bob | my second novel
526 3 | 22 | 2 | regress_rls_bob | my science fiction
527 4 | 44 | 1 | regress_rls_bob | my first manga
528 5 | 44 | 2 | regress_rls_bob | my second manga
529 6 | 22 | 1 | regress_rls_carol | great science fiction
530 7 | 33 | 2 | regress_rls_carol | great technology book
531 8 | 44 | 1 | regress_rls_carol | great manga
532 9 | 22 | 1 | regress_rls_dave | awesome science fiction
533 10 | 33 | 2 | regress_rls_dave | awesome technology book
534 11 | 33 | 1 | regress_rls_carol | hoge
537 SELECT * FROM category;
539 -----+-----------------
546 -- database non-superuser with bypass privilege can bypass RLS policy when disabled
547 SET SESSION AUTHORIZATION regress_rls_exempt_user;
548 SET row_security TO OFF;
549 SELECT * FROM document;
550 did | cid | dlevel | dauthor | dtitle
551 -----+-----+--------+-------------------+-------------------------
552 1 | 11 | 1 | regress_rls_bob | my first novel
553 2 | 11 | 2 | regress_rls_bob | my second novel
554 3 | 22 | 2 | regress_rls_bob | my science fiction
555 4 | 44 | 1 | regress_rls_bob | my first manga
556 5 | 44 | 2 | regress_rls_bob | my second manga
557 6 | 22 | 1 | regress_rls_carol | great science fiction
558 7 | 33 | 2 | regress_rls_carol | great technology book
559 8 | 44 | 1 | regress_rls_carol | great manga
560 9 | 22 | 1 | regress_rls_dave | awesome science fiction
561 10 | 33 | 2 | regress_rls_dave | awesome technology book
562 11 | 33 | 1 | regress_rls_carol | hoge
565 SELECT * FROM category;
567 -----+-----------------
574 -- RLS policy does not apply to table owner when RLS enabled.
575 SET SESSION AUTHORIZATION regress_rls_alice;
576 SET row_security TO ON;
577 SELECT * FROM document;
578 did | cid | dlevel | dauthor | dtitle
579 -----+-----+--------+-------------------+-------------------------
580 1 | 11 | 1 | regress_rls_bob | my first novel
581 2 | 11 | 2 | regress_rls_bob | my second novel
582 3 | 22 | 2 | regress_rls_bob | my science fiction
583 4 | 44 | 1 | regress_rls_bob | my first manga
584 5 | 44 | 2 | regress_rls_bob | my second manga
585 6 | 22 | 1 | regress_rls_carol | great science fiction
586 7 | 33 | 2 | regress_rls_carol | great technology book
587 8 | 44 | 1 | regress_rls_carol | great manga
588 9 | 22 | 1 | regress_rls_dave | awesome science fiction
589 10 | 33 | 2 | regress_rls_dave | awesome technology book
590 11 | 33 | 1 | regress_rls_carol | hoge
593 SELECT * FROM category;
595 -----+-----------------
602 -- RLS policy does not apply to table owner when RLS disabled.
603 SET SESSION AUTHORIZATION regress_rls_alice;
604 SET row_security TO OFF;
605 SELECT * FROM document;
606 did | cid | dlevel | dauthor | dtitle
607 -----+-----+--------+-------------------+-------------------------
608 1 | 11 | 1 | regress_rls_bob | my first novel
609 2 | 11 | 2 | regress_rls_bob | my second novel
610 3 | 22 | 2 | regress_rls_bob | my science fiction
611 4 | 44 | 1 | regress_rls_bob | my first manga
612 5 | 44 | 2 | regress_rls_bob | my second manga
613 6 | 22 | 1 | regress_rls_carol | great science fiction
614 7 | 33 | 2 | regress_rls_carol | great technology book
615 8 | 44 | 1 | regress_rls_carol | great manga
616 9 | 22 | 1 | regress_rls_dave | awesome science fiction
617 10 | 33 | 2 | regress_rls_dave | awesome technology book
618 11 | 33 | 1 | regress_rls_carol | hoge
621 SELECT * FROM category;
623 -----+-----------------
631 -- Table inheritance and RLS policy
633 SET SESSION AUTHORIZATION regress_rls_alice;
634 SET row_security TO ON;
635 CREATE TABLE t1 (id int not null primary key, a int, junk1 text, b text);
636 ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor
637 GRANT ALL ON t1 TO public;
638 COPY t1 FROM stdin WITH ;
639 CREATE TABLE t2 (c float) INHERITS (t1);
640 GRANT ALL ON t2 TO public;
642 CREATE TABLE t3 (id int not null primary key, c text, b text, a int);
643 ALTER TABLE t3 INHERIT t1;
644 GRANT ALL ON t3 TO public;
645 COPY t3(id, a,b,c) FROM stdin;
646 CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number
647 CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number
648 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
649 ALTER TABLE t2 ENABLE ROW LEVEL SECURITY;
650 SET SESSION AUTHORIZATION regress_rls_bob;
661 EXPLAIN (COSTS OFF) SELECT * FROM t1;
663 -------------------------------
666 Filter: ((a % 2) = 0)
668 Filter: ((a % 2) = 0)
670 Filter: ((a % 2) = 0)
673 SELECT * FROM t1 WHERE f_leak(b);
674 NOTICE: f_leak => bbb
675 NOTICE: f_leak => dad
676 NOTICE: f_leak => bcd
677 NOTICE: f_leak => def
678 NOTICE: f_leak => yyy
688 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
690 -----------------------------------------------
693 Filter: (((a % 2) = 0) AND f_leak(b))
695 Filter: (((a % 2) = 0) AND f_leak(b))
697 Filter: (((a % 2) = 0) AND f_leak(b))
700 -- reference to system column
701 SELECT tableoid::regclass, * FROM t1;
702 tableoid | id | a | b
703 ----------+-----+---+-----
711 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
713 -------------------------------
716 Filter: ((a % 2) = 0)
718 Filter: ((a % 2) = 0)
720 Filter: ((a % 2) = 0)
723 -- reference to whole-row reference
724 SELECT *, t1 FROM t1;
726 -----+---+-----+-------------
727 102 | 2 | bbb | (102,2,bbb)
728 104 | 4 | dad | (104,4,dad)
729 202 | 2 | bcd | (202,2,bcd)
730 204 | 4 | def | (204,4,def)
731 302 | 2 | yyy | (302,2,yyy)
734 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
736 -------------------------------
739 Filter: ((a % 2) = 0)
741 Filter: ((a % 2) = 0)
743 Filter: ((a % 2) = 0)
746 -- for share/update lock
747 SELECT * FROM t1 FOR SHARE;
757 EXPLAIN (COSTS OFF) SELECT * FROM t1 FOR SHARE;
759 -------------------------------------
763 Filter: ((a % 2) = 0)
765 Filter: ((a % 2) = 0)
767 Filter: ((a % 2) = 0)
770 SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
771 NOTICE: f_leak => bbb
772 NOTICE: f_leak => dad
773 NOTICE: f_leak => bcd
774 NOTICE: f_leak => def
775 NOTICE: f_leak => yyy
785 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
787 -----------------------------------------------------
791 Filter: (((a % 2) = 0) AND f_leak(b))
793 Filter: (((a % 2) = 0) AND f_leak(b))
795 Filter: (((a % 2) = 0) AND f_leak(b))
799 SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3;
809 EXPLAIN (COSTS OFF) SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3;
811 -------------------------------
814 Filter: ((a % 2) = 1)
818 -- superuser is allowed to bypass RLS checks
819 RESET SESSION AUTHORIZATION;
820 SET row_security TO OFF;
821 SELECT * FROM t1 WHERE f_leak(b);
822 NOTICE: f_leak => aba
823 NOTICE: f_leak => bbb
824 NOTICE: f_leak => ccc
825 NOTICE: f_leak => dad
826 NOTICE: f_leak => abc
827 NOTICE: f_leak => bcd
828 NOTICE: f_leak => cde
829 NOTICE: f_leak => def
830 NOTICE: f_leak => xxx
831 NOTICE: f_leak => yyy
832 NOTICE: f_leak => zzz
848 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
850 ---------------------------
860 -- non-superuser with bypass privilege can bypass RLS policy when disabled
861 SET SESSION AUTHORIZATION regress_rls_exempt_user;
862 SET row_security TO OFF;
863 SELECT * FROM t1 WHERE f_leak(b);
864 NOTICE: f_leak => aba
865 NOTICE: f_leak => bbb
866 NOTICE: f_leak => ccc
867 NOTICE: f_leak => dad
868 NOTICE: f_leak => abc
869 NOTICE: f_leak => bcd
870 NOTICE: f_leak => cde
871 NOTICE: f_leak => def
872 NOTICE: f_leak => xxx
873 NOTICE: f_leak => yyy
874 NOTICE: f_leak => zzz
890 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
892 ---------------------------
903 -- Partitioned Tables
905 SET SESSION AUTHORIZATION regress_rls_alice;
906 CREATE TABLE part_document (
912 ) PARTITION BY RANGE (cid);
913 GRANT ALL ON part_document TO public;
914 -- Create partitions for document categories
915 CREATE TABLE part_document_fiction PARTITION OF part_document FOR VALUES FROM (11) to (12);
916 CREATE TABLE part_document_satire PARTITION OF part_document FOR VALUES FROM (55) to (56);
917 CREATE TABLE part_document_nonfiction PARTITION OF part_document FOR VALUES FROM (99) to (100);
918 GRANT ALL ON part_document_fiction TO public;
919 GRANT ALL ON part_document_satire TO public;
920 GRANT ALL ON part_document_nonfiction TO public;
921 INSERT INTO part_document VALUES
922 ( 1, 11, 1, 'regress_rls_bob', 'my first novel'),
923 ( 2, 11, 2, 'regress_rls_bob', 'my second novel'),
924 ( 3, 99, 2, 'regress_rls_bob', 'my science textbook'),
925 ( 4, 55, 1, 'regress_rls_bob', 'my first satire'),
926 ( 5, 99, 2, 'regress_rls_bob', 'my history book'),
927 ( 6, 11, 1, 'regress_rls_carol', 'great science fiction'),
928 ( 7, 99, 2, 'regress_rls_carol', 'great technology book'),
929 ( 8, 55, 2, 'regress_rls_carol', 'great satire'),
930 ( 9, 11, 1, 'regress_rls_dave', 'awesome science fiction'),
931 (10, 99, 2, 'regress_rls_dave', 'awesome technology book');
932 ALTER TABLE part_document ENABLE ROW LEVEL SECURITY;
933 -- Create policy on parent
934 -- user's security level must be higher than or equal to document's
935 CREATE POLICY pp1 ON part_document AS PERMISSIVE
936 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
937 -- Dave is only allowed to see cid < 55
938 CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave
941 Partitioned table "regress_rls_schema.part_document"
942 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
943 ---------+---------+-----------+----------+---------+----------+--------------+-------------
944 did | integer | | | | plain | |
945 cid | integer | | | | plain | |
946 dlevel | integer | | not null | | plain | |
947 dauthor | name | | | | plain | |
948 dtitle | text | | | | extended | |
949 Partition key: RANGE (cid)
952 USING ((dlevel <= ( SELECT uaccount.seclv
954 WHERE (uaccount.pguser = CURRENT_USER))))
955 POLICY "pp1r" AS RESTRICTIVE
958 Partitions: part_document_fiction FOR VALUES FROM (11) TO (12),
959 part_document_nonfiction FOR VALUES FROM (99) TO (100),
960 part_document_satire FOR VALUES FROM (55) TO (56)
962 SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename like '%part_document%' ORDER BY policyname;
963 schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check
964 --------------------+---------------+------------+-------------+--------------------+-----+--------------------------------------------+------------
965 regress_rls_schema | part_document | pp1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +|
966 | | | | | | FROM uaccount +|
967 | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) |
968 regress_rls_schema | part_document | pp1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid < 55) |
971 -- viewpoint from regress_rls_bob
972 SET SESSION AUTHORIZATION regress_rls_bob;
973 SET row_security TO ON;
974 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
975 NOTICE: f_leak => my first novel
976 NOTICE: f_leak => great science fiction
977 NOTICE: f_leak => awesome science fiction
978 NOTICE: f_leak => my first satire
979 did | cid | dlevel | dauthor | dtitle
980 -----+-----+--------+-------------------+-------------------------
981 1 | 11 | 1 | regress_rls_bob | my first novel
982 4 | 55 | 1 | regress_rls_bob | my first satire
983 6 | 11 | 1 | regress_rls_carol | great science fiction
984 9 | 11 | 1 | regress_rls_dave | awesome science fiction
987 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
989 -----------------------------------------------------
991 InitPlan 1 (returns $0)
992 -> Index Scan using uaccount_pkey on uaccount
993 Index Cond: (pguser = CURRENT_USER)
994 -> Seq Scan on part_document_fiction
995 Filter: ((dlevel <= $0) AND f_leak(dtitle))
996 -> Seq Scan on part_document_satire
997 Filter: ((dlevel <= $0) AND f_leak(dtitle))
998 -> Seq Scan on part_document_nonfiction
999 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1002 -- viewpoint from regress_rls_carol
1003 SET SESSION AUTHORIZATION regress_rls_carol;
1004 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1005 NOTICE: f_leak => my first novel
1006 NOTICE: f_leak => my second novel
1007 NOTICE: f_leak => great science fiction
1008 NOTICE: f_leak => awesome science fiction
1009 NOTICE: f_leak => my first satire
1010 NOTICE: f_leak => great satire
1011 NOTICE: f_leak => my science textbook
1012 NOTICE: f_leak => my history book
1013 NOTICE: f_leak => great technology book
1014 NOTICE: f_leak => awesome technology book
1015 did | cid | dlevel | dauthor | dtitle
1016 -----+-----+--------+-------------------+-------------------------
1017 1 | 11 | 1 | regress_rls_bob | my first novel
1018 2 | 11 | 2 | regress_rls_bob | my second novel
1019 3 | 99 | 2 | regress_rls_bob | my science textbook
1020 4 | 55 | 1 | regress_rls_bob | my first satire
1021 5 | 99 | 2 | regress_rls_bob | my history book
1022 6 | 11 | 1 | regress_rls_carol | great science fiction
1023 7 | 99 | 2 | regress_rls_carol | great technology book
1024 8 | 55 | 2 | regress_rls_carol | great satire
1025 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1026 10 | 99 | 2 | regress_rls_dave | awesome technology book
1029 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1031 -----------------------------------------------------
1033 InitPlan 1 (returns $0)
1034 -> Index Scan using uaccount_pkey on uaccount
1035 Index Cond: (pguser = CURRENT_USER)
1036 -> Seq Scan on part_document_fiction
1037 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1038 -> Seq Scan on part_document_satire
1039 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1040 -> Seq Scan on part_document_nonfiction
1041 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1044 -- viewpoint from regress_rls_dave
1045 SET SESSION AUTHORIZATION regress_rls_dave;
1046 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1047 NOTICE: f_leak => my first novel
1048 NOTICE: f_leak => my second novel
1049 NOTICE: f_leak => great science fiction
1050 NOTICE: f_leak => awesome science fiction
1051 did | cid | dlevel | dauthor | dtitle
1052 -----+-----+--------+-------------------+-------------------------
1053 1 | 11 | 1 | regress_rls_bob | my first novel
1054 2 | 11 | 2 | regress_rls_bob | my second novel
1055 6 | 11 | 1 | regress_rls_carol | great science fiction
1056 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1059 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1061 --------------------------------------------------------------
1062 Seq Scan on part_document_fiction
1063 Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
1064 InitPlan 1 (returns $0)
1065 -> Index Scan using uaccount_pkey on uaccount
1066 Index Cond: (pguser = CURRENT_USER)
1070 INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail
1071 ERROR: new row violates row-level security policy for table "part_document"
1073 INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail
1074 ERROR: new row violates row-level security policy "pp1r" for table "part_document"
1075 -- Show that RLS policy does not apply for direct inserts to children
1076 -- This should fail with RLS POLICY pp1r violation.
1077 INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
1078 ERROR: new row violates row-level security policy "pp1r" for table "part_document"
1079 -- But this should succeed.
1080 INSERT INTO part_document_satire VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- success
1081 -- We still cannot see the row using the parent
1082 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1083 NOTICE: f_leak => my first novel
1084 NOTICE: f_leak => my second novel
1085 NOTICE: f_leak => great science fiction
1086 NOTICE: f_leak => awesome science fiction
1087 did | cid | dlevel | dauthor | dtitle
1088 -----+-----+--------+-------------------+-------------------------
1089 1 | 11 | 1 | regress_rls_bob | my first novel
1090 2 | 11 | 2 | regress_rls_bob | my second novel
1091 6 | 11 | 1 | regress_rls_carol | great science fiction
1092 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1095 -- But we can if we look directly
1096 SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
1097 NOTICE: f_leak => my first satire
1098 NOTICE: f_leak => great satire
1099 NOTICE: f_leak => testing RLS with partitions
1100 did | cid | dlevel | dauthor | dtitle
1101 -----+-----+--------+-------------------+-----------------------------
1102 4 | 55 | 1 | regress_rls_bob | my first satire
1103 8 | 55 | 2 | regress_rls_carol | great satire
1104 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1107 -- Turn on RLS and create policy on child to show RLS is checked before constraints
1108 SET SESSION AUTHORIZATION regress_rls_alice;
1109 ALTER TABLE part_document_satire ENABLE ROW LEVEL SECURITY;
1110 CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE
1112 -- This should fail with RLS violation now.
1113 SET SESSION AUTHORIZATION regress_rls_dave;
1114 INSERT INTO part_document_satire VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail
1115 ERROR: new row violates row-level security policy for table "part_document_satire"
1116 -- And now we cannot see directly into the partition either, due to RLS
1117 SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did;
1118 did | cid | dlevel | dauthor | dtitle
1119 -----+-----+--------+---------+--------
1122 -- The parent looks same as before
1123 -- viewpoint from regress_rls_dave
1124 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1125 NOTICE: f_leak => my first novel
1126 NOTICE: f_leak => my second novel
1127 NOTICE: f_leak => great science fiction
1128 NOTICE: f_leak => awesome science fiction
1129 did | cid | dlevel | dauthor | dtitle
1130 -----+-----+--------+-------------------+-------------------------
1131 1 | 11 | 1 | regress_rls_bob | my first novel
1132 2 | 11 | 2 | regress_rls_bob | my second novel
1133 6 | 11 | 1 | regress_rls_carol | great science fiction
1134 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1137 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1139 --------------------------------------------------------------
1140 Seq Scan on part_document_fiction
1141 Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle))
1142 InitPlan 1 (returns $0)
1143 -> Index Scan using uaccount_pkey on uaccount
1144 Index Cond: (pguser = CURRENT_USER)
1147 -- viewpoint from regress_rls_carol
1148 SET SESSION AUTHORIZATION regress_rls_carol;
1149 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1150 NOTICE: f_leak => my first novel
1151 NOTICE: f_leak => my second novel
1152 NOTICE: f_leak => great science fiction
1153 NOTICE: f_leak => awesome science fiction
1154 NOTICE: f_leak => my first satire
1155 NOTICE: f_leak => great satire
1156 NOTICE: f_leak => testing RLS with partitions
1157 NOTICE: f_leak => my science textbook
1158 NOTICE: f_leak => my history book
1159 NOTICE: f_leak => great technology book
1160 NOTICE: f_leak => awesome technology book
1161 did | cid | dlevel | dauthor | dtitle
1162 -----+-----+--------+-------------------+-----------------------------
1163 1 | 11 | 1 | regress_rls_bob | my first novel
1164 2 | 11 | 2 | regress_rls_bob | my second novel
1165 3 | 99 | 2 | regress_rls_bob | my science textbook
1166 4 | 55 | 1 | regress_rls_bob | my first satire
1167 5 | 99 | 2 | regress_rls_bob | my history book
1168 6 | 11 | 1 | regress_rls_carol | great science fiction
1169 7 | 99 | 2 | regress_rls_carol | great technology book
1170 8 | 55 | 2 | regress_rls_carol | great satire
1171 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1172 10 | 99 | 2 | regress_rls_dave | awesome technology book
1173 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1176 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1178 -----------------------------------------------------
1180 InitPlan 1 (returns $0)
1181 -> Index Scan using uaccount_pkey on uaccount
1182 Index Cond: (pguser = CURRENT_USER)
1183 -> Seq Scan on part_document_fiction
1184 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1185 -> Seq Scan on part_document_satire
1186 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1187 -> Seq Scan on part_document_nonfiction
1188 Filter: ((dlevel <= $0) AND f_leak(dtitle))
1191 -- only owner can change policies
1192 ALTER POLICY pp1 ON part_document USING (true); --fail
1193 ERROR: must be owner of table part_document
1194 DROP POLICY pp1 ON part_document; --fail
1195 ERROR: must be owner of relation part_document
1196 SET SESSION AUTHORIZATION regress_rls_alice;
1197 ALTER POLICY pp1 ON part_document USING (dauthor = current_user);
1198 -- viewpoint from regress_rls_bob again
1199 SET SESSION AUTHORIZATION regress_rls_bob;
1200 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1201 NOTICE: f_leak => my first novel
1202 NOTICE: f_leak => my second novel
1203 NOTICE: f_leak => my first satire
1204 NOTICE: f_leak => my science textbook
1205 NOTICE: f_leak => my history book
1206 did | cid | dlevel | dauthor | dtitle
1207 -----+-----+--------+-----------------+---------------------
1208 1 | 11 | 1 | regress_rls_bob | my first novel
1209 2 | 11 | 2 | regress_rls_bob | my second novel
1210 3 | 99 | 2 | regress_rls_bob | my science textbook
1211 4 | 55 | 1 | regress_rls_bob | my first satire
1212 5 | 99 | 2 | regress_rls_bob | my history book
1215 -- viewpoint from rls_regres_carol again
1216 SET SESSION AUTHORIZATION regress_rls_carol;
1217 SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did;
1218 NOTICE: f_leak => great science fiction
1219 NOTICE: f_leak => great satire
1220 NOTICE: f_leak => great technology book
1221 did | cid | dlevel | dauthor | dtitle
1222 -----+-----+--------+-------------------+-----------------------
1223 6 | 11 | 1 | regress_rls_carol | great science fiction
1224 7 | 99 | 2 | regress_rls_carol | great technology book
1225 8 | 55 | 2 | regress_rls_carol | great satire
1228 EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle);
1230 ---------------------------------------------------------------
1232 -> Seq Scan on part_document_fiction
1233 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
1234 -> Seq Scan on part_document_satire
1235 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
1236 -> Seq Scan on part_document_nonfiction
1237 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle))
1240 -- database superuser does bypass RLS policy when enabled
1241 RESET SESSION AUTHORIZATION;
1242 SET row_security TO ON;
1243 SELECT * FROM part_document ORDER BY did;
1244 did | cid | dlevel | dauthor | dtitle
1245 -----+-----+--------+-------------------+-----------------------------
1246 1 | 11 | 1 | regress_rls_bob | my first novel
1247 2 | 11 | 2 | regress_rls_bob | my second novel
1248 3 | 99 | 2 | regress_rls_bob | my science textbook
1249 4 | 55 | 1 | regress_rls_bob | my first satire
1250 5 | 99 | 2 | regress_rls_bob | my history book
1251 6 | 11 | 1 | regress_rls_carol | great science fiction
1252 7 | 99 | 2 | regress_rls_carol | great technology book
1253 8 | 55 | 2 | regress_rls_carol | great satire
1254 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1255 10 | 99 | 2 | regress_rls_dave | awesome technology book
1256 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1259 SELECT * FROM part_document_satire ORDER by did;
1260 did | cid | dlevel | dauthor | dtitle
1261 -----+-----+--------+-------------------+-----------------------------
1262 4 | 55 | 1 | regress_rls_bob | my first satire
1263 8 | 55 | 2 | regress_rls_carol | great satire
1264 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1267 -- database non-superuser with bypass privilege can bypass RLS policy when disabled
1268 SET SESSION AUTHORIZATION regress_rls_exempt_user;
1269 SET row_security TO OFF;
1270 SELECT * FROM part_document ORDER BY did;
1271 did | cid | dlevel | dauthor | dtitle
1272 -----+-----+--------+-------------------+-----------------------------
1273 1 | 11 | 1 | regress_rls_bob | my first novel
1274 2 | 11 | 2 | regress_rls_bob | my second novel
1275 3 | 99 | 2 | regress_rls_bob | my science textbook
1276 4 | 55 | 1 | regress_rls_bob | my first satire
1277 5 | 99 | 2 | regress_rls_bob | my history book
1278 6 | 11 | 1 | regress_rls_carol | great science fiction
1279 7 | 99 | 2 | regress_rls_carol | great technology book
1280 8 | 55 | 2 | regress_rls_carol | great satire
1281 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1282 10 | 99 | 2 | regress_rls_dave | awesome technology book
1283 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1286 SELECT * FROM part_document_satire ORDER by did;
1287 did | cid | dlevel | dauthor | dtitle
1288 -----+-----+--------+-------------------+-----------------------------
1289 4 | 55 | 1 | regress_rls_bob | my first satire
1290 8 | 55 | 2 | regress_rls_carol | great satire
1291 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1294 -- RLS policy does not apply to table owner when RLS enabled.
1295 SET SESSION AUTHORIZATION regress_rls_alice;
1296 SET row_security TO ON;
1297 SELECT * FROM part_document ORDER by did;
1298 did | cid | dlevel | dauthor | dtitle
1299 -----+-----+--------+-------------------+-----------------------------
1300 1 | 11 | 1 | regress_rls_bob | my first novel
1301 2 | 11 | 2 | regress_rls_bob | my second novel
1302 3 | 99 | 2 | regress_rls_bob | my science textbook
1303 4 | 55 | 1 | regress_rls_bob | my first satire
1304 5 | 99 | 2 | regress_rls_bob | my history book
1305 6 | 11 | 1 | regress_rls_carol | great science fiction
1306 7 | 99 | 2 | regress_rls_carol | great technology book
1307 8 | 55 | 2 | regress_rls_carol | great satire
1308 9 | 11 | 1 | regress_rls_dave | awesome science fiction
1309 10 | 99 | 2 | regress_rls_dave | awesome technology book
1310 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1313 SELECT * FROM part_document_satire ORDER by did;
1314 did | cid | dlevel | dauthor | dtitle
1315 -----+-----+--------+-------------------+-----------------------------
1316 4 | 55 | 1 | regress_rls_bob | my first satire
1317 8 | 55 | 2 | regress_rls_carol | great satire
1318 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions
1321 -- When RLS disabled, other users get ERROR.
1322 SET SESSION AUTHORIZATION regress_rls_dave;
1323 SET row_security TO OFF;
1324 SELECT * FROM part_document ORDER by did;
1325 ERROR: query would be affected by row-level security policy for table "part_document"
1326 SELECT * FROM part_document_satire ORDER by did;
1327 ERROR: query would be affected by row-level security policy for table "part_document_satire"
1328 -- Check behavior with a policy that uses a SubPlan not an InitPlan.
1329 SET SESSION AUTHORIZATION regress_rls_alice;
1330 SET row_security TO ON;
1331 CREATE POLICY pp3 ON part_document AS RESTRICTIVE
1332 USING ((SELECT dlevel <= seclv FROM uaccount WHERE pguser = current_user));
1333 SET SESSION AUTHORIZATION regress_rls_carol;
1334 INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_carol', 'testing pp3'); -- fail
1335 ERROR: new row violates row-level security policy "pp3" for table "part_document"
1336 ----- Dependencies -----
1337 SET SESSION AUTHORIZATION regress_rls_alice;
1338 SET row_security TO ON;
1339 CREATE TABLE dependee (x integer, y integer);
1340 CREATE TABLE dependent (x integer, y integer);
1341 CREATE POLICY d1 ON dependent FOR ALL
1343 USING (x = (SELECT d.x FROM dependee d WHERE d.y = y));
1344 DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row security qual?
1345 ERROR: cannot drop table dependee because other objects depend on it
1346 DETAIL: policy d1 on table dependent depends on table dependee
1347 HINT: Use DROP ... CASCADE to drop the dependent objects too.
1348 DROP TABLE dependee CASCADE;
1349 NOTICE: drop cascades to policy d1 on table dependent
1350 EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified
1352 -----------------------
1353 Seq Scan on dependent
1356 ----- RECURSION ----
1360 SET SESSION AUTHORIZATION regress_rls_alice;
1361 CREATE TABLE rec1 (x integer, y integer);
1362 CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y));
1363 ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;
1364 SET SESSION AUTHORIZATION regress_rls_bob;
1365 SELECT * FROM rec1; -- fail, direct recursion
1366 ERROR: infinite recursion detected in policy for relation "rec1"
1370 SET SESSION AUTHORIZATION regress_rls_alice;
1371 CREATE TABLE rec2 (a integer, b integer);
1372 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y));
1373 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b));
1374 ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;
1375 SET SESSION AUTHORIZATION regress_rls_bob;
1376 SELECT * FROM rec1; -- fail, mutual recursion
1377 ERROR: infinite recursion detected in policy for relation "rec1"
1379 -- Mutual recursion via views
1381 SET SESSION AUTHORIZATION regress_rls_bob;
1382 CREATE VIEW rec1v AS SELECT * FROM rec1;
1383 CREATE VIEW rec2v AS SELECT * FROM rec2;
1384 SET SESSION AUTHORIZATION regress_rls_alice;
1385 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
1386 ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
1387 SET SESSION AUTHORIZATION regress_rls_bob;
1388 SELECT * FROM rec1; -- fail, mutual recursion via views
1389 ERROR: infinite recursion detected in policy for relation "rec1"
1391 -- Mutual recursion via .s.b views
1393 SET SESSION AUTHORIZATION regress_rls_bob;
1394 DROP VIEW rec1v, rec2v CASCADE;
1395 NOTICE: drop cascades to 2 other objects
1396 DETAIL: drop cascades to policy r1 on table rec1
1397 drop cascades to policy r2 on table rec2
1398 CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
1399 CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
1400 SET SESSION AUTHORIZATION regress_rls_alice;
1401 CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
1402 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
1403 SET SESSION AUTHORIZATION regress_rls_bob;
1404 SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
1405 ERROR: infinite recursion detected in policy for relation "rec1"
1407 -- recursive RLS and VIEWs in policy
1409 SET SESSION AUTHORIZATION regress_rls_alice;
1410 CREATE TABLE s1 (a int, b text);
1411 INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
1412 CREATE TABLE s2 (x int, y text);
1413 INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
1414 GRANT SELECT ON s1, s2 TO regress_rls_bob;
1415 CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
1416 CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
1417 CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1));
1418 ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;
1419 ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;
1420 SET SESSION AUTHORIZATION regress_rls_bob;
1421 CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
1422 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
1423 ERROR: infinite recursion detected in policy for relation "s1"
1424 INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion)
1425 ERROR: infinite recursion detected in policy for relation "s1"
1426 SET SESSION AUTHORIZATION regress_rls_alice;
1427 DROP POLICY p3 on s1;
1428 ALTER POLICY p2 ON s2 USING (x % 2 = 0);
1429 SET SESSION AUTHORIZATION regress_rls_bob;
1430 SELECT * FROM s1 WHERE f_leak(b); -- OK
1431 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
1432 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1434 ---+----------------------------------
1435 2 | c81e728d9d4c2f636f067f89cc14862c
1436 4 | a87ff679a2f3e71d9181a67b7542122c
1439 EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
1441 -----------------------------------------------------------
1443 Filter: ((hashed SubPlan 1) AND f_leak(b))
1446 Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text))
1449 SET SESSION AUTHORIZATION regress_rls_alice;
1450 ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy
1451 SET SESSION AUTHORIZATION regress_rls_bob;
1452 SELECT * FROM s1 WHERE f_leak(b); -- OK
1453 NOTICE: f_leak => 0267aaf632e87a63288a08331f22c7c3
1454 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1456 ----+----------------------------------
1457 -4 | 0267aaf632e87a63288a08331f22c7c3
1458 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1461 EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
1463 -----------------------------------------------------------
1465 Filter: ((hashed SubPlan 1) AND f_leak(b))
1468 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
1471 SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
1473 ----+----+----------------------------------
1474 -6 | -6 | 596a3d04481816330f07e4f97510c28f
1475 -4 | -4 | 0267aaf632e87a63288a08331f22c7c3
1476 2 | 2 | c81e728d9d4c2f636f067f89cc14862c
1479 EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
1481 -------------------------------------------------------------------------
1483 Filter: (((x % 2) = 0) AND (y ~~ '%28%'::text))
1487 Filter: (hashed SubPlan 1)
1489 -> Seq Scan on s2 s2_1
1490 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text))
1493 SET SESSION AUTHORIZATION regress_rls_alice;
1494 ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%'));
1495 SET SESSION AUTHORIZATION regress_rls_bob;
1496 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
1497 ERROR: infinite recursion detected in policy for relation "s1"
1498 -- prepared statement with regress_rls_alice privilege
1499 PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
1508 EXPLAIN (COSTS OFF) EXECUTE p1(2);
1510 ----------------------------------------------
1513 Filter: ((a <= 2) AND ((a % 2) = 0))
1515 Filter: ((a <= 2) AND ((a % 2) = 0))
1517 Filter: ((a <= 2) AND ((a % 2) = 0))
1520 -- superuser is allowed to bypass RLS checks
1521 RESET SESSION AUTHORIZATION;
1522 SET row_security TO OFF;
1523 SELECT * FROM t1 WHERE f_leak(b);
1524 NOTICE: f_leak => aba
1525 NOTICE: f_leak => bbb
1526 NOTICE: f_leak => ccc
1527 NOTICE: f_leak => dad
1528 NOTICE: f_leak => abc
1529 NOTICE: f_leak => bcd
1530 NOTICE: f_leak => cde
1531 NOTICE: f_leak => def
1532 NOTICE: f_leak => xxx
1533 NOTICE: f_leak => yyy
1534 NOTICE: f_leak => zzz
1550 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
1552 ---------------------------
1562 -- plan cache should be invalidated
1574 EXPLAIN (COSTS OFF) EXECUTE p1(2);
1576 --------------------------
1586 PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1;
1595 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1597 -------------------------
1607 -- also, case when privilege switch from superuser
1608 SET SESSION AUTHORIZATION regress_rls_bob;
1609 SET row_security TO ON;
1618 EXPLAIN (COSTS OFF) EXECUTE p2(2);
1620 ---------------------------------------------
1623 Filter: ((a = 2) AND ((a % 2) = 0))
1625 Filter: ((a = 2) AND ((a % 2) = 0))
1627 Filter: ((a = 2) AND ((a % 2) = 0))
1631 -- UPDATE / DELETE and Row-level security
1633 SET SESSION AUTHORIZATION regress_rls_bob;
1634 EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
1636 -----------------------------------------------
1642 Filter: (((a % 2) = 0) AND f_leak(b))
1644 Filter: (((a % 2) = 0) AND f_leak(b))
1646 Filter: (((a % 2) = 0) AND f_leak(b))
1649 UPDATE t1 SET b = b || b WHERE f_leak(b);
1650 NOTICE: f_leak => bbb
1651 NOTICE: f_leak => dad
1652 NOTICE: f_leak => bcd
1653 NOTICE: f_leak => def
1654 NOTICE: f_leak => yyy
1655 EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1657 -----------------------------------------------
1660 Filter: (((a % 2) = 0) AND f_leak(b))
1663 UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
1664 NOTICE: f_leak => bbbbbb
1665 NOTICE: f_leak => daddad
1666 -- returning clause with system column
1667 UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1668 NOTICE: f_leak => bbbbbb_updt
1669 NOTICE: f_leak => daddad_updt
1670 tableoid | id | a | b | t1
1671 ----------+-----+---+-------------+---------------------
1672 t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt)
1673 t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt)
1676 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
1677 NOTICE: f_leak => bbbbbb_updt
1678 NOTICE: f_leak => daddad_updt
1679 NOTICE: f_leak => bcdbcd
1680 NOTICE: f_leak => defdef
1681 NOTICE: f_leak => yyyyyy
1683 -----+---+-------------
1684 102 | 2 | bbbbbb_updt
1685 104 | 4 | daddad_updt
1691 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1692 NOTICE: f_leak => bbbbbb_updt
1693 NOTICE: f_leak => daddad_updt
1694 NOTICE: f_leak => bcdbcd
1695 NOTICE: f_leak => defdef
1696 NOTICE: f_leak => yyyyyy
1697 tableoid | id | a | b | t1
1698 ----------+-----+---+-------------+---------------------
1699 t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt)
1700 t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt)
1701 t2 | 202 | 2 | bcdbcd | (202,2,bcdbcd)
1702 t2 | 204 | 4 | defdef | (204,4,defdef)
1703 t3 | 302 | 2 | yyyyyy | (302,2,yyyyyy)
1706 -- updates with from clause
1707 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3
1708 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1710 -----------------------------------------------------------------
1714 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1716 Filter: ((a = 2) AND f_leak(b))
1719 UPDATE t2 SET b=t2.b FROM t3
1720 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
1721 NOTICE: f_leak => cde
1722 NOTICE: f_leak => yyyyyy
1723 EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2
1724 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1726 -----------------------------------------------------------------
1733 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1735 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1737 -> Seq Scan on t2 t2_1
1738 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1740 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1743 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1745 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1748 UPDATE t1 SET b=t1.b FROM t2
1749 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1750 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1
1751 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1753 -----------------------------------------------------------------------
1757 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1760 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1761 -> Seq Scan on t2 t2_1
1762 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1764 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b))
1767 UPDATE t2 SET b=t2.b FROM t1
1768 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
1769 NOTICE: f_leak => cde
1770 -- updates with from clause self join
1771 EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1772 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1773 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1775 -----------------------------------------------------------------
1778 Join Filter: (t2_1.b = t2_2.b)
1779 -> Seq Scan on t2 t2_1
1780 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1781 -> Seq Scan on t2 t2_2
1782 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b))
1785 UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
1786 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
1787 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
1788 NOTICE: f_leak => cde
1789 NOTICE: f_leak => cde
1790 id | a | b | c | id | a | b | c | t2_1 | t2_2
1791 -----+---+-----+-----+-----+---+-----+-----+-----------------+-----------------
1792 203 | 3 | cde | 3.3 | 203 | 3 | cde | 3.3 | (203,3,cde,3.3) | (203,3,cde,3.3)
1795 EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1796 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1797 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1799 -----------------------------------------------------------------------
1805 Join Filter: (t1_1.b = t1_2.b)
1806 -> Seq Scan on t1 t1_1
1807 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1809 -> Seq Scan on t1 t1_2
1810 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1811 -> Seq Scan on t2 t1_2_1
1812 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1813 -> Seq Scan on t3 t1_2_2
1814 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1816 Join Filter: (t1_1_1.b = t1_2.b)
1817 -> Seq Scan on t2 t1_1_1
1818 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1820 -> Seq Scan on t1 t1_2
1821 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1822 -> Seq Scan on t2 t1_2_1
1823 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1824 -> Seq Scan on t3 t1_2_2
1825 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1827 Join Filter: (t1_1_2.b = t1_2.b)
1828 -> Seq Scan on t3 t1_1_2
1829 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1831 -> Seq Scan on t1 t1_2
1832 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1833 -> Seq Scan on t2 t1_2_1
1834 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1835 -> Seq Scan on t3 t1_2_2
1836 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b))
1839 UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
1840 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
1841 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
1842 NOTICE: f_leak => daddad_updt
1843 NOTICE: f_leak => daddad_updt
1844 NOTICE: f_leak => defdef
1845 NOTICE: f_leak => defdef
1846 NOTICE: f_leak => daddad_updt
1847 NOTICE: f_leak => defdef
1848 id | a | b | id | a | b | t1_1 | t1_2
1849 -----+---+-------------+-----+---+-------------+---------------------+---------------------
1850 104 | 4 | daddad_updt | 104 | 4 | daddad_updt | (104,4,daddad_updt) | (104,4,daddad_updt)
1851 204 | 4 | defdef | 204 | 4 | defdef | (204,4,defdef) | (204,4,defdef)
1854 RESET SESSION AUTHORIZATION;
1855 SET row_security TO OFF;
1856 SELECT * FROM t1 ORDER BY a,b;
1858 -----+---+-------------
1862 102 | 2 | bbbbbb_updt
1868 104 | 4 | daddad_updt
1872 SET SESSION AUTHORIZATION regress_rls_bob;
1873 SET row_security TO ON;
1874 EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b);
1876 -----------------------------------------------
1879 Filter: (((a % 2) = 0) AND f_leak(b))
1882 EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
1884 -----------------------------------------------
1890 Filter: (((a % 2) = 0) AND f_leak(b))
1892 Filter: (((a % 2) = 0) AND f_leak(b))
1894 Filter: (((a % 2) = 0) AND f_leak(b))
1897 DELETE FROM only t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1898 NOTICE: f_leak => bbbbbb_updt
1899 NOTICE: f_leak => daddad_updt
1900 tableoid | id | a | b | t1
1901 ----------+-----+---+-------------+---------------------
1902 t1 | 102 | 2 | bbbbbb_updt | (102,2,bbbbbb_updt)
1903 t1 | 104 | 4 | daddad_updt | (104,4,daddad_updt)
1906 DELETE FROM t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1;
1907 NOTICE: f_leak => bcdbcd
1908 NOTICE: f_leak => defdef
1909 NOTICE: f_leak => yyyyyy
1910 tableoid | id | a | b | t1
1911 ----------+-----+---+--------+----------------
1912 t2 | 202 | 2 | bcdbcd | (202,2,bcdbcd)
1913 t2 | 204 | 4 | defdef | (204,4,defdef)
1914 t3 | 302 | 2 | yyyyyy | (302,2,yyyyyy)
1918 -- S.b. view on top of Row-level security
1920 SET SESSION AUTHORIZATION regress_rls_alice;
1921 CREATE TABLE b1 (a int, b text);
1922 INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
1923 CREATE POLICY p1 ON b1 USING (a % 2 = 0);
1924 ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
1925 GRANT ALL ON b1 TO regress_rls_bob;
1926 SET SESSION AUTHORIZATION regress_rls_bob;
1927 CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
1928 GRANT ALL ON bv1 TO regress_rls_carol;
1929 SET SESSION AUTHORIZATION regress_rls_carol;
1930 EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
1932 ---------------------------------------------
1933 Subquery Scan on bv1
1934 Filter: f_leak(bv1.b)
1936 Filter: ((a > 0) AND ((a % 2) = 0))
1939 SELECT * FROM bv1 WHERE f_leak(b);
1940 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
1941 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1942 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1943 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
1944 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
1946 ----+----------------------------------
1947 2 | c81e728d9d4c2f636f067f89cc14862c
1948 4 | a87ff679a2f3e71d9181a67b7542122c
1949 6 | 1679091c5a880faf6fb5e6087eb1b2dc
1950 8 | c9f0f895fb98ab9159f51fd0297e236d
1951 10 | d3d9446802a44259755d38e6d163e820
1954 INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO
1955 ERROR: new row violates row-level security policy for table "b1"
1956 INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check
1957 ERROR: new row violates row-level security policy for table "b1"
1958 INSERT INTO bv1 VALUES (12, 'xxx'); -- ok
1959 EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1961 -----------------------------------------------------------------------
1964 Filter: ((a > 0) AND (a = 4) AND ((a % 2) = 0) AND f_leak(b))
1967 UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
1968 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
1969 EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1971 -----------------------------------------------------------------------
1974 Filter: ((a > 0) AND (a = 6) AND ((a % 2) = 0) AND f_leak(b))
1977 DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
1978 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
1979 SET SESSION AUTHORIZATION regress_rls_alice;
1982 -----+----------------------------------
1983 -10 | 1b0fd9efa5279c4203b7c70233f86dbf
1984 -9 | 252e691406782824eec43d7eadc3d256
1985 -8 | a8d2ec85eaf98407310b72eb73dda247
1986 -7 | 74687a12d3915d3c4d83f1af7b3683d5
1987 -6 | 596a3d04481816330f07e4f97510c28f
1988 -5 | 47c1b025fa18ea96c33fbb6718688c0f
1989 -4 | 0267aaf632e87a63288a08331f22c7c3
1990 -3 | b3149ecea4628efd23d2f86e5a723472
1991 -2 | 5d7b9adcbe1c629ec722529dd12e5129
1992 -1 | 6bb61e3b7bce0931da574d19d1d82c88
1993 0 | cfcd208495d565ef66e7dff9f98764da
1994 1 | c4ca4238a0b923820dcc509a6f75849b
1995 2 | c81e728d9d4c2f636f067f89cc14862c
1996 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
1997 5 | e4da3b7fbbce2345d7772b0674a318d5
1998 7 | 8f14e45fceea167a5a36dedd4bea2543
1999 8 | c9f0f895fb98ab9159f51fd0297e236d
2000 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2001 10 | d3d9446802a44259755d38e6d163e820
2007 -- INSERT ... ON CONFLICT DO UPDATE and Row-level security
2009 SET SESSION AUTHORIZATION regress_rls_alice;
2010 DROP POLICY p1 ON document;
2011 DROP POLICY p1r ON document;
2012 CREATE POLICY p1 ON document FOR SELECT USING (true);
2013 CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
2014 CREATE POLICY p3 ON document FOR UPDATE
2015 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
2016 WITH CHECK (dauthor = current_user);
2017 SET SESSION AUTHORIZATION regress_rls_bob;
2019 SELECT * FROM document WHERE did = 2;
2020 did | cid | dlevel | dauthor | dtitle
2021 -----+-----+--------+-----------------+-----------------
2022 2 | 11 | 2 | regress_rls_bob | my second novel
2025 -- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
2026 -- alternative UPDATE path happens to be taken):
2027 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel')
2028 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor;
2029 ERROR: new row violates row-level security policy for table "document"
2030 -- Violates USING qual for UPDATE policy p3.
2032 -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
2033 -- updated is not a "novel"/cid 11 (row is not leaked, even though we have
2034 -- SELECT privileges sufficient to see the row in this instance):
2035 INSERT INTO document VALUES (33, 22, 1, 'regress_rls_bob', 'okay science fiction'); -- preparation for next statement
2036 INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi') -- takes UPDATE path
2037 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
2038 ERROR: new row violates row-level security policy (USING expression) for table "document"
2039 -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
2041 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
2042 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2043 did | cid | dlevel | dauthor | dtitle
2044 -----+-----+--------+-----------------+----------------
2045 2 | 11 | 2 | regress_rls_bob | my first novel
2048 -- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated):
2049 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
2050 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
2051 did | cid | dlevel | dauthor | dtitle
2052 -----+-----+--------+-----------------+-----------------------
2053 78 | 11 | 1 | regress_rls_bob | some technology novel
2056 -- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the
2057 -- case in respect of *existing* tuple):
2058 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
2059 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
2060 did | cid | dlevel | dauthor | dtitle
2061 -----+-----+--------+-----------------+-----------------------
2062 78 | 33 | 1 | regress_rls_bob | some technology novel
2065 -- Same query a third time, but now fails due to existing tuple finally not
2067 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel')
2068 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
2069 ERROR: new row violates row-level security policy (USING expression) for table "document"
2070 -- Don't fail just because INSERT doesn't satisfy WITH CHECK option that
2071 -- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE
2072 -- path *isn't* taken, and so UPDATE-related policy does not apply:
2073 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
2074 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2075 did | cid | dlevel | dauthor | dtitle
2076 -----+-----+--------+-----------------+----------------------------------
2077 79 | 33 | 1 | regress_rls_bob | technology book, can only insert
2080 -- But this time, the same statement fails, because the UPDATE path is taken,
2081 -- and updating the row just inserted falls afoul of security barrier qual
2082 -- (enforced as WCO) -- what we might have updated target tuple to is
2083 -- irrelevant, in fact.
2084 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
2085 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2086 ERROR: new row violates row-level security policy (USING expression) for table "document"
2087 -- Test default USING qual enforced as WCO
2088 SET SESSION AUTHORIZATION regress_rls_alice;
2089 DROP POLICY p1 ON document;
2090 DROP POLICY p2 ON document;
2091 DROP POLICY p3 ON document;
2092 CREATE POLICY p3_with_default ON document FOR UPDATE
2093 USING (cid = (SELECT cid from category WHERE cname = 'novel'));
2094 SET SESSION AUTHORIZATION regress_rls_bob;
2095 -- Just because WCO-style enforcement of USING quals occurs with
2096 -- existing/target tuple does not mean that the implementation can be allowed
2097 -- to fail to also enforce this qual against the final tuple appended to
2098 -- relation (since in the absence of an explicit WCO, this is also interpreted
2099 -- as an UPDATE/ALL WCO in general).
2101 -- UPDATE path is taken here (fails due to existing tuple). Note that this is
2102 -- not reported as a "USING expression", because it's an RLS UPDATE check that originated as
2103 -- a USING qual for the purposes of RLS in general, as opposed to an explicit
2104 -- USING qual that is ordinarily a security barrier. We leave it up to the
2105 -- UPDATE to make this fail:
2106 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert')
2107 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
2108 ERROR: new row violates row-level security policy for table "document"
2109 -- UPDATE path is taken here. Existing tuple passes, since its cid
2110 -- corresponds to "novel", but default USING qual is enforced against
2111 -- post-UPDATE tuple too (as always when updating with a policy that lacks an
2112 -- explicit WCO), and so this fails:
2113 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'my first novel')
2114 ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *;
2115 ERROR: new row violates row-level security policy for table "document"
2116 SET SESSION AUTHORIZATION regress_rls_alice;
2117 DROP POLICY p3_with_default ON document;
2119 -- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE
2122 CREATE POLICY p3_with_all ON document FOR ALL
2123 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
2124 WITH CHECK (dauthor = current_user);
2125 SET SESSION AUTHORIZATION regress_rls_bob;
2126 -- Fails, since ALL WCO is enforced in insert path:
2127 INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel')
2128 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33;
2129 ERROR: new row violates row-level security policy for table "document"
2130 -- Fails, since ALL policy USING qual is enforced (existing, target tuple is in
2131 -- violation, since it has the "manga" cid):
2132 INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
2133 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
2134 ERROR: new row violates row-level security policy (USING expression) for table "document"
2135 -- Fails, since ALL WCO are enforced:
2136 INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel')
2137 ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol';
2138 ERROR: new row violates row-level security policy for table "document"
2142 SET SESSION AUTHORIZATION regress_rls_alice;
2143 CREATE TABLE z1 (a int, b text);
2144 CREATE TABLE z2 (a int, b text);
2145 GRANT SELECT ON z1,z2 TO regress_rls_group1, regress_rls_group2,
2146 regress_rls_bob, regress_rls_carol;
2147 INSERT INTO z1 VALUES
2152 CREATE POLICY p1 ON z1 TO regress_rls_group1 USING (a % 2 = 0);
2153 CREATE POLICY p2 ON z1 TO regress_rls_group2 USING (a % 2 = 1);
2154 ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
2155 SET SESSION AUTHORIZATION regress_rls_bob;
2156 SELECT * FROM z1 WHERE f_leak(b);
2157 NOTICE: f_leak => bbb
2158 NOTICE: f_leak => dad
2165 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2167 -----------------------------------------
2169 Filter: (((a % 2) = 0) AND f_leak(b))
2172 PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b);
2173 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2175 -----------------------------------------
2177 Filter: (((a % 2) = 0) AND f_leak(b))
2180 PREPARE plancache_test2 AS WITH q AS MATERIALIZED (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
2181 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2183 -------------------------------------------------
2187 Filter: (((a % 2) = 0) AND f_leak(b))
2193 PREPARE plancache_test3 AS WITH q AS MATERIALIZED (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
2194 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2196 -----------------------------------------------------
2203 Filter: (((a % 2) = 0) AND f_leak(b))
2206 SET ROLE regress_rls_group1;
2207 SELECT * FROM z1 WHERE f_leak(b);
2208 NOTICE: f_leak => bbb
2209 NOTICE: f_leak => dad
2216 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2218 -----------------------------------------
2220 Filter: (((a % 2) = 0) AND f_leak(b))
2223 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2225 -----------------------------------------
2227 Filter: (((a % 2) = 0) AND f_leak(b))
2230 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2232 -------------------------------------------------
2236 Filter: (((a % 2) = 0) AND f_leak(b))
2242 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2244 -----------------------------------------------------
2251 Filter: (((a % 2) = 0) AND f_leak(b))
2254 SET SESSION AUTHORIZATION regress_rls_carol;
2255 SELECT * FROM z1 WHERE f_leak(b);
2256 NOTICE: f_leak => aba
2257 NOTICE: f_leak => ccc
2264 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2266 -----------------------------------------
2268 Filter: (((a % 2) = 1) AND f_leak(b))
2271 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2273 -----------------------------------------
2275 Filter: (((a % 2) = 1) AND f_leak(b))
2278 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2280 -------------------------------------------------
2284 Filter: (((a % 2) = 1) AND f_leak(b))
2290 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2292 -----------------------------------------------------
2299 Filter: (((a % 2) = 1) AND f_leak(b))
2302 SET ROLE regress_rls_group2;
2303 SELECT * FROM z1 WHERE f_leak(b);
2304 NOTICE: f_leak => aba
2305 NOTICE: f_leak => ccc
2312 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
2314 -----------------------------------------
2316 Filter: (((a % 2) = 1) AND f_leak(b))
2319 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
2321 -----------------------------------------
2323 Filter: (((a % 2) = 1) AND f_leak(b))
2326 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
2328 -------------------------------------------------
2332 Filter: (((a % 2) = 1) AND f_leak(b))
2338 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
2340 -----------------------------------------------------
2347 Filter: (((a % 2) = 1) AND f_leak(b))
2351 -- Views should follow policy for view owner.
2353 -- View and Table owner are the same.
2354 SET SESSION AUTHORIZATION regress_rls_alice;
2355 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
2356 GRANT SELECT ON rls_view TO regress_rls_bob;
2357 -- Query as role that is not owner of view or table. Should return all records.
2358 SET SESSION AUTHORIZATION regress_rls_bob;
2359 SELECT * FROM rls_view;
2360 NOTICE: f_leak => aba
2361 NOTICE: f_leak => bbb
2362 NOTICE: f_leak => ccc
2363 NOTICE: f_leak => dad
2372 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2374 ---------------------
2379 -- Query as view/table owner. Should return all records.
2380 SET SESSION AUTHORIZATION regress_rls_alice;
2381 SELECT * FROM rls_view;
2382 NOTICE: f_leak => aba
2383 NOTICE: f_leak => bbb
2384 NOTICE: f_leak => ccc
2385 NOTICE: f_leak => dad
2394 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2396 ---------------------
2402 -- View and Table owners are different.
2403 SET SESSION AUTHORIZATION regress_rls_bob;
2404 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
2405 GRANT SELECT ON rls_view TO regress_rls_alice;
2406 -- Query as role that is not owner of view but is owner of table.
2407 -- Should return records based on view owner policies.
2408 SET SESSION AUTHORIZATION regress_rls_alice;
2409 SELECT * FROM rls_view;
2410 NOTICE: f_leak => bbb
2411 NOTICE: f_leak => dad
2418 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2420 -----------------------------------------
2422 Filter: (((a % 2) = 0) AND f_leak(b))
2425 -- Query as role that is not owner of table but is owner of view.
2426 -- Should return records based on view owner policies.
2427 SET SESSION AUTHORIZATION regress_rls_bob;
2428 SELECT * FROM rls_view;
2429 NOTICE: f_leak => bbb
2430 NOTICE: f_leak => dad
2437 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2439 -----------------------------------------
2441 Filter: (((a % 2) = 0) AND f_leak(b))
2444 -- Query as role that is not the owner of the table or view without permissions.
2445 SET SESSION AUTHORIZATION regress_rls_carol;
2446 SELECT * FROM rls_view; --fail - permission denied.
2447 ERROR: permission denied for view rls_view
2448 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
2449 ERROR: permission denied for view rls_view
2450 -- Query as role that is not the owner of the table or view with permissions.
2451 SET SESSION AUTHORIZATION regress_rls_bob;
2452 GRANT SELECT ON rls_view TO regress_rls_carol;
2453 SELECT * FROM rls_view;
2454 NOTICE: f_leak => bbb
2455 NOTICE: f_leak => dad
2462 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
2464 -----------------------------------------
2466 Filter: (((a % 2) = 0) AND f_leak(b))
2469 SET SESSION AUTHORIZATION regress_rls_bob;
2474 SET SESSION AUTHORIZATION regress_rls_alice;
2475 CREATE TABLE x1 (a int, b text, c text);
2476 GRANT ALL ON x1 TO PUBLIC;
2477 INSERT INTO x1 VALUES
2478 (1, 'abc', 'regress_rls_bob'),
2479 (2, 'bcd', 'regress_rls_bob'),
2480 (3, 'cde', 'regress_rls_carol'),
2481 (4, 'def', 'regress_rls_carol'),
2482 (5, 'efg', 'regress_rls_bob'),
2483 (6, 'fgh', 'regress_rls_bob'),
2484 (7, 'fgh', 'regress_rls_carol'),
2485 (8, 'fgh', 'regress_rls_carol');
2486 CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
2487 CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
2488 CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1);
2489 CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0);
2490 CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
2491 ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
2492 SET SESSION AUTHORIZATION regress_rls_bob;
2493 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
2494 NOTICE: f_leak => abc
2495 NOTICE: f_leak => bcd
2496 NOTICE: f_leak => def
2497 NOTICE: f_leak => efg
2498 NOTICE: f_leak => fgh
2499 NOTICE: f_leak => fgh
2501 ---+-----+-------------------
2502 1 | abc | regress_rls_bob
2503 2 | bcd | regress_rls_bob
2504 4 | def | regress_rls_carol
2505 5 | efg | regress_rls_bob
2506 6 | fgh | regress_rls_bob
2507 8 | fgh | regress_rls_carol
2510 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
2511 NOTICE: f_leak => abc
2512 NOTICE: f_leak => bcd
2513 NOTICE: f_leak => def
2514 NOTICE: f_leak => efg
2515 NOTICE: f_leak => fgh
2516 NOTICE: f_leak => fgh
2518 ---+----------+-------------------
2519 1 | abc_updt | regress_rls_bob
2520 2 | bcd_updt | regress_rls_bob
2521 4 | def_updt | regress_rls_carol
2522 5 | efg_updt | regress_rls_bob
2523 6 | fgh_updt | regress_rls_bob
2524 8 | fgh_updt | regress_rls_carol
2527 SET SESSION AUTHORIZATION regress_rls_carol;
2528 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
2529 NOTICE: f_leak => cde
2530 NOTICE: f_leak => fgh
2531 NOTICE: f_leak => bcd_updt
2532 NOTICE: f_leak => def_updt
2533 NOTICE: f_leak => fgh_updt
2534 NOTICE: f_leak => fgh_updt
2536 ---+----------+-------------------
2537 2 | bcd_updt | regress_rls_bob
2538 3 | cde | regress_rls_carol
2539 4 | def_updt | regress_rls_carol
2540 6 | fgh_updt | regress_rls_bob
2541 7 | fgh | regress_rls_carol
2542 8 | fgh_updt | regress_rls_carol
2545 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
2546 NOTICE: f_leak => cde
2547 NOTICE: f_leak => fgh
2548 NOTICE: f_leak => bcd_updt
2549 NOTICE: f_leak => def_updt
2550 NOTICE: f_leak => fgh_updt
2551 NOTICE: f_leak => fgh_updt
2553 ---+---------------+-------------------
2554 3 | cde_updt | regress_rls_carol
2555 7 | fgh_updt | regress_rls_carol
2556 2 | bcd_updt_updt | regress_rls_bob
2557 4 | def_updt_updt | regress_rls_carol
2558 6 | fgh_updt_updt | regress_rls_bob
2559 8 | fgh_updt_updt | regress_rls_carol
2562 DELETE FROM x1 WHERE f_leak(b) RETURNING *;
2563 NOTICE: f_leak => cde_updt
2564 NOTICE: f_leak => fgh_updt
2565 NOTICE: f_leak => bcd_updt_updt
2566 NOTICE: f_leak => def_updt_updt
2567 NOTICE: f_leak => fgh_updt_updt
2568 NOTICE: f_leak => fgh_updt_updt
2570 ---+---------------+-------------------
2571 3 | cde_updt | regress_rls_carol
2572 7 | fgh_updt | regress_rls_carol
2573 2 | bcd_updt_updt | regress_rls_bob
2574 4 | def_updt_updt | regress_rls_carol
2575 6 | fgh_updt_updt | regress_rls_bob
2576 8 | fgh_updt_updt | regress_rls_carol
2580 -- Duplicate Policy Names
2582 SET SESSION AUTHORIZATION regress_rls_alice;
2583 CREATE TABLE y1 (a int, b text);
2584 CREATE TABLE y2 (a int, b text);
2585 GRANT ALL ON y1, y2 TO regress_rls_bob;
2586 CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
2587 CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
2588 CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
2589 ERROR: policy "p1" for table "y1" already exists
2590 CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK
2591 ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;
2592 ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
2594 -- Expression structure with SBV
2596 -- Create view as table owner. RLS should NOT be applied.
2597 SET SESSION AUTHORIZATION regress_rls_alice;
2598 CREATE VIEW rls_sbv WITH (security_barrier) AS
2599 SELECT * FROM y1 WHERE f_leak(b);
2600 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
2602 -----------------------------------
2604 Filter: (f_leak(b) AND (a = 1))
2608 -- Create view as role that does not own table. RLS should be applied.
2609 SET SESSION AUTHORIZATION regress_rls_bob;
2610 CREATE VIEW rls_sbv WITH (security_barrier) AS
2611 SELECT * FROM y1 WHERE f_leak(b);
2612 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
2614 ------------------------------------------------------------------
2616 Filter: ((a = 1) AND ((a > 2) OR ((a % 2) = 0)) AND f_leak(b))
2621 -- Expression structure
2623 SET SESSION AUTHORIZATION regress_rls_alice;
2624 INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
2625 CREATE POLICY p2 ON y2 USING (a % 3 = 0);
2626 CREATE POLICY p3 ON y2 USING (a % 4 = 0);
2627 SET SESSION AUTHORIZATION regress_rls_bob;
2628 SELECT * FROM y2 WHERE f_leak(b);
2629 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2630 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2631 NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
2632 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2633 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2634 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2635 NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
2636 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2637 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2638 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2639 NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
2640 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2641 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2642 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2644 ----+----------------------------------
2645 0 | cfcd208495d565ef66e7dff9f98764da
2646 2 | c81e728d9d4c2f636f067f89cc14862c
2647 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2648 4 | a87ff679a2f3e71d9181a67b7542122c
2649 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2650 8 | c9f0f895fb98ab9159f51fd0297e236d
2651 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2652 10 | d3d9446802a44259755d38e6d163e820
2653 12 | c20ad4d76fe97759aa27a0c99bff6710
2654 14 | aab3238922bcc25a6f606eb525ffdc56
2655 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2656 16 | c74d97b01eae257e44aa9d5bade97baf
2657 18 | 6f4922f45568161a8cdf4ad2299f6d23
2658 20 | 98f13708210194c475687be6106a3b84
2661 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
2663 -----------------------------------------------------------------------------
2665 Filter: ((((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)) AND f_leak(b))
2669 -- Qual push-down of leaky functions, when not referring to table
2671 SELECT * FROM y2 WHERE f_leak('abc');
2672 NOTICE: f_leak => abc
2673 NOTICE: f_leak => abc
2674 NOTICE: f_leak => abc
2675 NOTICE: f_leak => abc
2676 NOTICE: f_leak => abc
2677 NOTICE: f_leak => abc
2678 NOTICE: f_leak => abc
2679 NOTICE: f_leak => abc
2680 NOTICE: f_leak => abc
2681 NOTICE: f_leak => abc
2682 NOTICE: f_leak => abc
2683 NOTICE: f_leak => abc
2684 NOTICE: f_leak => abc
2685 NOTICE: f_leak => abc
2686 NOTICE: f_leak => abc
2687 NOTICE: f_leak => abc
2688 NOTICE: f_leak => abc
2689 NOTICE: f_leak => abc
2690 NOTICE: f_leak => abc
2691 NOTICE: f_leak => abc
2692 NOTICE: f_leak => abc
2694 ----+----------------------------------
2695 0 | cfcd208495d565ef66e7dff9f98764da
2696 2 | c81e728d9d4c2f636f067f89cc14862c
2697 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
2698 4 | a87ff679a2f3e71d9181a67b7542122c
2699 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2700 8 | c9f0f895fb98ab9159f51fd0297e236d
2701 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
2702 10 | d3d9446802a44259755d38e6d163e820
2703 12 | c20ad4d76fe97759aa27a0c99bff6710
2704 14 | aab3238922bcc25a6f606eb525ffdc56
2705 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
2706 16 | c74d97b01eae257e44aa9d5bade97baf
2707 18 | 6f4922f45568161a8cdf4ad2299f6d23
2708 20 | 98f13708210194c475687be6106a3b84
2711 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc');
2713 ---------------------------------------------------------------------------------------
2715 Filter: (f_leak('abc'::text) AND (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)))
2718 CREATE TABLE test_qual_pushdown (
2721 INSERT INTO test_qual_pushdown VALUES ('abc'),('def');
2722 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
2723 NOTICE: f_leak => abc
2724 NOTICE: f_leak => def
2729 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
2731 -------------------------------------------------------------------------
2733 Hash Cond: (test_qual_pushdown.abc = y2.b)
2734 -> Seq Scan on test_qual_pushdown
2738 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))
2741 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
2742 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2743 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2744 NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3
2745 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2746 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2747 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2748 NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26
2749 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2750 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2751 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2752 NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3
2753 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2754 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2755 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2760 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
2762 -----------------------------------------------------------------------------------------
2764 Hash Cond: (test_qual_pushdown.abc = y2.b)
2765 -> Seq Scan on test_qual_pushdown
2768 Filter: ((((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)) AND f_leak(b))
2771 DROP TABLE test_qual_pushdown;
2773 -- Plancache invalidate on user change.
2775 RESET SESSION AUTHORIZATION;
2776 DROP TABLE t1 CASCADE;
2777 NOTICE: drop cascades to 2 other objects
2778 DETAIL: drop cascades to table t2
2779 drop cascades to table t3
2780 CREATE TABLE t1 (a integer);
2781 GRANT SELECT ON t1 TO regress_rls_bob, regress_rls_carol;
2782 CREATE POLICY p1 ON t1 TO regress_rls_bob USING ((a % 2) = 0);
2783 CREATE POLICY p2 ON t1 TO regress_rls_carol USING ((a % 4) = 0);
2784 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
2785 -- Prepare as regress_rls_bob
2786 SET ROLE regress_rls_bob;
2787 PREPARE role_inval AS SELECT * FROM t1;
2789 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2791 -------------------------
2793 Filter: ((a % 2) = 0)
2796 -- Change to regress_rls_carol
2797 SET ROLE regress_rls_carol;
2798 -- Check plan- should be different
2799 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2801 -------------------------
2803 Filter: ((a % 4) = 0)
2806 -- Change back to regress_rls_bob
2807 SET ROLE regress_rls_bob;
2808 -- Check plan- should be back to original
2809 EXPLAIN (COSTS OFF) EXECUTE role_inval;
2811 -------------------------
2813 Filter: ((a % 2) = 0)
2819 RESET SESSION AUTHORIZATION;
2820 DROP TABLE t1 CASCADE;
2821 CREATE TABLE t1 (a integer, b text);
2822 CREATE POLICY p1 ON t1 USING (a % 2 = 0);
2823 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
2824 GRANT ALL ON t1 TO regress_rls_bob;
2825 INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
2826 SET SESSION AUTHORIZATION regress_rls_bob;
2827 WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
2828 NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da
2829 NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
2830 NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
2831 NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
2832 NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d
2833 NOTICE: f_leak => d3d9446802a44259755d38e6d163e820
2834 NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710
2835 NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56
2836 NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf
2837 NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23
2838 NOTICE: f_leak => 98f13708210194c475687be6106a3b84
2840 ----+----------------------------------
2841 0 | cfcd208495d565ef66e7dff9f98764da
2842 2 | c81e728d9d4c2f636f067f89cc14862c
2843 4 | a87ff679a2f3e71d9181a67b7542122c
2844 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2845 8 | c9f0f895fb98ab9159f51fd0297e236d
2846 10 | d3d9446802a44259755d38e6d163e820
2847 12 | c20ad4d76fe97759aa27a0c99bff6710
2848 14 | aab3238922bcc25a6f606eb525ffdc56
2849 16 | c74d97b01eae257e44aa9d5bade97baf
2850 18 | 6f4922f45568161a8cdf4ad2299f6d23
2851 20 | 98f13708210194c475687be6106a3b84
2855 WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
2857 -------------------------------------------------
2861 Filter: (((a % 2) = 0) AND f_leak(b))
2864 WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
2865 ERROR: new row violates row-level security policy for table "t1"
2866 WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
2868 ----+----------------------------------
2869 0 | cfcd208495d565ef66e7dff9f98764da
2870 2 | c81e728d9d4c2f636f067f89cc14862c
2871 4 | a87ff679a2f3e71d9181a67b7542122c
2872 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2873 8 | c9f0f895fb98ab9159f51fd0297e236d
2874 10 | d3d9446802a44259755d38e6d163e820
2875 12 | c20ad4d76fe97759aa27a0c99bff6710
2876 14 | aab3238922bcc25a6f606eb525ffdc56
2877 16 | c74d97b01eae257e44aa9d5bade97baf
2878 18 | 6f4922f45568161a8cdf4ad2299f6d23
2879 20 | 98f13708210194c475687be6106a3b84
2882 WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail
2883 ERROR: new row violates row-level security policy for table "t1"
2884 WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok
2893 RESET SESSION AUTHORIZATION;
2894 ALTER POLICY p1 ON t1 RENAME TO p1; --fail
2895 ERROR: policy "p1" for table "t1" already exists
2896 SELECT polname, relname
2898 JOIN pg_class pc ON (pc.oid = pol.polrelid)
2899 WHERE relname = 't1';
2905 ALTER POLICY p1 ON t1 RENAME TO p2; --ok
2906 SELECT polname, relname
2908 JOIN pg_class pc ON (pc.oid = pol.polrelid)
2909 WHERE relname = 't1';
2916 -- Check INSERT SELECT
2918 SET SESSION AUTHORIZATION regress_rls_bob;
2919 CREATE TABLE t2 (a integer, b text);
2920 INSERT INTO t2 (SELECT * FROM t1);
2921 EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
2923 -------------------------------
2926 Filter: ((a % 2) = 0)
2931 ----+----------------------------------
2932 0 | cfcd208495d565ef66e7dff9f98764da
2933 2 | c81e728d9d4c2f636f067f89cc14862c
2934 4 | a87ff679a2f3e71d9181a67b7542122c
2935 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2936 8 | c9f0f895fb98ab9159f51fd0297e236d
2937 10 | d3d9446802a44259755d38e6d163e820
2938 12 | c20ad4d76fe97759aa27a0c99bff6710
2939 14 | aab3238922bcc25a6f606eb525ffdc56
2940 16 | c74d97b01eae257e44aa9d5bade97baf
2941 18 | 6f4922f45568161a8cdf4ad2299f6d23
2942 20 | 98f13708210194c475687be6106a3b84
2946 EXPLAIN (COSTS OFF) SELECT * FROM t2;
2952 CREATE TABLE t3 AS SELECT * FROM t1;
2955 ----+----------------------------------
2956 0 | cfcd208495d565ef66e7dff9f98764da
2957 2 | c81e728d9d4c2f636f067f89cc14862c
2958 4 | a87ff679a2f3e71d9181a67b7542122c
2959 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2960 8 | c9f0f895fb98ab9159f51fd0297e236d
2961 10 | d3d9446802a44259755d38e6d163e820
2962 12 | c20ad4d76fe97759aa27a0c99bff6710
2963 14 | aab3238922bcc25a6f606eb525ffdc56
2964 16 | c74d97b01eae257e44aa9d5bade97baf
2965 18 | 6f4922f45568161a8cdf4ad2299f6d23
2966 20 | 98f13708210194c475687be6106a3b84
2970 SELECT * INTO t4 FROM t1;
2973 ----+----------------------------------
2974 0 | cfcd208495d565ef66e7dff9f98764da
2975 2 | c81e728d9d4c2f636f067f89cc14862c
2976 4 | a87ff679a2f3e71d9181a67b7542122c
2977 6 | 1679091c5a880faf6fb5e6087eb1b2dc
2978 8 | c9f0f895fb98ab9159f51fd0297e236d
2979 10 | d3d9446802a44259755d38e6d163e820
2980 12 | c20ad4d76fe97759aa27a0c99bff6710
2981 14 | aab3238922bcc25a6f606eb525ffdc56
2982 16 | c74d97b01eae257e44aa9d5bade97baf
2983 18 | 6f4922f45568161a8cdf4ad2299f6d23
2984 20 | 98f13708210194c475687be6106a3b84
2991 SET SESSION AUTHORIZATION regress_rls_alice;
2992 CREATE TABLE blog (id integer, author text, post text);
2993 CREATE TABLE comment (blog_id integer, message text);
2994 GRANT ALL ON blog, comment TO regress_rls_bob;
2995 CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
2996 ALTER TABLE blog ENABLE ROW LEVEL SECURITY;
2997 INSERT INTO blog VALUES
2998 (1, 'alice', 'blog #1'),
2999 (2, 'bob', 'blog #1'),
3000 (3, 'alice', 'blog #2'),
3001 (4, 'alice', 'blog #3'),
3002 (5, 'john', 'blog #1');
3003 INSERT INTO comment VALUES
3010 SET SESSION AUTHORIZATION regress_rls_bob;
3011 -- Check RLS JOIN with Non-RLS.
3012 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
3013 id | author | message
3014 ----+--------+-------------
3016 2 | bob | who did it?
3019 -- Check Non-RLS JOIN with RLS.
3020 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
3021 id | author | message
3022 ----+--------+-------------
3024 2 | bob | who did it?
3027 SET SESSION AUTHORIZATION regress_rls_alice;
3028 CREATE POLICY comment_1 ON comment USING (blog_id < 4);
3029 ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
3030 SET SESSION AUTHORIZATION regress_rls_bob;
3031 -- Check RLS JOIN RLS
3032 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
3033 id | author | message
3034 ----+--------+-------------
3035 2 | bob | who did it?
3038 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
3039 id | author | message
3040 ----+--------+-------------
3041 2 | bob | who did it?
3044 SET SESSION AUTHORIZATION regress_rls_alice;
3045 DROP TABLE blog, comment;
3047 -- Default Deny Policy
3049 RESET SESSION AUTHORIZATION;
3050 DROP POLICY p2 ON t1;
3051 ALTER TABLE t1 OWNER TO regress_rls_alice;
3052 -- Check that default deny does not apply to superuser.
3053 RESET SESSION AUTHORIZATION;
3056 ----+----------------------------------
3057 1 | c4ca4238a0b923820dcc509a6f75849b
3058 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
3059 5 | e4da3b7fbbce2345d7772b0674a318d5
3060 7 | 8f14e45fceea167a5a36dedd4bea2543
3061 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
3062 11 | 6512bd43d9caa6e02c990b0a82652dca
3063 13 | c51ce410c124a10e0db5e4b97fc2af39
3064 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
3065 17 | 70efdf2ec9b086079795c442636b55fb
3066 19 | 1f0e3dad99908345f7439f8ffabdffc4
3067 0 | cfcd208495d565ef66e7dff9f98764da
3068 2 | c81e728d9d4c2f636f067f89cc14862c
3069 4 | a87ff679a2f3e71d9181a67b7542122c
3070 6 | 1679091c5a880faf6fb5e6087eb1b2dc
3071 8 | c9f0f895fb98ab9159f51fd0297e236d
3072 10 | d3d9446802a44259755d38e6d163e820
3073 12 | c20ad4d76fe97759aa27a0c99bff6710
3074 14 | aab3238922bcc25a6f606eb525ffdc56
3075 16 | c74d97b01eae257e44aa9d5bade97baf
3076 18 | 6f4922f45568161a8cdf4ad2299f6d23
3077 20 | 98f13708210194c475687be6106a3b84
3081 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3087 -- Check that default deny does not apply to table owner.
3088 SET SESSION AUTHORIZATION regress_rls_alice;
3091 ----+----------------------------------
3092 1 | c4ca4238a0b923820dcc509a6f75849b
3093 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3
3094 5 | e4da3b7fbbce2345d7772b0674a318d5
3095 7 | 8f14e45fceea167a5a36dedd4bea2543
3096 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26
3097 11 | 6512bd43d9caa6e02c990b0a82652dca
3098 13 | c51ce410c124a10e0db5e4b97fc2af39
3099 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3
3100 17 | 70efdf2ec9b086079795c442636b55fb
3101 19 | 1f0e3dad99908345f7439f8ffabdffc4
3102 0 | cfcd208495d565ef66e7dff9f98764da
3103 2 | c81e728d9d4c2f636f067f89cc14862c
3104 4 | a87ff679a2f3e71d9181a67b7542122c
3105 6 | 1679091c5a880faf6fb5e6087eb1b2dc
3106 8 | c9f0f895fb98ab9159f51fd0297e236d
3107 10 | d3d9446802a44259755d38e6d163e820
3108 12 | c20ad4d76fe97759aa27a0c99bff6710
3109 14 | aab3238922bcc25a6f606eb525ffdc56
3110 16 | c74d97b01eae257e44aa9d5bade97baf
3111 18 | 6f4922f45568161a8cdf4ad2299f6d23
3112 20 | 98f13708210194c475687be6106a3b84
3116 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3122 -- Check that default deny applies to non-owner/non-superuser when RLS on.
3123 SET SESSION AUTHORIZATION regress_rls_bob;
3124 SET row_security TO ON;
3130 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3132 --------------------------
3134 One-Time Filter: false
3137 SET SESSION AUTHORIZATION regress_rls_bob;
3143 EXPLAIN (COSTS OFF) SELECT * FROM t1;
3145 --------------------------
3147 One-Time Filter: false
3153 RESET SESSION AUTHORIZATION;
3154 DROP TABLE copy_t CASCADE;
3155 ERROR: table "copy_t" does not exist
3156 CREATE TABLE copy_t (a integer, b text);
3157 CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
3158 ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
3159 GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user;
3160 INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
3161 -- Check COPY TO as Superuser/owner.
3162 RESET SESSION AUTHORIZATION;
3163 SET row_security TO OFF;
3164 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
3165 0,cfcd208495d565ef66e7dff9f98764da
3166 1,c4ca4238a0b923820dcc509a6f75849b
3167 2,c81e728d9d4c2f636f067f89cc14862c
3168 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
3169 4,a87ff679a2f3e71d9181a67b7542122c
3170 5,e4da3b7fbbce2345d7772b0674a318d5
3171 6,1679091c5a880faf6fb5e6087eb1b2dc
3172 7,8f14e45fceea167a5a36dedd4bea2543
3173 8,c9f0f895fb98ab9159f51fd0297e236d
3174 9,45c48cce2e2d7fbdea1afc51c7c6ad26
3175 10,d3d9446802a44259755d38e6d163e820
3176 SET row_security TO ON;
3177 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
3178 0,cfcd208495d565ef66e7dff9f98764da
3179 1,c4ca4238a0b923820dcc509a6f75849b
3180 2,c81e728d9d4c2f636f067f89cc14862c
3181 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
3182 4,a87ff679a2f3e71d9181a67b7542122c
3183 5,e4da3b7fbbce2345d7772b0674a318d5
3184 6,1679091c5a880faf6fb5e6087eb1b2dc
3185 7,8f14e45fceea167a5a36dedd4bea2543
3186 8,c9f0f895fb98ab9159f51fd0297e236d
3187 9,45c48cce2e2d7fbdea1afc51c7c6ad26
3188 10,d3d9446802a44259755d38e6d163e820
3189 -- Check COPY TO as user with permissions.
3190 SET SESSION AUTHORIZATION regress_rls_bob;
3191 SET row_security TO OFF;
3192 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3193 ERROR: query would be affected by row-level security policy for table "copy_t"
3194 SET row_security TO ON;
3195 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
3196 0,cfcd208495d565ef66e7dff9f98764da
3197 2,c81e728d9d4c2f636f067f89cc14862c
3198 4,a87ff679a2f3e71d9181a67b7542122c
3199 6,1679091c5a880faf6fb5e6087eb1b2dc
3200 8,c9f0f895fb98ab9159f51fd0297e236d
3201 10,d3d9446802a44259755d38e6d163e820
3202 -- Check COPY TO as user with permissions and BYPASSRLS
3203 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3204 SET row_security TO OFF;
3205 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
3206 0,cfcd208495d565ef66e7dff9f98764da
3207 1,c4ca4238a0b923820dcc509a6f75849b
3208 2,c81e728d9d4c2f636f067f89cc14862c
3209 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
3210 4,a87ff679a2f3e71d9181a67b7542122c
3211 5,e4da3b7fbbce2345d7772b0674a318d5
3212 6,1679091c5a880faf6fb5e6087eb1b2dc
3213 7,8f14e45fceea167a5a36dedd4bea2543
3214 8,c9f0f895fb98ab9159f51fd0297e236d
3215 9,45c48cce2e2d7fbdea1afc51c7c6ad26
3216 10,d3d9446802a44259755d38e6d163e820
3217 SET row_security TO ON;
3218 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
3219 0,cfcd208495d565ef66e7dff9f98764da
3220 1,c4ca4238a0b923820dcc509a6f75849b
3221 2,c81e728d9d4c2f636f067f89cc14862c
3222 3,eccbc87e4b5ce2fe28308fd9f2a7baf3
3223 4,a87ff679a2f3e71d9181a67b7542122c
3224 5,e4da3b7fbbce2345d7772b0674a318d5
3225 6,1679091c5a880faf6fb5e6087eb1b2dc
3226 7,8f14e45fceea167a5a36dedd4bea2543
3227 8,c9f0f895fb98ab9159f51fd0297e236d
3228 9,45c48cce2e2d7fbdea1afc51c7c6ad26
3229 10,d3d9446802a44259755d38e6d163e820
3230 -- Check COPY TO as user without permissions. SET row_security TO OFF;
3231 SET SESSION AUTHORIZATION regress_rls_carol;
3232 SET row_security TO OFF;
3233 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3234 ERROR: query would be affected by row-level security policy for table "copy_t"
3235 SET row_security TO ON;
3236 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
3237 ERROR: permission denied for table copy_t
3238 -- Check COPY relation TO; keep it just one row to avoid reordering issues
3239 RESET SESSION AUTHORIZATION;
3240 SET row_security TO ON;
3241 CREATE TABLE copy_rel_to (a integer, b text);
3242 CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0);
3243 ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
3244 GRANT ALL ON copy_rel_to TO regress_rls_bob, regress_rls_exempt_user;
3245 INSERT INTO copy_rel_to VALUES (1, md5('1'));
3246 -- Check COPY TO as Superuser/owner.
3247 RESET SESSION AUTHORIZATION;
3248 SET row_security TO OFF;
3249 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3250 1,c4ca4238a0b923820dcc509a6f75849b
3251 SET row_security TO ON;
3252 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
3253 1,c4ca4238a0b923820dcc509a6f75849b
3254 -- Check COPY TO as user with permissions.
3255 SET SESSION AUTHORIZATION regress_rls_bob;
3256 SET row_security TO OFF;
3257 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
3258 ERROR: query would be affected by row-level security policy for table "copy_rel_to"
3259 SET row_security TO ON;
3260 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3261 -- Check COPY TO as user with permissions and BYPASSRLS
3262 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3263 SET row_security TO OFF;
3264 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3265 1,c4ca4238a0b923820dcc509a6f75849b
3266 SET row_security TO ON;
3267 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
3268 1,c4ca4238a0b923820dcc509a6f75849b
3269 -- Check COPY TO as user without permissions. SET row_security TO OFF;
3270 SET SESSION AUTHORIZATION regress_rls_carol;
3271 SET row_security TO OFF;
3272 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3273 ERROR: permission denied for table copy_rel_to
3274 SET row_security TO ON;
3275 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
3276 ERROR: permission denied for table copy_rel_to
3277 -- Check COPY FROM as Superuser/owner.
3278 RESET SESSION AUTHORIZATION;
3279 SET row_security TO OFF;
3280 COPY copy_t FROM STDIN; --ok
3281 SET row_security TO ON;
3282 COPY copy_t FROM STDIN; --ok
3283 -- Check COPY FROM as user with permissions.
3284 SET SESSION AUTHORIZATION regress_rls_bob;
3285 SET row_security TO OFF;
3286 COPY copy_t FROM STDIN; --fail - would be affected by RLS.
3287 ERROR: query would be affected by row-level security policy for table "copy_t"
3288 SET row_security TO ON;
3289 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
3290 ERROR: COPY FROM not supported with row-level security
3291 HINT: Use INSERT statements instead.
3292 -- Check COPY FROM as user with permissions and BYPASSRLS
3293 SET SESSION AUTHORIZATION regress_rls_exempt_user;
3294 SET row_security TO ON;
3295 COPY copy_t FROM STDIN; --ok
3296 -- Check COPY FROM as user without permissions.
3297 SET SESSION AUTHORIZATION regress_rls_carol;
3298 SET row_security TO OFF;
3299 COPY copy_t FROM STDIN; --fail - permission denied.
3300 ERROR: permission denied for table copy_t
3301 SET row_security TO ON;
3302 COPY copy_t FROM STDIN; --fail - permission denied.
3303 ERROR: permission denied for table copy_t
3304 RESET SESSION AUTHORIZATION;
3306 DROP TABLE copy_rel_to CASCADE;
3307 -- Check WHERE CURRENT OF
3308 SET SESSION AUTHORIZATION regress_rls_alice;
3309 CREATE TABLE current_check (currentid int, payload text, rlsuser text);
3310 GRANT ALL ON current_check TO PUBLIC;
3311 INSERT INTO current_check VALUES
3312 (1, 'abc', 'regress_rls_bob'),
3313 (2, 'bcd', 'regress_rls_bob'),
3314 (3, 'cde', 'regress_rls_bob'),
3315 (4, 'def', 'regress_rls_bob');
3316 CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
3317 CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
3318 CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user);
3319 ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
3320 SET SESSION AUTHORIZATION regress_rls_bob;
3321 -- Can SELECT even rows
3322 SELECT * FROM current_check;
3323 currentid | payload | rlsuser
3324 -----------+---------+-----------------
3325 2 | bcd | regress_rls_bob
3326 4 | def | regress_rls_bob
3329 -- Cannot UPDATE row 2
3330 UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *;
3331 currentid | payload | rlsuser
3332 -----------+---------+---------
3336 DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check;
3337 -- Returns rows that can be seen according to SELECT policy, like plain SELECT
3338 -- above (even rows)
3339 FETCH ABSOLUTE 1 FROM current_check_cursor;
3340 currentid | payload | rlsuser
3341 -----------+---------+-----------------
3342 2 | bcd | regress_rls_bob
3345 -- Still cannot UPDATE row 2 through cursor
3346 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
3347 currentid | payload | rlsuser
3348 -----------+---------+---------
3351 -- Can update row 4 through cursor, which is the next visible row
3352 FETCH RELATIVE 1 FROM current_check_cursor;
3353 currentid | payload | rlsuser
3354 -----------+---------+-----------------
3355 4 | def | regress_rls_bob
3358 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
3359 currentid | payload | rlsuser
3360 -----------+---------+-----------------
3361 4 | def_new | regress_rls_bob
3364 SELECT * FROM current_check;
3365 currentid | payload | rlsuser
3366 -----------+---------+-----------------
3367 2 | bcd | regress_rls_bob
3368 4 | def_new | regress_rls_bob
3371 -- Plan should be a subquery TID scan
3372 EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor;
3374 -------------------------------------------------------------
3375 Update on current_check
3376 -> Tid Scan on current_check
3377 TID Cond: CURRENT OF current_check_cursor
3378 Filter: ((currentid = 4) AND ((currentid % 2) = 0))
3381 -- Similarly can only delete row 4
3382 FETCH ABSOLUTE 1 FROM current_check_cursor;
3383 currentid | payload | rlsuser
3384 -----------+---------+-----------------
3385 2 | bcd | regress_rls_bob
3388 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
3389 currentid | payload | rlsuser
3390 -----------+---------+---------
3393 FETCH RELATIVE 1 FROM current_check_cursor;
3394 currentid | payload | rlsuser
3395 -----------+---------+-----------------
3396 4 | def | regress_rls_bob
3399 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
3400 currentid | payload | rlsuser
3401 -----------+---------+-----------------
3402 4 | def_new | regress_rls_bob
3405 SELECT * FROM current_check;
3406 currentid | payload | rlsuser
3407 -----------+---------+-----------------
3408 2 | bcd | regress_rls_bob
3413 -- check pg_stats view filtering
3415 SET row_security TO ON;
3416 SET SESSION AUTHORIZATION regress_rls_alice;
3417 ANALYZE current_check;
3419 SELECT row_security_active('current_check');
3421 ---------------------
3425 SELECT attname, most_common_vals FROM pg_stats
3426 WHERE tablename = 'current_check'
3428 attname | most_common_vals
3429 -----------+-------------------
3432 rlsuser | {regress_rls_bob}
3435 SET SESSION AUTHORIZATION regress_rls_bob;
3436 -- Stats not visible
3437 SELECT row_security_active('current_check');
3439 ---------------------
3443 SELECT attname, most_common_vals FROM pg_stats
3444 WHERE tablename = 'current_check'
3446 attname | most_common_vals
3447 ---------+------------------
3451 -- Collation support
3454 CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
3455 CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
3456 ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
3457 GRANT SELECT ON coll_t TO regress_rls_alice;
3458 SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
3464 SET SESSION AUTHORIZATION regress_rls_alice;
3465 SELECT * FROM coll_t;
3473 -- Shared Object Dependencies
3475 RESET SESSION AUTHORIZATION;
3477 CREATE ROLE regress_rls_eve;
3478 CREATE ROLE regress_rls_frank;
3479 CREATE TABLE tbl1 (c) AS VALUES ('bar'::text);
3480 GRANT SELECT ON TABLE tbl1 TO regress_rls_eve;
3481 CREATE POLICY P ON tbl1 TO regress_rls_eve, regress_rls_frank USING (true);
3482 SELECT refclassid::regclass, deptype
3484 WHERE classid = 'pg_policy'::regclass
3485 AND refobjid = 'tbl1'::regclass;
3486 refclassid | deptype
3487 ------------+---------
3491 SELECT refclassid::regclass, deptype
3493 WHERE classid = 'pg_policy'::regclass
3494 AND refobjid IN ('regress_rls_eve'::regrole, 'regress_rls_frank'::regrole);
3495 refclassid | deptype
3496 ------------+---------
3502 DROP ROLE regress_rls_eve; --fails due to dependency on POLICY p
3503 ERROR: role "regress_rls_eve" cannot be dropped because some objects depend on it
3504 DETAIL: privileges for table tbl1
3505 target of policy p on table tbl1
3507 ALTER POLICY p ON tbl1 TO regress_rls_frank USING (true);
3509 DROP ROLE regress_rls_eve; --fails due to dependency on GRANT SELECT
3510 ERROR: role "regress_rls_eve" cannot be dropped because some objects depend on it
3511 DETAIL: privileges for table tbl1
3513 REVOKE ALL ON TABLE tbl1 FROM regress_rls_eve;
3515 DROP ROLE regress_rls_eve; --succeeds
3518 DROP ROLE regress_rls_frank; --fails due to dependency on POLICY p
3519 ERROR: role "regress_rls_frank" cannot be dropped because some objects depend on it
3520 DETAIL: target of policy p on table tbl1
3522 DROP POLICY p ON tbl1;
3524 DROP ROLE regress_rls_frank; -- succeeds
3526 ROLLBACK; -- cleanup
3528 -- Converting table to view
3531 CREATE TABLE t (c int);
3532 CREATE POLICY p ON t USING (c % 2 = 1);
3533 ALTER TABLE t ENABLE ROW LEVEL SECURITY;
3535 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
3536 SELECT * FROM generate_series(1,5) t0(c); -- fails due to row level security enabled
3537 ERROR: could not convert table "t" to a view because it has row security enabled
3539 ALTER TABLE t DISABLE ROW LEVEL SECURITY;
3541 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
3542 SELECT * FROM generate_series(1,5) t0(c); -- fails due to policy p on t
3543 ERROR: could not convert table "t" to a view because it has row security policies
3546 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
3547 SELECT * FROM generate_series(1,5) t0(c); -- succeeds
3550 -- Policy expression handling
3553 CREATE TABLE t (c) AS VALUES ('bar'::text);
3554 CREATE POLICY p ON t USING (max(c)); -- fails: aggregate functions are not allowed in policy expressions
3555 ERROR: aggregate functions are not allowed in policy expressions
3558 -- Non-target relations are only subject to SELECT policies
3560 SET SESSION AUTHORIZATION regress_rls_alice;
3561 CREATE TABLE r1 (a int);
3562 CREATE TABLE r2 (a int);
3563 INSERT INTO r1 VALUES (10), (20);
3564 INSERT INTO r2 VALUES (10), (20);
3565 GRANT ALL ON r1, r2 TO regress_rls_bob;
3566 CREATE POLICY p1 ON r1 USING (true);
3567 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3568 CREATE POLICY p1 ON r2 FOR SELECT USING (true);
3569 CREATE POLICY p2 ON r2 FOR INSERT WITH CHECK (false);
3570 CREATE POLICY p3 ON r2 FOR UPDATE USING (false);
3571 CREATE POLICY p4 ON r2 FOR DELETE USING (false);
3572 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
3573 SET SESSION AUTHORIZATION regress_rls_bob;
3589 INSERT INTO r2 VALUES (2); -- Not allowed
3590 ERROR: new row violates row-level security policy for table "r2"
3591 UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing
3596 DELETE FROM r2 RETURNING *; -- Deletes nothing
3601 -- r2 can be used as a non-target relation in DML
3602 INSERT INTO r1 SELECT a + 1 FROM r2 RETURNING *; -- OK
3609 UPDATE r1 SET a = r2.a + 2 FROM r2 WHERE r1.a = r2.a RETURNING *; -- OK
3616 DELETE FROM r1 USING r2 WHERE r1.a = r2.a + 2 RETURNING *; -- OK
3637 SET SESSION AUTHORIZATION regress_rls_alice;
3641 -- FORCE ROW LEVEL SECURITY applies RLS to owners too
3643 SET SESSION AUTHORIZATION regress_rls_alice;
3644 SET row_security = on;
3645 CREATE TABLE r1 (a int);
3646 INSERT INTO r1 VALUES (10), (20);
3647 CREATE POLICY p1 ON r1 USING (false);
3648 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3649 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3650 -- No error, but no rows
3657 INSERT INTO r1 VALUES (1);
3658 ERROR: new row violates row-level security policy for table "r1"
3659 -- No error (unable to see any rows to update)
3660 UPDATE r1 SET a = 1;
3666 -- No error (unable to see any rows to delete)
3673 SET row_security = off;
3674 -- these all fail, would be affected by RLS
3676 ERROR: query would be affected by row-level security policy for table "r1"
3677 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
3678 UPDATE r1 SET a = 1;
3679 ERROR: query would be affected by row-level security policy for table "r1"
3680 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
3682 ERROR: query would be affected by row-level security policy for table "r1"
3683 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
3686 -- FORCE ROW LEVEL SECURITY does not break RI
3688 SET SESSION AUTHORIZATION regress_rls_alice;
3689 SET row_security = on;
3690 CREATE TABLE r1 (a int PRIMARY KEY);
3691 CREATE TABLE r2 (a int REFERENCES r1);
3692 INSERT INTO r1 VALUES (10), (20);
3693 INSERT INTO r2 VALUES (10), (20);
3694 -- Create policies on r2 which prevent the
3695 -- owner from seeing any rows, but RI should
3697 CREATE POLICY p1 ON r2 USING (false);
3698 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
3699 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
3700 -- Errors due to rows in r2
3702 ERROR: update or delete on table "r1" violates foreign key constraint "r2_a_fkey" on table "r2"
3703 DETAIL: Key (a)=(10) is still referenced from table "r2".
3704 -- Reset r2 to no-RLS
3705 DROP POLICY p1 ON r2;
3706 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
3707 ALTER TABLE r2 DISABLE ROW LEVEL SECURITY;
3708 -- clean out r2 for INSERT test below
3710 -- Change r1 to not allow rows to be seen
3711 CREATE POLICY p1 ON r1 USING (false);
3712 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3713 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3720 -- No error, RI still sees that row exists in r1
3721 INSERT INTO r2 VALUES (10);
3724 -- Ensure cascaded DELETE works
3725 CREATE TABLE r1 (a int PRIMARY KEY);
3726 CREATE TABLE r2 (a int REFERENCES r1 ON DELETE CASCADE);
3727 INSERT INTO r1 VALUES (10), (20);
3728 INSERT INTO r2 VALUES (10), (20);
3729 -- Create policies on r2 which prevent the
3730 -- owner from seeing any rows, but RI should
3732 CREATE POLICY p1 ON r2 USING (false);
3733 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
3734 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
3735 -- Deletes all records from both
3737 -- Remove FORCE from r2
3738 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
3739 -- As owner, we now bypass RLS
3740 -- verify no rows in r2 now
3748 -- Ensure cascaded UPDATE works
3749 CREATE TABLE r1 (a int PRIMARY KEY);
3750 CREATE TABLE r2 (a int REFERENCES r1 ON UPDATE CASCADE);
3751 INSERT INTO r1 VALUES (10), (20);
3752 INSERT INTO r2 VALUES (10), (20);
3753 -- Create policies on r2 which prevent the
3754 -- owner from seeing any rows, but RI should
3756 CREATE POLICY p1 ON r2 USING (false);
3757 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
3758 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
3759 -- Updates records in both
3760 UPDATE r1 SET a = a+5;
3761 -- Remove FORCE from r2
3762 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
3763 -- As owner, we now bypass RLS
3764 -- verify records in r2 updated
3775 -- Test INSERT+RETURNING applies SELECT policies as
3776 -- WithCheckOptions (meaning an error is thrown)
3778 SET SESSION AUTHORIZATION regress_rls_alice;
3779 SET row_security = on;
3780 CREATE TABLE r1 (a int);
3781 CREATE POLICY p1 ON r1 FOR SELECT USING (false);
3782 CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true);
3783 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3784 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3786 INSERT INTO r1 VALUES (10), (20);
3787 -- No error, but no rows
3793 SET row_security = off;
3794 -- fail, would be affected by RLS
3796 ERROR: query would be affected by row-level security policy for table "r1"
3797 HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY.
3798 SET row_security = on;
3800 INSERT INTO r1 VALUES (10), (20) RETURNING *;
3801 ERROR: new row violates row-level security policy for table "r1"
3804 -- Test UPDATE+RETURNING applies SELECT policies as
3805 -- WithCheckOptions (meaning an error is thrown)
3807 SET SESSION AUTHORIZATION regress_rls_alice;
3808 SET row_security = on;
3809 CREATE TABLE r1 (a int PRIMARY KEY);
3810 CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
3811 CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true);
3812 CREATE POLICY p3 ON r1 FOR INSERT WITH CHECK (true);
3813 INSERT INTO r1 VALUES (10);
3814 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
3815 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3817 UPDATE r1 SET a = 30;
3818 -- Show updated rows
3819 ALTER TABLE r1 NO FORCE ROW LEVEL SECURITY;
3826 -- reset value in r1 for test with RETURNING
3827 UPDATE r1 SET a = 10;
3835 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
3837 UPDATE r1 SET a = 30 RETURNING *;
3838 ERROR: new row violates row-level security policy for table "r1"
3839 -- UPDATE path of INSERT ... ON CONFLICT DO UPDATE should also error out
3840 INSERT INTO r1 VALUES (10)
3841 ON CONFLICT (a) DO UPDATE SET a = 30 RETURNING *;
3842 ERROR: new row violates row-level security policy for table "r1"
3843 -- Should still error out without RETURNING (use of arbiter always requires
3844 -- SELECT permissions)
3845 INSERT INTO r1 VALUES (10)
3846 ON CONFLICT (a) DO UPDATE SET a = 30;
3847 ERROR: new row violates row-level security policy for table "r1"
3848 INSERT INTO r1 VALUES (10)
3849 ON CONFLICT ON CONSTRAINT r1_pkey DO UPDATE SET a = 30;
3850 ERROR: new row violates row-level security policy for table "r1"
3852 -- Check dependency handling
3853 RESET SESSION AUTHORIZATION;
3854 CREATE TABLE dep1 (c1 int);
3855 CREATE TABLE dep2 (c1 int);
3856 CREATE POLICY dep_p1 ON dep1 TO regress_rls_bob USING (c1 > (select max(dep2.c1) from dep2));
3857 ALTER POLICY dep_p1 ON dep1 TO regress_rls_bob,regress_rls_carol;
3858 -- Should return one
3859 SELECT count(*) = 1 FROM pg_depend
3860 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
3861 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
3867 ALTER POLICY dep_p1 ON dep1 USING (true);
3868 -- Should return one
3869 SELECT count(*) = 1 FROM pg_shdepend
3870 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
3871 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_bob');
3877 -- Should return one
3878 SELECT count(*) = 1 FROM pg_shdepend
3879 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
3880 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_carol');
3886 -- Should return zero
3887 SELECT count(*) = 0 FROM pg_depend
3888 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
3889 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
3895 -- DROP OWNED BY testing
3896 RESET SESSION AUTHORIZATION;
3897 CREATE ROLE regress_rls_dob_role1;
3898 CREATE ROLE regress_rls_dob_role2;
3899 CREATE TABLE dob_t1 (c1 int);
3900 CREATE TABLE dob_t2 (c1 int) PARTITION BY RANGE (c1);
3901 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1 USING (true);
3902 DROP OWNED BY regress_rls_dob_role1;
3903 DROP POLICY p1 ON dob_t1; -- should fail, already gone
3904 ERROR: policy "p1" for table "dob_t1" does not exist
3905 CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
3906 DROP OWNED BY regress_rls_dob_role1;
3907 DROP POLICY p1 ON dob_t1; -- should succeed
3908 CREATE POLICY p1 ON dob_t2 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true);
3909 DROP OWNED BY regress_rls_dob_role1;
3910 DROP POLICY p1 ON dob_t2; -- should succeed
3911 DROP USER regress_rls_dob_role1;
3912 DROP USER regress_rls_dob_role2;
3913 -- Bug #15708: view + table with RLS should check policies as view owner
3914 CREATE TABLE ref_tbl (a int);
3915 INSERT INTO ref_tbl VALUES (1);
3916 CREATE TABLE rls_tbl (a int);
3917 INSERT INTO rls_tbl VALUES (10);
3918 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
3919 CREATE POLICY p1 ON rls_tbl USING (EXISTS (SELECT 1 FROM ref_tbl));
3920 GRANT SELECT ON ref_tbl TO regress_rls_bob;
3921 GRANT SELECT ON rls_tbl TO regress_rls_bob;
3922 CREATE VIEW rls_view AS SELECT * FROM rls_tbl;
3923 ALTER VIEW rls_view OWNER TO regress_rls_bob;
3924 GRANT SELECT ON rls_view TO regress_rls_alice;
3925 SET SESSION AUTHORIZATION regress_rls_alice;
3926 SELECT * FROM ref_tbl; -- Permission denied
3927 ERROR: permission denied for table ref_tbl
3928 SELECT * FROM rls_tbl; -- Permission denied
3929 ERROR: permission denied for table rls_tbl
3930 SELECT * FROM rls_view; -- OK
3936 RESET SESSION AUTHORIZATION;
3940 -- Leaky operator test
3941 CREATE TABLE rls_tbl (a int);
3942 INSERT INTO rls_tbl SELECT x/10 FROM generate_series(1, 100) x;
3944 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
3945 GRANT SELECT ON rls_tbl TO regress_rls_alice;
3946 SET SESSION AUTHORIZATION regress_rls_alice;
3947 CREATE FUNCTION op_leak(int, int) RETURNS bool
3948 AS 'BEGIN RAISE NOTICE ''op_leak => %, %'', $1, $2; RETURN $1 < $2; END'
3950 CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int,
3951 restrict = scalarltsel);
3952 SELECT * FROM rls_tbl WHERE a <<< 1000;
3957 DROP OPERATOR <<< (int, int);
3958 DROP FUNCTION op_leak(int, int);
3959 RESET SESSION AUTHORIZATION;
3961 -- Bug #16006: whole-row Vars in a policy don't play nice with sub-selects
3962 SET SESSION AUTHORIZATION regress_rls_alice;
3963 CREATE TABLE rls_tbl (a int, b int, c int);
3964 CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1));
3965 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
3966 ALTER TABLE rls_tbl FORCE ROW LEVEL SECURITY;
3967 INSERT INTO rls_tbl SELECT 10, 20, 30;
3968 EXPLAIN (VERBOSE, COSTS OFF)
3970 SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss;
3972 --------------------------------------------------------------------
3973 Insert on regress_rls_schema.rls_tbl
3974 -> Subquery Scan on ss
3975 Output: ss.b, ss.c, NULL::integer
3977 Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a
3978 Sort Key: rls_tbl_1.a
3979 -> Seq Scan on regress_rls_schema.rls_tbl rls_tbl_1
3980 Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a
3981 Filter: (rls_tbl_1.* >= '(1,1,1)'::record)
3985 SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss;
3986 SELECT * FROM rls_tbl;
3994 RESET SESSION AUTHORIZATION;
3998 RESET SESSION AUTHORIZATION;
3999 DROP SCHEMA regress_rls_schema CASCADE;
4000 NOTICE: drop cascades to 29 other objects
4001 DETAIL: drop cascades to function f_leak(text)
4002 drop cascades to table uaccount
4003 drop cascades to table category
4004 drop cascades to table document
4005 drop cascades to table part_document
4006 drop cascades to table dependent
4007 drop cascades to table rec1
4008 drop cascades to table rec2
4009 drop cascades to view rec1v
4010 drop cascades to view rec2v
4011 drop cascades to table s1
4012 drop cascades to table s2
4013 drop cascades to view v2
4014 drop cascades to table b1
4015 drop cascades to view bv1
4016 drop cascades to table z1
4017 drop cascades to table z2
4018 drop cascades to table x1
4019 drop cascades to table y1
4020 drop cascades to table y2
4021 drop cascades to table t1
4022 drop cascades to table t2
4023 drop cascades to table t3
4024 drop cascades to table t4
4025 drop cascades to table current_check
4026 drop cascades to table dep1
4027 drop cascades to table dep2
4028 drop cascades to table dob_t1
4029 drop cascades to table dob_t2
4030 DROP USER regress_rls_alice;
4031 DROP USER regress_rls_bob;
4032 DROP USER regress_rls_carol;
4033 DROP USER regress_rls_dave;
4034 DROP USER regress_rls_exempt_user;
4035 DROP ROLE regress_rls_group1;
4036 DROP ROLE regress_rls_group2;
4037 -- Arrange to have a few policies left over, for testing
4038 -- pg_dump/pg_restore
4039 CREATE SCHEMA regress_rls_schema;
4040 CREATE TABLE rls_tbl (c1 int);
4041 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
4042 CREATE POLICY p1 ON rls_tbl USING (c1 > 5);
4043 CREATE POLICY p2 ON rls_tbl FOR SELECT USING (c1 <= 3);
4044 CREATE POLICY p3 ON rls_tbl FOR UPDATE USING (c1 <= 3) WITH CHECK (c1 > 5);
4045 CREATE POLICY p4 ON rls_tbl FOR DELETE USING (c1 <= 3);
4046 CREATE TABLE rls_tbl_force (c1 int);
4047 ALTER TABLE rls_tbl_force ENABLE ROW LEVEL SECURITY;
4048 ALTER TABLE rls_tbl_force FORCE ROW LEVEL SECURITY;
4049 CREATE POLICY p1 ON rls_tbl_force USING (c1 = 5) WITH CHECK (c1 < 5);
4050 CREATE POLICY p2 ON rls_tbl_force FOR SELECT USING (c1 = 8);
4051 CREATE POLICY p3 ON rls_tbl_force FOR UPDATE USING (c1 = 8) WITH CHECK (c1 >= 5);
4052 CREATE POLICY p4 ON rls_tbl_force FOR DELETE USING (c1 = 8);