1-- 2-- Test of Row-level security feature 3-- 4-- Clean up in case a prior regression run failed 5-- Suppress NOTICE messages when users/groups don't exist 6SET client_min_messages TO 'warning'; 7DROP USER IF EXISTS regress_rls_alice; 8DROP USER IF EXISTS regress_rls_bob; 9DROP USER IF EXISTS regress_rls_carol; 10DROP USER IF EXISTS regress_rls_dave; 11DROP USER IF EXISTS regress_rls_exempt_user; 12DROP ROLE IF EXISTS regress_rls_group1; 13DROP ROLE IF EXISTS regress_rls_group2; 14DROP SCHEMA IF EXISTS regress_rls_schema CASCADE; 15RESET client_min_messages; 16-- initial setup 17CREATE USER regress_rls_alice NOLOGIN; 18CREATE USER regress_rls_bob NOLOGIN; 19CREATE USER regress_rls_carol NOLOGIN; 20CREATE USER regress_rls_dave NOLOGIN; 21CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN; 22CREATE ROLE regress_rls_group1 NOLOGIN; 23CREATE ROLE regress_rls_group2 NOLOGIN; 24GRANT regress_rls_group1 TO regress_rls_bob; 25GRANT regress_rls_group2 TO regress_rls_carol; 26CREATE SCHEMA regress_rls_schema; 27GRANT ALL ON SCHEMA regress_rls_schema to public; 28SET search_path = regress_rls_schema; 29-- setup of malicious function 30CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool 31 COST 0.0000001 LANGUAGE plpgsql 32 AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END'; 33GRANT EXECUTE ON FUNCTION f_leak(text) TO public; 34-- BASIC Row-Level Security Scenario 35SET SESSION AUTHORIZATION regress_rls_alice; 36CREATE TABLE uaccount ( 37 pguser name primary key, 38 seclv int 39); 40GRANT SELECT ON uaccount TO public; 41INSERT INTO uaccount VALUES 42 ('regress_rls_alice', 99), 43 ('regress_rls_bob', 1), 44 ('regress_rls_carol', 2), 45 ('regress_rls_dave', 3); 46CREATE TABLE category ( 47 cid int primary key, 48 cname text 49); 50GRANT ALL ON category TO public; 51INSERT INTO category VALUES 52 (11, 'novel'), 53 (22, 'science fiction'), 54 (33, 'technology'), 55 (44, 'manga'); 56CREATE TABLE document ( 57 did int primary key, 58 cid int references category(cid), 59 dlevel int not null, 60 dauthor name, 61 dtitle text 62); 63GRANT ALL ON document TO public; 64INSERT INTO document VALUES 65 ( 1, 11, 1, 'regress_rls_bob', 'my first novel'), 66 ( 2, 11, 2, 'regress_rls_bob', 'my second novel'), 67 ( 3, 22, 2, 'regress_rls_bob', 'my science fiction'), 68 ( 4, 44, 1, 'regress_rls_bob', 'my first manga'), 69 ( 5, 44, 2, 'regress_rls_bob', 'my second manga'), 70 ( 6, 22, 1, 'regress_rls_carol', 'great science fiction'), 71 ( 7, 33, 2, 'regress_rls_carol', 'great technology book'), 72 ( 8, 44, 1, 'regress_rls_carol', 'great manga'), 73 ( 9, 22, 1, 'regress_rls_dave', 'awesome science fiction'), 74 (10, 33, 2, 'regress_rls_dave', 'awesome technology book'); 75ALTER TABLE document ENABLE ROW LEVEL SECURITY; 76-- user's security level must be higher than or equal to document's 77CREATE POLICY p1 ON document AS PERMISSIVE 78 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); 79-- try to create a policy of bogus type 80CREATE POLICY p1 ON document AS UGLY 81 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); 82ERROR: unrecognized row security option "ugly" 83LINE 1: CREATE POLICY p1 ON document AS UGLY 84 ^ 85HINT: Only PERMISSIVE or RESTRICTIVE policies are supported currently. 86-- but Dave isn't allowed to anything at cid 50 or above 87-- this is to make sure that we sort the policies by name first 88-- when applying WITH CHECK, a later INSERT by Dave should fail due 89-- to p1r first 90CREATE POLICY p2r ON document AS RESTRICTIVE TO regress_rls_dave 91 USING (cid <> 44 AND cid < 50); 92-- and Dave isn't allowed to see manga documents 93CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave 94 USING (cid <> 44); 95\dp 96 Access privileges 97 Schema | Name | Type | Access privileges | Column privileges | Policies 98--------------------+----------+-------+---------------------------------------------+-------------------+-------------------------------------------- 99 regress_rls_schema | category | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | 100 | | | =arwdDxt/regress_rls_alice | | 101 regress_rls_schema | document | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p1: + 102 | | | =arwdDxt/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv + 103 | | | | | FROM uaccount + 104 | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+ 105 | | | | | p2r (RESTRICTIVE): + 106 | | | | | (u): ((cid <> 44) AND (cid < 50)) + 107 | | | | | to: regress_rls_dave + 108 | | | | | p1r (RESTRICTIVE): + 109 | | | | | (u): (cid <> 44) + 110 | | | | | to: regress_rls_dave 111 regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | 112 | | | =r/regress_rls_alice | | 113(3 rows) 114 115\d document 116 Table "regress_rls_schema.document" 117 Column | Type | Collation | Nullable | Default 118---------+---------+-----------+----------+--------- 119 did | integer | | not null | 120 cid | integer | | | 121 dlevel | integer | | not null | 122 dauthor | name | | | 123 dtitle | text | | | 124Indexes: 125 "document_pkey" PRIMARY KEY, btree (did) 126Foreign-key constraints: 127 "document_cid_fkey" FOREIGN KEY (cid) REFERENCES category(cid) 128Policies: 129 POLICY "p1" 130 USING ((dlevel <= ( SELECT uaccount.seclv 131 FROM uaccount 132 WHERE (uaccount.pguser = CURRENT_USER)))) 133 POLICY "p1r" AS RESTRICTIVE 134 TO regress_rls_dave 135 USING ((cid <> 44)) 136 POLICY "p2r" AS RESTRICTIVE 137 TO regress_rls_dave 138 USING (((cid <> 44) AND (cid < 50))) 139 140SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename = 'document' ORDER BY policyname; 141 schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check 142--------------------+-----------+------------+-------------+--------------------+-----+--------------------------------------------+------------ 143 regress_rls_schema | document | p1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +| 144 | | | | | | FROM uaccount +| 145 | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) | 146 regress_rls_schema | document | p1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid <> 44) | 147 regress_rls_schema | document | p2r | RESTRICTIVE | {regress_rls_dave} | ALL | ((cid <> 44) AND (cid < 50)) | 148(3 rows) 149 150-- viewpoint from regress_rls_bob 151SET SESSION AUTHORIZATION regress_rls_bob; 152SET row_security TO ON; 153SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 154NOTICE: f_leak => my first novel 155NOTICE: f_leak => my first manga 156NOTICE: f_leak => great science fiction 157NOTICE: f_leak => great manga 158NOTICE: f_leak => awesome science fiction 159 did | cid | dlevel | dauthor | dtitle 160-----+-----+--------+-------------------+------------------------- 161 1 | 11 | 1 | regress_rls_bob | my first novel 162 4 | 44 | 1 | regress_rls_bob | my first manga 163 6 | 22 | 1 | regress_rls_carol | great science fiction 164 8 | 44 | 1 | regress_rls_carol | great manga 165 9 | 22 | 1 | regress_rls_dave | awesome science fiction 166(5 rows) 167 168SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; 169NOTICE: f_leak => my first novel 170NOTICE: f_leak => my first manga 171NOTICE: f_leak => great science fiction 172NOTICE: f_leak => great manga 173NOTICE: f_leak => awesome science fiction 174 cid | did | dlevel | dauthor | dtitle | cname 175-----+-----+--------+-------------------+-------------------------+----------------- 176 11 | 1 | 1 | regress_rls_bob | my first novel | novel 177 44 | 4 | 1 | regress_rls_bob | my first manga | manga 178 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction 179 44 | 8 | 1 | regress_rls_carol | great manga | manga 180 22 | 9 | 1 | regress_rls_dave | awesome science fiction | science fiction 181(5 rows) 182 183-- try a sampled version 184SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0) 185 WHERE f_leak(dtitle) ORDER BY did; 186NOTICE: f_leak => my first manga 187NOTICE: f_leak => great science fiction 188NOTICE: f_leak => great manga 189NOTICE: f_leak => awesome science fiction 190 did | cid | dlevel | dauthor | dtitle 191-----+-----+--------+-------------------+------------------------- 192 4 | 44 | 1 | regress_rls_bob | my first manga 193 6 | 22 | 1 | regress_rls_carol | great science fiction 194 8 | 44 | 1 | regress_rls_carol | great manga 195 9 | 22 | 1 | regress_rls_dave | awesome science fiction 196(4 rows) 197 198-- viewpoint from regress_rls_carol 199SET SESSION AUTHORIZATION regress_rls_carol; 200SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 201NOTICE: f_leak => my first novel 202NOTICE: f_leak => my second novel 203NOTICE: f_leak => my science fiction 204NOTICE: f_leak => my first manga 205NOTICE: f_leak => my second manga 206NOTICE: f_leak => great science fiction 207NOTICE: f_leak => great technology book 208NOTICE: f_leak => great manga 209NOTICE: f_leak => awesome science fiction 210NOTICE: f_leak => awesome technology book 211 did | cid | dlevel | dauthor | dtitle 212-----+-----+--------+-------------------+------------------------- 213 1 | 11 | 1 | regress_rls_bob | my first novel 214 2 | 11 | 2 | regress_rls_bob | my second novel 215 3 | 22 | 2 | regress_rls_bob | my science fiction 216 4 | 44 | 1 | regress_rls_bob | my first manga 217 5 | 44 | 2 | regress_rls_bob | my second manga 218 6 | 22 | 1 | regress_rls_carol | great science fiction 219 7 | 33 | 2 | regress_rls_carol | great technology book 220 8 | 44 | 1 | regress_rls_carol | great manga 221 9 | 22 | 1 | regress_rls_dave | awesome science fiction 222 10 | 33 | 2 | regress_rls_dave | awesome technology book 223(10 rows) 224 225SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; 226NOTICE: f_leak => my first novel 227NOTICE: f_leak => my second novel 228NOTICE: f_leak => my science fiction 229NOTICE: f_leak => my first manga 230NOTICE: f_leak => my second manga 231NOTICE: f_leak => great science fiction 232NOTICE: f_leak => great technology book 233NOTICE: f_leak => great manga 234NOTICE: f_leak => awesome science fiction 235NOTICE: f_leak => awesome technology book 236 cid | did | dlevel | dauthor | dtitle | cname 237-----+-----+--------+-------------------+-------------------------+----------------- 238 11 | 1 | 1 | regress_rls_bob | my first novel | novel 239 11 | 2 | 2 | regress_rls_bob | my second novel | novel 240 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction 241 44 | 4 | 1 | regress_rls_bob | my first manga | manga 242 44 | 5 | 2 | regress_rls_bob | my second manga | manga 243 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction 244 33 | 7 | 2 | regress_rls_carol | great technology book | technology 245 44 | 8 | 1 | regress_rls_carol | great manga | manga 246 22 | 9 | 1 | regress_rls_dave | awesome science fiction | science fiction 247 33 | 10 | 2 | regress_rls_dave | awesome technology book | technology 248(10 rows) 249 250-- try a sampled version 251SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0) 252 WHERE f_leak(dtitle) ORDER BY did; 253NOTICE: f_leak => my first manga 254NOTICE: f_leak => my second manga 255NOTICE: f_leak => great science fiction 256NOTICE: f_leak => great manga 257NOTICE: f_leak => awesome science fiction 258 did | cid | dlevel | dauthor | dtitle 259-----+-----+--------+-------------------+------------------------- 260 4 | 44 | 1 | regress_rls_bob | my first manga 261 5 | 44 | 2 | regress_rls_bob | my second manga 262 6 | 22 | 1 | regress_rls_carol | great science fiction 263 8 | 44 | 1 | regress_rls_carol | great manga 264 9 | 22 | 1 | regress_rls_dave | awesome science fiction 265(5 rows) 266 267EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); 268 QUERY PLAN 269---------------------------------------------------- 270 Seq Scan on document 271 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 272 InitPlan 1 (returns $0) 273 -> Index Scan using uaccount_pkey on uaccount 274 Index Cond: (pguser = CURRENT_USER) 275(5 rows) 276 277EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); 278 QUERY PLAN 279----------------------------------------------------------- 280 Hash Join 281 Hash Cond: (category.cid = document.cid) 282 InitPlan 1 (returns $0) 283 -> Index Scan using uaccount_pkey on uaccount 284 Index Cond: (pguser = CURRENT_USER) 285 -> Seq Scan on category 286 -> Hash 287 -> Seq Scan on document 288 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 289(9 rows) 290 291-- viewpoint from regress_rls_dave 292SET SESSION AUTHORIZATION regress_rls_dave; 293SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 294NOTICE: f_leak => my first novel 295NOTICE: f_leak => my second novel 296NOTICE: f_leak => my science fiction 297NOTICE: f_leak => great science fiction 298NOTICE: f_leak => great technology book 299NOTICE: f_leak => awesome science fiction 300NOTICE: f_leak => awesome technology book 301 did | cid | dlevel | dauthor | dtitle 302-----+-----+--------+-------------------+------------------------- 303 1 | 11 | 1 | regress_rls_bob | my first novel 304 2 | 11 | 2 | regress_rls_bob | my second novel 305 3 | 22 | 2 | regress_rls_bob | my science fiction 306 6 | 22 | 1 | regress_rls_carol | great science fiction 307 7 | 33 | 2 | regress_rls_carol | great technology book 308 9 | 22 | 1 | regress_rls_dave | awesome science fiction 309 10 | 33 | 2 | regress_rls_dave | awesome technology book 310(7 rows) 311 312SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; 313NOTICE: f_leak => my first novel 314NOTICE: f_leak => my second novel 315NOTICE: f_leak => my science fiction 316NOTICE: f_leak => great science fiction 317NOTICE: f_leak => great technology book 318NOTICE: f_leak => awesome science fiction 319NOTICE: f_leak => awesome technology book 320 cid | did | dlevel | dauthor | dtitle | cname 321-----+-----+--------+-------------------+-------------------------+----------------- 322 11 | 1 | 1 | regress_rls_bob | my first novel | novel 323 11 | 2 | 2 | regress_rls_bob | my second novel | novel 324 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction 325 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction 326 33 | 7 | 2 | regress_rls_carol | great technology book | technology 327 22 | 9 | 1 | regress_rls_dave | awesome science fiction | science fiction 328 33 | 10 | 2 | regress_rls_dave | awesome technology book | technology 329(7 rows) 330 331EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); 332 QUERY PLAN 333---------------------------------------------------------------------------------------------- 334 Seq Scan on document 335 Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= $0) AND f_leak(dtitle)) 336 InitPlan 1 (returns $0) 337 -> Index Scan using uaccount_pkey on uaccount 338 Index Cond: (pguser = CURRENT_USER) 339(5 rows) 340 341EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); 342 QUERY PLAN 343---------------------------------------------------------------------------------------------------------- 344 Hash Join 345 Hash Cond: (category.cid = document.cid) 346 InitPlan 1 (returns $0) 347 -> Index Scan using uaccount_pkey on uaccount 348 Index Cond: (pguser = CURRENT_USER) 349 -> Seq Scan on category 350 -> Hash 351 -> Seq Scan on document 352 Filter: ((cid <> 44) AND (cid <> 44) AND (cid < 50) AND (dlevel <= $0) AND f_leak(dtitle)) 353(9 rows) 354 355-- 44 would technically fail for both p2r and p1r, but we should get an error 356-- back from p1r for this because it sorts first 357INSERT INTO document VALUES (100, 44, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail 358ERROR: new row violates row-level security policy "p1r" for table "document" 359-- Just to see a p2r error 360INSERT INTO document VALUES (100, 55, 1, 'regress_rls_dave', 'testing sorting of policies'); -- fail 361ERROR: new row violates row-level security policy "p2r" for table "document" 362-- only owner can change policies 363ALTER POLICY p1 ON document USING (true); --fail 364ERROR: must be owner of relation document 365DROP POLICY p1 ON document; --fail 366ERROR: must be owner of relation document 367SET SESSION AUTHORIZATION regress_rls_alice; 368ALTER POLICY p1 ON document USING (dauthor = current_user); 369-- viewpoint from regress_rls_bob again 370SET SESSION AUTHORIZATION regress_rls_bob; 371SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 372NOTICE: f_leak => my first novel 373NOTICE: f_leak => my second novel 374NOTICE: f_leak => my science fiction 375NOTICE: f_leak => my first manga 376NOTICE: f_leak => my second manga 377 did | cid | dlevel | dauthor | dtitle 378-----+-----+--------+-----------------+-------------------- 379 1 | 11 | 1 | regress_rls_bob | my first novel 380 2 | 11 | 2 | regress_rls_bob | my second novel 381 3 | 22 | 2 | regress_rls_bob | my science fiction 382 4 | 44 | 1 | regress_rls_bob | my first manga 383 5 | 44 | 2 | regress_rls_bob | my second manga 384(5 rows) 385 386SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did; 387NOTICE: f_leak => my first novel 388NOTICE: f_leak => my second novel 389NOTICE: f_leak => my science fiction 390NOTICE: f_leak => my first manga 391NOTICE: f_leak => my second manga 392 cid | did | dlevel | dauthor | dtitle | cname 393-----+-----+--------+-----------------+--------------------+----------------- 394 11 | 1 | 1 | regress_rls_bob | my first novel | novel 395 11 | 2 | 2 | regress_rls_bob | my second novel | novel 396 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction 397 44 | 4 | 1 | regress_rls_bob | my first manga | manga 398 44 | 5 | 2 | regress_rls_bob | my second manga | manga 399(5 rows) 400 401-- viewpoint from rls_regres_carol again 402SET SESSION AUTHORIZATION regress_rls_carol; 403SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; 404NOTICE: f_leak => great science fiction 405NOTICE: f_leak => great technology book 406NOTICE: f_leak => great manga 407 did | cid | dlevel | dauthor | dtitle 408-----+-----+--------+-------------------+----------------------- 409 6 | 22 | 1 | regress_rls_carol | great science fiction 410 7 | 33 | 2 | regress_rls_carol | great technology book 411 8 | 44 | 1 | regress_rls_carol | great manga 412(3 rows) 413 414SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER by did; 415NOTICE: f_leak => great science fiction 416NOTICE: f_leak => great technology book 417NOTICE: f_leak => great manga 418 cid | did | dlevel | dauthor | dtitle | cname 419-----+-----+--------+-------------------+-----------------------+----------------- 420 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction 421 33 | 7 | 2 | regress_rls_carol | great technology book | technology 422 44 | 8 | 1 | regress_rls_carol | great manga | manga 423(3 rows) 424 425EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle); 426 QUERY PLAN 427--------------------------------------------------------- 428 Seq Scan on document 429 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle)) 430(2 rows) 431 432EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); 433 QUERY PLAN 434--------------------------------------------------------------- 435 Nested Loop 436 -> Seq Scan on document 437 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle)) 438 -> Index Scan using category_pkey on category 439 Index Cond: (cid = document.cid) 440(5 rows) 441 442-- interaction of FK/PK constraints 443SET SESSION AUTHORIZATION regress_rls_alice; 444CREATE POLICY p2 ON category 445 USING (CASE WHEN current_user = 'regress_rls_bob' THEN cid IN (11, 33) 446 WHEN current_user = 'regress_rls_carol' THEN cid IN (22, 44) 447 ELSE false END); 448ALTER TABLE category ENABLE ROW LEVEL SECURITY; 449-- cannot delete PK referenced by invisible FK 450SET SESSION AUTHORIZATION regress_rls_bob; 451SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid; 452 did | cid | dlevel | dauthor | dtitle | cid | cname 453-----+-----+--------+-----------------+--------------------+-----+------------ 454 1 | 11 | 1 | regress_rls_bob | my first novel | 11 | novel 455 2 | 11 | 2 | regress_rls_bob | my second novel | 11 | novel 456 3 | 22 | 2 | regress_rls_bob | my science fiction | | 457 4 | 44 | 1 | regress_rls_bob | my first manga | | 458 5 | 44 | 2 | regress_rls_bob | my second manga | | 459 | | | | | 33 | technology 460(6 rows) 461 462DELETE FROM category WHERE cid = 33; -- fails with FK violation 463ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document" 464DETAIL: Key is still referenced from table "document". 465-- can insert FK referencing invisible PK 466SET SESSION AUTHORIZATION regress_rls_carol; 467SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid ORDER BY d.did, c.cid; 468 did | cid | dlevel | dauthor | dtitle | cid | cname 469-----+-----+--------+-------------------+-----------------------+-----+----------------- 470 6 | 22 | 1 | regress_rls_carol | great science fiction | 22 | science fiction 471 7 | 33 | 2 | regress_rls_carol | great technology book | | 472 8 | 44 | 1 | regress_rls_carol | great manga | 44 | manga 473(3 rows) 474 475INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge'); 476-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row 477SET SESSION AUTHORIZATION regress_rls_bob; 478INSERT 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 479ERROR: duplicate key value violates unique constraint "document_pkey" 480SELECT * FROM document WHERE did = 8; -- and confirm we can't see it 481 did | cid | dlevel | dauthor | dtitle 482-----+-----+--------+---------+-------- 483(0 rows) 484 485-- RLS policies are checked before constraints 486INSERT INTO document VALUES (8, 44, 1, 'regress_rls_carol', 'my third manga'); -- Should fail with RLS check violation, not duplicate key violation 487ERROR: new row violates row-level security policy for table "document" 488UPDATE document SET did = 8, dauthor = 'regress_rls_carol' WHERE did = 5; -- Should fail with RLS check violation, not duplicate key violation 489ERROR: new row violates row-level security policy for table "document" 490-- database superuser does bypass RLS policy when enabled 491RESET SESSION AUTHORIZATION; 492SET row_security TO ON; 493SELECT * FROM document; 494 did | cid | dlevel | dauthor | dtitle 495-----+-----+--------+-------------------+------------------------- 496 1 | 11 | 1 | regress_rls_bob | my first novel 497 2 | 11 | 2 | regress_rls_bob | my second novel 498 3 | 22 | 2 | regress_rls_bob | my science fiction 499 4 | 44 | 1 | regress_rls_bob | my first manga 500 5 | 44 | 2 | regress_rls_bob | my second manga 501 6 | 22 | 1 | regress_rls_carol | great science fiction 502 7 | 33 | 2 | regress_rls_carol | great technology book 503 8 | 44 | 1 | regress_rls_carol | great manga 504 9 | 22 | 1 | regress_rls_dave | awesome science fiction 505 10 | 33 | 2 | regress_rls_dave | awesome technology book 506 11 | 33 | 1 | regress_rls_carol | hoge 507(11 rows) 508 509SELECT * FROM category; 510 cid | cname 511-----+----------------- 512 11 | novel 513 22 | science fiction 514 33 | technology 515 44 | manga 516(4 rows) 517 518-- database superuser does bypass RLS policy when disabled 519RESET SESSION AUTHORIZATION; 520SET row_security TO OFF; 521SELECT * FROM document; 522 did | cid | dlevel | dauthor | dtitle 523-----+-----+--------+-------------------+------------------------- 524 1 | 11 | 1 | regress_rls_bob | my first novel 525 2 | 11 | 2 | regress_rls_bob | my second novel 526 3 | 22 | 2 | regress_rls_bob | my science fiction 527 4 | 44 | 1 | regress_rls_bob | my first manga 528 5 | 44 | 2 | regress_rls_bob | my second manga 529 6 | 22 | 1 | regress_rls_carol | great science fiction 530 7 | 33 | 2 | regress_rls_carol | great technology book 531 8 | 44 | 1 | regress_rls_carol | great manga 532 9 | 22 | 1 | regress_rls_dave | awesome science fiction 533 10 | 33 | 2 | regress_rls_dave | awesome technology book 534 11 | 33 | 1 | regress_rls_carol | hoge 535(11 rows) 536 537SELECT * FROM category; 538 cid | cname 539-----+----------------- 540 11 | novel 541 22 | science fiction 542 33 | technology 543 44 | manga 544(4 rows) 545 546-- database non-superuser with bypass privilege can bypass RLS policy when disabled 547SET SESSION AUTHORIZATION regress_rls_exempt_user; 548SET row_security TO OFF; 549SELECT * FROM document; 550 did | cid | dlevel | dauthor | dtitle 551-----+-----+--------+-------------------+------------------------- 552 1 | 11 | 1 | regress_rls_bob | my first novel 553 2 | 11 | 2 | regress_rls_bob | my second novel 554 3 | 22 | 2 | regress_rls_bob | my science fiction 555 4 | 44 | 1 | regress_rls_bob | my first manga 556 5 | 44 | 2 | regress_rls_bob | my second manga 557 6 | 22 | 1 | regress_rls_carol | great science fiction 558 7 | 33 | 2 | regress_rls_carol | great technology book 559 8 | 44 | 1 | regress_rls_carol | great manga 560 9 | 22 | 1 | regress_rls_dave | awesome science fiction 561 10 | 33 | 2 | regress_rls_dave | awesome technology book 562 11 | 33 | 1 | regress_rls_carol | hoge 563(11 rows) 564 565SELECT * FROM category; 566 cid | cname 567-----+----------------- 568 11 | novel 569 22 | science fiction 570 33 | technology 571 44 | manga 572(4 rows) 573 574-- RLS policy does not apply to table owner when RLS enabled. 575SET SESSION AUTHORIZATION regress_rls_alice; 576SET row_security TO ON; 577SELECT * FROM document; 578 did | cid | dlevel | dauthor | dtitle 579-----+-----+--------+-------------------+------------------------- 580 1 | 11 | 1 | regress_rls_bob | my first novel 581 2 | 11 | 2 | regress_rls_bob | my second novel 582 3 | 22 | 2 | regress_rls_bob | my science fiction 583 4 | 44 | 1 | regress_rls_bob | my first manga 584 5 | 44 | 2 | regress_rls_bob | my second manga 585 6 | 22 | 1 | regress_rls_carol | great science fiction 586 7 | 33 | 2 | regress_rls_carol | great technology book 587 8 | 44 | 1 | regress_rls_carol | great manga 588 9 | 22 | 1 | regress_rls_dave | awesome science fiction 589 10 | 33 | 2 | regress_rls_dave | awesome technology book 590 11 | 33 | 1 | regress_rls_carol | hoge 591(11 rows) 592 593SELECT * FROM category; 594 cid | cname 595-----+----------------- 596 11 | novel 597 22 | science fiction 598 33 | technology 599 44 | manga 600(4 rows) 601 602-- RLS policy does not apply to table owner when RLS disabled. 603SET SESSION AUTHORIZATION regress_rls_alice; 604SET row_security TO OFF; 605SELECT * FROM document; 606 did | cid | dlevel | dauthor | dtitle 607-----+-----+--------+-------------------+------------------------- 608 1 | 11 | 1 | regress_rls_bob | my first novel 609 2 | 11 | 2 | regress_rls_bob | my second novel 610 3 | 22 | 2 | regress_rls_bob | my science fiction 611 4 | 44 | 1 | regress_rls_bob | my first manga 612 5 | 44 | 2 | regress_rls_bob | my second manga 613 6 | 22 | 1 | regress_rls_carol | great science fiction 614 7 | 33 | 2 | regress_rls_carol | great technology book 615 8 | 44 | 1 | regress_rls_carol | great manga 616 9 | 22 | 1 | regress_rls_dave | awesome science fiction 617 10 | 33 | 2 | regress_rls_dave | awesome technology book 618 11 | 33 | 1 | regress_rls_carol | hoge 619(11 rows) 620 621SELECT * FROM category; 622 cid | cname 623-----+----------------- 624 11 | novel 625 22 | science fiction 626 33 | technology 627 44 | manga 628(4 rows) 629 630-- 631-- Table inheritance and RLS policy 632-- 633SET SESSION AUTHORIZATION regress_rls_alice; 634SET row_security TO ON; 635CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS; 636ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor 637GRANT ALL ON t1 TO public; 638COPY t1 FROM stdin WITH (oids); 639CREATE TABLE t2 (c float) INHERITS (t1); 640GRANT ALL ON t2 TO public; 641COPY t2 FROM stdin WITH (oids); 642CREATE TABLE t3 (c text, b text, a int) WITH OIDS; 643ALTER TABLE t3 INHERIT t1; 644GRANT ALL ON t3 TO public; 645COPY t3(a,b,c) FROM stdin WITH (oids); 646CREATE POLICY p1 ON t1 FOR ALL TO PUBLIC USING (a % 2 = 0); -- be even number 647CREATE POLICY p2 ON t2 FOR ALL TO PUBLIC USING (a % 2 = 1); -- be odd number 648ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; 649ALTER TABLE t2 ENABLE ROW LEVEL SECURITY; 650SET SESSION AUTHORIZATION regress_rls_bob; 651SELECT * FROM t1; 652 a | b 653---+----- 654 2 | bbb 655 4 | dad 656 2 | bcd 657 4 | def 658 2 | yyy 659(5 rows) 660 661EXPLAIN (COSTS OFF) SELECT * FROM t1; 662 QUERY PLAN 663------------------------------- 664 Append 665 -> Seq Scan on t1 666 Filter: ((a % 2) = 0) 667 -> Seq Scan on t2 668 Filter: ((a % 2) = 0) 669 -> Seq Scan on t3 670 Filter: ((a % 2) = 0) 671(7 rows) 672 673SELECT * FROM t1 WHERE f_leak(b); 674NOTICE: f_leak => bbb 675NOTICE: f_leak => dad 676NOTICE: f_leak => bcd 677NOTICE: f_leak => def 678NOTICE: f_leak => yyy 679 a | b 680---+----- 681 2 | bbb 682 4 | dad 683 2 | bcd 684 4 | def 685 2 | yyy 686(5 rows) 687 688EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); 689 QUERY PLAN 690----------------------------------------------- 691 Append 692 -> Seq Scan on t1 693 Filter: (((a % 2) = 0) AND f_leak(b)) 694 -> Seq Scan on t2 695 Filter: (((a % 2) = 0) AND f_leak(b)) 696 -> Seq Scan on t3 697 Filter: (((a % 2) = 0) AND f_leak(b)) 698(7 rows) 699 700-- reference to system column 701SELECT oid, * FROM t1; 702 oid | a | b 703-----+---+----- 704 102 | 2 | bbb 705 104 | 4 | dad 706 202 | 2 | bcd 707 204 | 4 | def 708 302 | 2 | yyy 709(5 rows) 710 711EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1; 712 QUERY PLAN 713------------------------------- 714 Append 715 -> Seq Scan on t1 716 Filter: ((a % 2) = 0) 717 -> Seq Scan on t2 718 Filter: ((a % 2) = 0) 719 -> Seq Scan on t3 720 Filter: ((a % 2) = 0) 721(7 rows) 722 723-- reference to whole-row reference 724SELECT *, t1 FROM t1; 725 a | b | t1 726---+-----+--------- 727 2 | bbb | (2,bbb) 728 4 | dad | (4,dad) 729 2 | bcd | (2,bcd) 730 4 | def | (4,def) 731 2 | yyy | (2,yyy) 732(5 rows) 733 734EXPLAIN (COSTS OFF) SELECT *, t1 FROM t1; 735 QUERY PLAN 736------------------------------- 737 Append 738 -> Seq Scan on t1 739 Filter: ((a % 2) = 0) 740 -> Seq Scan on t2 741 Filter: ((a % 2) = 0) 742 -> Seq Scan on t3 743 Filter: ((a % 2) = 0) 744(7 rows) 745 746-- for share/update lock 747SELECT * FROM t1 FOR SHARE; 748 a | b 749---+----- 750 2 | bbb 751 4 | dad 752 2 | bcd 753 4 | def 754 2 | yyy 755(5 rows) 756 757EXPLAIN (COSTS OFF) SELECT * FROM t1 FOR SHARE; 758 QUERY PLAN 759------------------------------------- 760 LockRows 761 -> Append 762 -> Seq Scan on t1 763 Filter: ((a % 2) = 0) 764 -> Seq Scan on t2 765 Filter: ((a % 2) = 0) 766 -> Seq Scan on t3 767 Filter: ((a % 2) = 0) 768(8 rows) 769 770SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; 771NOTICE: f_leak => bbb 772NOTICE: f_leak => dad 773NOTICE: f_leak => bcd 774NOTICE: f_leak => def 775NOTICE: f_leak => yyy 776 a | b 777---+----- 778 2 | bbb 779 4 | dad 780 2 | bcd 781 4 | def 782 2 | yyy 783(5 rows) 784 785EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; 786 QUERY PLAN 787----------------------------------------------------- 788 LockRows 789 -> Append 790 -> Seq Scan on t1 791 Filter: (((a % 2) = 0) AND f_leak(b)) 792 -> Seq Scan on t2 793 Filter: (((a % 2) = 0) AND f_leak(b)) 794 -> Seq Scan on t3 795 Filter: (((a % 2) = 0) AND f_leak(b)) 796(8 rows) 797 798-- union all query 799SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3; 800 a | b | oid 801---+-----+----- 802 1 | abc | 201 803 3 | cde | 203 804 1 | xxx | 301 805 2 | yyy | 302 806 3 | zzz | 303 807(5 rows) 808 809EXPLAIN (COSTS OFF) SELECT a, b, oid FROM t2 UNION ALL SELECT a, b, oid FROM t3; 810 QUERY PLAN 811------------------------------- 812 Append 813 -> Seq Scan on t2 814 Filter: ((a % 2) = 1) 815 -> Seq Scan on t3 816(4 rows) 817 818-- superuser is allowed to bypass RLS checks 819RESET SESSION AUTHORIZATION; 820SET row_security TO OFF; 821SELECT * FROM t1 WHERE f_leak(b); 822NOTICE: f_leak => aba 823NOTICE: f_leak => bbb 824NOTICE: f_leak => ccc 825NOTICE: f_leak => dad 826NOTICE: f_leak => abc 827NOTICE: f_leak => bcd 828NOTICE: f_leak => cde 829NOTICE: f_leak => def 830NOTICE: f_leak => xxx 831NOTICE: f_leak => yyy 832NOTICE: f_leak => zzz 833 a | b 834---+----- 835 1 | aba 836 2 | bbb 837 3 | ccc 838 4 | dad 839 1 | abc 840 2 | bcd 841 3 | cde 842 4 | def 843 1 | xxx 844 2 | yyy 845 3 | zzz 846(11 rows) 847 848EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); 849 QUERY PLAN 850--------------------------- 851 Append 852 -> Seq Scan on t1 853 Filter: f_leak(b) 854 -> Seq Scan on t2 855 Filter: f_leak(b) 856 -> Seq Scan on t3 857 Filter: f_leak(b) 858(7 rows) 859 860-- non-superuser with bypass privilege can bypass RLS policy when disabled 861SET SESSION AUTHORIZATION regress_rls_exempt_user; 862SET row_security TO OFF; 863SELECT * FROM t1 WHERE f_leak(b); 864NOTICE: f_leak => aba 865NOTICE: f_leak => bbb 866NOTICE: f_leak => ccc 867NOTICE: f_leak => dad 868NOTICE: f_leak => abc 869NOTICE: f_leak => bcd 870NOTICE: f_leak => cde 871NOTICE: f_leak => def 872NOTICE: f_leak => xxx 873NOTICE: f_leak => yyy 874NOTICE: f_leak => zzz 875 a | b 876---+----- 877 1 | aba 878 2 | bbb 879 3 | ccc 880 4 | dad 881 1 | abc 882 2 | bcd 883 3 | cde 884 4 | def 885 1 | xxx 886 2 | yyy 887 3 | zzz 888(11 rows) 889 890EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); 891 QUERY PLAN 892--------------------------- 893 Append 894 -> Seq Scan on t1 895 Filter: f_leak(b) 896 -> Seq Scan on t2 897 Filter: f_leak(b) 898 -> Seq Scan on t3 899 Filter: f_leak(b) 900(7 rows) 901 902-- 903-- Partitioned Tables 904-- 905SET SESSION AUTHORIZATION regress_rls_alice; 906CREATE TABLE part_document ( 907 did int, 908 cid int, 909 dlevel int not null, 910 dauthor name, 911 dtitle text 912) PARTITION BY RANGE (cid); 913GRANT ALL ON part_document TO public; 914-- Create partitions for document categories 915CREATE TABLE part_document_fiction PARTITION OF part_document FOR VALUES FROM (11) to (12); 916CREATE TABLE part_document_satire PARTITION OF part_document FOR VALUES FROM (55) to (56); 917CREATE TABLE part_document_nonfiction PARTITION OF part_document FOR VALUES FROM (99) to (100); 918GRANT ALL ON part_document_fiction TO public; 919GRANT ALL ON part_document_satire TO public; 920GRANT ALL ON part_document_nonfiction TO public; 921INSERT INTO part_document VALUES 922 ( 1, 11, 1, 'regress_rls_bob', 'my first novel'), 923 ( 2, 11, 2, 'regress_rls_bob', 'my second novel'), 924 ( 3, 99, 2, 'regress_rls_bob', 'my science textbook'), 925 ( 4, 55, 1, 'regress_rls_bob', 'my first satire'), 926 ( 5, 99, 2, 'regress_rls_bob', 'my history book'), 927 ( 6, 11, 1, 'regress_rls_carol', 'great science fiction'), 928 ( 7, 99, 2, 'regress_rls_carol', 'great technology book'), 929 ( 8, 55, 2, 'regress_rls_carol', 'great satire'), 930 ( 9, 11, 1, 'regress_rls_dave', 'awesome science fiction'), 931 (10, 99, 2, 'regress_rls_dave', 'awesome technology book'); 932ALTER TABLE part_document ENABLE ROW LEVEL SECURITY; 933-- Create policy on parent 934-- user's security level must be higher than or equal to document's 935CREATE POLICY pp1 ON part_document AS PERMISSIVE 936 USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user)); 937-- Dave is only allowed to see cid < 55 938CREATE POLICY pp1r ON part_document AS RESTRICTIVE TO regress_rls_dave 939 USING (cid < 55); 940\d+ part_document 941 Table "regress_rls_schema.part_document" 942 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 943---------+---------+-----------+----------+---------+----------+--------------+------------- 944 did | integer | | | | plain | | 945 cid | integer | | | | plain | | 946 dlevel | integer | | not null | | plain | | 947 dauthor | name | | | | plain | | 948 dtitle | text | | | | extended | | 949Partition key: RANGE (cid) 950Policies: 951 POLICY "pp1" 952 USING ((dlevel <= ( SELECT uaccount.seclv 953 FROM uaccount 954 WHERE (uaccount.pguser = CURRENT_USER)))) 955 POLICY "pp1r" AS RESTRICTIVE 956 TO regress_rls_dave 957 USING ((cid < 55)) 958Partitions: part_document_fiction FOR VALUES FROM (11) TO (12), 959 part_document_nonfiction FOR VALUES FROM (99) TO (100), 960 part_document_satire FOR VALUES FROM (55) TO (56) 961 962SELECT * FROM pg_policies WHERE schemaname = 'regress_rls_schema' AND tablename like '%part_document%' ORDER BY policyname; 963 schemaname | tablename | policyname | permissive | roles | cmd | qual | with_check 964--------------------+---------------+------------+-------------+--------------------+-----+--------------------------------------------+------------ 965 regress_rls_schema | part_document | pp1 | PERMISSIVE | {public} | ALL | (dlevel <= ( SELECT uaccount.seclv +| 966 | | | | | | FROM uaccount +| 967 | | | | | | WHERE (uaccount.pguser = CURRENT_USER))) | 968 regress_rls_schema | part_document | pp1r | RESTRICTIVE | {regress_rls_dave} | ALL | (cid < 55) | 969(2 rows) 970 971-- viewpoint from regress_rls_bob 972SET SESSION AUTHORIZATION regress_rls_bob; 973SET row_security TO ON; 974SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 975NOTICE: f_leak => my first novel 976NOTICE: f_leak => great science fiction 977NOTICE: f_leak => awesome science fiction 978NOTICE: f_leak => my first satire 979 did | cid | dlevel | dauthor | dtitle 980-----+-----+--------+-------------------+------------------------- 981 1 | 11 | 1 | regress_rls_bob | my first novel 982 4 | 55 | 1 | regress_rls_bob | my first satire 983 6 | 11 | 1 | regress_rls_carol | great science fiction 984 9 | 11 | 1 | regress_rls_dave | awesome science fiction 985(4 rows) 986 987EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 988 QUERY PLAN 989----------------------------------------------------- 990 Append 991 InitPlan 1 (returns $0) 992 -> Index Scan using uaccount_pkey on uaccount 993 Index Cond: (pguser = CURRENT_USER) 994 -> Seq Scan on part_document_fiction 995 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 996 -> Seq Scan on part_document_satire 997 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 998 -> Seq Scan on part_document_nonfiction 999 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 1000(10 rows) 1001 1002-- viewpoint from regress_rls_carol 1003SET SESSION AUTHORIZATION regress_rls_carol; 1004SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 1005NOTICE: f_leak => my first novel 1006NOTICE: f_leak => my second novel 1007NOTICE: f_leak => great science fiction 1008NOTICE: f_leak => awesome science fiction 1009NOTICE: f_leak => my first satire 1010NOTICE: f_leak => great satire 1011NOTICE: f_leak => my science textbook 1012NOTICE: f_leak => my history book 1013NOTICE: f_leak => great technology book 1014NOTICE: f_leak => awesome technology book 1015 did | cid | dlevel | dauthor | dtitle 1016-----+-----+--------+-------------------+------------------------- 1017 1 | 11 | 1 | regress_rls_bob | my first novel 1018 2 | 11 | 2 | regress_rls_bob | my second novel 1019 3 | 99 | 2 | regress_rls_bob | my science textbook 1020 4 | 55 | 1 | regress_rls_bob | my first satire 1021 5 | 99 | 2 | regress_rls_bob | my history book 1022 6 | 11 | 1 | regress_rls_carol | great science fiction 1023 7 | 99 | 2 | regress_rls_carol | great technology book 1024 8 | 55 | 2 | regress_rls_carol | great satire 1025 9 | 11 | 1 | regress_rls_dave | awesome science fiction 1026 10 | 99 | 2 | regress_rls_dave | awesome technology book 1027(10 rows) 1028 1029EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 1030 QUERY PLAN 1031----------------------------------------------------- 1032 Append 1033 InitPlan 1 (returns $0) 1034 -> Index Scan using uaccount_pkey on uaccount 1035 Index Cond: (pguser = CURRENT_USER) 1036 -> Seq Scan on part_document_fiction 1037 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 1038 -> Seq Scan on part_document_satire 1039 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 1040 -> Seq Scan on part_document_nonfiction 1041 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 1042(10 rows) 1043 1044-- viewpoint from regress_rls_dave 1045SET SESSION AUTHORIZATION regress_rls_dave; 1046SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 1047NOTICE: f_leak => my first novel 1048NOTICE: f_leak => my second novel 1049NOTICE: f_leak => great science fiction 1050NOTICE: f_leak => awesome science fiction 1051 did | cid | dlevel | dauthor | dtitle 1052-----+-----+--------+-------------------+------------------------- 1053 1 | 11 | 1 | regress_rls_bob | my first novel 1054 2 | 11 | 2 | regress_rls_bob | my second novel 1055 6 | 11 | 1 | regress_rls_carol | great science fiction 1056 9 | 11 | 1 | regress_rls_dave | awesome science fiction 1057(4 rows) 1058 1059EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 1060 QUERY PLAN 1061-------------------------------------------------------------------- 1062 Append 1063 InitPlan 1 (returns $0) 1064 -> Index Scan using uaccount_pkey on uaccount 1065 Index Cond: (pguser = CURRENT_USER) 1066 -> Seq Scan on part_document_fiction 1067 Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle)) 1068(6 rows) 1069 1070-- pp1 ERROR 1071INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_dave', 'testing pp1'); -- fail 1072ERROR: new row violates row-level security policy for table "part_document" 1073-- pp1r ERROR 1074INSERT INTO part_document VALUES (100, 99, 1, 'regress_rls_dave', 'testing pp1r'); -- fail 1075ERROR: new row violates row-level security policy "pp1r" for table "part_document" 1076-- Show that RLS policy does not apply for direct inserts to children 1077-- This should fail with RLS POLICY pp1r violation. 1078INSERT INTO part_document VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail 1079ERROR: new row violates row-level security policy "pp1r" for table "part_document" 1080-- But this should succeed. 1081INSERT INTO part_document_satire VALUES (100, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- success 1082-- We still cannot see the row using the parent 1083SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 1084NOTICE: f_leak => my first novel 1085NOTICE: f_leak => my second novel 1086NOTICE: f_leak => great science fiction 1087NOTICE: f_leak => awesome science fiction 1088 did | cid | dlevel | dauthor | dtitle 1089-----+-----+--------+-------------------+------------------------- 1090 1 | 11 | 1 | regress_rls_bob | my first novel 1091 2 | 11 | 2 | regress_rls_bob | my second novel 1092 6 | 11 | 1 | regress_rls_carol | great science fiction 1093 9 | 11 | 1 | regress_rls_dave | awesome science fiction 1094(4 rows) 1095 1096-- But we can if we look directly 1097SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did; 1098NOTICE: f_leak => my first satire 1099NOTICE: f_leak => great satire 1100NOTICE: f_leak => testing RLS with partitions 1101 did | cid | dlevel | dauthor | dtitle 1102-----+-----+--------+-------------------+----------------------------- 1103 4 | 55 | 1 | regress_rls_bob | my first satire 1104 8 | 55 | 2 | regress_rls_carol | great satire 1105 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions 1106(3 rows) 1107 1108-- Turn on RLS and create policy on child to show RLS is checked before constraints 1109SET SESSION AUTHORIZATION regress_rls_alice; 1110ALTER TABLE part_document_satire ENABLE ROW LEVEL SECURITY; 1111CREATE POLICY pp3 ON part_document_satire AS RESTRICTIVE 1112 USING (cid < 55); 1113-- This should fail with RLS violation now. 1114SET SESSION AUTHORIZATION regress_rls_dave; 1115INSERT INTO part_document_satire VALUES (101, 55, 1, 'regress_rls_dave', 'testing RLS with partitions'); -- fail 1116ERROR: new row violates row-level security policy for table "part_document_satire" 1117-- And now we cannot see directly into the partition either, due to RLS 1118SELECT * FROM part_document_satire WHERE f_leak(dtitle) ORDER BY did; 1119 did | cid | dlevel | dauthor | dtitle 1120-----+-----+--------+---------+-------- 1121(0 rows) 1122 1123-- The parent looks same as before 1124-- viewpoint from regress_rls_dave 1125SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 1126NOTICE: f_leak => my first novel 1127NOTICE: f_leak => my second novel 1128NOTICE: f_leak => great science fiction 1129NOTICE: f_leak => awesome science fiction 1130 did | cid | dlevel | dauthor | dtitle 1131-----+-----+--------+-------------------+------------------------- 1132 1 | 11 | 1 | regress_rls_bob | my first novel 1133 2 | 11 | 2 | regress_rls_bob | my second novel 1134 6 | 11 | 1 | regress_rls_carol | great science fiction 1135 9 | 11 | 1 | regress_rls_dave | awesome science fiction 1136(4 rows) 1137 1138EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 1139 QUERY PLAN 1140-------------------------------------------------------------------- 1141 Append 1142 InitPlan 1 (returns $0) 1143 -> Index Scan using uaccount_pkey on uaccount 1144 Index Cond: (pguser = CURRENT_USER) 1145 -> Seq Scan on part_document_fiction 1146 Filter: ((cid < 55) AND (dlevel <= $0) AND f_leak(dtitle)) 1147(6 rows) 1148 1149-- viewpoint from regress_rls_carol 1150SET SESSION AUTHORIZATION regress_rls_carol; 1151SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 1152NOTICE: f_leak => my first novel 1153NOTICE: f_leak => my second novel 1154NOTICE: f_leak => great science fiction 1155NOTICE: f_leak => awesome science fiction 1156NOTICE: f_leak => my first satire 1157NOTICE: f_leak => great satire 1158NOTICE: f_leak => testing RLS with partitions 1159NOTICE: f_leak => my science textbook 1160NOTICE: f_leak => my history book 1161NOTICE: f_leak => great technology book 1162NOTICE: f_leak => awesome technology book 1163 did | cid | dlevel | dauthor | dtitle 1164-----+-----+--------+-------------------+----------------------------- 1165 1 | 11 | 1 | regress_rls_bob | my first novel 1166 2 | 11 | 2 | regress_rls_bob | my second novel 1167 3 | 99 | 2 | regress_rls_bob | my science textbook 1168 4 | 55 | 1 | regress_rls_bob | my first satire 1169 5 | 99 | 2 | regress_rls_bob | my history book 1170 6 | 11 | 1 | regress_rls_carol | great science fiction 1171 7 | 99 | 2 | regress_rls_carol | great technology book 1172 8 | 55 | 2 | regress_rls_carol | great satire 1173 9 | 11 | 1 | regress_rls_dave | awesome science fiction 1174 10 | 99 | 2 | regress_rls_dave | awesome technology book 1175 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions 1176(11 rows) 1177 1178EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 1179 QUERY PLAN 1180----------------------------------------------------- 1181 Append 1182 InitPlan 1 (returns $0) 1183 -> Index Scan using uaccount_pkey on uaccount 1184 Index Cond: (pguser = CURRENT_USER) 1185 -> Seq Scan on part_document_fiction 1186 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 1187 -> Seq Scan on part_document_satire 1188 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 1189 -> Seq Scan on part_document_nonfiction 1190 Filter: ((dlevel <= $0) AND f_leak(dtitle)) 1191(10 rows) 1192 1193-- only owner can change policies 1194ALTER POLICY pp1 ON part_document USING (true); --fail 1195ERROR: must be owner of relation part_document 1196DROP POLICY pp1 ON part_document; --fail 1197ERROR: must be owner of relation part_document 1198SET SESSION AUTHORIZATION regress_rls_alice; 1199ALTER POLICY pp1 ON part_document USING (dauthor = current_user); 1200-- viewpoint from regress_rls_bob again 1201SET SESSION AUTHORIZATION regress_rls_bob; 1202SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 1203NOTICE: f_leak => my first novel 1204NOTICE: f_leak => my second novel 1205NOTICE: f_leak => my first satire 1206NOTICE: f_leak => my science textbook 1207NOTICE: f_leak => my history book 1208 did | cid | dlevel | dauthor | dtitle 1209-----+-----+--------+-----------------+--------------------- 1210 1 | 11 | 1 | regress_rls_bob | my first novel 1211 2 | 11 | 2 | regress_rls_bob | my second novel 1212 3 | 99 | 2 | regress_rls_bob | my science textbook 1213 4 | 55 | 1 | regress_rls_bob | my first satire 1214 5 | 99 | 2 | regress_rls_bob | my history book 1215(5 rows) 1216 1217-- viewpoint from rls_regres_carol again 1218SET SESSION AUTHORIZATION regress_rls_carol; 1219SELECT * FROM part_document WHERE f_leak(dtitle) ORDER BY did; 1220NOTICE: f_leak => great science fiction 1221NOTICE: f_leak => great satire 1222NOTICE: f_leak => great technology book 1223 did | cid | dlevel | dauthor | dtitle 1224-----+-----+--------+-------------------+----------------------- 1225 6 | 11 | 1 | regress_rls_carol | great science fiction 1226 7 | 99 | 2 | regress_rls_carol | great technology book 1227 8 | 55 | 2 | regress_rls_carol | great satire 1228(3 rows) 1229 1230EXPLAIN (COSTS OFF) SELECT * FROM part_document WHERE f_leak(dtitle); 1231 QUERY PLAN 1232--------------------------------------------------------------- 1233 Append 1234 -> Seq Scan on part_document_fiction 1235 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle)) 1236 -> Seq Scan on part_document_satire 1237 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle)) 1238 -> Seq Scan on part_document_nonfiction 1239 Filter: ((dauthor = CURRENT_USER) AND f_leak(dtitle)) 1240(7 rows) 1241 1242-- database superuser does bypass RLS policy when enabled 1243RESET SESSION AUTHORIZATION; 1244SET row_security TO ON; 1245SELECT * FROM part_document ORDER BY did; 1246 did | cid | dlevel | dauthor | dtitle 1247-----+-----+--------+-------------------+----------------------------- 1248 1 | 11 | 1 | regress_rls_bob | my first novel 1249 2 | 11 | 2 | regress_rls_bob | my second novel 1250 3 | 99 | 2 | regress_rls_bob | my science textbook 1251 4 | 55 | 1 | regress_rls_bob | my first satire 1252 5 | 99 | 2 | regress_rls_bob | my history book 1253 6 | 11 | 1 | regress_rls_carol | great science fiction 1254 7 | 99 | 2 | regress_rls_carol | great technology book 1255 8 | 55 | 2 | regress_rls_carol | great satire 1256 9 | 11 | 1 | regress_rls_dave | awesome science fiction 1257 10 | 99 | 2 | regress_rls_dave | awesome technology book 1258 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions 1259(11 rows) 1260 1261SELECT * FROM part_document_satire ORDER by did; 1262 did | cid | dlevel | dauthor | dtitle 1263-----+-----+--------+-------------------+----------------------------- 1264 4 | 55 | 1 | regress_rls_bob | my first satire 1265 8 | 55 | 2 | regress_rls_carol | great satire 1266 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions 1267(3 rows) 1268 1269-- database non-superuser with bypass privilege can bypass RLS policy when disabled 1270SET SESSION AUTHORIZATION regress_rls_exempt_user; 1271SET row_security TO OFF; 1272SELECT * FROM part_document ORDER BY did; 1273 did | cid | dlevel | dauthor | dtitle 1274-----+-----+--------+-------------------+----------------------------- 1275 1 | 11 | 1 | regress_rls_bob | my first novel 1276 2 | 11 | 2 | regress_rls_bob | my second novel 1277 3 | 99 | 2 | regress_rls_bob | my science textbook 1278 4 | 55 | 1 | regress_rls_bob | my first satire 1279 5 | 99 | 2 | regress_rls_bob | my history book 1280 6 | 11 | 1 | regress_rls_carol | great science fiction 1281 7 | 99 | 2 | regress_rls_carol | great technology book 1282 8 | 55 | 2 | regress_rls_carol | great satire 1283 9 | 11 | 1 | regress_rls_dave | awesome science fiction 1284 10 | 99 | 2 | regress_rls_dave | awesome technology book 1285 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions 1286(11 rows) 1287 1288SELECT * FROM part_document_satire ORDER by did; 1289 did | cid | dlevel | dauthor | dtitle 1290-----+-----+--------+-------------------+----------------------------- 1291 4 | 55 | 1 | regress_rls_bob | my first satire 1292 8 | 55 | 2 | regress_rls_carol | great satire 1293 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions 1294(3 rows) 1295 1296-- RLS policy does not apply to table owner when RLS enabled. 1297SET SESSION AUTHORIZATION regress_rls_alice; 1298SET row_security TO ON; 1299SELECT * FROM part_document ORDER by did; 1300 did | cid | dlevel | dauthor | dtitle 1301-----+-----+--------+-------------------+----------------------------- 1302 1 | 11 | 1 | regress_rls_bob | my first novel 1303 2 | 11 | 2 | regress_rls_bob | my second novel 1304 3 | 99 | 2 | regress_rls_bob | my science textbook 1305 4 | 55 | 1 | regress_rls_bob | my first satire 1306 5 | 99 | 2 | regress_rls_bob | my history book 1307 6 | 11 | 1 | regress_rls_carol | great science fiction 1308 7 | 99 | 2 | regress_rls_carol | great technology book 1309 8 | 55 | 2 | regress_rls_carol | great satire 1310 9 | 11 | 1 | regress_rls_dave | awesome science fiction 1311 10 | 99 | 2 | regress_rls_dave | awesome technology book 1312 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions 1313(11 rows) 1314 1315SELECT * FROM part_document_satire ORDER by did; 1316 did | cid | dlevel | dauthor | dtitle 1317-----+-----+--------+-------------------+----------------------------- 1318 4 | 55 | 1 | regress_rls_bob | my first satire 1319 8 | 55 | 2 | regress_rls_carol | great satire 1320 100 | 55 | 1 | regress_rls_dave | testing RLS with partitions 1321(3 rows) 1322 1323-- When RLS disabled, other users get ERROR. 1324SET SESSION AUTHORIZATION regress_rls_dave; 1325SET row_security TO OFF; 1326SELECT * FROM part_document ORDER by did; 1327ERROR: query would be affected by row-level security policy for table "part_document" 1328SELECT * FROM part_document_satire ORDER by did; 1329ERROR: query would be affected by row-level security policy for table "part_document_satire" 1330-- Check behavior with a policy that uses a SubPlan not an InitPlan. 1331SET SESSION AUTHORIZATION regress_rls_alice; 1332SET row_security TO ON; 1333CREATE POLICY pp3 ON part_document AS RESTRICTIVE 1334 USING ((SELECT dlevel <= seclv FROM uaccount WHERE pguser = current_user)); 1335SET SESSION AUTHORIZATION regress_rls_carol; 1336INSERT INTO part_document VALUES (100, 11, 5, 'regress_rls_carol', 'testing pp3'); -- fail 1337ERROR: new row violates row-level security policy "pp3" for table "part_document" 1338----- Dependencies ----- 1339SET SESSION AUTHORIZATION regress_rls_alice; 1340SET row_security TO ON; 1341CREATE TABLE dependee (x integer, y integer); 1342CREATE TABLE dependent (x integer, y integer); 1343CREATE POLICY d1 ON dependent FOR ALL 1344 TO PUBLIC 1345 USING (x = (SELECT d.x FROM dependee d WHERE d.y = y)); 1346DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row security qual? 1347ERROR: cannot drop table dependee because other objects depend on it 1348DETAIL: policy d1 on table dependent depends on table dependee 1349HINT: Use DROP ... CASCADE to drop the dependent objects too. 1350DROP TABLE dependee CASCADE; 1351NOTICE: drop cascades to policy d1 on table dependent 1352EXPLAIN (COSTS OFF) SELECT * FROM dependent; -- After drop, should be unqualified 1353 QUERY PLAN 1354----------------------- 1355 Seq Scan on dependent 1356(1 row) 1357 1358----- RECURSION ---- 1359-- 1360-- Simple recursion 1361-- 1362SET SESSION AUTHORIZATION regress_rls_alice; 1363CREATE TABLE rec1 (x integer, y integer); 1364CREATE POLICY r1 ON rec1 USING (x = (SELECT r.x FROM rec1 r WHERE y = r.y)); 1365ALTER TABLE rec1 ENABLE ROW LEVEL SECURITY; 1366SET SESSION AUTHORIZATION regress_rls_bob; 1367SELECT * FROM rec1; -- fail, direct recursion 1368ERROR: infinite recursion detected in policy for relation "rec1" 1369-- 1370-- Mutual recursion 1371-- 1372SET SESSION AUTHORIZATION regress_rls_alice; 1373CREATE TABLE rec2 (a integer, b integer); 1374ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2 WHERE b = y)); 1375CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1 WHERE y = b)); 1376ALTER TABLE rec2 ENABLE ROW LEVEL SECURITY; 1377SET SESSION AUTHORIZATION regress_rls_bob; 1378SELECT * FROM rec1; -- fail, mutual recursion 1379ERROR: infinite recursion detected in policy for relation "rec1" 1380-- 1381-- Mutual recursion via views 1382-- 1383SET SESSION AUTHORIZATION regress_rls_bob; 1384CREATE VIEW rec1v AS SELECT * FROM rec1; 1385CREATE VIEW rec2v AS SELECT * FROM rec2; 1386SET SESSION AUTHORIZATION regress_rls_alice; 1387ALTER POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y)); 1388ALTER POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b)); 1389SET SESSION AUTHORIZATION regress_rls_bob; 1390SELECT * FROM rec1; -- fail, mutual recursion via views 1391ERROR: infinite recursion detected in policy for relation "rec1" 1392-- 1393-- Mutual recursion via .s.b views 1394-- 1395SET SESSION AUTHORIZATION regress_rls_bob; 1396\set VERBOSITY terse \\ -- suppress cascade details 1397DROP VIEW rec1v, rec2v CASCADE; 1398NOTICE: drop cascades to 2 other objects 1399\set VERBOSITY default 1400CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1; 1401CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2; 1402SET SESSION AUTHORIZATION regress_rls_alice; 1403CREATE POLICY r1 ON rec1 USING (x = (SELECT a FROM rec2v WHERE b = y)); 1404CREATE POLICY r2 ON rec2 USING (a = (SELECT x FROM rec1v WHERE y = b)); 1405SET SESSION AUTHORIZATION regress_rls_bob; 1406SELECT * FROM rec1; -- fail, mutual recursion via s.b. views 1407ERROR: infinite recursion detected in policy for relation "rec1" 1408-- 1409-- recursive RLS and VIEWs in policy 1410-- 1411SET SESSION AUTHORIZATION regress_rls_alice; 1412CREATE TABLE s1 (a int, b text); 1413INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x); 1414CREATE TABLE s2 (x int, y text); 1415INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x); 1416GRANT SELECT ON s1, s2 TO regress_rls_bob; 1417CREATE POLICY p1 ON s1 USING (a in (select x from s2 where y like '%2f%')); 1418CREATE POLICY p2 ON s2 USING (x in (select a from s1 where b like '%22%')); 1419CREATE POLICY p3 ON s1 FOR INSERT WITH CHECK (a = (SELECT a FROM s1)); 1420ALTER TABLE s1 ENABLE ROW LEVEL SECURITY; 1421ALTER TABLE s2 ENABLE ROW LEVEL SECURITY; 1422SET SESSION AUTHORIZATION regress_rls_bob; 1423CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%'; 1424SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion) 1425ERROR: infinite recursion detected in policy for relation "s1" 1426INSERT INTO s1 VALUES (1, 'foo'); -- fail (infinite recursion) 1427ERROR: infinite recursion detected in policy for relation "s1" 1428SET SESSION AUTHORIZATION regress_rls_alice; 1429DROP POLICY p3 on s1; 1430ALTER POLICY p2 ON s2 USING (x % 2 = 0); 1431SET SESSION AUTHORIZATION regress_rls_bob; 1432SELECT * FROM s1 WHERE f_leak(b); -- OK 1433NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c 1434NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c 1435 a | b 1436---+---------------------------------- 1437 2 | c81e728d9d4c2f636f067f89cc14862c 1438 4 | a87ff679a2f3e71d9181a67b7542122c 1439(2 rows) 1440 1441EXPLAIN (COSTS OFF) SELECT * FROM only s1 WHERE f_leak(b); 1442 QUERY PLAN 1443----------------------------------------------------------- 1444 Seq Scan on s1 1445 Filter: ((hashed SubPlan 1) AND f_leak(b)) 1446 SubPlan 1 1447 -> Seq Scan on s2 1448 Filter: (((x % 2) = 0) AND (y ~~ '%2f%'::text)) 1449(5 rows) 1450 1451SET SESSION AUTHORIZATION regress_rls_alice; 1452ALTER POLICY p1 ON s1 USING (a in (select x from v2)); -- using VIEW in RLS policy 1453SET SESSION AUTHORIZATION regress_rls_bob; 1454SELECT * FROM s1 WHERE f_leak(b); -- OK 1455NOTICE: f_leak => 0267aaf632e87a63288a08331f22c7c3 1456NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc 1457 a | b 1458----+---------------------------------- 1459 -4 | 0267aaf632e87a63288a08331f22c7c3 1460 6 | 1679091c5a880faf6fb5e6087eb1b2dc 1461(2 rows) 1462 1463EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b); 1464 QUERY PLAN 1465----------------------------------------------------------- 1466 Seq Scan on s1 1467 Filter: ((hashed SubPlan 1) AND f_leak(b)) 1468 SubPlan 1 1469 -> Seq Scan on s2 1470 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text)) 1471(5 rows) 1472 1473SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%'; 1474 xx | x | y 1475----+----+---------------------------------- 1476 -6 | -6 | 596a3d04481816330f07e4f97510c28f 1477 -4 | -4 | 0267aaf632e87a63288a08331f22c7c3 1478 2 | 2 | c81e728d9d4c2f636f067f89cc14862c 1479(3 rows) 1480 1481EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%'; 1482 QUERY PLAN 1483------------------------------------------------------------------------- 1484 Seq Scan on s2 1485 Filter: (((x % 2) = 0) AND (y ~~ '%28%'::text)) 1486 SubPlan 2 1487 -> Limit 1488 -> Seq Scan on s1 1489 Filter: (hashed SubPlan 1) 1490 SubPlan 1 1491 -> Seq Scan on s2 s2_1 1492 Filter: (((x % 2) = 0) AND (y ~~ '%af%'::text)) 1493(9 rows) 1494 1495SET SESSION AUTHORIZATION regress_rls_alice; 1496ALTER POLICY p2 ON s2 USING (x in (select a from s1 where b like '%d2%')); 1497SET SESSION AUTHORIZATION regress_rls_bob; 1498SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view) 1499ERROR: infinite recursion detected in policy for relation "s1" 1500-- prepared statement with regress_rls_alice privilege 1501PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1; 1502EXECUTE p1(2); 1503 a | b 1504---+----- 1505 2 | bbb 1506 2 | bcd 1507 2 | yyy 1508(3 rows) 1509 1510EXPLAIN (COSTS OFF) EXECUTE p1(2); 1511 QUERY PLAN 1512---------------------------------------------- 1513 Append 1514 -> Seq Scan on t1 1515 Filter: ((a <= 2) AND ((a % 2) = 0)) 1516 -> Seq Scan on t2 1517 Filter: ((a <= 2) AND ((a % 2) = 0)) 1518 -> Seq Scan on t3 1519 Filter: ((a <= 2) AND ((a % 2) = 0)) 1520(7 rows) 1521 1522-- superuser is allowed to bypass RLS checks 1523RESET SESSION AUTHORIZATION; 1524SET row_security TO OFF; 1525SELECT * FROM t1 WHERE f_leak(b); 1526NOTICE: f_leak => aba 1527NOTICE: f_leak => bbb 1528NOTICE: f_leak => ccc 1529NOTICE: f_leak => dad 1530NOTICE: f_leak => abc 1531NOTICE: f_leak => bcd 1532NOTICE: f_leak => cde 1533NOTICE: f_leak => def 1534NOTICE: f_leak => xxx 1535NOTICE: f_leak => yyy 1536NOTICE: f_leak => zzz 1537 a | b 1538---+----- 1539 1 | aba 1540 2 | bbb 1541 3 | ccc 1542 4 | dad 1543 1 | abc 1544 2 | bcd 1545 3 | cde 1546 4 | def 1547 1 | xxx 1548 2 | yyy 1549 3 | zzz 1550(11 rows) 1551 1552EXPLAIN (COSTS OFF) SELECT * FROM t1 WHERE f_leak(b); 1553 QUERY PLAN 1554--------------------------- 1555 Append 1556 -> Seq Scan on t1 1557 Filter: f_leak(b) 1558 -> Seq Scan on t2 1559 Filter: f_leak(b) 1560 -> Seq Scan on t3 1561 Filter: f_leak(b) 1562(7 rows) 1563 1564-- plan cache should be invalidated 1565EXECUTE p1(2); 1566 a | b 1567---+----- 1568 1 | aba 1569 2 | bbb 1570 1 | abc 1571 2 | bcd 1572 1 | xxx 1573 2 | yyy 1574(6 rows) 1575 1576EXPLAIN (COSTS OFF) EXECUTE p1(2); 1577 QUERY PLAN 1578-------------------------- 1579 Append 1580 -> Seq Scan on t1 1581 Filter: (a <= 2) 1582 -> Seq Scan on t2 1583 Filter: (a <= 2) 1584 -> Seq Scan on t3 1585 Filter: (a <= 2) 1586(7 rows) 1587 1588PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1; 1589EXECUTE p2(2); 1590 a | b 1591---+----- 1592 2 | bbb 1593 2 | bcd 1594 2 | yyy 1595(3 rows) 1596 1597EXPLAIN (COSTS OFF) EXECUTE p2(2); 1598 QUERY PLAN 1599------------------------- 1600 Append 1601 -> Seq Scan on t1 1602 Filter: (a = 2) 1603 -> Seq Scan on t2 1604 Filter: (a = 2) 1605 -> Seq Scan on t3 1606 Filter: (a = 2) 1607(7 rows) 1608 1609-- also, case when privilege switch from superuser 1610SET SESSION AUTHORIZATION regress_rls_bob; 1611SET row_security TO ON; 1612EXECUTE p2(2); 1613 a | b 1614---+----- 1615 2 | bbb 1616 2 | bcd 1617 2 | yyy 1618(3 rows) 1619 1620EXPLAIN (COSTS OFF) EXECUTE p2(2); 1621 QUERY PLAN 1622--------------------------------------------- 1623 Append 1624 -> Seq Scan on t1 1625 Filter: ((a = 2) AND ((a % 2) = 0)) 1626 -> Seq Scan on t2 1627 Filter: ((a = 2) AND ((a % 2) = 0)) 1628 -> Seq Scan on t3 1629 Filter: ((a = 2) AND ((a % 2) = 0)) 1630(7 rows) 1631 1632-- 1633-- UPDATE / DELETE and Row-level security 1634-- 1635SET SESSION AUTHORIZATION regress_rls_bob; 1636EXPLAIN (COSTS OFF) UPDATE t1 SET b = b || b WHERE f_leak(b); 1637 QUERY PLAN 1638----------------------------------------------- 1639 Update on t1 1640 Update on t1 1641 Update on t2 1642 Update on t3 1643 -> Seq Scan on t1 1644 Filter: (((a % 2) = 0) AND f_leak(b)) 1645 -> Seq Scan on t2 1646 Filter: (((a % 2) = 0) AND f_leak(b)) 1647 -> Seq Scan on t3 1648 Filter: (((a % 2) = 0) AND f_leak(b)) 1649(10 rows) 1650 1651UPDATE t1 SET b = b || b WHERE f_leak(b); 1652NOTICE: f_leak => bbb 1653NOTICE: f_leak => dad 1654NOTICE: f_leak => bcd 1655NOTICE: f_leak => def 1656NOTICE: f_leak => yyy 1657EXPLAIN (COSTS OFF) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b); 1658 QUERY PLAN 1659----------------------------------------------- 1660 Update on t1 1661 -> Seq Scan on t1 1662 Filter: (((a % 2) = 0) AND f_leak(b)) 1663(3 rows) 1664 1665UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b); 1666NOTICE: f_leak => bbbbbb 1667NOTICE: f_leak => daddad 1668-- returning clause with system column 1669UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1; 1670NOTICE: f_leak => bbbbbb_updt 1671NOTICE: f_leak => daddad_updt 1672 oid | a | b | t1 1673-----+---+-------------+----------------- 1674 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt) 1675 104 | 4 | daddad_updt | (4,daddad_updt) 1676(2 rows) 1677 1678UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *; 1679NOTICE: f_leak => bbbbbb_updt 1680NOTICE: f_leak => daddad_updt 1681NOTICE: f_leak => bcdbcd 1682NOTICE: f_leak => defdef 1683NOTICE: f_leak => yyyyyy 1684 a | b 1685---+------------- 1686 2 | bbbbbb_updt 1687 4 | daddad_updt 1688 2 | bcdbcd 1689 4 | defdef 1690 2 | yyyyyy 1691(5 rows) 1692 1693UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1; 1694NOTICE: f_leak => bbbbbb_updt 1695NOTICE: f_leak => daddad_updt 1696NOTICE: f_leak => bcdbcd 1697NOTICE: f_leak => defdef 1698NOTICE: f_leak => yyyyyy 1699 oid | a | b | t1 1700-----+---+-------------+----------------- 1701 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt) 1702 104 | 4 | daddad_updt | (4,daddad_updt) 1703 202 | 2 | bcdbcd | (2,bcdbcd) 1704 204 | 4 | defdef | (4,defdef) 1705 302 | 2 | yyyyyy | (2,yyyyyy) 1706(5 rows) 1707 1708-- updates with from clause 1709EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t3 1710WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b); 1711 QUERY PLAN 1712----------------------------------------------------------------- 1713 Update on t2 1714 -> Nested Loop 1715 -> Seq Scan on t2 1716 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b)) 1717 -> Seq Scan on t3 1718 Filter: ((a = 2) AND f_leak(b)) 1719(6 rows) 1720 1721UPDATE t2 SET b=t2.b FROM t3 1722WHERE t2.a = 3 and t3.a = 2 AND f_leak(t2.b) AND f_leak(t3.b); 1723NOTICE: f_leak => cde 1724NOTICE: f_leak => yyyyyy 1725EXPLAIN (COSTS OFF) UPDATE t1 SET b=t1.b FROM t2 1726WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b); 1727 QUERY PLAN 1728----------------------------------------------------------------- 1729 Update on t1 1730 Update on t1 1731 Update on t2 t2_1 1732 Update on t3 1733 -> Nested Loop 1734 -> Seq Scan on t1 1735 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b)) 1736 -> Seq Scan on t2 1737 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b)) 1738 -> Nested Loop 1739 -> Seq Scan on t2 t2_1 1740 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b)) 1741 -> Seq Scan on t2 1742 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b)) 1743 -> Nested Loop 1744 -> Seq Scan on t3 1745 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b)) 1746 -> Seq Scan on t2 1747 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b)) 1748(19 rows) 1749 1750UPDATE t1 SET b=t1.b FROM t2 1751WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b); 1752EXPLAIN (COSTS OFF) UPDATE t2 SET b=t2.b FROM t1 1753WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b); 1754 QUERY PLAN 1755----------------------------------------------------------------------- 1756 Update on t2 1757 -> Nested Loop 1758 -> Seq Scan on t2 1759 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b)) 1760 -> Append 1761 -> Seq Scan on t1 1762 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b)) 1763 -> Seq Scan on t2 t2_1 1764 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b)) 1765 -> Seq Scan on t3 1766 Filter: ((a = 3) AND ((a % 2) = 0) AND f_leak(b)) 1767(11 rows) 1768 1769UPDATE t2 SET b=t2.b FROM t1 1770WHERE t1.a = 3 and t2.a = 3 AND f_leak(t1.b) AND f_leak(t2.b); 1771NOTICE: f_leak => cde 1772-- updates with from clause self join 1773EXPLAIN (COSTS OFF) UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2 1774WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b 1775AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2; 1776 QUERY PLAN 1777----------------------------------------------------------------- 1778 Update on t2 t2_1 1779 -> Nested Loop 1780 Join Filter: (t2_1.b = t2_2.b) 1781 -> Seq Scan on t2 t2_1 1782 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b)) 1783 -> Seq Scan on t2 t2_2 1784 Filter: ((a = 3) AND ((a % 2) = 1) AND f_leak(b)) 1785(7 rows) 1786 1787UPDATE t2 t2_1 SET b = t2_2.b FROM t2 t2_2 1788WHERE t2_1.a = 3 AND t2_2.a = t2_1.a AND t2_2.b = t2_1.b 1789AND f_leak(t2_1.b) AND f_leak(t2_2.b) RETURNING *, t2_1, t2_2; 1790NOTICE: f_leak => cde 1791NOTICE: f_leak => cde 1792 a | b | c | a | b | c | t2_1 | t2_2 1793---+-----+-----+---+-----+-----+-------------+------------- 1794 3 | cde | 3.3 | 3 | cde | 3.3 | (3,cde,3.3) | (3,cde,3.3) 1795(1 row) 1796 1797EXPLAIN (COSTS OFF) UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2 1798WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b 1799AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2; 1800 QUERY PLAN 1801----------------------------------------------------------------------- 1802 Update on t1 t1_1 1803 Update on t1 t1_1 1804 Update on t2 t1_1_1 1805 Update on t3 t1_1_2 1806 -> Nested Loop 1807 Join Filter: (t1_1.b = t1_2.b) 1808 -> Seq Scan on t1 t1_1 1809 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1810 -> Append 1811 -> Seq Scan on t1 t1_2 1812 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1813 -> Seq Scan on t2 t1_2_1 1814 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1815 -> Seq Scan on t3 t1_2_2 1816 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1817 -> Nested Loop 1818 Join Filter: (t1_1_1.b = t1_2.b) 1819 -> Seq Scan on t2 t1_1_1 1820 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1821 -> Append 1822 -> Seq Scan on t1 t1_2 1823 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1824 -> Seq Scan on t2 t1_2_1 1825 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1826 -> Seq Scan on t3 t1_2_2 1827 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1828 -> Nested Loop 1829 Join Filter: (t1_1_2.b = t1_2.b) 1830 -> Seq Scan on t3 t1_1_2 1831 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1832 -> Append 1833 -> Seq Scan on t1 t1_2 1834 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1835 -> Seq Scan on t2 t1_2_1 1836 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1837 -> Seq Scan on t3 t1_2_2 1838 Filter: ((a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1839(37 rows) 1840 1841UPDATE t1 t1_1 SET b = t1_2.b FROM t1 t1_2 1842WHERE t1_1.a = 4 AND t1_2.a = t1_1.a AND t1_2.b = t1_1.b 1843AND f_leak(t1_1.b) AND f_leak(t1_2.b) RETURNING *, t1_1, t1_2; 1844NOTICE: f_leak => daddad_updt 1845NOTICE: f_leak => daddad_updt 1846NOTICE: f_leak => defdef 1847NOTICE: f_leak => defdef 1848NOTICE: f_leak => daddad_updt 1849NOTICE: f_leak => defdef 1850 a | b | a | b | t1_1 | t1_2 1851---+-------------+---+-------------+-----------------+----------------- 1852 4 | daddad_updt | 4 | daddad_updt | (4,daddad_updt) | (4,daddad_updt) 1853 4 | defdef | 4 | defdef | (4,defdef) | (4,defdef) 1854(2 rows) 1855 1856RESET SESSION AUTHORIZATION; 1857SET row_security TO OFF; 1858SELECT * FROM t1 ORDER BY a,b; 1859 a | b 1860---+------------- 1861 1 | aba 1862 1 | abc 1863 1 | xxx 1864 2 | bbbbbb_updt 1865 2 | bcdbcd 1866 2 | yyyyyy 1867 3 | ccc 1868 3 | cde 1869 3 | zzz 1870 4 | daddad_updt 1871 4 | defdef 1872(11 rows) 1873 1874SET SESSION AUTHORIZATION regress_rls_bob; 1875SET row_security TO ON; 1876EXPLAIN (COSTS OFF) DELETE FROM only t1 WHERE f_leak(b); 1877 QUERY PLAN 1878----------------------------------------------- 1879 Delete on t1 1880 -> Seq Scan on t1 1881 Filter: (((a % 2) = 0) AND f_leak(b)) 1882(3 rows) 1883 1884EXPLAIN (COSTS OFF) DELETE FROM t1 WHERE f_leak(b); 1885 QUERY PLAN 1886----------------------------------------------- 1887 Delete on t1 1888 Delete on t1 1889 Delete on t2 1890 Delete on t3 1891 -> Seq Scan on t1 1892 Filter: (((a % 2) = 0) AND f_leak(b)) 1893 -> Seq Scan on t2 1894 Filter: (((a % 2) = 0) AND f_leak(b)) 1895 -> Seq Scan on t3 1896 Filter: (((a % 2) = 0) AND f_leak(b)) 1897(10 rows) 1898 1899DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1; 1900NOTICE: f_leak => bbbbbb_updt 1901NOTICE: f_leak => daddad_updt 1902 oid | a | b | t1 1903-----+---+-------------+----------------- 1904 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt) 1905 104 | 4 | daddad_updt | (4,daddad_updt) 1906(2 rows) 1907 1908DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1; 1909NOTICE: f_leak => bcdbcd 1910NOTICE: f_leak => defdef 1911NOTICE: f_leak => yyyyyy 1912 oid | a | b | t1 1913-----+---+--------+------------ 1914 202 | 2 | bcdbcd | (2,bcdbcd) 1915 204 | 4 | defdef | (4,defdef) 1916 302 | 2 | yyyyyy | (2,yyyyyy) 1917(3 rows) 1918 1919-- 1920-- S.b. view on top of Row-level security 1921-- 1922SET SESSION AUTHORIZATION regress_rls_alice; 1923CREATE TABLE b1 (a int, b text); 1924INSERT INTO b1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x); 1925CREATE POLICY p1 ON b1 USING (a % 2 = 0); 1926ALTER TABLE b1 ENABLE ROW LEVEL SECURITY; 1927GRANT ALL ON b1 TO regress_rls_bob; 1928SET SESSION AUTHORIZATION regress_rls_bob; 1929CREATE VIEW bv1 WITH (security_barrier) AS SELECT * FROM b1 WHERE a > 0 WITH CHECK OPTION; 1930GRANT ALL ON bv1 TO regress_rls_carol; 1931SET SESSION AUTHORIZATION regress_rls_carol; 1932EXPLAIN (COSTS OFF) SELECT * FROM bv1 WHERE f_leak(b); 1933 QUERY PLAN 1934--------------------------------------------- 1935 Subquery Scan on bv1 1936 Filter: f_leak(bv1.b) 1937 -> Seq Scan on b1 1938 Filter: ((a > 0) AND ((a % 2) = 0)) 1939(4 rows) 1940 1941SELECT * FROM bv1 WHERE f_leak(b); 1942NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c 1943NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c 1944NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc 1945NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d 1946NOTICE: f_leak => d3d9446802a44259755d38e6d163e820 1947 a | b 1948----+---------------------------------- 1949 2 | c81e728d9d4c2f636f067f89cc14862c 1950 4 | a87ff679a2f3e71d9181a67b7542122c 1951 6 | 1679091c5a880faf6fb5e6087eb1b2dc 1952 8 | c9f0f895fb98ab9159f51fd0297e236d 1953 10 | d3d9446802a44259755d38e6d163e820 1954(5 rows) 1955 1956INSERT INTO bv1 VALUES (-1, 'xxx'); -- should fail view WCO 1957ERROR: new row violates row-level security policy for table "b1" 1958INSERT INTO bv1 VALUES (11, 'xxx'); -- should fail RLS check 1959ERROR: new row violates row-level security policy for table "b1" 1960INSERT INTO bv1 VALUES (12, 'xxx'); -- ok 1961EXPLAIN (COSTS OFF) UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b); 1962 QUERY PLAN 1963----------------------------------------------------------------------- 1964 Update on b1 1965 -> Seq Scan on b1 1966 Filter: ((a > 0) AND (a = 4) AND ((a % 2) = 0) AND f_leak(b)) 1967(3 rows) 1968 1969UPDATE bv1 SET b = 'yyy' WHERE a = 4 AND f_leak(b); 1970NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c 1971EXPLAIN (COSTS OFF) DELETE FROM bv1 WHERE a = 6 AND f_leak(b); 1972 QUERY PLAN 1973----------------------------------------------------------------------- 1974 Delete on b1 1975 -> Seq Scan on b1 1976 Filter: ((a > 0) AND (a = 6) AND ((a % 2) = 0) AND f_leak(b)) 1977(3 rows) 1978 1979DELETE FROM bv1 WHERE a = 6 AND f_leak(b); 1980NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc 1981SET SESSION AUTHORIZATION regress_rls_alice; 1982SELECT * FROM b1; 1983 a | b 1984-----+---------------------------------- 1985 -10 | 1b0fd9efa5279c4203b7c70233f86dbf 1986 -9 | 252e691406782824eec43d7eadc3d256 1987 -8 | a8d2ec85eaf98407310b72eb73dda247 1988 -7 | 74687a12d3915d3c4d83f1af7b3683d5 1989 -6 | 596a3d04481816330f07e4f97510c28f 1990 -5 | 47c1b025fa18ea96c33fbb6718688c0f 1991 -4 | 0267aaf632e87a63288a08331f22c7c3 1992 -3 | b3149ecea4628efd23d2f86e5a723472 1993 -2 | 5d7b9adcbe1c629ec722529dd12e5129 1994 -1 | 6bb61e3b7bce0931da574d19d1d82c88 1995 0 | cfcd208495d565ef66e7dff9f98764da 1996 1 | c4ca4238a0b923820dcc509a6f75849b 1997 2 | c81e728d9d4c2f636f067f89cc14862c 1998 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 1999 5 | e4da3b7fbbce2345d7772b0674a318d5 2000 7 | 8f14e45fceea167a5a36dedd4bea2543 2001 8 | c9f0f895fb98ab9159f51fd0297e236d 2002 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 2003 10 | d3d9446802a44259755d38e6d163e820 2004 12 | xxx 2005 4 | yyy 2006(21 rows) 2007 2008-- 2009-- INSERT ... ON CONFLICT DO UPDATE and Row-level security 2010-- 2011SET SESSION AUTHORIZATION regress_rls_alice; 2012DROP POLICY p1 ON document; 2013DROP POLICY p1r ON document; 2014CREATE POLICY p1 ON document FOR SELECT USING (true); 2015CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user); 2016CREATE POLICY p3 ON document FOR UPDATE 2017 USING (cid = (SELECT cid from category WHERE cname = 'novel')) 2018 WITH CHECK (dauthor = current_user); 2019SET SESSION AUTHORIZATION regress_rls_bob; 2020-- Exists... 2021SELECT * FROM document WHERE did = 2; 2022 did | cid | dlevel | dauthor | dtitle 2023-----+-----+--------+-----------------+----------------- 2024 2 | 11 | 2 | regress_rls_bob | my second novel 2025(1 row) 2026 2027-- ...so violates actual WITH CHECK OPTION within UPDATE (not INSERT, since 2028-- alternative UPDATE path happens to be taken): 2029INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel') 2030 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, dauthor = EXCLUDED.dauthor; 2031ERROR: new row violates row-level security policy for table "document" 2032-- Violates USING qual for UPDATE policy p3. 2033-- 2034-- UPDATE path is taken, but UPDATE fails purely because *existing* row to be 2035-- updated is not a "novel"/cid 11 (row is not leaked, even though we have 2036-- SELECT privileges sufficient to see the row in this instance): 2037INSERT INTO document VALUES (33, 22, 1, 'regress_rls_bob', 'okay science fiction'); -- preparation for next statement 2038INSERT INTO document VALUES (33, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'Some novel, replaces sci-fi') -- takes UPDATE path 2039 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle; 2040ERROR: new row violates row-level security policy (USING expression) for table "document" 2041-- Fine (we UPDATE, since INSERT WCOs and UPDATE security barrier quals + WCOs 2042-- not violated): 2043INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel') 2044 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; 2045 did | cid | dlevel | dauthor | dtitle 2046-----+-----+--------+-----------------+---------------- 2047 2 | 11 | 2 | regress_rls_bob | my first novel 2048(1 row) 2049 2050-- Fine (we INSERT, so "cid = 33" ("technology") isn't evaluated): 2051INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel') 2052 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; 2053 did | cid | dlevel | dauthor | dtitle 2054-----+-----+--------+-----------------+----------------------- 2055 78 | 11 | 1 | regress_rls_bob | some technology novel 2056(1 row) 2057 2058-- Fine (same query, but we UPDATE, so "cid = 33", ("technology") is not the 2059-- case in respect of *existing* tuple): 2060INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel') 2061 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; 2062 did | cid | dlevel | dauthor | dtitle 2063-----+-----+--------+-----------------+----------------------- 2064 78 | 33 | 1 | regress_rls_bob | some technology novel 2065(1 row) 2066 2067-- Same query a third time, but now fails due to existing tuple finally not 2068-- passing quals: 2069INSERT INTO document VALUES (78, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'some technology novel') 2070 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33 RETURNING *; 2071ERROR: new row violates row-level security policy (USING expression) for table "document" 2072-- Don't fail just because INSERT doesn't satisfy WITH CHECK option that 2073-- originated as a barrier/USING() qual from the UPDATE. Note that the UPDATE 2074-- path *isn't* taken, and so UPDATE-related policy does not apply: 2075INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert') 2076 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; 2077 did | cid | dlevel | dauthor | dtitle 2078-----+-----+--------+-----------------+---------------------------------- 2079 79 | 33 | 1 | regress_rls_bob | technology book, can only insert 2080(1 row) 2081 2082-- But this time, the same statement fails, because the UPDATE path is taken, 2083-- and updating the row just inserted falls afoul of security barrier qual 2084-- (enforced as WCO) -- what we might have updated target tuple to is 2085-- irrelevant, in fact. 2086INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert') 2087 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; 2088ERROR: new row violates row-level security policy (USING expression) for table "document" 2089-- Test default USING qual enforced as WCO 2090SET SESSION AUTHORIZATION regress_rls_alice; 2091DROP POLICY p1 ON document; 2092DROP POLICY p2 ON document; 2093DROP POLICY p3 ON document; 2094CREATE POLICY p3_with_default ON document FOR UPDATE 2095 USING (cid = (SELECT cid from category WHERE cname = 'novel')); 2096SET SESSION AUTHORIZATION regress_rls_bob; 2097-- Just because WCO-style enforcement of USING quals occurs with 2098-- existing/target tuple does not mean that the implementation can be allowed 2099-- to fail to also enforce this qual against the final tuple appended to 2100-- relation (since in the absence of an explicit WCO, this is also interpreted 2101-- as an UPDATE/ALL WCO in general). 2102-- 2103-- UPDATE path is taken here (fails due to existing tuple). Note that this is 2104-- not reported as a "USING expression", because it's an RLS UPDATE check that originated as 2105-- a USING qual for the purposes of RLS in general, as opposed to an explicit 2106-- USING qual that is ordinarily a security barrier. We leave it up to the 2107-- UPDATE to make this fail: 2108INSERT INTO document VALUES (79, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'technology book, can only insert') 2109 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle RETURNING *; 2110ERROR: new row violates row-level security policy for table "document" 2111-- UPDATE path is taken here. Existing tuple passes, since its cid 2112-- corresponds to "novel", but default USING qual is enforced against 2113-- post-UPDATE tuple too (as always when updating with a policy that lacks an 2114-- explicit WCO), and so this fails: 2115INSERT INTO document VALUES (2, (SELECT cid from category WHERE cname = 'technology'), 1, 'regress_rls_bob', 'my first novel') 2116 ON CONFLICT (did) DO UPDATE SET cid = EXCLUDED.cid, dtitle = EXCLUDED.dtitle RETURNING *; 2117ERROR: new row violates row-level security policy for table "document" 2118SET SESSION AUTHORIZATION regress_rls_alice; 2119DROP POLICY p3_with_default ON document; 2120-- 2121-- Test ALL policies with ON CONFLICT DO UPDATE (much the same as existing UPDATE 2122-- tests) 2123-- 2124CREATE POLICY p3_with_all ON document FOR ALL 2125 USING (cid = (SELECT cid from category WHERE cname = 'novel')) 2126 WITH CHECK (dauthor = current_user); 2127SET SESSION AUTHORIZATION regress_rls_bob; 2128-- Fails, since ALL WCO is enforced in insert path: 2129INSERT INTO document VALUES (80, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_carol', 'my first novel') 2130 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle, cid = 33; 2131ERROR: new row violates row-level security policy for table "document" 2132-- Fails, since ALL policy USING qual is enforced (existing, target tuple is in 2133-- violation, since it has the "manga" cid): 2134INSERT INTO document VALUES (4, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel') 2135 ON CONFLICT (did) DO UPDATE SET dtitle = EXCLUDED.dtitle; 2136ERROR: new row violates row-level security policy (USING expression) for table "document" 2137-- Fails, since ALL WCO are enforced: 2138INSERT INTO document VALUES (1, (SELECT cid from category WHERE cname = 'novel'), 1, 'regress_rls_bob', 'my first novel') 2139 ON CONFLICT (did) DO UPDATE SET dauthor = 'regress_rls_carol'; 2140ERROR: new row violates row-level security policy for table "document" 2141-- 2142-- ROLE/GROUP 2143-- 2144SET SESSION AUTHORIZATION regress_rls_alice; 2145CREATE TABLE z1 (a int, b text); 2146CREATE TABLE z2 (a int, b text); 2147GRANT SELECT ON z1,z2 TO regress_rls_group1, regress_rls_group2, 2148 regress_rls_bob, regress_rls_carol; 2149INSERT INTO z1 VALUES 2150 (1, 'aba'), 2151 (2, 'bbb'), 2152 (3, 'ccc'), 2153 (4, 'dad'); 2154CREATE POLICY p1 ON z1 TO regress_rls_group1 USING (a % 2 = 0); 2155CREATE POLICY p2 ON z1 TO regress_rls_group2 USING (a % 2 = 1); 2156ALTER TABLE z1 ENABLE ROW LEVEL SECURITY; 2157SET SESSION AUTHORIZATION regress_rls_bob; 2158SELECT * FROM z1 WHERE f_leak(b); 2159NOTICE: f_leak => bbb 2160NOTICE: f_leak => dad 2161 a | b 2162---+----- 2163 2 | bbb 2164 4 | dad 2165(2 rows) 2166 2167EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b); 2168 QUERY PLAN 2169----------------------------------------- 2170 Seq Scan on z1 2171 Filter: (((a % 2) = 0) AND f_leak(b)) 2172(2 rows) 2173 2174PREPARE plancache_test AS SELECT * FROM z1 WHERE f_leak(b); 2175EXPLAIN (COSTS OFF) EXECUTE plancache_test; 2176 QUERY PLAN 2177----------------------------------------- 2178 Seq Scan on z1 2179 Filter: (((a % 2) = 0) AND f_leak(b)) 2180(2 rows) 2181 2182PREPARE plancache_test2 AS WITH q AS (SELECT * FROM z1 WHERE f_leak(b)) SELECT * FROM q,z2; 2183EXPLAIN (COSTS OFF) EXECUTE plancache_test2; 2184 QUERY PLAN 2185------------------------------------------------- 2186 Nested Loop 2187 CTE q 2188 -> Seq Scan on z1 2189 Filter: (((a % 2) = 0) AND f_leak(b)) 2190 -> CTE Scan on q 2191 -> Materialize 2192 -> Seq Scan on z2 2193(7 rows) 2194 2195PREPARE plancache_test3 AS WITH q AS (SELECT * FROM z2) SELECT * FROM q,z1 WHERE f_leak(z1.b); 2196EXPLAIN (COSTS OFF) EXECUTE plancache_test3; 2197 QUERY PLAN 2198----------------------------------------------------- 2199 Nested Loop 2200 CTE q 2201 -> Seq Scan on z2 2202 -> CTE Scan on q 2203 -> Materialize 2204 -> Seq Scan on z1 2205 Filter: (((a % 2) = 0) AND f_leak(b)) 2206(7 rows) 2207 2208SET ROLE regress_rls_group1; 2209SELECT * FROM z1 WHERE f_leak(b); 2210NOTICE: f_leak => bbb 2211NOTICE: f_leak => dad 2212 a | b 2213---+----- 2214 2 | bbb 2215 4 | dad 2216(2 rows) 2217 2218EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b); 2219 QUERY PLAN 2220----------------------------------------- 2221 Seq Scan on z1 2222 Filter: (((a % 2) = 0) AND f_leak(b)) 2223(2 rows) 2224 2225EXPLAIN (COSTS OFF) EXECUTE plancache_test; 2226 QUERY PLAN 2227----------------------------------------- 2228 Seq Scan on z1 2229 Filter: (((a % 2) = 0) AND f_leak(b)) 2230(2 rows) 2231 2232EXPLAIN (COSTS OFF) EXECUTE plancache_test2; 2233 QUERY PLAN 2234------------------------------------------------- 2235 Nested Loop 2236 CTE q 2237 -> Seq Scan on z1 2238 Filter: (((a % 2) = 0) AND f_leak(b)) 2239 -> CTE Scan on q 2240 -> Materialize 2241 -> Seq Scan on z2 2242(7 rows) 2243 2244EXPLAIN (COSTS OFF) EXECUTE plancache_test3; 2245 QUERY PLAN 2246----------------------------------------------------- 2247 Nested Loop 2248 CTE q 2249 -> Seq Scan on z2 2250 -> CTE Scan on q 2251 -> Materialize 2252 -> Seq Scan on z1 2253 Filter: (((a % 2) = 0) AND f_leak(b)) 2254(7 rows) 2255 2256SET SESSION AUTHORIZATION regress_rls_carol; 2257SELECT * FROM z1 WHERE f_leak(b); 2258NOTICE: f_leak => aba 2259NOTICE: f_leak => ccc 2260 a | b 2261---+----- 2262 1 | aba 2263 3 | ccc 2264(2 rows) 2265 2266EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b); 2267 QUERY PLAN 2268----------------------------------------- 2269 Seq Scan on z1 2270 Filter: (((a % 2) = 1) AND f_leak(b)) 2271(2 rows) 2272 2273EXPLAIN (COSTS OFF) EXECUTE plancache_test; 2274 QUERY PLAN 2275----------------------------------------- 2276 Seq Scan on z1 2277 Filter: (((a % 2) = 1) AND f_leak(b)) 2278(2 rows) 2279 2280EXPLAIN (COSTS OFF) EXECUTE plancache_test2; 2281 QUERY PLAN 2282------------------------------------------------- 2283 Nested Loop 2284 CTE q 2285 -> Seq Scan on z1 2286 Filter: (((a % 2) = 1) AND f_leak(b)) 2287 -> CTE Scan on q 2288 -> Materialize 2289 -> Seq Scan on z2 2290(7 rows) 2291 2292EXPLAIN (COSTS OFF) EXECUTE plancache_test3; 2293 QUERY PLAN 2294----------------------------------------------------- 2295 Nested Loop 2296 CTE q 2297 -> Seq Scan on z2 2298 -> CTE Scan on q 2299 -> Materialize 2300 -> Seq Scan on z1 2301 Filter: (((a % 2) = 1) AND f_leak(b)) 2302(7 rows) 2303 2304SET ROLE regress_rls_group2; 2305SELECT * FROM z1 WHERE f_leak(b); 2306NOTICE: f_leak => aba 2307NOTICE: f_leak => ccc 2308 a | b 2309---+----- 2310 1 | aba 2311 3 | ccc 2312(2 rows) 2313 2314EXPLAIN (COSTS OFF) SELECT * FROM z1 WHERE f_leak(b); 2315 QUERY PLAN 2316----------------------------------------- 2317 Seq Scan on z1 2318 Filter: (((a % 2) = 1) AND f_leak(b)) 2319(2 rows) 2320 2321EXPLAIN (COSTS OFF) EXECUTE plancache_test; 2322 QUERY PLAN 2323----------------------------------------- 2324 Seq Scan on z1 2325 Filter: (((a % 2) = 1) AND f_leak(b)) 2326(2 rows) 2327 2328EXPLAIN (COSTS OFF) EXECUTE plancache_test2; 2329 QUERY PLAN 2330------------------------------------------------- 2331 Nested Loop 2332 CTE q 2333 -> Seq Scan on z1 2334 Filter: (((a % 2) = 1) AND f_leak(b)) 2335 -> CTE Scan on q 2336 -> Materialize 2337 -> Seq Scan on z2 2338(7 rows) 2339 2340EXPLAIN (COSTS OFF) EXECUTE plancache_test3; 2341 QUERY PLAN 2342----------------------------------------------------- 2343 Nested Loop 2344 CTE q 2345 -> Seq Scan on z2 2346 -> CTE Scan on q 2347 -> Materialize 2348 -> Seq Scan on z1 2349 Filter: (((a % 2) = 1) AND f_leak(b)) 2350(7 rows) 2351 2352-- 2353-- Views should follow policy for view owner. 2354-- 2355-- View and Table owner are the same. 2356SET SESSION AUTHORIZATION regress_rls_alice; 2357CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b); 2358GRANT SELECT ON rls_view TO regress_rls_bob; 2359-- Query as role that is not owner of view or table. Should return all records. 2360SET SESSION AUTHORIZATION regress_rls_bob; 2361SELECT * FROM rls_view; 2362NOTICE: f_leak => aba 2363NOTICE: f_leak => bbb 2364NOTICE: f_leak => ccc 2365NOTICE: f_leak => dad 2366 a | b 2367---+----- 2368 1 | aba 2369 2 | bbb 2370 3 | ccc 2371 4 | dad 2372(4 rows) 2373 2374EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 2375 QUERY PLAN 2376--------------------- 2377 Seq Scan on z1 2378 Filter: f_leak(b) 2379(2 rows) 2380 2381-- Query as view/table owner. Should return all records. 2382SET SESSION AUTHORIZATION regress_rls_alice; 2383SELECT * FROM rls_view; 2384NOTICE: f_leak => aba 2385NOTICE: f_leak => bbb 2386NOTICE: f_leak => ccc 2387NOTICE: f_leak => dad 2388 a | b 2389---+----- 2390 1 | aba 2391 2 | bbb 2392 3 | ccc 2393 4 | dad 2394(4 rows) 2395 2396EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 2397 QUERY PLAN 2398--------------------- 2399 Seq Scan on z1 2400 Filter: f_leak(b) 2401(2 rows) 2402 2403DROP VIEW rls_view; 2404-- View and Table owners are different. 2405SET SESSION AUTHORIZATION regress_rls_bob; 2406CREATE VIEW rls_view AS SELECT * FROM z1 WHERE f_leak(b); 2407GRANT SELECT ON rls_view TO regress_rls_alice; 2408-- Query as role that is not owner of view but is owner of table. 2409-- Should return records based on view owner policies. 2410SET SESSION AUTHORIZATION regress_rls_alice; 2411SELECT * FROM rls_view; 2412NOTICE: f_leak => bbb 2413NOTICE: f_leak => dad 2414 a | b 2415---+----- 2416 2 | bbb 2417 4 | dad 2418(2 rows) 2419 2420EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 2421 QUERY PLAN 2422----------------------------------------- 2423 Seq Scan on z1 2424 Filter: (((a % 2) = 0) AND f_leak(b)) 2425(2 rows) 2426 2427-- Query as role that is not owner of table but is owner of view. 2428-- Should return records based on view owner policies. 2429SET SESSION AUTHORIZATION regress_rls_bob; 2430SELECT * FROM rls_view; 2431NOTICE: f_leak => bbb 2432NOTICE: f_leak => dad 2433 a | b 2434---+----- 2435 2 | bbb 2436 4 | dad 2437(2 rows) 2438 2439EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 2440 QUERY PLAN 2441----------------------------------------- 2442 Seq Scan on z1 2443 Filter: (((a % 2) = 0) AND f_leak(b)) 2444(2 rows) 2445 2446-- Query as role that is not the owner of the table or view without permissions. 2447SET SESSION AUTHORIZATION regress_rls_carol; 2448SELECT * FROM rls_view; --fail - permission denied. 2449ERROR: permission denied for relation rls_view 2450EXPLAIN (COSTS OFF) SELECT * FROM rls_view; --fail - permission denied. 2451ERROR: permission denied for relation rls_view 2452-- Query as role that is not the owner of the table or view with permissions. 2453SET SESSION AUTHORIZATION regress_rls_bob; 2454GRANT SELECT ON rls_view TO regress_rls_carol; 2455SELECT * FROM rls_view; 2456NOTICE: f_leak => bbb 2457NOTICE: f_leak => dad 2458 a | b 2459---+----- 2460 2 | bbb 2461 4 | dad 2462(2 rows) 2463 2464EXPLAIN (COSTS OFF) SELECT * FROM rls_view; 2465 QUERY PLAN 2466----------------------------------------- 2467 Seq Scan on z1 2468 Filter: (((a % 2) = 0) AND f_leak(b)) 2469(2 rows) 2470 2471SET SESSION AUTHORIZATION regress_rls_bob; 2472DROP VIEW rls_view; 2473-- 2474-- Command specific 2475-- 2476SET SESSION AUTHORIZATION regress_rls_alice; 2477CREATE TABLE x1 (a int, b text, c text); 2478GRANT ALL ON x1 TO PUBLIC; 2479INSERT INTO x1 VALUES 2480 (1, 'abc', 'regress_rls_bob'), 2481 (2, 'bcd', 'regress_rls_bob'), 2482 (3, 'cde', 'regress_rls_carol'), 2483 (4, 'def', 'regress_rls_carol'), 2484 (5, 'efg', 'regress_rls_bob'), 2485 (6, 'fgh', 'regress_rls_bob'), 2486 (7, 'fgh', 'regress_rls_carol'), 2487 (8, 'fgh', 'regress_rls_carol'); 2488CREATE POLICY p0 ON x1 FOR ALL USING (c = current_user); 2489CREATE POLICY p1 ON x1 FOR SELECT USING (a % 2 = 0); 2490CREATE POLICY p2 ON x1 FOR INSERT WITH CHECK (a % 2 = 1); 2491CREATE POLICY p3 ON x1 FOR UPDATE USING (a % 2 = 0); 2492CREATE POLICY p4 ON x1 FOR DELETE USING (a < 8); 2493ALTER TABLE x1 ENABLE ROW LEVEL SECURITY; 2494SET SESSION AUTHORIZATION regress_rls_bob; 2495SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC; 2496NOTICE: f_leak => abc 2497NOTICE: f_leak => bcd 2498NOTICE: f_leak => def 2499NOTICE: f_leak => efg 2500NOTICE: f_leak => fgh 2501NOTICE: f_leak => fgh 2502 a | b | c 2503---+-----+------------------- 2504 1 | abc | regress_rls_bob 2505 2 | bcd | regress_rls_bob 2506 4 | def | regress_rls_carol 2507 5 | efg | regress_rls_bob 2508 6 | fgh | regress_rls_bob 2509 8 | fgh | regress_rls_carol 2510(6 rows) 2511 2512UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *; 2513NOTICE: f_leak => abc 2514NOTICE: f_leak => bcd 2515NOTICE: f_leak => def 2516NOTICE: f_leak => efg 2517NOTICE: f_leak => fgh 2518NOTICE: f_leak => fgh 2519 a | b | c 2520---+----------+------------------- 2521 1 | abc_updt | regress_rls_bob 2522 2 | bcd_updt | regress_rls_bob 2523 4 | def_updt | regress_rls_carol 2524 5 | efg_updt | regress_rls_bob 2525 6 | fgh_updt | regress_rls_bob 2526 8 | fgh_updt | regress_rls_carol 2527(6 rows) 2528 2529SET SESSION AUTHORIZATION regress_rls_carol; 2530SELECT * FROM x1 WHERE f_leak(b) ORDER BY a ASC; 2531NOTICE: f_leak => cde 2532NOTICE: f_leak => fgh 2533NOTICE: f_leak => bcd_updt 2534NOTICE: f_leak => def_updt 2535NOTICE: f_leak => fgh_updt 2536NOTICE: f_leak => fgh_updt 2537 a | b | c 2538---+----------+------------------- 2539 2 | bcd_updt | regress_rls_bob 2540 3 | cde | regress_rls_carol 2541 4 | def_updt | regress_rls_carol 2542 6 | fgh_updt | regress_rls_bob 2543 7 | fgh | regress_rls_carol 2544 8 | fgh_updt | regress_rls_carol 2545(6 rows) 2546 2547UPDATE x1 SET b = b || '_updt' WHERE f_leak(b) RETURNING *; 2548NOTICE: f_leak => cde 2549NOTICE: f_leak => fgh 2550NOTICE: f_leak => bcd_updt 2551NOTICE: f_leak => def_updt 2552NOTICE: f_leak => fgh_updt 2553NOTICE: f_leak => fgh_updt 2554 a | b | c 2555---+---------------+------------------- 2556 3 | cde_updt | regress_rls_carol 2557 7 | fgh_updt | regress_rls_carol 2558 2 | bcd_updt_updt | regress_rls_bob 2559 4 | def_updt_updt | regress_rls_carol 2560 6 | fgh_updt_updt | regress_rls_bob 2561 8 | fgh_updt_updt | regress_rls_carol 2562(6 rows) 2563 2564DELETE FROM x1 WHERE f_leak(b) RETURNING *; 2565NOTICE: f_leak => cde_updt 2566NOTICE: f_leak => fgh_updt 2567NOTICE: f_leak => bcd_updt_updt 2568NOTICE: f_leak => def_updt_updt 2569NOTICE: f_leak => fgh_updt_updt 2570NOTICE: f_leak => fgh_updt_updt 2571 a | b | c 2572---+---------------+------------------- 2573 3 | cde_updt | regress_rls_carol 2574 7 | fgh_updt | regress_rls_carol 2575 2 | bcd_updt_updt | regress_rls_bob 2576 4 | def_updt_updt | regress_rls_carol 2577 6 | fgh_updt_updt | regress_rls_bob 2578 8 | fgh_updt_updt | regress_rls_carol 2579(6 rows) 2580 2581-- 2582-- Duplicate Policy Names 2583-- 2584SET SESSION AUTHORIZATION regress_rls_alice; 2585CREATE TABLE y1 (a int, b text); 2586CREATE TABLE y2 (a int, b text); 2587GRANT ALL ON y1, y2 TO regress_rls_bob; 2588CREATE POLICY p1 ON y1 FOR ALL USING (a % 2 = 0); 2589CREATE POLICY p2 ON y1 FOR SELECT USING (a > 2); 2590CREATE POLICY p1 ON y1 FOR SELECT USING (a % 2 = 1); --fail 2591ERROR: policy "p1" for table "y1" already exists 2592CREATE POLICY p1 ON y2 FOR ALL USING (a % 2 = 0); --OK 2593ALTER TABLE y1 ENABLE ROW LEVEL SECURITY; 2594ALTER TABLE y2 ENABLE ROW LEVEL SECURITY; 2595-- 2596-- Expression structure with SBV 2597-- 2598-- Create view as table owner. RLS should NOT be applied. 2599SET SESSION AUTHORIZATION regress_rls_alice; 2600CREATE VIEW rls_sbv WITH (security_barrier) AS 2601 SELECT * FROM y1 WHERE f_leak(b); 2602EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1); 2603 QUERY PLAN 2604----------------------------------- 2605 Seq Scan on y1 2606 Filter: (f_leak(b) AND (a = 1)) 2607(2 rows) 2608 2609DROP VIEW rls_sbv; 2610-- Create view as role that does not own table. RLS should be applied. 2611SET SESSION AUTHORIZATION regress_rls_bob; 2612CREATE VIEW rls_sbv WITH (security_barrier) AS 2613 SELECT * FROM y1 WHERE f_leak(b); 2614EXPLAIN (COSTS OFF) SELECT * FROM rls_sbv WHERE (a = 1); 2615 QUERY PLAN 2616------------------------------------------------------------------ 2617 Seq Scan on y1 2618 Filter: ((a = 1) AND ((a > 2) OR ((a % 2) = 0)) AND f_leak(b)) 2619(2 rows) 2620 2621DROP VIEW rls_sbv; 2622-- 2623-- Expression structure 2624-- 2625SET SESSION AUTHORIZATION regress_rls_alice; 2626INSERT INTO y2 (SELECT x, md5(x::text) FROM generate_series(0,20) x); 2627CREATE POLICY p2 ON y2 USING (a % 3 = 0); 2628CREATE POLICY p3 ON y2 USING (a % 4 = 0); 2629SET SESSION AUTHORIZATION regress_rls_bob; 2630SELECT * FROM y2 WHERE f_leak(b); 2631NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da 2632NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c 2633NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3 2634NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c 2635NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc 2636NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d 2637NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26 2638NOTICE: f_leak => d3d9446802a44259755d38e6d163e820 2639NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710 2640NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56 2641NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3 2642NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf 2643NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23 2644NOTICE: f_leak => 98f13708210194c475687be6106a3b84 2645 a | b 2646----+---------------------------------- 2647 0 | cfcd208495d565ef66e7dff9f98764da 2648 2 | c81e728d9d4c2f636f067f89cc14862c 2649 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 2650 4 | a87ff679a2f3e71d9181a67b7542122c 2651 6 | 1679091c5a880faf6fb5e6087eb1b2dc 2652 8 | c9f0f895fb98ab9159f51fd0297e236d 2653 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 2654 10 | d3d9446802a44259755d38e6d163e820 2655 12 | c20ad4d76fe97759aa27a0c99bff6710 2656 14 | aab3238922bcc25a6f606eb525ffdc56 2657 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 2658 16 | c74d97b01eae257e44aa9d5bade97baf 2659 18 | 6f4922f45568161a8cdf4ad2299f6d23 2660 20 | 98f13708210194c475687be6106a3b84 2661(14 rows) 2662 2663EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak(b); 2664 QUERY PLAN 2665----------------------------------------------------------------------------- 2666 Seq Scan on y2 2667 Filter: ((((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)) AND f_leak(b)) 2668(2 rows) 2669 2670-- 2671-- Qual push-down of leaky functions, when not referring to table 2672-- 2673SELECT * FROM y2 WHERE f_leak('abc'); 2674NOTICE: f_leak => abc 2675NOTICE: f_leak => abc 2676NOTICE: f_leak => abc 2677NOTICE: f_leak => abc 2678NOTICE: f_leak => abc 2679NOTICE: f_leak => abc 2680NOTICE: f_leak => abc 2681NOTICE: f_leak => abc 2682NOTICE: f_leak => abc 2683NOTICE: f_leak => abc 2684NOTICE: f_leak => abc 2685NOTICE: f_leak => abc 2686NOTICE: f_leak => abc 2687NOTICE: f_leak => abc 2688NOTICE: f_leak => abc 2689NOTICE: f_leak => abc 2690NOTICE: f_leak => abc 2691NOTICE: f_leak => abc 2692NOTICE: f_leak => abc 2693NOTICE: f_leak => abc 2694NOTICE: f_leak => abc 2695 a | b 2696----+---------------------------------- 2697 0 | cfcd208495d565ef66e7dff9f98764da 2698 2 | c81e728d9d4c2f636f067f89cc14862c 2699 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 2700 4 | a87ff679a2f3e71d9181a67b7542122c 2701 6 | 1679091c5a880faf6fb5e6087eb1b2dc 2702 8 | c9f0f895fb98ab9159f51fd0297e236d 2703 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 2704 10 | d3d9446802a44259755d38e6d163e820 2705 12 | c20ad4d76fe97759aa27a0c99bff6710 2706 14 | aab3238922bcc25a6f606eb525ffdc56 2707 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 2708 16 | c74d97b01eae257e44aa9d5bade97baf 2709 18 | 6f4922f45568161a8cdf4ad2299f6d23 2710 20 | 98f13708210194c475687be6106a3b84 2711(14 rows) 2712 2713EXPLAIN (COSTS OFF) SELECT * FROM y2 WHERE f_leak('abc'); 2714 QUERY PLAN 2715--------------------------------------------------------------------------------------- 2716 Seq Scan on y2 2717 Filter: (f_leak('abc'::text) AND (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0))) 2718(2 rows) 2719 2720CREATE TABLE test_qual_pushdown ( 2721 abc text 2722); 2723INSERT INTO test_qual_pushdown VALUES ('abc'),('def'); 2724SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc); 2725NOTICE: f_leak => abc 2726NOTICE: f_leak => def 2727 a | b | abc 2728---+---+----- 2729(0 rows) 2730 2731EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(abc); 2732 QUERY PLAN 2733------------------------------------------------------------------------- 2734 Hash Join 2735 Hash Cond: (test_qual_pushdown.abc = y2.b) 2736 -> Seq Scan on test_qual_pushdown 2737 Filter: f_leak(abc) 2738 -> Hash 2739 -> Seq Scan on y2 2740 Filter: (((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)) 2741(7 rows) 2742 2743SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b); 2744NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da 2745NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c 2746NOTICE: f_leak => eccbc87e4b5ce2fe28308fd9f2a7baf3 2747NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c 2748NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc 2749NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d 2750NOTICE: f_leak => 45c48cce2e2d7fbdea1afc51c7c6ad26 2751NOTICE: f_leak => d3d9446802a44259755d38e6d163e820 2752NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710 2753NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56 2754NOTICE: f_leak => 9bf31c7ff062936a96d3c8bd1f8f2ff3 2755NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf 2756NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23 2757NOTICE: f_leak => 98f13708210194c475687be6106a3b84 2758 a | b | abc 2759---+---+----- 2760(0 rows) 2761 2762EXPLAIN (COSTS OFF) SELECT * FROM y2 JOIN test_qual_pushdown ON (b = abc) WHERE f_leak(b); 2763 QUERY PLAN 2764----------------------------------------------------------------------------------------- 2765 Hash Join 2766 Hash Cond: (test_qual_pushdown.abc = y2.b) 2767 -> Seq Scan on test_qual_pushdown 2768 -> Hash 2769 -> Seq Scan on y2 2770 Filter: ((((a % 4) = 0) OR ((a % 3) = 0) OR ((a % 2) = 0)) AND f_leak(b)) 2771(6 rows) 2772 2773DROP TABLE test_qual_pushdown; 2774-- 2775-- Plancache invalidate on user change. 2776-- 2777RESET SESSION AUTHORIZATION; 2778\set VERBOSITY terse \\ -- suppress cascade details 2779DROP TABLE t1 CASCADE; 2780NOTICE: drop cascades to 2 other objects 2781\set VERBOSITY default 2782CREATE TABLE t1 (a integer); 2783GRANT SELECT ON t1 TO regress_rls_bob, regress_rls_carol; 2784CREATE POLICY p1 ON t1 TO regress_rls_bob USING ((a % 2) = 0); 2785CREATE POLICY p2 ON t1 TO regress_rls_carol USING ((a % 4) = 0); 2786ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; 2787-- Prepare as regress_rls_bob 2788SET ROLE regress_rls_bob; 2789PREPARE role_inval AS SELECT * FROM t1; 2790-- Check plan 2791EXPLAIN (COSTS OFF) EXECUTE role_inval; 2792 QUERY PLAN 2793------------------------- 2794 Seq Scan on t1 2795 Filter: ((a % 2) = 0) 2796(2 rows) 2797 2798-- Change to regress_rls_carol 2799SET ROLE regress_rls_carol; 2800-- Check plan- should be different 2801EXPLAIN (COSTS OFF) EXECUTE role_inval; 2802 QUERY PLAN 2803------------------------- 2804 Seq Scan on t1 2805 Filter: ((a % 4) = 0) 2806(2 rows) 2807 2808-- Change back to regress_rls_bob 2809SET ROLE regress_rls_bob; 2810-- Check plan- should be back to original 2811EXPLAIN (COSTS OFF) EXECUTE role_inval; 2812 QUERY PLAN 2813------------------------- 2814 Seq Scan on t1 2815 Filter: ((a % 2) = 0) 2816(2 rows) 2817 2818-- 2819-- CTE and RLS 2820-- 2821RESET SESSION AUTHORIZATION; 2822DROP TABLE t1 CASCADE; 2823CREATE TABLE t1 (a integer, b text); 2824CREATE POLICY p1 ON t1 USING (a % 2 = 0); 2825ALTER TABLE t1 ENABLE ROW LEVEL SECURITY; 2826GRANT ALL ON t1 TO regress_rls_bob; 2827INSERT INTO t1 (SELECT x, md5(x::text) FROM generate_series(0,20) x); 2828SET SESSION AUTHORIZATION regress_rls_bob; 2829WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1; 2830NOTICE: f_leak => cfcd208495d565ef66e7dff9f98764da 2831NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c 2832NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c 2833NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc 2834NOTICE: f_leak => c9f0f895fb98ab9159f51fd0297e236d 2835NOTICE: f_leak => d3d9446802a44259755d38e6d163e820 2836NOTICE: f_leak => c20ad4d76fe97759aa27a0c99bff6710 2837NOTICE: f_leak => aab3238922bcc25a6f606eb525ffdc56 2838NOTICE: f_leak => c74d97b01eae257e44aa9d5bade97baf 2839NOTICE: f_leak => 6f4922f45568161a8cdf4ad2299f6d23 2840NOTICE: f_leak => 98f13708210194c475687be6106a3b84 2841 a | b 2842----+---------------------------------- 2843 0 | cfcd208495d565ef66e7dff9f98764da 2844 2 | c81e728d9d4c2f636f067f89cc14862c 2845 4 | a87ff679a2f3e71d9181a67b7542122c 2846 6 | 1679091c5a880faf6fb5e6087eb1b2dc 2847 8 | c9f0f895fb98ab9159f51fd0297e236d 2848 10 | d3d9446802a44259755d38e6d163e820 2849 12 | c20ad4d76fe97759aa27a0c99bff6710 2850 14 | aab3238922bcc25a6f606eb525ffdc56 2851 16 | c74d97b01eae257e44aa9d5bade97baf 2852 18 | 6f4922f45568161a8cdf4ad2299f6d23 2853 20 | 98f13708210194c475687be6106a3b84 2854(11 rows) 2855 2856EXPLAIN (COSTS OFF) WITH cte1 AS (SELECT * FROM t1 WHERE f_leak(b)) SELECT * FROM cte1; 2857 QUERY PLAN 2858------------------------------------------------- 2859 CTE Scan on cte1 2860 CTE cte1 2861 -> Seq Scan on t1 2862 Filter: (((a % 2) = 0) AND f_leak(b)) 2863(4 rows) 2864 2865WITH cte1 AS (UPDATE t1 SET a = a + 1 RETURNING *) SELECT * FROM cte1; --fail 2866ERROR: new row violates row-level security policy for table "t1" 2867WITH cte1 AS (UPDATE t1 SET a = a RETURNING *) SELECT * FROM cte1; --ok 2868 a | b 2869----+---------------------------------- 2870 0 | cfcd208495d565ef66e7dff9f98764da 2871 2 | c81e728d9d4c2f636f067f89cc14862c 2872 4 | a87ff679a2f3e71d9181a67b7542122c 2873 6 | 1679091c5a880faf6fb5e6087eb1b2dc 2874 8 | c9f0f895fb98ab9159f51fd0297e236d 2875 10 | d3d9446802a44259755d38e6d163e820 2876 12 | c20ad4d76fe97759aa27a0c99bff6710 2877 14 | aab3238922bcc25a6f606eb525ffdc56 2878 16 | c74d97b01eae257e44aa9d5bade97baf 2879 18 | 6f4922f45568161a8cdf4ad2299f6d23 2880 20 | 98f13708210194c475687be6106a3b84 2881(11 rows) 2882 2883WITH cte1 AS (INSERT INTO t1 VALUES (21, 'Fail') RETURNING *) SELECT * FROM cte1; --fail 2884ERROR: new row violates row-level security policy for table "t1" 2885WITH cte1 AS (INSERT INTO t1 VALUES (20, 'Success') RETURNING *) SELECT * FROM cte1; --ok 2886 a | b 2887----+--------- 2888 20 | Success 2889(1 row) 2890 2891-- 2892-- Rename Policy 2893-- 2894RESET SESSION AUTHORIZATION; 2895ALTER POLICY p1 ON t1 RENAME TO p1; --fail 2896ERROR: policy "p1" for table "t1" already exists 2897SELECT polname, relname 2898 FROM pg_policy pol 2899 JOIN pg_class pc ON (pc.oid = pol.polrelid) 2900 WHERE relname = 't1'; 2901 polname | relname 2902---------+--------- 2903 p1 | t1 2904(1 row) 2905 2906ALTER POLICY p1 ON t1 RENAME TO p2; --ok 2907SELECT polname, relname 2908 FROM pg_policy pol 2909 JOIN pg_class pc ON (pc.oid = pol.polrelid) 2910 WHERE relname = 't1'; 2911 polname | relname 2912---------+--------- 2913 p2 | t1 2914(1 row) 2915 2916-- 2917-- Check INSERT SELECT 2918-- 2919SET SESSION AUTHORIZATION regress_rls_bob; 2920CREATE TABLE t2 (a integer, b text); 2921INSERT INTO t2 (SELECT * FROM t1); 2922EXPLAIN (COSTS OFF) INSERT INTO t2 (SELECT * FROM t1); 2923 QUERY PLAN 2924------------------------------- 2925 Insert on t2 2926 -> Seq Scan on t1 2927 Filter: ((a % 2) = 0) 2928(3 rows) 2929 2930SELECT * FROM t2; 2931 a | b 2932----+---------------------------------- 2933 0 | cfcd208495d565ef66e7dff9f98764da 2934 2 | c81e728d9d4c2f636f067f89cc14862c 2935 4 | a87ff679a2f3e71d9181a67b7542122c 2936 6 | 1679091c5a880faf6fb5e6087eb1b2dc 2937 8 | c9f0f895fb98ab9159f51fd0297e236d 2938 10 | d3d9446802a44259755d38e6d163e820 2939 12 | c20ad4d76fe97759aa27a0c99bff6710 2940 14 | aab3238922bcc25a6f606eb525ffdc56 2941 16 | c74d97b01eae257e44aa9d5bade97baf 2942 18 | 6f4922f45568161a8cdf4ad2299f6d23 2943 20 | 98f13708210194c475687be6106a3b84 2944 20 | Success 2945(12 rows) 2946 2947EXPLAIN (COSTS OFF) SELECT * FROM t2; 2948 QUERY PLAN 2949---------------- 2950 Seq Scan on t2 2951(1 row) 2952 2953CREATE TABLE t3 AS SELECT * FROM t1; 2954SELECT * FROM t3; 2955 a | b 2956----+---------------------------------- 2957 0 | cfcd208495d565ef66e7dff9f98764da 2958 2 | c81e728d9d4c2f636f067f89cc14862c 2959 4 | a87ff679a2f3e71d9181a67b7542122c 2960 6 | 1679091c5a880faf6fb5e6087eb1b2dc 2961 8 | c9f0f895fb98ab9159f51fd0297e236d 2962 10 | d3d9446802a44259755d38e6d163e820 2963 12 | c20ad4d76fe97759aa27a0c99bff6710 2964 14 | aab3238922bcc25a6f606eb525ffdc56 2965 16 | c74d97b01eae257e44aa9d5bade97baf 2966 18 | 6f4922f45568161a8cdf4ad2299f6d23 2967 20 | 98f13708210194c475687be6106a3b84 2968 20 | Success 2969(12 rows) 2970 2971SELECT * INTO t4 FROM t1; 2972SELECT * FROM t4; 2973 a | b 2974----+---------------------------------- 2975 0 | cfcd208495d565ef66e7dff9f98764da 2976 2 | c81e728d9d4c2f636f067f89cc14862c 2977 4 | a87ff679a2f3e71d9181a67b7542122c 2978 6 | 1679091c5a880faf6fb5e6087eb1b2dc 2979 8 | c9f0f895fb98ab9159f51fd0297e236d 2980 10 | d3d9446802a44259755d38e6d163e820 2981 12 | c20ad4d76fe97759aa27a0c99bff6710 2982 14 | aab3238922bcc25a6f606eb525ffdc56 2983 16 | c74d97b01eae257e44aa9d5bade97baf 2984 18 | 6f4922f45568161a8cdf4ad2299f6d23 2985 20 | 98f13708210194c475687be6106a3b84 2986 20 | Success 2987(12 rows) 2988 2989-- 2990-- RLS with JOIN 2991-- 2992SET SESSION AUTHORIZATION regress_rls_alice; 2993CREATE TABLE blog (id integer, author text, post text); 2994CREATE TABLE comment (blog_id integer, message text); 2995GRANT ALL ON blog, comment TO regress_rls_bob; 2996CREATE POLICY blog_1 ON blog USING (id % 2 = 0); 2997ALTER TABLE blog ENABLE ROW LEVEL SECURITY; 2998INSERT INTO blog VALUES 2999 (1, 'alice', 'blog #1'), 3000 (2, 'bob', 'blog #1'), 3001 (3, 'alice', 'blog #2'), 3002 (4, 'alice', 'blog #3'), 3003 (5, 'john', 'blog #1'); 3004INSERT INTO comment VALUES 3005 (1, 'cool blog'), 3006 (1, 'fun blog'), 3007 (3, 'crazy blog'), 3008 (5, 'what?'), 3009 (4, 'insane!'), 3010 (2, 'who did it?'); 3011SET SESSION AUTHORIZATION regress_rls_bob; 3012-- Check RLS JOIN with Non-RLS. 3013SELECT id, author, message FROM blog JOIN comment ON id = blog_id; 3014 id | author | message 3015----+--------+------------- 3016 4 | alice | insane! 3017 2 | bob | who did it? 3018(2 rows) 3019 3020-- Check Non-RLS JOIN with RLS. 3021SELECT id, author, message FROM comment JOIN blog ON id = blog_id; 3022 id | author | message 3023----+--------+------------- 3024 4 | alice | insane! 3025 2 | bob | who did it? 3026(2 rows) 3027 3028SET SESSION AUTHORIZATION regress_rls_alice; 3029CREATE POLICY comment_1 ON comment USING (blog_id < 4); 3030ALTER TABLE comment ENABLE ROW LEVEL SECURITY; 3031SET SESSION AUTHORIZATION regress_rls_bob; 3032-- Check RLS JOIN RLS 3033SELECT id, author, message FROM blog JOIN comment ON id = blog_id; 3034 id | author | message 3035----+--------+------------- 3036 2 | bob | who did it? 3037(1 row) 3038 3039SELECT id, author, message FROM comment JOIN blog ON id = blog_id; 3040 id | author | message 3041----+--------+------------- 3042 2 | bob | who did it? 3043(1 row) 3044 3045SET SESSION AUTHORIZATION regress_rls_alice; 3046DROP TABLE blog, comment; 3047-- 3048-- Default Deny Policy 3049-- 3050RESET SESSION AUTHORIZATION; 3051DROP POLICY p2 ON t1; 3052ALTER TABLE t1 OWNER TO regress_rls_alice; 3053-- Check that default deny does not apply to superuser. 3054RESET SESSION AUTHORIZATION; 3055SELECT * FROM t1; 3056 a | b 3057----+---------------------------------- 3058 1 | c4ca4238a0b923820dcc509a6f75849b 3059 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 3060 5 | e4da3b7fbbce2345d7772b0674a318d5 3061 7 | 8f14e45fceea167a5a36dedd4bea2543 3062 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 3063 11 | 6512bd43d9caa6e02c990b0a82652dca 3064 13 | c51ce410c124a10e0db5e4b97fc2af39 3065 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 3066 17 | 70efdf2ec9b086079795c442636b55fb 3067 19 | 1f0e3dad99908345f7439f8ffabdffc4 3068 0 | cfcd208495d565ef66e7dff9f98764da 3069 2 | c81e728d9d4c2f636f067f89cc14862c 3070 4 | a87ff679a2f3e71d9181a67b7542122c 3071 6 | 1679091c5a880faf6fb5e6087eb1b2dc 3072 8 | c9f0f895fb98ab9159f51fd0297e236d 3073 10 | d3d9446802a44259755d38e6d163e820 3074 12 | c20ad4d76fe97759aa27a0c99bff6710 3075 14 | aab3238922bcc25a6f606eb525ffdc56 3076 16 | c74d97b01eae257e44aa9d5bade97baf 3077 18 | 6f4922f45568161a8cdf4ad2299f6d23 3078 20 | 98f13708210194c475687be6106a3b84 3079 20 | Success 3080(22 rows) 3081 3082EXPLAIN (COSTS OFF) SELECT * FROM t1; 3083 QUERY PLAN 3084---------------- 3085 Seq Scan on t1 3086(1 row) 3087 3088-- Check that default deny does not apply to table owner. 3089SET SESSION AUTHORIZATION regress_rls_alice; 3090SELECT * FROM t1; 3091 a | b 3092----+---------------------------------- 3093 1 | c4ca4238a0b923820dcc509a6f75849b 3094 3 | eccbc87e4b5ce2fe28308fd9f2a7baf3 3095 5 | e4da3b7fbbce2345d7772b0674a318d5 3096 7 | 8f14e45fceea167a5a36dedd4bea2543 3097 9 | 45c48cce2e2d7fbdea1afc51c7c6ad26 3098 11 | 6512bd43d9caa6e02c990b0a82652dca 3099 13 | c51ce410c124a10e0db5e4b97fc2af39 3100 15 | 9bf31c7ff062936a96d3c8bd1f8f2ff3 3101 17 | 70efdf2ec9b086079795c442636b55fb 3102 19 | 1f0e3dad99908345f7439f8ffabdffc4 3103 0 | cfcd208495d565ef66e7dff9f98764da 3104 2 | c81e728d9d4c2f636f067f89cc14862c 3105 4 | a87ff679a2f3e71d9181a67b7542122c 3106 6 | 1679091c5a880faf6fb5e6087eb1b2dc 3107 8 | c9f0f895fb98ab9159f51fd0297e236d 3108 10 | d3d9446802a44259755d38e6d163e820 3109 12 | c20ad4d76fe97759aa27a0c99bff6710 3110 14 | aab3238922bcc25a6f606eb525ffdc56 3111 16 | c74d97b01eae257e44aa9d5bade97baf 3112 18 | 6f4922f45568161a8cdf4ad2299f6d23 3113 20 | 98f13708210194c475687be6106a3b84 3114 20 | Success 3115(22 rows) 3116 3117EXPLAIN (COSTS OFF) SELECT * FROM t1; 3118 QUERY PLAN 3119---------------- 3120 Seq Scan on t1 3121(1 row) 3122 3123-- Check that default deny applies to non-owner/non-superuser when RLS on. 3124SET SESSION AUTHORIZATION regress_rls_bob; 3125SET row_security TO ON; 3126SELECT * FROM t1; 3127 a | b 3128---+--- 3129(0 rows) 3130 3131EXPLAIN (COSTS OFF) SELECT * FROM t1; 3132 QUERY PLAN 3133-------------------------- 3134 Result 3135 One-Time Filter: false 3136(2 rows) 3137 3138SET SESSION AUTHORIZATION regress_rls_bob; 3139SELECT * FROM t1; 3140 a | b 3141---+--- 3142(0 rows) 3143 3144EXPLAIN (COSTS OFF) SELECT * FROM t1; 3145 QUERY PLAN 3146-------------------------- 3147 Result 3148 One-Time Filter: false 3149(2 rows) 3150 3151-- 3152-- COPY TO/FROM 3153-- 3154RESET SESSION AUTHORIZATION; 3155DROP TABLE copy_t CASCADE; 3156ERROR: table "copy_t" does not exist 3157CREATE TABLE copy_t (a integer, b text); 3158CREATE POLICY p1 ON copy_t USING (a % 2 = 0); 3159ALTER TABLE copy_t ENABLE ROW LEVEL SECURITY; 3160GRANT ALL ON copy_t TO regress_rls_bob, regress_rls_exempt_user; 3161INSERT INTO copy_t (SELECT x, md5(x::text) FROM generate_series(0,10) x); 3162-- Check COPY TO as Superuser/owner. 3163RESET SESSION AUTHORIZATION; 3164SET row_security TO OFF; 3165COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; 31660,cfcd208495d565ef66e7dff9f98764da 31671,c4ca4238a0b923820dcc509a6f75849b 31682,c81e728d9d4c2f636f067f89cc14862c 31693,eccbc87e4b5ce2fe28308fd9f2a7baf3 31704,a87ff679a2f3e71d9181a67b7542122c 31715,e4da3b7fbbce2345d7772b0674a318d5 31726,1679091c5a880faf6fb5e6087eb1b2dc 31737,8f14e45fceea167a5a36dedd4bea2543 31748,c9f0f895fb98ab9159f51fd0297e236d 31759,45c48cce2e2d7fbdea1afc51c7c6ad26 317610,d3d9446802a44259755d38e6d163e820 3177SET row_security TO ON; 3178COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; 31790,cfcd208495d565ef66e7dff9f98764da 31801,c4ca4238a0b923820dcc509a6f75849b 31812,c81e728d9d4c2f636f067f89cc14862c 31823,eccbc87e4b5ce2fe28308fd9f2a7baf3 31834,a87ff679a2f3e71d9181a67b7542122c 31845,e4da3b7fbbce2345d7772b0674a318d5 31856,1679091c5a880faf6fb5e6087eb1b2dc 31867,8f14e45fceea167a5a36dedd4bea2543 31878,c9f0f895fb98ab9159f51fd0297e236d 31889,45c48cce2e2d7fbdea1afc51c7c6ad26 318910,d3d9446802a44259755d38e6d163e820 3190-- Check COPY TO as user with permissions. 3191SET SESSION AUTHORIZATION regress_rls_bob; 3192SET row_security TO OFF; 3193COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS 3194ERROR: query would be affected by row-level security policy for table "copy_t" 3195SET row_security TO ON; 3196COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok 31970,cfcd208495d565ef66e7dff9f98764da 31982,c81e728d9d4c2f636f067f89cc14862c 31994,a87ff679a2f3e71d9181a67b7542122c 32006,1679091c5a880faf6fb5e6087eb1b2dc 32018,c9f0f895fb98ab9159f51fd0297e236d 320210,d3d9446802a44259755d38e6d163e820 3203-- Check COPY TO as user with permissions and BYPASSRLS 3204SET SESSION AUTHORIZATION regress_rls_exempt_user; 3205SET row_security TO OFF; 3206COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok 32070,cfcd208495d565ef66e7dff9f98764da 32081,c4ca4238a0b923820dcc509a6f75849b 32092,c81e728d9d4c2f636f067f89cc14862c 32103,eccbc87e4b5ce2fe28308fd9f2a7baf3 32114,a87ff679a2f3e71d9181a67b7542122c 32125,e4da3b7fbbce2345d7772b0674a318d5 32136,1679091c5a880faf6fb5e6087eb1b2dc 32147,8f14e45fceea167a5a36dedd4bea2543 32158,c9f0f895fb98ab9159f51fd0297e236d 32169,45c48cce2e2d7fbdea1afc51c7c6ad26 321710,d3d9446802a44259755d38e6d163e820 3218SET row_security TO ON; 3219COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --ok 32200,cfcd208495d565ef66e7dff9f98764da 32211,c4ca4238a0b923820dcc509a6f75849b 32222,c81e728d9d4c2f636f067f89cc14862c 32233,eccbc87e4b5ce2fe28308fd9f2a7baf3 32244,a87ff679a2f3e71d9181a67b7542122c 32255,e4da3b7fbbce2345d7772b0674a318d5 32266,1679091c5a880faf6fb5e6087eb1b2dc 32277,8f14e45fceea167a5a36dedd4bea2543 32288,c9f0f895fb98ab9159f51fd0297e236d 32299,45c48cce2e2d7fbdea1afc51c7c6ad26 323010,d3d9446802a44259755d38e6d163e820 3231-- Check COPY TO as user without permissions. SET row_security TO OFF; 3232SET SESSION AUTHORIZATION regress_rls_carol; 3233SET row_security TO OFF; 3234COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS 3235ERROR: query would be affected by row-level security policy for table "copy_t" 3236SET row_security TO ON; 3237COPY (SELECT * FROM copy_t ORDER BY a ASC) TO STDOUT WITH DELIMITER ','; --fail - permission denied 3238ERROR: permission denied for relation copy_t 3239-- Check COPY relation TO; keep it just one row to avoid reordering issues 3240RESET SESSION AUTHORIZATION; 3241SET row_security TO ON; 3242CREATE TABLE copy_rel_to (a integer, b text); 3243CREATE POLICY p1 ON copy_rel_to USING (a % 2 = 0); 3244ALTER TABLE copy_rel_to ENABLE ROW LEVEL SECURITY; 3245GRANT ALL ON copy_rel_to TO regress_rls_bob, regress_rls_exempt_user; 3246INSERT INTO copy_rel_to VALUES (1, md5('1')); 3247-- Check COPY TO as Superuser/owner. 3248RESET SESSION AUTHORIZATION; 3249SET row_security TO OFF; 3250COPY copy_rel_to TO STDOUT WITH DELIMITER ','; 32511,c4ca4238a0b923820dcc509a6f75849b 3252SET row_security TO ON; 3253COPY copy_rel_to TO STDOUT WITH DELIMITER ','; 32541,c4ca4238a0b923820dcc509a6f75849b 3255-- Check COPY TO as user with permissions. 3256SET SESSION AUTHORIZATION regress_rls_bob; 3257SET row_security TO OFF; 3258COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - would be affected by RLS 3259ERROR: query would be affected by row-level security policy for table "copy_rel_to" 3260SET row_security TO ON; 3261COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok 3262-- Check COPY TO as user with permissions and BYPASSRLS 3263SET SESSION AUTHORIZATION regress_rls_exempt_user; 3264SET row_security TO OFF; 3265COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok 32661,c4ca4238a0b923820dcc509a6f75849b 3267SET row_security TO ON; 3268COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --ok 32691,c4ca4238a0b923820dcc509a6f75849b 3270-- Check COPY TO as user without permissions. SET row_security TO OFF; 3271SET SESSION AUTHORIZATION regress_rls_carol; 3272SET row_security TO OFF; 3273COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied 3274ERROR: permission denied for relation copy_rel_to 3275SET row_security TO ON; 3276COPY copy_rel_to TO STDOUT WITH DELIMITER ','; --fail - permission denied 3277ERROR: permission denied for relation copy_rel_to 3278-- Check COPY FROM as Superuser/owner. 3279RESET SESSION AUTHORIZATION; 3280SET row_security TO OFF; 3281COPY copy_t FROM STDIN; --ok 3282SET row_security TO ON; 3283COPY copy_t FROM STDIN; --ok 3284-- Check COPY FROM as user with permissions. 3285SET SESSION AUTHORIZATION regress_rls_bob; 3286SET row_security TO OFF; 3287COPY copy_t FROM STDIN; --fail - would be affected by RLS. 3288ERROR: query would be affected by row-level security policy for table "copy_t" 3289SET row_security TO ON; 3290COPY copy_t FROM STDIN; --fail - COPY FROM not supported by RLS. 3291ERROR: COPY FROM not supported with row-level security 3292HINT: Use INSERT statements instead. 3293-- Check COPY FROM as user with permissions and BYPASSRLS 3294SET SESSION AUTHORIZATION regress_rls_exempt_user; 3295SET row_security TO ON; 3296COPY copy_t FROM STDIN; --ok 3297-- Check COPY FROM as user without permissions. 3298SET SESSION AUTHORIZATION regress_rls_carol; 3299SET row_security TO OFF; 3300COPY copy_t FROM STDIN; --fail - permission denied. 3301ERROR: permission denied for relation copy_t 3302SET row_security TO ON; 3303COPY copy_t FROM STDIN; --fail - permission denied. 3304ERROR: permission denied for relation copy_t 3305RESET SESSION AUTHORIZATION; 3306DROP TABLE copy_t; 3307DROP TABLE copy_rel_to CASCADE; 3308-- Check WHERE CURRENT OF 3309SET SESSION AUTHORIZATION regress_rls_alice; 3310CREATE TABLE current_check (currentid int, payload text, rlsuser text); 3311GRANT ALL ON current_check TO PUBLIC; 3312INSERT INTO current_check VALUES 3313 (1, 'abc', 'regress_rls_bob'), 3314 (2, 'bcd', 'regress_rls_bob'), 3315 (3, 'cde', 'regress_rls_bob'), 3316 (4, 'def', 'regress_rls_bob'); 3317CREATE POLICY p1 ON current_check FOR SELECT USING (currentid % 2 = 0); 3318CREATE POLICY p2 ON current_check FOR DELETE USING (currentid = 4 AND rlsuser = current_user); 3319CREATE POLICY p3 ON current_check FOR UPDATE USING (currentid = 4) WITH CHECK (rlsuser = current_user); 3320ALTER TABLE current_check ENABLE ROW LEVEL SECURITY; 3321SET SESSION AUTHORIZATION regress_rls_bob; 3322-- Can SELECT even rows 3323SELECT * FROM current_check; 3324 currentid | payload | rlsuser 3325-----------+---------+----------------- 3326 2 | bcd | regress_rls_bob 3327 4 | def | regress_rls_bob 3328(2 rows) 3329 3330-- Cannot UPDATE row 2 3331UPDATE current_check SET payload = payload || '_new' WHERE currentid = 2 RETURNING *; 3332 currentid | payload | rlsuser 3333-----------+---------+--------- 3334(0 rows) 3335 3336BEGIN; 3337DECLARE current_check_cursor SCROLL CURSOR FOR SELECT * FROM current_check; 3338-- Returns rows that can be seen according to SELECT policy, like plain SELECT 3339-- above (even rows) 3340FETCH ABSOLUTE 1 FROM current_check_cursor; 3341 currentid | payload | rlsuser 3342-----------+---------+----------------- 3343 2 | bcd | regress_rls_bob 3344(1 row) 3345 3346-- Still cannot UPDATE row 2 through cursor 3347UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *; 3348 currentid | payload | rlsuser 3349-----------+---------+--------- 3350(0 rows) 3351 3352-- Can update row 4 through cursor, which is the next visible row 3353FETCH RELATIVE 1 FROM current_check_cursor; 3354 currentid | payload | rlsuser 3355-----------+---------+----------------- 3356 4 | def | regress_rls_bob 3357(1 row) 3358 3359UPDATE current_check SET payload = payload || '_new' WHERE CURRENT OF current_check_cursor RETURNING *; 3360 currentid | payload | rlsuser 3361-----------+---------+----------------- 3362 4 | def_new | regress_rls_bob 3363(1 row) 3364 3365SELECT * FROM current_check; 3366 currentid | payload | rlsuser 3367-----------+---------+----------------- 3368 2 | bcd | regress_rls_bob 3369 4 | def_new | regress_rls_bob 3370(2 rows) 3371 3372-- Plan should be a subquery TID scan 3373EXPLAIN (COSTS OFF) UPDATE current_check SET payload = payload WHERE CURRENT OF current_check_cursor; 3374 QUERY PLAN 3375------------------------------------------------------------- 3376 Update on current_check 3377 -> Tid Scan on current_check 3378 TID Cond: CURRENT OF current_check_cursor 3379 Filter: ((currentid = 4) AND ((currentid % 2) = 0)) 3380(4 rows) 3381 3382-- Similarly can only delete row 4 3383FETCH ABSOLUTE 1 FROM current_check_cursor; 3384 currentid | payload | rlsuser 3385-----------+---------+----------------- 3386 2 | bcd | regress_rls_bob 3387(1 row) 3388 3389DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *; 3390 currentid | payload | rlsuser 3391-----------+---------+--------- 3392(0 rows) 3393 3394FETCH RELATIVE 1 FROM current_check_cursor; 3395 currentid | payload | rlsuser 3396-----------+---------+----------------- 3397 4 | def | regress_rls_bob 3398(1 row) 3399 3400DELETE FROM current_check WHERE CURRENT OF current_check_cursor RETURNING *; 3401 currentid | payload | rlsuser 3402-----------+---------+----------------- 3403 4 | def_new | regress_rls_bob 3404(1 row) 3405 3406SELECT * FROM current_check; 3407 currentid | payload | rlsuser 3408-----------+---------+----------------- 3409 2 | bcd | regress_rls_bob 3410(1 row) 3411 3412COMMIT; 3413-- 3414-- check pg_stats view filtering 3415-- 3416SET row_security TO ON; 3417SET SESSION AUTHORIZATION regress_rls_alice; 3418ANALYZE current_check; 3419-- Stats visible 3420SELECT row_security_active('current_check'); 3421 row_security_active 3422--------------------- 3423 f 3424(1 row) 3425 3426SELECT attname, most_common_vals FROM pg_stats 3427 WHERE tablename = 'current_check' 3428 ORDER BY 1; 3429 attname | most_common_vals 3430-----------+------------------- 3431 currentid | 3432 payload | 3433 rlsuser | {regress_rls_bob} 3434(3 rows) 3435 3436SET SESSION AUTHORIZATION regress_rls_bob; 3437-- Stats not visible 3438SELECT row_security_active('current_check'); 3439 row_security_active 3440--------------------- 3441 t 3442(1 row) 3443 3444SELECT attname, most_common_vals FROM pg_stats 3445 WHERE tablename = 'current_check' 3446 ORDER BY 1; 3447 attname | most_common_vals 3448---------+------------------ 3449(0 rows) 3450 3451-- 3452-- Collation support 3453-- 3454BEGIN; 3455CREATE TABLE coll_t (c) AS VALUES ('bar'::text); 3456CREATE POLICY coll_p ON coll_t USING (c < ('foo'::text COLLATE "C")); 3457ALTER TABLE coll_t ENABLE ROW LEVEL SECURITY; 3458GRANT SELECT ON coll_t TO regress_rls_alice; 3459SELECT (string_to_array(polqual, ':'))[7] AS inputcollid FROM pg_policy WHERE polrelid = 'coll_t'::regclass; 3460 inputcollid 3461------------------ 3462 inputcollid 950 3463(1 row) 3464 3465SET SESSION AUTHORIZATION regress_rls_alice; 3466SELECT * FROM coll_t; 3467 c 3468----- 3469 bar 3470(1 row) 3471 3472ROLLBACK; 3473-- 3474-- Shared Object Dependencies 3475-- 3476RESET SESSION AUTHORIZATION; 3477BEGIN; 3478CREATE ROLE regress_rls_eve; 3479CREATE ROLE regress_rls_frank; 3480CREATE TABLE tbl1 (c) AS VALUES ('bar'::text); 3481GRANT SELECT ON TABLE tbl1 TO regress_rls_eve; 3482CREATE POLICY P ON tbl1 TO regress_rls_eve, regress_rls_frank USING (true); 3483SELECT refclassid::regclass, deptype 3484 FROM pg_depend 3485 WHERE classid = 'pg_policy'::regclass 3486 AND refobjid = 'tbl1'::regclass; 3487 refclassid | deptype 3488------------+--------- 3489 pg_class | a 3490(1 row) 3491 3492SELECT refclassid::regclass, deptype 3493 FROM pg_shdepend 3494 WHERE classid = 'pg_policy'::regclass 3495 AND refobjid IN ('regress_rls_eve'::regrole, 'regress_rls_frank'::regrole); 3496 refclassid | deptype 3497------------+--------- 3498 pg_authid | r 3499 pg_authid | r 3500(2 rows) 3501 3502SAVEPOINT q; 3503DROP ROLE regress_rls_eve; --fails due to dependency on POLICY p 3504ERROR: role "regress_rls_eve" cannot be dropped because some objects depend on it 3505DETAIL: target of policy p on table tbl1 3506privileges for table tbl1 3507ROLLBACK TO q; 3508ALTER POLICY p ON tbl1 TO regress_rls_frank USING (true); 3509SAVEPOINT q; 3510DROP ROLE regress_rls_eve; --fails due to dependency on GRANT SELECT 3511ERROR: role "regress_rls_eve" cannot be dropped because some objects depend on it 3512DETAIL: privileges for table tbl1 3513ROLLBACK TO q; 3514REVOKE ALL ON TABLE tbl1 FROM regress_rls_eve; 3515SAVEPOINT q; 3516DROP ROLE regress_rls_eve; --succeeds 3517ROLLBACK TO q; 3518SAVEPOINT q; 3519DROP ROLE regress_rls_frank; --fails due to dependency on POLICY p 3520ERROR: role "regress_rls_frank" cannot be dropped because some objects depend on it 3521DETAIL: target of policy p on table tbl1 3522ROLLBACK TO q; 3523DROP POLICY p ON tbl1; 3524SAVEPOINT q; 3525DROP ROLE regress_rls_frank; -- succeeds 3526ROLLBACK TO q; 3527ROLLBACK; -- cleanup 3528-- 3529-- Converting table to view 3530-- 3531BEGIN; 3532CREATE TABLE t (c int); 3533CREATE POLICY p ON t USING (c % 2 = 1); 3534ALTER TABLE t ENABLE ROW LEVEL SECURITY; 3535SAVEPOINT q; 3536CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD 3537 SELECT * FROM generate_series(1,5) t0(c); -- fails due to row level security enabled 3538ERROR: could not convert table "t" to a view because it has row security enabled 3539ROLLBACK TO q; 3540ALTER TABLE t DISABLE ROW LEVEL SECURITY; 3541SAVEPOINT q; 3542CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD 3543 SELECT * FROM generate_series(1,5) t0(c); -- fails due to policy p on t 3544ERROR: could not convert table "t" to a view because it has row security policies 3545ROLLBACK TO q; 3546DROP POLICY p ON t; 3547CREATE RULE "_RETURN" AS ON SELECT TO t DO INSTEAD 3548 SELECT * FROM generate_series(1,5) t0(c); -- succeeds 3549ROLLBACK; 3550-- 3551-- Policy expression handling 3552-- 3553BEGIN; 3554CREATE TABLE t (c) AS VALUES ('bar'::text); 3555CREATE POLICY p ON t USING (max(c)); -- fails: aggregate functions are not allowed in policy expressions 3556ERROR: aggregate functions are not allowed in policy expressions 3557ROLLBACK; 3558-- 3559-- Non-target relations are only subject to SELECT policies 3560-- 3561SET SESSION AUTHORIZATION regress_rls_alice; 3562CREATE TABLE r1 (a int); 3563CREATE TABLE r2 (a int); 3564INSERT INTO r1 VALUES (10), (20); 3565INSERT INTO r2 VALUES (10), (20); 3566GRANT ALL ON r1, r2 TO regress_rls_bob; 3567CREATE POLICY p1 ON r1 USING (true); 3568ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 3569CREATE POLICY p1 ON r2 FOR SELECT USING (true); 3570CREATE POLICY p2 ON r2 FOR INSERT WITH CHECK (false); 3571CREATE POLICY p3 ON r2 FOR UPDATE USING (false); 3572CREATE POLICY p4 ON r2 FOR DELETE USING (false); 3573ALTER TABLE r2 ENABLE ROW LEVEL SECURITY; 3574SET SESSION AUTHORIZATION regress_rls_bob; 3575SELECT * FROM r1; 3576 a 3577---- 3578 10 3579 20 3580(2 rows) 3581 3582SELECT * FROM r2; 3583 a 3584---- 3585 10 3586 20 3587(2 rows) 3588 3589-- r2 is read-only 3590INSERT INTO r2 VALUES (2); -- Not allowed 3591ERROR: new row violates row-level security policy for table "r2" 3592UPDATE r2 SET a = 2 RETURNING *; -- Updates nothing 3593 a 3594--- 3595(0 rows) 3596 3597DELETE FROM r2 RETURNING *; -- Deletes nothing 3598 a 3599--- 3600(0 rows) 3601 3602-- r2 can be used as a non-target relation in DML 3603INSERT INTO r1 SELECT a + 1 FROM r2 RETURNING *; -- OK 3604 a 3605---- 3606 11 3607 21 3608(2 rows) 3609 3610UPDATE r1 SET a = r2.a + 2 FROM r2 WHERE r1.a = r2.a RETURNING *; -- OK 3611 a | a 3612----+---- 3613 12 | 10 3614 22 | 20 3615(2 rows) 3616 3617DELETE FROM r1 USING r2 WHERE r1.a = r2.a + 2 RETURNING *; -- OK 3618 a | a 3619----+---- 3620 12 | 10 3621 22 | 20 3622(2 rows) 3623 3624SELECT * FROM r1; 3625 a 3626---- 3627 11 3628 21 3629(2 rows) 3630 3631SELECT * FROM r2; 3632 a 3633---- 3634 10 3635 20 3636(2 rows) 3637 3638SET SESSION AUTHORIZATION regress_rls_alice; 3639DROP TABLE r1; 3640DROP TABLE r2; 3641-- 3642-- FORCE ROW LEVEL SECURITY applies RLS to owners too 3643-- 3644SET SESSION AUTHORIZATION regress_rls_alice; 3645SET row_security = on; 3646CREATE TABLE r1 (a int); 3647INSERT INTO r1 VALUES (10), (20); 3648CREATE POLICY p1 ON r1 USING (false); 3649ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 3650ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 3651-- No error, but no rows 3652TABLE r1; 3653 a 3654--- 3655(0 rows) 3656 3657-- RLS error 3658INSERT INTO r1 VALUES (1); 3659ERROR: new row violates row-level security policy for table "r1" 3660-- No error (unable to see any rows to update) 3661UPDATE r1 SET a = 1; 3662TABLE r1; 3663 a 3664--- 3665(0 rows) 3666 3667-- No error (unable to see any rows to delete) 3668DELETE FROM r1; 3669TABLE r1; 3670 a 3671--- 3672(0 rows) 3673 3674SET row_security = off; 3675-- these all fail, would be affected by RLS 3676TABLE r1; 3677ERROR: query would be affected by row-level security policy for table "r1" 3678HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY. 3679UPDATE r1 SET a = 1; 3680ERROR: query would be affected by row-level security policy for table "r1" 3681HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY. 3682DELETE FROM r1; 3683ERROR: query would be affected by row-level security policy for table "r1" 3684HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY. 3685DROP TABLE r1; 3686-- 3687-- FORCE ROW LEVEL SECURITY does not break RI 3688-- 3689SET SESSION AUTHORIZATION regress_rls_alice; 3690SET row_security = on; 3691CREATE TABLE r1 (a int PRIMARY KEY); 3692CREATE TABLE r2 (a int REFERENCES r1); 3693INSERT INTO r1 VALUES (10), (20); 3694INSERT INTO r2 VALUES (10), (20); 3695-- Create policies on r2 which prevent the 3696-- owner from seeing any rows, but RI should 3697-- still see them. 3698CREATE POLICY p1 ON r2 USING (false); 3699ALTER TABLE r2 ENABLE ROW LEVEL SECURITY; 3700ALTER TABLE r2 FORCE ROW LEVEL SECURITY; 3701-- Errors due to rows in r2 3702DELETE FROM r1; 3703ERROR: update or delete on table "r1" violates foreign key constraint "r2_a_fkey" on table "r2" 3704DETAIL: Key (a)=(10) is still referenced from table "r2". 3705-- Reset r2 to no-RLS 3706DROP POLICY p1 ON r2; 3707ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY; 3708ALTER TABLE r2 DISABLE ROW LEVEL SECURITY; 3709-- clean out r2 for INSERT test below 3710DELETE FROM r2; 3711-- Change r1 to not allow rows to be seen 3712CREATE POLICY p1 ON r1 USING (false); 3713ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 3714ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 3715-- No rows seen 3716TABLE r1; 3717 a 3718--- 3719(0 rows) 3720 3721-- No error, RI still sees that row exists in r1 3722INSERT INTO r2 VALUES (10); 3723DROP TABLE r2; 3724DROP TABLE r1; 3725-- Ensure cascaded DELETE works 3726CREATE TABLE r1 (a int PRIMARY KEY); 3727CREATE TABLE r2 (a int REFERENCES r1 ON DELETE CASCADE); 3728INSERT INTO r1 VALUES (10), (20); 3729INSERT INTO r2 VALUES (10), (20); 3730-- Create policies on r2 which prevent the 3731-- owner from seeing any rows, but RI should 3732-- still see them. 3733CREATE POLICY p1 ON r2 USING (false); 3734ALTER TABLE r2 ENABLE ROW LEVEL SECURITY; 3735ALTER TABLE r2 FORCE ROW LEVEL SECURITY; 3736-- Deletes all records from both 3737DELETE FROM r1; 3738-- Remove FORCE from r2 3739ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY; 3740-- As owner, we now bypass RLS 3741-- verify no rows in r2 now 3742TABLE r2; 3743 a 3744--- 3745(0 rows) 3746 3747DROP TABLE r2; 3748DROP TABLE r1; 3749-- Ensure cascaded UPDATE works 3750CREATE TABLE r1 (a int PRIMARY KEY); 3751CREATE TABLE r2 (a int REFERENCES r1 ON UPDATE CASCADE); 3752INSERT INTO r1 VALUES (10), (20); 3753INSERT INTO r2 VALUES (10), (20); 3754-- Create policies on r2 which prevent the 3755-- owner from seeing any rows, but RI should 3756-- still see them. 3757CREATE POLICY p1 ON r2 USING (false); 3758ALTER TABLE r2 ENABLE ROW LEVEL SECURITY; 3759ALTER TABLE r2 FORCE ROW LEVEL SECURITY; 3760-- Updates records in both 3761UPDATE r1 SET a = a+5; 3762-- Remove FORCE from r2 3763ALTER TABLE r2 NO FORCE ROW LEVEL SECURITY; 3764-- As owner, we now bypass RLS 3765-- verify records in r2 updated 3766TABLE r2; 3767 a 3768---- 3769 15 3770 25 3771(2 rows) 3772 3773DROP TABLE r2; 3774DROP TABLE r1; 3775-- 3776-- Test INSERT+RETURNING applies SELECT policies as 3777-- WithCheckOptions (meaning an error is thrown) 3778-- 3779SET SESSION AUTHORIZATION regress_rls_alice; 3780SET row_security = on; 3781CREATE TABLE r1 (a int); 3782CREATE POLICY p1 ON r1 FOR SELECT USING (false); 3783CREATE POLICY p2 ON r1 FOR INSERT WITH CHECK (true); 3784ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 3785ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 3786-- Works fine 3787INSERT INTO r1 VALUES (10), (20); 3788-- No error, but no rows 3789TABLE r1; 3790 a 3791--- 3792(0 rows) 3793 3794SET row_security = off; 3795-- fail, would be affected by RLS 3796TABLE r1; 3797ERROR: query would be affected by row-level security policy for table "r1" 3798HINT: To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL SECURITY. 3799SET row_security = on; 3800-- Error 3801INSERT INTO r1 VALUES (10), (20) RETURNING *; 3802ERROR: new row violates row-level security policy for table "r1" 3803DROP TABLE r1; 3804-- 3805-- Test UPDATE+RETURNING applies SELECT policies as 3806-- WithCheckOptions (meaning an error is thrown) 3807-- 3808SET SESSION AUTHORIZATION regress_rls_alice; 3809SET row_security = on; 3810CREATE TABLE r1 (a int PRIMARY KEY); 3811CREATE POLICY p1 ON r1 FOR SELECT USING (a < 20); 3812CREATE POLICY p2 ON r1 FOR UPDATE USING (a < 20) WITH CHECK (true); 3813CREATE POLICY p3 ON r1 FOR INSERT WITH CHECK (true); 3814INSERT INTO r1 VALUES (10); 3815ALTER TABLE r1 ENABLE ROW LEVEL SECURITY; 3816ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 3817-- Works fine 3818UPDATE r1 SET a = 30; 3819-- Show updated rows 3820ALTER TABLE r1 NO FORCE ROW LEVEL SECURITY; 3821TABLE r1; 3822 a 3823---- 3824 30 3825(1 row) 3826 3827-- reset value in r1 for test with RETURNING 3828UPDATE r1 SET a = 10; 3829-- Verify row reset 3830TABLE r1; 3831 a 3832---- 3833 10 3834(1 row) 3835 3836ALTER TABLE r1 FORCE ROW LEVEL SECURITY; 3837-- Error 3838UPDATE r1 SET a = 30 RETURNING *; 3839ERROR: new row violates row-level security policy for table "r1" 3840-- UPDATE path of INSERT ... ON CONFLICT DO UPDATE should also error out 3841INSERT INTO r1 VALUES (10) 3842 ON CONFLICT (a) DO UPDATE SET a = 30 RETURNING *; 3843ERROR: new row violates row-level security policy for table "r1" 3844-- Should still error out without RETURNING (use of arbiter always requires 3845-- SELECT permissions) 3846INSERT INTO r1 VALUES (10) 3847 ON CONFLICT (a) DO UPDATE SET a = 30; 3848ERROR: new row violates row-level security policy for table "r1" 3849INSERT INTO r1 VALUES (10) 3850 ON CONFLICT ON CONSTRAINT r1_pkey DO UPDATE SET a = 30; 3851ERROR: new row violates row-level security policy for table "r1" 3852DROP TABLE r1; 3853-- Check dependency handling 3854RESET SESSION AUTHORIZATION; 3855CREATE TABLE dep1 (c1 int); 3856CREATE TABLE dep2 (c1 int); 3857CREATE POLICY dep_p1 ON dep1 TO regress_rls_bob USING (c1 > (select max(dep2.c1) from dep2)); 3858ALTER POLICY dep_p1 ON dep1 TO regress_rls_bob,regress_rls_carol; 3859-- Should return one 3860SELECT count(*) = 1 FROM pg_depend 3861 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1') 3862 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2'); 3863 ?column? 3864---------- 3865 t 3866(1 row) 3867 3868ALTER POLICY dep_p1 ON dep1 USING (true); 3869-- Should return one 3870SELECT count(*) = 1 FROM pg_shdepend 3871 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1') 3872 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_bob'); 3873 ?column? 3874---------- 3875 t 3876(1 row) 3877 3878-- Should return one 3879SELECT count(*) = 1 FROM pg_shdepend 3880 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1') 3881 AND refobjid = (SELECT oid FROM pg_authid WHERE rolname = 'regress_rls_carol'); 3882 ?column? 3883---------- 3884 t 3885(1 row) 3886 3887-- Should return zero 3888SELECT count(*) = 0 FROM pg_depend 3889 WHERE objid = (SELECT oid FROM pg_policy WHERE polname = 'dep_p1') 3890 AND refobjid = (SELECT oid FROM pg_class WHERE relname = 'dep2'); 3891 ?column? 3892---------- 3893 t 3894(1 row) 3895 3896-- DROP OWNED BY testing 3897RESET SESSION AUTHORIZATION; 3898CREATE ROLE regress_rls_dob_role1; 3899CREATE ROLE regress_rls_dob_role2; 3900CREATE TABLE dob_t1 (c1 int); 3901CREATE TABLE dob_t2 (c1 int) PARTITION BY RANGE (c1); 3902CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1 USING (true); 3903DROP OWNED BY regress_rls_dob_role1; 3904DROP POLICY p1 ON dob_t1; -- should fail, already gone 3905ERROR: policy "p1" for table "dob_t1" does not exist 3906CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true); 3907DROP OWNED BY regress_rls_dob_role1; 3908DROP POLICY p1 ON dob_t1; -- should succeed 3909-- same cases with duplicate polroles entries 3910CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role1 USING (true); 3911DROP OWNED BY regress_rls_dob_role1; 3912DROP POLICY p1 ON dob_t1; -- should fail, already gone 3913ERROR: policy "p1" for table "dob_t1" does not exist 3914CREATE POLICY p1 ON dob_t1 TO regress_rls_dob_role1,regress_rls_dob_role1,regress_rls_dob_role2 USING (true); 3915DROP OWNED BY regress_rls_dob_role1; 3916DROP POLICY p1 ON dob_t1; -- should succeed 3917-- partitioned target 3918CREATE POLICY p1 ON dob_t2 TO regress_rls_dob_role1,regress_rls_dob_role2 USING (true); 3919DROP OWNED BY regress_rls_dob_role1; 3920DROP POLICY p1 ON dob_t2; -- should succeed 3921DROP USER regress_rls_dob_role1; 3922DROP USER regress_rls_dob_role2; 3923-- Bug #15708: view + table with RLS should check policies as view owner 3924CREATE TABLE ref_tbl (a int); 3925INSERT INTO ref_tbl VALUES (1); 3926CREATE TABLE rls_tbl (a int); 3927INSERT INTO rls_tbl VALUES (10); 3928ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY; 3929CREATE POLICY p1 ON rls_tbl USING (EXISTS (SELECT 1 FROM ref_tbl)); 3930GRANT SELECT ON ref_tbl TO regress_rls_bob; 3931GRANT SELECT ON rls_tbl TO regress_rls_bob; 3932CREATE VIEW rls_view AS SELECT * FROM rls_tbl; 3933ALTER VIEW rls_view OWNER TO regress_rls_bob; 3934GRANT SELECT ON rls_view TO regress_rls_alice; 3935SET SESSION AUTHORIZATION regress_rls_alice; 3936SELECT * FROM ref_tbl; -- Permission denied 3937ERROR: permission denied for relation ref_tbl 3938SELECT * FROM rls_tbl; -- Permission denied 3939ERROR: permission denied for relation rls_tbl 3940SELECT * FROM rls_view; -- OK 3941 a 3942---- 3943 10 3944(1 row) 3945 3946RESET SESSION AUTHORIZATION; 3947DROP VIEW rls_view; 3948DROP TABLE rls_tbl; 3949DROP TABLE ref_tbl; 3950-- Leaky operator test 3951CREATE TABLE rls_tbl (a int); 3952INSERT INTO rls_tbl SELECT x/10 FROM generate_series(1, 100) x; 3953ANALYZE rls_tbl; 3954ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY; 3955GRANT SELECT ON rls_tbl TO regress_rls_alice; 3956SET SESSION AUTHORIZATION regress_rls_alice; 3957CREATE FUNCTION op_leak(int, int) RETURNS bool 3958 AS 'BEGIN RAISE NOTICE ''op_leak => %, %'', $1, $2; RETURN $1 < $2; END' 3959 LANGUAGE plpgsql; 3960CREATE OPERATOR <<< (procedure = op_leak, leftarg = int, rightarg = int, 3961 restrict = scalarltsel); 3962SELECT * FROM rls_tbl WHERE a <<< 1000; 3963 a 3964--- 3965(0 rows) 3966 3967DROP OPERATOR <<< (int, int); 3968DROP FUNCTION op_leak(int, int); 3969RESET SESSION AUTHORIZATION; 3970DROP TABLE rls_tbl; 3971-- Bug #16006: whole-row Vars in a policy don't play nice with sub-selects 3972SET SESSION AUTHORIZATION regress_rls_alice; 3973CREATE TABLE rls_tbl (a int, b int, c int); 3974CREATE POLICY p1 ON rls_tbl USING (rls_tbl >= ROW(1,1,1)); 3975ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY; 3976ALTER TABLE rls_tbl FORCE ROW LEVEL SECURITY; 3977INSERT INTO rls_tbl SELECT 10, 20, 30; 3978EXPLAIN (VERBOSE, COSTS OFF) 3979INSERT INTO rls_tbl 3980 SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss; 3981 QUERY PLAN 3982-------------------------------------------------------------------- 3983 Insert on regress_rls_schema.rls_tbl 3984 -> Subquery Scan on ss 3985 Output: ss.b, ss.c, NULL::integer 3986 -> Sort 3987 Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a 3988 Sort Key: rls_tbl_1.a 3989 -> Seq Scan on regress_rls_schema.rls_tbl rls_tbl_1 3990 Output: rls_tbl_1.b, rls_tbl_1.c, rls_tbl_1.a 3991 Filter: (rls_tbl_1.* >= ROW(1, 1, 1)) 3992(9 rows) 3993 3994INSERT INTO rls_tbl 3995 SELECT * FROM (SELECT b, c FROM rls_tbl ORDER BY a) ss; 3996SELECT * FROM rls_tbl; 3997 a | b | c 3998----+----+---- 3999 10 | 20 | 30 4000 20 | 30 | 4001(2 rows) 4002 4003DROP TABLE rls_tbl; 4004RESET SESSION AUTHORIZATION; 4005-- 4006-- Clean up objects 4007-- 4008RESET SESSION AUTHORIZATION; 4009\set VERBOSITY terse \\ -- suppress cascade details 4010DROP SCHEMA regress_rls_schema CASCADE; 4011NOTICE: drop cascades to 29 other objects 4012\set VERBOSITY default 4013DROP USER regress_rls_alice; 4014DROP USER regress_rls_bob; 4015DROP USER regress_rls_carol; 4016DROP USER regress_rls_dave; 4017DROP USER regress_rls_exempt_user; 4018DROP ROLE regress_rls_group1; 4019DROP ROLE regress_rls_group2; 4020-- Arrange to have a few policies left over, for testing 4021-- pg_dump/pg_restore 4022CREATE SCHEMA regress_rls_schema; 4023CREATE TABLE rls_tbl (c1 int); 4024ALTER TABLE rls_tbl ENABLE ROW LEVEL SECURITY; 4025CREATE POLICY p1 ON rls_tbl USING (c1 > 5); 4026CREATE POLICY p2 ON rls_tbl FOR SELECT USING (c1 <= 3); 4027CREATE POLICY p3 ON rls_tbl FOR UPDATE USING (c1 <= 3) WITH CHECK (c1 > 5); 4028CREATE POLICY p4 ON rls_tbl FOR DELETE USING (c1 <= 3); 4029CREATE TABLE rls_tbl_force (c1 int); 4030ALTER TABLE rls_tbl_force ENABLE ROW LEVEL SECURITY; 4031ALTER TABLE rls_tbl_force FORCE ROW LEVEL SECURITY; 4032CREATE POLICY p1 ON rls_tbl_force USING (c1 = 5) WITH CHECK (c1 < 5); 4033CREATE POLICY p2 ON rls_tbl_force FOR SELECT USING (c1 = 8); 4034CREATE POLICY p3 ON rls_tbl_force FOR UPDATE USING (c1 = 8) WITH CHECK (c1 >= 5); 4035CREATE POLICY p4 ON rls_tbl_force FOR DELETE USING (c1 = 8); 4036