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, TRUNCATE, 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;
72
Schema | Name | Type | Access privileges | Column access privileges
73
--------+----------+-------+--------------------------------------------------+--------------------------
74
public | deptest1 | table | regression_user0=arwdDxt/regression_user0 |
75
: regression_user1=a*r*w*d*D*x*t*/regression_user0
76
: regression_user2=arwdDxt/regression_user1
79
DROP OWNED BY regression_user1;
83
Schema | Name | Type | Access privileges | Column access privileges
84
--------+----------+-------+-------------------------------------------+--------------------------
85
public | deptest1 | table | regression_user0=arwdDxt/regression_user0 |
90
-- Test REASSIGN OWNED
91
GRANT ALL ON deptest1 TO regression_user1;
92
SET SESSION AUTHORIZATION regression_user1;
93
CREATE TABLE deptest (a serial primary key, b text);
94
NOTICE: CREATE TABLE will create implicit sequence "deptest_a_seq" for serial column "deptest.a"
95
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "deptest_pkey" for table "deptest"
96
CREATE TABLE deptest2 (f1 int);
97
-- make a serial column the hard way
99
ALTER TABLE deptest2 ALTER f1 SET DEFAULT nextval('ss1');
100
ALTER SEQUENCE ss1 OWNED BY deptest2.f1;
101
RESET SESSION AUTHORIZATION;
102
REASSIGN OWNED BY regression_user1 TO regression_user2;
105
Schema | Name | Type | Owner
106
--------+---------+-------+------------------
107
public | deptest | table | regression_user2
110
-- doesn't work: grant still exists
111
DROP USER regression_user1;
112
ERROR: role "regression_user1" cannot be dropped because some objects depend on it
113
DETAIL: access to table deptest1
114
DROP OWNED BY regression_user1;
115
DROP USER regression_user1;
117
DROP USER regression_user2;
118
ERROR: role "regression_user2" cannot be dropped because some objects depend on it
119
DROP OWNED BY regression_user2, regression_user0;
120
DROP USER regression_user2;
121
DROP USER regression_user0;