4 CREATE USER regress_dep_user;
5 CREATE USER regress_dep_user2;
6 CREATE USER regress_dep_user3;
7 CREATE GROUP regress_dep_group;
8 CREATE TABLE deptest (f1 serial primary key, f2 text);
9 GRANT SELECT ON TABLE deptest TO GROUP regress_dep_group;
10 GRANT ALL ON TABLE deptest TO regress_dep_user, regress_dep_user2;
11 -- can't drop neither because they have privileges somewhere
12 DROP USER regress_dep_user;
13 ERROR: role "regress_dep_user" cannot be dropped because some objects depend on it
14 DETAIL: privileges for table deptest
15 DROP GROUP regress_dep_group;
16 ERROR: role "regress_dep_group" cannot be dropped because some objects depend on it
17 DETAIL: privileges for table deptest
18 -- if we revoke the privileges we can drop the group
19 REVOKE SELECT ON deptest FROM GROUP regress_dep_group;
20 DROP GROUP regress_dep_group;
21 -- can't drop the user if we revoke the privileges partially
22 REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regress_dep_user;
23 DROP USER regress_dep_user;
24 ERROR: role "regress_dep_user" cannot be dropped because some objects depend on it
25 DETAIL: privileges for table deptest
26 -- now we are OK to drop him
27 REVOKE TRIGGER ON deptest FROM regress_dep_user;
28 DROP USER regress_dep_user;
29 -- we are OK too if we drop the privileges all at once
30 REVOKE ALL ON deptest FROM regress_dep_user2;
31 DROP 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
36 ALTER TABLE deptest OWNER TO regress_dep_user3;
37 DROP USER regress_dep_user3;
38 ERROR: 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
42 DROP USER regress_dep_user3;
44 CREATE USER regress_dep_user0;
45 CREATE USER regress_dep_user1;
46 CREATE USER regress_dep_user2;
47 SET SESSION AUTHORIZATION regress_dep_user0;
49 DROP OWNED BY regress_dep_user1;
50 ERROR: permission denied to drop objects
51 DROP OWNED BY regress_dep_user0, regress_dep_user2;
52 ERROR: permission denied to drop objects
53 REASSIGN OWNED BY regress_dep_user0 TO regress_dep_user1;
54 ERROR: permission denied to reassign objects
55 REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user0;
56 ERROR: permission denied to reassign objects
57 -- this one is allowed
58 DROP OWNED BY regress_dep_user0;
59 CREATE TABLE deptest1 (f1 int unique);
60 GRANT ALL ON deptest1 TO regress_dep_user1 WITH GRANT OPTION;
61 SET SESSION AUTHORIZATION regress_dep_user1;
62 CREATE TABLE deptest (a serial primary key, b text);
63 GRANT ALL ON deptest1 TO regress_dep_user2;
64 RESET SESSION AUTHORIZATION;
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 | |
74 DROP OWNED BY regress_dep_user1;
78 Schema | Name | Type | Access privileges | Column privileges | Policies
79 --------+----------+-------+---------------------------------------------+-------------------+----------
80 public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 | |
85 -- Test REASSIGN OWNED
86 GRANT ALL ON deptest1 TO regress_dep_user1;
87 GRANT CREATE ON DATABASE regression TO regress_dep_user1;
88 SET SESSION AUTHORIZATION regress_dep_user1;
89 CREATE SCHEMA deptest;
90 CREATE TABLE deptest (a serial primary key, b text);
91 ALTER DEFAULT PRIVILEGES FOR ROLE regress_dep_user1 IN SCHEMA deptest
92 GRANT ALL ON TABLES TO regress_dep_user2;
93 CREATE FUNCTION deptest_func() RETURNS void LANGUAGE plpgsql
95 CREATE TYPE deptest_enum AS ENUM ('red');
96 CREATE TYPE deptest_range AS RANGE (SUBTYPE = int4);
97 CREATE TABLE deptest2 (f1 int);
98 -- make a serial column the hard way
100 ALTER TABLE deptest2 ALTER f1 SET DEFAULT nextval('ss1');
101 ALTER SEQUENCE ss1 OWNED BY deptest2.f1;
102 -- When reassigning ownership of a composite type, its pg_class entry
104 CREATE TYPE deptest_t AS (a int);
105 SELECT typowner = relowner
106 FROM pg_type JOIN pg_class c ON typrelid = c.oid WHERE typname = 'deptest_t';
112 RESET SESSION AUTHORIZATION;
113 REASSIGN OWNED BY regress_dep_user1 TO regress_dep_user2;
116 Schema | Name | Type | Owner
117 --------+---------+-------+-------------------
118 public | deptest | table | regress_dep_user2
121 SELECT typowner = relowner
122 FROM pg_type JOIN pg_class c ON typrelid = c.oid WHERE typname = 'deptest_t';
128 -- doesn't work: grant still exists
129 DROP USER regress_dep_user1;
130 ERROR: role "regress_dep_user1" cannot be dropped because some objects depend on it
131 DETAIL: privileges for table deptest1
132 privileges for database regression
133 owner of default privileges on new relations belonging to role regress_dep_user1 in schema deptest
134 DROP OWNED BY regress_dep_user1;
135 DROP USER regress_dep_user1;
137 DROP USER regress_dep_user2;
138 ERROR: role "regress_dep_user2" cannot be dropped because some objects depend on it
139 DROP OWNED BY regress_dep_user2, regress_dep_user0;
140 DROP USER regress_dep_user2;
141 DROP USER regress_dep_user0;