| Quellcodebibliothek Statistik Leitseite products/Sources/formale Sprachen/C/Postgres/src/test/regress/expected/ (Postgres Database Version 18.4©) Datei vom 11.4.2026 mit Größe 116 kB |
|
Quelle privileges.out Sprache: unbekannt |
|
|
Spracherkennung für: .out vermutete Sprache: Unknown {[0] [0] [0]} [Methode: Schwerpunktbildung, einfache Gewichte, sechs Dimensionen] --
-- Test access privileges -- -- Clean up in case a prior regression run failed -- Suppress NOTICE messages when users/groups don't exist SET client_min_messages TO 'warning'; DROP ROLE IF EXISTS regress_priv_group1; DROP ROLE IF EXISTS regress_priv_group2; DROP ROLE IF EXISTS regress_priv_user1; DROP ROLE IF EXISTS regress_priv_user2; DROP ROLE IF EXISTS regress_priv_user3; DROP ROLE IF EXISTS regress_priv_user4; DROP ROLE IF EXISTS regress_priv_user5; DROP ROLE IF EXISTS regress_priv_user6; DROP ROLE IF EXISTS regress_priv_user7; SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid lo_unlink ----------- (0 rows) RESET client_min_messages; -- test proper begins here CREATE USER regress_priv_user1; CREATE USER regress_priv_user2; CREATE USER regress_priv_user3; CREATE USER regress_priv_user4; CREATE USER regress_priv_user5; CREATE USER regress_priv_user5; -- duplicate ERROR: role "regress_priv_user5" already exists CREATE USER regress_priv_user6; CREATE USER regress_priv_user7; CREATE USER regress_priv_user8; CREATE USER regress_priv_user9; CREATE USER regress_priv_user10; CREATE ROLE regress_priv_role; -- circular ADMIN OPTION grants should be disallowed GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION; GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION GRANTED BY regress_priv_ GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION GRANTED BY regress_priv_ ERROR: ADMIN option cannot be granted back to your own grantor -- need CASCADE to revoke grant or admin option if dependent grants exist REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2; -- fail ERROR: dependent privileges exist HINT: Use CASCADE to revoke them too. REVOKE regress_priv_user1 FROM regress_priv_user2; -- fail ERROR: dependent privileges exist HINT: Use CASCADE to revoke them too. SELECT member::regrole, admin_option FROM pg_auth_members WHERE roleid = 'regress_priv_u member | admin_option --------------------+-------------- regress_priv_user2 | t regress_priv_user3 | t (2 rows) BEGIN; REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2 CASCADE; SELECT member::regrole, admin_option FROM pg_auth_members WHERE roleid = 'regress_priv_u member | admin_option --------------------+-------------- regress_priv_user2 | f (1 row) ROLLBACK; REVOKE regress_priv_user1 FROM regress_priv_user2 CASCADE; SELECT member::regrole, admin_option FROM pg_auth_members WHERE roleid = 'regress_priv_u member | admin_option --------+-------------- (0 rows) -- inferred grantor must be a role with ADMIN OPTION GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION; GRANT regress_priv_user2 TO regress_priv_user3; SET ROLE regress_priv_user3; GRANT regress_priv_user1 TO regress_priv_user4; SELECT grantor::regrole FROM pg_auth_members WHERE roleid = 'regress_priv_user1'::regro grantor -------------------- regress_priv_user2 (1 row) RESET ROLE; REVOKE regress_priv_user2 FROM regress_priv_user3; REVOKE regress_priv_user1 FROM regress_priv_user2 CASCADE; -- test GRANTED BY with DROP OWNED and REASSIGN OWNED GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION; GRANT regress_priv_user1 TO regress_priv_user3 GRANTED BY regress_priv_user2; DROP ROLE regress_priv_user2; -- fail, dependency ERROR: role "regress_priv_user2" cannot be dropped because some objects depend on it DETAIL: privileges for membership of role regress_priv_user3 in role regress_priv_user1 REASSIGN OWNED BY regress_priv_user2 TO regress_priv_user4; DROP ROLE regress_priv_user2; -- still fail, REASSIGN OWNED doesn't help ERROR: role "regress_priv_user2" cannot be dropped because some objects depend on it DETAIL: privileges for membership of role regress_priv_user3 in role regress_priv_user1 DROP OWNED BY regress_priv_user2; DROP ROLE regress_priv_user2; -- ok now, DROP OWNED does the job -- test that removing granted role or grantee role removes dependency GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION; GRANT regress_priv_user1 TO regress_priv_user4 GRANTED BY regress_priv_user3; DROP ROLE regress_priv_user3; -- should fail, dependency ERROR: role "regress_priv_user3" cannot be dropped because some objects depend on it DETAIL: privileges for membership of role regress_priv_user4 in role regress_priv_user1 DROP ROLE regress_priv_user4; -- ok DROP ROLE regress_priv_user3; -- ok now GRANT regress_priv_user1 TO regress_priv_user5 WITH ADMIN OPTION; GRANT regress_priv_user1 TO regress_priv_user6 GRANTED BY regress_priv_user5; DROP ROLE regress_priv_user5; -- should fail, dependency ERROR: role "regress_priv_user5" cannot be dropped because some objects depend on it DETAIL: privileges for membership of role regress_priv_user6 in role regress_priv_user1 DROP ROLE regress_priv_user1, regress_priv_user5; -- ok, despite order -- recreate the roles we just dropped CREATE USER regress_priv_user1; CREATE USER regress_priv_user2; CREATE USER regress_priv_user3; CREATE USER regress_priv_user4; CREATE USER regress_priv_user5; GRANT pg_read_all_data TO regress_priv_user6; GRANT pg_write_all_data TO regress_priv_user7; GRANT pg_read_all_settings TO regress_priv_user8 WITH ADMIN OPTION; GRANT regress_priv_user9 TO regress_priv_user8; SET SESSION AUTHORIZATION regress_priv_user8; GRANT pg_read_all_settings TO regress_priv_user9 WITH ADMIN OPTION; SET SESSION AUTHORIZATION regress_priv_user9; GRANT pg_read_all_settings TO regress_priv_user10; SET SESSION AUTHORIZATION regress_priv_user8; REVOKE pg_read_all_settings FROM regress_priv_user10 GRANTED BY regress_priv_user9; REVOKE ADMIN OPTION FOR pg_read_all_settings FROM regress_priv_user9; REVOKE pg_read_all_settings FROM regress_priv_user9; RESET SESSION AUTHORIZATION; REVOKE regress_priv_user9 FROM regress_priv_user8; REVOKE ADMIN OPTION FOR pg_read_all_settings FROM regress_priv_user8; SET SESSION AUTHORIZATION regress_priv_user8; SET ROLE pg_read_all_settings; RESET ROLE; RESET SESSION AUTHORIZATION; REVOKE SET OPTION FOR pg_read_all_settings FROM regress_priv_user8; GRANT pg_read_all_stats TO regress_priv_user8 WITH SET FALSE; SET SESSION AUTHORIZATION regress_priv_user8; SET ROLE pg_read_all_settings; -- fail, no SET option any more ERROR: permission denied to set role "pg_read_all_settings" SET ROLE pg_read_all_stats; -- fail, granted without SET option ERROR: permission denied to set role "pg_read_all_stats" RESET ROLE; RESET SESSION AUTHORIZATION; -- test interaction of SET SESSION AUTHORIZATION and SET ROLE, -- as well as propagation of these settings to parallel workers GRANT regress_priv_user9 TO regress_priv_user8; SET SESSION AUTHORIZATION regress_priv_user8; SET ROLE regress_priv_user9; SET debug_parallel_query = 0; SELECT session_user, current_role, current_user, current_setting('role') as role; session_user | current_role | current_user | role --------------------+--------------------+--------------------+----------------- regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9 (1 row) SET debug_parallel_query = 1; SELECT session_user, current_role, current_user, current_setting('role') as role; session_user | current_role | current_user | role --------------------+--------------------+--------------------+----------------- regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9 (1 row) BEGIN; SET SESSION AUTHORIZATION regress_priv_user10; SET debug_parallel_query = 0; SELECT session_user, current_role, current_user, current_setting('role') as role; session_user | current_role | current_user | role ---------------------+---------------------+---------------------+------ regress_priv_user10 | regress_priv_user10 | regress_priv_user10 | none (1 row) SET debug_parallel_query = 1; SELECT session_user, current_role, current_user, current_setting('role') as role; session_user | current_role | current_user | role ---------------------+---------------------+---------------------+------ regress_priv_user10 | regress_priv_user10 | regress_priv_user10 | none (1 row) ROLLBACK; SET debug_parallel_query = 0; SELECT session_user, current_role, current_user, current_setting('role') as role; session_user | current_role | current_user | role --------------------+--------------------+--------------------+----------------- regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9 (1 row) SET debug_parallel_query = 1; SELECT session_user, current_role, current_user, current_setting('role') as role; session_user | current_role | current_user | role --------------------+--------------------+--------------------+----------------- regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9 (1 row) RESET SESSION AUTHORIZATION; -- session_user at this point is installation-dependent SET debug_parallel_query = 0; SELECT session_user = current_role as c_r_ok, session_user = current_user as c_u_ok, curren c_r_ok | c_u_ok | role --------+--------+------ t | t | none (1 row) SET debug_parallel_query = 1; SELECT session_user = current_role as c_r_ok, session_user = current_user as c_u_ok, curren c_r_ok | c_u_ok | role --------+--------+------ t | t | none (1 row) RESET debug_parallel_query; REVOKE pg_read_all_settings FROM regress_priv_user8; DROP USER regress_priv_user10; DROP USER regress_priv_user9; DROP USER regress_priv_user8; CREATE GROUP regress_priv_group1; CREATE GROUP regress_priv_group2 WITH ADMIN regress_priv_user1 USER regress_priv_user2; ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4; GRANT regress_priv_group2 TO regress_priv_user2 GRANTED BY regress_priv_user1; SET SESSION AUTHORIZATION regress_priv_user3; ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- fail ERROR: permission denied to alter role DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may add or drop members ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; -- fail ERROR: permission denied to alter role DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may add or drop members SET SESSION AUTHORIZATION regress_priv_user1; ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; NOTICE: role "regress_priv_user2" has already been granted membership in role "regress_pri ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate NOTICE: role "regress_priv_user2" has already been granted membership in role "regress_pri ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; ALTER USER regress_priv_user2 PASSWORD 'verysecret'; -- not permitted ERROR: permission denied to alter role DETAIL: To change another role's password, the current user must have the CREATEROLE attribut RESET SESSION AUTHORIZATION; ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2; REVOKE ADMIN OPTION FOR regress_priv_group2 FROM regress_priv_user1; GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION; -- prepare non-leakproof function for later CREATE FUNCTION leak(integer,integer) RETURNS boolean AS 'int4lt' LANGUAGE internal IMMUTABLE STRICT; -- but deliberately not LEAKPROOF ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1; -- test owner privileges GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY regress_priv_r ERROR: permission denied to grant privileges as role "regress_priv_role" DETAIL: The grantor must have the ADMIN option on role "regress_priv_role". GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY CURRENT_ROLE; REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY foo; -- error ERROR: role "foo" does not exist REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY regress_priv WARNING: role "regress_priv_user1" has not been granted membership in role "regress_priv_r REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY CURRENT_USER REVOKE regress_priv_role FROM regress_priv_user1 GRANTED BY CURRENT_ROLE; DROP ROLE regress_priv_role; SET SESSION AUTHORIZATION regress_priv_user1; SELECT session_user, current_user; session_user | current_user --------------------+-------------------- regress_priv_user1 | regress_priv_user1 (1 row) CREATE TABLE atest1 ( a int, b text ); SELECT * FROM atest1; a | b ---+--- (0 rows) INSERT INTO atest1 VALUES (1, 'one'); DELETE FROM atest1; UPDATE atest1 SET a = 1 WHERE b = 'blech'; TRUNCATE atest1; BEGIN; LOCK atest1 IN ACCESS EXCLUSIVE MODE; COMMIT; REVOKE ALL ON atest1 FROM PUBLIC; SELECT * FROM atest1; a | b ---+--- (0 rows) GRANT ALL ON atest1 TO regress_priv_user2; GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4; SELECT * FROM atest1; a | b ---+--- (0 rows) CREATE TABLE atest2 (col1 varchar(10), col2 boolean); SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid, 0); pg_get_acl ------------ (1 row) GRANT SELECT ON atest2 TO regress_priv_user2; GRANT UPDATE ON atest2 TO regress_priv_user3; GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER; GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE; SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid, 0)); unnest ------------------------------------------------ regress_priv_user1=arwdDxtm/regress_priv_user1 regress_priv_user2=r/regress_priv_user1 regress_priv_user3=w/regress_priv_user1 regress_priv_user4=a/regress_priv_user1 regress_priv_user5=D/regress_priv_user1 (5 rows) -- Invalid inputs SELECT pg_get_acl('pg_class'::regclass, 0, 0); -- null pg_get_acl ------------ (1 row) SELECT pg_get_acl(0, 0, 0); -- null pg_get_acl ------------ (1 row) GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5; -- error ERROR: grantor must be current user SET SESSION AUTHORIZATION regress_priv_user2; SELECT session_user, current_user; session_user | current_user --------------------+-------------------- regress_priv_user2 | regress_priv_user2 (1 row) -- try various combinations of queries on atest1 and atest2 SELECT * FROM atest1; -- ok a | b ---+--- (0 rows) SELECT * FROM atest2; -- ok col1 | col2 ------+------ (0 rows) INSERT INTO atest1 VALUES (2, 'two'); -- ok INSERT INTO atest2 VALUES ('foo', true); -- fail ERROR: permission denied for table atest2 INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok UPDATE atest1 SET a = 1 WHERE a = 2; -- ok UPDATE atest2 SET col2 = NOT col2; -- fail ERROR: permission denied for table atest2 SELECT * FROM atest1 FOR UPDATE; -- ok a | b ---+----- 1 | two 1 | two (2 rows) SELECT * FROM atest2 FOR UPDATE; -- fail ERROR: permission denied for table atest2 DELETE FROM atest2; -- fail ERROR: permission denied for table atest2 TRUNCATE atest2; -- fail ERROR: permission denied for table atest2 BEGIN; LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail ERROR: permission denied for table atest2 COMMIT; COPY atest2 FROM stdin; -- fail ERROR: permission denied for table atest2 GRANT ALL ON atest1 TO PUBLIC; -- fail WARNING: no privileges were granted for "atest1" -- checks in subquery, both ok SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) ); a | b ---+--- (0 rows) SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) ); col1 | col2 ------+------ (0 rows) SET SESSION AUTHORIZATION regress_priv_user6; SELECT * FROM atest1; -- ok a | b ---+----- 1 | two 1 | two (2 rows) SELECT * FROM atest2; -- ok col1 | col2 ------+------ (0 rows) INSERT INTO atest2 VALUES ('foo', true); -- fail ERROR: permission denied for table atest2 SET SESSION AUTHORIZATION regress_priv_user7; SELECT * FROM atest1; -- fail ERROR: permission denied for table atest1 SELECT * FROM atest2; -- fail ERROR: permission denied for table atest2 INSERT INTO atest2 VALUES ('foo', true); -- ok UPDATE atest2 SET col2 = true; -- ok DELETE FROM atest2; -- ok -- Make sure we are not able to modify system catalogs UPDATE pg_catalog.pg_class SET relname = '123'; -- fail ERROR: permission denied for table pg_class DELETE FROM pg_catalog.pg_class; -- fail ERROR: permission denied for table pg_class UPDATE pg_toast.pg_toast_1213 SET chunk_id = 1; -- fail ERROR: permission denied for table pg_toast_1213 SET SESSION AUTHORIZATION regress_priv_user3; SELECT session_user, current_user; session_user | current_user --------------------+-------------------- regress_priv_user3 | regress_priv_user3 (1 row) SELECT * FROM atest1; -- ok a | b ---+----- 1 | two 1 | two (2 rows) SELECT * FROM atest2; -- fail ERROR: permission denied for table atest2 INSERT INTO atest1 VALUES (2, 'two'); -- fail ERROR: permission denied for table atest1 INSERT INTO atest2 VALUES ('foo', true); -- fail ERROR: permission denied for table atest2 INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail ERROR: permission denied for table atest1 UPDATE atest1 SET a = 1 WHERE a = 2; -- fail ERROR: permission denied for table atest1 UPDATE atest2 SET col2 = NULL; -- ok UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2 ERROR: permission denied for table atest2 UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok SELECT * FROM atest1 FOR UPDATE; -- fail ERROR: permission denied for table atest1 SELECT * FROM atest2 FOR UPDATE; -- fail ERROR: permission denied for table atest2 DELETE FROM atest2; -- fail ERROR: permission denied for table atest2 TRUNCATE atest2; -- fail ERROR: permission denied for table atest2 BEGIN; LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok COMMIT; COPY atest2 FROM stdin; -- fail ERROR: permission denied for table atest2 -- checks in subquery, both fail SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) ); ERROR: permission denied for table atest2 SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) ); ERROR: permission denied for table atest2 SET SESSION AUTHORIZATION regress_priv_user4; COPY atest2 FROM stdin; -- ok SELECT * FROM atest1; -- ok a | b ---+----- 1 | two 1 | two (2 rows) -- test leaky-function protections in selfuncs -- regress_priv_user1 will own a table and provide views for it. SET SESSION AUTHORIZATION regress_priv_user1; CREATE TABLE atest12 as SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x; CREATE INDEX ON atest12 (a); CREATE INDEX ON atest12 (abs(a)); -- results below depend on having quite accurate stats for atest12, so... ALTER TABLE atest12 SET (autovacuum_enabled = off); SET default_statistics_target = 10000; VACUUM ANALYZE atest12; RESET default_statistics_target; CREATE OPERATOR <<< (procedure = leak, leftarg = integer, rightarg = integer, restrict = scalarltsel); -- views with leaky operator CREATE VIEW atest12v AS SELECT * FROM atest12 WHERE b <<< 5; CREATE VIEW atest12sbv WITH (security_barrier=true) AS SELECT * FROM atest12 WHERE b <<< 5; -- This plan should use nestloop, knowing that few rows will be selected. EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; QUERY PLAN ------------------------------------------------- Nested Loop -> Seq Scan on atest12 atest12_1 Filter: (b <<< 5) -> Index Scan using atest12_a_idx on atest12 Index Cond: (a = atest12_1.b) Filter: (b <<< 5) (6 rows) -- And this one. EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y WHERE x.a = y.b and abs(y.a) <<< 5; QUERY PLAN --------------------------------------------------- Nested Loop -> Seq Scan on atest12 y Filter: (abs(a) <<< 5) -> Index Scan using atest12_a_idx on atest12 x Index Cond: (a = y.b) (5 rows) -- This should also be a nestloop, but the security barrier forces the inner -- scan to be materialized EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; QUERY PLAN ------------------------------------------- Nested Loop Join Filter: (atest12.a = atest12_1.b) -> Seq Scan on atest12 Filter: (b <<< 5) -> Materialize -> Seq Scan on atest12 atest12_1 Filter: (b <<< 5) (7 rows) -- Check if regress_priv_user2 can break security. SET SESSION AUTHORIZATION regress_priv_user2; CREATE FUNCTION leak2(integer,integer) RETURNS boolean AS $$begin raise notice 'leak % %', $1, $2; return $1 > $2; end$$ LANGUAGE plpgsql immutable; CREATE OPERATOR >>> (procedure = leak2, leftarg = integer, rightarg = integer, restrict = scalargtsel); -- These should not show any "leak" notices before failing. EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0; ERROR: permission denied for table atest12 EXPLAIN (COSTS OFF) SELECT * FROM atest12v WHERE a >>> 0; ERROR: permission denied for view atest12v EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv WHERE a >>> 0; ERROR: permission denied for view atest12sbv -- Now regress_priv_user1 grants access to regress_priv_user2 via the views. SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT ON atest12v TO PUBLIC; GRANT SELECT ON atest12sbv TO PUBLIC; SET SESSION AUTHORIZATION regress_priv_user2; -- These plans should continue to use a nestloop, since they execute with the -- privileges of the view owner. EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; QUERY PLAN ------------------------------------------------- Nested Loop -> Seq Scan on atest12 atest12_1 Filter: (b <<< 5) -> Index Scan using atest12_a_idx on atest12 Index Cond: (a = atest12_1.b) Filter: (b <<< 5) (6 rows) EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b; QUERY PLAN ------------------------------------------- Nested Loop Join Filter: (atest12.a = atest12_1.b) -> Seq Scan on atest12 Filter: (b <<< 5) -> Materialize -> Seq Scan on atest12 atest12_1 Filter: (b <<< 5) (7 rows) -- A non-security barrier view does not guard against information leakage. EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b and abs(y.a) <<< 5; QUERY PLAN ------------------------------------------------- Nested Loop -> Seq Scan on atest12 atest12_1 Filter: ((b <<< 5) AND (abs(a) <<< 5)) -> Index Scan using atest12_a_idx on atest12 Index Cond: (a = atest12_1.b) Filter: (b <<< 5) (6 rows) -- But a security barrier view isolates the leaky operator. EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b and abs(y.a) <<< 5; QUERY PLAN ------------------------------------- Nested Loop Join Filter: (atest12_1.a = y.b) -> Subquery Scan on y Filter: (abs(y.a) <<< 5) -> Seq Scan on atest12 Filter: (b <<< 5) -> Seq Scan on atest12 atest12_1 Filter: (b <<< 5) (8 rows) -- Now regress_priv_user1 grants sufficient access to regress_priv_user2. SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT (a, b) ON atest12 TO PUBLIC; SET SESSION AUTHORIZATION regress_priv_user2; -- regress_priv_user2 should continue to get a good row estimate. EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b; QUERY PLAN ------------------------------------------------- Nested Loop -> Seq Scan on atest12 atest12_1 Filter: (b <<< 5) -> Index Scan using atest12_a_idx on atest12 Index Cond: (a = atest12_1.b) Filter: (b <<< 5) (6 rows) -- But not for this, due to lack of table-wide permissions needed -- to make use of the expression index's statistics. EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y WHERE x.a = y.b and abs(y.a) <<< 5; QUERY PLAN -------------------------------------- Hash Join Hash Cond: (x.a = y.b) -> Seq Scan on atest12 x -> Hash -> Seq Scan on atest12 y Filter: (abs(a) <<< 5) (6 rows) -- clean up (regress_priv_user1's objects are all dropped later) DROP FUNCTION leak2(integer, integer) CASCADE; NOTICE: drop cascades to operator >>>(integer,integer) -- groups SET SESSION AUTHORIZATION regress_priv_user3; CREATE TABLE atest3 (one int, two int, three int); GRANT DELETE ON atest3 TO GROUP regress_priv_group2; SET SESSION AUTHORIZATION regress_priv_user1; SELECT * FROM atest3; -- fail ERROR: permission denied for table atest3 DELETE FROM atest3; -- ok BEGIN; RESET SESSION AUTHORIZATION; ALTER ROLE regress_priv_user1 NOINHERIT; SET SESSION AUTHORIZATION regress_priv_user1; SAVEPOINT s1; DELETE FROM atest3; -- ok because grant-level option is unchanged ROLLBACK TO s1; RESET SESSION AUTHORIZATION; GRANT regress_priv_group2 TO regress_priv_user1 WITH INHERIT FALSE; SET SESSION AUTHORIZATION regress_priv_user1; DELETE FROM atest3; -- fail ERROR: permission denied for table atest3 ROLLBACK TO s1; RESET SESSION AUTHORIZATION; REVOKE INHERIT OPTION FOR regress_priv_group2 FROM regress_priv_user1; SET SESSION AUTHORIZATION regress_priv_user1; DELETE FROM atest3; -- also fail ERROR: permission denied for table atest3 ROLLBACK; -- views SET SESSION AUTHORIZATION regress_priv_user3; CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok /* The next *should* fail, but it's not implemented that way yet. */ CREATE VIEW atestv2 AS SELECT * FROM atest2; CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok /* Empty view is a corner case that failed in 9.2. */ CREATE VIEW atestv0 AS SELECT 0 as x WHERE false; -- ok SELECT * FROM atestv1; -- ok a | b ---+----- 1 | two 1 | two (2 rows) SELECT * FROM atestv2; -- fail ERROR: permission denied for table atest2 GRANT SELECT ON atestv1, atestv3 TO regress_priv_user4; GRANT SELECT ON atestv2 TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user4; SELECT * FROM atestv1; -- ok a | b ---+----- 1 | two 1 | two (2 rows) SELECT * FROM atestv2; -- fail ERROR: permission denied for view atestv2 SELECT * FROM atestv3; -- ok one | two | three -----+-----+------- (0 rows) SELECT * FROM atestv0; -- fail ERROR: permission denied for view atestv0 -- Appendrels excluded by constraints failed to check permissions in 8.4-9.2. select * from ((select a.q1 as x from int8_tbl a offset 0) union all (select b.q2 as x from int8_tbl b offset 0)) ss where false; ERROR: permission denied for table int8_tbl set constraint_exclusion = on; select * from ((select a.q1 as x, random() from int8_tbl a where q1 > 0) union all (select b.q2 as x, random() from int8_tbl b where q2 > 0)) ss where x < 0; ERROR: permission denied for table int8_tbl reset constraint_exclusion; CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view SELECT * FROM atestv4; -- ok one | two | three -----+-----+------- (0 rows) GRANT SELECT ON atestv4 TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; -- Two complex cases: SELECT * FROM atestv3; -- fail ERROR: permission denied for view atestv3 SELECT * FROM atestv4; -- ok (even though regress_priv_user2 cannot access underlying atestv one | two | three -----+-----+------- (0 rows) SELECT * FROM atest2; -- ok col1 | col2 ------+------ bar | t (1 row) SELECT * FROM atestv2; -- fail (even though regress_priv_user2 can access underlying atest2) ERROR: permission denied for table atest2 -- Test column level permissions SET SESSION AUTHORIZATION regress_priv_user1; CREATE TABLE atest5 (one int, two int unique, three int, four int unique); CREATE TABLE atest6 (one int, two int, blue int); GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regress_priv_user4; GRANT ALL (one) ON atest5 TO regress_priv_user3; SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 1)); unnest -------------------------------------------- regress_priv_user4=r/regress_priv_user1 regress_priv_user3=arwx/regress_priv_user1 (2 rows) SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 2)); unnest ----------------------------------------- regress_priv_user4=a/regress_priv_user1 (1 row) SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 3)); unnest ----------------------------------------- regress_priv_user4=w/regress_priv_user1 (1 row) SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 4)); unnest -------- (0 rows) INSERT INTO atest5 VALUES (1,2,3); SET SESSION AUTHORIZATION regress_priv_user4; SELECT * FROM atest5; -- fail ERROR: permission denied for table atest5 SELECT one FROM atest5; -- ok one ----- 1 (1 row) COPY atest5 (one) TO stdout; -- ok 1 SELECT two FROM atest5; -- fail ERROR: permission denied for table atest5 COPY atest5 (two) TO stdout; -- fail ERROR: permission denied for table atest5 SELECT atest5 FROM atest5; -- fail ERROR: permission denied for table atest5 COPY atest5 (one,two) TO stdout; -- fail ERROR: permission denied for table atest5 SELECT 1 FROM atest5; -- ok ?column? ---------- 1 (1 row) SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok ?column? ---------- 1 (1 row) SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail ERROR: permission denied for table atest5 SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail ERROR: permission denied for table atest5 SELECT * FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail ERROR: permission denied for table atest5 SELECT j.* FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail ERROR: permission denied for table atest5 SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail ERROR: permission denied for table atest5 SELECT one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok one ----- 1 (1 row) SELECT j.one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok one ----- 1 (1 row) SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail ERROR: permission denied for table atest5 SELECT j.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail ERROR: permission denied for table atest5 SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail ERROR: permission denied for table atest5 SELECT j.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail ERROR: permission denied for table atest5 SELECT * FROM (atest5 a JOIN atest5 b USING (one)); -- fail ERROR: permission denied for table atest5 SELECT a.* FROM (atest5 a JOIN atest5 b USING (one)); -- fail ERROR: permission denied for table atest5 SELECT (a.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)); -- fail ERROR: permission denied for table atest5 SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail ERROR: permission denied for table atest5 SELECT a.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail ERROR: permission denied for table atest5 SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail ERROR: permission denied for table atest5 SELECT b.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail ERROR: permission denied for table atest5 SELECT y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail ERROR: permission denied for table atest5 SELECT b.y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail ERROR: permission denied for table atest5 SELECT y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail ERROR: permission denied for table atest5 SELECT b.y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail ERROR: permission denied for table atest5 SELECT 1 FROM atest5 WHERE two = 2; -- fail ERROR: permission denied for table atest5 SELECT * FROM atest1, atest5; -- fail ERROR: permission denied for table atest5 SELECT atest1.* FROM atest1, atest5; -- ok a | b ---+----- 1 | two 1 | two (2 rows) SELECT atest1.*,atest5.one FROM atest1, atest5; -- ok a | b | one ---+-----+----- 1 | two | 1 1 | two | 1 (2 rows) SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two); -- fail ERROR: permission denied for table atest5 SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- ok a | b | one ---+-----+----- 1 | two | 1 1 | two | 1 (2 rows) SELECT one, two FROM atest5; -- fail ERROR: permission denied for table atest5 SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT (one,two) ON atest6 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still ERROR: permission denied for table atest5 SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT (two) ON atest5 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now one | two -----+----- (0 rows) -- test column-level privileges for INSERT and UPDATE INSERT INTO atest5 (two) VALUES (3); -- ok COPY atest5 FROM stdin; -- fail ERROR: permission denied for table atest5 COPY atest5 (two) FROM stdin; -- ok INSERT INTO atest5 (three) VALUES (4); -- fail ERROR: permission denied for table atest5 INSERT INTO atest5 VALUES (5,5,5); -- fail ERROR: permission denied for table atest5 UPDATE atest5 SET three = 10; -- ok UPDATE atest5 SET one = 8; -- fail ERROR: permission denied for table atest5 UPDATE atest5 SET three = 5, one = 2; -- fail ERROR: permission denied for table atest5 -- Check that column level privs are enforced in RETURNING -- Ok. INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10; -- Error. No SELECT on column three. INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5. ERROR: permission denied for table atest5 -- Ok. May SELECT on column "one": INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5. one ----- (1 row) -- Check that column level privileges are enforced for EXCLUDED -- Ok. we may select one INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one; -- Error. No select rights on three INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three; ERROR: permission denied for table atest5 INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDAT ERROR: permission denied for table atest5 INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to ERROR: permission denied for table atest5 -- Check that the columns in the inference require select privileges INSERT INTO atest5(four) VALUES (4); -- fail ERROR: permission denied for table atest5 SET SESSION AUTHORIZATION regress_priv_user1; GRANT INSERT (four) ON atest5 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- fails (due to S ERROR: permission denied for table atest5 INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set t ERROR: permission denied for table atest5 INSERT INTO atest5(four) VALUES (4); -- ok SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT (four) ON atest5 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- ok INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set t SET SESSION AUTHORIZATION regress_priv_user1; REVOKE ALL (one) ON atest5 FROM regress_priv_user4; GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; SELECT one FROM atest5; -- fail ERROR: permission denied for table atest5 UPDATE atest5 SET one = 1; -- fail ERROR: permission denied for table atest5 SELECT atest6 FROM atest6; -- ok atest6 -------- (0 rows) COPY atest6 TO stdout; -- ok -- test column privileges with MERGE SET SESSION AUTHORIZATION regress_priv_user1; CREATE TABLE mtarget (a int, b text); CREATE TABLE msource (a int, b text); INSERT INTO mtarget VALUES (1, 'init1'), (2, 'init2'); INSERT INTO msource VALUES (1, 'source1'), (2, 'source2'), (3, 'source3'); GRANT SELECT (a) ON msource TO regress_priv_user4; GRANT SELECT (a) ON mtarget TO regress_priv_user4; GRANT INSERT (a,b) ON mtarget TO regress_priv_user4; GRANT UPDATE (b) ON mtarget TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; -- -- test source privileges -- -- fail (no SELECT priv on s.b) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED THEN INSERT VALUES (a, NULL); ERROR: permission denied for table msource -- fail (s.b used in the INSERTed values) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = 'x' WHEN NOT MATCHED THEN INSERT VALUES (a, b); ERROR: permission denied for table msource -- fail (s.b used in the WHEN quals) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED AND s.b = 'x' THEN UPDATE SET b = 'x' WHEN NOT MATCHED THEN INSERT VALUES (a, NULL); ERROR: permission denied for table msource -- this should be ok since only s.a is accessed BEGIN; MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = 'ok' WHEN NOT MATCHED THEN INSERT VALUES (a, NULL); ROLLBACK; SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT (b) ON msource TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; -- should now be ok BEGIN; MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b WHEN NOT MATCHED THEN INSERT VALUES (a, b); ROLLBACK; -- -- test target privileges -- -- fail (no SELECT priv on t.b) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = t.b WHEN NOT MATCHED THEN INSERT VALUES (a, NULL); ERROR: permission denied for table mtarget -- fail (no UPDATE on t.a) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b, a = t.a + 1 WHEN NOT MATCHED THEN INSERT VALUES (a, b); ERROR: permission denied for table mtarget -- fail (no SELECT on t.b) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED AND t.b IS NOT NULL THEN UPDATE SET b = s.b WHEN NOT MATCHED THEN INSERT VALUES (a, b); ERROR: permission denied for table mtarget -- ok BEGIN; MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED THEN UPDATE SET b = s.b; ROLLBACK; -- fail (no DELETE) MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED AND t.b IS NOT NULL THEN DELETE; ERROR: permission denied for table mtarget -- grant delete privileges SET SESSION AUTHORIZATION regress_priv_user1; GRANT DELETE ON mtarget TO regress_priv_user4; -- should be ok now BEGIN; MERGE INTO mtarget t USING msource s ON t.a = s.a WHEN MATCHED AND t.b IS NOT NULL THEN DELETE; ROLLBACK; -- check error reporting with column privs SET SESSION AUTHORIZATION regress_priv_user1; CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2)); GRANT SELECT (c1) ON t1 TO regress_priv_user2; GRANT INSERT (c1, c2, c3) ON t1 TO regress_priv_user2; GRANT UPDATE (c1, c2, c3) ON t1 TO regress_priv_user2; -- seed data INSERT INTO t1 VALUES (1, 1, 1); INSERT INTO t1 VALUES (1, 2, 1); INSERT INTO t1 VALUES (2, 1, 2); INSERT INTO t1 VALUES (2, 2, 2); INSERT INTO t1 VALUES (3, 1, 3); SET SESSION AUTHORIZATION regress_priv_user2; INSERT INTO t1 (c1, c2) VALUES (1, 1); -- fail, but row not shown ERROR: duplicate key value violates unique constraint "t1_pkey" UPDATE t1 SET c2 = 1; -- fail, but row not shown ERROR: duplicate key value violates unique constraint "t1_pkey" INSERT INTO t1 (c1, c2) VALUES (null, null); -- fail, but see columns being inserted ERROR: null value in column "c1" of relation "t1" violates not-null constraint DETAIL: Failing row contains (c1, c2) = (null, null). INSERT INTO t1 (c3) VALUES (null); -- fail, but see columns being inserted or have SELECT ERROR: null value in column "c1" of relation "t1" violates not-null constraint DETAIL: Failing row contains (c1, c3) = (null, null). INSERT INTO t1 (c1) VALUES (5); -- fail, but see columns being inserted or have SELECT ERROR: null value in column "c2" of relation "t1" violates not-null constraint DETAIL: Failing row contains (c1) = (5). UPDATE t1 SET c3 = 10; -- fail, but see columns with SELECT rights, or being modified ERROR: new row for relation "t1" violates check constraint "t1_c3_check" DETAIL: Failing row contains (c1, c3) = (1, 10). SET SESSION AUTHORIZATION regress_priv_user1; DROP TABLE t1; -- check error reporting with column privs on a partitioned table CREATE TABLE errtst(a text, b text NOT NULL, c text, secret1 text, secret2 text) PARTITION BY LI CREATE TABLE errtst_part_1(secret2 text, c text, a text, b text NOT NULL, secret1 text); CREATE TABLE errtst_part_2(secret1 text, secret2 text, a text, c text, b text NOT NULL); ALTER TABLE errtst ATTACH PARTITION errtst_part_1 FOR VALUES IN ('aaa'); ALTER TABLE errtst ATTACH PARTITION errtst_part_2 FOR VALUES IN ('aaaa'); GRANT SELECT (a, b, c) ON TABLE errtst TO regress_priv_user2; GRANT UPDATE (a, b, c) ON TABLE errtst TO regress_priv_user2; GRANT INSERT (a, b, c) ON TABLE errtst TO regress_priv_user2; INSERT INTO errtst_part_1 (a, b, c, secret1, secret2) VALUES ('aaa', 'bbb', 'ccc', 'the body', 'is in the attic'); SET SESSION AUTHORIZATION regress_priv_user2; -- Perform a few updates that violate the NOT NULL constraint. Make sure -- the error messages don't leak the secret fields. -- simple insert. INSERT INTO errtst (a, b) VALUES ('aaa', NULL); ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint DETAIL: Failing row contains (a, b, c) = (aaa, null, null). -- simple update. UPDATE errtst SET b = NULL; ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint DETAIL: Failing row contains (a, b, c) = (aaa, null, ccc). -- partitioning key is updated, doesn't move the row. UPDATE errtst SET a = 'aaa', b = NULL; ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint DETAIL: Failing row contains (a, b, c) = (aaa, null, ccc). -- row is moved to another partition. UPDATE errtst SET a = 'aaaa', b = NULL; ERROR: null value in column "b" of relation "errtst_part_2" violates not-null constraint DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). -- row is moved to another partition. This differs from the previous case in -- that the new partition is excluded by constraint exclusion, so its -- ResultRelInfo is not created at ExecInitModifyTable, but needs to be -- constructed on the fly when the updated tuple is routed to it. UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa'; ERROR: null value in column "b" of relation "errtst_part_2" violates not-null constraint DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc). SET SESSION AUTHORIZATION regress_priv_user1; DROP TABLE errtst; -- test column-level privileges when involved with DELETE SET SESSION AUTHORIZATION regress_priv_user1; ALTER TABLE atest6 ADD COLUMN three integer; GRANT DELETE ON atest5 TO regress_priv_user3; GRANT SELECT (two) ON atest5 TO regress_priv_user3; REVOKE ALL (one) ON atest5 FROM regress_priv_user3; GRANT SELECT (one) ON atest5 TO regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; SELECT atest6 FROM atest6; -- fail ERROR: permission denied for table atest6 SELECT one FROM atest5 NATURAL JOIN atest6; -- fail ERROR: permission denied for table atest5 SET SESSION AUTHORIZATION regress_priv_user1; ALTER TABLE atest6 DROP COLUMN three; SET SESSION AUTHORIZATION regress_priv_user4; SELECT atest6 FROM atest6; -- ok atest6 -------- (0 rows) SELECT one FROM atest5 NATURAL JOIN atest6; -- ok one ----- (0 rows) SET SESSION AUTHORIZATION regress_priv_user1; ALTER TABLE atest6 DROP COLUMN two; REVOKE SELECT (one,blue) ON atest6 FROM regress_priv_user4; SET SESSION AUTHORIZATION regress_priv_user4; SELECT * FROM atest6; -- fail ERROR: permission denied for table atest6 SELECT 1 FROM atest6; -- fail ERROR: permission denied for table atest6 SET SESSION AUTHORIZATION regress_priv_user3; DELETE FROM atest5 WHERE one = 1; -- fail ERROR: permission denied for table atest5 DELETE FROM atest5 WHERE two = 2; -- ok -- check inheritance cases SET SESSION AUTHORIZATION regress_priv_user1; CREATE TABLE atestp1 (f1 int, f2 int); CREATE TABLE atestp2 (fx int, fy int); CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2); GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2; GRANT SELECT(fx) ON atestc TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; SELECT fx FROM atestp2; -- ok fx ---- (0 rows) SELECT fy FROM atestp2; -- ok fy ---- (0 rows) SELECT atestp2 FROM atestp2; -- ok atestp2 --------- (0 rows) SELECT tableoid FROM atestp2; -- ok tableoid ---------- (0 rows) SELECT fy FROM atestc; -- fail ERROR: permission denied for table atestc SET SESSION AUTHORIZATION regress_priv_user1; GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; SELECT fx FROM atestp2; -- still ok fx ---- (0 rows) SELECT fy FROM atestp2; -- ok fy ---- (0 rows) SELECT atestp2 FROM atestp2; -- ok atestp2 --------- (0 rows) SELECT tableoid FROM atestp2; -- ok tableoid ---------- (0 rows) -- child's permissions do not apply when operating on parent SET SESSION AUTHORIZATION regress_priv_user1; REVOKE ALL ON atestc FROM regress_priv_user2; GRANT ALL ON atestp1 TO regress_priv_user2; SET SESSION AUTHORIZATION regress_priv_user2; SELECT f2 FROM atestp1; -- ok f2 ---- (0 rows) SELECT f2 FROM atestc; -- fail ERROR: permission denied for table atestc DELETE FROM atestp1; -- ok DELETE FROM atestc; -- fail ERROR: permission denied for table atestc UPDATE atestp1 SET f1 = 1; -- ok UPDATE atestc SET f1 = 1; -- fail ERROR: permission denied for table atestc TRUNCATE atestp1; -- ok TRUNCATE atestc; -- fail ERROR: permission denied for table atestc BEGIN; LOCK atestp1; END; BEGIN; LOCK atestc; ERROR: permission denied for table atestc END; -- privileges on functions, languages -- switch to superuser \c - REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC; GRANT USAGE ON LANGUAGE sql TO regress_priv_user1; -- ok GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail ERROR: language "c" is not trusted DETAIL: GRANT and REVOKE are not allowed on untrusted languages, because only superusers can u SET SESSION AUTHORIZATION regress_priv_user1; GRANT USAGE ON LANGUAGE sql TO regress_priv_user2; -- fail WARNING: no privileges were granted for "sql" CREATE FUNCTION priv_testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; CREATE FUNCTION priv_testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; CREATE AGGREGATE priv_testagg1(int) (sfunc = int4pl, stype = int4); CREATE PROCEDURE priv_testproc1(int) AS 'select $1;' LANGUAGE sql; REVOKE ALL ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) FRO GRANT EXECUTE ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) REVOKE ALL ON FUNCTION priv_testproc1(int) FROM PUBLIC; -- fail, not a function ERROR: priv_testproc1(integer) is not a function REVOKE ALL ON PROCEDURE priv_testproc1(int) FROM PUBLIC; GRANT EXECUTE ON PROCEDURE priv_testproc1(int) TO regress_priv_user2; GRANT USAGE ON FUNCTION priv_testfunc1(int) TO regress_priv_user3; -- semantic error ERROR: invalid privilege type USAGE for function GRANT USAGE ON FUNCTION priv_testagg1(int) TO regress_priv_user3; -- semantic error ERROR: invalid privilege type USAGE for function GRANT USAGE ON PROCEDURE priv_testproc1(int) TO regress_priv_user3; -- semantic error ERROR: invalid privilege type USAGE for procedure GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc1(int) TO regress_priv_user4; GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc_nosuch(int) TO regress_priv_user4; ERROR: function priv_testfunc_nosuch(integer) does not exist GRANT ALL PRIVILEGES ON FUNCTION priv_testagg1(int) TO regress_priv_user4; GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4; CREATE FUNCTION priv_testfunc4(boolean) RETURNS text AS 'select col1 from atest2 where col2 = $1;' LANGUAGE sql SECURITY DEFINER; GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3; SET SESSION AUTHORIZATION regress_priv_user2; SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok priv_testfunc1 | priv_testfunc2 ----------------+---------------- 10 | 15 (1 row) CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail ERROR: permission denied for language sql SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok priv_testagg1 --------------- 6 (1 row) CALL priv_testproc1(6); -- ok SET SESSION AUTHORIZATION regress_priv_user3; SELECT priv_testfunc1(5); -- fail ERROR: permission denied for function priv_testfunc1 SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail ERROR: permission denied for aggregate priv_testagg1 CALL priv_testproc1(6); -- fail ERROR: permission denied for procedure priv_testproc1 SELECT col1 FROM atest2 WHERE col2 = true; -- fail ERROR: permission denied for table atest2 SELECT priv_testfunc4(true); -- ok priv_testfunc4 ---------------- bar (1 row) SET SESSION AUTHORIZATION regress_priv_user4; SELECT priv_testfunc1(5); -- ok priv_testfunc1 ---------------- 10 (1 row) SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok priv_testagg1 --------------- 6 (1 row) CALL priv_testproc1(6); -- ok DROP FUNCTION priv_testfunc1(int); -- fail ERROR: must be owner of function priv_testfunc1 DROP AGGREGATE priv_testagg1(int); -- fail ERROR: must be owner of aggregate priv_testagg1 DROP PROCEDURE priv_testproc1(int); -- fail ERROR: must be owner of procedure priv_testproc1 \c - DROP FUNCTION priv_testfunc1(int); -- ok -- restore to sanity GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC; -- verify privilege checks on array-element coercions BEGIN; SELECT '{1}'::int4[]::int8[]; int8 ------ {1} (1 row) REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC; SELECT '{1}'::int4[]::int8[]; --superuser, succeed int8 ------ {1} (1 row) SET SESSION AUTHORIZATION regress_priv_user4; SELECT '{1}'::int4[]::int8[]; --other user, fail ERROR: permission denied for function int8 ROLLBACK; -- privileges on types -- switch to superuser \c - CREATE TYPE priv_testtype1 AS (a int, b text); REVOKE USAGE ON TYPE priv_testtype1 FROM PUBLIC; GRANT USAGE ON TYPE priv_testtype1 TO regress_priv_user2; GRANT USAGE ON TYPE _priv_testtype1 TO regress_priv_user2; -- fail ERROR: cannot set privileges of array types HINT: Set the privileges of the element type instead. GRANT USAGE ON DOMAIN priv_testtype1 TO regress_priv_user2; -- fail ERROR: "priv_testtype1" is not a domain CREATE DOMAIN priv_testdomain1 AS int; REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC; GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2; GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok SET SESSION AUTHORIZATION regress_priv_user1; -- commands that should fail CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint); ERROR: permission denied for type priv_testdomain1 CREATE DOMAIN priv_testdomain2a AS priv_testdomain1; ERROR: permission denied for type priv_testdomain1 CREATE DOMAIN priv_testdomain3a AS int; CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3a AS $$ SELECT $1::priv_testdomain CREATE CAST (priv_testdomain1 AS priv_testdomain3a) WITH FUNCTION castfunc(int); ERROR: permission denied for type priv_testdomain1 DROP FUNCTION castfunc(int) CASCADE; DROP DOMAIN priv_testdomain3a; CREATE FUNCTION priv_testfunc5a(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $ ERROR: permission denied for type priv_testdomain1 CREATE FUNCTION priv_testfunc6a(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $ ERROR: permission denied for type priv_testdomain1 CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = priv_testdomain1, RIGHTARG = priv_testdo ERROR: permission denied for type priv_testdomain1 CREATE TABLE test5a (a int, b priv_testdomain1); ERROR: permission denied for type priv_testdomain1 CREATE TABLE test6a OF priv_testtype1; ERROR: permission denied for type priv_testtype1 CREATE TABLE test10a (a int[], b priv_testtype1[]); ERROR: permission denied for type priv_testtype1 CREATE TABLE test9a (a int, b int); ALTER TABLE test9a ADD COLUMN c priv_testdomain1; ERROR: permission denied for type priv_testdomain1 ALTER TABLE test9a ALTER COLUMN b TYPE priv_testdomain1; ERROR: permission denied for type priv_testdomain1 CREATE TYPE test7a AS (a int, b priv_testdomain1); ERROR: permission denied for type priv_testdomain1 CREATE TYPE test8a AS (a int, b int); ALTER TYPE test8a ADD ATTRIBUTE c priv_testdomain1; ERROR: permission denied for type priv_testdomain1 ALTER TYPE test8a ALTER ATTRIBUTE b TYPE priv_testdomain1; ERROR: permission denied for type priv_testdomain1 CREATE TABLE test11a AS (SELECT 1::priv_testdomain1 AS a); ERROR: permission denied for type priv_testdomain1 REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC; ERROR: permission denied for type priv_testtype1 SET SESSION AUTHORIZATION regress_priv_user2; -- commands that should succeed CREATE AGGREGATE priv_testagg1b(priv_testdomain1) (sfunc = int4_sum, stype = bigint); CREATE DOMAIN priv_testdomain2b AS priv_testdomain1; CREATE DOMAIN priv_testdomain3b AS int; CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3b AS $$ SELECT $1::priv_testdomain CREATE CAST (priv_testdomain1 AS priv_testdomain3b) WITH FUNCTION castfunc(int); WARNING: cast will be ignored because the source data type is a domain CREATE FUNCTION priv_testfunc5b(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $ CREATE FUNCTION priv_testfunc6b(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $ CREATE OPERATOR !! (PROCEDURE = priv_testfunc5b, RIGHTARG = priv_testdomain1); CREATE TABLE test5b (a int, b priv_testdomain1); CREATE TABLE test6b OF priv_testtype1; CREATE TABLE test10b (a int[], b priv_testtype1[]); CREATE TABLE test9b (a int, b int); ALTER TABLE test9b ADD COLUMN c priv_testdomain1; ALTER TABLE test9b ALTER COLUMN b TYPE priv_testdomain1; CREATE TYPE test7b AS (a int, b priv_testdomain1); CREATE TYPE test8b AS (a int, b int); ALTER TYPE test8b ADD ATTRIBUTE c priv_testdomain1; ALTER TYPE test8b ALTER ATTRIBUTE b TYPE priv_testdomain1; CREATE TABLE test11b AS (SELECT 1::priv_testdomain1 AS a); REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC; WARNING: no privileges could be revoked for "priv_testtype1" \c - DROP AGGREGATE priv_testagg1b(priv_testdomain1); DROP DOMAIN priv_testdomain2b; DROP OPERATOR !! (NONE, priv_testdomain1); DROP FUNCTION priv_testfunc5b(a priv_testdomain1); DROP FUNCTION priv_testfunc6b(b int); DROP TABLE test5b; DROP TABLE test6b; DROP TABLE test9b; DROP TABLE test10b; DROP TYPE test7b; DROP TYPE test8b; DROP CAST (priv_testdomain1 AS priv_testdomain3b); DROP FUNCTION castfunc(int) CASCADE; DROP DOMAIN priv_testdomain3b; DROP TABLE test11b; DROP TYPE priv_testtype1; -- ok DROP DOMAIN priv_testdomain1; -- ok -- truncate SET SESSION AUTHORIZATION regress_priv_user5; TRUNCATE atest2; -- ok TRUNCATE atest3; -- fail ERROR: permission denied for table atest3 -- has_table_privilege function -- bad-input checks select has_table_privilege(NULL,'pg_authid','select'); has_table_privilege --------------------- (1 row) select has_table_privilege('pg_shad','select'); ERROR: relation "pg_shad" does not exist select has_table_privilege('nosuchuser','pg_authid','select'); ERROR: role "nosuchuser" does not exist select has_table_privilege('pg_authid','sel'); ERROR: unrecognized privilege type: "sel" select has_table_privilege(-999999,'pg_authid','update'); has_table_privilege --------------------- f (1 row) select has_table_privilege(1,'select'); has_table_privilege --------------------- (1 row) -- superuser \c - select has_table_privilege(current_user,'pg_authid','select'); has_table_privilege --------------------- t (1 row) select has_table_privilege(current_user,'pg_authid','insert'); has_table_privilege --------------------- t (1 row) select has_table_privilege(t2.oid,'pg_authid','update') from (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- t (1 row) select has_table_privilege(t2.oid,'pg_authid','delete') from (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- t (1 row) select has_table_privilege(current_user,t1.oid,'references') from (select oid from pg_class where relname = 'pg_authid') as t1; has_table_privilege --------------------- t (1 row) select has_table_privilege(t2.oid,t1.oid,'select') from (select oid from pg_class where relname = 'pg_authid') as t1, (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- t (1 row) select has_table_privilege(t2.oid,t1.oid,'insert') from (select oid from pg_class where relname = 'pg_authid') as t1, (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- t (1 row) select has_table_privilege('pg_authid','update'); has_table_privilege --------------------- t (1 row) select has_table_privilege('pg_authid','delete'); has_table_privilege --------------------- t (1 row) select has_table_privilege('pg_authid','truncate'); has_table_privilege --------------------- t (1 row) select has_table_privilege(t1.oid,'select') from (select oid from pg_class where relname = 'pg_authid') as t1; has_table_privilege --------------------- t (1 row) select has_table_privilege(t1.oid,'trigger') from (select oid from pg_class where relname = 'pg_authid') as t1; has_table_privilege --------------------- t (1 row) -- non-superuser SET SESSION AUTHORIZATION regress_priv_user3; select has_table_privilege(current_user,'pg_class','select'); has_table_privilege --------------------- t (1 row) select has_table_privilege(current_user,'pg_class','insert'); has_table_privilege --------------------- f (1 row) select has_table_privilege(t2.oid,'pg_class','update') from (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- f (1 row) select has_table_privilege(t2.oid,'pg_class','delete') from (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- f (1 row) select has_table_privilege(current_user,t1.oid,'references') from (select oid from pg_class where relname = 'pg_class') as t1; has_table_privilege --------------------- f (1 row) select has_table_privilege(t2.oid,t1.oid,'select') from (select oid from pg_class where relname = 'pg_class') as t1, (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- t (1 row) select has_table_privilege(t2.oid,t1.oid,'insert') from (select oid from pg_class where relname = 'pg_class') as t1, (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- f (1 row) select has_table_privilege('pg_class','update'); has_table_privilege --------------------- f (1 row) select has_table_privilege('pg_class','delete'); has_table_privilege --------------------- f (1 row) select has_table_privilege('pg_class','truncate'); has_table_privilege --------------------- f (1 row) select has_table_privilege(t1.oid,'select') from (select oid from pg_class where relname = 'pg_class') as t1; has_table_privilege --------------------- t (1 row) select has_table_privilege(t1.oid,'trigger') from (select oid from pg_class where relname = 'pg_class') as t1; has_table_privilege --------------------- f (1 row) select has_table_privilege(current_user,'atest1','select'); has_table_privilege --------------------- t (1 row) select has_table_privilege(current_user,'atest1','insert'); has_table_privilege --------------------- f (1 row) select has_table_privilege(t2.oid,'atest1','update') from (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- f (1 row) select has_table_privilege(t2.oid,'atest1','delete') from (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- f (1 row) select has_table_privilege(current_user,t1.oid,'references') from (select oid from pg_class where relname = 'atest1') as t1; has_table_privilege --------------------- f (1 row) select has_table_privilege(t2.oid,t1.oid,'select') from (select oid from pg_class where relname = 'atest1') as t1, (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- t (1 row) select has_table_privilege(t2.oid,t1.oid,'insert') from (select oid from pg_class where relname = 'atest1') as t1, (select oid from pg_roles where rolname = current_user) as t2; has_table_privilege --------------------- f (1 row) select has_table_privilege('atest1','update'); has_table_privilege --------------------- f (1 row) select has_table_privilege('atest1','delete'); has_table_privilege --------------------- f (1 row) select has_table_privilege('atest1','truncate'); has_table_privilege --------------------- f (1 row) select has_table_privilege(t1.oid,'select') from (select oid from pg_class where relname = 'atest1') as t1; has_table_privilege --------------------- t (1 row) select has_table_privilege(t1.oid,'trigger') from (select oid from pg_class where relname = 'atest1') as t1; has_table_privilege --------------------- f (1 row) -- has_column_privilege function -- bad-input checks (as non-super-user) select has_column_privilege('pg_authid',NULL,'select'); has_column_privilege ---------------------- (1 row) select has_column_privilege('pg_authid','nosuchcol','select'); ERROR: column "nosuchcol" of relation "pg_authid" does not exist select has_column_privilege(9999,'nosuchcol','select'); has_column_privilege ---------------------- (1 row) select has_column_privilege(9999,99::int2,'select'); has_column_privilege ---------------------- (1 row) select has_column_privilege('pg_authid',99::int2,'select'); has_column_privilege ---------------------- (1 row) select has_column_privilege(9999,99::int2,'select'); has_column_privilege ---------------------- (1 row) create temp table mytable(f1 int, f2 int, f3 int); alter table mytable drop column f2; select has_column_privilege('mytable','f2','select'); ERROR: column "f2" of relation "mytable" does not exist select has_column_privilege('mytable','........pg.dropped.2........','select'); has_column_privilege ---------------------- (1 row) select has_column_privilege('mytable',2::int2,'select'); has_column_privilege ---------------------- (1 row) select has_column_privilege('mytable',99::int2,'select'); has_column_privilege ---------------------- (1 row) revoke select on table mytable from regress_priv_user3; select has_column_privilege('mytable',2::int2,'select'); has_column_privilege ---------------------- (1 row) select has_column_privilege('mytable',99::int2,'select'); has_column_privilege ---------------------- (1 row) drop table mytable; -- Grant options SET SESSION AUTHORIZATION regress_priv_user1; CREATE TABLE atest4 (a int); GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION; GRANT UPDATE ON atest4 TO regress_priv_user2; GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION; SET SESSION AUTHORIZATION regress_priv_user2; GRANT SELECT ON atest4 TO regress_priv_user3; GRANT UPDATE ON atest4 TO regress_priv_user3; -- fail WARNING: no privileges were granted for "atest4" SET SESSION AUTHORIZATION regress_priv_user1; REVOKE SELECT ON atest4 FROM regress_priv_user3; -- does nothing SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- true has_table_privilege --------------------- t (1 row) REVOKE SELECT ON atest4 FROM regress_priv_user2; -- fail ERROR: dependent privileges exist HINT: Use CASCADE to revoke them too. REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_priv_user2 CASCADE; -- ok SELECT has_table_privilege('regress_priv_user2', 'atest4', 'SELECT'); -- true has_table_privilege --------------------- t (1 row) SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- false has_table_privilege --------------------- f (1 row) SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION') has_table_privilege --------------------- t (1 row) -- security-restricted operations \c - CREATE ROLE regress_sro_user; -- Check that index expressions and predicates are run as the table's owner -- A dummy index function checking current_user CREATE FUNCTION sro_ifun(int) RETURNS int AS $$ BEGIN -- Below we set the table's owner to regress_sro_user ASSERT current_user = 'regress_sro_user', format('sro_ifun(%s) called by %s', $1, current_user); RETURN $1; END; $$ LANGUAGE plpgsql IMMUTABLE; -- Create a table owned by regress_sro_user CREATE TABLE sro_tab (a int); ALTER TABLE sro_tab OWNER TO regress_sro_user; INSERT INTO sro_tab VALUES (1), (2), (3); -- Create an expression index with a predicate CREATE INDEX sro_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0))) WHERE sro_ifun(a + 10) > sro_ifun(10); DROP INDEX sro_idx; -- Do the same concurrently CREATE INDEX CONCURRENTLY sro_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0))) WHERE sro_ifun(a + 10) > sro_ifun(10); -- REINDEX REINDEX TABLE sro_tab; REINDEX INDEX sro_idx; REINDEX TABLE CONCURRENTLY sro_tab; DROP INDEX sro_idx; -- CLUSTER CREATE INDEX sro_cluster_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0))); CLUSTER sro_tab USING sro_cluster_idx; DROP INDEX sro_cluster_idx; -- BRIN index CREATE INDEX sro_brin ON sro_tab USING brin ((sro_ifun(a) + sro_ifun(0))); SELECT brin_desummarize_range('sro_brin', 0); brin_desummarize_range ------------------------ (1 row) SELECT brin_summarize_range('sro_brin', 0); brin_summarize_range ---------------------- 1 (1 row) DROP TABLE sro_tab; -- Check with a partitioned table CREATE TABLE sro_ptab (a int) PARTITION BY RANGE (a); ALTER TABLE sro_ptab OWNER TO regress_sro_user; CREATE TABLE sro_part PARTITION OF sro_ptab FOR VALUES FROM (1) TO (10); ALTER TABLE sro_part OWNER TO regress_sro_user; INSERT INTO sro_ptab VALUES (1), (2), (3); CREATE INDEX sro_pidx ON sro_ptab ((sro_ifun(a) + sro_ifun(0))) WHERE sro_ifun(a + 10) > sro_ifun(10); REINDEX TABLE sro_ptab; REINDEX INDEX CONCURRENTLY sro_pidx; SET SESSION AUTHORIZATION regress_sro_user; CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS 'GRANT regress_priv_group2 TO regress_sro_user'; CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 'DECLARE c CURSOR WITH HOLD FOR SELECT public.unwanted_grant(); SELECT true'; -- REFRESH of this MV will queue a GRANT at end of transaction CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA; REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot create a cursor WITH HOLD within security-restricted operation CONTEXT: SQL function "mv_action" statement 1 \c - REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot create a cursor WITH HOLD within security-restricted operation CONTEXT: SQL function "mv_action" statement 1 SET SESSION AUTHORIZATION regress_sro_user; -- INSERT to this table will queue a GRANT at end of transaction CREATE TABLE sro_trojan_table (); CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS 'BEGIN PERFORM public.unwanted_grant(); RETURN NULL; END'; CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan(); -- Now, REFRESH will issue such an INSERT, queueing the GRANT CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS 'INSERT INTO public.sro_trojan_table DEFAULT VALUES; SELECT true'; REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation CONTEXT: SQL function "mv_action" statement 1 \c - REFRESH MATERIALIZED VIEW sro_mv; ERROR: cannot fire deferred trigger within security-restricted operation CONTEXT: SQL function "mv_action" statement 1 BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT; ERROR: permission denied to grant role "regress_priv_group2" DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. CONTEXT: SQL function "unwanted_grant" statement 1 SQL statement "SELECT public.unwanted_grant()" PL/pgSQL function public.sro_trojan() line 1 at PERFORM SQL function "mv_action" statement 1 -- REFRESH MATERIALIZED VIEW CONCURRENTLY use of eval_const_expressions() SET SESSION AUTHORIZATION regress_sro_user; CREATE FUNCTION unwanted_grant_nofail(int) RETURNS int IMMUTABLE LANGUAGE plpgsql AS $$ BEGIN PERFORM public.unwanted_grant(); RAISE WARNING 'owned'; RETURN 1; EXCEPTION WHEN OTHERS THEN RETURN 2; END$$; CREATE MATERIALIZED VIEW sro_index_mv AS SELECT 1 AS c; CREATE UNIQUE INDEX ON sro_index_mv (c) WHERE unwanted_grant_nofail(1) > 0; \c - REFRESH MATERIALIZED VIEW CONCURRENTLY sro_index_mv; REFRESH MATERIALIZED VIEW sro_index_mv; DROP OWNED BY regress_sro_user; DROP ROLE regress_sro_user; -- Admin options SET SESSION AUTHORIZATION regress_priv_user4; CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS 'GRANT regress_priv_group2 TO regress_priv_user5'; GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege ERROR: permission denied to grant role "regress_priv_group2" DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. SET SESSION AUTHORIZATION regress_priv_user1; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION ERROR: permission denied to grant role "regress_priv_group2" DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN NOTICE: role "regress_priv_user5" has already been granted membership in role "regress_pri dogrant_ok ------------ (1 row) SET ROLE regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help ERROR: permission denied to grant role "regress_priv_group2" DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. SET SESSION AUTHORIZATION regress_priv_group2; GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no self-admin ERROR: permission denied to grant role "regress_priv_group2" DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role. SET SESSION AUTHORIZATION regress_priv_user4; DROP FUNCTION dogrant_ok(); REVOKE regress_priv_group2 FROM regress_priv_user5; -- has_sequence_privilege tests \c - CREATE SEQUENCE x_seq; GRANT USAGE on x_seq to regress_priv_user2; SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT'); ERROR: "atest1" is not a sequence SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT'); ERROR: unrecognized privilege type: "INSERT" SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT'); has_sequence_privilege ------------------------ f (1 row) SET SESSION AUTHORIZATION regress_priv_user2; SELECT has_sequence_privilege('x_seq', 'USAGE'); has_sequence_privilege ------------------------ t (1 row) -- largeobject privilege tests \c - SET SESSION AUTHORIZATION regress_priv_user1; SELECT lo_create(1001); lo_create ----------- 1001 (1 row) SELECT lo_create(1002); lo_create ----------- 1002 (1 row) SELECT lo_create(1003); lo_create ----------- 1003 (1 row) SELECT lo_create(1004); lo_create ----------- 1004 (1 row) SELECT lo_create(1005); lo_create ----------- 1005 (1 row) GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC; GRANT SELECT ON LARGE OBJECT 1003 TO regress_priv_user2; GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_priv_user2; GRANT ALL ON LARGE OBJECT 1005 TO regress_priv_user2; GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user2 WITH GRANT OPTION; GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC; -- to be failed ERROR: invalid privilege type INSERT for large object GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser; -- to be failed ERROR: role "nosuchuser" does not exist GRANT SELECT, UPDATE ON LARGE OBJECT 999 TO PUBLIC; -- to be failed ERROR: large object 999 does not exist \c - SET SESSION AUTHORIZATION regress_priv_user2; SELECT lo_create(2001); lo_create ----------- 2001 (1 row) SELECT lo_create(2002); lo_create ----------- 2002 (1 row) SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now loread -------- \x (1 row) SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode ERROR: large object descriptor 0 was not opened for writing SELECT loread(lo_open(1001, x'40000'::int), 32); loread -------- \x (1 row) SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied ERROR: permission denied for large object 1002 SELECT loread(lo_open(1003, x'40000'::int), 32); loread -------- \x (1 row) SELECT loread(lo_open(1004, x'40000'::int), 32); loread -------- \x (1 row) SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd'); lowrite --------- 4 (1 row) SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied ERROR: permission denied for large object 1002 SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied ERROR: permission denied for large object 1003 SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd'); lowrite --------- 4 (1 row) GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3; GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3; -- to be denied ERROR: large object 1006 does not exist REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC; GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3; SELECT lo_unlink(1001); -- to be denied ERROR: must be owner of large object 1001 SELECT lo_unlink(2002); lo_unlink ----------- 1 (1 row) \c - -- confirm ACL setting SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WH oid | ownername | lomacl ------+--------------------+---------------------------------------------------- 1001 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,=rw/regress_pr 1002 | regress_priv_user1 | 1003 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_u 1004 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_u 1005 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_u 2001 | regress_priv_user2 | {regress_priv_user2=rw/regress_priv_user2,regress_priv_u (6 rows) SET SESSION AUTHORIZATION regress_priv_user3; SELECT loread(lo_open(1001, x'40000'::int), 32); loread ------------ \x61626364 (1 row) SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied ERROR: permission denied for large object 1003 SELECT loread(lo_open(1005, x'40000'::int), 32); loread -------- \x (1 row) SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied ERROR: permission denied for large object 1005 SELECT lo_truncate(lo_open(2001, x'20000'::int), 10); lo_truncate ------------- 0 (1 row) -- has_largeobject_privilege function -- superuser \c - SELECT has_largeobject_privilege(1001, 'SELECT'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1002, 'SELECT'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1003, 'SELECT'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1004, 'SELECT'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1001, 'UPDATE'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1002, 'UPDATE'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1003, 'UPDATE'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1004, 'UPDATE'); has_largeobject_privilege --------------------------- t (1 row) -- not-existing large object SELECT has_largeobject_privilege(9999, 'SELECT'); -- NULL has_largeobject_privilege --------------------------- (1 row) -- non-superuser SET SESSION AUTHORIZATION regress_priv_user2; SELECT has_largeobject_privilege(1001, 'SELECT'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1002, 'SELECT'); -- false has_largeobject_privilege --------------------------- f (1 row) SELECT has_largeobject_privilege(1003, 'SELECT'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1004, 'SELECT'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1001, 'UPDATE'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1002, 'UPDATE'); -- false has_largeobject_privilege --------------------------- f (1 row) SELECT has_largeobject_privilege(1003, 'UPDATE'); -- false has_largeobject_privilege --------------------------- f (1 row) SELECT has_largeobject_privilege(1004, 'UPDATE'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege('regress_priv_user3', 1001, 'SELECT'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege('regress_priv_user3', 1003, 'SELECT'); -- false has_largeobject_privilege --------------------------- f (1 row) SELECT has_largeobject_privilege('regress_priv_user3', 1005, 'SELECT'); has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege('regress_priv_user3', 1005, 'UPDATE'); -- false has_largeobject_privilege --------------------------- f (1 row) SELECT has_largeobject_privilege('regress_priv_user3', 2001, 'UPDATE'); has_largeobject_privilege --------------------------- t (1 row) -- compatibility mode in largeobject permission \c - SET lo_compat_privileges = false; -- default setting SET SESSION AUTHORIZATION regress_priv_user4; SELECT has_largeobject_privilege(1002, 'SELECT'); -- false has_largeobject_privilege --------------------------- f (1 row) SELECT has_largeobject_privilege(1002, 'UPDATE'); -- false has_largeobject_privilege --------------------------- f (1 row) SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied ERROR: permission denied for large object 1002 SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied ERROR: permission denied for large object 1002 SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied ERROR: permission denied for large object 1002 SELECT lo_put(1002, 1, 'abcd'); -- to be denied ERROR: permission denied for large object 1002 SELECT lo_unlink(1002); -- to be denied ERROR: must be owner of large object 1002 SELECT lo_export(1001, '/dev/null'); -- to be denied ERROR: permission denied for function lo_export SELECT lo_import('/dev/null'); -- to be denied ERROR: permission denied for function lo_import SELECT lo_import('/dev/null', 2003); -- to be denied ERROR: permission denied for function lo_import \c - SET lo_compat_privileges = true; -- compatibility mode SET SESSION AUTHORIZATION regress_priv_user4; SELECT has_largeobject_privilege(1002, 'SELECT'); -- true has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege(1002, 'UPDATE'); -- true has_largeobject_privilege --------------------------- t (1 row) SELECT loread(lo_open(1002, x'40000'::int), 32); loread -------- \x (1 row) SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); lowrite --------- 4 (1 row) SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); lo_truncate ------------- 0 (1 row) SELECT lo_unlink(1002); lo_unlink ----------- 1 (1 row) SELECT lo_export(1001, '/dev/null'); -- to be denied ERROR: permission denied for function lo_export -- don't allow unpriv users to access pg_largeobject contents \c - SELECT * FROM pg_largeobject LIMIT 0; loid | pageno | data ------+--------+------ (0 rows) SET SESSION AUTHORIZATION regress_priv_user1; SELECT * FROM pg_largeobject LIMIT 0; -- to be denied ERROR: permission denied for table pg_largeobject -- pg_signal_backend can't signal superusers RESET SESSION AUTHORIZATION; BEGIN; CREATE OR REPLACE FUNCTION terminate_nothrow(pid int) RETURNS bool LANGUAGE plpgsql SECURITY DEFINER SET client_min_messages = error AS $$ BEGIN RETURN pg_terminate_backend($1); EXCEPTION WHEN OTHERS THEN RETURN false; END$$; ALTER FUNCTION terminate_nothrow OWNER TO pg_signal_backend; SELECT backend_type FROM pg_stat_activity WHERE CASE WHEN COALESCE(usesysid, 10) = 10 THEN terminate_nothrow(pid) END; backend_type -------------- (0 rows) ROLLBACK; -- test pg_database_owner RESET SESSION AUTHORIZATION; GRANT pg_database_owner TO regress_priv_user1; ERROR: role "pg_database_owner" cannot have explicit members GRANT regress_priv_user1 TO pg_database_owner; ERROR: role "pg_database_owner" cannot be a member of any role CREATE TABLE datdba_only (); ALTER TABLE datdba_only OWNER TO pg_database_owner; REVOKE DELETE ON datdba_only FROM pg_database_owner; SELECT pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv, pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem, pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER WITH ADMIN OPTION') as admin; priv | mem | admin ------+-----+------- f | f | f (1 row) BEGIN; DO $$BEGIN EXECUTE format( 'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$; SELECT pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv, pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem, pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER WITH ADMIN OPTION') as admin; priv | mem | admin ------+-----+------- t | t | f (1 row) SET SESSION AUTHORIZATION regress_priv_user1; TABLE information_schema.enabled_roles ORDER BY role_name COLLATE "C"; role_name --------------------- pg_database_owner regress_priv_group2 regress_priv_user1 (3 rows) TABLE information_schema.applicable_roles ORDER BY role_name COLLATE "C"; grantee | role_name | is_grantable ---------------------+---------------------+-------------- regress_priv_group2 | pg_database_owner | NO regress_priv_user1 | regress_priv_group2 | NO (2 rows) INSERT INTO datdba_only DEFAULT VALUES; SAVEPOINT q; DELETE FROM datdba_only; ROLLBACK TO q; ERROR: permission denied for table datdba_only SET SESSION AUTHORIZATION regress_priv_user2; TABLE information_schema.enabled_roles; role_name -------------------- regress_priv_user2 (1 row) INSERT INTO datdba_only DEFAULT VALUES; ERROR: permission denied for table datdba_only ROLLBACK; -- test default ACLs \c - CREATE SCHEMA testns; GRANT ALL ON SCHEMA testns TO regress_priv_user1; CREATE TABLE testns.acltest1 (x int); SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no has_table_privilege --------------------- f (1 row) SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no has_table_privilege --------------------- f (1 row) -- placeholder for test with duplicated schema and role names ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public; SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no has_table_privilege --------------------- f (1 row) SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no has_table_privilege --------------------- f (1 row) DROP TABLE testns.acltest1; CREATE TABLE testns.acltest1 (x int); SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes has_table_privilege --------------------- t (1 row) SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no has_table_privilege --------------------- f (1 row) ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1; DROP TABLE testns.acltest1; CREATE TABLE testns.acltest1 (x int); SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes has_table_privilege --------------------- t (1 row) SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- yes has_table_privilege --------------------- t (1 row) ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1; DROP TABLE testns.acltest1; CREATE TABLE testns.acltest1 (x int); SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes has_table_privilege --------------------- t (1 row) SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no has_table_privilege --------------------- f (1 row) ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM publi ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_priv_user2; -- er ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS -- Test makeaclitem() SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole, 'SELECT', TRUE); -- single privilege makeaclitem ------------------------------------------ regress_priv_user1=r*/regress_priv_user2 (1 row) SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole, 'SELECT, INSERT, UPDATE , DELETE ', FALSE); -- multiple privileges makeaclitem -------------------------------------------- regress_priv_user1=arwd/regress_priv_user2 (1 row) SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole, 'SELECT, fake_privilege', FALSE); -- error ERROR: unrecognized privilege type: "fake_privilege" -- Test quoting and dequoting of user names in ACLs CREATE ROLE "regress_""quoted"; SELECT makeaclitem('regress_"quoted'::regrole, 'regress_"quoted'::regrole, 'SELECT', TRUE); makeaclitem ------------------------------------------ "regress_""quoted"=r*/"regress_""quoted" (1 row) SELECT '"regress_""quoted"=r*/"regress_""quoted"'::aclitem; aclitem ------------------------------------------ "regress_""quoted"=r*/"regress_""quoted" (1 row) SELECT '""=r*/""'::aclitem; -- used to be misparsed as """" ERROR: a name must follow the "/" sign LINE 1: SELECT '""=r*/""'::aclitem; ^ DROP ROLE "regress_""quoted"; -- Test non-throwing aclitem I/O SELECT pg_input_is_valid('regress_priv_user1=r/regress_priv_user2', 'aclitem'); pg_input_is_valid ------------------- t (1 row) SELECT pg_input_is_valid('regress_priv_user1=r/', 'aclitem'); pg_input_is_valid ------------------- f (1 row) SELECT * FROM pg_input_error_info('regress_priv_user1=r/', 'aclitem'); message | detail | hint | sql_error_code ---------------------------------+--------+------+---------------- a name must follow the "/" sign | | | 22P02 (1 row) SELECT pg_input_is_valid('regress_priv_user1=r/regress_no_such_user', 'aclitem'); pg_input_is_valid ------------------- f (1 row) SELECT * FROM pg_input_error_info('regress_priv_user1=r/regress_no_such_user', 'acli message | detail | hint | sql_error_code --------------------------------------------+--------+------+---------------- role "regress_no_such_user" does not exist | | | 42704 (1 row) SELECT pg_input_is_valid('regress_priv_user1=rY', 'aclitem'); pg_input_is_valid ------------------- f (1 row) SELECT * FROM pg_input_error_info('regress_priv_user1=rY', 'aclitem'); message | detail | hint | sql_error_code ----------------------------------------------------------+--------+------+----- invalid mode character: must be one of "arwdDxtXUCTcsAm" | | | 22P02 (1 row) -- -- Testing blanket default grants is very hazardous since it might change -- the privileges attached to objects created by concurrent regression tests. -- To avoid that, be sure to revoke the privileges again before committing. -- BEGIN; ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2; CREATE SCHEMA testns2; SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes has_schema_privilege ---------------------- t (1 row) SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- yes has_schema_privilege ---------------------- t (1 row) SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no has_schema_privilege ---------------------- f (1 row) ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2; CREATE SCHEMA testns3; SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'USAGE'); -- no has_schema_privilege ---------------------- f (1 row) SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'CREATE'); -- no has_schema_privilege ---------------------- f (1 row) ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2; CREATE SCHEMA testns4; SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'USAGE'); -- yes has_schema_privilege ---------------------- t (1 row) SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes has_schema_privilege ---------------------- t (1 row) ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2; COMMIT; -- -- Test for default privileges on large objects. This is done in a -- separate, rollbacked, transaction to avoid any trouble with other -- regression sessions. -- BEGIN; SELECT lo_create(1007); lo_create ----------- 1007 (1 row) SELECT has_largeobject_privilege('regress_priv_user2', 1007, 'SELECT'); -- no has_largeobject_privilege --------------------------- f (1 row) SELECT has_largeobject_privilege('regress_priv_user2', 1007, 'UPDATE'); -- no has_largeobject_privilege --------------------------- f (1 row) ALTER DEFAULT PRIVILEGES GRANT SELECT ON LARGE OBJECTS TO regress_priv_user2; SELECT lo_create(1008); lo_create ----------- 1008 (1 row) SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'SELECT'); -- yes has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege('regress_priv_user6', 1008, 'SELECT'); -- no has_largeobject_privilege --------------------------- f (1 row) SELECT has_largeobject_privilege('regress_priv_user2', 1008, 'UPDATE'); -- no has_largeobject_privilege --------------------------- f (1 row) ALTER DEFAULT PRIVILEGES GRANT ALL ON LARGE OBJECTS TO regress_priv_user2; SELECT lo_create(1009); lo_create ----------- 1009 (1 row) SELECT has_largeobject_privilege('regress_priv_user2', 1009, 'SELECT'); -- true has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege('regress_priv_user2', 1009, 'UPDATE'); -- true has_largeobject_privilege --------------------------- t (1 row) ALTER DEFAULT PRIVILEGES REVOKE UPDATE ON LARGE OBJECTS FROM regress_priv_user2; SELECT lo_create(1010); lo_create ----------- 1010 (1 row) SELECT has_largeobject_privilege('regress_priv_user2', 1010, 'SELECT'); -- true has_largeobject_privilege --------------------------- t (1 row) SELECT has_largeobject_privilege('regress_priv_user2', 1010, 'UPDATE'); -- false has_largeobject_privilege --------------------------- f (1 row) ROLLBACK; ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON LARGE OBJECTS TO public; -- error ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON LARGE OBJECTS \c - -- Test for DROP OWNED BY with shared dependencies. This is done in a -- separate, rollbacked, transaction to avoid any trouble with other -- regression sessions. BEGIN; ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS TO regress_priv_user2; ALTER DEFAULT PRIVILEGES GRANT ALL ON LARGE OBJECTS TO regress_priv_user2; ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2; ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO regress_priv_user2; ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO regress_priv_user2; ALTER DEFAULT PRIVILEGES GRANT ALL ON TYPES TO regress_priv_user2; SELECT count(*) FROM pg_shdepend WHERE deptype = 'a' AND refobjid = 'regress_priv_user2'::regrole AND classid = 'pg_default_acl'::regclass; count ------- 6 (1 row) DROP OWNED BY regress_priv_user2, regress_priv_user2; SELECT count(*) FROM pg_shdepend WHERE deptype = 'a' AND refobjid = 'regress_priv_user2'::regrole AND classid = 'pg_default_acl'::regclass; count ------- 0 (1 row) ROLLBACK; CREATE SCHEMA testns5; SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE'); -- no has_schema_privilege ---------------------- f (1 row) SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'CREATE'); -- no has_schema_privilege ---------------------- f (1 row) SET ROLE regress_priv_user1; CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- no has_function_privilege ------------------------ f (1 row) SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); has_function_privilege ------------------------ f (1 row) SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- no has_function_privilege ------------------------ f (1 row) ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public; DROP FUNCTION testns.foo(); CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql; DROP AGGREGATE testns.agg1(int); CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4); DROP PROCEDURE testns.bar(); CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql; SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- ye has_function_privilege ------------------------ t (1 row) SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); has_function_privilege ------------------------ t (1 row) SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- ye has_function_privilege ------------------------ t (1 row) DROP FUNCTION testns.foo(); DROP AGGREGATE testns.agg1(int); DROP PROCEDURE testns.bar(); ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE USAGE ON TYPES FROM public; CREATE DOMAIN testns.priv_testdomain1 AS int; SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE') has_type_privilege -------------------- f (1 row) ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public; DROP DOMAIN testns.priv_testdomain1; CREATE DOMAIN testns.priv_testdomain1 AS int; SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE') has_type_privilege -------------------- t (1 row) DROP DOMAIN testns.priv_testdomain1; RESET ROLE; SELECT count(*) FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid WHERE nspname = 'testns'; count ------- 3 (1 row) DROP SCHEMA testns CASCADE; NOTICE: drop cascades to table testns.acltest1 DROP SCHEMA testns2 CASCADE; DROP SCHEMA testns3 CASCADE; DROP SCHEMA testns4 CASCADE; DROP SCHEMA testns5 CASCADE; SELECT d.* -- check that entries went away FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid WHERE nspname IS NULL AND defaclnamespace != 0; oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl -----+------------+-----------------+---------------+----------- (0 rows) -- Grant on all objects of given type in a schema \c - CREATE SCHEMA testns; CREATE TABLE testns.t1 (f1 int); CREATE TABLE testns.t2 (f1 int); SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false has_table_privilege --------------------- f (1 row) GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1; SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- true has_table_privilege --------------------- t (1 row) SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- true has_table_privilege --------------------- t (1 row) REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1; SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false has_table_privilege --------------------- f (1 row) SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- false has_table_privilege --------------------- f (1 row) CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql; CREATE AGGREGATE testns.priv_testagg(int) (sfunc = int4pl, stype = int4); CREATE PROCEDURE testns.priv_testproc(int) AS 'select 3' LANGUAGE sql; SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'E has_function_privilege ------------------------ t (1 row) SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EX has_function_privilege ------------------------ t (1 row) SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'E has_function_privilege ------------------------ t (1 row) REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC; SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'E has_function_privilege ------------------------ f (1 row) SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EX has_function_privilege ------------------------ f (1 row) SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'E has_function_privilege ------------------------ t (1 row) REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC; SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'E has_function_privilege ------------------------ f (1 row) GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC; SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'E has_function_privilege ------------------------ t (1 row) SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EX has_function_privilege ------------------------ t (1 row) SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'E has_function_privilege ------------------------ t (1 row) DROP SCHEMA testns CASCADE; NOTICE: drop cascades to 5 other objects DETAIL: drop cascades to table testns.t1 drop cascades to table testns.t2 drop cascades to function testns.priv_testfunc(integer) drop cascades to function testns.priv_testagg(integer) drop cascades to function testns.priv_testproc(integer) -- Change owner of the schema & and rename of new schema owner \c - CREATE ROLE regress_schemauser1 superuser login; CREATE ROLE regress_schemauser2 superuser login; SET SESSION ROLE regress_schemauser1; CREATE SCHEMA testns; SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' nspname | rolname ---------+--------------------- testns | regress_schemauser1 (1 row) ALTER SCHEMA testns OWNER TO regress_schemauser2; ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed; SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' nspname | rolname ---------+---------------------------- testns | regress_schemauser_renamed (1 row) set session role regress_schemauser_renamed; DROP SCHEMA testns CASCADE; -- clean up \c - DROP ROLE regress_schemauser1; DROP ROLE regress_schemauser_renamed; -- test that dependent privileges are revoked (or not) properly \c - set session role regress_priv_user1; create table dep_priv_test (a int); grant select on dep_priv_test to regress_priv_user2 with grant option; grant select on dep_priv_test to regress_priv_user3 with grant option; set session role regress_priv_user2; grant select on dep_priv_test to regress_priv_user4 with grant option; set session role regress_priv_user3; grant select on dep_priv_test to regress_priv_user4 with grant option; set session role regress_priv_user4; grant select on dep_priv_test to regress_priv_user5; \dp dep_priv_test Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+---------------+-------+----------------------------------------------- public | dep_priv_test | table | regress_priv_user1=arwdDxtm/regress_priv_user1+| | | | | regress_priv_user2=r*/regress_priv_user1 +| | | | | regress_priv_user3=r*/regress_priv_user1 +| | | | | regress_priv_user4=r*/regress_priv_user2 +| | | | | regress_priv_user4=r*/regress_priv_user3 +| | | | | regress_priv_user5=r/regress_priv_user4 | | (1 row) set session role regress_priv_user2; revoke select on dep_priv_test from regress_priv_user4 cascade; \dp dep_priv_test Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+---------------+-------+----------------------------------------------- public | dep_priv_test | table | regress_priv_user1=arwdDxtm/regress_priv_user1+| | | | | regress_priv_user2=r*/regress_priv_user1 +| | | | | regress_priv_user3=r*/regress_priv_user1 +| | | | | regress_priv_user4=r*/regress_priv_user3 +| | | | | regress_priv_user5=r/regress_priv_user4 | | (1 row) set session role regress_priv_user3; revoke select on dep_priv_test from regress_priv_user4 cascade; \dp dep_priv_test Access privileges Schema | Name | Type | Access privileges | Column privileges | Policies --------+---------------+-------+----------------------------------------------- public | dep_priv_test | table | regress_priv_user1=arwdDxtm/regress_priv_user1+| | | | | regress_priv_user2=r*/regress_priv_user1 +| | | | | regress_priv_user3=r*/regress_priv_user1 | | (1 row) set session role regress_priv_user1; drop table dep_priv_test; -- clean up \c drop sequence x_seq; DROP AGGREGATE priv_testagg1(int); DROP FUNCTION priv_testfunc2(int); DROP FUNCTION priv_testfunc4(boolean); DROP PROCEDURE priv_testproc1(int); DROP VIEW atestv0; DROP VIEW atestv1; DROP VIEW atestv2; -- this should cascade to drop atestv4 DROP VIEW atestv3 CASCADE; NOTICE: drop cascades to view atestv4 -- this should complain "does not exist" DROP VIEW atestv4; ERROR: view "atestv4" does not exist DROP TABLE atest1; DROP TABLE atest2; DROP TABLE atest3; DROP TABLE atest4; DROP TABLE atest5; DROP TABLE atest6; DROP TABLE atestc; DROP TABLE atestp1; DROP TABLE atestp2; SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid lo_unlink ----------- 1 1 1 1 1 (5 rows) DROP GROUP regress_priv_group1; DROP GROUP regress_priv_group2; -- these are needed to clean up permissions REVOKE USAGE ON LANGUAGE sql FROM regress_priv_user1; DROP OWNED BY regress_priv_user1; DROP USER regress_priv_user1; DROP USER regress_priv_user2; DROP USER regress_priv_user3; DROP USER regress_priv_user4; DROP USER regress_priv_user5; DROP USER regress_priv_user6; DROP USER regress_priv_user7; DROP USER regress_priv_user8; -- does not exist ERROR: role "regress_priv_user8" does not exist -- leave some default ACLs for pg_upgrade's dump-restore test input. ALTER DEFAULT PRIVILEGES FOR ROLE pg_signal_backend REVOKE USAGE ON TYPES FROM pg_signal_backend; ALTER DEFAULT PRIVILEGES FOR ROLE pg_read_all_settings REVOKE USAGE ON TYPES FROM pg_read_all_settings; -- permissions with LOCK TABLE CREATE USER regress_locktable_user; CREATE TABLE lock_table (a int); -- LOCK TABLE and SELECT permission GRANT SELECT ON lock_table TO regress_locktable_user; SET SESSION AUTHORIZATION regress_locktable_user; BEGIN; LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass COMMIT; BEGIN; LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail ERROR: permission denied for table lock_table ROLLBACK; BEGIN; LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail ERROR: permission denied for table lock_table ROLLBACK; \c REVOKE SELECT ON lock_table FROM regress_locktable_user; -- LOCK TABLE and INSERT permission GRANT INSERT ON lock_table TO regress_locktable_user; SET SESSION AUTHORIZATION regress_locktable_user; BEGIN; LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass ROLLBACK; BEGIN; LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass COMMIT; BEGIN; LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail ERROR: permission denied for table lock_table ROLLBACK; \c REVOKE INSERT ON lock_table FROM regress_locktable_user; -- LOCK TABLE and UPDATE permission GRANT UPDATE ON lock_table TO regress_locktable_user; SET SESSION AUTHORIZATION regress_locktable_user; BEGIN; LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass ROLLBACK; BEGIN; LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass COMMIT; BEGIN; LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass COMMIT; \c REVOKE UPDATE ON lock_table FROM regress_locktable_user; -- LOCK TABLE and DELETE permission GRANT DELETE ON lock_table TO regress_locktable_user; SET SESSION AUTHORIZATION regress_locktable_user; BEGIN; LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass ROLLBACK; BEGIN; LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass COMMIT; BEGIN; LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass COMMIT; \c REVOKE DELETE ON lock_table FROM regress_locktable_user; -- LOCK TABLE and TRUNCATE permission GRANT TRUNCATE ON lock_table TO regress_locktable_user; SET SESSION AUTHORIZATION regress_locktable_user; BEGIN; LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass ROLLBACK; BEGIN; LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass COMMIT; BEGIN; LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass COMMIT; \c REVOKE TRUNCATE ON lock_table FROM regress_locktable_user; -- LOCK TABLE and MAINTAIN permission GRANT MAINTAIN ON lock_table TO regress_locktable_user; SET SESSION AUTHORIZATION regress_locktable_user; BEGIN; LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass ROLLBACK; BEGIN; LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass COMMIT; BEGIN; LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass COMMIT; \c REVOKE MAINTAIN ON lock_table FROM regress_locktable_user; -- clean up DROP TABLE lock_table; DROP USER regress_locktable_user; -- test to check privileges of system views pg_shmem_allocations, -- pg_shmem_allocations_numa and pg_backend_memory_contexts. -- switch to superuser \c - CREATE ROLE regress_readallstats; SELECT has_table_privilege('regress_readallstats','pg_aios','SELECT'); -- no has_table_privilege --------------------- f (1 row) SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','S has_table_privilege --------------------- f (1 row) SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT' has_table_privilege --------------------- f (1 row) SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations_numa','SE has_table_privilege --------------------- f (1 row) GRANT pg_read_all_stats TO regress_readallstats; SELECT has_table_privilege('regress_readallstats','pg_aios','SELECT'); -- yes has_table_privilege --------------------- t (1 row) SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','S has_table_privilege --------------------- t (1 row) SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT' has_table_privilege --------------------- t (1 row) SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations_numa','SE has_table_privilege --------------------- t (1 row) -- run query to ensure that functions within views can be executed SET ROLE regress_readallstats; SELECT COUNT(*) >= 0 AS ok FROM pg_aios; ok ---- t (1 row) SELECT COUNT(*) >= 0 AS ok FROM pg_backend_memory_contexts; ok ---- t (1 row) SELECT COUNT(*) >= 0 AS ok FROM pg_shmem_allocations; ok ---- t (1 row) RESET ROLE; -- clean up DROP ROLE regress_readallstats; -- test role grantor machinery CREATE ROLE regress_group; CREATE ROLE regress_group_direct_manager; CREATE ROLE regress_group_indirect_manager; CREATE ROLE regress_group_member; GRANT regress_group TO regress_group_direct_manager WITH INHERIT FALSE, ADMIN TRUE; GRANT regress_group_direct_manager TO regress_group_indirect_manager; SET SESSION AUTHORIZATION regress_group_direct_manager; GRANT regress_group TO regress_group_member; SELECT member::regrole::text, CASE WHEN grantor = 10 THEN 'BOOTSTRAP SUPERUSER' ELSE granto member | grantor ------------------------------+------------------------------ regress_group_direct_manager | BOOTSTRAP SUPERUSER regress_group_member | regress_group_direct_manager (2 rows) REVOKE regress_group FROM regress_group_member; SET SESSION AUTHORIZATION regress_group_indirect_manager; GRANT regress_group TO regress_group_member; SELECT member::regrole::text, CASE WHEN grantor = 10 THEN 'BOOTSTRAP SUPERUSER' ELSE granto member | grantor ------------------------------+------------------------------ regress_group_direct_manager | BOOTSTRAP SUPERUSER regress_group_member | regress_group_direct_manager (2 rows) REVOKE regress_group FROM regress_group_member; RESET SESSION AUTHORIZATION; DROP ROLE regress_group; DROP ROLE regress_group_direct_manager; DROP ROLE regress_group_indirect_manager; DROP ROLE regress_group_member; -- test SET and INHERIT options with object ownership changes CREATE ROLE regress_roleoption_protagonist; CREATE ROLE regress_roleoption_donor; CREATE ROLE regress_roleoption_recipient; CREATE SCHEMA regress_roleoption; GRANT CREATE, USAGE ON SCHEMA regress_roleoption TO PUBLIC; GRANT regress_roleoption_donor TO regress_roleoption_protagonist WITH INHERIT TRUE, SET GRANT regress_roleoption_recipient TO regress_roleoption_protagonist WITH INHERIT FALS SET SESSION AUTHORIZATION regress_roleoption_protagonist; CREATE TABLE regress_roleoption.t1 (a int); CREATE TABLE regress_roleoption.t2 (a int); SET SESSION AUTHORIZATION regress_roleoption_donor; CREATE TABLE regress_roleoption.t3 (a int); SET SESSION AUTHORIZATION regress_roleoption_recipient; CREATE TABLE regress_roleoption.t4 (a int); SET SESSION AUTHORIZATION regress_roleoption_protagonist; ALTER TABLE regress_roleoption.t1 OWNER TO regress_roleoption_donor; -- fails, can't be co ERROR: must be able to SET ROLE "regress_roleoption_donor" ALTER TABLE regress_roleoption.t2 OWNER TO regress_roleoption_recipient; -- works ALTER TABLE regress_roleoption.t3 OWNER TO regress_roleoption_protagonist; -- works ALTER TABLE regress_roleoption.t4 OWNER TO regress_roleoption_protagonist; -- fails, we d ERROR: must be owner of table t4 RESET SESSION AUTHORIZATION; DROP TABLE regress_roleoption.t1; DROP TABLE regress_roleoption.t2; DROP TABLE regress_roleoption.t3; DROP TABLE regress_roleoption.t4; DROP SCHEMA regress_roleoption; DROP ROLE regress_roleoption_protagonist; DROP ROLE regress_roleoption_donor; DROP ROLE regress_roleoption_recipient; -- MAINTAIN CREATE ROLE regress_no_maintain; CREATE ROLE regress_maintain; CREATE ROLE regress_maintain_all IN ROLE pg_maintain; CREATE TABLE maintain_test (a INT); CREATE INDEX ON maintain_test (a); GRANT MAINTAIN ON maintain_test TO regress_maintain; CREATE MATERIALIZED VIEW refresh_test AS SELECT 1; GRANT MAINTAIN ON refresh_test TO regress_maintain; CREATE SCHEMA reindex_test; -- negative tests; should fail SET ROLE regress_no_maintain; VACUUM maintain_test; WARNING: permission denied to vacuum "maintain_test", skipping it ANALYZE maintain_test; WARNING: permission denied to analyze "maintain_test", skipping it VACUUM (ANALYZE) maintain_test; WARNING: permission denied to vacuum "maintain_test", skipping it CLUSTER maintain_test USING maintain_test_a_idx; ERROR: permission denied for table maintain_test REFRESH MATERIALIZED VIEW refresh_test; ERROR: permission denied for materialized view refresh_test REINDEX TABLE maintain_test; ERROR: permission denied for table maintain_test REINDEX INDEX maintain_test_a_idx; ERROR: permission denied for index maintain_test_a_idx REINDEX SCHEMA reindex_test; ERROR: must be owner of schema reindex_test RESET ROLE; SET ROLE regress_maintain; VACUUM maintain_test; ANALYZE maintain_test; VACUUM (ANALYZE) maintain_test; CLUSTER maintain_test USING maintain_test_a_idx; REFRESH MATERIALIZED VIEW refresh_test; REINDEX TABLE maintain_test; REINDEX INDEX maintain_test_a_idx; REINDEX SCHEMA reindex_test; ERROR: must be owner of schema reindex_test RESET ROLE; SET ROLE regress_maintain_all; VACUUM maintain_test; ANALYZE maintain_test; VACUUM (ANALYZE) maintain_test; CLUSTER maintain_test USING maintain_test_a_idx; REFRESH MATERIALIZED VIEW refresh_test; REINDEX TABLE maintain_test; REINDEX INDEX maintain_test_a_idx; REINDEX SCHEMA reindex_test; RESET ROLE; DROP TABLE maintain_test; DROP MATERIALIZED VIEW refresh_test; DROP SCHEMA reindex_test; DROP ROLE regress_no_maintain; DROP ROLE regress_maintain; DROP ROLE regress_maintain_all; -- grantor selection CREATE ROLE regress_grantor1; CREATE ROLE regress_grantor2 ROLE regress_grantor1; CREATE ROLE regress_grantor3; CREATE TABLE grantor_test1 (); CREATE TABLE grantor_test2 (); CREATE TABLE grantor_test3 (); GRANT SELECT ON grantor_test2 TO regress_grantor1 WITH GRANT OPTION; GRANT SELECT, UPDATE ON grantor_test3 TO regress_grantor2 WITH GRANT OPTION; SET ROLE regress_grantor1; GRANT SELECT, UPDATE ON grantor_test1 TO regress_grantor3; ERROR: permission denied for table grantor_test1 GRANT SELECT, UPDATE ON grantor_test2 TO regress_grantor3; WARNING: not all privileges were granted for "grantor_test2" GRANT SELECT, UPDATE ON grantor_test3 TO regress_grantor3; RESET ROLE; SELECT * FROM information_schema.table_privileges t WHERE grantor LIKE 'regress_grantor%' ORDER BY ROW(t.*); grantor | grantee | table_catalog | table_schema | table_name | privilege_type | is_grantabl ------------------+------------------+---------------+--------------+----------- regress_grantor1 | regress_grantor3 | regression | public | grantor_test2 | SELECT | NO | YES regress_grantor2 | regress_grantor3 | regression | public | grantor_test3 | SELECT | NO | YES regress_grantor2 | regress_grantor3 | regression | public | grantor_test3 | UPDATE | NO | NO (3 rows) DROP TABLE grantor_test1, grantor_test2, grantor_test3; DROP ROLE regress_grantor1, regress_grantor2, regress_grantor3; [Dauer der Verarbeitung: 0.47 Sekunden, vorverarbeitet 2026-08-08] |
2026-08-25