4 CREATE USER regression_user;
5 CREATE USER regression_user2;
6 CREATE USER regression_user3;
7 CREATE GROUP regression_group;
8 CREATE TABLE deptest (f1 serial primary key, f2 text);
9 NOTICE: CREATE TABLE will create implicit sequence "deptest_f1_seq" for serial column "deptest.f1"
10 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deptest_pkey" for table "deptest"
11 GRANT SELECT ON TABLE deptest TO GROUP regression_group;
12 GRANT ALL ON TABLE deptest TO regression_user, regression_user2;
13 -- can't drop neither because they have privileges somewhere
14 DROP USER regression_user;
15 ERROR: role "regression_user" cannot be dropped because some objects depend on it
16 DETAIL: access to table deptest
17 DROP GROUP regression_group;
18 ERROR: role "regression_group" cannot be dropped because some objects depend on it
19 DETAIL: access to table deptest
20 -- if we revoke the privileges we can drop the group
21 REVOKE SELECT ON deptest FROM GROUP regression_group;
22 DROP GROUP regression_group;
23 -- can't drop the user if we revoke the privileges partially
24 REVOKE SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES ON deptest FROM regression_user;
25 DROP USER regression_user;
26 ERROR: role "regression_user" cannot be dropped because some objects depend on it
27 DETAIL: access to table deptest
28 -- now we are OK to drop him
29 REVOKE TRIGGER ON deptest FROM regression_user;
30 DROP USER regression_user;
31 -- we are OK too if we drop the privileges all at once
32 REVOKE ALL ON deptest FROM regression_user2;
33 DROP USER regression_user2;
34 -- can't drop the owner of an object
35 -- the error message detail here would include a pg_toast_nnn name that
36 -- is not constant, so suppress it
38 ALTER TABLE deptest OWNER TO regression_user3;
39 DROP USER regression_user3;
40 ERROR: role "regression_user3" cannot be dropped because some objects depend on it
41 \set VERBOSITY default
42 -- if we drop the object, we can drop the user too
44 DROP USER regression_user3;
46 CREATE USER regression_user0;
47 CREATE USER regression_user1;
48 CREATE USER regression_user2;
49 SET SESSION AUTHORIZATION regression_user0;
51 DROP OWNED BY regression_user1;
52 ERROR: permission denied to drop objects
53 DROP OWNED BY regression_user0, regression_user2;
54 ERROR: permission denied to drop objects
55 REASSIGN OWNED BY regression_user0 TO regression_user1;
56 ERROR: permission denied to reassign objects
57 REASSIGN OWNED BY regression_user1 TO regression_user0;
58 ERROR: permission denied to reassign objects
59 -- this one is allowed
60 DROP OWNED BY regression_user0;
61 CREATE TABLE deptest1 (f1 int unique);
62 NOTICE: CREATE TABLE / UNIQUE will create implicit index "deptest1_f1_key" for table "deptest1"
63 GRANT ALL ON deptest1 TO regression_user1 WITH GRANT OPTION;
64 SET SESSION AUTHORIZATION regression_user1;
65 CREATE TABLE deptest (a serial primary key, b text);
66 NOTICE: CREATE TABLE will create implicit sequence "deptest_a_seq" for serial column "deptest.a"
67 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deptest_pkey" for table "deptest"
68 GRANT ALL ON deptest1 TO regression_user2;
69 RESET SESSION AUTHORIZATION;
71 Access privileges for database "regression"
72 Schema | Name | Type | Access privileges
73 --------+----------+-------+------------------------------------------------------------------------------------------------------------------------------------
74 public | deptest1 | table | {regression_user0=arwdxt/regression_user0,regression_user1=a*r*w*d*x*t*/regression_user0,regression_user2=arwdxt/regression_user1}
77 DROP OWNED BY regression_user1;
80 Access privileges for database "regression"
81 Schema | Name | Type | Access privileges
82 --------+----------+-------+--------------------------------------------
83 public | deptest1 | table | {regression_user0=arwdxt/regression_user0}
88 -- Test REASSIGN OWNED
89 GRANT ALL ON deptest1 TO regression_user1;
90 SET SESSION AUTHORIZATION regression_user1;
91 CREATE TABLE deptest (a serial primary key, b text);
92 NOTICE: CREATE TABLE will create implicit sequence "deptest_a_seq" for serial column "deptest.a"
93 NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deptest_pkey" for table "deptest"
94 CREATE TABLE deptest2 (f1 int);
95 -- make a serial column the hard way
97 ALTER TABLE deptest2 ALTER f1 SET DEFAULT nextval('ss1');
98 ALTER SEQUENCE ss1 OWNED BY deptest2.f1;
99 RESET SESSION AUTHORIZATION;
100 REASSIGN OWNED BY regression_user1 TO regression_user2;
103 Schema | Name | Type | Owner
104 --------+---------+-------+------------------
105 public | deptest | table | regression_user2
108 -- doesn't work: grant still exists
109 DROP USER regression_user1;
110 ERROR: role "regression_user1" cannot be dropped because some objects depend on it
111 DETAIL: access to table deptest1
112 DROP OWNED BY regression_user1;
113 DROP USER regression_user1;
115 DROP USER regression_user2;
116 ERROR: role "regression_user2" cannot be dropped because some objects depend on it
117 DROP OWNED BY regression_user2, regression_user0;
118 DROP USER regression_user2;
119 DROP USER regression_user0;