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