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