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