2 -- Test of Row-level security feature
5 -- Clean up in case a prior regression run failed
7 -- Suppress NOTICE messages when users/groups don't exist
8 SET client_min_messages TO 'warning';
10 DROP USER IF EXISTS rls_regress_user0;
11 DROP USER IF EXISTS rls_regress_user1;
12 DROP USER IF EXISTS rls_regress_user2;
13 DROP USER IF EXISTS rls_regress_exempt_user;
14 DROP ROLE IF EXISTS rls_regress_group1;
15 DROP ROLE IF EXISTS rls_regress_group2;
17 DROP SCHEMA IF EXISTS rls_regress_schema CASCADE;
19 RESET client_min_messages;
22 CREATE USER rls_regress_user0;
23 CREATE USER rls_regress_user1;
24 CREATE USER rls_regress_user2;
25 CREATE USER rls_regress_exempt_user BYPASSRLS;
26 CREATE ROLE rls_regress_group1 NOLOGIN;
27 CREATE ROLE rls_regress_group2 NOLOGIN;
29 GRANT rls_regress_group1 TO rls_regress_user1;
30 GRANT rls_regress_group2 TO rls_regress_user2;
32 CREATE SCHEMA rls_regress_schema;
33 GRANT ALL ON SCHEMA rls_regress_schema to public;
34 SET search_path = rls_regress_schema;
36 -- setup of malicious function
37 CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
38 COST 0.0000001 LANGUAGE plpgsql
39 AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
40 GRANT EXECUTE ON FUNCTION f_leak(text) TO public;
42 -- BASIC Row-Level Security Scenario
44 SET SESSION AUTHORIZATION rls_regress_user0;
45 CREATE TABLE uaccount (
46 pguser name primary key,
49 GRANT SELECT ON uaccount TO public;
50 INSERT INTO uaccount VALUES
51 ('rls_regress_user0', 99),
52 ('rls_regress_user1', 1),
53 ('rls_regress_user2', 2),
54 ('rls_regress_user3', 3);
56 CREATE TABLE category (
60 GRANT ALL ON category TO public;
61 INSERT INTO category VALUES
63 (22, 'science fiction'),
67 CREATE TABLE document (
69 cid int references category(cid),
74 GRANT ALL ON document TO public;
75 INSERT INTO document VALUES
76 ( 1, 11, 1, 'rls_regress_user1', 'my first novel'),
77 ( 2, 11, 2, 'rls_regress_user1', 'my second novel'),
78 ( 3, 22, 2, 'rls_regress_user1', 'my science fiction'),
79 ( 4, 44, 1, 'rls_regress_user1', 'my first manga'),
80 ( 5, 44, 2, 'rls_regress_user1', 'my second manga'),
81 ( 6, 22, 1, 'rls_regress_user2', 'great science fiction'),
82 ( 7, 33, 2, 'rls_regress_user2', 'great technology book'),
83 ( 8, 44, 1, 'rls_regress_user2', 'great manga');
85 ALTER TABLE document ENABLE ROW LEVEL SECURITY;
87 -- user's security level must be higher than or equal to document's
88 CREATE POLICY p1 ON document
89 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
91 -- viewpoint from rls_regress_user1
92 SET SESSION AUTHORIZATION rls_regress_user1;
93 SET row_security TO ON;
94 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
95 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
97 -- try a sampled version
98 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
99 WHERE f_leak(dtitle) ORDER BY did;
101 -- viewpoint from rls_regress_user2
102 SET SESSION AUTHORIZATION rls_regress_user2;
103 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
104 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
106 -- try a sampled version
107 SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
108 WHERE f_leak(dtitle) ORDER BY did;
110 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
111 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
113 -- only owner can change policies
114 ALTER POLICY p1 ON document USING (true); --fail
115 DROP POLICY p1 ON document; --fail
117 SET SESSION AUTHORIZATION rls_regress_user0;
118 ALTER POLICY p1 ON document USING (dauthor = current_user);
120 -- viewpoint from rls_regress_user1 again
121 SET SESSION AUTHORIZATION rls_regress_user1;
122 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
123 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
125 -- viewpoint from rls_regres_user2 again
126 SET SESSION AUTHORIZATION rls_regress_user2;
127 SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
128 SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did;
130 EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
131 EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
133 -- interaction of FK/PK constraints
134 SET SESSION AUTHORIZATION rls_regress_user0;
135 CREATE POLICY p2 ON category
136 USING (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33)
137 WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44)
140 ALTER TABLE category ENABLE ROW LEVEL SECURITY;
142 -- cannot delete PK referenced by invisible FK
143 SET SESSION AUTHORIZATION rls_regress_user1;
144 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
145 DELETE FROM category WHERE cid = 33; -- fails with FK violation
147 -- can insert FK referencing invisible PK
148 SET SESSION AUTHORIZATION rls_regress_user2;
149 SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
150 INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');
152 -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
153 SET SESSION AUTHORIZATION rls_regress_user1;
154 INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user1', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
155 SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
157 -- RLS policies are checked before constraints
158 INSERT INTO document VALUES (8, 44, 1, 'rls_regress_user2', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation
159 UPDATE document SET did = 8, dauthor = 'rls_regress_user2' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation
161 -- database superuser does bypass RLS policy when enabled
162 RESET SESSION AUTHORIZATION;
163 SET row_security TO ON;
164 SELECT * FROM document;
165 SELECT * FROM category;
167 -- database superuser does bypass RLS policy when disabled
168 RESET SESSION AUTHORIZATION;
169 SET row_security TO OFF;
170 SELECT * FROM document;
171 SELECT * FROM category;
173 -- database non-superuser with bypass privilege can bypass RLS policy when disabled
174 SET SESSION AUTHORIZATION rls_regress_exempt_user;
175 SET row_security TO OFF;
176 SELECT * FROM document;
177 SELECT * FROM category;
179 -- RLS policy does not apply to table owner when RLS enabled.
180 SET SESSION AUTHORIZATION rls_regress_user0;
181 SET row_security TO ON;
182 SELECT * FROM document;
183 SELECT * FROM category;
185 -- RLS policy does not apply to table owner when RLS disabled.
186 SET SESSION AUTHORIZATION rls_regress_user0;
187 SET row_security TO OFF;
188 SELECT * FROM document;
189 SELECT * FROM category;
192 -- Table inheritance and RLS policy
194 SET SESSION AUTHORIZATION rls_regress_user0;
196 SET row_security TO ON;
198 CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS;
199 ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor
200 GRANT ALL ON t1 TO public;
202 COPY t1 FROM stdin WITH (oids);
209 CREATE TABLE t2 (c float) INHERITS (t1);
210 GRANT ALL ON t2 TO public;
212 COPY t2 FROM stdin WITH (oids);
219 CREATE TABLE t3 (c text, b text, a int) WITH OIDS;
220 ALTER TABLE t3 INHERIT t1;
221 GRANT ALL ON t3 TO public;
223 COPY t3(a,b,c) FROM stdin WITH (oids);
229 CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number
230 CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number
232 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
233 ALTER TABLE t2 ENABLE ROW LEVEL SECURITY;
235 SET SESSION AUTHORIZATION rls_regress_user1;
238 EXPLAIN (COSTS OFF) SELECT * FROM t1;
240 SELECT * FROM t1 WHERE f_leak(b);
241 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
243 -- reference to system column
244 SELECT oid, * FROM t1;
245 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
247 -- reference to whole-row reference
248 SELECT *, t1 FROM t1;
249 EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1;
251 -- for share/update lock
252 SELECT * FROM t1 FOR SHARE;
253 EXPLAIN (COSTS OFF) SELECT * FROM t1 FOR SHARE;
255 SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
256 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
259 SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3;
260 EXPLAIN (COSTS OFF) SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3;
262 -- superuser is allowed to bypass RLS checks
263 RESET SESSION AUTHORIZATION;
264 SET row_security TO OFF;
265 SELECT * FROM t1 WHERE f_leak(b);
266 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
268 -- non-superuser with bypass privilege can bypass RLS policy when disabled
269 SET SESSION AUTHORIZATION rls_regress_exempt_user;
270 SET row_security TO OFF;
271 SELECT * FROM t1 WHERE f_leak(b);
272 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
274 ----- Dependencies -----
275 SET SESSION AUTHORIZATION rls_regress_user0;
276 SET row_security TO ON;
278 CREATE TABLE dependee (x integer, y integer);
280 CREATE TABLE dependent (x integer, y integer);
281 CREATE POLICY d1 ON dependent FOR ALL
283 USING (x = (SELECT d.x FROM dependee d WHERE d.y = y));
285 DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row security qual?
287 DROP TABLE dependee CASCADE;
289 EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified
296 SET SESSION AUTHORIZATION rls_regress_user0;
297 CREATE TABLE rec1 (x integer, y integer);
298 CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y));
299 ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY;
300 SET SESSION AUTHORIZATION rls_regress_user1;
301 SELECT * FROM rec1; -- fail, direct recursion
306 SET SESSION AUTHORIZATION rls_regress_user0;
307 CREATE TABLE rec2 (a integer, b integer);
308 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y));
309 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b));
310 ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY;
312 SET SESSION AUTHORIZATION rls_regress_user1;
313 SELECT * FROM rec1; -- fail, mutual recursion
316 -- Mutual recursion via views
318 SET SESSION AUTHORIZATION rls_regress_user1;
319 CREATE VIEW rec1v AS SELECT * FROM rec1;
320 CREATE VIEW rec2v AS SELECT * FROM rec2;
321 SET SESSION AUTHORIZATION rls_regress_user0;
322 ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
323 ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
325 SET SESSION AUTHORIZATION rls_regress_user1;
326 SELECT * FROM rec1; -- fail, mutual recursion via views
329 -- Mutual recursion via .s.b views
331 SET SESSION AUTHORIZATION rls_regress_user1;
332 -- Suppress NOTICE messages when doing a cascaded drop.
333 SET client_min_messages TO 'warning';
335 DROP VIEW rec1v, rec2v CASCADE;
336 RESET client_min_messages;
338 CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1;
339 CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2;
340 SET SESSION AUTHORIZATION rls_regress_user0;
341 CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y));
342 CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b));
344 SET SESSION AUTHORIZATION rls_regress_user1;
345 SELECT * FROM rec1; -- fail, mutual recursion via s.b. views
348 -- recursive RLS and VIEWs in policy
350 SET SESSION AUTHORIZATION rls_regress_user0;
351 CREATE TABLE s1 (a int, b text);
352 INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
354 CREATE TABLE s2 (x int, y text);
355 INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
357 GRANT SELECT ON s1, s2 TO rls_regress_user1;
359 CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%'));
360 CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%'));
361 CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1));
363 ALTER TABLE s1 ENABLE ROW LEVEL SECURITY;
364 ALTER TABLE s2 ENABLE ROW LEVEL SECURITY;
366 SET SESSION AUTHORIZATION rls_regress_user1;
367 CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
368 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
370 INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion)
372 SET SESSION AUTHORIZATION rls_regress_user0;
373 DROP POLICY p3 on s1;
374 ALTER POLICY p2 ON s2 USING (x % 2 = 0);
376 SET SESSION AUTHORIZATION rls_regress_user1;
377 SELECT * FROM s1 WHERE f_leak(b); -- OK
378 EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b);
380 SET SESSION AUTHORIZATION rls_regress_user0;
381 ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy
382 SET SESSION AUTHORIZATION rls_regress_user1;
383 SELECT * FROM s1 WHERE f_leak(b); -- OK
384 EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
386 SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
387 EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
389 SET SESSION AUTHORIZATION rls_regress_user0;
390 ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%'));
391 SET SESSION AUTHORIZATION rls_regress_user1;
392 SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
394 -- prepared statement with rls_regress_user0 privilege
395 PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
397 EXPLAIN (COSTS OFF) EXECUTE p1(2);
399 -- superuser is allowed to bypass RLS checks
400 RESET SESSION AUTHORIZATION;
401 SET row_security TO OFF;
402 SELECT * FROM t1 WHERE f_leak(b);
403 EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b);
405 -- plan cache should be invalidated
407 EXPLAIN (COSTS OFF) EXECUTE p1(2);
409 PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1;
411 EXPLAIN (COSTS OFF) EXECUTE p2(2);
413 -- also, case when privilege switch from superuser
414 SET SESSION AUTHORIZATION rls_regress_user1;
415 SET row_security TO ON;
417 EXPLAIN (COSTS OFF) EXECUTE p2(2);
420 -- UPDATE / DELETE and Row-level security
422 SET SESSION AUTHORIZATION rls_regress_user1;
423 EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b);
424 UPDATE t1 SET b = b || b WHERE f_leak(b);
426 EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
427 UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
429 -- returning clause with system column
430 UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
431 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
432 UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
434 -- updates with from clause
435 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3
436 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
438 UPDATE t2 SET b=t2.b FROM t3
439 WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b);
441 EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2
442 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
444 UPDATE t1 SET b=t1.b FROM t2
445 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
447 EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1
448 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
450 UPDATE t2 SET b=t2.b FROM t1
451 WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b);
453 -- updates with from clause self join
454 EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
455 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
456 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
458 UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2
459 WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b
460 AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2;
462 EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
463 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
464 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
466 UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2
467 WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b
468 AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2;
470 RESET SESSION AUTHORIZATION;
471 SET row_security TO OFF;
472 SELECT * FROM t1 ORDER BY a,b;
474 SET SESSION AUTHORIZATION rls_regress_user1;
475 SET row_security TO ON;
476 EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b);
477 EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b);
479 DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1;
480 DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1;
483 -- S.b. view on top of Row-level security
485 SET SESSION AUTHORIZATION rls_regress_user0;
486 CREATE TABLE b1 (a int, b text);
487 INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
489 CREATE POLICY p1 ON b1 USING (a % 2 = 0);
490 ALTER TABLE b1 ENABLE ROW LEVEL SECURITY;
491 GRANT ALL ON b1 TO rls_regress_user1;
493 SET SESSION AUTHORIZATION rls_regress_user1;
494 CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION;
495 GRANT ALL ON bv1 TO rls_regress_user2;
497 SET SESSION AUTHORIZATION rls_regress_user2;
499 EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b);
500 SELECT * FROM bv1 WHERE f_leak(b);
502 INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO
503 INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check
504 INSERT INTO bv1 VALUES (12, 'xxx'); -- ok
506 EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
507 UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b);
509 EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
510 DELETE FROM bv1 WHERE a = 6 AND f_leak(b);
512 SET SESSION AUTHORIZATION rls_regress_user0;
515 -- INSERT ... ON CONFLICT DO UPDATE and Row-level security
518 SET SESSION AUTHORIZATION rls_regress_user0;
519 DROP POLICY p1 ON document;
521 CREATE POLICY p1 ON document FOR SELECT USING (true);
522 CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
523 CREATE POLICY p3 ON document FOR UPDATE
524 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
525 WITH CHECK (dauthor = current_user);
527 SET SESSION AUTHORIZATION rls_regress_user1;
530 SELECT * FROM document WHERE did = 2;
532 -- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since
533 -- alternative UPDATE path happens to be taken):
534 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
535 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor;
537 -- Violates USING qual for UPDATE policy p3.
539 -- UPDATE path is taken, but UPDATE fails purely because *existing* row to be
540 -- updated is not a "novel"/cid 11 (row is not leaked, even though we have
541 -- SELECT privileges sufficient to see the row in this instance):
542 INSERT INTO document VALUES (33, 22, 1, 'rls_regress_user1', 'okay science fiction'); -- preparation for next statement
543 INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'Some novel, replaces sci-fi') -- takes UPDATE path
544 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
545 -- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs
547 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
548 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
549 -- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated):
550 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
551 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
552 -- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the
553 -- case in respect of *existing* tuple):
554 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
555 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
556 -- Same query a third time, but now fails due to existing tuple finally not
558 INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'some technology novel')
559 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *;
560 -- Don't fail just because INSERT doesn't satisfy WITH CHECK option that
561 -- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE
562 -- path *isn't* taken, and so UPDATE-related policy does not apply:
563 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
564 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
565 -- But this time, the same statement fails, because the UPDATE path is taken,
566 -- and updating the row just inserted falls afoul of security barrier qual
567 -- (enforced as WCO) -- what we might have updated target tuple to is
568 -- irrelevant, in fact.
569 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
570 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
572 -- Test default USING qual enforced as WCO
573 SET SESSION AUTHORIZATION rls_regress_user0;
574 DROP POLICY p1 ON document;
575 DROP POLICY p2 ON document;
576 DROP POLICY p3 ON document;
578 CREATE POLICY p3_with_default ON document FOR UPDATE
579 USING (cid = (SELECT cid from category WHERE cname = 'novel'));
581 SET SESSION AUTHORIZATION rls_regress_user1;
582 -- Just because WCO-style enforcement of USING quals occurs with
583 -- existing/target tuple does not mean that the implementation can be allowed
584 -- to fail to also enforce this qual against the final tuple appended to
585 -- relation (since in the absence of an explicit WCO, this is also interpreted
586 -- as an UPDATE/ALL WCO in general).
588 -- UPDATE path is taken here (fails due to existing tuple). Note that this is
589 -- not reported as a "USING expression", because it's an RLS UPDATE check that originated as
590 -- a USING qual for the purposes of RLS in general, as opposed to an explicit
591 -- USING qual that is ordinarily a security barrier. We leave it up to the
592 -- UPDATE to make this fail:
593 INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'technology book, can only insert')
594 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *;
596 -- UPDATE path is taken here. Existing tuple passes, since it's cid
597 -- corresponds to "novel", but default USING qual is enforced against
598 -- post-UPDATE tuple too (as always when updating with a policy that lacks an
599 -- explicit WCO), and so this fails:
600 INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'rls_regress_user1', 'my first novel')
601 ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *;
603 SET SESSION AUTHORIZATION rls_regress_user0;
604 DROP POLICY p3_with_default ON document;
607 -- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE
610 CREATE POLICY p3_with_all ON document FOR ALL
611 USING (cid = (SELECT cid from category WHERE cname = 'novel'))
612 WITH CHECK (dauthor = current_user);
614 SET SESSION AUTHORIZATION rls_regress_user1;
616 -- Fails, since ALL WCO is enforced in insert path:
617 INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user2', 'my first novel')
618 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33;
619 -- Fails, since ALL policy USING qual is enforced (existing, target tuple is in
620 -- violation, since it has the "manga" cid):
621 INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
622 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle;
623 -- Fails, since ALL WCO are enforced:
624 INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'rls_regress_user1', 'my first novel')
625 ON CONFLICT (did) DO UPDATE SET dauthor = 'rls_regress_user2';
630 SET SESSION AUTHORIZATION rls_regress_user0;
631 CREATE TABLE z1 (a int, b text);
632 CREATE TABLE z2 (a int, b text);
634 GRANT SELECT ON z1,z2 TO rls_regress_group1, rls_regress_group2,
635 rls_regress_user1, rls_regress_user2;
637 INSERT INTO z1 VALUES
643 CREATE POLICY p1 ON z1 TO rls_regress_group1 USING (a % 2 = 0);
644 CREATE POLICY p2 ON z1 TO rls_regress_group2 USING (a % 2 = 1);
646 ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
648 SET SESSION AUTHORIZATION rls_regress_user1;
649 SELECT * FROM z1 WHERE f_leak(b);
650 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
652 PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b);
653 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
655 PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2;
656 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
658 PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b);
659 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
661 SET ROLE rls_regress_group1;
662 SELECT * FROM z1 WHERE f_leak(b);
663 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
665 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
666 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
667 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
669 SET SESSION AUTHORIZATION rls_regress_user2;
670 SELECT * FROM z1 WHERE f_leak(b);
671 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
673 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
674 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
675 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
677 SET ROLE rls_regress_group2;
678 SELECT * FROM z1 WHERE f_leak(b);
679 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
681 EXPLAIN (COSTS OFF) EXECUTE plancache_test;
682 EXPLAIN (COSTS OFF) EXECUTE plancache_test2;
683 EXPLAIN (COSTS OFF) EXECUTE plancache_test3;
686 -- Views should follow policy for view owner.
688 -- View and Table owner are the same.
689 SET SESSION AUTHORIZATION rls_regress_user0;
690 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
691 GRANT SELECT ON rls_view TO rls_regress_user1;
693 -- Query as role that is not owner of view or table. Should return all records.
694 SET SESSION AUTHORIZATION rls_regress_user1;
695 SELECT * FROM rls_view;
696 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
698 -- Query as view/table owner. Should return all records.
699 SET SESSION AUTHORIZATION rls_regress_user0;
700 SELECT * FROM rls_view;
701 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
704 -- View and Table owners are different.
705 SET SESSION AUTHORIZATION rls_regress_user1;
706 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
707 GRANT SELECT ON rls_view TO rls_regress_user0;
709 -- Query as role that is not owner of view but is owner of table.
710 -- Should return records based on view owner policies.
711 SET SESSION AUTHORIZATION rls_regress_user0;
712 SELECT * FROM rls_view;
713 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
715 -- Query as role that is not owner of table but is owner of view.
716 -- Should return records based on view owner policies.
717 SET SESSION AUTHORIZATION rls_regress_user1;
718 SELECT * FROM rls_view;
719 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
721 -- Query as role that is not the owner of the table or view without permissions.
722 SET SESSION AUTHORIZATION rls_regress_user2;
723 SELECT * FROM rls_view; --fail - permission denied.
724 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
726 -- Query as role that is not the owner of the table or view with permissions.
727 SET SESSION AUTHORIZATION rls_regress_user1;
728 GRANT SELECT ON rls_view TO rls_regress_user2;
729 SELECT * FROM rls_view;
730 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
732 SET SESSION AUTHORIZATION rls_regress_user1;
738 SET SESSION AUTHORIZATION rls_regress_user0;
740 CREATE TABLE x1 (a int, b text, c text);
741 GRANT ALL ON x1 TO PUBLIC;
743 INSERT INTO x1 VALUES
744 (1, 'abc', 'rls_regress_user1'),
745 (2, 'bcd', 'rls_regress_user1'),
746 (3, 'cde', 'rls_regress_user2'),
747 (4, 'def', 'rls_regress_user2'),
748 (5, 'efg', 'rls_regress_user1'),
749 (6, 'fgh', 'rls_regress_user1'),
750 (7, 'fgh', 'rls_regress_user2'),
751 (8, 'fgh', 'rls_regress_user2');
753 CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
754 CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
755 CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1);
756 CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0);
757 CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
759 ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
761 SET SESSION AUTHORIZATION rls_regress_user1;
762 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
763 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
765 SET SESSION AUTHORIZATION rls_regress_user2;
766 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
767 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
768 DELETE FROM x1 WHERE f_leak(b) RETURNING *;
771 -- Duplicate Policy Names
773 SET SESSION AUTHORIZATION rls_regress_user0;
774 CREATE TABLE y1 (a int, b text);
775 CREATE TABLE y2 (a int, b text);
777 GRANT ALL ON y1, y2 TO rls_regress_user1;
779 CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
780 CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
781 CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
782 CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK
784 ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;
785 ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
788 -- Expression structure with SBV
790 -- Create view as table owner. RLS should NOT be applied.
791 SET SESSION AUTHORIZATION rls_regress_user0;
792 CREATE VIEW rls_sbv WITH (security_barrier) AS
793 SELECT * FROM y1 WHERE f_leak(b);
794 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
797 -- Create view as role that does not own table. RLS should be applied.
798 SET SESSION AUTHORIZATION rls_regress_user1;
799 CREATE VIEW rls_sbv WITH (security_barrier) AS
800 SELECT * FROM y1 WHERE f_leak(b);
801 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
805 -- Expression structure
807 SET SESSION AUTHORIZATION rls_regress_user0;
808 INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
809 CREATE POLICY p2 ON y2 USING (a % 3 = 0);
810 CREATE POLICY p3 ON y2 USING (a % 4 = 0);
812 SET SESSION AUTHORIZATION rls_regress_user1;
813 SELECT * FROM y2 WHERE f_leak(b);
814 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
817 -- Qual push-down of leaky functions, when not referring to table
819 SELECT * FROM y2 WHERE f_leak('abc');
820 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc');
822 CREATE TABLE test_qual_pushdown (
826 INSERT INTO test_qual_pushdown VALUES ('abc'),('def');
828 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
829 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
831 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
832 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
834 DROP TABLE test_qual_pushdown;
837 -- Plancache invalidate on user change.
839 RESET SESSION AUTHORIZATION;
840 -- Suppress NOTICE messages when doing a cascaded drop.
841 SET client_min_messages TO 'warning';
843 DROP TABLE t1 CASCADE;
844 RESET client_min_messages;
846 CREATE TABLE t1 (a integer);
848 GRANT SELECT ON t1 TO rls_regress_user1, rls_regress_user2;
850 CREATE POLICY p1 ON t1 TO rls_regress_user1 USING ((a % 2) = 0);
851 CREATE POLICY p2 ON t1 TO rls_regress_user2 USING ((a % 4) = 0);
853 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
855 -- Prepare as rls_regress_user1
856 SET ROLE rls_regress_user1;
857 PREPARE role_inval AS SELECT * FROM t1;
859 EXPLAIN (COSTS OFF) EXECUTE role_inval;
861 -- Change to rls_regress_user2
862 SET ROLE rls_regress_user2;
863 -- Check plan- should be different
864 EXPLAIN (COSTS OFF) EXECUTE role_inval;
866 -- Change back to rls_regress_user1
867 SET ROLE rls_regress_user1;
868 -- Check plan- should be back to original
869 EXPLAIN (COSTS OFF) EXECUTE role_inval;
874 RESET SESSION AUTHORIZATION;
875 DROP TABLE t1 CASCADE;
876 CREATE TABLE t1 (a integer, b text);
877 CREATE POLICY p1 ON t1 USING (a % 2 = 0);
879 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
881 GRANT ALL ON t1 TO rls_regress_user1;
883 INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
885 SET SESSION AUTHORIZATION rls_regress_user1;
887 WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
888 EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
890 WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
891 WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
893 WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail
894 WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok
899 RESET SESSION AUTHORIZATION;
900 ALTER POLICY p1 ON t1 RENAME TO p1; --fail
902 SELECT polname, relname
904 JOIN pg_class pc ON (pc.oid = pol.polrelid)
905 WHERE relname = 't1';
907 ALTER POLICY p1 ON t1 RENAME TO p2; --ok
909 SELECT polname, relname
911 JOIN pg_class pc ON (pc.oid = pol.polrelid)
912 WHERE relname = 't1';
915 -- Check INSERT SELECT
917 SET SESSION AUTHORIZATION rls_regress_user1;
918 CREATE TABLE t2 (a integer, b text);
919 INSERT INTO t2 (SELECT * FROM t1);
920 EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
922 EXPLAIN (COSTS OFF) SELECT * FROM t2;
923 CREATE TABLE t3 AS SELECT * FROM t1;
925 SELECT * INTO t4 FROM t1;
931 SET SESSION AUTHORIZATION rls_regress_user0;
932 CREATE TABLE blog (id integer, author text, post text);
933 CREATE TABLE comment (blog_id integer, message text);
935 GRANT ALL ON blog, comment TO rls_regress_user1;
937 CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
939 ALTER TABLE blog ENABLE ROW LEVEL SECURITY;
941 INSERT INTO blog VALUES
942 (1, 'alice', 'blog #1'),
943 (2, 'bob', 'blog #1'),
944 (3, 'alice', 'blog #2'),
945 (4, 'alice', 'blog #3'),
946 (5, 'john', 'blog #1');
948 INSERT INTO comment VALUES
956 SET SESSION AUTHORIZATION rls_regress_user1;
957 -- Check RLS JOIN with Non-RLS.
958 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
959 -- Check Non-RLS JOIN with RLS.
960 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
962 SET SESSION AUTHORIZATION rls_regress_user0;
963 CREATE POLICY comment_1 ON comment USING (blog_id < 4);
965 ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
967 SET SESSION AUTHORIZATION rls_regress_user1;
968 -- Check RLS JOIN RLS
969 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
970 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
972 SET SESSION AUTHORIZATION rls_regress_user0;
973 DROP TABLE blog, comment;
976 -- Default Deny Policy
978 RESET SESSION AUTHORIZATION;
979 DROP POLICY p2 ON t1;
980 ALTER TABLE t1 OWNER TO rls_regress_user0;
982 -- Check that default deny does not apply to superuser.
983 RESET SESSION AUTHORIZATION;
985 EXPLAIN (COSTS OFF) SELECT * FROM t1;
987 -- Check that default deny does not apply to table owner.
988 SET SESSION AUTHORIZATION rls_regress_user0;
990 EXPLAIN (COSTS OFF) SELECT * FROM t1;
992 -- Check that default deny applies to non-owner/non-superuser when RLS on.
993 SET SESSION AUTHORIZATION rls_regress_user1;
994 SET row_security TO ON;
996 EXPLAIN (COSTS OFF) SELECT * FROM t1;
997 SET SESSION AUTHORIZATION rls_regress_user1;
999 EXPLAIN (COSTS OFF) SELECT * FROM t1;
1005 RESET SESSION AUTHORIZATION;
1006 DROP TABLE copy_t CASCADE;
1007 CREATE TABLE copy_t (a integer, b text);
1008 CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
1010 ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
1012 GRANT ALL ON copy_t TO rls_regress_user1, rls_regress_exempt_user;
1014 INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
1016 -- Check COPY TO as Superuser/owner.
1017 RESET SESSION AUTHORIZATION;
1018 SET row_security TO OFF;
1019 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
1020 SET row_security TO ON;
1021 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
1023 -- Check COPY TO as user with permissions.
1024 SET SESSION AUTHORIZATION rls_regress_user1;
1025 SET row_security TO OFF;
1026 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
1027 SET row_security TO ON;
1028 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
1030 -- Check COPY TO as user with permissions and BYPASSRLS
1031 SET SESSION AUTHORIZATION rls_regress_exempt_user;
1032 SET row_security TO OFF;
1033 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
1034 SET row_security TO ON;
1035 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
1037 -- Check COPY TO as user without permissions. SET row_security TO OFF;
1038 SET SESSION AUTHORIZATION rls_regress_user2;
1039 SET row_security TO OFF;
1040 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
1041 SET row_security TO ON;
1042 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
1044 -- Check COPY relation TO; keep it just one row to avoid reordering issues
1045 RESET SESSION AUTHORIZATION;
1046 SET row_security TO ON;
1047 CREATE TABLE copy_rel_to (a integer, b text);
1048 CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0);
1050 ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
1052 GRANT ALL ON copy_rel_to TO rls_regress_user1, rls_regress_exempt_user;
1054 INSERT INTO copy_rel_to VALUES (1, md5('1'));
1056 -- Check COPY TO as Superuser/owner.
1057 RESET SESSION AUTHORIZATION;
1058 SET row_security TO OFF;
1059 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
1060 SET row_security TO ON;
1061 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
1063 -- Check COPY TO as user with permissions.
1064 SET SESSION AUTHORIZATION rls_regress_user1;
1065 SET row_security TO OFF;
1066 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS
1067 SET row_security TO ON;
1068 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1070 -- Check COPY TO as user with permissions and BYPASSRLS
1071 SET SESSION AUTHORIZATION rls_regress_exempt_user;
1072 SET row_security TO OFF;
1073 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1074 SET row_security TO ON;
1075 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1077 -- Check COPY TO as user without permissions. SET row_security TO OFF;
1078 SET SESSION AUTHORIZATION rls_regress_user2;
1079 SET row_security TO OFF;
1080 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
1081 SET row_security TO ON;
1082 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
1084 -- Check COPY FROM as Superuser/owner.
1085 RESET SESSION AUTHORIZATION;
1086 SET row_security TO OFF;
1087 COPY copy_t FROM STDIN; --ok
1093 SET row_security TO ON;
1094 COPY copy_t FROM STDIN; --ok
1101 -- Check COPY FROM as user with permissions.
1102 SET SESSION AUTHORIZATION rls_regress_user1;
1103 SET row_security TO OFF;
1104 COPY copy_t FROM STDIN; --fail - would be affected by RLS.
1105 SET row_security TO ON;
1106 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
1108 -- Check COPY FROM as user with permissions and BYPASSRLS
1109 SET SESSION AUTHORIZATION rls_regress_exempt_user;
1110 SET row_security TO ON;
1111 COPY copy_t FROM STDIN; --ok
1118 -- Check COPY FROM as user without permissions.
1119 SET SESSION AUTHORIZATION rls_regress_user2;
1120 SET row_security TO OFF;
1121 COPY copy_t FROM STDIN; --fail - permission denied.
1122 SET row_security TO ON;
1123 COPY copy_t FROM STDIN; --fail - permission denied.
1125 RESET SESSION AUTHORIZATION;
1127 DROP TABLE copy_rel_to CASCADE;
1129 -- Check WHERE CURRENT OF
1130 SET SESSION AUTHORIZATION rls_regress_user0;
1132 CREATE TABLE current_check (currentid int, payload text, rlsuser text);
1133 GRANT ALL ON current_check TO PUBLIC;
1135 INSERT INTO current_check VALUES
1136 (1, 'abc', 'rls_regress_user1'),
1137 (2, 'bcd', 'rls_regress_user1'),
1138 (3, 'cde', 'rls_regress_user1'),
1139 (4, 'def', 'rls_regress_user1');
1141 CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
1142 CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
1143 CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user);
1145 ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
1147 SET SESSION AUTHORIZATION rls_regress_user1;
1149 -- Can SELECT even rows
1150 SELECT * FROM current_check;
1152 -- Cannot UPDATE row 2
1153 UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *;
1157 DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check;
1158 -- Returns rows that can be seen according to SELECT policy, like plain SELECT
1159 -- above (even rows)
1160 FETCH ABSOLUTE 1 FROM current_check_cursor;
1161 -- Still cannot UPDATE row 2 through cursor
1162 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
1163 -- Can update row 4 through cursor, which is the next visible row
1164 FETCH RELATIVE 1 FROM current_check_cursor;
1165 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
1166 SELECT * FROM current_check;
1167 -- Plan should be a subquery TID scan
1168 EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor;
1169 -- Similarly can only delete row 4
1170 FETCH ABSOLUTE 1 FROM current_check_cursor;
1171 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
1172 FETCH RELATIVE 1 FROM current_check_cursor;
1173 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
1174 SELECT * FROM current_check;
1179 -- check pg_stats view filtering
1181 SET row_security TO ON;
1182 SET SESSION AUTHORIZATION rls_regress_user0;
1183 ANALYZE current_check;
1185 SELECT row_security_active('current_check');
1186 SELECT attname, most_common_vals FROM pg_stats
1187 WHERE tablename = 'current_check'
1190 SET SESSION AUTHORIZATION rls_regress_user1;
1191 -- Stats not visible
1192 SELECT row_security_active('current_check');
1193 SELECT attname, most_common_vals FROM pg_stats
1194 WHERE tablename = 'current_check'
1198 -- Collation support
1201 CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
1202 CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
1203 ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
1204 GRANT SELECT ON coll_t TO rls_regress_user0;
1205 SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
1206 SET SESSION AUTHORIZATION rls_regress_user0;
1207 SELECT * FROM coll_t;
1211 -- Shared Object Dependencies
1213 RESET SESSION AUTHORIZATION;
1217 CREATE TABLE tbl1 (c) AS VALUES ('bar'::text);
1218 GRANT SELECT ON TABLE tbl1 TO alice;
1219 CREATE POLICY P ON tbl1 TO alice, bob USING (true);
1220 SELECT refclassid::regclass, deptype
1222 WHERE classid = 'pg_policy'::regclass
1223 AND refobjid = 'tbl1'::regclass;
1224 SELECT refclassid::regclass, deptype
1226 WHERE classid = 'pg_policy'::regclass
1227 AND refobjid IN ('alice'::regrole, 'bob'::regrole);
1230 DROP ROLE alice; --fails due to dependency on POLICY p
1233 ALTER POLICY p ON tbl1 TO bob USING (true);
1235 DROP ROLE alice; --fails due to dependency on GRANT SELECT
1238 REVOKE ALL ON TABLE tbl1 FROM alice;
1240 DROP ROLE alice; --succeeds
1244 DROP ROLE bob; --fails due to dependency on POLICY p
1247 DROP POLICY p ON tbl1;
1249 DROP ROLE bob; -- succeeds
1252 ROLLBACK; -- cleanup
1255 -- Converting table to view
1258 CREATE TABLE t (c int);
1259 CREATE POLICY p ON t USING (c % 2 = 1);
1260 ALTER TABLE t ENABLE ROW LEVEL SECURITY;
1263 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
1264 SELECT * FROM generate_series(1,5) t0(c); -- fails due to row level security enabled
1267 ALTER TABLE t DISABLE ROW LEVEL SECURITY;
1269 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
1270 SELECT * FROM generate_series(1,5) t0(c); -- fails due to policy p on t
1274 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
1275 SELECT * FROM generate_series(1,5) t0(c); -- succeeds
1279 -- Policy expression handling
1282 CREATE TABLE t (c) AS VALUES ('bar'::text);
1283 CREATE POLICY p ON t USING (max(c)); -- fails: aggregate functions are not allowed in policy expressions
1287 -- Non-target relations are only subject to SELECT policies
1289 SET SESSION AUTHORIZATION rls_regress_user0;
1290 CREATE TABLE r1 (a int);
1291 CREATE TABLE r2 (a int);
1292 INSERT INTO r1 VALUES (10), (20);
1293 INSERT INTO r2 VALUES (10), (20);
1295 GRANT ALL ON r1, r2 TO rls_regress_user1;
1297 CREATE POLICY p1 ON r1 USING (true);
1298 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1300 CREATE POLICY p1 ON r2 FOR SELECT USING (true);
1301 CREATE POLICY p2 ON r2 FOR INSERT WITH CHECK (false);
1302 CREATE POLICY p3 ON r2 FOR UPDATE USING (false);
1303 CREATE POLICY p4 ON r2 FOR DELETE USING (false);
1304 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
1306 SET SESSION AUTHORIZATION rls_regress_user1;
1311 INSERT INTO r2 VALUES (2); -- Not allowed
1312 UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing
1313 DELETE FROM r2 RETURNING *; -- Deletes nothing
1315 -- r2 can be used as a non-target relation in DML
1316 INSERT INTO r1 SELECT a + 1 FROM r2 RETURNING *; -- OK
1317 UPDATE r1 SET a = r2.a + 2 FROM r2 WHERE r1.a = r2.a RETURNING *; -- OK
1318 DELETE FROM r1 USING r2 WHERE r1.a = r2.a + 2 RETURNING *; -- OK
1322 SET SESSION AUTHORIZATION rls_regress_user0;
1327 -- FORCE ROW LEVEL SECURITY applies RLS to owners too
1329 SET SESSION AUTHORIZATION rls_regress_user0;
1330 SET row_security = on;
1331 CREATE TABLE r1 (a int);
1332 INSERT INTO r1 VALUES (10), (20);
1334 CREATE POLICY p1 ON r1 USING (false);
1335 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1336 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1338 -- No error, but no rows
1342 INSERT INTO r1 VALUES (1);
1344 -- No error (unable to see any rows to update)
1345 UPDATE r1 SET a = 1;
1348 -- No error (unable to see any rows to delete)
1352 SET row_security = off;
1353 -- these all fail, would be affected by RLS
1355 UPDATE r1 SET a = 1;
1361 -- FORCE ROW LEVEL SECURITY does not break RI
1363 SET SESSION AUTHORIZATION rls_regress_user0;
1364 SET row_security = on;
1365 CREATE TABLE r1 (a int PRIMARY KEY);
1366 CREATE TABLE r2 (a int REFERENCES r1);
1367 INSERT INTO r1 VALUES (10), (20);
1368 INSERT INTO r2 VALUES (10), (20);
1370 -- Create policies on r2 which prevent the
1371 -- owner from seeing any rows, but RI should
1373 CREATE POLICY p1 ON r2 USING (false);
1374 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
1375 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
1377 -- Errors due to rows in r2
1380 -- Reset r2 to no-RLS
1381 DROP POLICY p1 ON r2;
1382 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
1383 ALTER TABLE r2 DISABLE ROW LEVEL SECURITY;
1385 -- clean out r2 for INSERT test below
1388 -- Change r1 to not allow rows to be seen
1389 CREATE POLICY p1 ON r1 USING (false);
1390 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1391 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1396 -- No error, RI still sees that row exists in r1
1397 INSERT INTO r2 VALUES (10);
1402 -- Ensure cascaded DELETE works
1403 CREATE TABLE r1 (a int PRIMARY KEY);
1404 CREATE TABLE r2 (a int REFERENCES r1 ON DELETE CASCADE);
1405 INSERT INTO r1 VALUES (10), (20);
1406 INSERT INTO r2 VALUES (10), (20);
1408 -- Create policies on r2 which prevent the
1409 -- owner from seeing any rows, but RI should
1411 CREATE POLICY p1 ON r2 USING (false);
1412 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
1413 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
1415 -- Deletes all records from both
1418 -- Remove FORCE from r2
1419 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
1421 -- As owner, we now bypass RLS
1422 -- verify no rows in r2 now
1428 -- Ensure cascaded UPDATE works
1429 CREATE TABLE r1 (a int PRIMARY KEY);
1430 CREATE TABLE r2 (a int REFERENCES r1 ON UPDATE CASCADE);
1431 INSERT INTO r1 VALUES (10), (20);
1432 INSERT INTO r2 VALUES (10), (20);
1434 -- Create policies on r2 which prevent the
1435 -- owner from seeing any rows, but RI should
1437 CREATE POLICY p1 ON r2 USING (false);
1438 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
1439 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
1441 -- Updates records in both
1442 UPDATE r1 SET a = a+5;
1444 -- Remove FORCE from r2
1445 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
1447 -- As owner, we now bypass RLS
1448 -- verify records in r2 updated
1455 -- Test INSERT+RETURNING applies SELECT policies as
1456 -- WithCheckOptions (meaning an error is thrown)
1458 SET SESSION AUTHORIZATION rls_regress_user0;
1459 SET row_security = on;
1460 CREATE TABLE r1 (a int);
1462 CREATE POLICY p1 ON r1 FOR SELECT USING (false);
1463 CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true);
1464 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1465 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1468 INSERT INTO r1 VALUES (10), (20);
1470 -- No error, but no rows
1473 SET row_security = off;
1474 -- fail, would be affected by RLS
1477 SET row_security = on;
1480 INSERT INTO r1 VALUES (10), (20) RETURNING *;
1485 -- Test UPDATE+RETURNING applies SELECT policies as
1486 -- WithCheckOptions (meaning an error is thrown)
1488 SET SESSION AUTHORIZATION rls_regress_user0;
1489 SET row_security = on;
1490 CREATE TABLE r1 (a int);
1492 CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
1493 CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true);
1494 INSERT INTO r1 VALUES (10);
1495 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1496 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1499 UPDATE r1 SET a = 30;
1501 -- Show updated rows
1502 ALTER TABLE r1 NO FORCE ROW LEVEL SECURITY;
1504 -- reset value in r1 for test with RETURNING
1505 UPDATE r1 SET a = 10;
1510 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1513 UPDATE r1 SET a = 30 RETURNING *;
1517 -- Check dependency handling
1518 RESET SESSION AUTHORIZATION;
1519 CREATE TABLE dep1 (c1 int);
1520 CREATE TABLE dep2 (c1 int);
1522 CREATE POLICY dep_p1 ON dep1 TO rls_regress_user1 USING (c1 > (select max(dep2.c1) from dep2));
1523 ALTER POLICY dep_p1 ON dep1 TO rls_regress_user1,rls_regress_user2;
1525 -- Should return one
1526 SELECT count(*) = 1 FROM pg_depend
1527 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
1528 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
1530 ALTER POLICY dep_p1 ON dep1 USING (true);
1532 -- Should return one
1533 SELECT count(*) = 1 FROM pg_shdepend
1534 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
1535 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'rls_regress_user1');
1537 -- Should return one
1538 SELECT count(*) = 1 FROM pg_shdepend
1539 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
1540 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'rls_regress_user2');
1542 -- Should return zero
1543 SELECT count(*) = 0 FROM pg_depend
1544 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
1545 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
1547 -- DROP OWNED BY testing
1548 RESET SESSION AUTHORIZATION;
1550 CREATE ROLE dob_role1;
1551 CREATE ROLE dob_role2;
1553 CREATE TABLE dob_t1 (c1 int);
1555 CREATE POLICY p1 ON dob_t1 TO dob_role1 USING (true);
1556 DROP OWNED BY dob_role1;
1557 DROP POLICY p1 ON dob_t1; -- should fail, already gone
1559 CREATE POLICY p1 ON dob_t1 TO dob_role1,dob_role2 USING (true);
1560 DROP OWNED BY dob_role1;
1561 DROP POLICY p1 ON dob_t1; -- should succeed
1563 DROP USER dob_role1;
1564 DROP USER dob_role2;
1569 RESET SESSION AUTHORIZATION;
1571 -- Suppress NOTICE messages when doing a cascaded drop.
1572 SET client_min_messages TO 'warning';
1574 DROP SCHEMA rls_regress_schema CASCADE;
1575 RESET client_min_messages;
1577 DROP USER rls_regress_user0;
1578 DROP USER rls_regress_user1;
1579 DROP USER rls_regress_user2;
1580 DROP USER rls_regress_exempt_user;
1581 DROP ROLE rls_regress_group1;
1582 DROP ROLE rls_regress_group2;
1584 -- Arrange to have a few policies left over, for testing
1585 -- pg_dump/pg_restore
1586 CREATE SCHEMA rls_regress_schema;
1587 CREATE TABLE rls_tbl (c1 int);
1588 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
1589 CREATE POLICY p1 ON rls_tbl USING (c1 > 5);
1590 CREATE POLICY p2 ON rls_tbl FOR SELECT USING (c1 <= 3);
1591 CREATE POLICY p3 ON rls_tbl FOR UPDATE USING (c1 <= 3) WITH CHECK (c1 > 5);
1592 CREATE POLICY p4 ON rls_tbl FOR DELETE USING (c1 <= 3);
1594 CREATE TABLE rls_tbl_force (c1 int);
1595 ALTER TABLE rls_tbl_force ENABLE ROW LEVEL SECURITY;
1596 ALTER TABLE rls_tbl_force FORCE ROW LEVEL SECURITY;
1597 CREATE POLICY p1 ON rls_tbl_force USING (c1 = 5) WITH CHECK (c1 < 5);
1598 CREATE POLICY p2 ON rls_tbl_force FOR SELECT USING (c1 = 8);
1599 CREATE POLICY p3 ON rls_tbl_force FOR UPDATE USING (c1 = 8) WITH CHECK (c1 >= 5);
1600 CREATE POLICY p4 ON rls_tbl_force FOR DELETE USING (c1 = 8);