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