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-- verify privilege checks on array-element coercions 763BEGIN; 764SELECT '{1}'::int4[]::int8[]; 765 int8 766------ 767 {1} 768(1 row) 769 770REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC; 771SELECT '{1}'::int4[]::int8[]; --superuser, suceed 772 int8 773------ 774 {1} 775(1 row) 776 777SET SESSION AUTHORIZATION regress_user4; 778SELECT '{1}'::int4[]::int8[]; --other user, fail 779ERROR: permission denied for function int8 780ROLLBACK; 781-- privileges on types 782-- switch to superuser 783\c - 784CREATE TYPE testtype1 AS (a int, b text); 785REVOKE USAGE ON TYPE testtype1 FROM PUBLIC; 786GRANT USAGE ON TYPE testtype1 TO regress_user2; 787GRANT USAGE ON TYPE _testtype1 TO regress_user2; -- fail 788ERROR: cannot set privileges of array types 789HINT: Set the privileges of the element type instead. 790GRANT USAGE ON DOMAIN testtype1 TO regress_user2; -- fail 791ERROR: "testtype1" is not a domain 792CREATE DOMAIN testdomain1 AS int; 793REVOKE USAGE on DOMAIN testdomain1 FROM PUBLIC; 794GRANT USAGE ON DOMAIN testdomain1 TO regress_user2; 795GRANT USAGE ON TYPE testdomain1 TO regress_user2; -- ok 796SET SESSION AUTHORIZATION regress_user1; 797-- commands that should fail 798CREATE AGGREGATE testagg1a(testdomain1) (sfunc = int4_sum, stype = bigint); 799ERROR: permission denied for type testdomain1 800CREATE DOMAIN testdomain2a AS testdomain1; 801ERROR: permission denied for type testdomain1 802CREATE DOMAIN testdomain3a AS int; 803CREATE FUNCTION castfunc(int) RETURNS testdomain3a AS $$ SELECT $1::testdomain3a $$ LANGUAGE SQL; 804CREATE CAST (testdomain1 AS testdomain3a) WITH FUNCTION castfunc(int); 805ERROR: permission denied for type testdomain1 806DROP FUNCTION castfunc(int) CASCADE; 807DROP DOMAIN testdomain3a; 808CREATE FUNCTION testfunc5a(a testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 809ERROR: permission denied for type testdomain1 810CREATE FUNCTION testfunc6a(b int) RETURNS testdomain1 LANGUAGE SQL AS $$ SELECT $1::testdomain1 $$; 811ERROR: permission denied for type testdomain1 812CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = testdomain1, RIGHTARG = testdomain1); 813ERROR: permission denied for type testdomain1 814CREATE TABLE test5a (a int, b testdomain1); 815ERROR: permission denied for type testdomain1 816CREATE TABLE test6a OF testtype1; 817ERROR: permission denied for type testtype1 818CREATE TABLE test10a (a int[], b testtype1[]); 819ERROR: permission denied for type testtype1 820CREATE TABLE test9a (a int, b int); 821ALTER TABLE test9a ADD COLUMN c testdomain1; 822ERROR: permission denied for type testdomain1 823ALTER TABLE test9a ALTER COLUMN b TYPE testdomain1; 824ERROR: permission denied for type testdomain1 825CREATE TYPE test7a AS (a int, b testdomain1); 826ERROR: permission denied for type testdomain1 827CREATE TYPE test8a AS (a int, b int); 828ALTER TYPE test8a ADD ATTRIBUTE c testdomain1; 829ERROR: permission denied for type testdomain1 830ALTER TYPE test8a ALTER ATTRIBUTE b TYPE testdomain1; 831ERROR: permission denied for type testdomain1 832CREATE TABLE test11a AS (SELECT 1::testdomain1 AS a); 833ERROR: permission denied for type testdomain1 834REVOKE ALL ON TYPE testtype1 FROM PUBLIC; 835ERROR: permission denied for type testtype1 836SET SESSION AUTHORIZATION regress_user2; 837-- commands that should succeed 838CREATE AGGREGATE testagg1b(testdomain1) (sfunc = int4_sum, stype = bigint); 839CREATE DOMAIN testdomain2b AS testdomain1; 840CREATE DOMAIN testdomain3b AS int; 841CREATE FUNCTION castfunc(int) RETURNS testdomain3b AS $$ SELECT $1::testdomain3b $$ LANGUAGE SQL; 842CREATE CAST (testdomain1 AS testdomain3b) WITH FUNCTION castfunc(int); 843WARNING: cast will be ignored because the source data type is a domain 844CREATE FUNCTION testfunc5b(a testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 845CREATE FUNCTION testfunc6b(b int) RETURNS testdomain1 LANGUAGE SQL AS $$ SELECT $1::testdomain1 $$; 846CREATE OPERATOR !! (PROCEDURE = testfunc5b, RIGHTARG = testdomain1); 847CREATE TABLE test5b (a int, b testdomain1); 848CREATE TABLE test6b OF testtype1; 849CREATE TABLE test10b (a int[], b testtype1[]); 850CREATE TABLE test9b (a int, b int); 851ALTER TABLE test9b ADD COLUMN c testdomain1; 852ALTER TABLE test9b ALTER COLUMN b TYPE testdomain1; 853CREATE TYPE test7b AS (a int, b testdomain1); 854CREATE TYPE test8b AS (a int, b int); 855ALTER TYPE test8b ADD ATTRIBUTE c testdomain1; 856ALTER TYPE test8b ALTER ATTRIBUTE b TYPE testdomain1; 857CREATE TABLE test11b AS (SELECT 1::testdomain1 AS a); 858REVOKE ALL ON TYPE testtype1 FROM PUBLIC; 859WARNING: no privileges could be revoked for "testtype1" 860\c - 861DROP AGGREGATE testagg1b(testdomain1); 862DROP DOMAIN testdomain2b; 863DROP OPERATOR !! (NONE, testdomain1); 864DROP FUNCTION testfunc5b(a testdomain1); 865DROP FUNCTION testfunc6b(b int); 866DROP TABLE test5b; 867DROP TABLE test6b; 868DROP TABLE test9b; 869DROP TABLE test10b; 870DROP TYPE test7b; 871DROP TYPE test8b; 872DROP CAST (testdomain1 AS testdomain3b); 873DROP FUNCTION castfunc(int) CASCADE; 874DROP DOMAIN testdomain3b; 875DROP TABLE test11b; 876DROP TYPE testtype1; -- ok 877DROP DOMAIN testdomain1; -- ok 878-- truncate 879SET SESSION AUTHORIZATION regress_user5; 880TRUNCATE atest2; -- ok 881TRUNCATE atest3; -- fail 882ERROR: permission denied for relation atest3 883-- has_table_privilege function 884-- bad-input checks 885select has_table_privilege(NULL,'pg_authid','select'); 886 has_table_privilege 887--------------------- 888 889(1 row) 890 891select has_table_privilege('pg_shad','select'); 892ERROR: relation "pg_shad" does not exist 893select has_table_privilege('nosuchuser','pg_authid','select'); 894ERROR: role "nosuchuser" does not exist 895select has_table_privilege('pg_authid','sel'); 896ERROR: unrecognized privilege type: "sel" 897select has_table_privilege(-999999,'pg_authid','update'); 898 has_table_privilege 899--------------------- 900 f 901(1 row) 902 903select has_table_privilege(1,'select'); 904 has_table_privilege 905--------------------- 906 907(1 row) 908 909-- superuser 910\c - 911select has_table_privilege(current_user,'pg_authid','select'); 912 has_table_privilege 913--------------------- 914 t 915(1 row) 916 917select has_table_privilege(current_user,'pg_authid','insert'); 918 has_table_privilege 919--------------------- 920 t 921(1 row) 922 923select has_table_privilege(t2.oid,'pg_authid','update') 924from (select oid from pg_roles where rolname = current_user) as t2; 925 has_table_privilege 926--------------------- 927 t 928(1 row) 929 930select has_table_privilege(t2.oid,'pg_authid','delete') 931from (select oid from pg_roles where rolname = current_user) as t2; 932 has_table_privilege 933--------------------- 934 t 935(1 row) 936 937-- 'rule' privilege no longer exists, but for backwards compatibility 938-- has_table_privilege still recognizes the keyword and says FALSE 939select has_table_privilege(current_user,t1.oid,'rule') 940from (select oid from pg_class where relname = 'pg_authid') as t1; 941 has_table_privilege 942--------------------- 943 f 944(1 row) 945 946select has_table_privilege(current_user,t1.oid,'references') 947from (select oid from pg_class where relname = 'pg_authid') as t1; 948 has_table_privilege 949--------------------- 950 t 951(1 row) 952 953select has_table_privilege(t2.oid,t1.oid,'select') 954from (select oid from pg_class where relname = 'pg_authid') as t1, 955 (select oid from pg_roles where rolname = current_user) as t2; 956 has_table_privilege 957--------------------- 958 t 959(1 row) 960 961select has_table_privilege(t2.oid,t1.oid,'insert') 962from (select oid from pg_class where relname = 'pg_authid') as t1, 963 (select oid from pg_roles where rolname = current_user) as t2; 964 has_table_privilege 965--------------------- 966 t 967(1 row) 968 969select has_table_privilege('pg_authid','update'); 970 has_table_privilege 971--------------------- 972 t 973(1 row) 974 975select has_table_privilege('pg_authid','delete'); 976 has_table_privilege 977--------------------- 978 t 979(1 row) 980 981select has_table_privilege('pg_authid','truncate'); 982 has_table_privilege 983--------------------- 984 t 985(1 row) 986 987select has_table_privilege(t1.oid,'select') 988from (select oid from pg_class where relname = 'pg_authid') as t1; 989 has_table_privilege 990--------------------- 991 t 992(1 row) 993 994select has_table_privilege(t1.oid,'trigger') 995from (select oid from pg_class where relname = 'pg_authid') as t1; 996 has_table_privilege 997--------------------- 998 t 999(1 row) 1000 1001-- non-superuser 1002SET SESSION AUTHORIZATION regress_user3; 1003select has_table_privilege(current_user,'pg_class','select'); 1004 has_table_privilege 1005--------------------- 1006 t 1007(1 row) 1008 1009select has_table_privilege(current_user,'pg_class','insert'); 1010 has_table_privilege 1011--------------------- 1012 f 1013(1 row) 1014 1015select has_table_privilege(t2.oid,'pg_class','update') 1016from (select oid from pg_roles where rolname = current_user) as t2; 1017 has_table_privilege 1018--------------------- 1019 f 1020(1 row) 1021 1022select has_table_privilege(t2.oid,'pg_class','delete') 1023from (select oid from pg_roles where rolname = current_user) as t2; 1024 has_table_privilege 1025--------------------- 1026 f 1027(1 row) 1028 1029select has_table_privilege(current_user,t1.oid,'references') 1030from (select oid from pg_class where relname = 'pg_class') as t1; 1031 has_table_privilege 1032--------------------- 1033 f 1034(1 row) 1035 1036select has_table_privilege(t2.oid,t1.oid,'select') 1037from (select oid from pg_class where relname = 'pg_class') as t1, 1038 (select oid from pg_roles where rolname = current_user) as t2; 1039 has_table_privilege 1040--------------------- 1041 t 1042(1 row) 1043 1044select has_table_privilege(t2.oid,t1.oid,'insert') 1045from (select oid from pg_class where relname = 'pg_class') as t1, 1046 (select oid from pg_roles where rolname = current_user) as t2; 1047 has_table_privilege 1048--------------------- 1049 f 1050(1 row) 1051 1052select has_table_privilege('pg_class','update'); 1053 has_table_privilege 1054--------------------- 1055 f 1056(1 row) 1057 1058select has_table_privilege('pg_class','delete'); 1059 has_table_privilege 1060--------------------- 1061 f 1062(1 row) 1063 1064select has_table_privilege('pg_class','truncate'); 1065 has_table_privilege 1066--------------------- 1067 f 1068(1 row) 1069 1070select has_table_privilege(t1.oid,'select') 1071from (select oid from pg_class where relname = 'pg_class') as t1; 1072 has_table_privilege 1073--------------------- 1074 t 1075(1 row) 1076 1077select has_table_privilege(t1.oid,'trigger') 1078from (select oid from pg_class where relname = 'pg_class') as t1; 1079 has_table_privilege 1080--------------------- 1081 f 1082(1 row) 1083 1084select has_table_privilege(current_user,'atest1','select'); 1085 has_table_privilege 1086--------------------- 1087 t 1088(1 row) 1089 1090select has_table_privilege(current_user,'atest1','insert'); 1091 has_table_privilege 1092--------------------- 1093 f 1094(1 row) 1095 1096select has_table_privilege(t2.oid,'atest1','update') 1097from (select oid from pg_roles where rolname = current_user) as t2; 1098 has_table_privilege 1099--------------------- 1100 f 1101(1 row) 1102 1103select has_table_privilege(t2.oid,'atest1','delete') 1104from (select oid from pg_roles where rolname = current_user) as t2; 1105 has_table_privilege 1106--------------------- 1107 f 1108(1 row) 1109 1110select has_table_privilege(current_user,t1.oid,'references') 1111from (select oid from pg_class where relname = 'atest1') as t1; 1112 has_table_privilege 1113--------------------- 1114 f 1115(1 row) 1116 1117select has_table_privilege(t2.oid,t1.oid,'select') 1118from (select oid from pg_class where relname = 'atest1') as t1, 1119 (select oid from pg_roles where rolname = current_user) as t2; 1120 has_table_privilege 1121--------------------- 1122 t 1123(1 row) 1124 1125select has_table_privilege(t2.oid,t1.oid,'insert') 1126from (select oid from pg_class where relname = 'atest1') as t1, 1127 (select oid from pg_roles where rolname = current_user) as t2; 1128 has_table_privilege 1129--------------------- 1130 f 1131(1 row) 1132 1133select has_table_privilege('atest1','update'); 1134 has_table_privilege 1135--------------------- 1136 f 1137(1 row) 1138 1139select has_table_privilege('atest1','delete'); 1140 has_table_privilege 1141--------------------- 1142 f 1143(1 row) 1144 1145select has_table_privilege('atest1','truncate'); 1146 has_table_privilege 1147--------------------- 1148 f 1149(1 row) 1150 1151select has_table_privilege(t1.oid,'select') 1152from (select oid from pg_class where relname = 'atest1') as t1; 1153 has_table_privilege 1154--------------------- 1155 t 1156(1 row) 1157 1158select has_table_privilege(t1.oid,'trigger') 1159from (select oid from pg_class where relname = 'atest1') as t1; 1160 has_table_privilege 1161--------------------- 1162 f 1163(1 row) 1164 1165-- has_column_privilege function 1166-- bad-input checks (as non-super-user) 1167select has_column_privilege('pg_authid',NULL,'select'); 1168 has_column_privilege 1169---------------------- 1170 1171(1 row) 1172 1173select has_column_privilege('pg_authid','nosuchcol','select'); 1174ERROR: column "nosuchcol" of relation "pg_authid" does not exist 1175select has_column_privilege(9999,'nosuchcol','select'); 1176 has_column_privilege 1177---------------------- 1178 1179(1 row) 1180 1181select has_column_privilege(9999,99::int2,'select'); 1182 has_column_privilege 1183---------------------- 1184 1185(1 row) 1186 1187select has_column_privilege('pg_authid',99::int2,'select'); 1188 has_column_privilege 1189---------------------- 1190 1191(1 row) 1192 1193select has_column_privilege(9999,99::int2,'select'); 1194 has_column_privilege 1195---------------------- 1196 1197(1 row) 1198 1199create temp table mytable(f1 int, f2 int, f3 int); 1200alter table mytable drop column f2; 1201select has_column_privilege('mytable','f2','select'); 1202ERROR: column "f2" of relation "mytable" does not exist 1203select has_column_privilege('mytable','........pg.dropped.2........','select'); 1204 has_column_privilege 1205---------------------- 1206 1207(1 row) 1208 1209select has_column_privilege('mytable',2::int2,'select'); 1210 has_column_privilege 1211---------------------- 1212 t 1213(1 row) 1214 1215revoke select on table mytable from regress_user3; 1216select has_column_privilege('mytable',2::int2,'select'); 1217 has_column_privilege 1218---------------------- 1219 1220(1 row) 1221 1222drop table mytable; 1223-- Grant options 1224SET SESSION AUTHORIZATION regress_user1; 1225CREATE TABLE atest4 (a int); 1226GRANT SELECT ON atest4 TO regress_user2 WITH GRANT OPTION; 1227GRANT UPDATE ON atest4 TO regress_user2; 1228GRANT SELECT ON atest4 TO GROUP regress_group1 WITH GRANT OPTION; 1229SET SESSION AUTHORIZATION regress_user2; 1230GRANT SELECT ON atest4 TO regress_user3; 1231GRANT UPDATE ON atest4 TO regress_user3; -- fail 1232WARNING: no privileges were granted for "atest4" 1233SET SESSION AUTHORIZATION regress_user1; 1234REVOKE SELECT ON atest4 FROM regress_user3; -- does nothing 1235SELECT has_table_privilege('regress_user3', 'atest4', 'SELECT'); -- true 1236 has_table_privilege 1237--------------------- 1238 t 1239(1 row) 1240 1241REVOKE SELECT ON atest4 FROM regress_user2; -- fail 1242ERROR: dependent privileges exist 1243HINT: Use CASCADE to revoke them too. 1244REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_user2 CASCADE; -- ok 1245SELECT has_table_privilege('regress_user2', 'atest4', 'SELECT'); -- true 1246 has_table_privilege 1247--------------------- 1248 t 1249(1 row) 1250 1251SELECT has_table_privilege('regress_user3', 'atest4', 'SELECT'); -- false 1252 has_table_privilege 1253--------------------- 1254 f 1255(1 row) 1256 1257SELECT has_table_privilege('regress_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true 1258 has_table_privilege 1259--------------------- 1260 t 1261(1 row) 1262 1263-- security-restricted operations 1264\c - 1265CREATE ROLE regress_sro_user; 1266SET SESSION AUTHORIZATION regress_sro_user; 1267CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS 1268 'GRANT regress_group2 TO regress_sro_user'; 1269CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 1270 'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant(); SELECT true'; 1271-- REFRESH of this MV will queue a GRANT at end of transaction 1272CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA; 1273REFRESH MATERIALIZED VIEW sro_mv; 1274ERROR: cannot create a cursor WITH HOLD within security-restricted operation 1275CONTEXT: SQL function "mv_action" statement 1 1276\c - 1277REFRESH MATERIALIZED VIEW sro_mv; 1278ERROR: cannot create a cursor WITH HOLD within security-restricted operation 1279CONTEXT: SQL function "mv_action" statement 1 1280SET SESSION AUTHORIZATION regress_sro_user; 1281-- INSERT to this table will queue a GRANT at end of transaction 1282CREATE TABLE sro_trojan_table (); 1283CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS 1284 'BEGIN PERFORM unwanted_grant(); RETURN NULL; END'; 1285CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table 1286 INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan(); 1287-- Now, REFRESH will issue such an INSERT, queueing the GRANT 1288CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 1289 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; 1290REFRESH MATERIALIZED VIEW sro_mv; 1291ERROR: cannot fire deferred trigger within security-restricted operation 1292CONTEXT: SQL function "mv_action" statement 1 1293\c - 1294REFRESH MATERIALIZED VIEW sro_mv; 1295ERROR: cannot fire deferred trigger within security-restricted operation 1296CONTEXT: SQL function "mv_action" statement 1 1297BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; 1298ERROR: must have admin option on role "regress_group2" 1299CONTEXT: SQL function "unwanted_grant" statement 1 1300SQL statement "SELECT unwanted_grant()" 1301PL/pgSQL function sro_trojan() line 1 at PERFORM 1302SQL function "mv_action" statement 1 1303DROP OWNED BY regress_sro_user; 1304DROP ROLE regress_sro_user; 1305-- Admin options 1306SET SESSION AUTHORIZATION regress_user4; 1307CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS 1308 'GRANT regress_group2 TO regress_user5'; 1309GRANT regress_group2 TO regress_user5; -- ok: had ADMIN OPTION 1310SET ROLE regress_group2; 1311GRANT regress_group2 TO regress_user5; -- fails: SET ROLE suspended privilege 1312ERROR: must have admin option on role "regress_group2" 1313SET SESSION AUTHORIZATION regress_user1; 1314GRANT regress_group2 TO regress_user5; -- fails: no ADMIN OPTION 1315ERROR: must have admin option on role "regress_group2" 1316SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN 1317NOTICE: role "regress_user5" is already a member of role "regress_group2" 1318 dogrant_ok 1319------------ 1320 1321(1 row) 1322 1323SET ROLE regress_group2; 1324GRANT regress_group2 TO regress_user5; -- fails: SET ROLE did not help 1325ERROR: must have admin option on role "regress_group2" 1326SET SESSION AUTHORIZATION regress_group2; 1327GRANT regress_group2 TO regress_user5; -- ok: a role can self-admin 1328NOTICE: role "regress_user5" is already a member of role "regress_group2" 1329CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS 1330 'GRANT regress_group2 TO regress_user5'; 1331SELECT dogrant_fails(); -- fails: no self-admin in SECURITY DEFINER 1332ERROR: must have admin option on role "regress_group2" 1333CONTEXT: SQL function "dogrant_fails" statement 1 1334DROP FUNCTION dogrant_fails(); 1335SET SESSION AUTHORIZATION regress_user4; 1336DROP FUNCTION dogrant_ok(); 1337REVOKE regress_group2 FROM regress_user5; 1338-- has_sequence_privilege tests 1339\c - 1340CREATE SEQUENCE x_seq; 1341GRANT USAGE on x_seq to regress_user2; 1342SELECT has_sequence_privilege('regress_user1', 'atest1', 'SELECT'); 1343ERROR: "atest1" is not a sequence 1344SELECT has_sequence_privilege('regress_user1', 'x_seq', 'INSERT'); 1345ERROR: unrecognized privilege type: "INSERT" 1346SELECT has_sequence_privilege('regress_user1', 'x_seq', 'SELECT'); 1347 has_sequence_privilege 1348------------------------ 1349 f 1350(1 row) 1351 1352SET SESSION AUTHORIZATION regress_user2; 1353SELECT has_sequence_privilege('x_seq', 'USAGE'); 1354 has_sequence_privilege 1355------------------------ 1356 t 1357(1 row) 1358 1359-- largeobject privilege tests 1360\c - 1361SET SESSION AUTHORIZATION regress_user1; 1362SELECT lo_create(1001); 1363 lo_create 1364----------- 1365 1001 1366(1 row) 1367 1368SELECT lo_create(1002); 1369 lo_create 1370----------- 1371 1002 1372(1 row) 1373 1374SELECT lo_create(1003); 1375 lo_create 1376----------- 1377 1003 1378(1 row) 1379 1380SELECT lo_create(1004); 1381 lo_create 1382----------- 1383 1004 1384(1 row) 1385 1386SELECT lo_create(1005); 1387 lo_create 1388----------- 1389 1005 1390(1 row) 1391 1392GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC; 1393GRANT SELECT ON LARGE OBJECT 1003 TO regress_user2; 1394GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_user2; 1395GRANT ALL ON LARGE OBJECT 1005 TO regress_user2; 1396GRANT SELECT ON LARGE OBJECT 1005 TO regress_user2 WITH GRANT OPTION; 1397GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC; -- to be failed 1398ERROR: invalid privilege type INSERT for large object 1399GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser; -- to be failed 1400ERROR: role "nosuchuser" does not exist 1401GRANT SELECT, UPDATE ON LARGE OBJECT 999 TO PUBLIC; -- to be failed 1402ERROR: large object 999 does not exist 1403\c - 1404SET SESSION AUTHORIZATION regress_user2; 1405SELECT lo_create(2001); 1406 lo_create 1407----------- 1408 2001 1409(1 row) 1410 1411SELECT lo_create(2002); 1412 lo_create 1413----------- 1414 2002 1415(1 row) 1416 1417SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now 1418 loread 1419-------- 1420 \x 1421(1 row) 1422 1423SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode 1424ERROR: large object descriptor 0 was not opened for writing 1425SELECT loread(lo_open(1001, x'40000'::int), 32); 1426 loread 1427-------- 1428 \x 1429(1 row) 1430 1431SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 1432ERROR: permission denied for large object 1002 1433SELECT loread(lo_open(1003, x'40000'::int), 32); 1434 loread 1435-------- 1436 \x 1437(1 row) 1438 1439SELECT loread(lo_open(1004, x'40000'::int), 32); 1440 loread 1441-------- 1442 \x 1443(1 row) 1444 1445SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd'); 1446 lowrite 1447--------- 1448 4 1449(1 row) 1450 1451SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 1452ERROR: permission denied for large object 1002 1453SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied 1454ERROR: permission denied for large object 1003 1455SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd'); 1456 lowrite 1457--------- 1458 4 1459(1 row) 1460 1461GRANT SELECT ON LARGE OBJECT 1005 TO regress_user3; 1462GRANT UPDATE ON LARGE OBJECT 1006 TO regress_user3; -- to be denied 1463ERROR: large object 1006 does not exist 1464REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC; 1465GRANT ALL ON LARGE OBJECT 2001 TO regress_user3; 1466SELECT lo_unlink(1001); -- to be denied 1467ERROR: must be owner of large object 1001 1468SELECT lo_unlink(2002); 1469 lo_unlink 1470----------- 1471 1 1472(1 row) 1473 1474\c - 1475-- confirm ACL setting 1476SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 1477 oid | ownername | lomacl 1478------+---------------+------------------------------------------------------------------------------------------------ 1479 1001 | regress_user1 | {regress_user1=rw/regress_user1,=rw/regress_user1} 1480 1002 | regress_user1 | 1481 1003 | regress_user1 | {regress_user1=rw/regress_user1,regress_user2=r/regress_user1} 1482 1004 | regress_user1 | {regress_user1=rw/regress_user1,regress_user2=rw/regress_user1} 1483 1005 | regress_user1 | {regress_user1=rw/regress_user1,regress_user2=r*w/regress_user1,regress_user3=r/regress_user2} 1484 2001 | regress_user2 | {regress_user2=rw/regress_user2,regress_user3=rw/regress_user2} 1485(6 rows) 1486 1487SET SESSION AUTHORIZATION regress_user3; 1488SELECT loread(lo_open(1001, x'40000'::int), 32); 1489 loread 1490------------ 1491 \x61626364 1492(1 row) 1493 1494SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied 1495ERROR: permission denied for large object 1003 1496SELECT loread(lo_open(1005, x'40000'::int), 32); 1497 loread 1498-------- 1499 \x 1500(1 row) 1501 1502SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied 1503ERROR: permission denied for large object 1005 1504SELECT lo_truncate(lo_open(2001, x'20000'::int), 10); 1505 lo_truncate 1506------------- 1507 0 1508(1 row) 1509 1510-- compatibility mode in largeobject permission 1511\c - 1512SET lo_compat_privileges = false; -- default setting 1513SET SESSION AUTHORIZATION regress_user4; 1514SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 1515ERROR: permission denied for large object 1002 1516SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 1517ERROR: permission denied for large object 1002 1518SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied 1519ERROR: permission denied for large object 1002 1520SELECT lo_put(1002, 1, 'abcd'); -- to be denied 1521ERROR: permission denied for large object 1002 1522SELECT lo_unlink(1002); -- to be denied 1523ERROR: must be owner of large object 1002 1524SELECT lo_export(1001, '/dev/null'); -- to be denied 1525ERROR: must be superuser to use server-side lo_export() 1526HINT: Anyone can use the client-side lo_export() provided by libpq. 1527\c - 1528SET lo_compat_privileges = true; -- compatibility mode 1529SET SESSION AUTHORIZATION regress_user4; 1530SELECT loread(lo_open(1002, x'40000'::int), 32); 1531 loread 1532-------- 1533 \x 1534(1 row) 1535 1536SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); 1537 lowrite 1538--------- 1539 4 1540(1 row) 1541 1542SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); 1543 lo_truncate 1544------------- 1545 0 1546(1 row) 1547 1548SELECT lo_unlink(1002); 1549 lo_unlink 1550----------- 1551 1 1552(1 row) 1553 1554SELECT lo_export(1001, '/dev/null'); -- to be denied 1555ERROR: must be superuser to use server-side lo_export() 1556HINT: Anyone can use the client-side lo_export() provided by libpq. 1557-- don't allow unpriv users to access pg_largeobject contents 1558\c - 1559SELECT * FROM pg_largeobject LIMIT 0; 1560 loid | pageno | data 1561------+--------+------ 1562(0 rows) 1563 1564SET SESSION AUTHORIZATION regress_user1; 1565SELECT * FROM pg_largeobject LIMIT 0; -- to be denied 1566ERROR: permission denied for relation pg_largeobject 1567-- test default ACLs 1568\c - 1569CREATE SCHEMA testns; 1570GRANT ALL ON SCHEMA testns TO regress_user1; 1571CREATE TABLE testns.acltest1 (x int); 1572SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- no 1573 has_table_privilege 1574--------------------- 1575 f 1576(1 row) 1577 1578SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 1579 has_table_privilege 1580--------------------- 1581 f 1582(1 row) 1583 1584-- placeholder for test with duplicated schema and role names 1585ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public; 1586SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- no 1587 has_table_privilege 1588--------------------- 1589 f 1590(1 row) 1591 1592SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 1593 has_table_privilege 1594--------------------- 1595 f 1596(1 row) 1597 1598DROP TABLE testns.acltest1; 1599CREATE TABLE testns.acltest1 (x int); 1600SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- yes 1601 has_table_privilege 1602--------------------- 1603 t 1604(1 row) 1605 1606SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 1607 has_table_privilege 1608--------------------- 1609 f 1610(1 row) 1611 1612ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_user1; 1613DROP TABLE testns.acltest1; 1614CREATE TABLE testns.acltest1 (x int); 1615SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- yes 1616 has_table_privilege 1617--------------------- 1618 t 1619(1 row) 1620 1621SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- yes 1622 has_table_privilege 1623--------------------- 1624 t 1625(1 row) 1626 1627ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_user1; 1628DROP TABLE testns.acltest1; 1629CREATE TABLE testns.acltest1 (x int); 1630SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- yes 1631 has_table_privilege 1632--------------------- 1633 t 1634(1 row) 1635 1636SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 1637 has_table_privilege 1638--------------------- 1639 f 1640(1 row) 1641 1642ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE EXECUTE ON FUNCTIONS FROM public; 1643ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_user2; -- error 1644ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS 1645-- 1646-- Testing blanket default grants is very hazardous since it might change 1647-- the privileges attached to objects created by concurrent regression tests. 1648-- To avoid that, be sure to revoke the privileges again before committing. 1649-- 1650BEGIN; 1651ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_user2; 1652CREATE SCHEMA testns2; 1653SELECT has_schema_privilege('regress_user2', 'testns2', 'USAGE'); -- yes 1654 has_schema_privilege 1655---------------------- 1656 t 1657(1 row) 1658 1659SELECT has_schema_privilege('regress_user2', 'testns2', 'CREATE'); -- no 1660 has_schema_privilege 1661---------------------- 1662 f 1663(1 row) 1664 1665ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_user2; 1666CREATE SCHEMA testns3; 1667SELECT has_schema_privilege('regress_user2', 'testns3', 'USAGE'); -- no 1668 has_schema_privilege 1669---------------------- 1670 f 1671(1 row) 1672 1673SELECT has_schema_privilege('regress_user2', 'testns3', 'CREATE'); -- no 1674 has_schema_privilege 1675---------------------- 1676 f 1677(1 row) 1678 1679ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_user2; 1680CREATE SCHEMA testns4; 1681SELECT has_schema_privilege('regress_user2', 'testns4', 'USAGE'); -- yes 1682 has_schema_privilege 1683---------------------- 1684 t 1685(1 row) 1686 1687SELECT has_schema_privilege('regress_user2', 'testns4', 'CREATE'); -- yes 1688 has_schema_privilege 1689---------------------- 1690 t 1691(1 row) 1692 1693ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_user2; 1694COMMIT; 1695CREATE SCHEMA testns5; 1696SELECT has_schema_privilege('regress_user2', 'testns5', 'USAGE'); -- no 1697 has_schema_privilege 1698---------------------- 1699 f 1700(1 row) 1701 1702SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no 1703 has_schema_privilege 1704---------------------- 1705 f 1706(1 row) 1707 1708SET ROLE regress_user1; 1709CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1710SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no 1711 has_function_privilege 1712------------------------ 1713 f 1714(1 row) 1715 1716ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON FUNCTIONS to public; 1717DROP FUNCTION testns.foo(); 1718CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1719SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- yes 1720 has_function_privilege 1721------------------------ 1722 t 1723(1 row) 1724 1725DROP FUNCTION testns.foo(); 1726ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE USAGE ON TYPES FROM public; 1727CREATE DOMAIN testns.testdomain1 AS int; 1728SELECT has_type_privilege('regress_user2', 'testns.testdomain1', 'USAGE'); -- no 1729 has_type_privilege 1730-------------------- 1731 f 1732(1 row) 1733 1734ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public; 1735DROP DOMAIN testns.testdomain1; 1736CREATE DOMAIN testns.testdomain1 AS int; 1737SELECT has_type_privilege('regress_user2', 'testns.testdomain1', 'USAGE'); -- yes 1738 has_type_privilege 1739-------------------- 1740 t 1741(1 row) 1742 1743DROP DOMAIN testns.testdomain1; 1744RESET ROLE; 1745SELECT count(*) 1746 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1747 WHERE nspname = 'testns'; 1748 count 1749------- 1750 3 1751(1 row) 1752 1753DROP SCHEMA testns CASCADE; 1754NOTICE: drop cascades to table testns.acltest1 1755DROP SCHEMA testns2 CASCADE; 1756DROP SCHEMA testns3 CASCADE; 1757DROP SCHEMA testns4 CASCADE; 1758DROP SCHEMA testns5 CASCADE; 1759SELECT d.* -- check that entries went away 1760 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1761 WHERE nspname IS NULL AND defaclnamespace != 0; 1762 defaclrole | defaclnamespace | defaclobjtype | defaclacl 1763------------+-----------------+---------------+----------- 1764(0 rows) 1765 1766-- Grant on all objects of given type in a schema 1767\c - 1768CREATE SCHEMA testns; 1769CREATE TABLE testns.t1 (f1 int); 1770CREATE TABLE testns.t2 (f1 int); 1771SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- false 1772 has_table_privilege 1773--------------------- 1774 f 1775(1 row) 1776 1777GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_user1; 1778SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- true 1779 has_table_privilege 1780--------------------- 1781 t 1782(1 row) 1783 1784SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- true 1785 has_table_privilege 1786--------------------- 1787 t 1788(1 row) 1789 1790REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_user1; 1791SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- false 1792 has_table_privilege 1793--------------------- 1794 f 1795(1 row) 1796 1797SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- false 1798 has_table_privilege 1799--------------------- 1800 f 1801(1 row) 1802 1803CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 1804SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default 1805 has_function_privilege 1806------------------------ 1807 t 1808(1 row) 1809 1810REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; 1811SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- false 1812 has_function_privilege 1813------------------------ 1814 f 1815(1 row) 1816 1817\set VERBOSITY terse \\ -- suppress cascade details 1818DROP SCHEMA testns CASCADE; 1819NOTICE: drop cascades to 3 other objects 1820\set VERBOSITY default 1821-- Change owner of the schema & and rename of new schema owner 1822\c - 1823CREATE ROLE regress_schemauser1 superuser login; 1824CREATE ROLE regress_schemauser2 superuser login; 1825SET SESSION ROLE regress_schemauser1; 1826CREATE SCHEMA testns; 1827SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1828 nspname | rolname 1829---------+--------------------- 1830 testns | regress_schemauser1 1831(1 row) 1832 1833ALTER SCHEMA testns OWNER TO regress_schemauser2; 1834ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed; 1835SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1836 nspname | rolname 1837---------+---------------------------- 1838 testns | regress_schemauser_renamed 1839(1 row) 1840 1841set session role regress_schemauser_renamed; 1842\set VERBOSITY terse \\ -- suppress cascade details 1843DROP SCHEMA testns CASCADE; 1844\set VERBOSITY default 1845-- clean up 1846\c - 1847DROP ROLE regress_schemauser1; 1848DROP ROLE regress_schemauser_renamed; 1849-- test that dependent privileges are revoked (or not) properly 1850\c - 1851set session role regress_user1; 1852create table dep_priv_test (a int); 1853grant select on dep_priv_test to regress_user2 with grant option; 1854grant select on dep_priv_test to regress_user3 with grant option; 1855set session role regress_user2; 1856grant select on dep_priv_test to regress_user4 with grant option; 1857set session role regress_user3; 1858grant select on dep_priv_test to regress_user4 with grant option; 1859set session role regress_user4; 1860grant select on dep_priv_test to regress_user5; 1861\dp dep_priv_test 1862 Access privileges 1863 Schema | Name | Type | Access privileges | Column privileges | Policies 1864--------+---------------+-------+-------------------------------------+-------------------+---------- 1865 public | dep_priv_test | table | regress_user1=arwdDxt/regress_user1+| | 1866 | | | regress_user2=r*/regress_user1 +| | 1867 | | | regress_user3=r*/regress_user1 +| | 1868 | | | regress_user4=r*/regress_user2 +| | 1869 | | | regress_user4=r*/regress_user3 +| | 1870 | | | regress_user5=r/regress_user4 | | 1871(1 row) 1872 1873set session role regress_user2; 1874revoke select on dep_priv_test from regress_user4 cascade; 1875\dp dep_priv_test 1876 Access privileges 1877 Schema | Name | Type | Access privileges | Column privileges | Policies 1878--------+---------------+-------+-------------------------------------+-------------------+---------- 1879 public | dep_priv_test | table | regress_user1=arwdDxt/regress_user1+| | 1880 | | | regress_user2=r*/regress_user1 +| | 1881 | | | regress_user3=r*/regress_user1 +| | 1882 | | | regress_user4=r*/regress_user3 +| | 1883 | | | regress_user5=r/regress_user4 | | 1884(1 row) 1885 1886set session role regress_user3; 1887revoke select on dep_priv_test from regress_user4 cascade; 1888\dp dep_priv_test 1889 Access privileges 1890 Schema | Name | Type | Access privileges | Column privileges | Policies 1891--------+---------------+-------+-------------------------------------+-------------------+---------- 1892 public | dep_priv_test | table | regress_user1=arwdDxt/regress_user1+| | 1893 | | | regress_user2=r*/regress_user1 +| | 1894 | | | regress_user3=r*/regress_user1 | | 1895(1 row) 1896 1897set session role regress_user1; 1898drop table dep_priv_test; 1899-- clean up 1900\c 1901drop sequence x_seq; 1902DROP FUNCTION testfunc2(int); 1903DROP FUNCTION testfunc4(boolean); 1904DROP VIEW atestv0; 1905DROP VIEW atestv1; 1906DROP VIEW atestv2; 1907-- this should cascade to drop atestv4 1908DROP VIEW atestv3 CASCADE; 1909NOTICE: drop cascades to view atestv4 1910-- this should complain "does not exist" 1911DROP VIEW atestv4; 1912ERROR: view "atestv4" does not exist 1913DROP TABLE atest1; 1914DROP TABLE atest2; 1915DROP TABLE atest3; 1916DROP TABLE atest4; 1917DROP TABLE atest5; 1918DROP TABLE atest6; 1919DROP TABLE atestc; 1920DROP TABLE atestp1; 1921DROP TABLE atestp2; 1922SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 1923 lo_unlink 1924----------- 1925 1 1926 1 1927 1 1928 1 1929 1 1930(5 rows) 1931 1932DROP GROUP regress_group1; 1933DROP GROUP regress_group2; 1934-- these are needed to clean up permissions 1935REVOKE USAGE ON LANGUAGE sql FROM regress_user1; 1936DROP OWNED BY regress_user1; 1937DROP USER regress_user1; 1938DROP USER regress_user2; 1939DROP USER regress_user3; 1940DROP USER regress_user4; 1941DROP USER regress_user5; 1942DROP USER regress_user6; 1943ERROR: role "regress_user6" does not exist 1944-- permissions with LOCK TABLE 1945CREATE USER regress_locktable_user; 1946CREATE TABLE lock_table (a int); 1947-- LOCK TABLE and SELECT permission 1948GRANT SELECT ON lock_table TO regress_locktable_user; 1949SET SESSION AUTHORIZATION regress_locktable_user; 1950BEGIN; 1951LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail 1952ERROR: permission denied for relation lock_table 1953ROLLBACK; 1954BEGIN; 1955LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass 1956COMMIT; 1957BEGIN; 1958LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 1959ERROR: permission denied for relation lock_table 1960ROLLBACK; 1961\c 1962REVOKE SELECT ON lock_table FROM regress_locktable_user; 1963-- LOCK TABLE and INSERT permission 1964GRANT INSERT ON lock_table TO regress_locktable_user; 1965SET SESSION AUTHORIZATION regress_locktable_user; 1966BEGIN; 1967LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1968COMMIT; 1969BEGIN; 1970LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1971ERROR: permission denied for relation lock_table 1972ROLLBACK; 1973BEGIN; 1974LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 1975ERROR: permission denied for relation lock_table 1976ROLLBACK; 1977\c 1978REVOKE INSERT ON lock_table FROM regress_locktable_user; 1979-- LOCK TABLE and UPDATE permission 1980GRANT UPDATE ON lock_table TO regress_locktable_user; 1981SET SESSION AUTHORIZATION regress_locktable_user; 1982BEGIN; 1983LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1984COMMIT; 1985BEGIN; 1986LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1987ERROR: permission denied for relation lock_table 1988ROLLBACK; 1989BEGIN; 1990LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1991COMMIT; 1992\c 1993REVOKE UPDATE ON lock_table FROM regress_locktable_user; 1994-- LOCK TABLE and DELETE permission 1995GRANT DELETE ON lock_table TO regress_locktable_user; 1996SET SESSION AUTHORIZATION regress_locktable_user; 1997BEGIN; 1998LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1999COMMIT; 2000BEGIN; 2001LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2002ERROR: permission denied for relation lock_table 2003ROLLBACK; 2004BEGIN; 2005LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 2006COMMIT; 2007\c 2008REVOKE DELETE ON lock_table FROM regress_locktable_user; 2009-- LOCK TABLE and TRUNCATE permission 2010GRANT TRUNCATE ON lock_table TO regress_locktable_user; 2011SET SESSION AUTHORIZATION regress_locktable_user; 2012BEGIN; 2013LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 2014COMMIT; 2015BEGIN; 2016LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2017ERROR: permission denied for relation lock_table 2018ROLLBACK; 2019BEGIN; 2020LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 2021COMMIT; 2022\c 2023REVOKE TRUNCATE ON lock_table FROM regress_locktable_user; 2024-- clean up 2025DROP TABLE lock_table; 2026DROP USER regress_locktable_user; 2027