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-- privileges on types 501 502-- switch to superuser 503\c - 504 505CREATE TYPE testtype1 AS (a int, b text); 506REVOKE USAGE ON TYPE testtype1 FROM PUBLIC; 507GRANT USAGE ON TYPE testtype1 TO regress_user2; 508GRANT USAGE ON TYPE _testtype1 TO regress_user2; -- fail 509GRANT USAGE ON DOMAIN testtype1 TO regress_user2; -- fail 510 511CREATE DOMAIN testdomain1 AS int; 512REVOKE USAGE on DOMAIN testdomain1 FROM PUBLIC; 513GRANT USAGE ON DOMAIN testdomain1 TO regress_user2; 514GRANT USAGE ON TYPE testdomain1 TO regress_user2; -- ok 515 516SET SESSION AUTHORIZATION regress_user1; 517 518-- commands that should fail 519 520CREATE AGGREGATE testagg1a(testdomain1) (sfunc = int4_sum, stype = bigint); 521 522CREATE DOMAIN testdomain2a AS testdomain1; 523 524CREATE DOMAIN testdomain3a AS int; 525CREATE FUNCTION castfunc(int) RETURNS testdomain3a AS $$ SELECT $1::testdomain3a $$ LANGUAGE SQL; 526CREATE CAST (testdomain1 AS testdomain3a) WITH FUNCTION castfunc(int); 527DROP FUNCTION castfunc(int) CASCADE; 528DROP DOMAIN testdomain3a; 529 530CREATE FUNCTION testfunc5a(a testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 531CREATE FUNCTION testfunc6a(b int) RETURNS testdomain1 LANGUAGE SQL AS $$ SELECT $1::testdomain1 $$; 532 533CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = testdomain1, RIGHTARG = testdomain1); 534 535CREATE TABLE test5a (a int, b testdomain1); 536CREATE TABLE test6a OF testtype1; 537CREATE TABLE test10a (a int[], b testtype1[]); 538 539CREATE TABLE test9a (a int, b int); 540ALTER TABLE test9a ADD COLUMN c testdomain1; 541ALTER TABLE test9a ALTER COLUMN b TYPE testdomain1; 542 543CREATE TYPE test7a AS (a int, b testdomain1); 544 545CREATE TYPE test8a AS (a int, b int); 546ALTER TYPE test8a ADD ATTRIBUTE c testdomain1; 547ALTER TYPE test8a ALTER ATTRIBUTE b TYPE testdomain1; 548 549CREATE TABLE test11a AS (SELECT 1::testdomain1 AS a); 550 551REVOKE ALL ON TYPE testtype1 FROM PUBLIC; 552 553SET SESSION AUTHORIZATION regress_user2; 554 555-- commands that should succeed 556 557CREATE AGGREGATE testagg1b(testdomain1) (sfunc = int4_sum, stype = bigint); 558 559CREATE DOMAIN testdomain2b AS testdomain1; 560 561CREATE DOMAIN testdomain3b AS int; 562CREATE FUNCTION castfunc(int) RETURNS testdomain3b AS $$ SELECT $1::testdomain3b $$ LANGUAGE SQL; 563CREATE CAST (testdomain1 AS testdomain3b) WITH FUNCTION castfunc(int); 564 565CREATE FUNCTION testfunc5b(a testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 566CREATE FUNCTION testfunc6b(b int) RETURNS testdomain1 LANGUAGE SQL AS $$ SELECT $1::testdomain1 $$; 567 568CREATE OPERATOR !! (PROCEDURE = testfunc5b, RIGHTARG = testdomain1); 569 570CREATE TABLE test5b (a int, b testdomain1); 571CREATE TABLE test6b OF testtype1; 572CREATE TABLE test10b (a int[], b testtype1[]); 573 574CREATE TABLE test9b (a int, b int); 575ALTER TABLE test9b ADD COLUMN c testdomain1; 576ALTER TABLE test9b ALTER COLUMN b TYPE testdomain1; 577 578CREATE TYPE test7b AS (a int, b testdomain1); 579 580CREATE TYPE test8b AS (a int, b int); 581ALTER TYPE test8b ADD ATTRIBUTE c testdomain1; 582ALTER TYPE test8b ALTER ATTRIBUTE b TYPE testdomain1; 583 584CREATE TABLE test11b AS (SELECT 1::testdomain1 AS a); 585 586REVOKE ALL ON TYPE testtype1 FROM PUBLIC; 587 588\c - 589DROP AGGREGATE testagg1b(testdomain1); 590DROP DOMAIN testdomain2b; 591DROP OPERATOR !! (NONE, testdomain1); 592DROP FUNCTION testfunc5b(a testdomain1); 593DROP FUNCTION testfunc6b(b int); 594DROP TABLE test5b; 595DROP TABLE test6b; 596DROP TABLE test9b; 597DROP TABLE test10b; 598DROP TYPE test7b; 599DROP TYPE test8b; 600DROP CAST (testdomain1 AS testdomain3b); 601DROP FUNCTION castfunc(int) CASCADE; 602DROP DOMAIN testdomain3b; 603DROP TABLE test11b; 604 605DROP TYPE testtype1; -- ok 606DROP DOMAIN testdomain1; -- ok 607 608 609-- truncate 610SET SESSION AUTHORIZATION regress_user5; 611TRUNCATE atest2; -- ok 612TRUNCATE atest3; -- fail 613 614-- has_table_privilege function 615 616-- bad-input checks 617select has_table_privilege(NULL,'pg_authid','select'); 618select has_table_privilege('pg_shad','select'); 619select has_table_privilege('nosuchuser','pg_authid','select'); 620select has_table_privilege('pg_authid','sel'); 621select has_table_privilege(-999999,'pg_authid','update'); 622select has_table_privilege(1,'select'); 623 624-- superuser 625\c - 626 627select has_table_privilege(current_user,'pg_authid','select'); 628select has_table_privilege(current_user,'pg_authid','insert'); 629 630select has_table_privilege(t2.oid,'pg_authid','update') 631from (select oid from pg_roles where rolname = current_user) as t2; 632select has_table_privilege(t2.oid,'pg_authid','delete') 633from (select oid from pg_roles where rolname = current_user) as t2; 634 635-- 'rule' privilege no longer exists, but for backwards compatibility 636-- has_table_privilege still recognizes the keyword and says FALSE 637select has_table_privilege(current_user,t1.oid,'rule') 638from (select oid from pg_class where relname = 'pg_authid') as t1; 639select has_table_privilege(current_user,t1.oid,'references') 640from (select oid from pg_class where relname = 'pg_authid') as t1; 641 642select has_table_privilege(t2.oid,t1.oid,'select') 643from (select oid from pg_class where relname = 'pg_authid') as t1, 644 (select oid from pg_roles where rolname = current_user) as t2; 645select has_table_privilege(t2.oid,t1.oid,'insert') 646from (select oid from pg_class where relname = 'pg_authid') as t1, 647 (select oid from pg_roles where rolname = current_user) as t2; 648 649select has_table_privilege('pg_authid','update'); 650select has_table_privilege('pg_authid','delete'); 651select has_table_privilege('pg_authid','truncate'); 652 653select has_table_privilege(t1.oid,'select') 654from (select oid from pg_class where relname = 'pg_authid') as t1; 655select has_table_privilege(t1.oid,'trigger') 656from (select oid from pg_class where relname = 'pg_authid') as t1; 657 658-- non-superuser 659SET SESSION AUTHORIZATION regress_user3; 660 661select has_table_privilege(current_user,'pg_class','select'); 662select has_table_privilege(current_user,'pg_class','insert'); 663 664select has_table_privilege(t2.oid,'pg_class','update') 665from (select oid from pg_roles where rolname = current_user) as t2; 666select has_table_privilege(t2.oid,'pg_class','delete') 667from (select oid from pg_roles where rolname = current_user) as t2; 668 669select has_table_privilege(current_user,t1.oid,'references') 670from (select oid from pg_class where relname = 'pg_class') as t1; 671 672select has_table_privilege(t2.oid,t1.oid,'select') 673from (select oid from pg_class where relname = 'pg_class') as t1, 674 (select oid from pg_roles where rolname = current_user) as t2; 675select has_table_privilege(t2.oid,t1.oid,'insert') 676from (select oid from pg_class where relname = 'pg_class') as t1, 677 (select oid from pg_roles where rolname = current_user) as t2; 678 679select has_table_privilege('pg_class','update'); 680select has_table_privilege('pg_class','delete'); 681select has_table_privilege('pg_class','truncate'); 682 683select has_table_privilege(t1.oid,'select') 684from (select oid from pg_class where relname = 'pg_class') as t1; 685select has_table_privilege(t1.oid,'trigger') 686from (select oid from pg_class where relname = 'pg_class') as t1; 687 688select has_table_privilege(current_user,'atest1','select'); 689select has_table_privilege(current_user,'atest1','insert'); 690 691select has_table_privilege(t2.oid,'atest1','update') 692from (select oid from pg_roles where rolname = current_user) as t2; 693select has_table_privilege(t2.oid,'atest1','delete') 694from (select oid from pg_roles where rolname = current_user) as t2; 695 696select has_table_privilege(current_user,t1.oid,'references') 697from (select oid from pg_class where relname = 'atest1') as t1; 698 699select has_table_privilege(t2.oid,t1.oid,'select') 700from (select oid from pg_class where relname = 'atest1') as t1, 701 (select oid from pg_roles where rolname = current_user) as t2; 702select has_table_privilege(t2.oid,t1.oid,'insert') 703from (select oid from pg_class where relname = 'atest1') as t1, 704 (select oid from pg_roles where rolname = current_user) as t2; 705 706select has_table_privilege('atest1','update'); 707select has_table_privilege('atest1','delete'); 708select has_table_privilege('atest1','truncate'); 709 710select has_table_privilege(t1.oid,'select') 711from (select oid from pg_class where relname = 'atest1') as t1; 712select has_table_privilege(t1.oid,'trigger') 713from (select oid from pg_class where relname = 'atest1') as t1; 714 715-- has_column_privilege function 716 717-- bad-input checks (as non-super-user) 718select has_column_privilege('pg_authid',NULL,'select'); 719select has_column_privilege('pg_authid','nosuchcol','select'); 720select has_column_privilege(9999,'nosuchcol','select'); 721select has_column_privilege(9999,99::int2,'select'); 722select has_column_privilege('pg_authid',99::int2,'select'); 723select has_column_privilege(9999,99::int2,'select'); 724 725create temp table mytable(f1 int, f2 int, f3 int); 726alter table mytable drop column f2; 727select has_column_privilege('mytable','f2','select'); 728select has_column_privilege('mytable','........pg.dropped.2........','select'); 729select has_column_privilege('mytable',2::int2,'select'); 730revoke select on table mytable from regress_user3; 731select has_column_privilege('mytable',2::int2,'select'); 732drop table mytable; 733 734-- Grant options 735 736SET SESSION AUTHORIZATION regress_user1; 737 738CREATE TABLE atest4 (a int); 739 740GRANT SELECT ON atest4 TO regress_user2 WITH GRANT OPTION; 741GRANT UPDATE ON atest4 TO regress_user2; 742GRANT SELECT ON atest4 TO GROUP regress_group1 WITH GRANT OPTION; 743 744SET SESSION AUTHORIZATION regress_user2; 745 746GRANT SELECT ON atest4 TO regress_user3; 747GRANT UPDATE ON atest4 TO regress_user3; -- fail 748 749SET SESSION AUTHORIZATION regress_user1; 750 751REVOKE SELECT ON atest4 FROM regress_user3; -- does nothing 752SELECT has_table_privilege('regress_user3', 'atest4', 'SELECT'); -- true 753REVOKE SELECT ON atest4 FROM regress_user2; -- fail 754REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_user2 CASCADE; -- ok 755SELECT has_table_privilege('regress_user2', 'atest4', 'SELECT'); -- true 756SELECT has_table_privilege('regress_user3', 'atest4', 'SELECT'); -- false 757 758SELECT has_table_privilege('regress_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true 759 760 761-- security-restricted operations 762\c - 763CREATE ROLE regress_sro_user; 764 765SET SESSION AUTHORIZATION regress_sro_user; 766CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS 767 'GRANT regress_group2 TO regress_sro_user'; 768CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 769 'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant(); SELECT true'; 770-- REFRESH of this MV will queue a GRANT at end of transaction 771CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA; 772REFRESH MATERIALIZED VIEW sro_mv; 773\c - 774REFRESH MATERIALIZED VIEW sro_mv; 775 776SET SESSION AUTHORIZATION regress_sro_user; 777-- INSERT to this table will queue a GRANT at end of transaction 778CREATE TABLE sro_trojan_table (); 779CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS 780 'BEGIN PERFORM unwanted_grant(); RETURN NULL; END'; 781CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table 782 INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan(); 783-- Now, REFRESH will issue such an INSERT, queueing the GRANT 784CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 785 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; 786REFRESH MATERIALIZED VIEW sro_mv; 787\c - 788REFRESH MATERIALIZED VIEW sro_mv; 789BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; 790 791DROP OWNED BY regress_sro_user; 792DROP ROLE regress_sro_user; 793 794 795-- Admin options 796 797SET SESSION AUTHORIZATION regress_user4; 798CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS 799 'GRANT regress_group2 TO regress_user5'; 800GRANT regress_group2 TO regress_user5; -- ok: had ADMIN OPTION 801SET ROLE regress_group2; 802GRANT regress_group2 TO regress_user5; -- fails: SET ROLE suspended privilege 803 804SET SESSION AUTHORIZATION regress_user1; 805GRANT regress_group2 TO regress_user5; -- fails: no ADMIN OPTION 806SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN 807SET ROLE regress_group2; 808GRANT regress_group2 TO regress_user5; -- fails: SET ROLE did not help 809 810SET SESSION AUTHORIZATION regress_group2; 811GRANT regress_group2 TO regress_user5; -- ok: a role can self-admin 812CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS 813 'GRANT regress_group2 TO regress_user5'; 814SELECT dogrant_fails(); -- fails: no self-admin in SECURITY DEFINER 815DROP FUNCTION dogrant_fails(); 816 817SET SESSION AUTHORIZATION regress_user4; 818DROP FUNCTION dogrant_ok(); 819REVOKE regress_group2 FROM regress_user5; 820 821 822-- has_sequence_privilege tests 823\c - 824 825CREATE SEQUENCE x_seq; 826 827GRANT USAGE on x_seq to regress_user2; 828 829SELECT has_sequence_privilege('regress_user1', 'atest1', 'SELECT'); 830SELECT has_sequence_privilege('regress_user1', 'x_seq', 'INSERT'); 831SELECT has_sequence_privilege('regress_user1', 'x_seq', 'SELECT'); 832 833SET SESSION AUTHORIZATION regress_user2; 834 835SELECT has_sequence_privilege('x_seq', 'USAGE'); 836 837-- largeobject privilege tests 838\c - 839SET SESSION AUTHORIZATION regress_user1; 840 841SELECT lo_create(1001); 842SELECT lo_create(1002); 843SELECT lo_create(1003); 844SELECT lo_create(1004); 845SELECT lo_create(1005); 846 847GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC; 848GRANT SELECT ON LARGE OBJECT 1003 TO regress_user2; 849GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_user2; 850GRANT ALL ON LARGE OBJECT 1005 TO regress_user2; 851GRANT SELECT ON LARGE OBJECT 1005 TO regress_user2 WITH GRANT OPTION; 852 853GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC; -- to be failed 854GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser; -- to be failed 855GRANT SELECT, UPDATE ON LARGE OBJECT 999 TO PUBLIC; -- to be failed 856 857\c - 858SET SESSION AUTHORIZATION regress_user2; 859 860SELECT lo_create(2001); 861SELECT lo_create(2002); 862 863SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now 864SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode 865 866SELECT loread(lo_open(1001, x'40000'::int), 32); 867SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 868SELECT loread(lo_open(1003, x'40000'::int), 32); 869SELECT loread(lo_open(1004, x'40000'::int), 32); 870 871SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd'); 872SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 873SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied 874SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd'); 875 876GRANT SELECT ON LARGE OBJECT 1005 TO regress_user3; 877GRANT UPDATE ON LARGE OBJECT 1006 TO regress_user3; -- to be denied 878REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC; 879GRANT ALL ON LARGE OBJECT 2001 TO regress_user3; 880 881SELECT lo_unlink(1001); -- to be denied 882SELECT lo_unlink(2002); 883 884\c - 885-- confirm ACL setting 886SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 887 888SET SESSION AUTHORIZATION regress_user3; 889 890SELECT loread(lo_open(1001, x'40000'::int), 32); 891SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied 892SELECT loread(lo_open(1005, x'40000'::int), 32); 893 894SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied 895SELECT lo_truncate(lo_open(2001, x'20000'::int), 10); 896 897-- compatibility mode in largeobject permission 898\c - 899SET lo_compat_privileges = false; -- default setting 900SET SESSION AUTHORIZATION regress_user4; 901 902SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 903SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 904SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied 905SELECT lo_put(1002, 1, 'abcd'); -- to be denied 906SELECT lo_unlink(1002); -- to be denied 907SELECT lo_export(1001, '/dev/null'); -- to be denied 908 909\c - 910SET lo_compat_privileges = true; -- compatibility mode 911SET SESSION AUTHORIZATION regress_user4; 912 913SELECT loread(lo_open(1002, x'40000'::int), 32); 914SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); 915SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); 916SELECT lo_unlink(1002); 917SELECT lo_export(1001, '/dev/null'); -- to be denied 918 919-- don't allow unpriv users to access pg_largeobject contents 920\c - 921SELECT * FROM pg_largeobject LIMIT 0; 922 923SET SESSION AUTHORIZATION regress_user1; 924SELECT * FROM pg_largeobject LIMIT 0; -- to be denied 925 926-- test default ACLs 927\c - 928 929CREATE SCHEMA testns; 930GRANT ALL ON SCHEMA testns TO regress_user1; 931 932CREATE TABLE testns.acltest1 (x int); 933SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- no 934SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 935 936-- placeholder for test with duplicated schema and role names 937ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public; 938 939SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- no 940SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 941 942DROP TABLE testns.acltest1; 943CREATE TABLE testns.acltest1 (x int); 944 945SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- yes 946SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 947 948ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_user1; 949 950DROP TABLE testns.acltest1; 951CREATE TABLE testns.acltest1 (x int); 952 953SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- yes 954SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- yes 955 956ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_user1; 957 958DROP TABLE testns.acltest1; 959CREATE TABLE testns.acltest1 (x int); 960 961SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'SELECT'); -- yes 962SELECT has_table_privilege('regress_user1', 'testns.acltest1', 'INSERT'); -- no 963 964ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE EXECUTE ON FUNCTIONS FROM public; 965 966SET ROLE regress_user1; 967 968CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 969 970SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- no 971 972ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON FUNCTIONS to public; 973 974DROP FUNCTION testns.foo(); 975CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 976 977SELECT has_function_privilege('regress_user2', 'testns.foo()', 'EXECUTE'); -- yes 978 979DROP FUNCTION testns.foo(); 980 981ALTER DEFAULT PRIVILEGES FOR ROLE regress_user1 REVOKE USAGE ON TYPES FROM public; 982 983CREATE DOMAIN testns.testdomain1 AS int; 984 985SELECT has_type_privilege('regress_user2', 'testns.testdomain1', 'USAGE'); -- no 986 987ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public; 988 989DROP DOMAIN testns.testdomain1; 990CREATE DOMAIN testns.testdomain1 AS int; 991 992SELECT has_type_privilege('regress_user2', 'testns.testdomain1', 'USAGE'); -- yes 993 994DROP DOMAIN testns.testdomain1; 995 996RESET ROLE; 997 998SELECT count(*) 999 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1000 WHERE nspname = 'testns'; 1001 1002DROP SCHEMA testns CASCADE; 1003 1004SELECT d.* -- check that entries went away 1005 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1006 WHERE nspname IS NULL AND defaclnamespace != 0; 1007 1008 1009-- Grant on all objects of given type in a schema 1010\c - 1011 1012CREATE SCHEMA testns; 1013CREATE TABLE testns.t1 (f1 int); 1014CREATE TABLE testns.t2 (f1 int); 1015 1016SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- false 1017 1018GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_user1; 1019 1020SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- true 1021SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- true 1022 1023REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_user1; 1024 1025SELECT has_table_privilege('regress_user1', 'testns.t1', 'SELECT'); -- false 1026SELECT has_table_privilege('regress_user1', 'testns.t2', 'SELECT'); -- false 1027 1028CREATE FUNCTION testns.testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 1029 1030SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- true by default 1031 1032REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; 1033 1034SELECT has_function_privilege('regress_user1', 'testns.testfunc(int)', 'EXECUTE'); -- false 1035 1036SET client_min_messages TO 'warning'; 1037DROP SCHEMA testns CASCADE; 1038RESET client_min_messages; 1039 1040 1041-- Change owner of the schema & and rename of new schema owner 1042\c - 1043 1044CREATE ROLE regress_schemauser1 superuser login; 1045CREATE ROLE regress_schemauser2 superuser login; 1046 1047SET SESSION ROLE regress_schemauser1; 1048CREATE SCHEMA testns; 1049 1050SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1051 1052ALTER SCHEMA testns OWNER TO regress_schemauser2; 1053ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed; 1054SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1055 1056set session role regress_schemauser_renamed; 1057SET client_min_messages TO 'warning'; 1058DROP SCHEMA testns CASCADE; 1059RESET client_min_messages; 1060 1061-- clean up 1062\c - 1063 1064DROP ROLE regress_schemauser1; 1065DROP ROLE regress_schemauser_renamed; 1066 1067 1068-- test that dependent privileges are revoked (or not) properly 1069\c - 1070 1071set session role regress_user1; 1072create table dep_priv_test (a int); 1073grant select on dep_priv_test to regress_user2 with grant option; 1074grant select on dep_priv_test to regress_user3 with grant option; 1075set session role regress_user2; 1076grant select on dep_priv_test to regress_user4 with grant option; 1077set session role regress_user3; 1078grant select on dep_priv_test to regress_user4 with grant option; 1079set session role regress_user4; 1080grant select on dep_priv_test to regress_user5; 1081\dp dep_priv_test 1082set session role regress_user2; 1083revoke select on dep_priv_test from regress_user4 cascade; 1084\dp dep_priv_test 1085set session role regress_user3; 1086revoke select on dep_priv_test from regress_user4 cascade; 1087\dp dep_priv_test 1088set session role regress_user1; 1089drop table dep_priv_test; 1090 1091 1092-- clean up 1093 1094\c 1095 1096drop sequence x_seq; 1097 1098DROP FUNCTION testfunc2(int); 1099DROP FUNCTION testfunc4(boolean); 1100 1101DROP VIEW atestv0; 1102DROP VIEW atestv1; 1103DROP VIEW atestv2; 1104-- this should cascade to drop atestv4 1105DROP VIEW atestv3 CASCADE; 1106-- this should complain "does not exist" 1107DROP VIEW atestv4; 1108 1109DROP TABLE atest1; 1110DROP TABLE atest2; 1111DROP TABLE atest3; 1112DROP TABLE atest4; 1113DROP TABLE atest5; 1114DROP TABLE atest6; 1115DROP TABLE atestc; 1116DROP TABLE atestp1; 1117DROP TABLE atestp2; 1118 1119SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 1120 1121DROP GROUP regress_group1; 1122DROP GROUP regress_group2; 1123 1124-- these are needed to clean up permissions 1125REVOKE USAGE ON LANGUAGE sql FROM regress_user1; 1126DROP OWNED BY regress_user1; 1127 1128DROP USER regress_user1; 1129DROP USER regress_user2; 1130DROP USER regress_user3; 1131DROP USER regress_user4; 1132DROP USER regress_user5; 1133DROP USER regress_user6; 1134 1135 1136-- permissions with LOCK TABLE 1137CREATE USER regress_locktable_user; 1138CREATE TABLE lock_table (a int); 1139 1140-- LOCK TABLE and SELECT permission 1141GRANT SELECT ON lock_table TO regress_locktable_user; 1142SET SESSION AUTHORIZATION regress_locktable_user; 1143BEGIN; 1144LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail 1145ROLLBACK; 1146BEGIN; 1147LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass 1148COMMIT; 1149BEGIN; 1150LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 1151ROLLBACK; 1152\c 1153REVOKE SELECT ON lock_table FROM regress_locktable_user; 1154 1155-- LOCK TABLE and INSERT permission 1156GRANT INSERT ON lock_table TO regress_locktable_user; 1157SET SESSION AUTHORIZATION regress_locktable_user; 1158BEGIN; 1159LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1160COMMIT; 1161BEGIN; 1162LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1163ROLLBACK; 1164BEGIN; 1165LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 1166ROLLBACK; 1167\c 1168REVOKE INSERT ON lock_table FROM regress_locktable_user; 1169 1170-- LOCK TABLE and UPDATE permission 1171GRANT UPDATE ON lock_table TO regress_locktable_user; 1172SET SESSION AUTHORIZATION regress_locktable_user; 1173BEGIN; 1174LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1175COMMIT; 1176BEGIN; 1177LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1178ROLLBACK; 1179BEGIN; 1180LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1181COMMIT; 1182\c 1183REVOKE UPDATE ON lock_table FROM regress_locktable_user; 1184 1185-- LOCK TABLE and DELETE permission 1186GRANT DELETE ON lock_table TO regress_locktable_user; 1187SET SESSION AUTHORIZATION regress_locktable_user; 1188BEGIN; 1189LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1190COMMIT; 1191BEGIN; 1192LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1193ROLLBACK; 1194BEGIN; 1195LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1196COMMIT; 1197\c 1198REVOKE DELETE ON lock_table FROM regress_locktable_user; 1199 1200-- LOCK TABLE and TRUNCATE permission 1201GRANT TRUNCATE ON lock_table TO regress_locktable_user; 1202SET SESSION AUTHORIZATION regress_locktable_user; 1203BEGIN; 1204LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1205COMMIT; 1206BEGIN; 1207LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1208ROLLBACK; 1209BEGIN; 1210LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1211COMMIT; 1212\c 1213REVOKE TRUNCATE ON lock_table FROM regress_locktable_user; 1214 1215-- clean up 1216DROP TABLE lock_table; 1217DROP USER regress_locktable_user; 1218