1-- 2-- Test of Row-level security feature 3-- 4 5-- Clean up in case a prior regression run failed 6 7-- Suppress NOTICE messages when users/groups don't exist 8SET client_min_messages TO 'warning'; 9 10DROP USER IF EXISTS regress_rls_alice; 11DROP USER IF EXISTS regress_rls_bob; 12DROP USER IF EXISTS regress_rls_carol; 13DROP USER IF EXISTS regress_rls_dave; 14DROP USER IF EXISTS regress_rls_exempt_user; 15DROP ROLE IF EXISTS regress_rls_group1; 16DROP ROLE IF EXISTS regress_rls_group2; 17 18DROP SCHEMA IF EXISTS regress_rls_schema CASCADE; 19 20RESET client_min_messages; 21 22-- initial setup 23CREATE USER regress_rls_alice NOLOGIN; 24CREATE USER regress_rls_bob NOLOGIN; 25CREATE USER regress_rls_carol NOLOGIN; 26CREATE USER regress_rls_dave NOLOGIN; 27CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN; 28CREATE ROLE regress_rls_group1 NOLOGIN; 29CREATE ROLE regress_rls_group2 NOLOGIN; 30 31GRANT regress_rls_group1 TO regress_rls_bob; 32GRANT regress_rls_group2 TO regress_rls_carol; 33 34CREATE SCHEMA regress_rls_schema; 35GRANT ALL ON SCHEMA regress_rls_schema to public; 36SET search_path = regress_rls_schema; 37 38-- setup of malicious function 39CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool 40 COST 0.0000001 LANGUAGE plpgsql 41 AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END'; 42GRANT EXECUTE ON FUNCTION f_leak(text) TO public; 43 44-- BASIC Row-Level Security Scenario 45 46SET SESSION AUTHORIZATION regress_rls_alice; 47CREATE TABLE uaccount ( 48 pguser name primary key, 49 seclv int 50); 51GRANT SELECT ON uaccount TO public; 52INSERT INTO uaccount VALUES 53 ('regress_rls_alice', 99), 54 ('regress_rls_bob', 1), 55 ('regress_rls_carol', 2), 56 ('regress_rls_dave', 3); 57 58CREATE TABLE category ( 59 cid int primary key, 60 cname text 61); 62GRANT ALL ON category TO public; 63INSERT INTO category VALUES 64 (11, 'novel'), 65 (22, 'science fiction'), 66 (33, 'technology'), 67 (44, 'manga'); 68 69CREATE TABLE document ( 70 did int primary key, 71 cid int references category(cid), 72 dlevel int not null, 73 dauthor name, 74 dtitle text 75); 76GRANT ALL ON document TO public; 77INSERT INTO document VALUES 78 ( 1, 11, 1, 'regress_rls_bob', 'my first novel'), 79 ( 2, 11, 2, 'regress_rls_bob', 'my second novel'), 80 ( 3, 22, 2, 'regress_rls_bob', 'my science fiction'), 81 ( 4, 44, 1, 'regress_rls_bob', 'my first manga'), 82 ( 5, 44, 2, 'regress_rls_bob', 'my second manga'), 83 ( 6, 22, 1, 'regress_rls_carol', 'great science fiction'), 84 ( 7, 33, 2, 'regress_rls_carol', 'great technology book'), 85 ( 8, 44, 1, 'regress_rls_carol', 'great manga'), 86 ( 9, 22, 1, 'regress_rls_dave', 'awesome science fiction'), 87 (10, 33, 2, 'regress_rls_dave', 'awesome technology book'); 88 89ALTER TABLE document ENABLE ROW LEVEL SECURITY; 90 91-- user's security level must be higher than or equal to document's 92CREATE POLICY p1 ON document AS PERMISSIVE 93 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); 94 95-- try to create a policy of bogus type 96CREATE POLICY p1 ON document AS UGLY 97 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); 98 99-- but Dave isn't allowed to anything at cid 50 or above 100-- this is to make sure that we sort the policies by name first 101-- when applying WITH CHECK, a later INSERT by Dave should fail due 102-- to p1r first 103CREATE POLICY p2r ON document AS RESTRICTIVE TO regress_rls_dave 104 USING (cid <> 44 AND cid < 50); 105 106-- and Dave isn't allowed to see manga documents 107CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave 108 USING (cid <> 44); 109 110\dp 111\d document 112SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname; 113 114-- viewpoint from regress_rls_bob 115SET SESSION AUTHORIZATION regress_rls_bob; 116SET row_security TO ON; 117SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 118SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; 119 120-- try a sampled version 121SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0) 122 WHERE f_leak(dtitle) ORDER BY did; 123 124-- viewpoint from regress_rls_carol 125SET SESSION AUTHORIZATION regress_rls_carol; 126SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 127SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; 128 129-- try a sampled version 130SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0) 131 WHERE f_leak(dtitle) ORDER BY did; 132 133EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); 134EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); 135 136-- viewpoint from regress_rls_dave 137SET SESSION AUTHORIZATION regress_rls_dave; 138SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 139SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; 140 141EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); 142EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); 143 144-- 44 would technically fail for both p2r and p1r, but we should get an error 145-- back from p1r for this because it sorts first 146INSERT INTO document VALUES (100, 44, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail 147-- Just to see a p2r error 148INSERT INTO document VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail 149 150-- only owner can change policies 151ALTER POLICY p1 ON document USING (true); --fail 152DROP POLICY p1 ON document; --fail 153 154SET SESSION AUTHORIZATION regress_rls_alice; 155ALTER POLICY p1 ON document USING (dauthor = current_user); 156 157-- viewpoint from regress_rls_bob again 158SET SESSION AUTHORIZATION regress_rls_bob; 159SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 160SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did; 161 162-- viewpoint from rls_regres_carol again 163SET SESSION AUTHORIZATION regress_rls_carol; 164SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 165SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did; 166 167EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); 168EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); 169 170-- interaction of FK/PK constraints 171SET SESSION AUTHORIZATION regress_rls_alice; 172CREATE POLICY p2 ON category 173 USING (CASE WHEN current_user = 'regress_rls_bob' THEN cid IN (11, 33) 174 WHEN current_user = 'regress_rls_carol' THEN cid IN (22, 44) 175 ELSE false END); 176 177ALTER TABLE category ENABLE ROW LEVEL SECURITY; 178 179-- cannot delete PK referenced by invisible FK 180SET SESSION AUTHORIZATION regress_rls_bob; 181SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid; 182DELETE FROM category WHERE cid = 33; -- fails with FK violation 183 184-- can insert FK referencing invisible PK 185SET SESSION AUTHORIZATION regress_rls_carol; 186SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid; 187INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge'); 188 189-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row 190SET SESSION AUTHORIZATION regress_rls_bob; 191INSERT INTO document VALUES (8, 44, 1, 'regress_rls_bob', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see 192SELECT * FROM document WHERE did = 8; -- and confirm we can't see it 193 194-- RLS policies are checked before constraints 195INSERT INTO document VALUES (8, 44, 1, 'regress_rls_carol', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation 196UPDATE document SET did = 8, dauthor = 'regress_rls_carol' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation 197 198-- database superuser does bypass RLS policy when enabled 199RESET SESSION AUTHORIZATION; 200SET row_security TO ON; 201SELECT * FROM document; 202SELECT * FROM category; 203 204-- database superuser does bypass RLS policy when disabled 205RESET SESSION AUTHORIZATION; 206SET row_security TO OFF; 207SELECT * FROM document; 208SELECT * FROM category; 209 210-- database non-superuser with bypass privilege can bypass RLS policy when disabled 211SET SESSION AUTHORIZATION regress_rls_exempt_user; 212SET row_security TO OFF; 213SELECT * FROM document; 214SELECT * FROM category; 215 216-- RLS policy does not apply to table owner when RLS enabled. 217SET SESSION AUTHORIZATION regress_rls_alice; 218SET row_security TO ON; 219SELECT * FROM document; 220SELECT * FROM category; 221 222-- RLS policy does not apply to table owner when RLS disabled. 223SET SESSION AUTHORIZATION regress_rls_alice; 224SET row_security TO OFF; 225SELECT * FROM document; 226SELECT * FROM category; 227 228-- 229-- Table inheritance and RLS policy 230-- 231SET SESSION AUTHORIZATION regress_rls_alice; 232 233SET row_security TO ON; 234 235CREATE TABLE t1 (id int not null primary key, a int, junk1 text, b text); 236ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor 237GRANT ALL ON t1 TO public; 238 239CREATE TABLE t2 (c float) INHERITS (t1); 240GRANT ALL ON t2 TO public; 241 242CREATE TABLE t3 (id int not null primary key, c text, b text, a int); 243ALTER TABLE t3 INHERIT t1; 244GRANT ALL ON t3 TO public; 245 246CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number 247CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number 248 249ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; 250ALTER TABLE t2 ENABLE ROW LEVEL SECURITY; 251 252SET SESSION AUTHORIZATION regress_rls_bob; 253 254SELECT * FROM t1; 255EXPLAIN (COSTS OFF) SELECT * FROM t1; 256 257SELECT * FROM t1 WHERE f_leak(b); 258EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); 259 260-- reference to system column 261SELECT tableoid::regclass, * FROM t1; 262EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1; 263 264-- reference to whole-row reference 265SELECT *, t1 FROM t1; 266EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1; 267 268-- for share/update lock 269SELECT * FROM t1 FOR SHARE; 270EXPLAIN (COSTS OFF) SELECT * FROM t1 FOR SHARE; 271 272SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; 273EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; 274 275-- union all query 276SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3; 277EXPLAIN (COSTS OFF) SELECT a, b, tableoid::regclass FROM t2 UNION ALL SELECT a, b, tableoid::regclass FROM t3; 278 279-- superuser is allowed to bypass RLS checks 280RESET SESSION AUTHORIZATION; 281SET row_security TO OFF; 282SELECT * FROM t1 WHERE f_leak(b); 283EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); 284 285-- non-superuser with bypass privilege can bypass RLS policy when disabled 286SET SESSION AUTHORIZATION regress_rls_exempt_user; 287SET row_security TO OFF; 288SELECT * FROM t1 WHERE f_leak(b); 289EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); 290 291-- 292-- Partitioned Tables 293-- 294 295SET SESSION AUTHORIZATION regress_rls_alice; 296 297CREATE TABLE part_document ( 298 did int, 299 cid int, 300 dlevel int not null, 301 dauthor name, 302 dtitle text 303) PARTITION BY RANGE (cid); 304GRANT ALL ON part_document TO public; 305 306-- Create partitions for document categories 307CREATE TABLE part_document_fiction PARTITION OF part_document FOR VALUES FROM (11) to (12); 308CREATE TABLE part_document_satire PARTITION OF part_document FOR VALUES FROM (55) to (56); 309CREATE TABLE part_document_nonfiction PARTITION OF part_document FOR VALUES FROM (99) to (100); 310 311GRANT ALL ON part_document_fiction TO public; 312GRANT ALL ON part_document_satire TO public; 313GRANT ALL ON part_document_nonfiction TO public; 314 315INSERT INTO part_document VALUES 316 ( 1, 11, 1, 'regress_rls_bob', 'my first novel'), 317 ( 2, 11, 2, 'regress_rls_bob', 'my second novel'), 318 ( 3, 99, 2, 'regress_rls_bob', 'my science textbook'), 319 ( 4, 55, 1, 'regress_rls_bob', 'my first satire'), 320 ( 5, 99, 2, 'regress_rls_bob', 'my history book'), 321 ( 6, 11, 1, 'regress_rls_carol', 'great science fiction'), 322 ( 7, 99, 2, 'regress_rls_carol', 'great technology book'), 323 ( 8, 55, 2, 'regress_rls_carol', 'great satire'), 324 ( 9, 11, 1, 'regress_rls_dave', 'awesome science fiction'), 325 (10, 99, 2, 'regress_rls_dave', 'awesome technology book'); 326 327ALTER TABLE part_document ENABLE ROW LEVEL SECURITY; 328 329-- Create policy on parent 330-- user's security level must be higher than or equal to document's 331CREATE POLICY pp1 ON part_document AS PERMISSIVE 332 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); 333 334-- Dave is only allowed to see cid < 55 335CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave 336 USING (cid < 55); 337 338\d+ part_document 339SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename like '%part_document%' ORDER BY policyname; 340 341-- viewpoint from regress_rls_bob 342SET SESSION AUTHORIZATION regress_rls_bob; 343SET row_security TO ON; 344SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 345EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 346 347-- viewpoint from regress_rls_carol 348SET SESSION AUTHORIZATION regress_rls_carol; 349SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 350EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 351 352-- viewpoint from regress_rls_dave 353SET SESSION AUTHORIZATION regress_rls_dave; 354SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 355EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 356 357-- pp1 ERROR 358INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail 359-- pp1r ERROR 360INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail 361 362-- Show that RLS policy does not apply for direct inserts to children 363-- This should fail with RLS POLICY pp1r violation. 364INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail 365-- But this should succeed. 366INSERT INTO part_document_satire VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- success 367-- We still cannot see the row using the parent 368SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 369-- But we can if we look directly 370SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did; 371 372-- Turn on RLS and create policy on child to show RLS is checked before constraints 373SET SESSION AUTHORIZATION regress_rls_alice; 374ALTER TABLE part_document_satire ENABLE ROW LEVEL SECURITY; 375CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE 376 USING (cid < 55); 377-- This should fail with RLS violation now. 378SET SESSION AUTHORIZATION regress_rls_dave; 379INSERT INTO part_document_satire VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail 380-- And now we cannot see directly into the partition either, due to RLS 381SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did; 382-- The parent looks same as before 383-- viewpoint from regress_rls_dave 384SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 385EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 386 387-- viewpoint from regress_rls_carol 388SET SESSION AUTHORIZATION regress_rls_carol; 389SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 390EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 391 392-- only owner can change policies 393ALTER POLICY pp1 ON part_document USING (true); --fail 394DROP POLICY pp1 ON part_document; --fail 395 396SET SESSION AUTHORIZATION regress_rls_alice; 397ALTER POLICY pp1 ON part_document USING (dauthor = current_user); 398 399-- viewpoint from regress_rls_bob again 400SET SESSION AUTHORIZATION regress_rls_bob; 401SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 402 403-- viewpoint from rls_regres_carol again 404SET SESSION AUTHORIZATION regress_rls_carol; 405SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 406 407EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 408 409-- database superuser does bypass RLS policy when enabled 410RESET SESSION AUTHORIZATION; 411SET row_security TO ON; 412SELECT * FROM part_document ORDER BY did; 413SELECT * FROM part_document_satire ORDER by did; 414 415-- database non-superuser with bypass privilege can bypass RLS policy when disabled 416SET SESSION AUTHORIZATION regress_rls_exempt_user; 417SET row_security TO OFF; 418SELECT * FROM part_document ORDER BY did; 419SELECT * FROM part_document_satire ORDER by did; 420 421-- RLS policy does not apply to table owner when RLS enabled. 422SET SESSION AUTHORIZATION regress_rls_alice; 423SET row_security TO ON; 424SELECT * FROM part_document ORDER by did; 425SELECT * FROM part_document_satire ORDER by did; 426 427-- When RLS disabled, other users get ERROR. 428SET SESSION AUTHORIZATION regress_rls_dave; 429SET row_security TO OFF; 430SELECT * FROM part_document ORDER by did; 431SELECT * FROM part_document_satire ORDER by did; 432 433-- Check behavior with a policy that uses a SubPlan not an InitPlan. 434SET SESSION AUTHORIZATION regress_rls_alice; 435SET row_security TO ON; 436CREATE POLICY pp3 ON part_document AS RESTRICTIVE 437 USING ((SELECT dlevel <= seclv FROM uaccount WHERE pguser = current_user)); 438 439SET SESSION AUTHORIZATION regress_rls_carol; 440INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_carol', 'testing pp3'); -- fail 441 442----- Dependencies ----- 443SET SESSION AUTHORIZATION regress_rls_alice; 444SET row_security TO ON; 445 446CREATE TABLE dependee (x integer, y integer); 447 448CREATE TABLE dependent (x integer, y integer); 449CREATE POLICY d1 ON dependent FOR ALL 450 TO PUBLIC 451 USING (x = (SELECT d.x FROM dependee d WHERE d.y = y)); 452 453DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row security qual? 454 455DROP TABLE dependee CASCADE; 456 457EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified 458 459----- RECURSION ---- 460 461-- 462-- Simple recursion 463-- 464SET SESSION AUTHORIZATION regress_rls_alice; 465CREATE TABLE rec1 (x integer, y integer); 466CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y)); 467ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY; 468SET SESSION AUTHORIZATION regress_rls_bob; 469SELECT * FROM rec1; -- fail, direct recursion 470 471-- 472-- Mutual recursion 473-- 474SET SESSION AUTHORIZATION regress_rls_alice; 475CREATE TABLE rec2 (a integer, b integer); 476ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y)); 477CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b)); 478ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY; 479 480SET SESSION AUTHORIZATION regress_rls_bob; 481SELECT * FROM rec1; -- fail, mutual recursion 482 483-- 484-- Mutual recursion via views 485-- 486SET SESSION AUTHORIZATION regress_rls_bob; 487CREATE VIEW rec1v AS SELECT * FROM rec1; 488CREATE VIEW rec2v AS SELECT * FROM rec2; 489SET SESSION AUTHORIZATION regress_rls_alice; 490ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y)); 491ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b)); 492 493SET SESSION AUTHORIZATION regress_rls_bob; 494SELECT * FROM rec1; -- fail, mutual recursion via views 495 496-- 497-- Mutual recursion via .s.b views 498-- 499SET SESSION AUTHORIZATION regress_rls_bob; 500 501DROP VIEW rec1v, rec2v CASCADE; 502 503CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1; 504CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2; 505SET SESSION AUTHORIZATION regress_rls_alice; 506CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y)); 507CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b)); 508 509SET SESSION AUTHORIZATION regress_rls_bob; 510SELECT * FROM rec1; -- fail, mutual recursion via s.b. views 511 512-- 513-- recursive RLS and VIEWs in policy 514-- 515SET SESSION AUTHORIZATION regress_rls_alice; 516CREATE TABLE s1 (a int, b text); 517INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x); 518 519CREATE TABLE s2 (x int, y text); 520INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x); 521 522GRANT SELECT ON s1, s2 TO regress_rls_bob; 523 524CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%')); 525CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%')); 526CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1)); 527 528ALTER TABLE s1 ENABLE ROW LEVEL SECURITY; 529ALTER TABLE s2 ENABLE ROW LEVEL SECURITY; 530 531SET SESSION AUTHORIZATION regress_rls_bob; 532CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%'; 533SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion) 534 535INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion) 536 537SET SESSION AUTHORIZATION regress_rls_alice; 538DROP POLICY p3 on s1; 539ALTER POLICY p2 ON s2 USING (x % 2 = 0); 540 541SET SESSION AUTHORIZATION regress_rls_bob; 542SELECT * FROM s1 WHERE f_leak(b); -- OK 543EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b); 544 545SET SESSION AUTHORIZATION regress_rls_alice; 546ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy 547SET SESSION AUTHORIZATION regress_rls_bob; 548SELECT * FROM s1 WHERE f_leak(b); -- OK 549EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b); 550 551SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%'; 552EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%'; 553 554SET SESSION AUTHORIZATION regress_rls_alice; 555ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%')); 556SET SESSION AUTHORIZATION regress_rls_bob; 557SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view) 558 559-- prepared statement with regress_rls_alice privilege 560PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1; 561EXECUTE p1(2); 562EXPLAIN (COSTS OFF) EXECUTE p1(2); 563 564-- superuser is allowed to bypass RLS checks 565RESET SESSION AUTHORIZATION; 566SET row_security TO OFF; 567SELECT * FROM t1 WHERE f_leak(b); 568EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); 569 570-- plan cache should be invalidated 571EXECUTE p1(2); 572EXPLAIN (COSTS OFF) EXECUTE p1(2); 573 574PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1; 575EXECUTE p2(2); 576EXPLAIN (COSTS OFF) EXECUTE p2(2); 577 578-- also, case when privilege switch from superuser 579SET SESSION AUTHORIZATION regress_rls_bob; 580SET row_security TO ON; 581EXECUTE p2(2); 582EXPLAIN (COSTS OFF) EXECUTE p2(2); 583 584-- 585-- UPDATE / DELETE and Row-level security 586-- 587SET SESSION AUTHORIZATION regress_rls_bob; 588EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b); 589UPDATE t1 SET b = b || b WHERE f_leak(b); 590 591EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b); 592UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b); 593 594-- returning clause with system column 595UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; 596UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *; 597UPDATE t1 SET b = b WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; 598 599-- updates with from clause 600EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3 601WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b); 602 603UPDATE t2 SET b=t2.b FROM t3 604WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b); 605 606EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2 607WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b); 608 609UPDATE t1 SET b=t1.b FROM t2 610WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b); 611 612EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1 613WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b); 614 615UPDATE t2 SET b=t2.b FROM t1 616WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b); 617 618-- updates with from clause self join 619EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2 620WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b 621AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2; 622 623UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2 624WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b 625AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2; 626 627EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2 628WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b 629AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2; 630 631UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2 632WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b 633AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2; 634 635RESET SESSION AUTHORIZATION; 636SET row_security TO OFF; 637SELECT * FROM t1 ORDER BY a,b; 638 639SET SESSION AUTHORIZATION regress_rls_bob; 640SET row_security TO ON; 641EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b); 642EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b); 643 644DELETE FROM only t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; 645DELETE FROM t1 WHERE f_leak(b) RETURNING tableoid::regclass, *, t1; 646 647-- 648-- S.b. view on top of Row-level security 649-- 650SET SESSION AUTHORIZATION regress_rls_alice; 651CREATE TABLE b1 (a int, b text); 652INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x); 653 654CREATE POLICY p1 ON b1 USING (a % 2 = 0); 655ALTER TABLE b1 ENABLE ROW LEVEL SECURITY; 656GRANT ALL ON b1 TO regress_rls_bob; 657 658SET SESSION AUTHORIZATION regress_rls_bob; 659CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION; 660GRANT ALL ON bv1 TO regress_rls_carol; 661 662SET SESSION AUTHORIZATION regress_rls_carol; 663 664EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b); 665SELECT * FROM bv1 WHERE f_leak(b); 666 667INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO 668INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check 669INSERT INTO bv1 VALUES (12, 'xxx'); -- ok 670 671EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b); 672UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b); 673 674EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b); 675DELETE FROM bv1 WHERE a = 6 AND f_leak(b); 676 677SET SESSION AUTHORIZATION regress_rls_alice; 678SELECT * FROM b1; 679-- 680-- INSERT ... ON CONFLICT DO UPDATE and Row-level security 681-- 682 683SET SESSION AUTHORIZATION regress_rls_alice; 684DROP POLICY p1 ON document; 685DROP POLICY p1r ON document; 686 687CREATE POLICY p1 ON document FOR SELECT USING (true); 688CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user); 689CREATE POLICY p3 ON document FOR UPDATE 690 USING (cid = (SELECT cid from category WHERE cname = 'novel')) 691 WITH CHECK (dauthor = current_user); 692 693SET SESSION AUTHORIZATION regress_rls_bob; 694 695-- Exists... 696SELECT * FROM document WHERE did = 2; 697 698-- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since 699-- alternative UPDATE path happens to be taken): 700INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel') 701 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor; 702 703-- Violates USING qual for UPDATE policy p3. 704-- 705-- UPDATE path is taken, but UPDATE fails purely because *existing* row to be 706-- updated is not a "novel"/cid 11 (row is not leaked, even though we have 707-- SELECT privileges sufficient to see the row in this instance): 708INSERT INTO document VALUES (33, 22, 1, 'regress_rls_bob', 'okay science fiction'); -- preparation for next statement 709INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi') -- takes UPDATE path 710 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle; 711-- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs 712-- not violated): 713INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel') 714 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; 715-- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated): 716INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel') 717 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; 718-- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the 719-- case in respect of *existing* tuple): 720INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel') 721 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; 722-- Same query a third time, but now fails due to existing tuple finally not 723-- passing quals: 724INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel') 725 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; 726-- Don't fail just because INSERT doesn't satisfy WITH CHECK option that 727-- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE 728-- path *isn't* taken, and so UPDATE-related policy does not apply: 729INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert') 730 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; 731-- But this time, the same statement fails, because the UPDATE path is taken, 732-- and updating the row just inserted falls afoul of security barrier qual 733-- (enforced as WCO) -- what we might have updated target tuple to is 734-- irrelevant, in fact. 735INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert') 736 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; 737 738-- Test default USING qual enforced as WCO 739SET SESSION AUTHORIZATION regress_rls_alice; 740DROP POLICY p1 ON document; 741DROP POLICY p2 ON document; 742DROP POLICY p3 ON document; 743 744CREATE POLICY p3_with_default ON document FOR UPDATE 745 USING (cid = (SELECT cid from category WHERE cname = 'novel')); 746 747SET SESSION AUTHORIZATION regress_rls_bob; 748-- Just because WCO-style enforcement of USING quals occurs with 749-- existing/target tuple does not mean that the implementation can be allowed 750-- to fail to also enforce this qual against the final tuple appended to 751-- relation (since in the absence of an explicit WCO, this is also interpreted 752-- as an UPDATE/ALL WCO in general). 753-- 754-- UPDATE path is taken here (fails due to existing tuple). Note that this is 755-- not reported as a "USING expression", because it's an RLS UPDATE check that originated as 756-- a USING qual for the purposes of RLS in general, as opposed to an explicit 757-- USING qual that is ordinarily a security barrier. We leave it up to the 758-- UPDATE to make this fail: 759INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert') 760 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; 761 762-- UPDATE path is taken here. Existing tuple passes, since its cid 763-- corresponds to "novel", but default USING qual is enforced against 764-- post-UPDATE tuple too (as always when updating with a policy that lacks an 765-- explicit WCO), and so this fails: 766INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'my first novel') 767 ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *; 768 769SET SESSION AUTHORIZATION regress_rls_alice; 770DROP POLICY p3_with_default ON document; 771 772-- 773-- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE 774-- tests) 775-- 776CREATE POLICY p3_with_all ON document FOR ALL 777 USING (cid = (SELECT cid from category WHERE cname = 'novel')) 778 WITH CHECK (dauthor = current_user); 779 780SET SESSION AUTHORIZATION regress_rls_bob; 781 782-- Fails, since ALL WCO is enforced in insert path: 783INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel') 784 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33; 785-- Fails, since ALL policy USING qual is enforced (existing, target tuple is in 786-- violation, since it has the "manga" cid): 787INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel') 788 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle; 789-- Fails, since ALL WCO are enforced: 790INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel') 791 ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol'; 792 793-- 794-- ROLE/GROUP 795-- 796SET SESSION AUTHORIZATION regress_rls_alice; 797CREATE TABLE z1 (a int, b text); 798CREATE TABLE z2 (a int, b text); 799 800GRANT SELECT ON z1,z2 TO regress_rls_group1, regress_rls_group2, 801 regress_rls_bob, regress_rls_carol; 802 803INSERT INTO z1 VALUES 804 (1, 'aba'), 805 (2, 'bbb'), 806 (3, 'ccc'), 807 (4, 'dad'); 808 809CREATE POLICY p1 ON z1 TO regress_rls_group1 USING (a % 2 = 0); 810CREATE POLICY p2 ON z1 TO regress_rls_group2 USING (a % 2 = 1); 811 812ALTER TABLE z1 ENABLE ROW LEVEL SECURITY; 813 814SET SESSION AUTHORIZATION regress_rls_bob; 815SELECT * FROM z1 WHERE f_leak(b); 816EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b); 817 818PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b); 819EXPLAIN (COSTS OFF) EXECUTE plancache_test; 820 821PREPARE plancache_test2 AS WITH q AS MATERIALIZED (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2; 822EXPLAIN (COSTS OFF) EXECUTE plancache_test2; 823 824PREPARE plancache_test3 AS WITH q AS MATERIALIZED (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b); 825EXPLAIN (COSTS OFF) EXECUTE plancache_test3; 826 827SET ROLE regress_rls_group1; 828SELECT * FROM z1 WHERE f_leak(b); 829EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b); 830 831EXPLAIN (COSTS OFF) EXECUTE plancache_test; 832EXPLAIN (COSTS OFF) EXECUTE plancache_test2; 833EXPLAIN (COSTS OFF) EXECUTE plancache_test3; 834 835SET SESSION AUTHORIZATION regress_rls_carol; 836SELECT * FROM z1 WHERE f_leak(b); 837EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b); 838 839EXPLAIN (COSTS OFF) EXECUTE plancache_test; 840EXPLAIN (COSTS OFF) EXECUTE plancache_test2; 841EXPLAIN (COSTS OFF) EXECUTE plancache_test3; 842 843SET ROLE regress_rls_group2; 844SELECT * FROM z1 WHERE f_leak(b); 845EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b); 846 847EXPLAIN (COSTS OFF) EXECUTE plancache_test; 848EXPLAIN (COSTS OFF) EXECUTE plancache_test2; 849EXPLAIN (COSTS OFF) EXECUTE plancache_test3; 850 851-- 852-- Views should follow policy for view owner. 853-- 854-- View and Table owner are the same. 855SET SESSION AUTHORIZATION regress_rls_alice; 856CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b); 857GRANT SELECT ON rls_view TO regress_rls_bob; 858 859-- Query as role that is not owner of view or table. Should return all records. 860SET SESSION AUTHORIZATION regress_rls_bob; 861SELECT * FROM rls_view; 862EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 863 864-- Query as view/table owner. Should return all records. 865SET SESSION AUTHORIZATION regress_rls_alice; 866SELECT * FROM rls_view; 867EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 868DROP VIEW rls_view; 869 870-- View and Table owners are different. 871SET SESSION AUTHORIZATION regress_rls_bob; 872CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b); 873GRANT SELECT ON rls_view TO regress_rls_alice; 874 875-- Query as role that is not owner of view but is owner of table. 876-- Should return records based on view owner policies. 877SET SESSION AUTHORIZATION regress_rls_alice; 878SELECT * FROM rls_view; 879EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 880 881-- Query as role that is not owner of table but is owner of view. 882-- Should return records based on view owner policies. 883SET SESSION AUTHORIZATION regress_rls_bob; 884SELECT * FROM rls_view; 885EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 886 887-- Query as role that is not the owner of the table or view without permissions. 888SET SESSION AUTHORIZATION regress_rls_carol; 889SELECT * FROM rls_view; --fail - permission denied. 890EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied. 891 892-- Query as role that is not the owner of the table or view with permissions. 893SET SESSION AUTHORIZATION regress_rls_bob; 894GRANT SELECT ON rls_view TO regress_rls_carol; 895SELECT * FROM rls_view; 896EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 897 898SET SESSION AUTHORIZATION regress_rls_bob; 899DROP VIEW rls_view; 900 901-- 902-- Command specific 903-- 904SET SESSION AUTHORIZATION regress_rls_alice; 905 906CREATE TABLE x1 (a int, b text, c text); 907GRANT ALL ON x1 TO PUBLIC; 908 909INSERT INTO x1 VALUES 910 (1, 'abc', 'regress_rls_bob'), 911 (2, 'bcd', 'regress_rls_bob'), 912 (3, 'cde', 'regress_rls_carol'), 913 (4, 'def', 'regress_rls_carol'), 914 (5, 'efg', 'regress_rls_bob'), 915 (6, 'fgh', 'regress_rls_bob'), 916 (7, 'fgh', 'regress_rls_carol'), 917 (8, 'fgh', 'regress_rls_carol'); 918 919CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user); 920CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0); 921CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1); 922CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0); 923CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8); 924 925ALTER TABLE x1 ENABLE ROW LEVEL SECURITY; 926 927SET SESSION AUTHORIZATION regress_rls_bob; 928SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC; 929UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *; 930 931SET SESSION AUTHORIZATION regress_rls_carol; 932SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC; 933UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *; 934DELETE FROM x1 WHERE f_leak(b) RETURNING *; 935 936-- 937-- Duplicate Policy Names 938-- 939SET SESSION AUTHORIZATION regress_rls_alice; 940CREATE TABLE y1 (a int, b text); 941CREATE TABLE y2 (a int, b text); 942 943GRANT ALL ON y1, y2 TO regress_rls_bob; 944 945CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0); 946CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2); 947CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail 948CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK 949 950ALTER TABLE y1 ENABLE ROW LEVEL SECURITY; 951ALTER TABLE y2 ENABLE ROW LEVEL SECURITY; 952 953-- 954-- Expression structure with SBV 955-- 956-- Create view as table owner. RLS should NOT be applied. 957SET SESSION AUTHORIZATION regress_rls_alice; 958CREATE VIEW rls_sbv WITH (security_barrier) AS 959 SELECT * FROM y1 WHERE f_leak(b); 960EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1); 961DROP VIEW rls_sbv; 962 963-- Create view as role that does not own table. RLS should be applied. 964SET SESSION AUTHORIZATION regress_rls_bob; 965CREATE VIEW rls_sbv WITH (security_barrier) AS 966 SELECT * FROM y1 WHERE f_leak(b); 967EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1); 968DROP VIEW rls_sbv; 969 970-- 971-- Expression structure 972-- 973SET SESSION AUTHORIZATION regress_rls_alice; 974INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x); 975CREATE POLICY p2 ON y2 USING (a % 3 = 0); 976CREATE POLICY p3 ON y2 USING (a % 4 = 0); 977 978SET SESSION AUTHORIZATION regress_rls_bob; 979SELECT * FROM y2 WHERE f_leak(b); 980EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b); 981 982-- 983-- Qual push-down of leaky functions, when not referring to table 984-- 985SELECT * FROM y2 WHERE f_leak('abc'); 986EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc'); 987 988CREATE TABLE test_qual_pushdown ( 989 abc text 990); 991 992INSERT INTO test_qual_pushdown VALUES ('abc'),('def'); 993 994SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc); 995EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc); 996 997SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b); 998EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b); 999 1000DROP TABLE test_qual_pushdown; 1001 1002-- 1003-- Plancache invalidate on user change. 1004-- 1005RESET SESSION AUTHORIZATION; 1006 1007DROP TABLE t1 CASCADE; 1008 1009CREATE TABLE t1 (a integer); 1010 1011GRANT SELECT ON t1 TO regress_rls_bob, regress_rls_carol; 1012 1013CREATE POLICY p1 ON t1 TO regress_rls_bob USING ((a % 2) = 0); 1014CREATE POLICY p2 ON t1 TO regress_rls_carol USING ((a % 4) = 0); 1015 1016ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; 1017 1018-- Prepare as regress_rls_bob 1019SET ROLE regress_rls_bob; 1020PREPARE role_inval AS SELECT * FROM t1; 1021-- Check plan 1022EXPLAIN (COSTS OFF) EXECUTE role_inval; 1023 1024-- Change to regress_rls_carol 1025SET ROLE regress_rls_carol; 1026-- Check plan- should be different 1027EXPLAIN (COSTS OFF) EXECUTE role_inval; 1028 1029-- Change back to regress_rls_bob 1030SET ROLE regress_rls_bob; 1031-- Check plan- should be back to original 1032EXPLAIN (COSTS OFF) EXECUTE role_inval; 1033 1034-- 1035-- CTE and RLS 1036-- 1037RESET SESSION AUTHORIZATION; 1038DROP TABLE t1 CASCADE; 1039CREATE TABLE t1 (a integer, b text); 1040CREATE POLICY p1 ON t1 USING (a % 2 = 0); 1041 1042ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; 1043 1044GRANT ALL ON t1 TO regress_rls_bob; 1045 1046INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x); 1047 1048SET SESSION AUTHORIZATION regress_rls_bob; 1049 1050WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1; 1051EXPLAIN (COSTS OFF) 1052WITH cte1 AS MATERIALIZED (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1; 1053 1054WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail 1055WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok 1056 1057WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail 1058WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok 1059 1060-- 1061-- Rename Policy 1062-- 1063RESET SESSION AUTHORIZATION; 1064ALTER POLICY p1 ON t1 RENAME TO p1; --fail 1065 1066SELECT polname, relname 1067 FROM pg_policy pol 1068 JOIN pg_class pc ON (pc.oid = pol.polrelid) 1069 WHERE relname = 't1'; 1070 1071ALTER POLICY p1 ON t1 RENAME TO p2; --ok 1072 1073SELECT polname, relname 1074 FROM pg_policy pol 1075 JOIN pg_class pc ON (pc.oid = pol.polrelid) 1076 WHERE relname = 't1'; 1077 1078-- 1079-- Check INSERT SELECT 1080-- 1081SET SESSION AUTHORIZATION regress_rls_bob; 1082CREATE TABLE t2 (a integer, b text); 1083INSERT INTO t2 (SELECT * FROM t1); 1084EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1); 1085SELECT * FROM t2; 1086EXPLAIN (COSTS OFF) SELECT * FROM t2; 1087CREATE TABLE t3 AS SELECT * FROM t1; 1088SELECT * FROM t3; 1089SELECT * INTO t4 FROM t1; 1090SELECT * FROM t4; 1091 1092-- 1093-- RLS with JOIN 1094-- 1095SET SESSION AUTHORIZATION regress_rls_alice; 1096CREATE TABLE blog (id integer, author text, post text); 1097CREATE TABLE comment (blog_id integer, message text); 1098 1099GRANT ALL ON blog, comment TO regress_rls_bob; 1100 1101CREATE POLICY blog_1 ON blog USING (id % 2 = 0); 1102 1103ALTER TABLE blog ENABLE ROW LEVEL SECURITY; 1104 1105INSERT INTO blog VALUES 1106 (1, 'alice', 'blog #1'), 1107 (2, 'bob', 'blog #1'), 1108 (3, 'alice', 'blog #2'), 1109 (4, 'alice', 'blog #3'), 1110 (5, 'john', 'blog #1'); 1111 1112INSERT INTO comment VALUES 1113 (1, 'cool blog'), 1114 (1, 'fun blog'), 1115 (3, 'crazy blog'), 1116 (5, 'what?'), 1117 (4, 'insane!'), 1118 (2, 'who did it?'); 1119 1120SET SESSION AUTHORIZATION regress_rls_bob; 1121-- Check RLS JOIN with Non-RLS. 1122SELECT id, author, message FROM blog JOIN comment ON id = blog_id; 1123-- Check Non-RLS JOIN with RLS. 1124SELECT id, author, message FROM comment JOIN blog ON id = blog_id; 1125 1126SET SESSION AUTHORIZATION regress_rls_alice; 1127CREATE POLICY comment_1 ON comment USING (blog_id < 4); 1128 1129ALTER TABLE comment ENABLE ROW LEVEL SECURITY; 1130 1131SET SESSION AUTHORIZATION regress_rls_bob; 1132-- Check RLS JOIN RLS 1133SELECT id, author, message FROM blog JOIN comment ON id = blog_id; 1134SELECT id, author, message FROM comment JOIN blog ON id = blog_id; 1135 1136SET SESSION AUTHORIZATION regress_rls_alice; 1137DROP TABLE blog, comment; 1138 1139-- 1140-- Default Deny Policy 1141-- 1142RESET SESSION AUTHORIZATION; 1143DROP POLICY p2 ON t1; 1144ALTER TABLE t1 OWNER TO regress_rls_alice; 1145 1146-- Check that default deny does not apply to superuser. 1147RESET SESSION AUTHORIZATION; 1148SELECT * FROM t1; 1149EXPLAIN (COSTS OFF) SELECT * FROM t1; 1150 1151-- Check that default deny does not apply to table owner. 1152SET SESSION AUTHORIZATION regress_rls_alice; 1153SELECT * FROM t1; 1154EXPLAIN (COSTS OFF) SELECT * FROM t1; 1155 1156-- Check that default deny applies to non-owner/non-superuser when RLS on. 1157SET SESSION AUTHORIZATION regress_rls_bob; 1158SET row_security TO ON; 1159SELECT * FROM t1; 1160EXPLAIN (COSTS OFF) SELECT * FROM t1; 1161SET SESSION AUTHORIZATION regress_rls_bob; 1162SELECT * FROM t1; 1163EXPLAIN (COSTS OFF) SELECT * FROM t1; 1164 1165-- 1166-- COPY TO/FROM 1167-- 1168 1169RESET SESSION AUTHORIZATION; 1170DROP TABLE copy_t CASCADE; 1171CREATE TABLE copy_t (a integer, b text); 1172CREATE POLICY p1 ON copy_t USING (a % 2 = 0); 1173 1174ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY; 1175 1176GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user; 1177 1178INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x); 1179 1180-- Check COPY TO as Superuser/owner. 1181RESET SESSION AUTHORIZATION; 1182SET row_security TO OFF; 1183COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; 1184SET row_security TO ON; 1185COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; 1186 1187-- Check COPY TO as user with permissions. 1188SET SESSION AUTHORIZATION regress_rls_bob; 1189SET row_security TO OFF; 1190COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS 1191SET row_security TO ON; 1192COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok 1193 1194-- Check COPY TO as user with permissions and BYPASSRLS 1195SET SESSION AUTHORIZATION regress_rls_exempt_user; 1196SET row_security TO OFF; 1197COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok 1198SET row_security TO ON; 1199COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok 1200 1201-- Check COPY TO as user without permissions. SET row_security TO OFF; 1202SET SESSION AUTHORIZATION regress_rls_carol; 1203SET row_security TO OFF; 1204COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS 1205SET row_security TO ON; 1206COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied 1207 1208-- Check COPY relation TO; keep it just one row to avoid reordering issues 1209RESET SESSION AUTHORIZATION; 1210SET row_security TO ON; 1211CREATE TABLE copy_rel_to (a integer, b text); 1212CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0); 1213 1214ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY; 1215 1216GRANT ALL ON copy_rel_to TO regress_rls_bob, regress_rls_exempt_user; 1217 1218INSERT INTO copy_rel_to VALUES (1, md5('1')); 1219 1220-- Check COPY TO as Superuser/owner. 1221RESET SESSION AUTHORIZATION; 1222SET row_security TO OFF; 1223COPY copy_rel_to TO STDOUT WITH DELIMITER ','; 1224SET row_security TO ON; 1225COPY copy_rel_to TO STDOUT WITH DELIMITER ','; 1226 1227-- Check COPY TO as user with permissions. 1228SET SESSION AUTHORIZATION regress_rls_bob; 1229SET row_security TO OFF; 1230COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS 1231SET row_security TO ON; 1232COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok 1233 1234-- Check COPY TO as user with permissions and BYPASSRLS 1235SET SESSION AUTHORIZATION regress_rls_exempt_user; 1236SET row_security TO OFF; 1237COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok 1238SET row_security TO ON; 1239COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok 1240 1241-- Check COPY TO as user without permissions. SET row_security TO OFF; 1242SET SESSION AUTHORIZATION regress_rls_carol; 1243SET row_security TO OFF; 1244COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied 1245SET row_security TO ON; 1246COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied 1247 1248-- Check COPY FROM as Superuser/owner. 1249RESET SESSION AUTHORIZATION; 1250SET row_security TO OFF; 1251SET row_security TO ON; 1252 1253-- Check COPY FROM as user with permissions. 1254SET SESSION AUTHORIZATION regress_rls_bob; 1255SET row_security TO OFF; 1256COPY copy_t FROM STDIN; --fail - would be affected by RLS. 1257SET row_security TO ON; 1258COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS. 1259 1260-- Check COPY FROM as user with permissions and BYPASSRLS 1261SET SESSION AUTHORIZATION regress_rls_exempt_user; 1262SET row_security TO ON; 1263 1264-- Check COPY FROM as user without permissions. 1265SET SESSION AUTHORIZATION regress_rls_carol; 1266SET row_security TO OFF; 1267COPY copy_t FROM STDIN; --fail - permission denied. 1268SET row_security TO ON; 1269COPY copy_t FROM STDIN; --fail - permission denied. 1270 1271RESET SESSION AUTHORIZATION; 1272DROP TABLE copy_t; 1273DROP TABLE copy_rel_to CASCADE; 1274 1275-- Check WHERE CURRENT OF 1276SET SESSION AUTHORIZATION regress_rls_alice; 1277 1278CREATE TABLE current_check (currentid int, payload text, rlsuser text); 1279GRANT ALL ON current_check TO PUBLIC; 1280 1281INSERT INTO current_check VALUES 1282 (1, 'abc', 'regress_rls_bob'), 1283 (2, 'bcd', 'regress_rls_bob'), 1284 (3, 'cde', 'regress_rls_bob'), 1285 (4, 'def', 'regress_rls_bob'); 1286 1287CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0); 1288CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user); 1289CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user); 1290 1291ALTER TABLE current_check ENABLE ROW LEVEL SECURITY; 1292 1293SET SESSION AUTHORIZATION regress_rls_bob; 1294 1295-- Can SELECT even rows 1296SELECT * FROM current_check; 1297 1298-- Cannot UPDATE row 2 1299UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *; 1300 1301BEGIN; 1302 1303DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check; 1304-- Returns rows that can be seen according to SELECT policy, like plain SELECT 1305-- above (even rows) 1306FETCH ABSOLUTE 1 FROM current_check_cursor; 1307-- Still cannot UPDATE row 2 through cursor 1308UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *; 1309-- Can update row 4 through cursor, which is the next visible row 1310FETCH RELATIVE 1 FROM current_check_cursor; 1311UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *; 1312SELECT * FROM current_check; 1313-- Plan should be a subquery TID scan 1314EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor; 1315-- Similarly can only delete row 4 1316FETCH ABSOLUTE 1 FROM current_check_cursor; 1317DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *; 1318FETCH RELATIVE 1 FROM current_check_cursor; 1319DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *; 1320SELECT * FROM current_check; 1321 1322COMMIT; 1323 1324-- 1325-- check pg_stats view filtering 1326-- 1327SET row_security TO ON; 1328SET SESSION AUTHORIZATION regress_rls_alice; 1329ANALYZE current_check; 1330-- Stats visible 1331SELECT row_security_active('current_check'); 1332SELECT attname, most_common_vals FROM pg_stats 1333 WHERE tablename = 'current_check' 1334 ORDER BY 1; 1335 1336SET SESSION AUTHORIZATION regress_rls_bob; 1337-- Stats not visible 1338SELECT row_security_active('current_check'); 1339SELECT attname, most_common_vals FROM pg_stats 1340 WHERE tablename = 'current_check' 1341 ORDER BY 1; 1342 1343-- 1344-- Collation support 1345-- 1346BEGIN; 1347CREATE TABLE coll_t (c) AS VALUES ('bar'::text); 1348CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C")); 1349ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY; 1350GRANT SELECT ON coll_t TO regress_rls_alice; 1351SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass; 1352SET SESSION AUTHORIZATION regress_rls_alice; 1353SELECT * FROM coll_t; 1354ROLLBACK; 1355 1356-- 1357-- Shared Object Dependencies 1358-- 1359RESET SESSION AUTHORIZATION; 1360BEGIN; 1361CREATE ROLE regress_rls_eve; 1362CREATE ROLE regress_rls_frank; 1363CREATE TABLE tbl1 (c) AS VALUES ('bar'::text); 1364GRANT SELECT ON TABLE tbl1 TO regress_rls_eve; 1365CREATE POLICY P ON tbl1 TO regress_rls_eve, regress_rls_frank USING (true); 1366SELECT refclassid::regclass, deptype 1367 FROM pg_depend 1368 WHERE classid = 'pg_policy'::regclass 1369 AND refobjid = 'tbl1'::regclass; 1370SELECT refclassid::regclass, deptype 1371 FROM pg_shdepend 1372 WHERE classid = 'pg_policy'::regclass 1373 AND refobjid IN ('regress_rls_eve'::regrole, 'regress_rls_frank'::regrole); 1374 1375SAVEPOINT q; 1376DROP ROLE regress_rls_eve; --fails due to dependency on POLICY p 1377ROLLBACK TO q; 1378 1379ALTER POLICY p ON tbl1 TO regress_rls_frank USING (true); 1380SAVEPOINT q; 1381DROP ROLE regress_rls_eve; --fails due to dependency on GRANT SELECT 1382ROLLBACK TO q; 1383 1384REVOKE ALL ON TABLE tbl1 FROM regress_rls_eve; 1385SAVEPOINT q; 1386DROP ROLE regress_rls_eve; --succeeds 1387ROLLBACK TO q; 1388 1389SAVEPOINT q; 1390DROP ROLE regress_rls_frank; --fails due to dependency on POLICY p 1391ROLLBACK TO q; 1392 1393DROP POLICY p ON tbl1; 1394SAVEPOINT q; 1395DROP ROLE regress_rls_frank; -- succeeds 1396ROLLBACK TO q; 1397 1398ROLLBACK; -- cleanup 1399 1400-- 1401-- Converting table to view 1402-- 1403BEGIN; 1404CREATE TABLE t (c int); 1405CREATE POLICY p ON t USING (c % 2 = 1); 1406ALTER TABLE t ENABLE ROW LEVEL SECURITY; 1407 1408SAVEPOINT q; 1409CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD 1410 SELECT * FROM generate_series(1,5) t0(c); -- fails due to row level security enabled 1411ROLLBACK TO q; 1412 1413ALTER TABLE t DISABLE ROW LEVEL SECURITY; 1414SAVEPOINT q; 1415CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD 1416 SELECT * FROM generate_series(1,5) t0(c); -- fails due to policy p on t 1417ROLLBACK TO q; 1418 1419DROP POLICY p ON t; 1420CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD 1421 SELECT * FROM generate_series(1,5) t0(c); -- succeeds 1422ROLLBACK; 1423 1424-- 1425-- Policy expression handling 1426-- 1427BEGIN; 1428CREATE TABLE t (c) AS VALUES ('bar'::text); 1429CREATE POLICY p ON t USING (max(c)); -- fails: aggregate functions are not allowed in policy expressions 1430ROLLBACK; 1431 1432-- 1433-- Non-target relations are only subject to SELECT policies 1434-- 1435SET SESSION AUTHORIZATION regress_rls_alice; 1436CREATE TABLE r1 (a int); 1437CREATE TABLE r2 (a int); 1438INSERT INTO r1 VALUES (10), (20); 1439INSERT INTO r2 VALUES (10), (20); 1440 1441GRANT ALL ON r1, r2 TO regress_rls_bob; 1442 1443CREATE POLICY p1 ON r1 USING (true); 1444ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 1445 1446CREATE POLICY p1 ON r2 FOR SELECT USING (true); 1447CREATE POLICY p2 ON r2 FOR INSERT WITH CHECK (false); 1448CREATE POLICY p3 ON r2 FOR UPDATE USING (false); 1449CREATE POLICY p4 ON r2 FOR DELETE USING (false); 1450ALTER TABLE r2 ENABLE ROW LEVEL SECURITY; 1451 1452SET SESSION AUTHORIZATION regress_rls_bob; 1453SELECT * FROM r1; 1454SELECT * FROM r2; 1455 1456-- r2 is read-only 1457INSERT INTO r2 VALUES (2); -- Not allowed 1458UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing 1459DELETE FROM r2 RETURNING *; -- Deletes nothing 1460 1461-- r2 can be used as a non-target relation in DML 1462INSERT INTO r1 SELECT a + 1 FROM r2 RETURNING *; -- OK 1463UPDATE r1 SET a = r2.a + 2 FROM r2 WHERE r1.a = r2.a RETURNING *; -- OK 1464DELETE FROM r1 USING r2 WHERE r1.a = r2.a + 2 RETURNING *; -- OK 1465SELECT * FROM r1; 1466SELECT * FROM r2; 1467 1468SET SESSION AUTHORIZATION regress_rls_alice; 1469DROP TABLE r1; 1470DROP TABLE r2; 1471 1472-- 1473-- FORCE ROW LEVEL SECURITY applies RLS to owners too 1474-- 1475SET SESSION AUTHORIZATION regress_rls_alice; 1476SET row_security = on; 1477CREATE TABLE r1 (a int); 1478INSERT INTO r1 VALUES (10), (20); 1479 1480CREATE POLICY p1 ON r1 USING (false); 1481ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 1482ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 1483 1484-- No error, but no rows 1485TABLE r1; 1486 1487-- RLS error 1488INSERT INTO r1 VALUES (1); 1489 1490-- No error (unable to see any rows to update) 1491UPDATE r1 SET a = 1; 1492TABLE r1; 1493 1494-- No error (unable to see any rows to delete) 1495DELETE FROM r1; 1496TABLE r1; 1497 1498SET row_security = off; 1499-- these all fail, would be affected by RLS 1500TABLE r1; 1501UPDATE r1 SET a = 1; 1502DELETE FROM r1; 1503 1504DROP TABLE r1; 1505 1506-- 1507-- FORCE ROW LEVEL SECURITY does not break RI 1508-- 1509SET SESSION AUTHORIZATION regress_rls_alice; 1510SET row_security = on; 1511CREATE TABLE r1 (a int PRIMARY KEY); 1512CREATE TABLE r2 (a int REFERENCES r1); 1513INSERT INTO r1 VALUES (10), (20); 1514INSERT INTO r2 VALUES (10), (20); 1515 1516-- Create policies on r2 which prevent the 1517-- owner from seeing any rows, but RI should 1518-- still see them. 1519CREATE POLICY p1 ON r2 USING (false); 1520ALTER TABLE r2 ENABLE ROW LEVEL SECURITY; 1521ALTER TABLE r2 FORCE ROW LEVEL SECURITY; 1522 1523-- Errors due to rows in r2 1524DELETE FROM r1; 1525 1526-- Reset r2 to no-RLS 1527DROP POLICY p1 ON r2; 1528ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY; 1529ALTER TABLE r2 DISABLE ROW LEVEL SECURITY; 1530 1531-- clean out r2 for INSERT test below 1532DELETE FROM r2; 1533 1534-- Change r1 to not allow rows to be seen 1535CREATE POLICY p1 ON r1 USING (false); 1536ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 1537ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 1538 1539-- No rows seen 1540TABLE r1; 1541 1542-- No error, RI still sees that row exists in r1 1543INSERT INTO r2 VALUES (10); 1544 1545DROP TABLE r2; 1546DROP TABLE r1; 1547 1548-- Ensure cascaded DELETE works 1549CREATE TABLE r1 (a int PRIMARY KEY); 1550CREATE TABLE r2 (a int REFERENCES r1 ON DELETE CASCADE); 1551INSERT INTO r1 VALUES (10), (20); 1552INSERT INTO r2 VALUES (10), (20); 1553 1554-- Create policies on r2 which prevent the 1555-- owner from seeing any rows, but RI should 1556-- still see them. 1557CREATE POLICY p1 ON r2 USING (false); 1558ALTER TABLE r2 ENABLE ROW LEVEL SECURITY; 1559ALTER TABLE r2 FORCE ROW LEVEL SECURITY; 1560 1561-- Deletes all records from both 1562DELETE FROM r1; 1563 1564-- Remove FORCE from r2 1565ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY; 1566 1567-- As owner, we now bypass RLS 1568-- verify no rows in r2 now 1569TABLE r2; 1570 1571DROP TABLE r2; 1572DROP TABLE r1; 1573 1574-- Ensure cascaded UPDATE works 1575CREATE TABLE r1 (a int PRIMARY KEY); 1576CREATE TABLE r2 (a int REFERENCES r1 ON UPDATE CASCADE); 1577INSERT INTO r1 VALUES (10), (20); 1578INSERT INTO r2 VALUES (10), (20); 1579 1580-- Create policies on r2 which prevent the 1581-- owner from seeing any rows, but RI should 1582-- still see them. 1583CREATE POLICY p1 ON r2 USING (false); 1584ALTER TABLE r2 ENABLE ROW LEVEL SECURITY; 1585ALTER TABLE r2 FORCE ROW LEVEL SECURITY; 1586 1587-- Updates records in both 1588UPDATE r1 SET a = a+5; 1589 1590-- Remove FORCE from r2 1591ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY; 1592 1593-- As owner, we now bypass RLS 1594-- verify records in r2 updated 1595TABLE r2; 1596 1597DROP TABLE r2; 1598DROP TABLE r1; 1599 1600-- 1601-- Test INSERT+RETURNING applies SELECT policies as 1602-- WithCheckOptions (meaning an error is thrown) 1603-- 1604SET SESSION AUTHORIZATION regress_rls_alice; 1605SET row_security = on; 1606CREATE TABLE r1 (a int); 1607 1608CREATE POLICY p1 ON r1 FOR SELECT USING (false); 1609CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true); 1610ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 1611ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 1612 1613-- Works fine 1614INSERT INTO r1 VALUES (10), (20); 1615 1616-- No error, but no rows 1617TABLE r1; 1618 1619SET row_security = off; 1620-- fail, would be affected by RLS 1621TABLE r1; 1622 1623SET row_security = on; 1624 1625-- Error 1626INSERT INTO r1 VALUES (10), (20) RETURNING *; 1627 1628DROP TABLE r1; 1629 1630-- 1631-- Test UPDATE+RETURNING applies SELECT policies as 1632-- WithCheckOptions (meaning an error is thrown) 1633-- 1634SET SESSION AUTHORIZATION regress_rls_alice; 1635SET row_security = on; 1636CREATE TABLE r1 (a int PRIMARY KEY); 1637 1638CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20); 1639CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true); 1640CREATE POLICY p3 ON r1 FOR INSERT WITH CHECK (true); 1641INSERT INTO r1 VALUES (10); 1642ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 1643ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 1644 1645-- Works fine 1646UPDATE r1 SET a = 30; 1647 1648-- Show updated rows 1649ALTER TABLE r1 NO FORCE ROW LEVEL SECURITY; 1650TABLE r1; 1651-- reset value in r1 for test with RETURNING 1652UPDATE r1 SET a = 10; 1653 1654-- Verify row reset 1655TABLE r1; 1656 1657ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 1658 1659-- Error 1660UPDATE r1 SET a = 30 RETURNING *; 1661 1662-- UPDATE path of INSERT ... ON CONFLICT DO UPDATE should also error out 1663INSERT INTO r1 VALUES (10) 1664 ON CONFLICT (a) DO UPDATE SET a = 30 RETURNING *; 1665 1666-- Should still error out without RETURNING (use of arbiter always requires 1667-- SELECT permissions) 1668INSERT INTO r1 VALUES (10) 1669 ON CONFLICT (a) DO UPDATE SET a = 30; 1670INSERT INTO r1 VALUES (10) 1671 ON CONFLICT ON CONSTRAINT r1_pkey DO UPDATE SET a = 30; 1672 1673DROP TABLE r1; 1674 1675-- Check dependency handling 1676RESET SESSION AUTHORIZATION; 1677CREATE TABLE dep1 (c1 int); 1678CREATE TABLE dep2 (c1 int); 1679 1680CREATE POLICY dep_p1 ON dep1 TO regress_rls_bob USING (c1 > (select max(dep2.c1) from dep2)); 1681ALTER POLICY dep_p1 ON dep1 TO regress_rls_bob,regress_rls_carol; 1682 1683-- Should return one 1684SELECT count(*) = 1 FROM pg_depend 1685 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1') 1686 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2'); 1687 1688ALTER POLICY dep_p1 ON dep1 USING (true); 1689 1690-- Should return one 1691SELECT count(*) = 1 FROM pg_shdepend 1692 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1') 1693 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_bob'); 1694 1695-- Should return one 1696SELECT count(*) = 1 FROM pg_shdepend 1697 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1') 1698 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_carol'); 1699 1700-- Should return zero 1701SELECT count(*) = 0 FROM pg_depend 1702 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1') 1703 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2'); 1704 1705-- DROP OWNED BY testing 1706RESET SESSION AUTHORIZATION; 1707 1708CREATE ROLE regress_rls_dob_role1; 1709CREATE ROLE regress_rls_dob_role2; 1710 1711CREATE TABLE dob_t1 (c1 int); 1712CREATE TABLE dob_t2 (c1 int) PARTITION BY RANGE (c1); 1713 1714CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1 USING (true); 1715DROP OWNED BY regress_rls_dob_role1; 1716DROP POLICY p1 ON dob_t1; -- should fail, already gone 1717 1718CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true); 1719DROP OWNED BY regress_rls_dob_role1; 1720DROP POLICY p1 ON dob_t1; -- should succeed 1721 1722CREATE POLICY p1 ON dob_t2 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true); 1723DROP OWNED BY regress_rls_dob_role1; 1724DROP POLICY p1 ON dob_t2; -- should succeed 1725 1726DROP USER regress_rls_dob_role1; 1727DROP USER regress_rls_dob_role2; 1728 1729-- Bug #15708: view + table with RLS should check policies as view owner 1730CREATE TABLE ref_tbl (a int); 1731INSERT INTO ref_tbl VALUES (1); 1732 1733CREATE TABLE rls_tbl (a int); 1734INSERT INTO rls_tbl VALUES (10); 1735ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY; 1736CREATE POLICY p1 ON rls_tbl USING (EXISTS (SELECT 1 FROM ref_tbl)); 1737 1738GRANT SELECT ON ref_tbl TO regress_rls_bob; 1739GRANT SELECT ON rls_tbl TO regress_rls_bob; 1740 1741CREATE VIEW rls_view AS SELECT * FROM rls_tbl; 1742ALTER VIEW rls_view OWNER TO regress_rls_bob; 1743GRANT SELECT ON rls_view TO regress_rls_alice; 1744 1745SET SESSION AUTHORIZATION regress_rls_alice; 1746SELECT * FROM ref_tbl; -- Permission denied 1747SELECT * FROM rls_tbl; -- Permission denied 1748SELECT * FROM rls_view; -- OK 1749RESET SESSION AUTHORIZATION; 1750 1751DROP VIEW rls_view; 1752DROP TABLE rls_tbl; 1753DROP TABLE ref_tbl; 1754 1755-- 1756-- Clean up objects 1757-- 1758RESET SESSION AUTHORIZATION; 1759 1760DROP SCHEMA regress_rls_schema CASCADE; 1761 1762DROP USER regress_rls_alice; 1763DROP USER regress_rls_bob; 1764DROP USER regress_rls_carol; 1765DROP USER regress_rls_dave; 1766DROP USER regress_rls_exempt_user; 1767DROP ROLE regress_rls_group1; 1768DROP ROLE regress_rls_group2; 1769 1770-- Arrange to have a few policies left over, for testing 1771-- pg_dump/pg_restore 1772CREATE SCHEMA regress_rls_schema; 1773CREATE TABLE rls_tbl (c1 int); 1774ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY; 1775CREATE POLICY p1 ON rls_tbl USING (c1 > 5); 1776CREATE POLICY p2 ON rls_tbl FOR SELECT USING (c1 <= 3); 1777CREATE POLICY p3 ON rls_tbl FOR UPDATE USING (c1 <= 3) WITH CHECK (c1 > 5); 1778CREATE POLICY p4 ON rls_tbl FOR DELETE USING (c1 <= 3); 1779 1780CREATE TABLE rls_tbl_force (c1 int); 1781ALTER TABLE rls_tbl_force ENABLE ROW LEVEL SECURITY; 1782ALTER TABLE rls_tbl_force FORCE ROW LEVEL SECURITY; 1783CREATE POLICY p1 ON rls_tbl_force USING (c1 = 5) WITH CHECK (c1 < 5); 1784CREATE POLICY p2 ON rls_tbl_force FOR SELECT USING (c1 = 8); 1785CREATE POLICY p3 ON rls_tbl_force FOR UPDATE USING (c1 = 8) WITH CHECK (c1 >= 5); 1786CREATE POLICY p4 ON rls_tbl_force FOR DELETE USING (c1 = 8); 1787