1-- 2-- Test access privileges 3-- 4 5-- Clean up in case a prior regression run failed 6 7-- Suppress NOTICE messages when users/groups don't exist 8SET client_min_messages TO 'warning'; 9 10DROP ROLE IF EXISTS regress_group1; 11DROP ROLE IF EXISTS regress_group2; 12 13DROP ROLE IF EXISTS regress_user1; 14DROP ROLE IF EXISTS regress_user2; 15DROP ROLE IF EXISTS regress_user3; 16DROP ROLE IF EXISTS regress_user4; 17DROP ROLE IF EXISTS regress_user5; 18DROP ROLE IF EXISTS regress_user6; 19 20SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 21 22RESET client_min_messages; 23 24-- test proper begins here 25 26CREATE USER regress_user1; 27CREATE USER regress_user2; 28CREATE USER regress_user3; 29CREATE USER regress_user4; 30CREATE USER regress_user5; 31CREATE USER regress_user5; -- duplicate 32 33CREATE GROUP regress_group1; 34CREATE GROUP regress_group2 WITH USER regress_user1, regress_user2; 35 36ALTER GROUP regress_group1 ADD USER regress_user4; 37 38ALTER GROUP regress_group2 ADD USER regress_user2; -- duplicate 39ALTER GROUP regress_group2 DROP USER regress_user2; 40GRANT regress_group2 TO regress_user4 WITH ADMIN OPTION; 41 42-- test owner privileges 43 44SET SESSION AUTHORIZATION regress_user1; 45SELECT session_user, current_user; 46 47CREATE TABLE atest1 ( a int, b text ); 48SELECT * FROM atest1; 49INSERT INTO atest1 VALUES (1, 'one'); 50DELETE FROM atest1; 51UPDATE atest1 SET a = 1 WHERE b = 'blech'; 52TRUNCATE atest1; 53BEGIN; 54LOCK atest1 IN ACCESS EXCLUSIVE MODE; 55COMMIT; 56 57REVOKE ALL ON atest1 FROM PUBLIC; 58SELECT * FROM atest1; 59 60GRANT ALL ON atest1 TO regress_user2; 61GRANT SELECT ON atest1 TO regress_user3, regress_user4; 62SELECT * FROM atest1; 63 64CREATE TABLE atest2 (col1 varchar(10), col2 boolean); 65GRANT SELECT ON atest2 TO regress_user2; 66GRANT UPDATE ON atest2 TO regress_user3; 67GRANT INSERT ON atest2 TO regress_user4; 68GRANT TRUNCATE ON atest2 TO regress_user5; 69 70 71SET SESSION AUTHORIZATION regress_user2; 72SELECT session_user, current_user; 73 74-- try various combinations of queries on atest1 and atest2 75 76SELECT * FROM atest1; -- ok 77SELECT * FROM atest2; -- ok 78INSERT INTO atest1 VALUES (2, 'two'); -- ok 79INSERT INTO atest2 VALUES ('foo', true); -- fail 80INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok 81UPDATE atest1 SET a = 1 WHERE a = 2; -- ok 82UPDATE atest2 SET col2 = NOT col2; -- fail 83SELECT * FROM atest1 FOR UPDATE; -- ok 84SELECT * FROM atest2 FOR UPDATE; -- fail 85DELETE FROM atest2; -- fail 86TRUNCATE atest2; -- fail 87BEGIN; 88LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail 89COMMIT; 90COPY atest2 FROM stdin; -- fail 91GRANT ALL ON atest1 TO PUBLIC; -- fail 92 93-- checks in subquery, both ok 94SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) ); 95SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) ); 96 97 98SET SESSION AUTHORIZATION regress_user3; 99SELECT session_user, current_user; 100 101SELECT * FROM atest1; -- ok 102SELECT * FROM atest2; -- fail 103INSERT INTO atest1 VALUES (2, 'two'); -- fail 104INSERT INTO atest2 VALUES ('foo', true); -- fail 105INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail 106UPDATE atest1 SET a = 1 WHERE a = 2; -- fail 107UPDATE atest2 SET col2 = NULL; -- ok 108UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2 109UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok 110SELECT * FROM atest1 FOR UPDATE; -- fail 111SELECT * FROM atest2 FOR UPDATE; -- fail 112DELETE FROM atest2; -- fail 113TRUNCATE atest2; -- fail 114BEGIN; 115LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok 116COMMIT; 117COPY atest2 FROM stdin; -- fail 118 119-- checks in subquery, both fail 120SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) ); 121SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) ); 122 123SET SESSION AUTHORIZATION regress_user4; 124COPY atest2 FROM stdin; -- ok 125bar true 126\. 127SELECT * FROM atest1; -- ok 128 129 130-- test leaky-function protections in selfuncs 131 132-- regress_user1 will own a table and provide views for it. 133SET SESSION AUTHORIZATION regress_user1; 134 135CREATE TABLE atest12 as 136 SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x; 137CREATE INDEX ON atest12 (a); 138CREATE INDEX ON atest12 (abs(a)); 139VACUUM ANALYZE atest12; 140 141CREATE FUNCTION leak(integer,integer) RETURNS boolean 142 AS $$begin return $1 < $2; end$$ 143 LANGUAGE plpgsql immutable; 144CREATE OPERATOR <<< (procedure = leak, leftarg = integer, rightarg = integer, 145 restrict = scalarltsel); 146 147-- views with leaky operator 148CREATE VIEW atest12v AS 149 SELECT * FROM atest12 WHERE b <<< 5; 150CREATE VIEW atest12sbv WITH (security_barrier=true) AS 151 SELECT * FROM atest12 WHERE b <<< 5; 152GRANT SELECT ON atest12v TO PUBLIC; 153GRANT SELECT ON atest12sbv TO PUBLIC; 154 155-- This plan should use nestloop, knowing that few rows will be selected. 156EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; 157 158-- And this one. 159EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y 160 WHERE x.a = y.b and abs(y.a) <<< 5; 161 162-- This should also be a nestloop, but the security barrier forces the inner 163-- scan to be materialized 164EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; 165 166-- Check if regress_user2 can break security. 167SET SESSION AUTHORIZATION regress_user2; 168 169CREATE FUNCTION leak2(integer,integer) RETURNS boolean 170 AS $$begin raise notice 'leak % %', $1, $2; return $1 > $2; end$$ 171 LANGUAGE plpgsql immutable; 172CREATE OPERATOR >>> (procedure = leak2, leftarg = integer, rightarg = integer, 173 restrict = scalargtsel); 174 175-- This should not show any "leak" notices before failing. 176EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0; 177 178-- These plans should continue to use a nestloop, since they execute with the 179-- privileges of the view owner. 180EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; 181EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; 182 183-- A non-security barrier view does not guard against information leakage. 184EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y 185 WHERE x.a = y.b and abs(y.a) <<< 5; 186 187-- But a security barrier view isolates the leaky operator. 188EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y 189 WHERE x.a = y.b and abs(y.a) <<< 5; 190 191-- Now regress_user1 grants sufficient access to regress_user2. 192SET SESSION AUTHORIZATION regress_user1; 193GRANT SELECT (a, b) ON atest12 TO PUBLIC; 194SET SESSION AUTHORIZATION regress_user2; 195 196-- regress_user2 should continue to get a good row estimate. 197EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; 198 199-- But not for this, due to lack of table-wide permissions needed 200-- to make use of the expression index's statistics. 201EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y 202 WHERE x.a = y.b and abs(y.a) <<< 5; 203 204-- clean up (regress_user1's objects are all dropped later) 205DROP FUNCTION leak2(integer, integer) CASCADE; 206 207 208-- groups 209 210SET SESSION AUTHORIZATION regress_user3; 211CREATE TABLE atest3 (one int, two int, three int); 212GRANT DELETE ON atest3 TO GROUP regress_group2; 213 214SET SESSION AUTHORIZATION regress_user1; 215 216SELECT * FROM atest3; -- fail 217DELETE FROM atest3; -- ok 218 219BEGIN; 220RESET SESSION AUTHORIZATION; 221ALTER ROLE regress_user1 NOINHERIT; 222SET SESSION AUTHORIZATION regress_user1; 223DELETE FROM atest3; 224ROLLBACK; 225 226-- views 227 228SET SESSION AUTHORIZATION regress_user3; 229 230CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok 231/* The next *should* fail, but it's not implemented that way yet. */ 232CREATE VIEW atestv2 AS SELECT * FROM atest2; 233CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok 234/* Empty view is a corner case that failed in 9.2. */ 235CREATE VIEW atestv0 AS SELECT 0 as x WHERE false; -- ok 236 237SELECT * FROM atestv1; -- ok 238SELECT * FROM atestv2; -- fail 239GRANT SELECT ON atestv1, atestv3 TO regress_user4; 240GRANT SELECT ON atestv2 TO regress_user2; 241 242SET SESSION AUTHORIZATION regress_user4; 243 244SELECT * FROM atestv1; -- ok 245SELECT * FROM atestv2; -- fail 246SELECT * FROM atestv3; -- ok 247SELECT * FROM atestv0; -- fail 248 249-- Appendrels excluded by constraints failed to check permissions in 8.4-9.2. 250select * from 251 ((select a.q1 as x from int8_tbl a offset 0) 252 union all 253 (select b.q2 as x from int8_tbl b offset 0)) ss 254where false; 255 256set constraint_exclusion = on; 257select * from 258 ((select a.q1 as x, random() from int8_tbl a where q1 > 0) 259 union all 260 (select b.q2 as x, random() from int8_tbl b where q2 > 0)) ss 261where x < 0; 262reset constraint_exclusion; 263 264CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view 265SELECT * FROM atestv4; -- ok 266GRANT SELECT ON atestv4 TO regress_user2; 267 268SET SESSION AUTHORIZATION regress_user2; 269 270-- Two complex cases: 271 272SELECT * FROM atestv3; -- fail 273SELECT * FROM atestv4; -- ok (even though regress_user2 cannot access underlying atestv3) 274 275SELECT * FROM atest2; -- ok 276SELECT * FROM atestv2; -- fail (even though regress_user2 can access underlying atest2) 277 278-- Test column level permissions 279 280SET SESSION AUTHORIZATION regress_user1; 281CREATE TABLE atest5 (one int, two int unique, three int, four int unique); 282CREATE TABLE atest6 (one int, two int, blue int); 283GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regress_user4; 284GRANT ALL (one) ON atest5 TO regress_user3; 285 286INSERT INTO atest5 VALUES (1,2,3); 287 288SET SESSION AUTHORIZATION regress_user4; 289SELECT * FROM atest5; -- fail 290SELECT one FROM atest5; -- ok 291COPY atest5 (one) TO stdout; -- ok 292SELECT two FROM atest5; -- fail 293COPY atest5 (two) TO stdout; -- fail 294SELECT atest5 FROM atest5; -- fail 295COPY atest5 (one,two) TO stdout; -- fail 296SELECT 1 FROM atest5; -- ok 297SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok 298SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail 299SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail 300SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail 301SELECT 1 FROM atest5 WHERE two = 2; -- fail 302SELECT * FROM atest1, atest5; -- fail 303SELECT atest1.* FROM atest1, atest5; -- ok 304SELECT atest1.*,atest5.one FROM atest1, atest5; -- ok 305SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two); -- fail 306SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- ok 307SELECT one, two FROM atest5; -- fail 308 309SET SESSION AUTHORIZATION regress_user1; 310GRANT SELECT (one,two) ON atest6 TO regress_user4; 311 312SET SESSION AUTHORIZATION regress_user4; 313SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still 314 315SET SESSION AUTHORIZATION regress_user1; 316GRANT SELECT (two) ON atest5 TO regress_user4; 317 318SET SESSION AUTHORIZATION regress_user4; 319SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now 320 321-- test column-level privileges for INSERT and UPDATE 322INSERT INTO atest5 (two) VALUES (3); -- ok 323COPY atest5 FROM stdin; -- fail 324COPY atest5 (two) FROM stdin; -- ok 3251 326\. 327INSERT INTO atest5 (three) VALUES (4); -- fail 328INSERT INTO atest5 VALUES (5,5,5); -- fail 329UPDATE atest5 SET three = 10; -- ok 330UPDATE atest5 SET one = 8; -- fail 331UPDATE atest5 SET three = 5, one = 2; -- fail 332-- Check that column level privs are enforced in RETURNING 333-- Ok. 334INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10; 335-- Error. No SELECT on column three. 336INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three; 337-- Ok. May SELECT on column "one": 338INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one; 339-- Check that column level privileges are enforced for EXCLUDED 340-- Ok. we may select one 341INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one; 342-- Error. No select rights on three 343INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three; 344INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE) 345INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT) 346 347-- Check that the columns in the inference require select privileges 348INSERT INTO atest5(four) VALUES (4); -- fail 349 350SET SESSION AUTHORIZATION regress_user1; 351GRANT INSERT (four) ON atest5 TO regress_user4; 352SET SESSION AUTHORIZATION regress_user4; 353 354INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- fails (due to SELECT) 355INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- fails (due to SELECT) 356INSERT INTO atest5(four) VALUES (4); -- ok 357 358SET SESSION AUTHORIZATION regress_user1; 359GRANT SELECT (four) ON atest5 TO regress_user4; 360SET SESSION AUTHORIZATION regress_user4; 361 362INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- ok 363INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- ok 364 365SET SESSION AUTHORIZATION regress_user1; 366REVOKE ALL (one) ON atest5 FROM regress_user4; 367GRANT SELECT (one,two,blue) ON atest6 TO regress_user4; 368 369SET SESSION AUTHORIZATION regress_user4; 370SELECT one FROM atest5; -- fail 371UPDATE atest5 SET one = 1; -- fail 372SELECT atest6 FROM atest6; -- ok 373COPY atest6 TO stdout; -- ok 374 375-- check error reporting with column privs 376SET SESSION AUTHORIZATION regress_user1; 377CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2)); 378GRANT SELECT (c1) ON t1 TO regress_user2; 379GRANT INSERT (c1, c2, c3) ON t1 TO regress_user2; 380GRANT UPDATE (c1, c2, c3) ON t1 TO regress_user2; 381 382-- seed data 383INSERT INTO t1 VALUES (1, 1, 1); 384INSERT INTO t1 VALUES (1, 2, 1); 385INSERT INTO t1 VALUES (2, 1, 2); 386INSERT INTO t1 VALUES (2, 2, 2); 387INSERT INTO t1 VALUES (3, 1, 3); 388 389SET SESSION AUTHORIZATION regress_user2; 390INSERT INTO t1 (c1, c2) VALUES (1, 1); -- fail, but row not shown 391UPDATE t1 SET c2 = 1; -- fail, but row not shown 392INSERT INTO t1 (c1, c2) VALUES (null, null); -- fail, but see columns being inserted 393INSERT INTO t1 (c3) VALUES (null); -- fail, but see columns being inserted or have SELECT 394INSERT INTO t1 (c1) VALUES (5); -- fail, but see columns being inserted or have SELECT 395UPDATE t1 SET c3 = 10; -- fail, but see columns with SELECT rights, or being modified 396 397SET SESSION AUTHORIZATION regress_user1; 398DROP TABLE t1; 399 400-- test column-level privileges when involved with DELETE 401SET SESSION AUTHORIZATION regress_user1; 402ALTER TABLE atest6 ADD COLUMN three integer; 403GRANT DELETE ON atest5 TO regress_user3; 404GRANT SELECT (two) ON atest5 TO regress_user3; 405REVOKE ALL (one) ON atest5 FROM regress_user3; 406GRANT SELECT (one) ON atest5 TO regress_user4; 407 408SET SESSION AUTHORIZATION regress_user4; 409SELECT atest6 FROM atest6; -- fail 410SELECT one FROM atest5 NATURAL JOIN atest6; -- fail 411 412SET SESSION AUTHORIZATION regress_user1; 413ALTER TABLE atest6 DROP COLUMN three; 414 415SET SESSION AUTHORIZATION regress_user4; 416SELECT atest6 FROM atest6; -- ok 417SELECT one FROM atest5 NATURAL JOIN atest6; -- ok 418 419SET SESSION AUTHORIZATION regress_user1; 420ALTER TABLE atest6 DROP COLUMN two; 421REVOKE SELECT (one,blue) ON atest6 FROM regress_user4; 422 423SET SESSION AUTHORIZATION regress_user4; 424SELECT * FROM atest6; -- fail 425SELECT 1 FROM atest6; -- fail 426 427SET SESSION AUTHORIZATION regress_user3; 428DELETE FROM atest5 WHERE one = 1; -- fail 429DELETE FROM atest5 WHERE two = 2; -- ok 430 431-- check inheritance cases 432SET SESSION AUTHORIZATION regress_user1; 433CREATE TABLE atestp1 (f1 int, f2 int) WITH OIDS; 434CREATE TABLE atestp2 (fx int, fy int) WITH OIDS; 435CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2); 436GRANT SELECT(fx,fy,oid) ON atestp2 TO regress_user2; 437GRANT SELECT(fx) ON atestc TO regress_user2; 438 439SET SESSION AUTHORIZATION regress_user2; 440SELECT fx FROM atestp2; -- ok 441SELECT fy FROM atestp2; -- ok 442SELECT atestp2 FROM atestp2; -- ok 443SELECT oid FROM atestp2; -- ok 444SELECT fy FROM atestc; -- fail 445 446SET SESSION AUTHORIZATION regress_user1; 447GRANT SELECT(fy,oid) ON atestc TO regress_user2; 448 449SET SESSION AUTHORIZATION regress_user2; 450SELECT fx FROM atestp2; -- still ok 451SELECT fy FROM atestp2; -- ok 452SELECT atestp2 FROM atestp2; -- ok 453SELECT oid FROM atestp2; -- ok 454 455-- privileges on functions, languages 456 457-- switch to superuser 458\c - 459 460REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC; 461GRANT USAGE ON LANGUAGE sql TO regress_user1; -- ok 462GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail 463 464SET SESSION AUTHORIZATION regress_user1; 465GRANT USAGE ON LANGUAGE sql TO regress_user2; -- fail 466CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; 467CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 468 469REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC; 470GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regress_user2; 471GRANT USAGE ON FUNCTION testfunc1(int) TO regress_user3; -- semantic error 472GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regress_user4; 473GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regress_user4; 474 475CREATE FUNCTION testfunc4(boolean) RETURNS text 476 AS 'select col1 from atest2 where col2 = $1;' 477 LANGUAGE sql SECURITY DEFINER; 478GRANT EXECUTE ON FUNCTION testfunc4(boolean) TO regress_user3; 479 480SET SESSION AUTHORIZATION regress_user2; 481SELECT testfunc1(5), testfunc2(5); -- ok 482CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail 483 484SET SESSION AUTHORIZATION regress_user3; 485SELECT testfunc1(5); -- fail 486SELECT col1 FROM atest2 WHERE col2 = true; -- fail 487SELECT testfunc4(true); -- ok 488 489SET SESSION AUTHORIZATION regress_user4; 490SELECT testfunc1(5); -- ok 491 492DROP FUNCTION testfunc1(int); -- fail 493 494\c - 495 496DROP FUNCTION testfunc1(int); -- ok 497-- restore to sanity 498GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC; 499 500-- verify privilege checks on array-element coercions 501BEGIN; 502SELECT '{1}'::int4[]::int8[]; 503REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC; 504SELECT '{1}'::int4[]::int8[]; --superuser, suceed 505SET SESSION AUTHORIZATION regress_user4; 506SELECT '{1}'::int4[]::int8[]; --other user, fail 507ROLLBACK; 508 509-- privileges on types 510 511-- switch to superuser 512\c - 513 514CREATE TYPE testtype1 AS (a int, b text); 515REVOKE USAGE ON TYPE testtype1 FROM PUBLIC; 516GRANT USAGE ON TYPE testtype1 TO regress_user2; 517GRANT USAGE ON TYPE _testtype1 TO regress_user2; -- fail 518GRANT USAGE ON DOMAIN testtype1 TO regress_user2; -- fail 519 520CREATE DOMAIN testdomain1 AS int; 521REVOKE USAGE on DOMAIN testdomain1 FROM PUBLIC; 522GRANT USAGE ON DOMAIN testdomain1 TO regress_user2; 523GRANT USAGE ON TYPE testdomain1 TO regress_user2; -- ok 524 525SET SESSION AUTHORIZATION regress_user1; 526 527-- commands that should fail 528 529CREATE AGGREGATE testagg1a(testdomain1) (sfunc = int4_sum, stype = bigint); 530 531CREATE DOMAIN testdomain2a AS testdomain1; 532 533CREATE DOMAIN testdomain3a AS int; 534CREATE FUNCTION castfunc(int) RETURNS testdomain3a AS $$ SELECT $1::testdomain3a $$ LANGUAGE SQL; 535CREATE CAST (testdomain1 AS testdomain3a) WITH FUNCTION castfunc(int); 536DROP FUNCTION castfunc(int) CASCADE; 537DROP DOMAIN testdomain3a; 538 539CREATE FUNCTION testfunc5a(a testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 540CREATE FUNCTION testfunc6a(b int) RETURNS testdomain1 LANGUAGE SQL AS $$ SELECT $1::testdomain1 $$; 541 542CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = testdomain1, RIGHTARG = testdomain1); 543 544CREATE TABLE test5a (a int, b testdomain1); 545CREATE TABLE test6a OF testtype1; 546CREATE TABLE test10a (a int[], b testtype1[]); 547 548CREATE TABLE test9a (a int, b int); 549ALTER TABLE test9a ADD COLUMN c testdomain1; 550ALTER TABLE test9a ALTER COLUMN b TYPE testdomain1; 551 552CREATE TYPE test7a AS (a int, b testdomain1); 553 554CREATE TYPE test8a AS (a int, b int); 555ALTER TYPE test8a ADD ATTRIBUTE c testdomain1; 556ALTER TYPE test8a ALTER ATTRIBUTE b TYPE testdomain1; 557 558CREATE TABLE test11a AS (SELECT 1::testdomain1 AS a); 559 560REVOKE ALL ON TYPE testtype1 FROM PUBLIC; 561 562SET SESSION AUTHORIZATION regress_user2; 563 564-- commands that should succeed 565 566CREATE AGGREGATE testagg1b(testdomain1) (sfunc = int4_sum, stype = bigint); 567 568CREATE DOMAIN testdomain2b AS testdomain1; 569 570CREATE DOMAIN testdomain3b AS int; 571CREATE FUNCTION castfunc(int) RETURNS testdomain3b AS $$ SELECT $1::testdomain3b $$ LANGUAGE SQL; 572CREATE CAST (testdomain1 AS testdomain3b) WITH FUNCTION castfunc(int); 573 574CREATE FUNCTION testfunc5b(a testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 575CREATE FUNCTION testfunc6b(b int) RETURNS testdomain1 LANGUAGE SQL AS $$ SELECT $1::testdomain1 $$; 576 577CREATE OPERATOR !! (PROCEDURE = testfunc5b, RIGHTARG = testdomain1); 578 579CREATE TABLE test5b (a int, b testdomain1); 580CREATE TABLE test6b OF testtype1; 581CREATE TABLE test10b (a int[], b testtype1[]); 582 583CREATE TABLE test9b (a int, b int); 584ALTER TABLE test9b ADD COLUMN c testdomain1; 585ALTER TABLE test9b ALTER COLUMN b TYPE testdomain1; 586 587CREATE TYPE test7b AS (a int, b testdomain1); 588 589CREATE TYPE test8b AS (a int, b int); 590ALTER TYPE test8b ADD ATTRIBUTE c testdomain1; 591ALTER TYPE test8b ALTER ATTRIBUTE b TYPE testdomain1; 592 593CREATE TABLE test11b AS (SELECT 1::testdomain1 AS a); 594 595REVOKE ALL ON TYPE testtype1 FROM PUBLIC; 596 597\c - 598DROP AGGREGATE testagg1b(testdomain1); 599DROP DOMAIN testdomain2b; 600DROP OPERATOR !! (NONE, testdomain1); 601DROP FUNCTION testfunc5b(a testdomain1); 602DROP FUNCTION testfunc6b(b int); 603DROP TABLE test5b; 604DROP TABLE test6b; 605DROP TABLE test9b; 606DROP TABLE test10b; 607DROP TYPE test7b; 608DROP TYPE test8b; 609DROP CAST (testdomain1 AS testdomain3b); 610DROP FUNCTION castfunc(int) CASCADE; 611DROP DOMAIN testdomain3b; 612DROP TABLE test11b; 613 614DROP TYPE testtype1; -- ok 615DROP DOMAIN testdomain1; -- ok 616 617 618-- truncate 619SET SESSION AUTHORIZATION regress_user5; 620TRUNCATE atest2; -- ok 621TRUNCATE atest3; -- fail 622 623-- has_table_privilege function 624 625-- bad-input checks 626select has_table_privilege(NULL,'pg_authid','select'); 627select has_table_privilege('pg_shad','select'); 628select has_table_privilege('nosuchuser','pg_authid','select'); 629select has_table_privilege('pg_authid','sel'); 630select has_table_privilege(-999999,'pg_authid','update'); 631select has_table_privilege(1,'select'); 632 633-- superuser 634\c - 635 636select has_table_privilege(current_user,'pg_authid','select'); 637select has_table_privilege(current_user,'pg_authid','insert'); 638 639select has_table_privilege(t2.oid,'pg_authid','update') 640from (select oid from pg_roles where rolname = current_user) as t2; 641select has_table_privilege(t2.oid,'pg_authid','delete') 642from (select oid from pg_roles where rolname = current_user) as t2; 643 644-- 'rule' privilege no longer exists, but for backwards compatibility 645-- has_table_privilege still recognizes the keyword and says FALSE 646select has_table_privilege(current_user,t1.oid,'rule') 647from (select oid from pg_class where relname = 'pg_authid') as t1; 648select has_table_privilege(current_user,t1.oid,'references') 649from (select oid from pg_class where relname = 'pg_authid') as t1; 650 651select has_table_privilege(t2.oid,t1.oid,'select') 652from (select oid from pg_class where relname = 'pg_authid') as t1, 653 (select oid from pg_roles where rolname = current_user) as t2; 654select has_table_privilege(t2.oid,t1.oid,'insert') 655from (select oid from pg_class where relname = 'pg_authid') as t1, 656 (select oid from pg_roles where rolname = current_user) as t2; 657 658select has_table_privilege('pg_authid','update'); 659select has_table_privilege('pg_authid','delete'); 660select has_table_privilege('pg_authid','truncate'); 661 662select has_table_privilege(t1.oid,'select') 663from (select oid from pg_class where relname = 'pg_authid') as t1; 664select has_table_privilege(t1.oid,'trigger') 665from (select oid from pg_class where relname = 'pg_authid') as t1; 666 667-- non-superuser 668SET SESSION AUTHORIZATION regress_user3; 669 670select has_table_privilege(current_user,'pg_class','select'); 671select has_table_privilege(current_user,'pg_class','insert'); 672 673select has_table_privilege(t2.oid,'pg_class','update') 674from (select oid from pg_roles where rolname = current_user) as t2; 675select has_table_privilege(t2.oid,'pg_class','delete') 676from (select oid from pg_roles where rolname = current_user) as t2; 677 678select has_table_privilege(current_user,t1.oid,'references') 679from (select oid from pg_class where relname = 'pg_class') as t1; 680 681select has_table_privilege(t2.oid,t1.oid,'select') 682from (select oid from pg_class where relname = 'pg_class') as t1, 683 (select oid from pg_roles where rolname = current_user) as t2; 684select has_table_privilege(t2.oid,t1.oid,'insert') 685from (select oid from pg_class where relname = 'pg_class') as t1, 686 (select oid from pg_roles where rolname = current_user) as t2; 687 688select has_table_privilege('pg_class','update'); 689select has_table_privilege('pg_class','delete'); 690select has_table_privilege('pg_class','truncate'); 691 692select has_table_privilege(t1.oid,'select') 693from (select oid from pg_class where relname = 'pg_class') as t1; 694select has_table_privilege(t1.oid,'trigger') 695from (select oid from pg_class where relname = 'pg_class') as t1; 696 697select has_table_privilege(current_user,'atest1','select'); 698select has_table_privilege(current_user,'atest1','insert'); 699 700select has_table_privilege(t2.oid,'atest1','update') 701from (select oid from pg_roles where rolname = current_user) as t2; 702select has_table_privilege(t2.oid,'atest1','delete') 703from (select oid from pg_roles where rolname = current_user) as t2; 704 705select has_table_privilege(current_user,t1.oid,'references') 706from (select oid from pg_class where relname = 'atest1') as t1; 707 708select has_table_privilege(t2.oid,t1.oid,'select') 709from (select oid from pg_class where relname = 'atest1') as t1, 710 (select oid from pg_roles where rolname = current_user) as t2; 711select has_table_privilege(t2.oid,t1.oid,'insert') 712from (select oid from pg_class where relname = 'atest1') as t1, 713 (select oid from pg_roles where rolname = current_user) as t2; 714 715select has_table_privilege('atest1','update'); 716select has_table_privilege('atest1','delete'); 717select has_table_privilege('atest1','truncate'); 718 719select has_table_privilege(t1.oid,'select') 720from (select oid from pg_class where relname = 'atest1') as t1; 721select has_table_privilege(t1.oid,'trigger') 722from (select oid from pg_class where relname = 'atest1') as t1; 723 724-- has_column_privilege function 725 726-- bad-input checks (as non-super-user) 727select has_column_privilege('pg_authid',NULL,'select'); 728select has_column_privilege('pg_authid','nosuchcol','select'); 729select has_column_privilege(9999,'nosuchcol','select'); 730select has_column_privilege(9999,99::int2,'select'); 731select has_column_privilege('pg_authid',99::int2,'select'); 732select has_column_privilege(9999,99::int2,'select'); 733 734create temp table mytable(f1 int, f2 int, f3 int); 735alter table mytable drop column f2; 736select has_column_privilege('mytable','f2','select'); 737select has_column_privilege('mytable','........pg.dropped.2........','select'); 738select has_column_privilege('mytable',2::int2,'select'); 739revoke select on table mytable from regress_user3; 740select has_column_privilege('mytable',2::int2,'select'); 741drop table mytable; 742 743-- Grant options 744 745SET SESSION AUTHORIZATION regress_user1; 746 747CREATE TABLE atest4 (a int); 748 749GRANT SELECT ON atest4 TO regress_user2 WITH GRANT OPTION; 750GRANT UPDATE ON atest4 TO regress_user2; 751GRANT SELECT ON atest4 TO GROUP regress_group1 WITH GRANT OPTION; 752 753SET SESSION AUTHORIZATION regress_user2; 754 755GRANT SELECT ON atest4 TO regress_user3; 756GRANT UPDATE ON atest4 TO regress_user3; -- fail 757 758SET SESSION AUTHORIZATION regress_user1; 759 760REVOKE SELECT ON atest4 FROM regress_user3; -- does nothing 761SELECT has_table_privilege('regress_user3', 'atest4', 'SELECT'); -- true 762REVOKE SELECT ON atest4 FROM regress_user2; -- fail 763REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_user2 CASCADE; -- ok 764SELECT has_table_privilege('regress_user2', 'atest4', 'SELECT'); -- true 765SELECT has_table_privilege('regress_user3', 'atest4', 'SELECT'); -- false 766 767SELECT has_table_privilege('regress_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true 768 769 770-- security-restricted operations 771\c - 772CREATE ROLE regress_sro_user; 773 774SET SESSION AUTHORIZATION regress_sro_user; 775CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS 776 'GRANT regress_group2 TO regress_sro_user'; 777CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 778 'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant(); SELECT true'; 779-- REFRESH of this MV will queue a GRANT at end of transaction 780CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA; 781REFRESH MATERIALIZED VIEW sro_mv; 782\c - 783REFRESH MATERIALIZED VIEW sro_mv; 784 785SET SESSION AUTHORIZATION regress_sro_user; 786-- INSERT to this table will queue a GRANT at end of transaction 787CREATE TABLE sro_trojan_table (); 788CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS 789 'BEGIN PERFORM unwanted_grant(); RETURN NULL; END'; 790CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table 791 INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan(); 792-- Now, REFRESH will issue such an INSERT, queueing the GRANT 793CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 794 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; 795REFRESH MATERIALIZED VIEW sro_mv; 796\c - 797REFRESH MATERIALIZED VIEW sro_mv; 798BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; 799 800DROP OWNED BY regress_sro_user; 801DROP ROLE regress_sro_user; 802 803 804-- Admin options 805 806SET SESSION AUTHORIZATION regress_user4; 807CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS 808 'GRANT regress_group2 TO regress_user5'; 809GRANT regress_group2 TO regress_user5; -- ok: had ADMIN OPTION 810SET ROLE regress_group2; 811GRANT regress_group2 TO regress_user5; -- fails: SET ROLE suspended privilege 812 813SET SESSION AUTHORIZATION regress_user1; 814GRANT regress_group2 TO regress_user5; -- fails: no ADMIN OPTION 815SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN 816SET ROLE regress_group2; 817GRANT regress_group2 TO regress_user5; -- fails: SET ROLE did not help 818 819SET SESSION AUTHORIZATION regress_group2; 820GRANT regress_group2 TO regress_user5; -- ok: a role can self-admin 821CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS 822 'GRANT regress_group2 TO regress_user5'; 823SELECT dogrant_fails(); -- fails: no self-admin in SECURITY DEFINER 824DROP FUNCTION dogrant_fails(); 825 826SET SESSION AUTHORIZATION regress_user4; 827DROP FUNCTION dogrant_ok(); 828REVOKE regress_group2 FROM regress_user5; 829 830 831-- has_sequence_privilege tests 832\c - 833 834CREATE SEQUENCE x_seq; 835 836GRANT USAGE on x_seq to regress_user2; 837 838SELECT has_sequence_privilege('regress_user1', 'atest1', 'SELECT'); 839SELECT has_sequence_privilege('regress_user1', 'x_seq', 'INSERT'); 840SELECT has_sequence_privilege('regress_user1', 'x_seq', 'SELECT'); 841 842SET SESSION AUTHORIZATION regress_user2; 843 844SELECT has_sequence_privilege('x_seq', 'USAGE'); 845 846-- largeobject privilege tests 847\c - 848SET SESSION AUTHORIZATION regress_user1; 849 850SELECT lo_create(1001); 851SELECT lo_create(1002); 852SELECT lo_create(1003); 853SELECT lo_create(1004); 854SELECT lo_create(1005); 855 856GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC; 857GRANT SELECT ON LARGE OBJECT 1003 TO regress_user2; 858GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_user2; 859GRANT ALL ON LARGE OBJECT 1005 TO regress_user2; 860GRANT SELECT ON LARGE OBJECT 1005 TO regress_user2 WITH GRANT OPTION; 861 862GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC; -- to be failed 863GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser; -- to be failed 864GRANT SELECT, UPDATE ON LARGE OBJECT 999 TO PUBLIC; -- to be failed 865 866\c - 867SET SESSION AUTHORIZATION regress_user2; 868 869SELECT lo_create(2001); 870SELECT lo_create(2002); 871 872SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now 873SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode 874 875SELECT loread(lo_open(1001, x'40000'::int), 32); 876SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 877SELECT loread(lo_open(1003, x'40000'::int), 32); 878SELECT loread(lo_open(1004, x'40000'::int), 32); 879 880SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd'); 881SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 882SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied 883SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd'); 884 885GRANT SELECT ON LARGE OBJECT 1005 TO regress_user3; 886GRANT UPDATE ON LARGE OBJECT 1006 TO regress_user3; -- to be denied 887REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC; 888GRANT ALL ON LARGE OBJECT 2001 TO regress_user3; 889 890SELECT lo_unlink(1001); -- to be denied 891SELECT lo_unlink(2002); 892 893\c - 894-- confirm ACL setting 895SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 896 897SET SESSION AUTHORIZATION regress_user3; 898 899SELECT loread(lo_open(1001, x'40000'::int), 32); 900SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied 901SELECT loread(lo_open(1005, x'40000'::int), 32); 902 903SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied 904SELECT lo_truncate(lo_open(2001, x'20000'::int), 10); 905 906-- compatibility mode in largeobject permission 907\c - 908SET lo_compat_privileges = false; -- default setting 909SET SESSION AUTHORIZATION regress_user4; 910 911SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 912SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 913SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied 914SELECT lo_put(1002, 1, 'abcd'); -- to be denied 915SELECT lo_unlink(1002); -- to be denied 916SELECT lo_export(1001, '/dev/null'); -- to be denied 917 918\c - 919SET lo_compat_privileges = true; -- compatibility mode 920SET SESSION AUTHORIZATION regress_user4; 921 922SELECT loread(lo_open(1002, x'40000'::int), 32); 923SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); 924SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); 925SELECT lo_unlink(1002); 926SELECT lo_export(1001, '/dev/null'); -- to be denied 927 928-- don't allow unpriv users to access pg_largeobject contents 929\c - 930SELECT * FROM pg_largeobject LIMIT 0; 931 932SET SESSION AUTHORIZATION regress_user1; 933SELECT * FROM pg_largeobject LIMIT 0; -- to be denied 934 935-- test default ACLs 936\c - 937 938CREATE SCHEMA testns; 939GRANT ALL ON SCHEMA testns TO regress_user1; 940 941CREATE TABLE testns.acltest1 (x int); 942SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- no 943SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 944 945-- placeholder for test with duplicated schema and role names 946ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public; 947 948SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- no 949SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 950 951DROP TABLE testns.acltest1; 952CREATE TABLE testns.acltest1 (x int); 953 954SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- yes 955SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 956 957ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_user1; 958 959DROP TABLE testns.acltest1; 960CREATE TABLE testns.acltest1 (x int); 961 962SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- yes 963SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- yes 964 965ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_user1; 966 967DROP TABLE testns.acltest1; 968CREATE TABLE testns.acltest1 (x int); 969 970SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- yes 971SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 972 973ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE EXECUTE ON FUNCTIONS FROM public; 974 975ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_user2; -- error 976 977-- 978-- Testing blanket default grants is very hazardous since it might change 979-- the privileges attached to objects created by concurrent regression tests. 980-- To avoid that, be sure to revoke the privileges again before committing. 981-- 982BEGIN; 983 984ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_user2; 985 986CREATE SCHEMA testns2; 987 988SELECT has_schema_privilege('regress_user2', 'testns2', 'USAGE'); -- yes 989SELECT has_schema_privilege('regress_user2', 'testns2', 'CREATE'); -- no 990 991ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_user2; 992 993CREATE SCHEMA testns3; 994 995SELECT has_schema_privilege('regress_user2', 'testns3', 'USAGE'); -- no 996SELECT has_schema_privilege('regress_user2', 'testns3', 'CREATE'); -- no 997 998ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_user2; 999 1000CREATE SCHEMA testns4; 1001 1002SELECT has_schema_privilege('regress_user2', 'testns4', 'USAGE'); -- yes 1003SELECT has_schema_privilege('regress_user2', 'testns4', 'CREATE'); -- yes 1004 1005ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_user2; 1006 1007COMMIT; 1008 1009CREATE SCHEMA testns5; 1010 1011SELECT has_schema_privilege('regress_user2', 'testns5', 'USAGE'); -- no 1012SELECT has_schema_privilege('regress_user2', 'testns5', 'CREATE'); -- no 1013 1014SET ROLE regress_user1; 1015 1016CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1017 1018SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no 1019 1020ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON FUNCTIONS to public; 1021 1022DROP FUNCTION testns.foo(); 1023CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1024 1025SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- yes 1026 1027DROP FUNCTION testns.foo(); 1028 1029ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE USAGE ON TYPES FROM public; 1030 1031CREATE DOMAIN testns.testdomain1 AS int; 1032 1033SELECT has_type_privilege('regress_user2', 'testns.testdomain1', 'USAGE'); -- no 1034 1035ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public; 1036 1037DROP DOMAIN testns.testdomain1; 1038CREATE DOMAIN testns.testdomain1 AS int; 1039 1040SELECT has_type_privilege('regress_user2', 'testns.testdomain1', 'USAGE'); -- yes 1041 1042DROP DOMAIN testns.testdomain1; 1043 1044RESET ROLE; 1045 1046SELECT count(*) 1047 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1048 WHERE nspname = 'testns'; 1049 1050DROP SCHEMA testns CASCADE; 1051DROP SCHEMA testns2 CASCADE; 1052DROP SCHEMA testns3 CASCADE; 1053DROP SCHEMA testns4 CASCADE; 1054DROP SCHEMA testns5 CASCADE; 1055 1056SELECT d.* -- check that entries went away 1057 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1058 WHERE nspname IS NULL AND defaclnamespace != 0; 1059 1060 1061-- Grant on all objects of given type in a schema 1062\c - 1063 1064CREATE SCHEMA testns; 1065CREATE TABLE testns.t1 (f1 int); 1066CREATE TABLE testns.t2 (f1 int); 1067 1068SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- false 1069 1070GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_user1; 1071 1072SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- true 1073SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- true 1074 1075REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_user1; 1076 1077SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- false 1078SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- false 1079 1080CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 1081 1082SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default 1083 1084REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; 1085 1086SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- false 1087 1088\set VERBOSITY terse \\ -- suppress cascade details 1089DROP SCHEMA testns CASCADE; 1090\set VERBOSITY default 1091 1092 1093-- Change owner of the schema & and rename of new schema owner 1094\c - 1095 1096CREATE ROLE regress_schemauser1 superuser login; 1097CREATE ROLE regress_schemauser2 superuser login; 1098 1099SET SESSION ROLE regress_schemauser1; 1100CREATE SCHEMA testns; 1101 1102SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1103 1104ALTER SCHEMA testns OWNER TO regress_schemauser2; 1105ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed; 1106SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1107 1108set session role regress_schemauser_renamed; 1109\set VERBOSITY terse \\ -- suppress cascade details 1110DROP SCHEMA testns CASCADE; 1111\set VERBOSITY default 1112 1113-- clean up 1114\c - 1115 1116DROP ROLE regress_schemauser1; 1117DROP ROLE regress_schemauser_renamed; 1118 1119 1120-- test that dependent privileges are revoked (or not) properly 1121\c - 1122 1123set session role regress_user1; 1124create table dep_priv_test (a int); 1125grant select on dep_priv_test to regress_user2 with grant option; 1126grant select on dep_priv_test to regress_user3 with grant option; 1127set session role regress_user2; 1128grant select on dep_priv_test to regress_user4 with grant option; 1129set session role regress_user3; 1130grant select on dep_priv_test to regress_user4 with grant option; 1131set session role regress_user4; 1132grant select on dep_priv_test to regress_user5; 1133\dp dep_priv_test 1134set session role regress_user2; 1135revoke select on dep_priv_test from regress_user4 cascade; 1136\dp dep_priv_test 1137set session role regress_user3; 1138revoke select on dep_priv_test from regress_user4 cascade; 1139\dp dep_priv_test 1140set session role regress_user1; 1141drop table dep_priv_test; 1142 1143 1144-- clean up 1145 1146\c 1147 1148drop sequence x_seq; 1149 1150DROP FUNCTION testfunc2(int); 1151DROP FUNCTION testfunc4(boolean); 1152 1153DROP VIEW atestv0; 1154DROP VIEW atestv1; 1155DROP VIEW atestv2; 1156-- this should cascade to drop atestv4 1157DROP VIEW atestv3 CASCADE; 1158-- this should complain "does not exist" 1159DROP VIEW atestv4; 1160 1161DROP TABLE atest1; 1162DROP TABLE atest2; 1163DROP TABLE atest3; 1164DROP TABLE atest4; 1165DROP TABLE atest5; 1166DROP TABLE atest6; 1167DROP TABLE atestc; 1168DROP TABLE atestp1; 1169DROP TABLE atestp2; 1170 1171SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 1172 1173DROP GROUP regress_group1; 1174DROP GROUP regress_group2; 1175 1176-- these are needed to clean up permissions 1177REVOKE USAGE ON LANGUAGE sql FROM regress_user1; 1178DROP OWNED BY regress_user1; 1179 1180DROP USER regress_user1; 1181DROP USER regress_user2; 1182DROP USER regress_user3; 1183DROP USER regress_user4; 1184DROP USER regress_user5; 1185DROP USER regress_user6; 1186 1187 1188-- permissions with LOCK TABLE 1189CREATE USER regress_locktable_user; 1190CREATE TABLE lock_table (a int); 1191 1192-- LOCK TABLE and SELECT permission 1193GRANT SELECT ON lock_table TO regress_locktable_user; 1194SET SESSION AUTHORIZATION regress_locktable_user; 1195BEGIN; 1196LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail 1197ROLLBACK; 1198BEGIN; 1199LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass 1200COMMIT; 1201BEGIN; 1202LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 1203ROLLBACK; 1204\c 1205REVOKE SELECT ON lock_table FROM regress_locktable_user; 1206 1207-- LOCK TABLE and INSERT permission 1208GRANT INSERT ON lock_table TO regress_locktable_user; 1209SET SESSION AUTHORIZATION regress_locktable_user; 1210BEGIN; 1211LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1212COMMIT; 1213BEGIN; 1214LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1215ROLLBACK; 1216BEGIN; 1217LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 1218ROLLBACK; 1219\c 1220REVOKE INSERT ON lock_table FROM regress_locktable_user; 1221 1222-- LOCK TABLE and UPDATE permission 1223GRANT UPDATE ON lock_table TO regress_locktable_user; 1224SET SESSION AUTHORIZATION regress_locktable_user; 1225BEGIN; 1226LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1227COMMIT; 1228BEGIN; 1229LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1230ROLLBACK; 1231BEGIN; 1232LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1233COMMIT; 1234\c 1235REVOKE UPDATE ON lock_table FROM regress_locktable_user; 1236 1237-- LOCK TABLE and DELETE permission 1238GRANT DELETE ON lock_table TO regress_locktable_user; 1239SET SESSION AUTHORIZATION regress_locktable_user; 1240BEGIN; 1241LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1242COMMIT; 1243BEGIN; 1244LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1245ROLLBACK; 1246BEGIN; 1247LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1248COMMIT; 1249\c 1250REVOKE DELETE ON lock_table FROM regress_locktable_user; 1251 1252-- LOCK TABLE and TRUNCATE permission 1253GRANT TRUNCATE ON lock_table TO regress_locktable_user; 1254SET SESSION AUTHORIZATION regress_locktable_user; 1255BEGIN; 1256LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1257COMMIT; 1258BEGIN; 1259LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1260ROLLBACK; 1261BEGIN; 1262LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1263COMMIT; 1264\c 1265REVOKE TRUNCATE ON lock_table FROM regress_locktable_user; 1266 1267-- clean up 1268DROP TABLE lock_table; 1269DROP USER regress_locktable_user; 1270