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
|
--
-- DEPENDENCIES
--
CREATE USER regression_user;
CREATE USER regression_user2;
CREATE USER regression_user3;
CREATE GROUP regression_group;
CREATE TABLE deptest (f1 serial primary key, f2 text);
GRANT SELECT ON TABLE deptest TO GROUP regression_group;
GRANT ALL ON TABLE deptest TO regression_user, regression_user2;
-- can't drop neither because they have privileges somewhere
DROP USER regression_user;
ERROR: role "regression_user" cannot be dropped because some objects depend on it
DETAIL: privileges for table deptest
DROP GROUP regression_group;
ERROR: role "regression_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 regression_group;
DROP GROUP regression_group;
-- can't drop the user if we revoke the privileges partially
REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regression_user;
DROP USER regression_user;
ERROR: role "regression_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 regression_user;
DROP USER regression_user;
-- we are OK too if we drop the privileges all at once
REVOKE ALL ON deptest FROM regression_user2;
DROP USER regression_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 regression_user3;
DROP USER regression_user3;
ERROR: role "regression_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 regression_user3;
-- Test DROP OWNED
CREATE USER regression_user0;
CREATE USER regression_user1;
CREATE USER regression_user2;
SET SESSION AUTHORIZATION regression_user0;
-- permission denied
DROP OWNED BY regression_user1;
ERROR: permission denied to drop objects
DROP OWNED BY regression_user0, regression_user2;
ERROR: permission denied to drop objects
REASSIGN OWNED BY regression_user0 TO regression_user1;
ERROR: permission denied to reassign objects
REASSIGN OWNED BY regression_user1 TO regression_user0;
ERROR: permission denied to reassign objects
-- this one is allowed
DROP OWNED BY regression_user0;
CREATE TABLE deptest1 (f1 int unique);
GRANT ALL ON deptest1 TO regression_user1 WITH GRANT OPTION;
SET SESSION AUTHORIZATION regression_user1;
CREATE TABLE deptest (a serial primary key, b text);
GRANT ALL ON deptest1 TO regression_user2;
RESET SESSION AUTHORIZATION;
\z deptest1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------+-------+--------------------------------------------------+-------------------+----------
public | deptest1 | table | regression_user0=arwdDxt/regression_user0 +| |
| | | regression_user1=a*r*w*d*D*x*t*/regression_user0+| |
| | | regression_user2=arwdDxt/regression_user1 | |
(1 row)
DROP OWNED BY regression_user1;
-- all grants revoked
\z deptest1
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+----------+-------+-------------------------------------------+-------------------+----------
public | deptest1 | table | regression_user0=arwdDxt/regression_user0 | |
(1 row)
-- table was dropped
\d deptest
-- Test REASSIGN OWNED
GRANT ALL ON deptest1 TO regression_user1;
GRANT CREATE ON DATABASE regression TO regression_user1;
SET SESSION AUTHORIZATION regression_user1;
CREATE SCHEMA deptest;
CREATE TABLE deptest (a serial primary key, b text);
ALTER DEFAULT PRIVILEGES FOR ROLE regression_user1 IN SCHEMA deptest
GRANT ALL ON TABLES TO regression_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 regression_user1 TO regression_user2;
\dt deptest
List of relations
Schema | Name | Type | Owner
--------+---------+-------+------------------
public | deptest | table | regression_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 regression_user1;
ERROR: role "regression_user1" cannot be dropped because some objects depend on it
DETAIL: owner of default privileges on new relations belonging to role regression_user1 in schema deptest
privileges for database regression
privileges for table deptest1
DROP OWNED BY regression_user1;
DROP USER regression_user1;
\set VERBOSITY terse
DROP USER regression_user2;
ERROR: role "regression_user2" cannot be dropped because some objects depend on it
DROP OWNED BY regression_user2, regression_user0;
DROP USER regression_user2;
DROP USER regression_user0;
|
