1-- 2-- Test access privileges 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 ROLE IF EXISTS regress_priv_group1; 8DROP ROLE IF EXISTS regress_priv_group2; 9DROP ROLE IF EXISTS regress_priv_user1; 10DROP ROLE IF EXISTS regress_priv_user2; 11DROP ROLE IF EXISTS regress_priv_user3; 12DROP ROLE IF EXISTS regress_priv_user4; 13DROP ROLE IF EXISTS regress_priv_user5; 14DROP ROLE IF EXISTS regress_priv_user6; 15SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 16 lo_unlink 17----------- 18(0 rows) 19 20RESET client_min_messages; 21-- test proper begins here 22CREATE USER regress_priv_user1; 23CREATE USER regress_priv_user2; 24CREATE USER regress_priv_user3; 25CREATE USER regress_priv_user4; 26CREATE USER regress_priv_user5; 27CREATE USER regress_priv_user5; -- duplicate 28ERROR: role "regress_priv_user5" already exists 29CREATE GROUP regress_priv_group1; 30CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2; 31ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4; 32ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate 33NOTICE: role "regress_priv_user2" is already a member of role "regress_priv_group2" 34ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; 35GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION; 36-- prepare non-leakproof function for later 37CREATE FUNCTION leak(integer,integer) RETURNS boolean 38 AS 'int4lt' 39 LANGUAGE internal IMMUTABLE STRICT; -- but deliberately not LEAKPROOF 40ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1; 41-- test owner privileges 42SET SESSION AUTHORIZATION regress_priv_user1; 43SELECT session_user, current_user; 44 session_user | current_user 45--------------------+-------------------- 46 regress_priv_user1 | regress_priv_user1 47(1 row) 48 49CREATE TABLE atest1 ( a int, b text ); 50SELECT * FROM atest1; 51 a | b 52---+--- 53(0 rows) 54 55INSERT INTO atest1 VALUES (1, 'one'); 56DELETE FROM atest1; 57UPDATE atest1 SET a = 1 WHERE b = 'blech'; 58TRUNCATE atest1; 59BEGIN; 60LOCK atest1 IN ACCESS EXCLUSIVE MODE; 61COMMIT; 62REVOKE ALL ON atest1 FROM PUBLIC; 63SELECT * FROM atest1; 64 a | b 65---+--- 66(0 rows) 67 68GRANT ALL ON atest1 TO regress_priv_user2; 69GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4; 70SELECT * FROM atest1; 71 a | b 72---+--- 73(0 rows) 74 75CREATE TABLE atest2 (col1 varchar(10), col2 boolean); 76GRANT SELECT ON atest2 TO regress_priv_user2; 77GRANT UPDATE ON atest2 TO regress_priv_user3; 78GRANT INSERT ON atest2 TO regress_priv_user4; 79GRANT TRUNCATE ON atest2 TO regress_priv_user5; 80SET SESSION AUTHORIZATION regress_priv_user2; 81SELECT session_user, current_user; 82 session_user | current_user 83--------------------+-------------------- 84 regress_priv_user2 | regress_priv_user2 85(1 row) 86 87-- try various combinations of queries on atest1 and atest2 88SELECT * FROM atest1; -- ok 89 a | b 90---+--- 91(0 rows) 92 93SELECT * FROM atest2; -- ok 94 col1 | col2 95------+------ 96(0 rows) 97 98INSERT INTO atest1 VALUES (2, 'two'); -- ok 99INSERT INTO atest2 VALUES ('foo', true); -- fail 100ERROR: permission denied for table atest2 101INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok 102UPDATE atest1 SET a = 1 WHERE a = 2; -- ok 103UPDATE atest2 SET col2 = NOT col2; -- fail 104ERROR: permission denied for table atest2 105SELECT * FROM atest1 FOR UPDATE; -- ok 106 a | b 107---+----- 108 1 | two 109 1 | two 110(2 rows) 111 112SELECT * FROM atest2 FOR UPDATE; -- fail 113ERROR: permission denied for table atest2 114DELETE FROM atest2; -- fail 115ERROR: permission denied for table atest2 116TRUNCATE atest2; -- fail 117ERROR: permission denied for table atest2 118BEGIN; 119LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail 120ERROR: permission denied for table atest2 121COMMIT; 122COPY atest2 FROM stdin; -- fail 123ERROR: permission denied for table atest2 124GRANT ALL ON atest1 TO PUBLIC; -- fail 125WARNING: no privileges were granted for "atest1" 126-- checks in subquery, both ok 127SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) ); 128 a | b 129---+--- 130(0 rows) 131 132SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) ); 133 col1 | col2 134------+------ 135(0 rows) 136 137SET SESSION AUTHORIZATION regress_priv_user3; 138SELECT session_user, current_user; 139 session_user | current_user 140--------------------+-------------------- 141 regress_priv_user3 | regress_priv_user3 142(1 row) 143 144SELECT * FROM atest1; -- ok 145 a | b 146---+----- 147 1 | two 148 1 | two 149(2 rows) 150 151SELECT * FROM atest2; -- fail 152ERROR: permission denied for table atest2 153INSERT INTO atest1 VALUES (2, 'two'); -- fail 154ERROR: permission denied for table atest1 155INSERT INTO atest2 VALUES ('foo', true); -- fail 156ERROR: permission denied for table atest2 157INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail 158ERROR: permission denied for table atest1 159UPDATE atest1 SET a = 1 WHERE a = 2; -- fail 160ERROR: permission denied for table atest1 161UPDATE atest2 SET col2 = NULL; -- ok 162UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2 163ERROR: permission denied for table atest2 164UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok 165SELECT * FROM atest1 FOR UPDATE; -- fail 166ERROR: permission denied for table atest1 167SELECT * FROM atest2 FOR UPDATE; -- fail 168ERROR: permission denied for table atest2 169DELETE FROM atest2; -- fail 170ERROR: permission denied for table atest2 171TRUNCATE atest2; -- fail 172ERROR: permission denied for table atest2 173BEGIN; 174LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok 175COMMIT; 176COPY atest2 FROM stdin; -- fail 177ERROR: permission denied for table atest2 178-- checks in subquery, both fail 179SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) ); 180ERROR: permission denied for table atest2 181SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) ); 182ERROR: permission denied for table atest2 183SET SESSION AUTHORIZATION regress_priv_user4; 184COPY atest2 FROM stdin; -- ok 185SELECT * FROM atest1; -- ok 186 a | b 187---+----- 188 1 | two 189 1 | two 190(2 rows) 191 192-- test leaky-function protections in selfuncs 193-- regress_priv_user1 will own a table and provide views for it. 194SET SESSION AUTHORIZATION regress_priv_user1; 195CREATE TABLE atest12 as 196 SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x; 197CREATE INDEX ON atest12 (a); 198CREATE INDEX ON atest12 (abs(a)); 199-- results below depend on having quite accurate stats for atest12, so... 200ALTER TABLE atest12 SET (autovacuum_enabled = off); 201SET default_statistics_target = 10000; 202VACUUM ANALYZE atest12; 203RESET default_statistics_target; 204CREATE OPERATOR <<< (procedure = leak, leftarg = integer, rightarg = integer, 205 restrict = scalarltsel); 206-- views with leaky operator 207CREATE VIEW atest12v AS 208 SELECT * FROM atest12 WHERE b <<< 5; 209CREATE VIEW atest12sbv WITH (security_barrier=true) AS 210 SELECT * FROM atest12 WHERE b <<< 5; 211GRANT SELECT ON atest12v TO PUBLIC; 212GRANT SELECT ON atest12sbv TO PUBLIC; 213-- This plan should use nestloop, knowing that few rows will be selected. 214EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; 215 QUERY PLAN 216------------------------------------------------- 217 Nested Loop 218 -> Seq Scan on atest12 atest12_1 219 Filter: (b <<< 5) 220 -> Index Scan using atest12_a_idx on atest12 221 Index Cond: (a = atest12_1.b) 222 Filter: (b <<< 5) 223(6 rows) 224 225-- And this one. 226EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y 227 WHERE x.a = y.b and abs(y.a) <<< 5; 228 QUERY PLAN 229--------------------------------------------------- 230 Nested Loop 231 -> Seq Scan on atest12 y 232 Filter: (abs(a) <<< 5) 233 -> Index Scan using atest12_a_idx on atest12 x 234 Index Cond: (a = y.b) 235(5 rows) 236 237-- This should also be a nestloop, but the security barrier forces the inner 238-- scan to be materialized 239EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; 240 QUERY PLAN 241------------------------------------------- 242 Nested Loop 243 Join Filter: (atest12.a = atest12_1.b) 244 -> Seq Scan on atest12 245 Filter: (b <<< 5) 246 -> Materialize 247 -> Seq Scan on atest12 atest12_1 248 Filter: (b <<< 5) 249(7 rows) 250 251-- Check if regress_priv_user2 can break security. 252SET SESSION AUTHORIZATION regress_priv_user2; 253CREATE FUNCTION leak2(integer,integer) RETURNS boolean 254 AS $$begin raise notice 'leak % %', $1, $2; return $1 > $2; end$$ 255 LANGUAGE plpgsql immutable; 256CREATE OPERATOR >>> (procedure = leak2, leftarg = integer, rightarg = integer, 257 restrict = scalargtsel); 258-- This should not show any "leak" notices before failing. 259EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0; 260ERROR: permission denied for table atest12 261-- These plans should continue to use a nestloop, since they execute with the 262-- privileges of the view owner. 263EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; 264 QUERY PLAN 265------------------------------------------------- 266 Nested Loop 267 -> Seq Scan on atest12 atest12_1 268 Filter: (b <<< 5) 269 -> Index Scan using atest12_a_idx on atest12 270 Index Cond: (a = atest12_1.b) 271 Filter: (b <<< 5) 272(6 rows) 273 274EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; 275 QUERY PLAN 276------------------------------------------- 277 Nested Loop 278 Join Filter: (atest12.a = atest12_1.b) 279 -> Seq Scan on atest12 280 Filter: (b <<< 5) 281 -> Materialize 282 -> Seq Scan on atest12 atest12_1 283 Filter: (b <<< 5) 284(7 rows) 285 286-- A non-security barrier view does not guard against information leakage. 287EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y 288 WHERE x.a = y.b and abs(y.a) <<< 5; 289 QUERY PLAN 290------------------------------------------------- 291 Nested Loop 292 -> Seq Scan on atest12 atest12_1 293 Filter: ((b <<< 5) AND (abs(a) <<< 5)) 294 -> Index Scan using atest12_a_idx on atest12 295 Index Cond: (a = atest12_1.b) 296 Filter: (b <<< 5) 297(6 rows) 298 299-- But a security barrier view isolates the leaky operator. 300EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y 301 WHERE x.a = y.b and abs(y.a) <<< 5; 302 QUERY PLAN 303------------------------------------- 304 Nested Loop 305 Join Filter: (atest12_1.a = y.b) 306 -> Subquery Scan on y 307 Filter: (abs(y.a) <<< 5) 308 -> Seq Scan on atest12 309 Filter: (b <<< 5) 310 -> Seq Scan on atest12 atest12_1 311 Filter: (b <<< 5) 312(8 rows) 313 314-- Now regress_priv_user1 grants sufficient access to regress_priv_user2. 315SET SESSION AUTHORIZATION regress_priv_user1; 316GRANT SELECT (a, b) ON atest12 TO PUBLIC; 317SET SESSION AUTHORIZATION regress_priv_user2; 318-- regress_priv_user2 should continue to get a good row estimate. 319EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; 320 QUERY PLAN 321------------------------------------------------- 322 Nested Loop 323 -> Seq Scan on atest12 atest12_1 324 Filter: (b <<< 5) 325 -> Index Scan using atest12_a_idx on atest12 326 Index Cond: (a = atest12_1.b) 327 Filter: (b <<< 5) 328(6 rows) 329 330-- But not for this, due to lack of table-wide permissions needed 331-- to make use of the expression index's statistics. 332EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y 333 WHERE x.a = y.b and abs(y.a) <<< 5; 334 QUERY PLAN 335-------------------------------------- 336 Hash Join 337 Hash Cond: (x.a = y.b) 338 -> Seq Scan on atest12 x 339 -> Hash 340 -> Seq Scan on atest12 y 341 Filter: (abs(a) <<< 5) 342(6 rows) 343 344-- clean up (regress_priv_user1's objects are all dropped later) 345DROP FUNCTION leak2(integer, integer) CASCADE; 346NOTICE: drop cascades to operator >>>(integer,integer) 347-- groups 348SET SESSION AUTHORIZATION regress_priv_user3; 349CREATE TABLE atest3 (one int, two int, three int); 350GRANT DELETE ON atest3 TO GROUP regress_priv_group2; 351SET SESSION AUTHORIZATION regress_priv_user1; 352SELECT * FROM atest3; -- fail 353ERROR: permission denied for table atest3 354DELETE FROM atest3; -- ok 355BEGIN; 356RESET SESSION AUTHORIZATION; 357ALTER ROLE regress_priv_user1 NOINHERIT; 358SET SESSION AUTHORIZATION regress_priv_user1; 359DELETE FROM atest3; 360ERROR: permission denied for table atest3 361ROLLBACK; 362-- views 363SET SESSION AUTHORIZATION regress_priv_user3; 364CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok 365/* The next *should* fail, but it's not implemented that way yet. */ 366CREATE VIEW atestv2 AS SELECT * FROM atest2; 367CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok 368/* Empty view is a corner case that failed in 9.2. */ 369CREATE VIEW atestv0 AS SELECT 0 as x WHERE false; -- ok 370SELECT * FROM atestv1; -- ok 371 a | b 372---+----- 373 1 | two 374 1 | two 375(2 rows) 376 377SELECT * FROM atestv2; -- fail 378ERROR: permission denied for table atest2 379GRANT SELECT ON atestv1, atestv3 TO regress_priv_user4; 380GRANT SELECT ON atestv2 TO regress_priv_user2; 381SET SESSION AUTHORIZATION regress_priv_user4; 382SELECT * FROM atestv1; -- ok 383 a | b 384---+----- 385 1 | two 386 1 | two 387(2 rows) 388 389SELECT * FROM atestv2; -- fail 390ERROR: permission denied for view atestv2 391SELECT * FROM atestv3; -- ok 392 one | two | three 393-----+-----+------- 394(0 rows) 395 396SELECT * FROM atestv0; -- fail 397ERROR: permission denied for view atestv0 398-- Appendrels excluded by constraints failed to check permissions in 8.4-9.2. 399select * from 400 ((select a.q1 as x from int8_tbl a offset 0) 401 union all 402 (select b.q2 as x from int8_tbl b offset 0)) ss 403where false; 404ERROR: permission denied for table int8_tbl 405set constraint_exclusion = on; 406select * from 407 ((select a.q1 as x, random() from int8_tbl a where q1 > 0) 408 union all 409 (select b.q2 as x, random() from int8_tbl b where q2 > 0)) ss 410where x < 0; 411ERROR: permission denied for table int8_tbl 412reset constraint_exclusion; 413CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view 414SELECT * FROM atestv4; -- ok 415 one | two | three 416-----+-----+------- 417(0 rows) 418 419GRANT SELECT ON atestv4 TO regress_priv_user2; 420SET SESSION AUTHORIZATION regress_priv_user2; 421-- Two complex cases: 422SELECT * FROM atestv3; -- fail 423ERROR: permission denied for view atestv3 424SELECT * FROM atestv4; -- ok (even though regress_priv_user2 cannot access underlying atestv3) 425 one | two | three 426-----+-----+------- 427(0 rows) 428 429SELECT * FROM atest2; -- ok 430 col1 | col2 431------+------ 432 bar | t 433(1 row) 434 435SELECT * FROM atestv2; -- fail (even though regress_priv_user2 can access underlying atest2) 436ERROR: permission denied for table atest2 437-- Test column level permissions 438SET SESSION AUTHORIZATION regress_priv_user1; 439CREATE TABLE atest5 (one int, two int unique, three int, four int unique); 440CREATE TABLE atest6 (one int, two int, blue int); 441GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regress_priv_user4; 442GRANT ALL (one) ON atest5 TO regress_priv_user3; 443INSERT INTO atest5 VALUES (1,2,3); 444SET SESSION AUTHORIZATION regress_priv_user4; 445SELECT * FROM atest5; -- fail 446ERROR: permission denied for table atest5 447SELECT one FROM atest5; -- ok 448 one 449----- 450 1 451(1 row) 452 453COPY atest5 (one) TO stdout; -- ok 4541 455SELECT two FROM atest5; -- fail 456ERROR: permission denied for table atest5 457COPY atest5 (two) TO stdout; -- fail 458ERROR: permission denied for table atest5 459SELECT atest5 FROM atest5; -- fail 460ERROR: permission denied for table atest5 461COPY atest5 (one,two) TO stdout; -- fail 462ERROR: permission denied for table atest5 463SELECT 1 FROM atest5; -- ok 464 ?column? 465---------- 466 1 467(1 row) 468 469SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok 470 ?column? 471---------- 472 1 473(1 row) 474 475SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail 476ERROR: permission denied for table atest5 477SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail 478ERROR: permission denied for table atest5 479SELECT * FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail 480ERROR: permission denied for table atest5 481SELECT j.* FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail 482ERROR: permission denied for table atest5 483SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail 484ERROR: permission denied for table atest5 485SELECT one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok 486 one 487----- 488 1 489(1 row) 490 491SELECT j.one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok 492 one 493----- 494 1 495(1 row) 496 497SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail 498ERROR: permission denied for table atest5 499SELECT j.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail 500ERROR: permission denied for table atest5 501SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail 502ERROR: permission denied for table atest5 503SELECT j.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail 504ERROR: permission denied for table atest5 505SELECT * FROM (atest5 a JOIN atest5 b USING (one)); -- fail 506ERROR: permission denied for table atest5 507SELECT a.* FROM (atest5 a JOIN atest5 b USING (one)); -- fail 508ERROR: permission denied for table atest5 509SELECT (a.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)); -- fail 510ERROR: permission denied for table atest5 511SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail 512ERROR: permission denied for table atest5 513SELECT a.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail 514ERROR: permission denied for table atest5 515SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail 516ERROR: permission denied for table atest5 517SELECT b.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail 518ERROR: permission denied for table atest5 519SELECT y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail 520ERROR: permission denied for table atest5 521SELECT b.y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail 522ERROR: permission denied for table atest5 523SELECT y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail 524ERROR: permission denied for table atest5 525SELECT b.y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail 526ERROR: permission denied for table atest5 527SELECT 1 FROM atest5 WHERE two = 2; -- fail 528ERROR: permission denied for table atest5 529SELECT * FROM atest1, atest5; -- fail 530ERROR: permission denied for table atest5 531SELECT atest1.* FROM atest1, atest5; -- ok 532 a | b 533---+----- 534 1 | two 535 1 | two 536(2 rows) 537 538SELECT atest1.*,atest5.one FROM atest1, atest5; -- ok 539 a | b | one 540---+-----+----- 541 1 | two | 1 542 1 | two | 1 543(2 rows) 544 545SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two); -- fail 546ERROR: permission denied for table atest5 547SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- ok 548 a | b | one 549---+-----+----- 550 1 | two | 1 551 1 | two | 1 552(2 rows) 553 554SELECT one, two FROM atest5; -- fail 555ERROR: permission denied for table atest5 556SET SESSION AUTHORIZATION regress_priv_user1; 557GRANT SELECT (one,two) ON atest6 TO regress_priv_user4; 558SET SESSION AUTHORIZATION regress_priv_user4; 559SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still 560ERROR: permission denied for table atest5 561SET SESSION AUTHORIZATION regress_priv_user1; 562GRANT SELECT (two) ON atest5 TO regress_priv_user4; 563SET SESSION AUTHORIZATION regress_priv_user4; 564SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now 565 one | two 566-----+----- 567(0 rows) 568 569-- test column-level privileges for INSERT and UPDATE 570INSERT INTO atest5 (two) VALUES (3); -- ok 571COPY atest5 FROM stdin; -- fail 572ERROR: permission denied for table atest5 573COPY atest5 (two) FROM stdin; -- ok 574INSERT INTO atest5 (three) VALUES (4); -- fail 575ERROR: permission denied for table atest5 576INSERT INTO atest5 VALUES (5,5,5); -- fail 577ERROR: permission denied for table atest5 578UPDATE atest5 SET three = 10; -- ok 579UPDATE atest5 SET one = 8; -- fail 580ERROR: permission denied for table atest5 581UPDATE atest5 SET three = 5, one = 2; -- fail 582ERROR: permission denied for table atest5 583-- Check that column level privs are enforced in RETURNING 584-- Ok. 585INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10; 586-- Error. No SELECT on column three. 587INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three; 588ERROR: permission denied for table atest5 589-- Ok. May SELECT on column "one": 590INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one; 591 one 592----- 593 594(1 row) 595 596-- Check that column level privileges are enforced for EXCLUDED 597-- Ok. we may select one 598INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one; 599-- Error. No select rights on three 600INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three; 601ERROR: permission denied for table atest5 602INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE) 603ERROR: permission denied for table atest5 604INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT) 605ERROR: permission denied for table atest5 606-- Check that the columns in the inference require select privileges 607INSERT INTO atest5(four) VALUES (4); -- fail 608ERROR: permission denied for table atest5 609SET SESSION AUTHORIZATION regress_priv_user1; 610GRANT INSERT (four) ON atest5 TO regress_priv_user4; 611SET SESSION AUTHORIZATION regress_priv_user4; 612INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- fails (due to SELECT) 613ERROR: permission denied for table atest5 614INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- fails (due to SELECT) 615ERROR: permission denied for table atest5 616INSERT INTO atest5(four) VALUES (4); -- ok 617SET SESSION AUTHORIZATION regress_priv_user1; 618GRANT SELECT (four) ON atest5 TO regress_priv_user4; 619SET SESSION AUTHORIZATION regress_priv_user4; 620INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- ok 621INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- ok 622SET SESSION AUTHORIZATION regress_priv_user1; 623REVOKE ALL (one) ON atest5 FROM regress_priv_user4; 624GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4; 625SET SESSION AUTHORIZATION regress_priv_user4; 626SELECT one FROM atest5; -- fail 627ERROR: permission denied for table atest5 628UPDATE atest5 SET one = 1; -- fail 629ERROR: permission denied for table atest5 630SELECT atest6 FROM atest6; -- ok 631 atest6 632-------- 633(0 rows) 634 635COPY atest6 TO stdout; -- ok 636-- check error reporting with column privs 637SET SESSION AUTHORIZATION regress_priv_user1; 638CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2)); 639GRANT SELECT (c1) ON t1 TO regress_priv_user2; 640GRANT INSERT (c1, c2, c3) ON t1 TO regress_priv_user2; 641GRANT UPDATE (c1, c2, c3) ON t1 TO regress_priv_user2; 642-- seed data 643INSERT INTO t1 VALUES (1, 1, 1); 644INSERT INTO t1 VALUES (1, 2, 1); 645INSERT INTO t1 VALUES (2, 1, 2); 646INSERT INTO t1 VALUES (2, 2, 2); 647INSERT INTO t1 VALUES (3, 1, 3); 648SET SESSION AUTHORIZATION regress_priv_user2; 649INSERT INTO t1 (c1, c2) VALUES (1, 1); -- fail, but row not shown 650ERROR: duplicate key value violates unique constraint "t1_pkey" 651UPDATE t1 SET c2 = 1; -- fail, but row not shown 652ERROR: duplicate key value violates unique constraint "t1_pkey" 653INSERT INTO t1 (c1, c2) VALUES (null, null); -- fail, but see columns being inserted 654ERROR: null value in column "c1" of relation "t1" violates not-null constraint 655DETAIL: Failing row contains (c1, c2) = (null, null). 656INSERT INTO t1 (c3) VALUES (null); -- fail, but see columns being inserted or have SELECT 657ERROR: null value in column "c1" of relation "t1" violates not-null constraint 658DETAIL: Failing row contains (c1, c3) = (null, null). 659INSERT INTO t1 (c1) VALUES (5); -- fail, but see columns being inserted or have SELECT 660ERROR: null value in column "c2" of relation "t1" violates not-null constraint 661DETAIL: Failing row contains (c1) = (5). 662UPDATE t1 SET c3 = 10; -- fail, but see columns with SELECT rights, or being modified 663ERROR: new row for relation "t1" violates check constraint "t1_c3_check" 664DETAIL: Failing row contains (c1, c3) = (1, 10). 665SET SESSION AUTHORIZATION regress_priv_user1; 666DROP TABLE t1; 667-- check error reporting with column privs on a partitioned table 668CREATE TABLE errtst(a text, b text NOT NULL, c text, secret1 text, secret2 text) PARTITION BY LIST (a); 669CREATE TABLE errtst_part_1(secret2 text, c text, a text, b text NOT NULL, secret1 text); 670CREATE TABLE errtst_part_2(secret1 text, secret2 text, a text, c text, b text NOT NULL); 671ALTER TABLE errtst ATTACH PARTITION errtst_part_1 FOR VALUES IN ('aaa'); 672ALTER TABLE errtst ATTACH PARTITION errtst_part_2 FOR VALUES IN ('aaaa'); 673GRANT SELECT (a, b, c) ON TABLE errtst TO regress_priv_user2; 674GRANT UPDATE (a, b, c) ON TABLE errtst TO regress_priv_user2; 675GRANT INSERT (a, b, c) ON TABLE errtst TO regress_priv_user2; 676INSERT INTO errtst_part_1 (a, b, c, secret1, secret2) 677VALUES ('aaa', 'bbb', 'ccc', 'the body', 'is in the attic'); 678SET SESSION AUTHORIZATION regress_priv_user2; 679-- Perform a few updates that violate the NOT NULL constraint. Make sure 680-- the error messages don't leak the secret fields. 681-- simple insert. 682INSERT INTO errtst (a, b) VALUES ('aaa', NULL); 683ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint 684DETAIL: Failing row contains (a, b, c) = (aaa, null, null). 685-- simple update. 686UPDATE errtst SET b = NULL; 687ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint 688DETAIL: Failing row contains (b) = (null). 689-- partitioning key is updated, doesn't move the row. 690UPDATE errtst SET a = 'aaa', b = NULL; 691ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint 692DETAIL: Failing row contains (a, b, c) = (aaa, null, ccc). 693-- row is moved to another partition. 694UPDATE errtst SET a = 'aaaa', b = NULL; 695ERROR: null value in column "b" of relation "errtst_part_2" violates not-null constraint 696DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). 697-- row is moved to another partition. This differs from the previous case in 698-- that the new partition is excluded by constraint exclusion, so its 699-- ResultRelInfo is not created at ExecInitModifyTable, but needs to be 700-- constructed on the fly when the updated tuple is routed to it. 701UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa'; 702ERROR: null value in column "b" of relation "errtst_part_2" violates not-null constraint 703DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). 704SET SESSION AUTHORIZATION regress_priv_user1; 705DROP TABLE errtst; 706-- test column-level privileges when involved with DELETE 707SET SESSION AUTHORIZATION regress_priv_user1; 708ALTER TABLE atest6 ADD COLUMN three integer; 709GRANT DELETE ON atest5 TO regress_priv_user3; 710GRANT SELECT (two) ON atest5 TO regress_priv_user3; 711REVOKE ALL (one) ON atest5 FROM regress_priv_user3; 712GRANT SELECT (one) ON atest5 TO regress_priv_user4; 713SET SESSION AUTHORIZATION regress_priv_user4; 714SELECT atest6 FROM atest6; -- fail 715ERROR: permission denied for table atest6 716SELECT one FROM atest5 NATURAL JOIN atest6; -- fail 717ERROR: permission denied for table atest5 718SET SESSION AUTHORIZATION regress_priv_user1; 719ALTER TABLE atest6 DROP COLUMN three; 720SET SESSION AUTHORIZATION regress_priv_user4; 721SELECT atest6 FROM atest6; -- ok 722 atest6 723-------- 724(0 rows) 725 726SELECT one FROM atest5 NATURAL JOIN atest6; -- ok 727 one 728----- 729(0 rows) 730 731SET SESSION AUTHORIZATION regress_priv_user1; 732ALTER TABLE atest6 DROP COLUMN two; 733REVOKE SELECT (one,blue) ON atest6 FROM regress_priv_user4; 734SET SESSION AUTHORIZATION regress_priv_user4; 735SELECT * FROM atest6; -- fail 736ERROR: permission denied for table atest6 737SELECT 1 FROM atest6; -- fail 738ERROR: permission denied for table atest6 739SET SESSION AUTHORIZATION regress_priv_user3; 740DELETE FROM atest5 WHERE one = 1; -- fail 741ERROR: permission denied for table atest5 742DELETE FROM atest5 WHERE two = 2; -- ok 743-- check inheritance cases 744SET SESSION AUTHORIZATION regress_priv_user1; 745CREATE TABLE atestp1 (f1 int, f2 int); 746CREATE TABLE atestp2 (fx int, fy int); 747CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2); 748GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2; 749GRANT SELECT(fx) ON atestc TO regress_priv_user2; 750SET SESSION AUTHORIZATION regress_priv_user2; 751SELECT fx FROM atestp2; -- ok 752 fx 753---- 754(0 rows) 755 756SELECT fy FROM atestp2; -- ok 757 fy 758---- 759(0 rows) 760 761SELECT atestp2 FROM atestp2; -- ok 762 atestp2 763--------- 764(0 rows) 765 766SELECT tableoid FROM atestp2; -- ok 767 tableoid 768---------- 769(0 rows) 770 771SELECT fy FROM atestc; -- fail 772ERROR: permission denied for table atestc 773SET SESSION AUTHORIZATION regress_priv_user1; 774GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2; 775SET SESSION AUTHORIZATION regress_priv_user2; 776SELECT fx FROM atestp2; -- still ok 777 fx 778---- 779(0 rows) 780 781SELECT fy FROM atestp2; -- ok 782 fy 783---- 784(0 rows) 785 786SELECT atestp2 FROM atestp2; -- ok 787 atestp2 788--------- 789(0 rows) 790 791SELECT tableoid FROM atestp2; -- ok 792 tableoid 793---------- 794(0 rows) 795 796-- child's permissions do not apply when operating on parent 797SET SESSION AUTHORIZATION regress_priv_user1; 798REVOKE ALL ON atestc FROM regress_priv_user2; 799GRANT ALL ON atestp1 TO regress_priv_user2; 800SET SESSION AUTHORIZATION regress_priv_user2; 801SELECT f2 FROM atestp1; -- ok 802 f2 803---- 804(0 rows) 805 806SELECT f2 FROM atestc; -- fail 807ERROR: permission denied for table atestc 808DELETE FROM atestp1; -- ok 809DELETE FROM atestc; -- fail 810ERROR: permission denied for table atestc 811UPDATE atestp1 SET f1 = 1; -- ok 812UPDATE atestc SET f1 = 1; -- fail 813ERROR: permission denied for table atestc 814TRUNCATE atestp1; -- ok 815TRUNCATE atestc; -- fail 816ERROR: permission denied for table atestc 817BEGIN; 818LOCK atestp1; 819END; 820BEGIN; 821LOCK atestc; 822ERROR: permission denied for table atestc 823END; 824-- privileges on functions, languages 825-- switch to superuser 826\c - 827REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC; 828GRANT USAGE ON LANGUAGE sql TO regress_priv_user1; -- ok 829GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail 830ERROR: language "c" is not trusted 831DETAIL: GRANT and REVOKE are not allowed on untrusted languages, because only superusers can use untrusted languages. 832SET SESSION AUTHORIZATION regress_priv_user1; 833GRANT USAGE ON LANGUAGE sql TO regress_priv_user2; -- fail 834WARNING: no privileges were granted for "sql" 835CREATE FUNCTION priv_testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; 836CREATE FUNCTION priv_testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 837CREATE AGGREGATE priv_testagg1(int) (sfunc = int4pl, stype = int4); 838CREATE PROCEDURE priv_testproc1(int) AS 'select $1;' LANGUAGE sql; 839REVOKE ALL ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) FROM PUBLIC; 840GRANT EXECUTE ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) TO regress_priv_user2; 841REVOKE ALL ON FUNCTION priv_testproc1(int) FROM PUBLIC; -- fail, not a function 842ERROR: priv_testproc1(integer) is not a function 843REVOKE ALL ON PROCEDURE priv_testproc1(int) FROM PUBLIC; 844GRANT EXECUTE ON PROCEDURE priv_testproc1(int) TO regress_priv_user2; 845GRANT USAGE ON FUNCTION priv_testfunc1(int) TO regress_priv_user3; -- semantic error 846ERROR: invalid privilege type USAGE for function 847GRANT USAGE ON FUNCTION priv_testagg1(int) TO regress_priv_user3; -- semantic error 848ERROR: invalid privilege type USAGE for function 849GRANT USAGE ON PROCEDURE priv_testproc1(int) TO regress_priv_user3; -- semantic error 850ERROR: invalid privilege type USAGE for procedure 851GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc1(int) TO regress_priv_user4; 852GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc_nosuch(int) TO regress_priv_user4; 853ERROR: function priv_testfunc_nosuch(integer) does not exist 854GRANT ALL PRIVILEGES ON FUNCTION priv_testagg1(int) TO regress_priv_user4; 855GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4; 856CREATE FUNCTION priv_testfunc4(boolean) RETURNS text 857 AS 'select col1 from atest2 where col2 = $1;' 858 LANGUAGE sql SECURITY DEFINER; 859GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3; 860SET SESSION AUTHORIZATION regress_priv_user2; 861SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok 862 priv_testfunc1 | priv_testfunc2 863----------------+---------------- 864 10 | 15 865(1 row) 866 867CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail 868ERROR: permission denied for language sql 869SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok 870 priv_testagg1 871--------------- 872 6 873(1 row) 874 875CALL priv_testproc1(6); -- ok 876SET SESSION AUTHORIZATION regress_priv_user3; 877SELECT priv_testfunc1(5); -- fail 878ERROR: permission denied for function priv_testfunc1 879SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail 880ERROR: permission denied for aggregate priv_testagg1 881CALL priv_testproc1(6); -- fail 882ERROR: permission denied for procedure priv_testproc1 883SELECT col1 FROM atest2 WHERE col2 = true; -- fail 884ERROR: permission denied for table atest2 885SELECT priv_testfunc4(true); -- ok 886 priv_testfunc4 887---------------- 888 bar 889(1 row) 890 891SET SESSION AUTHORIZATION regress_priv_user4; 892SELECT priv_testfunc1(5); -- ok 893 priv_testfunc1 894---------------- 895 10 896(1 row) 897 898SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok 899 priv_testagg1 900--------------- 901 6 902(1 row) 903 904CALL priv_testproc1(6); -- ok 905DROP FUNCTION priv_testfunc1(int); -- fail 906ERROR: must be owner of function priv_testfunc1 907DROP AGGREGATE priv_testagg1(int); -- fail 908ERROR: must be owner of aggregate priv_testagg1 909DROP PROCEDURE priv_testproc1(int); -- fail 910ERROR: must be owner of procedure priv_testproc1 911\c - 912DROP FUNCTION priv_testfunc1(int); -- ok 913-- restore to sanity 914GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC; 915-- verify privilege checks on array-element coercions 916BEGIN; 917SELECT '{1}'::int4[]::int8[]; 918 int8 919------ 920 {1} 921(1 row) 922 923REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC; 924SELECT '{1}'::int4[]::int8[]; --superuser, succeed 925 int8 926------ 927 {1} 928(1 row) 929 930SET SESSION AUTHORIZATION regress_priv_user4; 931SELECT '{1}'::int4[]::int8[]; --other user, fail 932ERROR: permission denied for function int8 933ROLLBACK; 934-- privileges on types 935-- switch to superuser 936\c - 937CREATE TYPE priv_testtype1 AS (a int, b text); 938REVOKE USAGE ON TYPE priv_testtype1 FROM PUBLIC; 939GRANT USAGE ON TYPE priv_testtype1 TO regress_priv_user2; 940GRANT USAGE ON TYPE _priv_testtype1 TO regress_priv_user2; -- fail 941ERROR: cannot set privileges of array types 942HINT: Set the privileges of the element type instead. 943GRANT USAGE ON DOMAIN priv_testtype1 TO regress_priv_user2; -- fail 944ERROR: "priv_testtype1" is not a domain 945CREATE DOMAIN priv_testdomain1 AS int; 946REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC; 947GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2; 948GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok 949SET SESSION AUTHORIZATION regress_priv_user1; 950-- commands that should fail 951CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint); 952ERROR: permission denied for type priv_testdomain1 953CREATE DOMAIN priv_testdomain2a AS priv_testdomain1; 954ERROR: permission denied for type priv_testdomain1 955CREATE DOMAIN priv_testdomain3a AS int; 956CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3a AS $$ SELECT $1::priv_testdomain3a $$ LANGUAGE SQL; 957CREATE CAST (priv_testdomain1 AS priv_testdomain3a) WITH FUNCTION castfunc(int); 958ERROR: permission denied for type priv_testdomain1 959DROP FUNCTION castfunc(int) CASCADE; 960DROP DOMAIN priv_testdomain3a; 961CREATE FUNCTION priv_testfunc5a(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 962ERROR: permission denied for type priv_testdomain1 963CREATE FUNCTION priv_testfunc6a(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$; 964ERROR: permission denied for type priv_testdomain1 965CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = priv_testdomain1, RIGHTARG = priv_testdomain1); 966ERROR: permission denied for type priv_testdomain1 967CREATE TABLE test5a (a int, b priv_testdomain1); 968ERROR: permission denied for type priv_testdomain1 969CREATE TABLE test6a OF priv_testtype1; 970ERROR: permission denied for type priv_testtype1 971CREATE TABLE test10a (a int[], b priv_testtype1[]); 972ERROR: permission denied for type priv_testtype1 973CREATE TABLE test9a (a int, b int); 974ALTER TABLE test9a ADD COLUMN c priv_testdomain1; 975ERROR: permission denied for type priv_testdomain1 976ALTER TABLE test9a ALTER COLUMN b TYPE priv_testdomain1; 977ERROR: permission denied for type priv_testdomain1 978CREATE TYPE test7a AS (a int, b priv_testdomain1); 979ERROR: permission denied for type priv_testdomain1 980CREATE TYPE test8a AS (a int, b int); 981ALTER TYPE test8a ADD ATTRIBUTE c priv_testdomain1; 982ERROR: permission denied for type priv_testdomain1 983ALTER TYPE test8a ALTER ATTRIBUTE b TYPE priv_testdomain1; 984ERROR: permission denied for type priv_testdomain1 985CREATE TABLE test11a AS (SELECT 1::priv_testdomain1 AS a); 986ERROR: permission denied for type priv_testdomain1 987REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC; 988ERROR: permission denied for type priv_testtype1 989SET SESSION AUTHORIZATION regress_priv_user2; 990-- commands that should succeed 991CREATE AGGREGATE priv_testagg1b(priv_testdomain1) (sfunc = int4_sum, stype = bigint); 992CREATE DOMAIN priv_testdomain2b AS priv_testdomain1; 993CREATE DOMAIN priv_testdomain3b AS int; 994CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3b AS $$ SELECT $1::priv_testdomain3b $$ LANGUAGE SQL; 995CREATE CAST (priv_testdomain1 AS priv_testdomain3b) WITH FUNCTION castfunc(int); 996WARNING: cast will be ignored because the source data type is a domain 997CREATE FUNCTION priv_testfunc5b(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 998CREATE FUNCTION priv_testfunc6b(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$; 999CREATE OPERATOR !! (PROCEDURE = priv_testfunc5b, RIGHTARG = priv_testdomain1); 1000CREATE TABLE test5b (a int, b priv_testdomain1); 1001CREATE TABLE test6b OF priv_testtype1; 1002CREATE TABLE test10b (a int[], b priv_testtype1[]); 1003CREATE TABLE test9b (a int, b int); 1004ALTER TABLE test9b ADD COLUMN c priv_testdomain1; 1005ALTER TABLE test9b ALTER COLUMN b TYPE priv_testdomain1; 1006CREATE TYPE test7b AS (a int, b priv_testdomain1); 1007CREATE TYPE test8b AS (a int, b int); 1008ALTER TYPE test8b ADD ATTRIBUTE c priv_testdomain1; 1009ALTER TYPE test8b ALTER ATTRIBUTE b TYPE priv_testdomain1; 1010CREATE TABLE test11b AS (SELECT 1::priv_testdomain1 AS a); 1011REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC; 1012WARNING: no privileges could be revoked for "priv_testtype1" 1013\c - 1014DROP AGGREGATE priv_testagg1b(priv_testdomain1); 1015DROP DOMAIN priv_testdomain2b; 1016DROP OPERATOR !! (NONE, priv_testdomain1); 1017DROP FUNCTION priv_testfunc5b(a priv_testdomain1); 1018DROP FUNCTION priv_testfunc6b(b int); 1019DROP TABLE test5b; 1020DROP TABLE test6b; 1021DROP TABLE test9b; 1022DROP TABLE test10b; 1023DROP TYPE test7b; 1024DROP TYPE test8b; 1025DROP CAST (priv_testdomain1 AS priv_testdomain3b); 1026DROP FUNCTION castfunc(int) CASCADE; 1027DROP DOMAIN priv_testdomain3b; 1028DROP TABLE test11b; 1029DROP TYPE priv_testtype1; -- ok 1030DROP DOMAIN priv_testdomain1; -- ok 1031-- truncate 1032SET SESSION AUTHORIZATION regress_priv_user5; 1033TRUNCATE atest2; -- ok 1034TRUNCATE atest3; -- fail 1035ERROR: permission denied for table atest3 1036-- has_table_privilege function 1037-- bad-input checks 1038select has_table_privilege(NULL,'pg_authid','select'); 1039 has_table_privilege 1040--------------------- 1041 1042(1 row) 1043 1044select has_table_privilege('pg_shad','select'); 1045ERROR: relation "pg_shad" does not exist 1046select has_table_privilege('nosuchuser','pg_authid','select'); 1047ERROR: role "nosuchuser" does not exist 1048select has_table_privilege('pg_authid','sel'); 1049ERROR: unrecognized privilege type: "sel" 1050select has_table_privilege(-999999,'pg_authid','update'); 1051 has_table_privilege 1052--------------------- 1053 f 1054(1 row) 1055 1056select has_table_privilege(1,'select'); 1057 has_table_privilege 1058--------------------- 1059 1060(1 row) 1061 1062-- superuser 1063\c - 1064select has_table_privilege(current_user,'pg_authid','select'); 1065 has_table_privilege 1066--------------------- 1067 t 1068(1 row) 1069 1070select has_table_privilege(current_user,'pg_authid','insert'); 1071 has_table_privilege 1072--------------------- 1073 t 1074(1 row) 1075 1076select has_table_privilege(t2.oid,'pg_authid','update') 1077from (select oid from pg_roles where rolname = current_user) as t2; 1078 has_table_privilege 1079--------------------- 1080 t 1081(1 row) 1082 1083select has_table_privilege(t2.oid,'pg_authid','delete') 1084from (select oid from pg_roles where rolname = current_user) as t2; 1085 has_table_privilege 1086--------------------- 1087 t 1088(1 row) 1089 1090-- 'rule' privilege no longer exists, but for backwards compatibility 1091-- has_table_privilege still recognizes the keyword and says FALSE 1092select has_table_privilege(current_user,t1.oid,'rule') 1093from (select oid from pg_class where relname = 'pg_authid') as t1; 1094 has_table_privilege 1095--------------------- 1096 f 1097(1 row) 1098 1099select has_table_privilege(current_user,t1.oid,'references') 1100from (select oid from pg_class where relname = 'pg_authid') as t1; 1101 has_table_privilege 1102--------------------- 1103 t 1104(1 row) 1105 1106select has_table_privilege(t2.oid,t1.oid,'select') 1107from (select oid from pg_class where relname = 'pg_authid') as t1, 1108 (select oid from pg_roles where rolname = current_user) as t2; 1109 has_table_privilege 1110--------------------- 1111 t 1112(1 row) 1113 1114select has_table_privilege(t2.oid,t1.oid,'insert') 1115from (select oid from pg_class where relname = 'pg_authid') as t1, 1116 (select oid from pg_roles where rolname = current_user) as t2; 1117 has_table_privilege 1118--------------------- 1119 t 1120(1 row) 1121 1122select has_table_privilege('pg_authid','update'); 1123 has_table_privilege 1124--------------------- 1125 t 1126(1 row) 1127 1128select has_table_privilege('pg_authid','delete'); 1129 has_table_privilege 1130--------------------- 1131 t 1132(1 row) 1133 1134select has_table_privilege('pg_authid','truncate'); 1135 has_table_privilege 1136--------------------- 1137 t 1138(1 row) 1139 1140select has_table_privilege(t1.oid,'select') 1141from (select oid from pg_class where relname = 'pg_authid') as t1; 1142 has_table_privilege 1143--------------------- 1144 t 1145(1 row) 1146 1147select has_table_privilege(t1.oid,'trigger') 1148from (select oid from pg_class where relname = 'pg_authid') as t1; 1149 has_table_privilege 1150--------------------- 1151 t 1152(1 row) 1153 1154-- non-superuser 1155SET SESSION AUTHORIZATION regress_priv_user3; 1156select has_table_privilege(current_user,'pg_class','select'); 1157 has_table_privilege 1158--------------------- 1159 t 1160(1 row) 1161 1162select has_table_privilege(current_user,'pg_class','insert'); 1163 has_table_privilege 1164--------------------- 1165 f 1166(1 row) 1167 1168select has_table_privilege(t2.oid,'pg_class','update') 1169from (select oid from pg_roles where rolname = current_user) as t2; 1170 has_table_privilege 1171--------------------- 1172 f 1173(1 row) 1174 1175select has_table_privilege(t2.oid,'pg_class','delete') 1176from (select oid from pg_roles where rolname = current_user) as t2; 1177 has_table_privilege 1178--------------------- 1179 f 1180(1 row) 1181 1182select has_table_privilege(current_user,t1.oid,'references') 1183from (select oid from pg_class where relname = 'pg_class') as t1; 1184 has_table_privilege 1185--------------------- 1186 f 1187(1 row) 1188 1189select has_table_privilege(t2.oid,t1.oid,'select') 1190from (select oid from pg_class where relname = 'pg_class') as t1, 1191 (select oid from pg_roles where rolname = current_user) as t2; 1192 has_table_privilege 1193--------------------- 1194 t 1195(1 row) 1196 1197select has_table_privilege(t2.oid,t1.oid,'insert') 1198from (select oid from pg_class where relname = 'pg_class') as t1, 1199 (select oid from pg_roles where rolname = current_user) as t2; 1200 has_table_privilege 1201--------------------- 1202 f 1203(1 row) 1204 1205select has_table_privilege('pg_class','update'); 1206 has_table_privilege 1207--------------------- 1208 f 1209(1 row) 1210 1211select has_table_privilege('pg_class','delete'); 1212 has_table_privilege 1213--------------------- 1214 f 1215(1 row) 1216 1217select has_table_privilege('pg_class','truncate'); 1218 has_table_privilege 1219--------------------- 1220 f 1221(1 row) 1222 1223select has_table_privilege(t1.oid,'select') 1224from (select oid from pg_class where relname = 'pg_class') as t1; 1225 has_table_privilege 1226--------------------- 1227 t 1228(1 row) 1229 1230select has_table_privilege(t1.oid,'trigger') 1231from (select oid from pg_class where relname = 'pg_class') as t1; 1232 has_table_privilege 1233--------------------- 1234 f 1235(1 row) 1236 1237select has_table_privilege(current_user,'atest1','select'); 1238 has_table_privilege 1239--------------------- 1240 t 1241(1 row) 1242 1243select has_table_privilege(current_user,'atest1','insert'); 1244 has_table_privilege 1245--------------------- 1246 f 1247(1 row) 1248 1249select has_table_privilege(t2.oid,'atest1','update') 1250from (select oid from pg_roles where rolname = current_user) as t2; 1251 has_table_privilege 1252--------------------- 1253 f 1254(1 row) 1255 1256select has_table_privilege(t2.oid,'atest1','delete') 1257from (select oid from pg_roles where rolname = current_user) as t2; 1258 has_table_privilege 1259--------------------- 1260 f 1261(1 row) 1262 1263select has_table_privilege(current_user,t1.oid,'references') 1264from (select oid from pg_class where relname = 'atest1') as t1; 1265 has_table_privilege 1266--------------------- 1267 f 1268(1 row) 1269 1270select has_table_privilege(t2.oid,t1.oid,'select') 1271from (select oid from pg_class where relname = 'atest1') as t1, 1272 (select oid from pg_roles where rolname = current_user) as t2; 1273 has_table_privilege 1274--------------------- 1275 t 1276(1 row) 1277 1278select has_table_privilege(t2.oid,t1.oid,'insert') 1279from (select oid from pg_class where relname = 'atest1') as t1, 1280 (select oid from pg_roles where rolname = current_user) as t2; 1281 has_table_privilege 1282--------------------- 1283 f 1284(1 row) 1285 1286select has_table_privilege('atest1','update'); 1287 has_table_privilege 1288--------------------- 1289 f 1290(1 row) 1291 1292select has_table_privilege('atest1','delete'); 1293 has_table_privilege 1294--------------------- 1295 f 1296(1 row) 1297 1298select has_table_privilege('atest1','truncate'); 1299 has_table_privilege 1300--------------------- 1301 f 1302(1 row) 1303 1304select has_table_privilege(t1.oid,'select') 1305from (select oid from pg_class where relname = 'atest1') as t1; 1306 has_table_privilege 1307--------------------- 1308 t 1309(1 row) 1310 1311select has_table_privilege(t1.oid,'trigger') 1312from (select oid from pg_class where relname = 'atest1') as t1; 1313 has_table_privilege 1314--------------------- 1315 f 1316(1 row) 1317 1318-- has_column_privilege function 1319-- bad-input checks (as non-super-user) 1320select has_column_privilege('pg_authid',NULL,'select'); 1321 has_column_privilege 1322---------------------- 1323 1324(1 row) 1325 1326select has_column_privilege('pg_authid','nosuchcol','select'); 1327ERROR: column "nosuchcol" of relation "pg_authid" does not exist 1328select has_column_privilege(9999,'nosuchcol','select'); 1329 has_column_privilege 1330---------------------- 1331 1332(1 row) 1333 1334select has_column_privilege(9999,99::int2,'select'); 1335 has_column_privilege 1336---------------------- 1337 1338(1 row) 1339 1340select has_column_privilege('pg_authid',99::int2,'select'); 1341 has_column_privilege 1342---------------------- 1343 1344(1 row) 1345 1346select has_column_privilege(9999,99::int2,'select'); 1347 has_column_privilege 1348---------------------- 1349 1350(1 row) 1351 1352create temp table mytable(f1 int, f2 int, f3 int); 1353alter table mytable drop column f2; 1354select has_column_privilege('mytable','f2','select'); 1355ERROR: column "f2" of relation "mytable" does not exist 1356select has_column_privilege('mytable','........pg.dropped.2........','select'); 1357 has_column_privilege 1358---------------------- 1359 1360(1 row) 1361 1362select has_column_privilege('mytable',2::int2,'select'); 1363 has_column_privilege 1364---------------------- 1365 t 1366(1 row) 1367 1368revoke select on table mytable from regress_priv_user3; 1369select has_column_privilege('mytable',2::int2,'select'); 1370 has_column_privilege 1371---------------------- 1372 1373(1 row) 1374 1375drop table mytable; 1376-- Grant options 1377SET SESSION AUTHORIZATION regress_priv_user1; 1378CREATE TABLE atest4 (a int); 1379GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION; 1380GRANT UPDATE ON atest4 TO regress_priv_user2; 1381GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION; 1382SET SESSION AUTHORIZATION regress_priv_user2; 1383GRANT SELECT ON atest4 TO regress_priv_user3; 1384GRANT UPDATE ON atest4 TO regress_priv_user3; -- fail 1385WARNING: no privileges were granted for "atest4" 1386SET SESSION AUTHORIZATION regress_priv_user1; 1387REVOKE SELECT ON atest4 FROM regress_priv_user3; -- does nothing 1388SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- true 1389 has_table_privilege 1390--------------------- 1391 t 1392(1 row) 1393 1394REVOKE SELECT ON atest4 FROM regress_priv_user2; -- fail 1395ERROR: dependent privileges exist 1396HINT: Use CASCADE to revoke them too. 1397REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_priv_user2 CASCADE; -- ok 1398SELECT has_table_privilege('regress_priv_user2', 'atest4', 'SELECT'); -- true 1399 has_table_privilege 1400--------------------- 1401 t 1402(1 row) 1403 1404SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- false 1405 has_table_privilege 1406--------------------- 1407 f 1408(1 row) 1409 1410SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true 1411 has_table_privilege 1412--------------------- 1413 t 1414(1 row) 1415 1416-- security-restricted operations 1417\c - 1418CREATE ROLE regress_sro_user; 1419SET SESSION AUTHORIZATION regress_sro_user; 1420CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS 1421 'GRANT regress_priv_group2 TO regress_sro_user'; 1422CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 1423 'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant(); SELECT true'; 1424-- REFRESH of this MV will queue a GRANT at end of transaction 1425CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA; 1426REFRESH MATERIALIZED VIEW sro_mv; 1427ERROR: cannot create a cursor WITH HOLD within security-restricted operation 1428CONTEXT: SQL function "mv_action" statement 1 1429\c - 1430REFRESH MATERIALIZED VIEW sro_mv; 1431ERROR: cannot create a cursor WITH HOLD within security-restricted operation 1432CONTEXT: SQL function "mv_action" statement 1 1433SET SESSION AUTHORIZATION regress_sro_user; 1434-- INSERT to this table will queue a GRANT at end of transaction 1435CREATE TABLE sro_trojan_table (); 1436CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS 1437 'BEGIN PERFORM unwanted_grant(); RETURN NULL; END'; 1438CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table 1439 INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan(); 1440-- Now, REFRESH will issue such an INSERT, queueing the GRANT 1441CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 1442 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; 1443REFRESH MATERIALIZED VIEW sro_mv; 1444ERROR: cannot fire deferred trigger within security-restricted operation 1445CONTEXT: SQL function "mv_action" statement 1 1446\c - 1447REFRESH MATERIALIZED VIEW sro_mv; 1448ERROR: cannot fire deferred trigger within security-restricted operation 1449CONTEXT: SQL function "mv_action" statement 1 1450BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; 1451ERROR: must have admin option on role "regress_priv_group2" 1452CONTEXT: SQL function "unwanted_grant" statement 1 1453SQL statement "SELECT unwanted_grant()" 1454PL/pgSQL function sro_trojan() line 1 at PERFORM 1455SQL function "mv_action" statement 1 1456DROP OWNED BY regress_sro_user; 1457DROP ROLE regress_sro_user; 1458-- Admin options 1459SET SESSION AUTHORIZATION regress_priv_user4; 1460CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS 1461 'GRANT regress_priv_group2 TO regress_priv_user5'; 1462GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION 1463SET ROLE regress_priv_group2; 1464GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege 1465ERROR: must have admin option on role "regress_priv_group2" 1466SET SESSION AUTHORIZATION regress_priv_user1; 1467GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION 1468ERROR: must have admin option on role "regress_priv_group2" 1469SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN 1470NOTICE: role "regress_priv_user5" is already a member of role "regress_priv_group2" 1471 dogrant_ok 1472------------ 1473 1474(1 row) 1475 1476SET ROLE regress_priv_group2; 1477GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help 1478ERROR: must have admin option on role "regress_priv_group2" 1479SET SESSION AUTHORIZATION regress_priv_group2; 1480GRANT regress_priv_group2 TO regress_priv_user5; -- ok: a role can self-admin 1481NOTICE: role "regress_priv_user5" is already a member of role "regress_priv_group2" 1482CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS 1483 'GRANT regress_priv_group2 TO regress_priv_user5'; 1484SELECT dogrant_fails(); -- fails: no self-admin in SECURITY DEFINER 1485ERROR: must have admin option on role "regress_priv_group2" 1486CONTEXT: SQL function "dogrant_fails" statement 1 1487DROP FUNCTION dogrant_fails(); 1488SET SESSION AUTHORIZATION regress_priv_user4; 1489DROP FUNCTION dogrant_ok(); 1490REVOKE regress_priv_group2 FROM regress_priv_user5; 1491-- has_sequence_privilege tests 1492\c - 1493CREATE SEQUENCE x_seq; 1494GRANT USAGE on x_seq to regress_priv_user2; 1495SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT'); 1496ERROR: "atest1" is not a sequence 1497SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT'); 1498ERROR: unrecognized privilege type: "INSERT" 1499SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT'); 1500 has_sequence_privilege 1501------------------------ 1502 f 1503(1 row) 1504 1505SET SESSION AUTHORIZATION regress_priv_user2; 1506SELECT has_sequence_privilege('x_seq', 'USAGE'); 1507 has_sequence_privilege 1508------------------------ 1509 t 1510(1 row) 1511 1512-- largeobject privilege tests 1513\c - 1514SET SESSION AUTHORIZATION regress_priv_user1; 1515SELECT lo_create(1001); 1516 lo_create 1517----------- 1518 1001 1519(1 row) 1520 1521SELECT lo_create(1002); 1522 lo_create 1523----------- 1524 1002 1525(1 row) 1526 1527SELECT lo_create(1003); 1528 lo_create 1529----------- 1530 1003 1531(1 row) 1532 1533SELECT lo_create(1004); 1534 lo_create 1535----------- 1536 1004 1537(1 row) 1538 1539SELECT lo_create(1005); 1540 lo_create 1541----------- 1542 1005 1543(1 row) 1544 1545GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC; 1546GRANT SELECT ON LARGE OBJECT 1003 TO regress_priv_user2; 1547GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_priv_user2; 1548GRANT ALL ON LARGE OBJECT 1005 TO regress_priv_user2; 1549GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user2 WITH GRANT OPTION; 1550GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC; -- to be failed 1551ERROR: invalid privilege type INSERT for large object 1552GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser; -- to be failed 1553ERROR: role "nosuchuser" does not exist 1554GRANT SELECT, UPDATE ON LARGE OBJECT 999 TO PUBLIC; -- to be failed 1555ERROR: large object 999 does not exist 1556\c - 1557SET SESSION AUTHORIZATION regress_priv_user2; 1558SELECT lo_create(2001); 1559 lo_create 1560----------- 1561 2001 1562(1 row) 1563 1564SELECT lo_create(2002); 1565 lo_create 1566----------- 1567 2002 1568(1 row) 1569 1570SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now 1571 loread 1572-------- 1573 \x 1574(1 row) 1575 1576SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode 1577ERROR: large object descriptor 0 was not opened for writing 1578SELECT loread(lo_open(1001, x'40000'::int), 32); 1579 loread 1580-------- 1581 \x 1582(1 row) 1583 1584SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 1585ERROR: permission denied for large object 1002 1586SELECT loread(lo_open(1003, x'40000'::int), 32); 1587 loread 1588-------- 1589 \x 1590(1 row) 1591 1592SELECT loread(lo_open(1004, x'40000'::int), 32); 1593 loread 1594-------- 1595 \x 1596(1 row) 1597 1598SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd'); 1599 lowrite 1600--------- 1601 4 1602(1 row) 1603 1604SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 1605ERROR: permission denied for large object 1002 1606SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied 1607ERROR: permission denied for large object 1003 1608SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd'); 1609 lowrite 1610--------- 1611 4 1612(1 row) 1613 1614GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3; 1615GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3; -- to be denied 1616ERROR: large object 1006 does not exist 1617REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC; 1618GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3; 1619SELECT lo_unlink(1001); -- to be denied 1620ERROR: must be owner of large object 1001 1621SELECT lo_unlink(2002); 1622 lo_unlink 1623----------- 1624 1 1625(1 row) 1626 1627\c - 1628-- confirm ACL setting 1629SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 1630 oid | ownername | lomacl 1631------+--------------------+------------------------------------------------------------------------------------------------------------------------------ 1632 1001 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,=rw/regress_priv_user1} 1633 1002 | regress_priv_user1 | 1634 1003 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r/regress_priv_user1} 1635 1004 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=rw/regress_priv_user1} 1636 1005 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r*w/regress_priv_user1,regress_priv_user3=r/regress_priv_user2} 1637 2001 | regress_priv_user2 | {regress_priv_user2=rw/regress_priv_user2,regress_priv_user3=rw/regress_priv_user2} 1638(6 rows) 1639 1640SET SESSION AUTHORIZATION regress_priv_user3; 1641SELECT loread(lo_open(1001, x'40000'::int), 32); 1642 loread 1643------------ 1644 \x61626364 1645(1 row) 1646 1647SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied 1648ERROR: permission denied for large object 1003 1649SELECT loread(lo_open(1005, x'40000'::int), 32); 1650 loread 1651-------- 1652 \x 1653(1 row) 1654 1655SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied 1656ERROR: permission denied for large object 1005 1657SELECT lo_truncate(lo_open(2001, x'20000'::int), 10); 1658 lo_truncate 1659------------- 1660 0 1661(1 row) 1662 1663-- compatibility mode in largeobject permission 1664\c - 1665SET lo_compat_privileges = false; -- default setting 1666SET SESSION AUTHORIZATION regress_priv_user4; 1667SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 1668ERROR: permission denied for large object 1002 1669SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 1670ERROR: permission denied for large object 1002 1671SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied 1672ERROR: permission denied for large object 1002 1673SELECT lo_put(1002, 1, 'abcd'); -- to be denied 1674ERROR: permission denied for large object 1002 1675SELECT lo_unlink(1002); -- to be denied 1676ERROR: must be owner of large object 1002 1677SELECT lo_export(1001, '/dev/null'); -- to be denied 1678ERROR: permission denied for function lo_export 1679SELECT lo_import('/dev/null'); -- to be denied 1680ERROR: permission denied for function lo_import 1681SELECT lo_import('/dev/null', 2003); -- to be denied 1682ERROR: permission denied for function lo_import 1683\c - 1684SET lo_compat_privileges = true; -- compatibility mode 1685SET SESSION AUTHORIZATION regress_priv_user4; 1686SELECT loread(lo_open(1002, x'40000'::int), 32); 1687 loread 1688-------- 1689 \x 1690(1 row) 1691 1692SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); 1693 lowrite 1694--------- 1695 4 1696(1 row) 1697 1698SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); 1699 lo_truncate 1700------------- 1701 0 1702(1 row) 1703 1704SELECT lo_unlink(1002); 1705 lo_unlink 1706----------- 1707 1 1708(1 row) 1709 1710SELECT lo_export(1001, '/dev/null'); -- to be denied 1711ERROR: permission denied for function lo_export 1712-- don't allow unpriv users to access pg_largeobject contents 1713\c - 1714SELECT * FROM pg_largeobject LIMIT 0; 1715 loid | pageno | data 1716------+--------+------ 1717(0 rows) 1718 1719SET SESSION AUTHORIZATION regress_priv_user1; 1720SELECT * FROM pg_largeobject LIMIT 0; -- to be denied 1721ERROR: permission denied for table pg_largeobject 1722-- test default ACLs 1723\c - 1724CREATE SCHEMA testns; 1725GRANT ALL ON SCHEMA testns TO regress_priv_user1; 1726CREATE TABLE testns.acltest1 (x int); 1727SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no 1728 has_table_privilege 1729--------------------- 1730 f 1731(1 row) 1732 1733SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1734 has_table_privilege 1735--------------------- 1736 f 1737(1 row) 1738 1739-- placeholder for test with duplicated schema and role names 1740ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public; 1741SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no 1742 has_table_privilege 1743--------------------- 1744 f 1745(1 row) 1746 1747SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1748 has_table_privilege 1749--------------------- 1750 f 1751(1 row) 1752 1753DROP TABLE testns.acltest1; 1754CREATE TABLE testns.acltest1 (x int); 1755SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes 1756 has_table_privilege 1757--------------------- 1758 t 1759(1 row) 1760 1761SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1762 has_table_privilege 1763--------------------- 1764 f 1765(1 row) 1766 1767ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1; 1768DROP TABLE testns.acltest1; 1769CREATE TABLE testns.acltest1 (x int); 1770SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes 1771 has_table_privilege 1772--------------------- 1773 t 1774(1 row) 1775 1776SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- yes 1777 has_table_privilege 1778--------------------- 1779 t 1780(1 row) 1781 1782ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1; 1783DROP TABLE testns.acltest1; 1784CREATE TABLE testns.acltest1 (x int); 1785SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes 1786 has_table_privilege 1787--------------------- 1788 t 1789(1 row) 1790 1791SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1792 has_table_privilege 1793--------------------- 1794 f 1795(1 row) 1796 1797ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM public; 1798ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_priv_user2; -- error 1799ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS 1800-- 1801-- Testing blanket default grants is very hazardous since it might change 1802-- the privileges attached to objects created by concurrent regression tests. 1803-- To avoid that, be sure to revoke the privileges again before committing. 1804-- 1805BEGIN; 1806ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2; 1807CREATE SCHEMA testns2; 1808SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes 1809 has_schema_privilege 1810---------------------- 1811 t 1812(1 row) 1813 1814SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no 1815 has_schema_privilege 1816---------------------- 1817 f 1818(1 row) 1819 1820ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2; 1821CREATE SCHEMA testns3; 1822SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'USAGE'); -- no 1823 has_schema_privilege 1824---------------------- 1825 f 1826(1 row) 1827 1828SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'CREATE'); -- no 1829 has_schema_privilege 1830---------------------- 1831 f 1832(1 row) 1833 1834ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2; 1835CREATE SCHEMA testns4; 1836SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'USAGE'); -- yes 1837 has_schema_privilege 1838---------------------- 1839 t 1840(1 row) 1841 1842SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes 1843 has_schema_privilege 1844---------------------- 1845 t 1846(1 row) 1847 1848ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2; 1849COMMIT; 1850CREATE SCHEMA testns5; 1851SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE'); -- no 1852 has_schema_privilege 1853---------------------- 1854 f 1855(1 row) 1856 1857SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'CREATE'); -- no 1858 has_schema_privilege 1859---------------------- 1860 f 1861(1 row) 1862 1863SET ROLE regress_priv_user1; 1864CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1865CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); 1866CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; 1867SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- no 1868 has_function_privilege 1869------------------------ 1870 f 1871(1 row) 1872 1873SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- no 1874 has_function_privilege 1875------------------------ 1876 f 1877(1 row) 1878 1879SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- no 1880 has_function_privilege 1881------------------------ 1882 f 1883(1 row) 1884 1885ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public; 1886DROP FUNCTION testns.foo(); 1887CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1888DROP AGGREGATE testns.agg1(int); 1889CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); 1890DROP PROCEDURE testns.bar(); 1891CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; 1892SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- yes 1893 has_function_privilege 1894------------------------ 1895 t 1896(1 row) 1897 1898SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes 1899 has_function_privilege 1900------------------------ 1901 t 1902(1 row) 1903 1904SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here) 1905 has_function_privilege 1906------------------------ 1907 t 1908(1 row) 1909 1910DROP FUNCTION testns.foo(); 1911DROP AGGREGATE testns.agg1(int); 1912DROP PROCEDURE testns.bar(); 1913ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE USAGE ON TYPES FROM public; 1914CREATE DOMAIN testns.priv_testdomain1 AS int; 1915SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- no 1916 has_type_privilege 1917-------------------- 1918 f 1919(1 row) 1920 1921ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public; 1922DROP DOMAIN testns.priv_testdomain1; 1923CREATE DOMAIN testns.priv_testdomain1 AS int; 1924SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- yes 1925 has_type_privilege 1926-------------------- 1927 t 1928(1 row) 1929 1930DROP DOMAIN testns.priv_testdomain1; 1931RESET ROLE; 1932SELECT count(*) 1933 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1934 WHERE nspname = 'testns'; 1935 count 1936------- 1937 3 1938(1 row) 1939 1940DROP SCHEMA testns CASCADE; 1941NOTICE: drop cascades to table testns.acltest1 1942DROP SCHEMA testns2 CASCADE; 1943DROP SCHEMA testns3 CASCADE; 1944DROP SCHEMA testns4 CASCADE; 1945DROP SCHEMA testns5 CASCADE; 1946SELECT d.* -- check that entries went away 1947 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1948 WHERE nspname IS NULL AND defaclnamespace != 0; 1949 oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl 1950-----+------------+-----------------+---------------+----------- 1951(0 rows) 1952 1953-- Grant on all objects of given type in a schema 1954\c - 1955CREATE SCHEMA testns; 1956CREATE TABLE testns.t1 (f1 int); 1957CREATE TABLE testns.t2 (f1 int); 1958SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false 1959 has_table_privilege 1960--------------------- 1961 f 1962(1 row) 1963 1964GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1; 1965SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- true 1966 has_table_privilege 1967--------------------- 1968 t 1969(1 row) 1970 1971SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- true 1972 has_table_privilege 1973--------------------- 1974 t 1975(1 row) 1976 1977REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1; 1978SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false 1979 has_table_privilege 1980--------------------- 1981 f 1982(1 row) 1983 1984SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- false 1985 has_table_privilege 1986--------------------- 1987 f 1988(1 row) 1989 1990CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 1991CREATE AGGREGATE testns.priv_testagg(int) (sfunc = int4pl, stype = int4); 1992CREATE PROCEDURE testns.priv_testproc(int) AS 'select 3' LANGUAGE sql; 1993SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true by default 1994 has_function_privilege 1995------------------------ 1996 t 1997(1 row) 1998 1999SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true by default 2000 has_function_privilege 2001------------------------ 2002 t 2003(1 row) 2004 2005SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true by default 2006 has_function_privilege 2007------------------------ 2008 t 2009(1 row) 2010 2011REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; 2012SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- false 2013 has_function_privilege 2014------------------------ 2015 f 2016(1 row) 2017 2018SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- false 2019 has_function_privilege 2020------------------------ 2021 f 2022(1 row) 2023 2024SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- still true, not a function 2025 has_function_privilege 2026------------------------ 2027 t 2028(1 row) 2029 2030REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC; 2031SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- now false 2032 has_function_privilege 2033------------------------ 2034 f 2035(1 row) 2036 2037GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC; 2038SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true 2039 has_function_privilege 2040------------------------ 2041 t 2042(1 row) 2043 2044SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true 2045 has_function_privilege 2046------------------------ 2047 t 2048(1 row) 2049 2050SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true 2051 has_function_privilege 2052------------------------ 2053 t 2054(1 row) 2055 2056DROP SCHEMA testns CASCADE; 2057NOTICE: drop cascades to 5 other objects 2058DETAIL: drop cascades to table testns.t1 2059drop cascades to table testns.t2 2060drop cascades to function testns.priv_testfunc(integer) 2061drop cascades to function testns.priv_testagg(integer) 2062drop cascades to function testns.priv_testproc(integer) 2063-- Change owner of the schema & and rename of new schema owner 2064\c - 2065CREATE ROLE regress_schemauser1 superuser login; 2066CREATE ROLE regress_schemauser2 superuser login; 2067SET SESSION ROLE regress_schemauser1; 2068CREATE SCHEMA testns; 2069SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 2070 nspname | rolname 2071---------+--------------------- 2072 testns | regress_schemauser1 2073(1 row) 2074 2075ALTER SCHEMA testns OWNER TO regress_schemauser2; 2076ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed; 2077SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 2078 nspname | rolname 2079---------+---------------------------- 2080 testns | regress_schemauser_renamed 2081(1 row) 2082 2083set session role regress_schemauser_renamed; 2084DROP SCHEMA testns CASCADE; 2085-- clean up 2086\c - 2087DROP ROLE regress_schemauser1; 2088DROP ROLE regress_schemauser_renamed; 2089-- test that dependent privileges are revoked (or not) properly 2090\c - 2091set session role regress_priv_user1; 2092create table dep_priv_test (a int); 2093grant select on dep_priv_test to regress_priv_user2 with grant option; 2094grant select on dep_priv_test to regress_priv_user3 with grant option; 2095set session role regress_priv_user2; 2096grant select on dep_priv_test to regress_priv_user4 with grant option; 2097set session role regress_priv_user3; 2098grant select on dep_priv_test to regress_priv_user4 with grant option; 2099set session role regress_priv_user4; 2100grant select on dep_priv_test to regress_priv_user5; 2101\dp dep_priv_test 2102 Access privileges 2103 Schema | Name | Type | Access privileges | Column privileges | Policies 2104--------+---------------+-------+-----------------------------------------------+-------------------+---------- 2105 public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | 2106 | | | regress_priv_user2=r*/regress_priv_user1 +| | 2107 | | | regress_priv_user3=r*/regress_priv_user1 +| | 2108 | | | regress_priv_user4=r*/regress_priv_user2 +| | 2109 | | | regress_priv_user4=r*/regress_priv_user3 +| | 2110 | | | regress_priv_user5=r/regress_priv_user4 | | 2111(1 row) 2112 2113set session role regress_priv_user2; 2114revoke select on dep_priv_test from regress_priv_user4 cascade; 2115\dp dep_priv_test 2116 Access privileges 2117 Schema | Name | Type | Access privileges | Column privileges | Policies 2118--------+---------------+-------+-----------------------------------------------+-------------------+---------- 2119 public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | 2120 | | | regress_priv_user2=r*/regress_priv_user1 +| | 2121 | | | regress_priv_user3=r*/regress_priv_user1 +| | 2122 | | | regress_priv_user4=r*/regress_priv_user3 +| | 2123 | | | regress_priv_user5=r/regress_priv_user4 | | 2124(1 row) 2125 2126set session role regress_priv_user3; 2127revoke select on dep_priv_test from regress_priv_user4 cascade; 2128\dp dep_priv_test 2129 Access privileges 2130 Schema | Name | Type | Access privileges | Column privileges | Policies 2131--------+---------------+-------+-----------------------------------------------+-------------------+---------- 2132 public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | 2133 | | | regress_priv_user2=r*/regress_priv_user1 +| | 2134 | | | regress_priv_user3=r*/regress_priv_user1 | | 2135(1 row) 2136 2137set session role regress_priv_user1; 2138drop table dep_priv_test; 2139-- clean up 2140\c 2141drop sequence x_seq; 2142DROP AGGREGATE priv_testagg1(int); 2143DROP FUNCTION priv_testfunc2(int); 2144DROP FUNCTION priv_testfunc4(boolean); 2145DROP PROCEDURE priv_testproc1(int); 2146DROP VIEW atestv0; 2147DROP VIEW atestv1; 2148DROP VIEW atestv2; 2149-- this should cascade to drop atestv4 2150DROP VIEW atestv3 CASCADE; 2151NOTICE: drop cascades to view atestv4 2152-- this should complain "does not exist" 2153DROP VIEW atestv4; 2154ERROR: view "atestv4" does not exist 2155DROP TABLE atest1; 2156DROP TABLE atest2; 2157DROP TABLE atest3; 2158DROP TABLE atest4; 2159DROP TABLE atest5; 2160DROP TABLE atest6; 2161DROP TABLE atestc; 2162DROP TABLE atestp1; 2163DROP TABLE atestp2; 2164SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 2165 lo_unlink 2166----------- 2167 1 2168 1 2169 1 2170 1 2171 1 2172(5 rows) 2173 2174DROP GROUP regress_priv_group1; 2175DROP GROUP regress_priv_group2; 2176-- these are needed to clean up permissions 2177REVOKE USAGE ON LANGUAGE sql FROM regress_priv_user1; 2178DROP OWNED BY regress_priv_user1; 2179DROP USER regress_priv_user1; 2180DROP USER regress_priv_user2; 2181DROP USER regress_priv_user3; 2182DROP USER regress_priv_user4; 2183DROP USER regress_priv_user5; 2184DROP USER regress_priv_user6; 2185ERROR: role "regress_priv_user6" does not exist 2186-- permissions with LOCK TABLE 2187CREATE USER regress_locktable_user; 2188CREATE TABLE lock_table (a int); 2189-- LOCK TABLE and SELECT permission 2190GRANT SELECT ON lock_table TO regress_locktable_user; 2191SET SESSION AUTHORIZATION regress_locktable_user; 2192BEGIN; 2193LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail 2194ERROR: permission denied for table lock_table 2195ROLLBACK; 2196BEGIN; 2197LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass 2198COMMIT; 2199BEGIN; 2200LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 2201ERROR: permission denied for table lock_table 2202ROLLBACK; 2203\c 2204REVOKE SELECT ON lock_table FROM regress_locktable_user; 2205-- LOCK TABLE and INSERT permission 2206GRANT INSERT ON lock_table TO regress_locktable_user; 2207SET SESSION AUTHORIZATION regress_locktable_user; 2208BEGIN; 2209LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 2210COMMIT; 2211BEGIN; 2212LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2213ERROR: permission denied for table lock_table 2214ROLLBACK; 2215BEGIN; 2216LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 2217ERROR: permission denied for table lock_table 2218ROLLBACK; 2219\c 2220REVOKE INSERT ON lock_table FROM regress_locktable_user; 2221-- LOCK TABLE and UPDATE permission 2222GRANT UPDATE ON lock_table TO regress_locktable_user; 2223SET SESSION AUTHORIZATION regress_locktable_user; 2224BEGIN; 2225LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 2226COMMIT; 2227BEGIN; 2228LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2229ERROR: permission denied for table lock_table 2230ROLLBACK; 2231BEGIN; 2232LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 2233COMMIT; 2234\c 2235REVOKE UPDATE ON lock_table FROM regress_locktable_user; 2236-- LOCK TABLE and DELETE permission 2237GRANT DELETE ON lock_table TO regress_locktable_user; 2238SET SESSION AUTHORIZATION regress_locktable_user; 2239BEGIN; 2240LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 2241COMMIT; 2242BEGIN; 2243LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2244ERROR: permission denied for table lock_table 2245ROLLBACK; 2246BEGIN; 2247LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 2248COMMIT; 2249\c 2250REVOKE DELETE ON lock_table FROM regress_locktable_user; 2251-- LOCK TABLE and TRUNCATE permission 2252GRANT TRUNCATE ON lock_table TO regress_locktable_user; 2253SET SESSION AUTHORIZATION regress_locktable_user; 2254BEGIN; 2255LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 2256COMMIT; 2257BEGIN; 2258LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2259ERROR: permission denied for table lock_table 2260ROLLBACK; 2261BEGIN; 2262LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 2263COMMIT; 2264\c 2265REVOKE TRUNCATE ON lock_table FROM regress_locktable_user; 2266-- clean up 2267DROP TABLE lock_table; 2268DROP USER regress_locktable_user; 2269