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