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