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