1-- 2-- Test access privileges 3-- 4-- Clean up in case a prior regression run failed 5-- Suppress NOTICE messages when users/groups don't exist 6SET client_min_messages TO 'warning'; 7DROP ROLE IF EXISTS regress_priv_group1; 8DROP ROLE IF EXISTS regress_priv_group2; 9DROP ROLE IF EXISTS regress_priv_user1; 10DROP ROLE IF EXISTS regress_priv_user2; 11DROP ROLE IF EXISTS regress_priv_user3; 12DROP ROLE IF EXISTS regress_priv_user4; 13DROP ROLE IF EXISTS regress_priv_user5; 14DROP ROLE IF EXISTS regress_priv_user6; 15SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 16 lo_unlink 17----------- 18(0 rows) 19 20RESET client_min_messages; 21-- test proper begins here 22CREATE USER regress_priv_user1; 23CREATE USER regress_priv_user2; 24CREATE USER regress_priv_user3; 25CREATE USER regress_priv_user4; 26CREATE USER regress_priv_user5; 27CREATE USER regress_priv_user5; -- duplicate 28ERROR: role "regress_priv_user5" already exists 29CREATE GROUP regress_priv_group1; 30CREATE GROUP regress_priv_group2 WITH USER regress_priv_user1, regress_priv_user2; 31ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4; 32ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate 33NOTICE: role "regress_priv_user2" is already a member of role "regress_priv_group2" 34ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; 35GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION; 36-- test owner privileges 37SET SESSION AUTHORIZATION regress_priv_user1; 38SELECT session_user, current_user; 39 session_user | current_user 40--------------------+-------------------- 41 regress_priv_user1 | regress_priv_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_priv_user2; 64GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_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_priv_user2; 72GRANT UPDATE ON atest2 TO regress_priv_user3; 73GRANT INSERT ON atest2 TO regress_priv_user4; 74GRANT TRUNCATE ON atest2 TO regress_priv_user5; 75SET SESSION AUTHORIZATION regress_priv_user2; 76SELECT session_user, current_user; 77 session_user | current_user 78--------------------+-------------------- 79 regress_priv_user2 | regress_priv_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 table 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 table 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 table atest2 109DELETE FROM atest2; -- fail 110ERROR: permission denied for table atest2 111TRUNCATE atest2; -- fail 112ERROR: permission denied for table atest2 113BEGIN; 114LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail 115ERROR: permission denied for table atest2 116COMMIT; 117COPY atest2 FROM stdin; -- fail 118ERROR: permission denied for table 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_priv_user3; 133SELECT session_user, current_user; 134 session_user | current_user 135--------------------+-------------------- 136 regress_priv_user3 | regress_priv_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 table atest2 148INSERT INTO atest1 VALUES (2, 'two'); -- fail 149ERROR: permission denied for table atest1 150INSERT INTO atest2 VALUES ('foo', true); -- fail 151ERROR: permission denied for table atest2 152INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail 153ERROR: permission denied for table atest1 154UPDATE atest1 SET a = 1 WHERE a = 2; -- fail 155ERROR: permission denied for table atest1 156UPDATE atest2 SET col2 = NULL; -- ok 157UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2 158ERROR: permission denied for table atest2 159UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok 160SELECT * FROM atest1 FOR UPDATE; -- fail 161ERROR: permission denied for table atest1 162SELECT * FROM atest2 FOR UPDATE; -- fail 163ERROR: permission denied for table atest2 164DELETE FROM atest2; -- fail 165ERROR: permission denied for table atest2 166TRUNCATE atest2; -- fail 167ERROR: permission denied for table atest2 168BEGIN; 169LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok 170COMMIT; 171COPY atest2 FROM stdin; -- fail 172ERROR: permission denied for table atest2 173-- checks in subquery, both fail 174SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) ); 175ERROR: permission denied for table atest2 176SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) ); 177ERROR: permission denied for table atest2 178SET SESSION AUTHORIZATION regress_priv_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_priv_user1 will own a table and provide views for it. 189SET SESSION AUTHORIZATION regress_priv_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_priv_user2 can break security. 246SET SESSION AUTHORIZATION regress_priv_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 table 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_priv_user1 grants sufficient access to regress_priv_user2. 309SET SESSION AUTHORIZATION regress_priv_user1; 310GRANT SELECT (a, b) ON atest12 TO PUBLIC; 311SET SESSION AUTHORIZATION regress_priv_user2; 312-- regress_priv_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_priv_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_priv_user3; 343CREATE TABLE atest3 (one int, two int, three int); 344GRANT DELETE ON atest3 TO GROUP regress_priv_group2; 345SET SESSION AUTHORIZATION regress_priv_user1; 346SELECT * FROM atest3; -- fail 347ERROR: permission denied for table atest3 348DELETE FROM atest3; -- ok 349BEGIN; 350RESET SESSION AUTHORIZATION; 351ALTER ROLE regress_priv_user1 NOINHERIT; 352SET SESSION AUTHORIZATION regress_priv_user1; 353DELETE FROM atest3; 354ERROR: permission denied for table atest3 355ROLLBACK; 356-- views 357SET SESSION AUTHORIZATION regress_priv_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 table atest2 373GRANT SELECT ON atestv1, atestv3 TO regress_priv_user4; 374GRANT SELECT ON atestv2 TO regress_priv_user2; 375SET SESSION AUTHORIZATION regress_priv_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 view atestv2 385SELECT * FROM atestv3; -- ok 386 one | two | three 387-----+-----+------- 388(0 rows) 389 390SELECT * FROM atestv0; -- fail 391ERROR: permission denied for view 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 table 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 table 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_priv_user2; 414SET SESSION AUTHORIZATION regress_priv_user2; 415-- Two complex cases: 416SELECT * FROM atestv3; -- fail 417ERROR: permission denied for view atestv3 418SELECT * FROM atestv4; -- ok (even though regress_priv_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_priv_user2 can access underlying atest2) 430ERROR: permission denied for table atest2 431-- Test column level permissions 432SET SESSION AUTHORIZATION regress_priv_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_priv_user4; 436GRANT ALL (one) ON atest5 TO regress_priv_user3; 437INSERT INTO atest5 VALUES (1,2,3); 438SET SESSION AUTHORIZATION regress_priv_user4; 439SELECT * FROM atest5; -- fail 440ERROR: permission denied for table 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 table atest5 451COPY atest5 (two) TO stdout; -- fail 452ERROR: permission denied for table atest5 453SELECT atest5 FROM atest5; -- fail 454ERROR: permission denied for table atest5 455COPY atest5 (one,two) TO stdout; -- fail 456ERROR: permission denied for table 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 table atest5 471SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail 472ERROR: permission denied for table atest5 473SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail 474ERROR: permission denied for table atest5 475SELECT 1 FROM atest5 WHERE two = 2; -- fail 476ERROR: permission denied for table atest5 477SELECT * FROM atest1, atest5; -- fail 478ERROR: permission denied for table 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 table 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 table atest5 504SET SESSION AUTHORIZATION regress_priv_user1; 505GRANT SELECT (one,two) ON atest6 TO regress_priv_user4; 506SET SESSION AUTHORIZATION regress_priv_user4; 507SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still 508ERROR: permission denied for table atest5 509SET SESSION AUTHORIZATION regress_priv_user1; 510GRANT SELECT (two) ON atest5 TO regress_priv_user4; 511SET SESSION AUTHORIZATION regress_priv_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 table atest5 521COPY atest5 (two) FROM stdin; -- ok 522INSERT INTO atest5 (three) VALUES (4); -- fail 523ERROR: permission denied for table atest5 524INSERT INTO atest5 VALUES (5,5,5); -- fail 525ERROR: permission denied for table atest5 526UPDATE atest5 SET three = 10; -- ok 527UPDATE atest5 SET one = 8; -- fail 528ERROR: permission denied for table atest5 529UPDATE atest5 SET three = 5, one = 2; -- fail 530ERROR: permission denied for table 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 table 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 table atest5 550INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE) 551ERROR: permission denied for table atest5 552INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT) 553ERROR: permission denied for table atest5 554-- Check that the columns in the inference require select privileges 555INSERT INTO atest5(four) VALUES (4); -- fail 556ERROR: permission denied for table atest5 557SET SESSION AUTHORIZATION regress_priv_user1; 558GRANT INSERT (four) ON atest5 TO regress_priv_user4; 559SET SESSION AUTHORIZATION regress_priv_user4; 560INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- fails (due to SELECT) 561ERROR: permission denied for table 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 table atest5 564INSERT INTO atest5(four) VALUES (4); -- ok 565SET SESSION AUTHORIZATION regress_priv_user1; 566GRANT SELECT (four) ON atest5 TO regress_priv_user4; 567SET SESSION AUTHORIZATION regress_priv_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_priv_user1; 571REVOKE ALL (one) ON atest5 FROM regress_priv_user4; 572GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4; 573SET SESSION AUTHORIZATION regress_priv_user4; 574SELECT one FROM atest5; -- fail 575ERROR: permission denied for table atest5 576UPDATE atest5 SET one = 1; -- fail 577ERROR: permission denied for table 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_priv_user1; 586CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2)); 587GRANT SELECT (c1) ON t1 TO regress_priv_user2; 588GRANT INSERT (c1, c2, c3) ON t1 TO regress_priv_user2; 589GRANT UPDATE (c1, c2, c3) ON t1 TO regress_priv_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_priv_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_priv_user1; 614DROP TABLE t1; 615-- check error reporting with column privs on a partitioned table 616CREATE TABLE errtst(a text, b text NOT NULL, c text, secret1 text, secret2 text) PARTITION BY LIST (a); 617CREATE TABLE errtst_part_1(secret2 text, c text, a text, b text NOT NULL, secret1 text); 618CREATE TABLE errtst_part_2(secret1 text, secret2 text, a text, c text, b text NOT NULL); 619ALTER TABLE errtst ATTACH PARTITION errtst_part_1 FOR VALUES IN ('aaa'); 620ALTER TABLE errtst ATTACH PARTITION errtst_part_2 FOR VALUES IN ('aaaa'); 621GRANT SELECT (a, b, c) ON TABLE errtst TO regress_priv_user2; 622GRANT UPDATE (a, b, c) ON TABLE errtst TO regress_priv_user2; 623GRANT INSERT (a, b, c) ON TABLE errtst TO regress_priv_user2; 624INSERT INTO errtst_part_1 (a, b, c, secret1, secret2) 625VALUES ('aaa', 'bbb', 'ccc', 'the body', 'is in the attic'); 626SET SESSION AUTHORIZATION regress_priv_user2; 627-- Perform a few updates that violate the NOT NULL constraint. Make sure 628-- the error messages don't leak the secret fields. 629-- simple insert. 630INSERT INTO errtst (a, b) VALUES ('aaa', NULL); 631ERROR: null value in column "b" violates not-null constraint 632DETAIL: Failing row contains (a, b, c) = (aaa, null, null). 633-- simple update. 634UPDATE errtst SET b = NULL; 635ERROR: null value in column "b" violates not-null constraint 636DETAIL: Failing row contains (b) = (null). 637-- partitioning key is updated, doesn't move the row. 638UPDATE errtst SET a = 'aaa', b = NULL; 639ERROR: null value in column "b" violates not-null constraint 640DETAIL: Failing row contains (a, b, c) = (aaa, null, ccc). 641-- row is moved to another partition. 642UPDATE errtst SET a = 'aaaa', b = NULL; 643ERROR: null value in column "b" violates not-null constraint 644DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). 645-- row is moved to another partition. This differs from the previous case in 646-- that the new partition is excluded by constraint exclusion, so its 647-- ResultRelInfo is not created at ExecInitModifyTable, but needs to be 648-- constructed on the fly when the updated tuple is routed to it. 649UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa'; 650ERROR: null value in column "b" violates not-null constraint 651DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). 652SET SESSION AUTHORIZATION regress_priv_user1; 653DROP TABLE errtst; 654-- test column-level privileges when involved with DELETE 655SET SESSION AUTHORIZATION regress_priv_user1; 656ALTER TABLE atest6 ADD COLUMN three integer; 657GRANT DELETE ON atest5 TO regress_priv_user3; 658GRANT SELECT (two) ON atest5 TO regress_priv_user3; 659REVOKE ALL (one) ON atest5 FROM regress_priv_user3; 660GRANT SELECT (one) ON atest5 TO regress_priv_user4; 661SET SESSION AUTHORIZATION regress_priv_user4; 662SELECT atest6 FROM atest6; -- fail 663ERROR: permission denied for table atest6 664SELECT one FROM atest5 NATURAL JOIN atest6; -- fail 665ERROR: permission denied for table atest5 666SET SESSION AUTHORIZATION regress_priv_user1; 667ALTER TABLE atest6 DROP COLUMN three; 668SET SESSION AUTHORIZATION regress_priv_user4; 669SELECT atest6 FROM atest6; -- ok 670 atest6 671-------- 672(0 rows) 673 674SELECT one FROM atest5 NATURAL JOIN atest6; -- ok 675 one 676----- 677(0 rows) 678 679SET SESSION AUTHORIZATION regress_priv_user1; 680ALTER TABLE atest6 DROP COLUMN two; 681REVOKE SELECT (one,blue) ON atest6 FROM regress_priv_user4; 682SET SESSION AUTHORIZATION regress_priv_user4; 683SELECT * FROM atest6; -- fail 684ERROR: permission denied for table atest6 685SELECT 1 FROM atest6; -- fail 686ERROR: permission denied for table atest6 687SET SESSION AUTHORIZATION regress_priv_user3; 688DELETE FROM atest5 WHERE one = 1; -- fail 689ERROR: permission denied for table atest5 690DELETE FROM atest5 WHERE two = 2; -- ok 691-- check inheritance cases 692SET SESSION AUTHORIZATION regress_priv_user1; 693CREATE TABLE atestp1 (f1 int, f2 int) WITH OIDS; 694CREATE TABLE atestp2 (fx int, fy int) WITH OIDS; 695CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2); 696GRANT SELECT(fx,fy,oid) ON atestp2 TO regress_priv_user2; 697GRANT SELECT(fx) ON atestc TO regress_priv_user2; 698SET SESSION AUTHORIZATION regress_priv_user2; 699SELECT fx FROM atestp2; -- ok 700 fx 701---- 702(0 rows) 703 704SELECT fy FROM atestp2; -- ok 705 fy 706---- 707(0 rows) 708 709SELECT atestp2 FROM atestp2; -- ok 710 atestp2 711--------- 712(0 rows) 713 714SELECT oid FROM atestp2; -- ok 715 oid 716----- 717(0 rows) 718 719SELECT fy FROM atestc; -- fail 720ERROR: permission denied for table atestc 721SET SESSION AUTHORIZATION regress_priv_user1; 722GRANT SELECT(fy,oid) ON atestc TO regress_priv_user2; 723SET SESSION AUTHORIZATION regress_priv_user2; 724SELECT fx FROM atestp2; -- still ok 725 fx 726---- 727(0 rows) 728 729SELECT fy FROM atestp2; -- ok 730 fy 731---- 732(0 rows) 733 734SELECT atestp2 FROM atestp2; -- ok 735 atestp2 736--------- 737(0 rows) 738 739SELECT oid FROM atestp2; -- ok 740 oid 741----- 742(0 rows) 743 744-- privileges on functions, languages 745-- switch to superuser 746\c - 747REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC; 748GRANT USAGE ON LANGUAGE sql TO regress_priv_user1; -- ok 749GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail 750ERROR: language "c" is not trusted 751DETAIL: GRANT and REVOKE are not allowed on untrusted languages, because only superusers can use untrusted languages. 752SET SESSION AUTHORIZATION regress_priv_user1; 753GRANT USAGE ON LANGUAGE sql TO regress_priv_user2; -- fail 754WARNING: no privileges were granted for "sql" 755CREATE FUNCTION priv_testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; 756CREATE FUNCTION priv_testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 757CREATE AGGREGATE priv_testagg1(int) (sfunc = int4pl, stype = int4); 758CREATE PROCEDURE priv_testproc1(int) AS 'select $1;' LANGUAGE sql; 759REVOKE ALL ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) FROM PUBLIC; 760GRANT EXECUTE ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) TO regress_priv_user2; 761REVOKE ALL ON FUNCTION priv_testproc1(int) FROM PUBLIC; -- fail, not a function 762ERROR: priv_testproc1(integer) is not a function 763REVOKE ALL ON PROCEDURE priv_testproc1(int) FROM PUBLIC; 764GRANT EXECUTE ON PROCEDURE priv_testproc1(int) TO regress_priv_user2; 765GRANT USAGE ON FUNCTION priv_testfunc1(int) TO regress_priv_user3; -- semantic error 766ERROR: invalid privilege type USAGE for function 767GRANT USAGE ON FUNCTION priv_testagg1(int) TO regress_priv_user3; -- semantic error 768ERROR: invalid privilege type USAGE for function 769GRANT USAGE ON PROCEDURE priv_testproc1(int) TO regress_priv_user3; -- semantic error 770ERROR: invalid privilege type USAGE for procedure 771GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc1(int) TO regress_priv_user4; 772GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc_nosuch(int) TO regress_priv_user4; 773ERROR: function priv_testfunc_nosuch(integer) does not exist 774GRANT ALL PRIVILEGES ON FUNCTION priv_testagg1(int) TO regress_priv_user4; 775GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4; 776CREATE FUNCTION priv_testfunc4(boolean) RETURNS text 777 AS 'select col1 from atest2 where col2 = $1;' 778 LANGUAGE sql SECURITY DEFINER; 779GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3; 780SET SESSION AUTHORIZATION regress_priv_user2; 781SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok 782 priv_testfunc1 | priv_testfunc2 783----------------+---------------- 784 10 | 15 785(1 row) 786 787CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail 788ERROR: permission denied for language sql 789SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok 790 priv_testagg1 791--------------- 792 6 793(1 row) 794 795CALL priv_testproc1(6); -- ok 796SET SESSION AUTHORIZATION regress_priv_user3; 797SELECT priv_testfunc1(5); -- fail 798ERROR: permission denied for function priv_testfunc1 799SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail 800ERROR: permission denied for aggregate priv_testagg1 801CALL priv_testproc1(6); -- fail 802ERROR: permission denied for procedure priv_testproc1 803SELECT col1 FROM atest2 WHERE col2 = true; -- fail 804ERROR: permission denied for table atest2 805SELECT priv_testfunc4(true); -- ok 806 priv_testfunc4 807---------------- 808 bar 809(1 row) 810 811SET SESSION AUTHORIZATION regress_priv_user4; 812SELECT priv_testfunc1(5); -- ok 813 priv_testfunc1 814---------------- 815 10 816(1 row) 817 818SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok 819 priv_testagg1 820--------------- 821 6 822(1 row) 823 824CALL priv_testproc1(6); -- ok 825DROP FUNCTION priv_testfunc1(int); -- fail 826ERROR: must be owner of function priv_testfunc1 827DROP AGGREGATE priv_testagg1(int); -- fail 828ERROR: must be owner of aggregate priv_testagg1 829DROP PROCEDURE priv_testproc1(int); -- fail 830ERROR: must be owner of procedure priv_testproc1 831\c - 832DROP FUNCTION priv_testfunc1(int); -- ok 833-- restore to sanity 834GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC; 835-- verify privilege checks on array-element coercions 836BEGIN; 837SELECT '{1}'::int4[]::int8[]; 838 int8 839------ 840 {1} 841(1 row) 842 843REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC; 844SELECT '{1}'::int4[]::int8[]; --superuser, suceed 845 int8 846------ 847 {1} 848(1 row) 849 850SET SESSION AUTHORIZATION regress_priv_user4; 851SELECT '{1}'::int4[]::int8[]; --other user, fail 852ERROR: permission denied for function int8 853ROLLBACK; 854-- privileges on types 855-- switch to superuser 856\c - 857CREATE TYPE priv_testtype1 AS (a int, b text); 858REVOKE USAGE ON TYPE priv_testtype1 FROM PUBLIC; 859GRANT USAGE ON TYPE priv_testtype1 TO regress_priv_user2; 860GRANT USAGE ON TYPE _priv_testtype1 TO regress_priv_user2; -- fail 861ERROR: cannot set privileges of array types 862HINT: Set the privileges of the element type instead. 863GRANT USAGE ON DOMAIN priv_testtype1 TO regress_priv_user2; -- fail 864ERROR: "priv_testtype1" is not a domain 865CREATE DOMAIN priv_testdomain1 AS int; 866REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC; 867GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2; 868GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok 869SET SESSION AUTHORIZATION regress_priv_user1; 870-- commands that should fail 871CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint); 872ERROR: permission denied for type priv_testdomain1 873CREATE DOMAIN priv_testdomain2a AS priv_testdomain1; 874ERROR: permission denied for type priv_testdomain1 875CREATE DOMAIN priv_testdomain3a AS int; 876CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3a AS $$ SELECT $1::priv_testdomain3a $$ LANGUAGE SQL; 877CREATE CAST (priv_testdomain1 AS priv_testdomain3a) WITH FUNCTION castfunc(int); 878ERROR: permission denied for type priv_testdomain1 879DROP FUNCTION castfunc(int) CASCADE; 880DROP DOMAIN priv_testdomain3a; 881CREATE FUNCTION priv_testfunc5a(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 882ERROR: permission denied for type priv_testdomain1 883CREATE FUNCTION priv_testfunc6a(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$; 884ERROR: permission denied for type priv_testdomain1 885CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = priv_testdomain1, RIGHTARG = priv_testdomain1); 886ERROR: permission denied for type priv_testdomain1 887CREATE TABLE test5a (a int, b priv_testdomain1); 888ERROR: permission denied for type priv_testdomain1 889CREATE TABLE test6a OF priv_testtype1; 890ERROR: permission denied for type priv_testtype1 891CREATE TABLE test10a (a int[], b priv_testtype1[]); 892ERROR: permission denied for type priv_testtype1 893CREATE TABLE test9a (a int, b int); 894ALTER TABLE test9a ADD COLUMN c priv_testdomain1; 895ERROR: permission denied for type priv_testdomain1 896ALTER TABLE test9a ALTER COLUMN b TYPE priv_testdomain1; 897ERROR: permission denied for type priv_testdomain1 898CREATE TYPE test7a AS (a int, b priv_testdomain1); 899ERROR: permission denied for type priv_testdomain1 900CREATE TYPE test8a AS (a int, b int); 901ALTER TYPE test8a ADD ATTRIBUTE c priv_testdomain1; 902ERROR: permission denied for type priv_testdomain1 903ALTER TYPE test8a ALTER ATTRIBUTE b TYPE priv_testdomain1; 904ERROR: permission denied for type priv_testdomain1 905CREATE TABLE test11a AS (SELECT 1::priv_testdomain1 AS a); 906ERROR: permission denied for type priv_testdomain1 907REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC; 908ERROR: permission denied for type priv_testtype1 909SET SESSION AUTHORIZATION regress_priv_user2; 910-- commands that should succeed 911CREATE AGGREGATE priv_testagg1b(priv_testdomain1) (sfunc = int4_sum, stype = bigint); 912CREATE DOMAIN priv_testdomain2b AS priv_testdomain1; 913CREATE DOMAIN priv_testdomain3b AS int; 914CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3b AS $$ SELECT $1::priv_testdomain3b $$ LANGUAGE SQL; 915CREATE CAST (priv_testdomain1 AS priv_testdomain3b) WITH FUNCTION castfunc(int); 916WARNING: cast will be ignored because the source data type is a domain 917CREATE FUNCTION priv_testfunc5b(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 918CREATE FUNCTION priv_testfunc6b(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$; 919CREATE OPERATOR !! (PROCEDURE = priv_testfunc5b, RIGHTARG = priv_testdomain1); 920CREATE TABLE test5b (a int, b priv_testdomain1); 921CREATE TABLE test6b OF priv_testtype1; 922CREATE TABLE test10b (a int[], b priv_testtype1[]); 923CREATE TABLE test9b (a int, b int); 924ALTER TABLE test9b ADD COLUMN c priv_testdomain1; 925ALTER TABLE test9b ALTER COLUMN b TYPE priv_testdomain1; 926CREATE TYPE test7b AS (a int, b priv_testdomain1); 927CREATE TYPE test8b AS (a int, b int); 928ALTER TYPE test8b ADD ATTRIBUTE c priv_testdomain1; 929ALTER TYPE test8b ALTER ATTRIBUTE b TYPE priv_testdomain1; 930CREATE TABLE test11b AS (SELECT 1::priv_testdomain1 AS a); 931REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC; 932WARNING: no privileges could be revoked for "priv_testtype1" 933\c - 934DROP AGGREGATE priv_testagg1b(priv_testdomain1); 935DROP DOMAIN priv_testdomain2b; 936DROP OPERATOR !! (NONE, priv_testdomain1); 937DROP FUNCTION priv_testfunc5b(a priv_testdomain1); 938DROP FUNCTION priv_testfunc6b(b int); 939DROP TABLE test5b; 940DROP TABLE test6b; 941DROP TABLE test9b; 942DROP TABLE test10b; 943DROP TYPE test7b; 944DROP TYPE test8b; 945DROP CAST (priv_testdomain1 AS priv_testdomain3b); 946DROP FUNCTION castfunc(int) CASCADE; 947DROP DOMAIN priv_testdomain3b; 948DROP TABLE test11b; 949DROP TYPE priv_testtype1; -- ok 950DROP DOMAIN priv_testdomain1; -- ok 951-- truncate 952SET SESSION AUTHORIZATION regress_priv_user5; 953TRUNCATE atest2; -- ok 954TRUNCATE atest3; -- fail 955ERROR: permission denied for table atest3 956-- has_table_privilege function 957-- bad-input checks 958select has_table_privilege(NULL,'pg_authid','select'); 959 has_table_privilege 960--------------------- 961 962(1 row) 963 964select has_table_privilege('pg_shad','select'); 965ERROR: relation "pg_shad" does not exist 966select has_table_privilege('nosuchuser','pg_authid','select'); 967ERROR: role "nosuchuser" does not exist 968select has_table_privilege('pg_authid','sel'); 969ERROR: unrecognized privilege type: "sel" 970select has_table_privilege(-999999,'pg_authid','update'); 971 has_table_privilege 972--------------------- 973 f 974(1 row) 975 976select has_table_privilege(1,'select'); 977 has_table_privilege 978--------------------- 979 980(1 row) 981 982-- superuser 983\c - 984select has_table_privilege(current_user,'pg_authid','select'); 985 has_table_privilege 986--------------------- 987 t 988(1 row) 989 990select has_table_privilege(current_user,'pg_authid','insert'); 991 has_table_privilege 992--------------------- 993 t 994(1 row) 995 996select has_table_privilege(t2.oid,'pg_authid','update') 997from (select oid from pg_roles where rolname = current_user) as t2; 998 has_table_privilege 999--------------------- 1000 t 1001(1 row) 1002 1003select has_table_privilege(t2.oid,'pg_authid','delete') 1004from (select oid from pg_roles where rolname = current_user) as t2; 1005 has_table_privilege 1006--------------------- 1007 t 1008(1 row) 1009 1010-- 'rule' privilege no longer exists, but for backwards compatibility 1011-- has_table_privilege still recognizes the keyword and says FALSE 1012select has_table_privilege(current_user,t1.oid,'rule') 1013from (select oid from pg_class where relname = 'pg_authid') as t1; 1014 has_table_privilege 1015--------------------- 1016 f 1017(1 row) 1018 1019select has_table_privilege(current_user,t1.oid,'references') 1020from (select oid from pg_class where relname = 'pg_authid') as t1; 1021 has_table_privilege 1022--------------------- 1023 t 1024(1 row) 1025 1026select has_table_privilege(t2.oid,t1.oid,'select') 1027from (select oid from pg_class where relname = 'pg_authid') as t1, 1028 (select oid from pg_roles where rolname = current_user) as t2; 1029 has_table_privilege 1030--------------------- 1031 t 1032(1 row) 1033 1034select has_table_privilege(t2.oid,t1.oid,'insert') 1035from (select oid from pg_class where relname = 'pg_authid') as t1, 1036 (select oid from pg_roles where rolname = current_user) as t2; 1037 has_table_privilege 1038--------------------- 1039 t 1040(1 row) 1041 1042select has_table_privilege('pg_authid','update'); 1043 has_table_privilege 1044--------------------- 1045 t 1046(1 row) 1047 1048select has_table_privilege('pg_authid','delete'); 1049 has_table_privilege 1050--------------------- 1051 t 1052(1 row) 1053 1054select has_table_privilege('pg_authid','truncate'); 1055 has_table_privilege 1056--------------------- 1057 t 1058(1 row) 1059 1060select has_table_privilege(t1.oid,'select') 1061from (select oid from pg_class where relname = 'pg_authid') as t1; 1062 has_table_privilege 1063--------------------- 1064 t 1065(1 row) 1066 1067select has_table_privilege(t1.oid,'trigger') 1068from (select oid from pg_class where relname = 'pg_authid') as t1; 1069 has_table_privilege 1070--------------------- 1071 t 1072(1 row) 1073 1074-- non-superuser 1075SET SESSION AUTHORIZATION regress_priv_user3; 1076select has_table_privilege(current_user,'pg_class','select'); 1077 has_table_privilege 1078--------------------- 1079 t 1080(1 row) 1081 1082select has_table_privilege(current_user,'pg_class','insert'); 1083 has_table_privilege 1084--------------------- 1085 f 1086(1 row) 1087 1088select has_table_privilege(t2.oid,'pg_class','update') 1089from (select oid from pg_roles where rolname = current_user) as t2; 1090 has_table_privilege 1091--------------------- 1092 f 1093(1 row) 1094 1095select has_table_privilege(t2.oid,'pg_class','delete') 1096from (select oid from pg_roles where rolname = current_user) as t2; 1097 has_table_privilege 1098--------------------- 1099 f 1100(1 row) 1101 1102select has_table_privilege(current_user,t1.oid,'references') 1103from (select oid from pg_class where relname = 'pg_class') as t1; 1104 has_table_privilege 1105--------------------- 1106 f 1107(1 row) 1108 1109select has_table_privilege(t2.oid,t1.oid,'select') 1110from (select oid from pg_class where relname = 'pg_class') as t1, 1111 (select oid from pg_roles where rolname = current_user) as t2; 1112 has_table_privilege 1113--------------------- 1114 t 1115(1 row) 1116 1117select has_table_privilege(t2.oid,t1.oid,'insert') 1118from (select oid from pg_class where relname = 'pg_class') as t1, 1119 (select oid from pg_roles where rolname = current_user) as t2; 1120 has_table_privilege 1121--------------------- 1122 f 1123(1 row) 1124 1125select has_table_privilege('pg_class','update'); 1126 has_table_privilege 1127--------------------- 1128 f 1129(1 row) 1130 1131select has_table_privilege('pg_class','delete'); 1132 has_table_privilege 1133--------------------- 1134 f 1135(1 row) 1136 1137select has_table_privilege('pg_class','truncate'); 1138 has_table_privilege 1139--------------------- 1140 f 1141(1 row) 1142 1143select has_table_privilege(t1.oid,'select') 1144from (select oid from pg_class where relname = 'pg_class') as t1; 1145 has_table_privilege 1146--------------------- 1147 t 1148(1 row) 1149 1150select has_table_privilege(t1.oid,'trigger') 1151from (select oid from pg_class where relname = 'pg_class') as t1; 1152 has_table_privilege 1153--------------------- 1154 f 1155(1 row) 1156 1157select has_table_privilege(current_user,'atest1','select'); 1158 has_table_privilege 1159--------------------- 1160 t 1161(1 row) 1162 1163select has_table_privilege(current_user,'atest1','insert'); 1164 has_table_privilege 1165--------------------- 1166 f 1167(1 row) 1168 1169select has_table_privilege(t2.oid,'atest1','update') 1170from (select oid from pg_roles where rolname = current_user) as t2; 1171 has_table_privilege 1172--------------------- 1173 f 1174(1 row) 1175 1176select has_table_privilege(t2.oid,'atest1','delete') 1177from (select oid from pg_roles where rolname = current_user) as t2; 1178 has_table_privilege 1179--------------------- 1180 f 1181(1 row) 1182 1183select has_table_privilege(current_user,t1.oid,'references') 1184from (select oid from pg_class where relname = 'atest1') as t1; 1185 has_table_privilege 1186--------------------- 1187 f 1188(1 row) 1189 1190select has_table_privilege(t2.oid,t1.oid,'select') 1191from (select oid from pg_class where relname = 'atest1') as t1, 1192 (select oid from pg_roles where rolname = current_user) as t2; 1193 has_table_privilege 1194--------------------- 1195 t 1196(1 row) 1197 1198select has_table_privilege(t2.oid,t1.oid,'insert') 1199from (select oid from pg_class where relname = 'atest1') as t1, 1200 (select oid from pg_roles where rolname = current_user) as t2; 1201 has_table_privilege 1202--------------------- 1203 f 1204(1 row) 1205 1206select has_table_privilege('atest1','update'); 1207 has_table_privilege 1208--------------------- 1209 f 1210(1 row) 1211 1212select has_table_privilege('atest1','delete'); 1213 has_table_privilege 1214--------------------- 1215 f 1216(1 row) 1217 1218select has_table_privilege('atest1','truncate'); 1219 has_table_privilege 1220--------------------- 1221 f 1222(1 row) 1223 1224select has_table_privilege(t1.oid,'select') 1225from (select oid from pg_class where relname = 'atest1') as t1; 1226 has_table_privilege 1227--------------------- 1228 t 1229(1 row) 1230 1231select has_table_privilege(t1.oid,'trigger') 1232from (select oid from pg_class where relname = 'atest1') as t1; 1233 has_table_privilege 1234--------------------- 1235 f 1236(1 row) 1237 1238-- has_column_privilege function 1239-- bad-input checks (as non-super-user) 1240select has_column_privilege('pg_authid',NULL,'select'); 1241 has_column_privilege 1242---------------------- 1243 1244(1 row) 1245 1246select has_column_privilege('pg_authid','nosuchcol','select'); 1247ERROR: column "nosuchcol" of relation "pg_authid" does not exist 1248select has_column_privilege(9999,'nosuchcol','select'); 1249 has_column_privilege 1250---------------------- 1251 1252(1 row) 1253 1254select has_column_privilege(9999,99::int2,'select'); 1255 has_column_privilege 1256---------------------- 1257 1258(1 row) 1259 1260select has_column_privilege('pg_authid',99::int2,'select'); 1261 has_column_privilege 1262---------------------- 1263 1264(1 row) 1265 1266select has_column_privilege(9999,99::int2,'select'); 1267 has_column_privilege 1268---------------------- 1269 1270(1 row) 1271 1272create temp table mytable(f1 int, f2 int, f3 int); 1273alter table mytable drop column f2; 1274select has_column_privilege('mytable','f2','select'); 1275ERROR: column "f2" of relation "mytable" does not exist 1276select has_column_privilege('mytable','........pg.dropped.2........','select'); 1277 has_column_privilege 1278---------------------- 1279 1280(1 row) 1281 1282select has_column_privilege('mytable',2::int2,'select'); 1283 has_column_privilege 1284---------------------- 1285 t 1286(1 row) 1287 1288revoke select on table mytable from regress_priv_user3; 1289select has_column_privilege('mytable',2::int2,'select'); 1290 has_column_privilege 1291---------------------- 1292 1293(1 row) 1294 1295drop table mytable; 1296-- Grant options 1297SET SESSION AUTHORIZATION regress_priv_user1; 1298CREATE TABLE atest4 (a int); 1299GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION; 1300GRANT UPDATE ON atest4 TO regress_priv_user2; 1301GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION; 1302SET SESSION AUTHORIZATION regress_priv_user2; 1303GRANT SELECT ON atest4 TO regress_priv_user3; 1304GRANT UPDATE ON atest4 TO regress_priv_user3; -- fail 1305WARNING: no privileges were granted for "atest4" 1306SET SESSION AUTHORIZATION regress_priv_user1; 1307REVOKE SELECT ON atest4 FROM regress_priv_user3; -- does nothing 1308SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- true 1309 has_table_privilege 1310--------------------- 1311 t 1312(1 row) 1313 1314REVOKE SELECT ON atest4 FROM regress_priv_user2; -- fail 1315ERROR: dependent privileges exist 1316HINT: Use CASCADE to revoke them too. 1317REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_priv_user2 CASCADE; -- ok 1318SELECT has_table_privilege('regress_priv_user2', 'atest4', 'SELECT'); -- true 1319 has_table_privilege 1320--------------------- 1321 t 1322(1 row) 1323 1324SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- false 1325 has_table_privilege 1326--------------------- 1327 f 1328(1 row) 1329 1330SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true 1331 has_table_privilege 1332--------------------- 1333 t 1334(1 row) 1335 1336-- security-restricted operations 1337\c - 1338CREATE ROLE regress_sro_user; 1339SET SESSION AUTHORIZATION regress_sro_user; 1340CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS 1341 'GRANT regress_priv_group2 TO regress_sro_user'; 1342CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 1343 'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant(); SELECT true'; 1344-- REFRESH of this MV will queue a GRANT at end of transaction 1345CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA; 1346REFRESH MATERIALIZED VIEW sro_mv; 1347ERROR: cannot create a cursor WITH HOLD within security-restricted operation 1348CONTEXT: SQL function "mv_action" statement 1 1349\c - 1350REFRESH MATERIALIZED VIEW sro_mv; 1351ERROR: cannot create a cursor WITH HOLD within security-restricted operation 1352CONTEXT: SQL function "mv_action" statement 1 1353SET SESSION AUTHORIZATION regress_sro_user; 1354-- INSERT to this table will queue a GRANT at end of transaction 1355CREATE TABLE sro_trojan_table (); 1356CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS 1357 'BEGIN PERFORM unwanted_grant(); RETURN NULL; END'; 1358CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table 1359 INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan(); 1360-- Now, REFRESH will issue such an INSERT, queueing the GRANT 1361CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 1362 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; 1363REFRESH MATERIALIZED VIEW sro_mv; 1364ERROR: cannot fire deferred trigger within security-restricted operation 1365CONTEXT: SQL function "mv_action" statement 1 1366\c - 1367REFRESH MATERIALIZED VIEW sro_mv; 1368ERROR: cannot fire deferred trigger within security-restricted operation 1369CONTEXT: SQL function "mv_action" statement 1 1370BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; 1371ERROR: must have admin option on role "regress_priv_group2" 1372CONTEXT: SQL function "unwanted_grant" statement 1 1373SQL statement "SELECT unwanted_grant()" 1374PL/pgSQL function sro_trojan() line 1 at PERFORM 1375SQL function "mv_action" statement 1 1376DROP OWNED BY regress_sro_user; 1377DROP ROLE regress_sro_user; 1378-- Admin options 1379SET SESSION AUTHORIZATION regress_priv_user4; 1380CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS 1381 'GRANT regress_priv_group2 TO regress_priv_user5'; 1382GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION 1383SET ROLE regress_priv_group2; 1384GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege 1385ERROR: must have admin option on role "regress_priv_group2" 1386SET SESSION AUTHORIZATION regress_priv_user1; 1387GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION 1388ERROR: must have admin option on role "regress_priv_group2" 1389SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN 1390NOTICE: role "regress_priv_user5" is already a member of role "regress_priv_group2" 1391 dogrant_ok 1392------------ 1393 1394(1 row) 1395 1396SET ROLE regress_priv_group2; 1397GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help 1398ERROR: must have admin option on role "regress_priv_group2" 1399SET SESSION AUTHORIZATION regress_priv_group2; 1400GRANT regress_priv_group2 TO regress_priv_user5; -- ok: a role can self-admin 1401NOTICE: role "regress_priv_user5" is already a member of role "regress_priv_group2" 1402CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS 1403 'GRANT regress_priv_group2 TO regress_priv_user5'; 1404SELECT dogrant_fails(); -- fails: no self-admin in SECURITY DEFINER 1405ERROR: must have admin option on role "regress_priv_group2" 1406CONTEXT: SQL function "dogrant_fails" statement 1 1407DROP FUNCTION dogrant_fails(); 1408SET SESSION AUTHORIZATION regress_priv_user4; 1409DROP FUNCTION dogrant_ok(); 1410REVOKE regress_priv_group2 FROM regress_priv_user5; 1411-- has_sequence_privilege tests 1412\c - 1413CREATE SEQUENCE x_seq; 1414GRANT USAGE on x_seq to regress_priv_user2; 1415SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT'); 1416ERROR: "atest1" is not a sequence 1417SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT'); 1418ERROR: unrecognized privilege type: "INSERT" 1419SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT'); 1420 has_sequence_privilege 1421------------------------ 1422 f 1423(1 row) 1424 1425SET SESSION AUTHORIZATION regress_priv_user2; 1426SELECT has_sequence_privilege('x_seq', 'USAGE'); 1427 has_sequence_privilege 1428------------------------ 1429 t 1430(1 row) 1431 1432-- largeobject privilege tests 1433\c - 1434SET SESSION AUTHORIZATION regress_priv_user1; 1435SELECT lo_create(1001); 1436 lo_create 1437----------- 1438 1001 1439(1 row) 1440 1441SELECT lo_create(1002); 1442 lo_create 1443----------- 1444 1002 1445(1 row) 1446 1447SELECT lo_create(1003); 1448 lo_create 1449----------- 1450 1003 1451(1 row) 1452 1453SELECT lo_create(1004); 1454 lo_create 1455----------- 1456 1004 1457(1 row) 1458 1459SELECT lo_create(1005); 1460 lo_create 1461----------- 1462 1005 1463(1 row) 1464 1465GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC; 1466GRANT SELECT ON LARGE OBJECT 1003 TO regress_priv_user2; 1467GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_priv_user2; 1468GRANT ALL ON LARGE OBJECT 1005 TO regress_priv_user2; 1469GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user2 WITH GRANT OPTION; 1470GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC; -- to be failed 1471ERROR: invalid privilege type INSERT for large object 1472GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser; -- to be failed 1473ERROR: role "nosuchuser" does not exist 1474GRANT SELECT, UPDATE ON LARGE OBJECT 999 TO PUBLIC; -- to be failed 1475ERROR: large object 999 does not exist 1476\c - 1477SET SESSION AUTHORIZATION regress_priv_user2; 1478SELECT lo_create(2001); 1479 lo_create 1480----------- 1481 2001 1482(1 row) 1483 1484SELECT lo_create(2002); 1485 lo_create 1486----------- 1487 2002 1488(1 row) 1489 1490SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now 1491 loread 1492-------- 1493 \x 1494(1 row) 1495 1496SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode 1497ERROR: large object descriptor 0 was not opened for writing 1498SELECT loread(lo_open(1001, x'40000'::int), 32); 1499 loread 1500-------- 1501 \x 1502(1 row) 1503 1504SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 1505ERROR: permission denied for large object 1002 1506SELECT loread(lo_open(1003, x'40000'::int), 32); 1507 loread 1508-------- 1509 \x 1510(1 row) 1511 1512SELECT loread(lo_open(1004, x'40000'::int), 32); 1513 loread 1514-------- 1515 \x 1516(1 row) 1517 1518SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd'); 1519 lowrite 1520--------- 1521 4 1522(1 row) 1523 1524SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 1525ERROR: permission denied for large object 1002 1526SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied 1527ERROR: permission denied for large object 1003 1528SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd'); 1529 lowrite 1530--------- 1531 4 1532(1 row) 1533 1534GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3; 1535GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3; -- to be denied 1536ERROR: large object 1006 does not exist 1537REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC; 1538GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3; 1539SELECT lo_unlink(1001); -- to be denied 1540ERROR: must be owner of large object 1001 1541SELECT lo_unlink(2002); 1542 lo_unlink 1543----------- 1544 1 1545(1 row) 1546 1547\c - 1548-- confirm ACL setting 1549SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 1550 oid | ownername | lomacl 1551------+--------------------+------------------------------------------------------------------------------------------------------------------------------ 1552 1001 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,=rw/regress_priv_user1} 1553 1002 | regress_priv_user1 | 1554 1003 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r/regress_priv_user1} 1555 1004 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=rw/regress_priv_user1} 1556 1005 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r*w/regress_priv_user1,regress_priv_user3=r/regress_priv_user2} 1557 2001 | regress_priv_user2 | {regress_priv_user2=rw/regress_priv_user2,regress_priv_user3=rw/regress_priv_user2} 1558(6 rows) 1559 1560SET SESSION AUTHORIZATION regress_priv_user3; 1561SELECT loread(lo_open(1001, x'40000'::int), 32); 1562 loread 1563------------ 1564 \x61626364 1565(1 row) 1566 1567SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied 1568ERROR: permission denied for large object 1003 1569SELECT loread(lo_open(1005, x'40000'::int), 32); 1570 loread 1571-------- 1572 \x 1573(1 row) 1574 1575SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied 1576ERROR: permission denied for large object 1005 1577SELECT lo_truncate(lo_open(2001, x'20000'::int), 10); 1578 lo_truncate 1579------------- 1580 0 1581(1 row) 1582 1583-- compatibility mode in largeobject permission 1584\c - 1585SET lo_compat_privileges = false; -- default setting 1586SET SESSION AUTHORIZATION regress_priv_user4; 1587SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 1588ERROR: permission denied for large object 1002 1589SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 1590ERROR: permission denied for large object 1002 1591SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied 1592ERROR: permission denied for large object 1002 1593SELECT lo_put(1002, 1, 'abcd'); -- to be denied 1594ERROR: permission denied for large object 1002 1595SELECT lo_unlink(1002); -- to be denied 1596ERROR: must be owner of large object 1002 1597SELECT lo_export(1001, '/dev/null'); -- to be denied 1598ERROR: permission denied for function lo_export 1599SELECT lo_import('/dev/null'); -- to be denied 1600ERROR: permission denied for function lo_import 1601SELECT lo_import('/dev/null', 2003); -- to be denied 1602ERROR: permission denied for function lo_import 1603\c - 1604SET lo_compat_privileges = true; -- compatibility mode 1605SET SESSION AUTHORIZATION regress_priv_user4; 1606SELECT loread(lo_open(1002, x'40000'::int), 32); 1607 loread 1608-------- 1609 \x 1610(1 row) 1611 1612SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); 1613 lowrite 1614--------- 1615 4 1616(1 row) 1617 1618SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); 1619 lo_truncate 1620------------- 1621 0 1622(1 row) 1623 1624SELECT lo_unlink(1002); 1625 lo_unlink 1626----------- 1627 1 1628(1 row) 1629 1630SELECT lo_export(1001, '/dev/null'); -- to be denied 1631ERROR: permission denied for function lo_export 1632-- don't allow unpriv users to access pg_largeobject contents 1633\c - 1634SELECT * FROM pg_largeobject LIMIT 0; 1635 loid | pageno | data 1636------+--------+------ 1637(0 rows) 1638 1639SET SESSION AUTHORIZATION regress_priv_user1; 1640SELECT * FROM pg_largeobject LIMIT 0; -- to be denied 1641ERROR: permission denied for table pg_largeobject 1642-- test default ACLs 1643\c - 1644CREATE SCHEMA testns; 1645GRANT ALL ON SCHEMA testns TO regress_priv_user1; 1646CREATE TABLE testns.acltest1 (x int); 1647SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no 1648 has_table_privilege 1649--------------------- 1650 f 1651(1 row) 1652 1653SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1654 has_table_privilege 1655--------------------- 1656 f 1657(1 row) 1658 1659-- placeholder for test with duplicated schema and role names 1660ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public; 1661SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no 1662 has_table_privilege 1663--------------------- 1664 f 1665(1 row) 1666 1667SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1668 has_table_privilege 1669--------------------- 1670 f 1671(1 row) 1672 1673DROP TABLE testns.acltest1; 1674CREATE TABLE testns.acltest1 (x int); 1675SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes 1676 has_table_privilege 1677--------------------- 1678 t 1679(1 row) 1680 1681SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1682 has_table_privilege 1683--------------------- 1684 f 1685(1 row) 1686 1687ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1; 1688DROP TABLE testns.acltest1; 1689CREATE TABLE testns.acltest1 (x int); 1690SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes 1691 has_table_privilege 1692--------------------- 1693 t 1694(1 row) 1695 1696SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- yes 1697 has_table_privilege 1698--------------------- 1699 t 1700(1 row) 1701 1702ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1; 1703DROP TABLE testns.acltest1; 1704CREATE TABLE testns.acltest1 (x int); 1705SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes 1706 has_table_privilege 1707--------------------- 1708 t 1709(1 row) 1710 1711SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1712 has_table_privilege 1713--------------------- 1714 f 1715(1 row) 1716 1717ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM public; 1718ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_priv_user2; -- error 1719ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS 1720-- 1721-- Testing blanket default grants is very hazardous since it might change 1722-- the privileges attached to objects created by concurrent regression tests. 1723-- To avoid that, be sure to revoke the privileges again before committing. 1724-- 1725BEGIN; 1726ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2; 1727CREATE SCHEMA testns2; 1728SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes 1729 has_schema_privilege 1730---------------------- 1731 t 1732(1 row) 1733 1734SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no 1735 has_schema_privilege 1736---------------------- 1737 f 1738(1 row) 1739 1740ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2; 1741CREATE SCHEMA testns3; 1742SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'USAGE'); -- no 1743 has_schema_privilege 1744---------------------- 1745 f 1746(1 row) 1747 1748SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'CREATE'); -- no 1749 has_schema_privilege 1750---------------------- 1751 f 1752(1 row) 1753 1754ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2; 1755CREATE SCHEMA testns4; 1756SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'USAGE'); -- yes 1757 has_schema_privilege 1758---------------------- 1759 t 1760(1 row) 1761 1762SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes 1763 has_schema_privilege 1764---------------------- 1765 t 1766(1 row) 1767 1768ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2; 1769COMMIT; 1770CREATE SCHEMA testns5; 1771SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE'); -- no 1772 has_schema_privilege 1773---------------------- 1774 f 1775(1 row) 1776 1777SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'CREATE'); -- no 1778 has_schema_privilege 1779---------------------- 1780 f 1781(1 row) 1782 1783SET ROLE regress_priv_user1; 1784CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1785CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); 1786CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; 1787SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- no 1788 has_function_privilege 1789------------------------ 1790 f 1791(1 row) 1792 1793SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- no 1794 has_function_privilege 1795------------------------ 1796 f 1797(1 row) 1798 1799SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- no 1800 has_function_privilege 1801------------------------ 1802 f 1803(1 row) 1804 1805ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public; 1806DROP FUNCTION testns.foo(); 1807CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1808DROP AGGREGATE testns.agg1(int); 1809CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); 1810DROP PROCEDURE testns.bar(); 1811CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; 1812SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- yes 1813 has_function_privilege 1814------------------------ 1815 t 1816(1 row) 1817 1818SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes 1819 has_function_privilege 1820------------------------ 1821 t 1822(1 row) 1823 1824SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here) 1825 has_function_privilege 1826------------------------ 1827 t 1828(1 row) 1829 1830DROP FUNCTION testns.foo(); 1831DROP AGGREGATE testns.agg1(int); 1832DROP PROCEDURE testns.bar(); 1833ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE USAGE ON TYPES FROM public; 1834CREATE DOMAIN testns.priv_testdomain1 AS int; 1835SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- no 1836 has_type_privilege 1837-------------------- 1838 f 1839(1 row) 1840 1841ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public; 1842DROP DOMAIN testns.priv_testdomain1; 1843CREATE DOMAIN testns.priv_testdomain1 AS int; 1844SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- yes 1845 has_type_privilege 1846-------------------- 1847 t 1848(1 row) 1849 1850DROP DOMAIN testns.priv_testdomain1; 1851RESET ROLE; 1852SELECT count(*) 1853 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1854 WHERE nspname = 'testns'; 1855 count 1856------- 1857 3 1858(1 row) 1859 1860DROP SCHEMA testns CASCADE; 1861NOTICE: drop cascades to table testns.acltest1 1862DROP SCHEMA testns2 CASCADE; 1863DROP SCHEMA testns3 CASCADE; 1864DROP SCHEMA testns4 CASCADE; 1865DROP SCHEMA testns5 CASCADE; 1866SELECT d.* -- check that entries went away 1867 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1868 WHERE nspname IS NULL AND defaclnamespace != 0; 1869 defaclrole | defaclnamespace | defaclobjtype | defaclacl 1870------------+-----------------+---------------+----------- 1871(0 rows) 1872 1873-- Grant on all objects of given type in a schema 1874\c - 1875CREATE SCHEMA testns; 1876CREATE TABLE testns.t1 (f1 int); 1877CREATE TABLE testns.t2 (f1 int); 1878SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false 1879 has_table_privilege 1880--------------------- 1881 f 1882(1 row) 1883 1884GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1; 1885SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- true 1886 has_table_privilege 1887--------------------- 1888 t 1889(1 row) 1890 1891SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- true 1892 has_table_privilege 1893--------------------- 1894 t 1895(1 row) 1896 1897REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1; 1898SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false 1899 has_table_privilege 1900--------------------- 1901 f 1902(1 row) 1903 1904SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- false 1905 has_table_privilege 1906--------------------- 1907 f 1908(1 row) 1909 1910CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 1911CREATE AGGREGATE testns.priv_testagg(int) (sfunc = int4pl, stype = int4); 1912CREATE PROCEDURE testns.priv_testproc(int) AS 'select 3' LANGUAGE sql; 1913SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true by default 1914 has_function_privilege 1915------------------------ 1916 t 1917(1 row) 1918 1919SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true by default 1920 has_function_privilege 1921------------------------ 1922 t 1923(1 row) 1924 1925SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true by default 1926 has_function_privilege 1927------------------------ 1928 t 1929(1 row) 1930 1931REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; 1932SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- false 1933 has_function_privilege 1934------------------------ 1935 f 1936(1 row) 1937 1938SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- false 1939 has_function_privilege 1940------------------------ 1941 f 1942(1 row) 1943 1944SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- still true, not a function 1945 has_function_privilege 1946------------------------ 1947 t 1948(1 row) 1949 1950REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC; 1951SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- now false 1952 has_function_privilege 1953------------------------ 1954 f 1955(1 row) 1956 1957GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC; 1958SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true 1959 has_function_privilege 1960------------------------ 1961 t 1962(1 row) 1963 1964SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true 1965 has_function_privilege 1966------------------------ 1967 t 1968(1 row) 1969 1970SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true 1971 has_function_privilege 1972------------------------ 1973 t 1974(1 row) 1975 1976\set VERBOSITY terse \\ -- suppress cascade details 1977DROP SCHEMA testns CASCADE; 1978NOTICE: drop cascades to 5 other objects 1979\set VERBOSITY default 1980-- Change owner of the schema & and rename of new schema owner 1981\c - 1982CREATE ROLE regress_schemauser1 superuser login; 1983CREATE ROLE regress_schemauser2 superuser login; 1984SET SESSION ROLE regress_schemauser1; 1985CREATE SCHEMA testns; 1986SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1987 nspname | rolname 1988---------+--------------------- 1989 testns | regress_schemauser1 1990(1 row) 1991 1992ALTER SCHEMA testns OWNER TO regress_schemauser2; 1993ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed; 1994SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1995 nspname | rolname 1996---------+---------------------------- 1997 testns | regress_schemauser_renamed 1998(1 row) 1999 2000set session role regress_schemauser_renamed; 2001\set VERBOSITY terse \\ -- suppress cascade details 2002DROP SCHEMA testns CASCADE; 2003\set VERBOSITY default 2004-- clean up 2005\c - 2006DROP ROLE regress_schemauser1; 2007DROP ROLE regress_schemauser_renamed; 2008-- test that dependent privileges are revoked (or not) properly 2009\c - 2010set session role regress_priv_user1; 2011create table dep_priv_test (a int); 2012grant select on dep_priv_test to regress_priv_user2 with grant option; 2013grant select on dep_priv_test to regress_priv_user3 with grant option; 2014set session role regress_priv_user2; 2015grant select on dep_priv_test to regress_priv_user4 with grant option; 2016set session role regress_priv_user3; 2017grant select on dep_priv_test to regress_priv_user4 with grant option; 2018set session role regress_priv_user4; 2019grant select on dep_priv_test to regress_priv_user5; 2020\dp dep_priv_test 2021 Access privileges 2022 Schema | Name | Type | Access privileges | Column privileges | Policies 2023--------+---------------+-------+-----------------------------------------------+-------------------+---------- 2024 public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | 2025 | | | regress_priv_user2=r*/regress_priv_user1 +| | 2026 | | | regress_priv_user3=r*/regress_priv_user1 +| | 2027 | | | regress_priv_user4=r*/regress_priv_user2 +| | 2028 | | | regress_priv_user4=r*/regress_priv_user3 +| | 2029 | | | regress_priv_user5=r/regress_priv_user4 | | 2030(1 row) 2031 2032set session role regress_priv_user2; 2033revoke select on dep_priv_test from regress_priv_user4 cascade; 2034\dp dep_priv_test 2035 Access privileges 2036 Schema | Name | Type | Access privileges | Column privileges | Policies 2037--------+---------------+-------+-----------------------------------------------+-------------------+---------- 2038 public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | 2039 | | | regress_priv_user2=r*/regress_priv_user1 +| | 2040 | | | regress_priv_user3=r*/regress_priv_user1 +| | 2041 | | | regress_priv_user4=r*/regress_priv_user3 +| | 2042 | | | regress_priv_user5=r/regress_priv_user4 | | 2043(1 row) 2044 2045set session role regress_priv_user3; 2046revoke select on dep_priv_test from regress_priv_user4 cascade; 2047\dp dep_priv_test 2048 Access privileges 2049 Schema | Name | Type | Access privileges | Column privileges | Policies 2050--------+---------------+-------+-----------------------------------------------+-------------------+---------- 2051 public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| | 2052 | | | regress_priv_user2=r*/regress_priv_user1 +| | 2053 | | | regress_priv_user3=r*/regress_priv_user1 | | 2054(1 row) 2055 2056set session role regress_priv_user1; 2057drop table dep_priv_test; 2058-- clean up 2059\c 2060drop sequence x_seq; 2061DROP AGGREGATE priv_testagg1(int); 2062DROP FUNCTION priv_testfunc2(int); 2063DROP FUNCTION priv_testfunc4(boolean); 2064DROP PROCEDURE priv_testproc1(int); 2065DROP VIEW atestv0; 2066DROP VIEW atestv1; 2067DROP VIEW atestv2; 2068-- this should cascade to drop atestv4 2069DROP VIEW atestv3 CASCADE; 2070NOTICE: drop cascades to view atestv4 2071-- this should complain "does not exist" 2072DROP VIEW atestv4; 2073ERROR: view "atestv4" does not exist 2074DROP TABLE atest1; 2075DROP TABLE atest2; 2076DROP TABLE atest3; 2077DROP TABLE atest4; 2078DROP TABLE atest5; 2079DROP TABLE atest6; 2080DROP TABLE atestc; 2081DROP TABLE atestp1; 2082DROP TABLE atestp2; 2083SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 2084 lo_unlink 2085----------- 2086 1 2087 1 2088 1 2089 1 2090 1 2091(5 rows) 2092 2093DROP GROUP regress_priv_group1; 2094DROP GROUP regress_priv_group2; 2095-- these are needed to clean up permissions 2096REVOKE USAGE ON LANGUAGE sql FROM regress_priv_user1; 2097DROP OWNED BY regress_priv_user1; 2098DROP USER regress_priv_user1; 2099DROP USER regress_priv_user2; 2100DROP USER regress_priv_user3; 2101DROP USER regress_priv_user4; 2102DROP USER regress_priv_user5; 2103DROP USER regress_priv_user6; 2104ERROR: role "regress_priv_user6" does not exist 2105-- permissions with LOCK TABLE 2106CREATE USER regress_locktable_user; 2107CREATE TABLE lock_table (a int); 2108-- LOCK TABLE and SELECT permission 2109GRANT SELECT ON lock_table TO regress_locktable_user; 2110SET SESSION AUTHORIZATION regress_locktable_user; 2111BEGIN; 2112LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail 2113ERROR: permission denied for table lock_table 2114ROLLBACK; 2115BEGIN; 2116LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass 2117COMMIT; 2118BEGIN; 2119LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 2120ERROR: permission denied for table lock_table 2121ROLLBACK; 2122\c 2123REVOKE SELECT ON lock_table FROM regress_locktable_user; 2124-- LOCK TABLE and INSERT permission 2125GRANT INSERT ON lock_table TO regress_locktable_user; 2126SET SESSION AUTHORIZATION regress_locktable_user; 2127BEGIN; 2128LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 2129COMMIT; 2130BEGIN; 2131LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2132ERROR: permission denied for table lock_table 2133ROLLBACK; 2134BEGIN; 2135LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 2136ERROR: permission denied for table lock_table 2137ROLLBACK; 2138\c 2139REVOKE INSERT ON lock_table FROM regress_locktable_user; 2140-- LOCK TABLE and UPDATE permission 2141GRANT UPDATE ON lock_table TO regress_locktable_user; 2142SET SESSION AUTHORIZATION regress_locktable_user; 2143BEGIN; 2144LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 2145COMMIT; 2146BEGIN; 2147LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2148ERROR: permission denied for table lock_table 2149ROLLBACK; 2150BEGIN; 2151LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 2152COMMIT; 2153\c 2154REVOKE UPDATE ON lock_table FROM regress_locktable_user; 2155-- LOCK TABLE and DELETE permission 2156GRANT DELETE ON lock_table TO regress_locktable_user; 2157SET SESSION AUTHORIZATION regress_locktable_user; 2158BEGIN; 2159LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 2160COMMIT; 2161BEGIN; 2162LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2163ERROR: permission denied for table lock_table 2164ROLLBACK; 2165BEGIN; 2166LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 2167COMMIT; 2168\c 2169REVOKE DELETE ON lock_table FROM regress_locktable_user; 2170-- LOCK TABLE and TRUNCATE permission 2171GRANT TRUNCATE ON lock_table TO regress_locktable_user; 2172SET SESSION AUTHORIZATION regress_locktable_user; 2173BEGIN; 2174LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 2175COMMIT; 2176BEGIN; 2177LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 2178ERROR: permission denied for table lock_table 2179ROLLBACK; 2180BEGIN; 2181LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 2182COMMIT; 2183\c 2184REVOKE TRUNCATE ON lock_table FROM regress_locktable_user; 2185-- clean up 2186DROP TABLE lock_table; 2187DROP USER regress_locktable_user; 2188