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);
633 GRANT SELECT ON z1 TO rls_regress_group1, rls_regress_group2,
634 rls_regress_user1, rls_regress_user2;
636 INSERT INTO z1 VALUES
642 CREATE POLICY p1 ON z1 TO rls_regress_group1 USING (a % 2 = 0);
643 CREATE POLICY p2 ON z1 TO rls_regress_group2 USING (a % 2 = 1);
645 ALTER TABLE z1 ENABLE ROW LEVEL SECURITY;
647 SET SESSION AUTHORIZATION rls_regress_user1;
648 SELECT * FROM z1 WHERE f_leak(b);
649 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
651 SET ROLE rls_regress_group1;
652 SELECT * FROM z1 WHERE f_leak(b);
653 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
655 SET SESSION AUTHORIZATION rls_regress_user2;
656 SELECT * FROM z1 WHERE f_leak(b);
657 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
659 SET ROLE rls_regress_group2;
660 SELECT * FROM z1 WHERE f_leak(b);
661 EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b);
664 -- Views should follow policy for view owner.
666 -- View and Table owner are the same.
667 SET SESSION AUTHORIZATION rls_regress_user0;
668 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
669 GRANT SELECT ON rls_view TO rls_regress_user1;
671 -- Query as role that is not owner of view or table. Should return all records.
672 SET SESSION AUTHORIZATION rls_regress_user1;
673 SELECT * FROM rls_view;
674 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
676 -- Query as view/table owner. Should return all records.
677 SET SESSION AUTHORIZATION rls_regress_user0;
678 SELECT * FROM rls_view;
679 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
682 -- View and Table owners are different.
683 SET SESSION AUTHORIZATION rls_regress_user1;
684 CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b);
685 GRANT SELECT ON rls_view TO rls_regress_user0;
687 -- Query as role that is not owner of view but is owner of table.
688 -- Should return records based on view owner policies.
689 SET SESSION AUTHORIZATION rls_regress_user0;
690 SELECT * FROM rls_view;
691 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
693 -- Query as role that is not owner of table but is owner of view.
694 -- Should return records based on view owner policies.
695 SET SESSION AUTHORIZATION rls_regress_user1;
696 SELECT * FROM rls_view;
697 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
699 -- Query as role that is not the owner of the table or view without permissions.
700 SET SESSION AUTHORIZATION rls_regress_user2;
701 SELECT * FROM rls_view; --fail - permission denied.
702 EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied.
704 -- Query as role that is not the owner of the table or view with permissions.
705 SET SESSION AUTHORIZATION rls_regress_user1;
706 GRANT SELECT ON rls_view TO rls_regress_user2;
707 SELECT * FROM rls_view;
708 EXPLAIN (COSTS OFF) SELECT * FROM rls_view;
710 SET SESSION AUTHORIZATION rls_regress_user1;
716 SET SESSION AUTHORIZATION rls_regress_user0;
718 CREATE TABLE x1 (a int, b text, c text);
719 GRANT ALL ON x1 TO PUBLIC;
721 INSERT INTO x1 VALUES
722 (1, 'abc', 'rls_regress_user1'),
723 (2, 'bcd', 'rls_regress_user1'),
724 (3, 'cde', 'rls_regress_user2'),
725 (4, 'def', 'rls_regress_user2'),
726 (5, 'efg', 'rls_regress_user1'),
727 (6, 'fgh', 'rls_regress_user1'),
728 (7, 'fgh', 'rls_regress_user2'),
729 (8, 'fgh', 'rls_regress_user2');
731 CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user);
732 CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0);
733 CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1);
734 CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0);
735 CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8);
737 ALTER TABLE x1 ENABLE ROW LEVEL SECURITY;
739 SET SESSION AUTHORIZATION rls_regress_user1;
740 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
741 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
743 SET SESSION AUTHORIZATION rls_regress_user2;
744 SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC;
745 UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *;
746 DELETE FROM x1 WHERE f_leak(b) RETURNING *;
749 -- Duplicate Policy Names
751 SET SESSION AUTHORIZATION rls_regress_user0;
752 CREATE TABLE y1 (a int, b text);
753 CREATE TABLE y2 (a int, b text);
755 GRANT ALL ON y1, y2 TO rls_regress_user1;
757 CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0);
758 CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2);
759 CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail
760 CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK
762 ALTER TABLE y1 ENABLE ROW LEVEL SECURITY;
763 ALTER TABLE y2 ENABLE ROW LEVEL SECURITY;
766 -- Expression structure with SBV
768 -- Create view as table owner. RLS should NOT be applied.
769 SET SESSION AUTHORIZATION rls_regress_user0;
770 CREATE VIEW rls_sbv WITH (security_barrier) AS
771 SELECT * FROM y1 WHERE f_leak(b);
772 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
775 -- Create view as role that does not own table. RLS should be applied.
776 SET SESSION AUTHORIZATION rls_regress_user1;
777 CREATE VIEW rls_sbv WITH (security_barrier) AS
778 SELECT * FROM y1 WHERE f_leak(b);
779 EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1);
783 -- Expression structure
785 SET SESSION AUTHORIZATION rls_regress_user0;
786 INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
787 CREATE POLICY p2 ON y2 USING (a % 3 = 0);
788 CREATE POLICY p3 ON y2 USING (a % 4 = 0);
790 SET SESSION AUTHORIZATION rls_regress_user1;
791 SELECT * FROM y2 WHERE f_leak(b);
792 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b);
795 -- Qual push-down of leaky functions, when not referring to table
797 SELECT * FROM y2 WHERE f_leak('abc');
798 EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc');
800 CREATE TABLE test_qual_pushdown (
804 INSERT INTO test_qual_pushdown VALUES ('abc'),('def');
806 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
807 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc);
809 SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
810 EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b);
812 DROP TABLE test_qual_pushdown;
815 -- Plancache invalidate on user change.
817 RESET SESSION AUTHORIZATION;
818 -- Suppress NOTICE messages when doing a cascaded drop.
819 SET client_min_messages TO 'warning';
821 DROP TABLE t1 CASCADE;
822 RESET client_min_messages;
824 CREATE TABLE t1 (a integer);
826 GRANT SELECT ON t1 TO rls_regress_user1, rls_regress_user2;
828 CREATE POLICY p1 ON t1 TO rls_regress_user1 USING ((a % 2) = 0);
829 CREATE POLICY p2 ON t1 TO rls_regress_user2 USING ((a % 4) = 0);
831 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
833 SET ROLE rls_regress_user1;
834 PREPARE role_inval AS SELECT * FROM t1;
835 EXPLAIN (COSTS OFF) EXECUTE role_inval;
837 SET ROLE rls_regress_user2;
838 EXPLAIN (COSTS OFF) EXECUTE role_inval;
843 RESET SESSION AUTHORIZATION;
844 DROP TABLE t1 CASCADE;
845 CREATE TABLE t1 (a integer, b text);
846 CREATE POLICY p1 ON t1 USING (a % 2 = 0);
848 ALTER TABLE t1 ENABLE ROW LEVEL SECURITY;
850 GRANT ALL ON t1 TO rls_regress_user1;
852 INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x);
854 SET SESSION AUTHORIZATION rls_regress_user1;
856 WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
857 EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1;
859 WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail
860 WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok
862 WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail
863 WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok
868 RESET SESSION AUTHORIZATION;
869 ALTER POLICY p1 ON t1 RENAME TO p1; --fail
871 SELECT polname, relname
873 JOIN pg_class pc ON (pc.oid = pol.polrelid)
874 WHERE relname = 't1';
876 ALTER POLICY p1 ON t1 RENAME TO p2; --ok
878 SELECT polname, relname
880 JOIN pg_class pc ON (pc.oid = pol.polrelid)
881 WHERE relname = 't1';
884 -- Check INSERT SELECT
886 SET SESSION AUTHORIZATION rls_regress_user1;
887 CREATE TABLE t2 (a integer, b text);
888 INSERT INTO t2 (SELECT * FROM t1);
889 EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1);
891 EXPLAIN (COSTS OFF) SELECT * FROM t2;
892 CREATE TABLE t3 AS SELECT * FROM t1;
894 SELECT * INTO t4 FROM t1;
900 SET SESSION AUTHORIZATION rls_regress_user0;
901 CREATE TABLE blog (id integer, author text, post text);
902 CREATE TABLE comment (blog_id integer, message text);
904 GRANT ALL ON blog, comment TO rls_regress_user1;
906 CREATE POLICY blog_1 ON blog USING (id % 2 = 0);
908 ALTER TABLE blog ENABLE ROW LEVEL SECURITY;
910 INSERT INTO blog VALUES
911 (1, 'alice', 'blog #1'),
912 (2, 'bob', 'blog #1'),
913 (3, 'alice', 'blog #2'),
914 (4, 'alice', 'blog #3'),
915 (5, 'john', 'blog #1');
917 INSERT INTO comment VALUES
925 SET SESSION AUTHORIZATION rls_regress_user1;
926 -- Check RLS JOIN with Non-RLS.
927 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
928 -- Check Non-RLS JOIN with RLS.
929 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
931 SET SESSION AUTHORIZATION rls_regress_user0;
932 CREATE POLICY comment_1 ON comment USING (blog_id < 4);
934 ALTER TABLE comment ENABLE ROW LEVEL SECURITY;
936 SET SESSION AUTHORIZATION rls_regress_user1;
937 -- Check RLS JOIN RLS
938 SELECT id, author, message FROM blog JOIN comment ON id = blog_id;
939 SELECT id, author, message FROM comment JOIN blog ON id = blog_id;
941 SET SESSION AUTHORIZATION rls_regress_user0;
942 DROP TABLE blog, comment;
945 -- Default Deny Policy
947 RESET SESSION AUTHORIZATION;
948 DROP POLICY p2 ON t1;
949 ALTER TABLE t1 OWNER TO rls_regress_user0;
951 -- Check that default deny does not apply to superuser.
952 RESET SESSION AUTHORIZATION;
954 EXPLAIN (COSTS OFF) SELECT * FROM t1;
956 -- Check that default deny does not apply to table owner.
957 SET SESSION AUTHORIZATION rls_regress_user0;
959 EXPLAIN (COSTS OFF) SELECT * FROM t1;
961 -- Check that default deny applies to non-owner/non-superuser when RLS on.
962 SET SESSION AUTHORIZATION rls_regress_user1;
963 SET row_security TO ON;
965 EXPLAIN (COSTS OFF) SELECT * FROM t1;
966 SET SESSION AUTHORIZATION rls_regress_user1;
968 EXPLAIN (COSTS OFF) SELECT * FROM t1;
974 RESET SESSION AUTHORIZATION;
975 DROP TABLE copy_t CASCADE;
976 CREATE TABLE copy_t (a integer, b text);
977 CREATE POLICY p1 ON copy_t USING (a % 2 = 0);
979 ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY;
981 GRANT ALL ON copy_t TO rls_regress_user1, rls_regress_exempt_user;
983 INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x);
985 -- Check COPY TO as Superuser/owner.
986 RESET SESSION AUTHORIZATION;
987 SET row_security TO OFF;
988 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
989 SET row_security TO ON;
990 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ',';
992 -- Check COPY TO as user with permissions.
993 SET SESSION AUTHORIZATION rls_regress_user1;
994 SET row_security TO OFF;
995 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
996 SET row_security TO ON;
997 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
999 -- Check COPY TO as user with permissions and BYPASSRLS
1000 SET SESSION AUTHORIZATION rls_regress_exempt_user;
1001 SET row_security TO OFF;
1002 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
1003 SET row_security TO ON;
1004 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok
1006 -- Check COPY TO as user without permissions. SET row_security TO OFF;
1007 SET SESSION AUTHORIZATION rls_regress_user2;
1008 SET row_security TO OFF;
1009 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
1010 SET row_security TO ON;
1011 COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied
1013 -- Check COPY relation TO; keep it just one row to avoid reordering issues
1014 RESET SESSION AUTHORIZATION;
1015 SET row_security TO ON;
1016 CREATE TABLE copy_rel_to (a integer, b text);
1017 CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0);
1019 ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY;
1021 GRANT ALL ON copy_rel_to TO rls_regress_user1, rls_regress_exempt_user;
1023 INSERT INTO copy_rel_to VALUES (1, md5('1'));
1025 -- Check COPY TO as Superuser/owner.
1026 RESET SESSION AUTHORIZATION;
1027 SET row_security TO OFF;
1028 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
1029 SET row_security TO ON;
1030 COPY copy_rel_to TO STDOUT WITH DELIMITER ',';
1032 -- Check COPY TO as user with permissions.
1033 SET SESSION AUTHORIZATION rls_regress_user1;
1034 SET row_security TO OFF;
1035 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - insufficient to bypass rls
1036 SET row_security TO ON;
1037 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1039 -- Check COPY TO as user with permissions and BYPASSRLS
1040 SET SESSION AUTHORIZATION rls_regress_exempt_user;
1041 SET row_security TO OFF;
1042 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1043 SET row_security TO ON;
1044 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok
1046 -- Check COPY TO as user without permissions. SET row_security TO OFF;
1047 SET SESSION AUTHORIZATION rls_regress_user2;
1048 SET row_security TO OFF;
1049 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
1050 SET row_security TO ON;
1051 COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied
1053 -- Check COPY FROM as Superuser/owner.
1054 RESET SESSION AUTHORIZATION;
1055 SET row_security TO OFF;
1056 COPY copy_t FROM STDIN; --ok
1062 SET row_security TO ON;
1063 COPY copy_t FROM STDIN; --ok
1070 -- Check COPY FROM as user with permissions.
1071 SET SESSION AUTHORIZATION rls_regress_user1;
1072 SET row_security TO OFF;
1073 COPY copy_t FROM STDIN; --fail - insufficient privilege to bypass rls.
1074 SET row_security TO ON;
1075 COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS.
1077 -- Check COPY FROM as user with permissions and BYPASSRLS
1078 SET SESSION AUTHORIZATION rls_regress_exempt_user;
1079 SET row_security TO ON;
1080 COPY copy_t FROM STDIN; --ok
1087 -- Check COPY FROM as user without permissions.
1088 SET SESSION AUTHORIZATION rls_regress_user2;
1089 SET row_security TO OFF;
1090 COPY copy_t FROM STDIN; --fail - permission denied.
1091 SET row_security TO ON;
1092 COPY copy_t FROM STDIN; --fail - permission denied.
1094 RESET SESSION AUTHORIZATION;
1096 DROP TABLE copy_rel_to CASCADE;
1098 -- Check WHERE CURRENT OF
1099 SET SESSION AUTHORIZATION rls_regress_user0;
1101 CREATE TABLE current_check (currentid int, payload text, rlsuser text);
1102 GRANT ALL ON current_check TO PUBLIC;
1104 INSERT INTO current_check VALUES
1105 (1, 'abc', 'rls_regress_user1'),
1106 (2, 'bcd', 'rls_regress_user1'),
1107 (3, 'cde', 'rls_regress_user1'),
1108 (4, 'def', 'rls_regress_user1');
1110 CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0);
1111 CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user);
1112 CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user);
1114 ALTER TABLE current_check ENABLE ROW LEVEL SECURITY;
1116 SET SESSION AUTHORIZATION rls_regress_user1;
1118 -- Can SELECT even rows
1119 SELECT * FROM current_check;
1121 -- Cannot UPDATE row 2
1122 UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *;
1126 DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check;
1127 -- Returns rows that can be seen according to SELECT policy, like plain SELECT
1128 -- above (even rows)
1129 FETCH ABSOLUTE 1 FROM current_check_cursor;
1130 -- Still cannot UPDATE row 2 through cursor
1131 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
1132 -- Can update row 4 through cursor, which is the next visible row
1133 FETCH RELATIVE 1 FROM current_check_cursor;
1134 UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *;
1135 SELECT * FROM current_check;
1136 -- Plan should be a subquery TID scan
1137 EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor;
1138 -- Similarly can only delete row 4
1139 FETCH ABSOLUTE 1 FROM current_check_cursor;
1140 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
1141 FETCH RELATIVE 1 FROM current_check_cursor;
1142 DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *;
1143 SELECT * FROM current_check;
1148 -- check pg_stats view filtering
1150 SET row_security TO ON;
1151 SET SESSION AUTHORIZATION rls_regress_user0;
1152 ANALYZE current_check;
1154 SELECT row_security_active('current_check');
1155 SELECT attname, most_common_vals FROM pg_stats
1156 WHERE tablename = 'current_check'
1159 SET SESSION AUTHORIZATION rls_regress_user1;
1160 -- Stats not visible
1161 SELECT row_security_active('current_check');
1162 SELECT attname, most_common_vals FROM pg_stats
1163 WHERE tablename = 'current_check'
1167 -- Collation support
1170 CREATE TABLE coll_t (c) AS VALUES ('bar'::text);
1171 CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C"));
1172 ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY;
1173 GRANT SELECT ON coll_t TO rls_regress_user0;
1174 SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass;
1175 SET SESSION AUTHORIZATION rls_regress_user0;
1176 SELECT * FROM coll_t;
1180 -- Shared Object Dependencies
1182 RESET SESSION AUTHORIZATION;
1186 CREATE TABLE tbl1 (c) AS VALUES ('bar'::text);
1187 GRANT SELECT ON TABLE tbl1 TO alice;
1188 CREATE POLICY P ON tbl1 TO alice, bob USING (true);
1189 SELECT refclassid::regclass, deptype
1191 WHERE classid = 'pg_policy'::regclass
1192 AND refobjid = 'tbl1'::regclass;
1193 SELECT refclassid::regclass, deptype
1195 WHERE classid = 'pg_policy'::regclass
1196 AND refobjid IN ('alice'::regrole, 'bob'::regrole);
1199 DROP ROLE alice; --fails due to dependency on POLICY p
1202 ALTER POLICY p ON tbl1 TO bob USING (true);
1204 DROP ROLE alice; --fails due to dependency on GRANT SELECT
1207 REVOKE ALL ON TABLE tbl1 FROM alice;
1209 DROP ROLE alice; --succeeds
1213 DROP ROLE bob; --fails due to dependency on POLICY p
1216 DROP POLICY p ON tbl1;
1218 DROP ROLE bob; -- succeeds
1221 ROLLBACK; -- cleanup
1224 -- Converting table to view
1227 CREATE TABLE t (c int);
1228 CREATE POLICY p ON t USING (c % 2 = 1);
1229 ALTER TABLE t ENABLE ROW LEVEL SECURITY;
1232 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
1233 SELECT * FROM generate_series(1,5) t0(c); -- fails due to row level security enabled
1236 ALTER TABLE t DISABLE ROW LEVEL SECURITY;
1238 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
1239 SELECT * FROM generate_series(1,5) t0(c); -- fails due to policy p on t
1243 CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD
1244 SELECT * FROM generate_series(1,5) t0(c); -- succeeds
1248 -- Policy expression handling
1251 CREATE TABLE t (c) AS VALUES ('bar'::text);
1252 CREATE POLICY p ON t USING (max(c)); -- fails: aggregate functions are not allowed in policy expressions
1256 -- Non-target relations are only subject to SELECT policies
1258 SET SESSION AUTHORIZATION rls_regress_user0;
1259 CREATE TABLE r1 (a int);
1260 CREATE TABLE r2 (a int);
1261 INSERT INTO r1 VALUES (10), (20);
1262 INSERT INTO r2 VALUES (10), (20);
1264 GRANT ALL ON r1, r2 TO rls_regress_user1;
1266 CREATE POLICY p1 ON r1 USING (true);
1267 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1269 CREATE POLICY p1 ON r2 FOR SELECT USING (true);
1270 CREATE POLICY p2 ON r2 FOR INSERT WITH CHECK (false);
1271 CREATE POLICY p3 ON r2 FOR UPDATE USING (false);
1272 CREATE POLICY p4 ON r2 FOR DELETE USING (false);
1273 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
1275 SET SESSION AUTHORIZATION rls_regress_user1;
1280 INSERT INTO r2 VALUES (2); -- Not allowed
1281 UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing
1282 DELETE FROM r2 RETURNING *; -- Deletes nothing
1284 -- r2 can be used as a non-target relation in DML
1285 INSERT INTO r1 SELECT a + 1 FROM r2 RETURNING *; -- OK
1286 UPDATE r1 SET a = r2.a + 2 FROM r2 WHERE r1.a = r2.a RETURNING *; -- OK
1287 DELETE FROM r1 USING r2 WHERE r1.a = r2.a + 2 RETURNING *; -- OK
1291 SET SESSION AUTHORIZATION rls_regress_user0;
1296 -- FORCE ROW LEVEL SECURITY applies RLS to owners but
1297 -- only when row_security = on
1299 SET SESSION AUTHORIZATION rls_regress_user0;
1300 SET row_security = on;
1301 CREATE TABLE r1 (a int);
1302 INSERT INTO r1 VALUES (10), (20);
1304 CREATE POLICY p1 ON r1 USING (false);
1305 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1306 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1308 -- No error, but no rows
1312 INSERT INTO r1 VALUES (1);
1314 -- No error (unable to see any rows to update)
1315 UPDATE r1 SET a = 1;
1318 -- No error (unable to see any rows to delete)
1322 SET row_security = off;
1327 UPDATE r1 SET a = 1;
1337 -- FORCE ROW LEVEL SECURITY does not break RI
1339 SET SESSION AUTHORIZATION rls_regress_user0;
1340 SET row_security = on;
1341 CREATE TABLE r1 (a int PRIMARY KEY);
1342 CREATE TABLE r2 (a int REFERENCES r1);
1343 INSERT INTO r1 VALUES (10), (20);
1344 INSERT INTO r2 VALUES (10), (20);
1346 -- Create policies on r2 which prevent the
1347 -- owner from seeing any rows, but RI should
1349 CREATE POLICY p1 ON r2 USING (false);
1350 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
1351 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
1353 -- Errors due to rows in r2
1356 -- Reset r2 to no-RLS
1357 DROP POLICY p1 ON r2;
1358 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
1359 ALTER TABLE r2 DISABLE ROW LEVEL SECURITY;
1361 -- clean out r2 for INSERT test below
1364 -- Change r1 to not allow rows to be seen
1365 CREATE POLICY p1 ON r1 USING (false);
1366 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1367 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1372 -- No error, RI still sees that row exists in r1
1373 INSERT INTO r2 VALUES (10);
1378 -- Ensure cascaded DELETE works
1379 CREATE TABLE r1 (a int PRIMARY KEY);
1380 CREATE TABLE r2 (a int REFERENCES r1 ON DELETE CASCADE);
1381 INSERT INTO r1 VALUES (10), (20);
1382 INSERT INTO r2 VALUES (10), (20);
1384 -- Create policies on r2 which prevent the
1385 -- owner from seeing any rows, but RI should
1387 CREATE POLICY p1 ON r2 USING (false);
1388 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
1389 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
1391 -- Deletes all records from both
1394 -- Remove FORCE from r2
1395 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
1397 -- As owner, we now bypass RLS
1398 -- verify no rows in r2 now
1404 -- Ensure cascaded UPDATE works
1405 CREATE TABLE r1 (a int PRIMARY KEY);
1406 CREATE TABLE r2 (a int REFERENCES r1 ON UPDATE CASCADE);
1407 INSERT INTO r1 VALUES (10), (20);
1408 INSERT INTO r2 VALUES (10), (20);
1410 -- Create policies on r2 which prevent the
1411 -- owner from seeing any rows, but RI should
1413 CREATE POLICY p1 ON r2 USING (false);
1414 ALTER TABLE r2 ENABLE ROW LEVEL SECURITY;
1415 ALTER TABLE r2 FORCE ROW LEVEL SECURITY;
1417 -- Updates records in both
1418 UPDATE r1 SET a = a+5;
1420 -- Remove FORCE from r2
1421 ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY;
1423 -- As owner, we now bypass RLS
1424 -- verify records in r2 updated
1431 -- Test INSERT+RETURNING applies SELECT policies as
1432 -- WithCheckOptions (meaning an error is thrown)
1434 SET SESSION AUTHORIZATION rls_regress_user0;
1435 SET row_security = on;
1436 CREATE TABLE r1 (a int);
1438 CREATE POLICY p1 ON r1 FOR SELECT USING (false);
1439 CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true);
1440 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1441 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1444 INSERT INTO r1 VALUES (10), (20);
1446 -- No error, but no rows
1449 SET row_security = off;
1453 SET row_security = on;
1456 INSERT INTO r1 VALUES (10), (20) RETURNING *;
1461 -- Test UPDATE+RETURNING applies SELECT policies as
1462 -- WithCheckOptions (meaning an error is thrown)
1464 SET SESSION AUTHORIZATION rls_regress_user0;
1465 SET row_security = on;
1466 CREATE TABLE r1 (a int);
1468 CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20);
1469 CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true);
1470 INSERT INTO r1 VALUES (10);
1471 ALTER TABLE r1 ENABLE ROW LEVEL SECURITY;
1472 ALTER TABLE r1 FORCE ROW LEVEL SECURITY;
1475 UPDATE r1 SET a = 30;
1477 -- Show updated rows
1478 SET row_security = off;
1480 -- reset value in r1 for test with RETURNING
1481 UPDATE r1 SET a = 10;
1486 SET row_security = on;
1489 UPDATE r1 SET a = 30 RETURNING *;
1493 -- Check dependency handling
1494 RESET SESSION AUTHORIZATION;
1495 CREATE TABLE dep1 (c1 int);
1496 CREATE TABLE dep2 (c1 int);
1498 CREATE POLICY dep_p1 ON dep1 TO rls_regress_user1 USING (c1 > (select max(dep2.c1) from dep2));
1499 ALTER POLICY dep_p1 ON dep1 TO rls_regress_user1,rls_regress_user2;
1501 -- Should return one
1502 SELECT count(*) = 1 FROM pg_depend
1503 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
1504 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
1506 ALTER POLICY dep_p1 ON dep1 USING (true);
1508 -- Should return one
1509 SELECT count(*) = 1 FROM pg_shdepend
1510 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
1511 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'rls_regress_user1');
1513 -- Should return one
1514 SELECT count(*) = 1 FROM pg_shdepend
1515 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
1516 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'rls_regress_user2');
1518 -- Should return zero
1519 SELECT count(*) = 0 FROM pg_depend
1520 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1')
1521 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2');
1527 RESET SESSION AUTHORIZATION;
1529 -- Suppress NOTICE messages when doing a cascaded drop.
1530 SET client_min_messages TO 'warning';
1532 DROP SCHEMA rls_regress_schema CASCADE;
1533 RESET client_min_messages;
1535 DROP USER rls_regress_user0;
1536 DROP USER rls_regress_user1;
1537 DROP USER rls_regress_user2;
1538 DROP USER rls_regress_exempt_user;
1539 DROP ROLE rls_regress_group1;
1540 DROP ROLE rls_regress_group2;
1542 -- Arrange to have a few policies left over, for testing
1543 -- pg_dump/pg_restore
1544 CREATE SCHEMA rls_regress_schema;
1545 CREATE TABLE rls_tbl (c1 int);
1546 ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY;
1547 CREATE POLICY p1 ON rls_tbl USING (c1 > 5);
1548 CREATE POLICY p2 ON rls_tbl FOR SELECT USING (c1 <= 3);
1549 CREATE POLICY p3 ON rls_tbl FOR UPDATE USING (c1 <= 3) WITH CHECK (c1 > 5);
1550 CREATE POLICY p4 ON rls_tbl FOR DELETE USING (c1 <= 3);
1552 CREATE TABLE rls_tbl_force (c1 int);
1553 ALTER TABLE rls_tbl_force ENABLE ROW LEVEL SECURITY;
1554 ALTER TABLE rls_tbl_force FORCE ROW LEVEL SECURITY;
1555 CREATE POLICY p1 ON rls_tbl_force USING (c1 = 5) WITH CHECK (c1 < 5);
1556 CREATE POLICY p2 ON rls_tbl_force FOR SELECT USING (c1 = 8);
1557 CREATE POLICY p3 ON rls_tbl_force FOR UPDATE USING (c1 = 8) WITH CHECK (c1 >= 5);
1558 CREATE POLICY p4 ON rls_tbl_force FOR DELETE USING (c1 = 8);