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; 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_priv_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_priv_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_priv_user1 will own a table and provide views for it. 133SET SESSION AUTHORIZATION regress_priv_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_priv_user2 can break security. 167SET SESSION AUTHORIZATION regress_priv_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_priv_user1 grants sufficient access to regress_priv_user2. 192SET SESSION AUTHORIZATION regress_priv_user1; 193GRANT SELECT (a, b) ON atest12 TO PUBLIC; 194SET SESSION AUTHORIZATION regress_priv_user2; 195 196-- regress_priv_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_priv_user1's objects are all dropped later) 205DROP FUNCTION leak2(integer, integer) CASCADE; 206 207 208-- groups 209 210SET SESSION AUTHORIZATION regress_priv_user3; 211CREATE TABLE atest3 (one int, two int, three int); 212GRANT DELETE ON atest3 TO GROUP regress_priv_group2; 213 214SET SESSION AUTHORIZATION regress_priv_user1; 215 216SELECT * FROM atest3; -- fail 217DELETE FROM atest3; -- ok 218 219BEGIN; 220RESET SESSION AUTHORIZATION; 221ALTER ROLE regress_priv_user1 NOINHERIT; 222SET SESSION AUTHORIZATION regress_priv_user1; 223DELETE FROM atest3; 224ROLLBACK; 225 226-- views 227 228SET SESSION AUTHORIZATION regress_priv_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_priv_user4; 240GRANT SELECT ON atestv2 TO regress_priv_user2; 241 242SET SESSION AUTHORIZATION regress_priv_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_priv_user2; 267 268SET SESSION AUTHORIZATION regress_priv_user2; 269 270-- Two complex cases: 271 272SELECT * FROM atestv3; -- fail 273SELECT * FROM atestv4; -- ok (even though regress_priv_user2 cannot access underlying atestv3) 274 275SELECT * FROM atest2; -- ok 276SELECT * FROM atestv2; -- fail (even though regress_priv_user2 can access underlying atest2) 277 278-- Test column level permissions 279 280SET SESSION AUTHORIZATION regress_priv_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_priv_user4; 284GRANT ALL (one) ON atest5 TO regress_priv_user3; 285 286INSERT INTO atest5 VALUES (1,2,3); 287 288SET SESSION AUTHORIZATION regress_priv_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_priv_user1; 310GRANT SELECT (one,two) ON atest6 TO regress_priv_user4; 311 312SET SESSION AUTHORIZATION regress_priv_user4; 313SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still 314 315SET SESSION AUTHORIZATION regress_priv_user1; 316GRANT SELECT (two) ON atest5 TO regress_priv_user4; 317 318SET SESSION AUTHORIZATION regress_priv_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_priv_user1; 351GRANT INSERT (four) ON atest5 TO regress_priv_user4; 352SET SESSION AUTHORIZATION regress_priv_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_priv_user1; 359GRANT SELECT (four) ON atest5 TO regress_priv_user4; 360SET SESSION AUTHORIZATION regress_priv_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_priv_user1; 366REVOKE ALL (one) ON atest5 FROM regress_priv_user4; 367GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4; 368 369SET SESSION AUTHORIZATION regress_priv_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_priv_user1; 377CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2)); 378GRANT SELECT (c1) ON t1 TO regress_priv_user2; 379GRANT INSERT (c1, c2, c3) ON t1 TO regress_priv_user2; 380GRANT UPDATE (c1, c2, c3) ON t1 TO regress_priv_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_priv_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_priv_user1; 398DROP TABLE t1; 399 400-- check error reporting with column privs on a partitioned table 401CREATE TABLE errtst(a text, b text NOT NULL, c text, secret1 text, secret2 text) PARTITION BY LIST (a); 402CREATE TABLE errtst_part_1(secret2 text, c text, a text, b text NOT NULL, secret1 text); 403CREATE TABLE errtst_part_2(secret1 text, secret2 text, a text, c text, b text NOT NULL); 404 405ALTER TABLE errtst ATTACH PARTITION errtst_part_1 FOR VALUES IN ('aaa'); 406ALTER TABLE errtst ATTACH PARTITION errtst_part_2 FOR VALUES IN ('aaaa'); 407 408GRANT SELECT (a, b, c) ON TABLE errtst TO regress_priv_user2; 409GRANT UPDATE (a, b, c) ON TABLE errtst TO regress_priv_user2; 410GRANT INSERT (a, b, c) ON TABLE errtst TO regress_priv_user2; 411 412INSERT INTO errtst_part_1 (a, b, c, secret1, secret2) 413VALUES ('aaa', 'bbb', 'ccc', 'the body', 'is in the attic'); 414 415SET SESSION AUTHORIZATION regress_priv_user2; 416 417-- Perform a few updates that violate the NOT NULL constraint. Make sure 418-- the error messages don't leak the secret fields. 419 420-- simple insert. 421INSERT INTO errtst (a, b) VALUES ('aaa', NULL); 422-- simple update. 423UPDATE errtst SET b = NULL; 424-- partitioning key is updated, doesn't move the row. 425UPDATE errtst SET a = 'aaa', b = NULL; 426-- row is moved to another partition. 427UPDATE errtst SET a = 'aaaa', b = NULL; 428 429-- row is moved to another partition. This differs from the previous case in 430-- that the new partition is excluded by constraint exclusion, so its 431-- ResultRelInfo is not created at ExecInitModifyTable, but needs to be 432-- constructed on the fly when the updated tuple is routed to it. 433UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa'; 434 435SET SESSION AUTHORIZATION regress_priv_user1; 436DROP TABLE errtst; 437 438-- test column-level privileges when involved with DELETE 439SET SESSION AUTHORIZATION regress_priv_user1; 440ALTER TABLE atest6 ADD COLUMN three integer; 441GRANT DELETE ON atest5 TO regress_priv_user3; 442GRANT SELECT (two) ON atest5 TO regress_priv_user3; 443REVOKE ALL (one) ON atest5 FROM regress_priv_user3; 444GRANT SELECT (one) ON atest5 TO regress_priv_user4; 445 446SET SESSION AUTHORIZATION regress_priv_user4; 447SELECT atest6 FROM atest6; -- fail 448SELECT one FROM atest5 NATURAL JOIN atest6; -- fail 449 450SET SESSION AUTHORIZATION regress_priv_user1; 451ALTER TABLE atest6 DROP COLUMN three; 452 453SET SESSION AUTHORIZATION regress_priv_user4; 454SELECT atest6 FROM atest6; -- ok 455SELECT one FROM atest5 NATURAL JOIN atest6; -- ok 456 457SET SESSION AUTHORIZATION regress_priv_user1; 458ALTER TABLE atest6 DROP COLUMN two; 459REVOKE SELECT (one,blue) ON atest6 FROM regress_priv_user4; 460 461SET SESSION AUTHORIZATION regress_priv_user4; 462SELECT * FROM atest6; -- fail 463SELECT 1 FROM atest6; -- fail 464 465SET SESSION AUTHORIZATION regress_priv_user3; 466DELETE FROM atest5 WHERE one = 1; -- fail 467DELETE FROM atest5 WHERE two = 2; -- ok 468 469-- check inheritance cases 470SET SESSION AUTHORIZATION regress_priv_user1; 471CREATE TABLE atestp1 (f1 int, f2 int); 472CREATE TABLE atestp2 (fx int, fy int); 473CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2); 474GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2; 475GRANT SELECT(fx) ON atestc TO regress_priv_user2; 476 477SET SESSION AUTHORIZATION regress_priv_user2; 478SELECT fx FROM atestp2; -- ok 479SELECT fy FROM atestp2; -- ok 480SELECT atestp2 FROM atestp2; -- ok 481SELECT tableoid FROM atestp2; -- ok 482SELECT fy FROM atestc; -- fail 483 484SET SESSION AUTHORIZATION regress_priv_user1; 485GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2; 486 487SET SESSION AUTHORIZATION regress_priv_user2; 488SELECT fx FROM atestp2; -- still ok 489SELECT fy FROM atestp2; -- ok 490SELECT atestp2 FROM atestp2; -- ok 491SELECT tableoid FROM atestp2; -- ok 492 493-- privileges on functions, languages 494 495-- switch to superuser 496\c - 497 498REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC; 499GRANT USAGE ON LANGUAGE sql TO regress_priv_user1; -- ok 500GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail 501 502SET SESSION AUTHORIZATION regress_priv_user1; 503GRANT USAGE ON LANGUAGE sql TO regress_priv_user2; -- fail 504CREATE FUNCTION priv_testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; 505CREATE FUNCTION priv_testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 506CREATE AGGREGATE priv_testagg1(int) (sfunc = int4pl, stype = int4); 507CREATE PROCEDURE priv_testproc1(int) AS 'select $1;' LANGUAGE sql; 508 509REVOKE ALL ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) FROM PUBLIC; 510GRANT EXECUTE ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) TO regress_priv_user2; 511REVOKE ALL ON FUNCTION priv_testproc1(int) FROM PUBLIC; -- fail, not a function 512REVOKE ALL ON PROCEDURE priv_testproc1(int) FROM PUBLIC; 513GRANT EXECUTE ON PROCEDURE priv_testproc1(int) TO regress_priv_user2; 514GRANT USAGE ON FUNCTION priv_testfunc1(int) TO regress_priv_user3; -- semantic error 515GRANT USAGE ON FUNCTION priv_testagg1(int) TO regress_priv_user3; -- semantic error 516GRANT USAGE ON PROCEDURE priv_testproc1(int) TO regress_priv_user3; -- semantic error 517GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc1(int) TO regress_priv_user4; 518GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc_nosuch(int) TO regress_priv_user4; 519GRANT ALL PRIVILEGES ON FUNCTION priv_testagg1(int) TO regress_priv_user4; 520GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4; 521 522CREATE FUNCTION priv_testfunc4(boolean) RETURNS text 523 AS 'select col1 from atest2 where col2 = $1;' 524 LANGUAGE sql SECURITY DEFINER; 525GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3; 526 527SET SESSION AUTHORIZATION regress_priv_user2; 528SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok 529CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail 530SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok 531CALL priv_testproc1(6); -- ok 532 533SET SESSION AUTHORIZATION regress_priv_user3; 534SELECT priv_testfunc1(5); -- fail 535SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail 536CALL priv_testproc1(6); -- fail 537SELECT col1 FROM atest2 WHERE col2 = true; -- fail 538SELECT priv_testfunc4(true); -- ok 539 540SET SESSION AUTHORIZATION regress_priv_user4; 541SELECT priv_testfunc1(5); -- ok 542SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok 543CALL priv_testproc1(6); -- ok 544 545DROP FUNCTION priv_testfunc1(int); -- fail 546DROP AGGREGATE priv_testagg1(int); -- fail 547DROP PROCEDURE priv_testproc1(int); -- fail 548 549\c - 550 551DROP FUNCTION priv_testfunc1(int); -- ok 552-- restore to sanity 553GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC; 554 555-- verify privilege checks on array-element coercions 556BEGIN; 557SELECT '{1}'::int4[]::int8[]; 558REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC; 559SELECT '{1}'::int4[]::int8[]; --superuser, succeed 560SET SESSION AUTHORIZATION regress_priv_user4; 561SELECT '{1}'::int4[]::int8[]; --other user, fail 562ROLLBACK; 563 564-- privileges on types 565 566-- switch to superuser 567\c - 568 569CREATE TYPE priv_testtype1 AS (a int, b text); 570REVOKE USAGE ON TYPE priv_testtype1 FROM PUBLIC; 571GRANT USAGE ON TYPE priv_testtype1 TO regress_priv_user2; 572GRANT USAGE ON TYPE _priv_testtype1 TO regress_priv_user2; -- fail 573GRANT USAGE ON DOMAIN priv_testtype1 TO regress_priv_user2; -- fail 574 575CREATE DOMAIN priv_testdomain1 AS int; 576REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC; 577GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2; 578GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok 579 580SET SESSION AUTHORIZATION regress_priv_user1; 581 582-- commands that should fail 583 584CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint); 585 586CREATE DOMAIN priv_testdomain2a AS priv_testdomain1; 587 588CREATE DOMAIN priv_testdomain3a AS int; 589CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3a AS $$ SELECT $1::priv_testdomain3a $$ LANGUAGE SQL; 590CREATE CAST (priv_testdomain1 AS priv_testdomain3a) WITH FUNCTION castfunc(int); 591DROP FUNCTION castfunc(int) CASCADE; 592DROP DOMAIN priv_testdomain3a; 593 594CREATE FUNCTION priv_testfunc5a(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 595CREATE FUNCTION priv_testfunc6a(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$; 596 597CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = priv_testdomain1, RIGHTARG = priv_testdomain1); 598 599CREATE TABLE test5a (a int, b priv_testdomain1); 600CREATE TABLE test6a OF priv_testtype1; 601CREATE TABLE test10a (a int[], b priv_testtype1[]); 602 603CREATE TABLE test9a (a int, b int); 604ALTER TABLE test9a ADD COLUMN c priv_testdomain1; 605ALTER TABLE test9a ALTER COLUMN b TYPE priv_testdomain1; 606 607CREATE TYPE test7a AS (a int, b priv_testdomain1); 608 609CREATE TYPE test8a AS (a int, b int); 610ALTER TYPE test8a ADD ATTRIBUTE c priv_testdomain1; 611ALTER TYPE test8a ALTER ATTRIBUTE b TYPE priv_testdomain1; 612 613CREATE TABLE test11a AS (SELECT 1::priv_testdomain1 AS a); 614 615REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC; 616 617SET SESSION AUTHORIZATION regress_priv_user2; 618 619-- commands that should succeed 620 621CREATE AGGREGATE priv_testagg1b(priv_testdomain1) (sfunc = int4_sum, stype = bigint); 622 623CREATE DOMAIN priv_testdomain2b AS priv_testdomain1; 624 625CREATE DOMAIN priv_testdomain3b AS int; 626CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3b AS $$ SELECT $1::priv_testdomain3b $$ LANGUAGE SQL; 627CREATE CAST (priv_testdomain1 AS priv_testdomain3b) WITH FUNCTION castfunc(int); 628 629CREATE FUNCTION priv_testfunc5b(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$; 630CREATE FUNCTION priv_testfunc6b(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$; 631 632CREATE OPERATOR !! (PROCEDURE = priv_testfunc5b, RIGHTARG = priv_testdomain1); 633 634CREATE TABLE test5b (a int, b priv_testdomain1); 635CREATE TABLE test6b OF priv_testtype1; 636CREATE TABLE test10b (a int[], b priv_testtype1[]); 637 638CREATE TABLE test9b (a int, b int); 639ALTER TABLE test9b ADD COLUMN c priv_testdomain1; 640ALTER TABLE test9b ALTER COLUMN b TYPE priv_testdomain1; 641 642CREATE TYPE test7b AS (a int, b priv_testdomain1); 643 644CREATE TYPE test8b AS (a int, b int); 645ALTER TYPE test8b ADD ATTRIBUTE c priv_testdomain1; 646ALTER TYPE test8b ALTER ATTRIBUTE b TYPE priv_testdomain1; 647 648CREATE TABLE test11b AS (SELECT 1::priv_testdomain1 AS a); 649 650REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC; 651 652\c - 653DROP AGGREGATE priv_testagg1b(priv_testdomain1); 654DROP DOMAIN priv_testdomain2b; 655DROP OPERATOR !! (NONE, priv_testdomain1); 656DROP FUNCTION priv_testfunc5b(a priv_testdomain1); 657DROP FUNCTION priv_testfunc6b(b int); 658DROP TABLE test5b; 659DROP TABLE test6b; 660DROP TABLE test9b; 661DROP TABLE test10b; 662DROP TYPE test7b; 663DROP TYPE test8b; 664DROP CAST (priv_testdomain1 AS priv_testdomain3b); 665DROP FUNCTION castfunc(int) CASCADE; 666DROP DOMAIN priv_testdomain3b; 667DROP TABLE test11b; 668 669DROP TYPE priv_testtype1; -- ok 670DROP DOMAIN priv_testdomain1; -- ok 671 672 673-- truncate 674SET SESSION AUTHORIZATION regress_priv_user5; 675TRUNCATE atest2; -- ok 676TRUNCATE atest3; -- fail 677 678-- has_table_privilege function 679 680-- bad-input checks 681select has_table_privilege(NULL,'pg_authid','select'); 682select has_table_privilege('pg_shad','select'); 683select has_table_privilege('nosuchuser','pg_authid','select'); 684select has_table_privilege('pg_authid','sel'); 685select has_table_privilege(-999999,'pg_authid','update'); 686select has_table_privilege(1,'select'); 687 688-- superuser 689\c - 690 691select has_table_privilege(current_user,'pg_authid','select'); 692select has_table_privilege(current_user,'pg_authid','insert'); 693 694select has_table_privilege(t2.oid,'pg_authid','update') 695from (select oid from pg_roles where rolname = current_user) as t2; 696select has_table_privilege(t2.oid,'pg_authid','delete') 697from (select oid from pg_roles where rolname = current_user) as t2; 698 699-- 'rule' privilege no longer exists, but for backwards compatibility 700-- has_table_privilege still recognizes the keyword and says FALSE 701select has_table_privilege(current_user,t1.oid,'rule') 702from (select oid from pg_class where relname = 'pg_authid') as t1; 703select has_table_privilege(current_user,t1.oid,'references') 704from (select oid from pg_class where relname = 'pg_authid') as t1; 705 706select has_table_privilege(t2.oid,t1.oid,'select') 707from (select oid from pg_class where relname = 'pg_authid') as t1, 708 (select oid from pg_roles where rolname = current_user) as t2; 709select has_table_privilege(t2.oid,t1.oid,'insert') 710from (select oid from pg_class where relname = 'pg_authid') as t1, 711 (select oid from pg_roles where rolname = current_user) as t2; 712 713select has_table_privilege('pg_authid','update'); 714select has_table_privilege('pg_authid','delete'); 715select has_table_privilege('pg_authid','truncate'); 716 717select has_table_privilege(t1.oid,'select') 718from (select oid from pg_class where relname = 'pg_authid') as t1; 719select has_table_privilege(t1.oid,'trigger') 720from (select oid from pg_class where relname = 'pg_authid') as t1; 721 722-- non-superuser 723SET SESSION AUTHORIZATION regress_priv_user3; 724 725select has_table_privilege(current_user,'pg_class','select'); 726select has_table_privilege(current_user,'pg_class','insert'); 727 728select has_table_privilege(t2.oid,'pg_class','update') 729from (select oid from pg_roles where rolname = current_user) as t2; 730select has_table_privilege(t2.oid,'pg_class','delete') 731from (select oid from pg_roles where rolname = current_user) as t2; 732 733select has_table_privilege(current_user,t1.oid,'references') 734from (select oid from pg_class where relname = 'pg_class') as t1; 735 736select has_table_privilege(t2.oid,t1.oid,'select') 737from (select oid from pg_class where relname = 'pg_class') as t1, 738 (select oid from pg_roles where rolname = current_user) as t2; 739select has_table_privilege(t2.oid,t1.oid,'insert') 740from (select oid from pg_class where relname = 'pg_class') as t1, 741 (select oid from pg_roles where rolname = current_user) as t2; 742 743select has_table_privilege('pg_class','update'); 744select has_table_privilege('pg_class','delete'); 745select has_table_privilege('pg_class','truncate'); 746 747select has_table_privilege(t1.oid,'select') 748from (select oid from pg_class where relname = 'pg_class') as t1; 749select has_table_privilege(t1.oid,'trigger') 750from (select oid from pg_class where relname = 'pg_class') as t1; 751 752select has_table_privilege(current_user,'atest1','select'); 753select has_table_privilege(current_user,'atest1','insert'); 754 755select has_table_privilege(t2.oid,'atest1','update') 756from (select oid from pg_roles where rolname = current_user) as t2; 757select has_table_privilege(t2.oid,'atest1','delete') 758from (select oid from pg_roles where rolname = current_user) as t2; 759 760select has_table_privilege(current_user,t1.oid,'references') 761from (select oid from pg_class where relname = 'atest1') as t1; 762 763select has_table_privilege(t2.oid,t1.oid,'select') 764from (select oid from pg_class where relname = 'atest1') as t1, 765 (select oid from pg_roles where rolname = current_user) as t2; 766select has_table_privilege(t2.oid,t1.oid,'insert') 767from (select oid from pg_class where relname = 'atest1') as t1, 768 (select oid from pg_roles where rolname = current_user) as t2; 769 770select has_table_privilege('atest1','update'); 771select has_table_privilege('atest1','delete'); 772select has_table_privilege('atest1','truncate'); 773 774select has_table_privilege(t1.oid,'select') 775from (select oid from pg_class where relname = 'atest1') as t1; 776select has_table_privilege(t1.oid,'trigger') 777from (select oid from pg_class where relname = 'atest1') as t1; 778 779-- has_column_privilege function 780 781-- bad-input checks (as non-super-user) 782select has_column_privilege('pg_authid',NULL,'select'); 783select has_column_privilege('pg_authid','nosuchcol','select'); 784select has_column_privilege(9999,'nosuchcol','select'); 785select has_column_privilege(9999,99::int2,'select'); 786select has_column_privilege('pg_authid',99::int2,'select'); 787select has_column_privilege(9999,99::int2,'select'); 788 789create temp table mytable(f1 int, f2 int, f3 int); 790alter table mytable drop column f2; 791select has_column_privilege('mytable','f2','select'); 792select has_column_privilege('mytable','........pg.dropped.2........','select'); 793select has_column_privilege('mytable',2::int2,'select'); 794revoke select on table mytable from regress_priv_user3; 795select has_column_privilege('mytable',2::int2,'select'); 796drop table mytable; 797 798-- Grant options 799 800SET SESSION AUTHORIZATION regress_priv_user1; 801 802CREATE TABLE atest4 (a int); 803 804GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION; 805GRANT UPDATE ON atest4 TO regress_priv_user2; 806GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION; 807 808SET SESSION AUTHORIZATION regress_priv_user2; 809 810GRANT SELECT ON atest4 TO regress_priv_user3; 811GRANT UPDATE ON atest4 TO regress_priv_user3; -- fail 812 813SET SESSION AUTHORIZATION regress_priv_user1; 814 815REVOKE SELECT ON atest4 FROM regress_priv_user3; -- does nothing 816SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- true 817REVOKE SELECT ON atest4 FROM regress_priv_user2; -- fail 818REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_priv_user2 CASCADE; -- ok 819SELECT has_table_privilege('regress_priv_user2', 'atest4', 'SELECT'); -- true 820SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- false 821 822SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true 823 824 825-- security-restricted operations 826\c - 827CREATE ROLE regress_sro_user; 828 829SET SESSION AUTHORIZATION regress_sro_user; 830CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS 831 'GRANT regress_priv_group2 TO regress_sro_user'; 832CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 833 'DECLARE c CURSOR WITH HOLD FOR SELECT unwanted_grant(); SELECT true'; 834-- REFRESH of this MV will queue a GRANT at end of transaction 835CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA; 836REFRESH MATERIALIZED VIEW sro_mv; 837\c - 838REFRESH MATERIALIZED VIEW sro_mv; 839 840SET SESSION AUTHORIZATION regress_sro_user; 841-- INSERT to this table will queue a GRANT at end of transaction 842CREATE TABLE sro_trojan_table (); 843CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS 844 'BEGIN PERFORM unwanted_grant(); RETURN NULL; END'; 845CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table 846 INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan(); 847-- Now, REFRESH will issue such an INSERT, queueing the GRANT 848CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 849 'INSERT INTO sro_trojan_table DEFAULT VALUES; SELECT true'; 850REFRESH MATERIALIZED VIEW sro_mv; 851\c - 852REFRESH MATERIALIZED VIEW sro_mv; 853BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; 854 855DROP OWNED BY regress_sro_user; 856DROP ROLE regress_sro_user; 857 858 859-- Admin options 860 861SET SESSION AUTHORIZATION regress_priv_user4; 862CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS 863 'GRANT regress_priv_group2 TO regress_priv_user5'; 864GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION 865SET ROLE regress_priv_group2; 866GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege 867 868SET SESSION AUTHORIZATION regress_priv_user1; 869GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION 870SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN 871SET ROLE regress_priv_group2; 872GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help 873 874SET SESSION AUTHORIZATION regress_priv_group2; 875GRANT regress_priv_group2 TO regress_priv_user5; -- ok: a role can self-admin 876CREATE FUNCTION dogrant_fails() RETURNS void LANGUAGE sql SECURITY DEFINER AS 877 'GRANT regress_priv_group2 TO regress_priv_user5'; 878SELECT dogrant_fails(); -- fails: no self-admin in SECURITY DEFINER 879DROP FUNCTION dogrant_fails(); 880 881SET SESSION AUTHORIZATION regress_priv_user4; 882DROP FUNCTION dogrant_ok(); 883REVOKE regress_priv_group2 FROM regress_priv_user5; 884 885 886-- has_sequence_privilege tests 887\c - 888 889CREATE SEQUENCE x_seq; 890 891GRANT USAGE on x_seq to regress_priv_user2; 892 893SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT'); 894SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT'); 895SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT'); 896 897SET SESSION AUTHORIZATION regress_priv_user2; 898 899SELECT has_sequence_privilege('x_seq', 'USAGE'); 900 901-- largeobject privilege tests 902\c - 903SET SESSION AUTHORIZATION regress_priv_user1; 904 905SELECT lo_create(1001); 906SELECT lo_create(1002); 907SELECT lo_create(1003); 908SELECT lo_create(1004); 909SELECT lo_create(1005); 910 911GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC; 912GRANT SELECT ON LARGE OBJECT 1003 TO regress_priv_user2; 913GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_priv_user2; 914GRANT ALL ON LARGE OBJECT 1005 TO regress_priv_user2; 915GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user2 WITH GRANT OPTION; 916 917GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC; -- to be failed 918GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser; -- to be failed 919GRANT SELECT, UPDATE ON LARGE OBJECT 999 TO PUBLIC; -- to be failed 920 921\c - 922SET SESSION AUTHORIZATION regress_priv_user2; 923 924SELECT lo_create(2001); 925SELECT lo_create(2002); 926 927SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now 928SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode 929 930SELECT loread(lo_open(1001, x'40000'::int), 32); 931SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 932SELECT loread(lo_open(1003, x'40000'::int), 32); 933SELECT loread(lo_open(1004, x'40000'::int), 32); 934 935SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd'); 936SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 937SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied 938SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd'); 939 940GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3; 941GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3; -- to be denied 942REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC; 943GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3; 944 945SELECT lo_unlink(1001); -- to be denied 946SELECT lo_unlink(2002); 947 948\c - 949-- confirm ACL setting 950SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 951 952SET SESSION AUTHORIZATION regress_priv_user3; 953 954SELECT loread(lo_open(1001, x'40000'::int), 32); 955SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied 956SELECT loread(lo_open(1005, x'40000'::int), 32); 957 958SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied 959SELECT lo_truncate(lo_open(2001, x'20000'::int), 10); 960 961-- compatibility mode in largeobject permission 962\c - 963SET lo_compat_privileges = false; -- default setting 964SET SESSION AUTHORIZATION regress_priv_user4; 965 966SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied 967SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied 968SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied 969SELECT lo_put(1002, 1, 'abcd'); -- to be denied 970SELECT lo_unlink(1002); -- to be denied 971SELECT lo_export(1001, '/dev/null'); -- to be denied 972SELECT lo_import('/dev/null'); -- to be denied 973SELECT lo_import('/dev/null', 2003); -- to be denied 974 975\c - 976SET lo_compat_privileges = true; -- compatibility mode 977SET SESSION AUTHORIZATION regress_priv_user4; 978 979SELECT loread(lo_open(1002, x'40000'::int), 32); 980SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); 981SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); 982SELECT lo_unlink(1002); 983SELECT lo_export(1001, '/dev/null'); -- to be denied 984 985-- don't allow unpriv users to access pg_largeobject contents 986\c - 987SELECT * FROM pg_largeobject LIMIT 0; 988 989SET SESSION AUTHORIZATION regress_priv_user1; 990SELECT * FROM pg_largeobject LIMIT 0; -- to be denied 991 992-- test default ACLs 993\c - 994 995CREATE SCHEMA testns; 996GRANT ALL ON SCHEMA testns TO regress_priv_user1; 997 998CREATE TABLE testns.acltest1 (x int); 999SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no 1000SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1001 1002-- placeholder for test with duplicated schema and role names 1003ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public; 1004 1005SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no 1006SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1007 1008DROP TABLE testns.acltest1; 1009CREATE TABLE testns.acltest1 (x int); 1010 1011SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes 1012SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1013 1014ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1; 1015 1016DROP TABLE testns.acltest1; 1017CREATE TABLE testns.acltest1 (x int); 1018 1019SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes 1020SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- yes 1021 1022ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1; 1023 1024DROP TABLE testns.acltest1; 1025CREATE TABLE testns.acltest1 (x int); 1026 1027SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes 1028SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no 1029 1030ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM public; 1031 1032ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_priv_user2; -- error 1033 1034-- 1035-- Testing blanket default grants is very hazardous since it might change 1036-- the privileges attached to objects created by concurrent regression tests. 1037-- To avoid that, be sure to revoke the privileges again before committing. 1038-- 1039BEGIN; 1040 1041ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2; 1042 1043CREATE SCHEMA testns2; 1044 1045SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes 1046SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no 1047 1048ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2; 1049 1050CREATE SCHEMA testns3; 1051 1052SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'USAGE'); -- no 1053SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'CREATE'); -- no 1054 1055ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2; 1056 1057CREATE SCHEMA testns4; 1058 1059SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'USAGE'); -- yes 1060SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes 1061 1062ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2; 1063 1064COMMIT; 1065 1066CREATE SCHEMA testns5; 1067 1068SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE'); -- no 1069SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'CREATE'); -- no 1070 1071SET ROLE regress_priv_user1; 1072 1073CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1074CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); 1075CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; 1076 1077SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- no 1078SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- no 1079SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- no 1080 1081ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public; 1082 1083DROP FUNCTION testns.foo(); 1084CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; 1085DROP AGGREGATE testns.agg1(int); 1086CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); 1087DROP PROCEDURE testns.bar(); 1088CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; 1089 1090SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- yes 1091SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes 1092SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here) 1093 1094DROP FUNCTION testns.foo(); 1095DROP AGGREGATE testns.agg1(int); 1096DROP PROCEDURE testns.bar(); 1097 1098ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE USAGE ON TYPES FROM public; 1099 1100CREATE DOMAIN testns.priv_testdomain1 AS int; 1101 1102SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- no 1103 1104ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public; 1105 1106DROP DOMAIN testns.priv_testdomain1; 1107CREATE DOMAIN testns.priv_testdomain1 AS int; 1108 1109SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- yes 1110 1111DROP DOMAIN testns.priv_testdomain1; 1112 1113RESET ROLE; 1114 1115SELECT count(*) 1116 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1117 WHERE nspname = 'testns'; 1118 1119DROP SCHEMA testns CASCADE; 1120DROP SCHEMA testns2 CASCADE; 1121DROP SCHEMA testns3 CASCADE; 1122DROP SCHEMA testns4 CASCADE; 1123DROP SCHEMA testns5 CASCADE; 1124 1125SELECT d.* -- check that entries went away 1126 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid 1127 WHERE nspname IS NULL AND defaclnamespace != 0; 1128 1129 1130-- Grant on all objects of given type in a schema 1131\c - 1132 1133CREATE SCHEMA testns; 1134CREATE TABLE testns.t1 (f1 int); 1135CREATE TABLE testns.t2 (f1 int); 1136 1137SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false 1138 1139GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1; 1140 1141SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- true 1142SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- true 1143 1144REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1; 1145 1146SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false 1147SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- false 1148 1149CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; 1150CREATE AGGREGATE testns.priv_testagg(int) (sfunc = int4pl, stype = int4); 1151CREATE PROCEDURE testns.priv_testproc(int) AS 'select 3' LANGUAGE sql; 1152 1153SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true by default 1154SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true by default 1155SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true by default 1156 1157REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; 1158 1159SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- false 1160SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- false 1161SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- still true, not a function 1162 1163REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC; 1164 1165SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- now false 1166 1167GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC; 1168 1169SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true 1170SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true 1171SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true 1172 1173DROP SCHEMA testns CASCADE; 1174 1175 1176-- Change owner of the schema & and rename of new schema owner 1177\c - 1178 1179CREATE ROLE regress_schemauser1 superuser login; 1180CREATE ROLE regress_schemauser2 superuser login; 1181 1182SET SESSION ROLE regress_schemauser1; 1183CREATE SCHEMA testns; 1184 1185SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1186 1187ALTER SCHEMA testns OWNER TO regress_schemauser2; 1188ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed; 1189SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid; 1190 1191set session role regress_schemauser_renamed; 1192DROP SCHEMA testns CASCADE; 1193 1194-- clean up 1195\c - 1196 1197DROP ROLE regress_schemauser1; 1198DROP ROLE regress_schemauser_renamed; 1199 1200 1201-- test that dependent privileges are revoked (or not) properly 1202\c - 1203 1204set session role regress_priv_user1; 1205create table dep_priv_test (a int); 1206grant select on dep_priv_test to regress_priv_user2 with grant option; 1207grant select on dep_priv_test to regress_priv_user3 with grant option; 1208set session role regress_priv_user2; 1209grant select on dep_priv_test to regress_priv_user4 with grant option; 1210set session role regress_priv_user3; 1211grant select on dep_priv_test to regress_priv_user4 with grant option; 1212set session role regress_priv_user4; 1213grant select on dep_priv_test to regress_priv_user5; 1214\dp dep_priv_test 1215set session role regress_priv_user2; 1216revoke select on dep_priv_test from regress_priv_user4 cascade; 1217\dp dep_priv_test 1218set session role regress_priv_user3; 1219revoke select on dep_priv_test from regress_priv_user4 cascade; 1220\dp dep_priv_test 1221set session role regress_priv_user1; 1222drop table dep_priv_test; 1223 1224 1225-- clean up 1226 1227\c 1228 1229drop sequence x_seq; 1230 1231DROP AGGREGATE priv_testagg1(int); 1232DROP FUNCTION priv_testfunc2(int); 1233DROP FUNCTION priv_testfunc4(boolean); 1234DROP PROCEDURE priv_testproc1(int); 1235 1236DROP VIEW atestv0; 1237DROP VIEW atestv1; 1238DROP VIEW atestv2; 1239-- this should cascade to drop atestv4 1240DROP VIEW atestv3 CASCADE; 1241-- this should complain "does not exist" 1242DROP VIEW atestv4; 1243 1244DROP TABLE atest1; 1245DROP TABLE atest2; 1246DROP TABLE atest3; 1247DROP TABLE atest4; 1248DROP TABLE atest5; 1249DROP TABLE atest6; 1250DROP TABLE atestc; 1251DROP TABLE atestp1; 1252DROP TABLE atestp2; 1253 1254SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid; 1255 1256DROP GROUP regress_priv_group1; 1257DROP GROUP regress_priv_group2; 1258 1259-- these are needed to clean up permissions 1260REVOKE USAGE ON LANGUAGE sql FROM regress_priv_user1; 1261DROP OWNED BY regress_priv_user1; 1262 1263DROP USER regress_priv_user1; 1264DROP USER regress_priv_user2; 1265DROP USER regress_priv_user3; 1266DROP USER regress_priv_user4; 1267DROP USER regress_priv_user5; 1268DROP USER regress_priv_user6; 1269 1270 1271-- permissions with LOCK TABLE 1272CREATE USER regress_locktable_user; 1273CREATE TABLE lock_table (a int); 1274 1275-- LOCK TABLE and SELECT permission 1276GRANT SELECT ON lock_table TO regress_locktable_user; 1277SET SESSION AUTHORIZATION regress_locktable_user; 1278BEGIN; 1279LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail 1280ROLLBACK; 1281BEGIN; 1282LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass 1283COMMIT; 1284BEGIN; 1285LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 1286ROLLBACK; 1287\c 1288REVOKE SELECT ON lock_table FROM regress_locktable_user; 1289 1290-- LOCK TABLE and INSERT permission 1291GRANT INSERT ON lock_table TO regress_locktable_user; 1292SET SESSION AUTHORIZATION regress_locktable_user; 1293BEGIN; 1294LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1295COMMIT; 1296BEGIN; 1297LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1298ROLLBACK; 1299BEGIN; 1300LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail 1301ROLLBACK; 1302\c 1303REVOKE INSERT ON lock_table FROM regress_locktable_user; 1304 1305-- LOCK TABLE and UPDATE permission 1306GRANT UPDATE ON lock_table TO regress_locktable_user; 1307SET SESSION AUTHORIZATION regress_locktable_user; 1308BEGIN; 1309LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1310COMMIT; 1311BEGIN; 1312LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1313ROLLBACK; 1314BEGIN; 1315LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1316COMMIT; 1317\c 1318REVOKE UPDATE ON lock_table FROM regress_locktable_user; 1319 1320-- LOCK TABLE and DELETE permission 1321GRANT DELETE ON lock_table TO regress_locktable_user; 1322SET SESSION AUTHORIZATION regress_locktable_user; 1323BEGIN; 1324LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1325COMMIT; 1326BEGIN; 1327LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1328ROLLBACK; 1329BEGIN; 1330LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1331COMMIT; 1332\c 1333REVOKE DELETE ON lock_table FROM regress_locktable_user; 1334 1335-- LOCK TABLE and TRUNCATE permission 1336GRANT TRUNCATE ON lock_table TO regress_locktable_user; 1337SET SESSION AUTHORIZATION regress_locktable_user; 1338BEGIN; 1339LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass 1340COMMIT; 1341BEGIN; 1342LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should fail 1343ROLLBACK; 1344BEGIN; 1345LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass 1346COMMIT; 1347\c 1348REVOKE TRUNCATE ON lock_table FROM regress_locktable_user; 1349 1350-- clean up 1351DROP TABLE lock_table; 1352DROP USER regress_locktable_user; 1353