1-- 2-- DEPENDENCIES 3-- 4CREATE USER regress_dep_user; 5CREATE USER regress_dep_user2; 6CREATE USER regress_dep_user3; 7CREATE GROUP regress_dep_group; 8CREATE TABLE deptest (f1 serial primary key, f2 text); 9GRANT SELECT ON TABLE deptest TO GROUP regress_dep_group; 10GRANT ALL ON TABLE deptest TO regress_dep_user, regress_dep_user2; 11-- can't drop neither because they have privileges somewhere 12DROP USER regress_dep_user; 13ERROR: role "regress_dep_user" cannot be dropped because some objects depend on it 14DETAIL: privileges for table deptest 15DROP GROUP regress_dep_group; 16ERROR: role "regress_dep_group" cannot be dropped because some objects depend on it 17DETAIL: privileges for table deptest 18-- if we revoke the privileges we can drop the group 19REVOKE SELECT ON deptest FROM GROUP regress_dep_group; 20DROP GROUP regress_dep_group; 21-- can't drop the user if we revoke the privileges partially 22REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regress_dep_user; 23DROP USER regress_dep_user; 24ERROR: role "regress_dep_user" cannot be dropped because some objects depend on it 25DETAIL: privileges for table deptest 26-- now we are OK to drop him 27REVOKE TRIGGER ON deptest FROM regress_dep_user; 28DROP USER regress_dep_user; 29-- we are OK too if we drop the privileges all at once 30REVOKE ALL ON deptest FROM regress_dep_user2; 31DROP USER regress_dep_user2; 32-- can't drop the owner of an object 33-- the error message detail here would include a pg_toast_nnn name that 34-- is not constant, so suppress it 35\set VERBOSITY terse 36ALTER TABLE deptest OWNER TO regress_dep_user3; 37DROP USER regress_dep_user3; 38ERROR: role "regress_dep_user3" cannot be dropped because some objects depend on it 39\set VERBOSITY default 40-- if we drop the object, we can drop the user too 41DROP TABLE deptest; 42DROP USER regress_dep_user3; 43-- Test DROP OWNED 44CREATE USER regress_dep_user0; 45CREATE USER regress_dep_user1; 46CREATE USER regress_dep_user2; 47SET SESSION AUTHORIZATION regress_dep_user0; 48-- permission denied 49DROP OWNED BY regress_dep_user1; 50ERROR: permission denied to drop objects 51DROP OWNED BY regress_dep_user0, regress_dep_user2; 52ERROR: permission denied to drop objects 53REASSIGN OWNED BY regress_dep_user0 TO regress_dep_user1; 54ERROR: permission denied to reassign objects 55REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user0; 56ERROR: permission denied to reassign objects 57-- this one is allowed 58DROP OWNED BY regress_dep_user0; 59CREATE TABLE deptest1 (f1 int unique); 60GRANT ALL ON deptest1 TO regress_dep_user1 WITH GRANT OPTION; 61SET SESSION AUTHORIZATION regress_dep_user1; 62CREATE TABLE deptest (a serial primary key, b text); 63GRANT ALL ON deptest1 TO regress_dep_user2; 64RESET SESSION AUTHORIZATION; 65\z deptest1 66 Access privileges 67 Schema | Name | Type | Access privileges | Column privileges | Policies 68--------+----------+-------+----------------------------------------------------+-------------------+---------- 69 public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 +| | 70 | | | regress_dep_user1=a*r*w*d*D*x*t*/regress_dep_user0+| | 71 | | | regress_dep_user2=arwdDxt/regress_dep_user1 | | 72(1 row) 73 74DROP OWNED BY regress_dep_user1; 75-- all grants revoked 76\z deptest1 77 Access privileges 78 Schema | Name | Type | Access privileges | Column privileges | Policies 79--------+----------+-------+---------------------------------------------+-------------------+---------- 80 public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 | | 81(1 row) 82 83-- table was dropped 84\d deptest 85-- Test REASSIGN OWNED 86GRANT ALL ON deptest1 TO regress_dep_user1; 87GRANT CREATE ON DATABASE regression TO regress_dep_user1; 88SET SESSION AUTHORIZATION regress_dep_user1; 89CREATE SCHEMA deptest; 90CREATE TABLE deptest (a serial primary key, b text); 91ALTER DEFAULT PRIVILEGES FOR ROLE regress_dep_user1 IN SCHEMA deptest 92 GRANT ALL ON TABLES TO regress_dep_user2; 93CREATE FUNCTION deptest_func() RETURNS void LANGUAGE plpgsql 94 AS $$ BEGIN END; $$; 95CREATE TYPE deptest_enum AS ENUM ('red'); 96CREATE TYPE deptest_range AS RANGE (SUBTYPE = int4); 97CREATE TABLE deptest2 (f1 int); 98-- make a serial column the hard way 99CREATE SEQUENCE ss1; 100ALTER TABLE deptest2 ALTER f1 SET DEFAULT nextval('ss1'); 101ALTER SEQUENCE ss1 OWNED BY deptest2.f1; 102-- When reassigning ownership of a composite type, its pg_class entry 103-- should match 104CREATE TYPE deptest_t AS (a int); 105SELECT typowner = relowner 106FROM pg_type JOIN pg_class c ON typrelid = c.oid WHERE typname = 'deptest_t'; 107 ?column? 108---------- 109 t 110(1 row) 111 112RESET SESSION AUTHORIZATION; 113REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user2; 114\dt deptest 115 List of relations 116 Schema | Name | Type | Owner 117--------+---------+-------+------------------- 118 public | deptest | table | regress_dep_user2 119(1 row) 120 121SELECT typowner = relowner 122FROM pg_type JOIN pg_class c ON typrelid = c.oid WHERE typname = 'deptest_t'; 123 ?column? 124---------- 125 t 126(1 row) 127 128-- doesn't work: grant still exists 129DROP USER regress_dep_user1; 130ERROR: role "regress_dep_user1" cannot be dropped because some objects depend on it 131DETAIL: owner of default privileges on new relations belonging to role regress_dep_user1 in schema deptest 132privileges for database regression 133privileges for table deptest1 134DROP OWNED BY regress_dep_user1; 135DROP USER regress_dep_user1; 136\set VERBOSITY terse 137DROP USER regress_dep_user2; 138ERROR: role "regress_dep_user2" cannot be dropped because some objects depend on it 139DROP OWNED BY regress_dep_user2, regress_dep_user0; 140DROP USER regress_dep_user2; 141DROP USER regress_dep_user0; 142