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
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
|
CREATE EXTENSION pg_permissions;
/* test roles (will be removed afterwards) */
CREATE ROLE users;
CREATE ROLE user1 LOGIN IN ROLE users;
CREATE ROLE user2 LOGIN IN ROLE users;
/* database */
-- desired permissions
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('users', ARRAY['CONNECT','TEMPORARY']::perm_type[], 'DATABASE', NULL, NULL, NULL),
('user1', ARRAY['CONNECT','TEMPORARY']::perm_type[], 'DATABASE', NULL, NULL, NULL),
('user2', ARRAY['CONNECT','TEMPORARY']::perm_type[], 'DATABASE', NULL, NULL, NULL);
-- this should fail
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('user2', ARRAY['CREATE']::perm_type[], 'DATABASE', 'public', NULL, NULL);
ERROR: new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL: Failing row contains (4, user2, {CREATE}, DATABASE, public, null, null).
-- actual permissions
REVOKE ALL ON DATABASE contrib_regression FROM PUBLIC;
GRANT CONNECT, TEMPORARY ON DATABASE contrib_regression TO users;
GRANT CREATE ON DATABASE contrib_regression TO user2; -- too much
/* schema */
-- desired permissions
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('users', ARRAY['USAGE']::perm_type[], 'SCHEMA', 'appschema', NULL, NULL),
('user1', ARRAY['USAGE','CREATE']::perm_type[], 'SCHEMA', 'appschema', NULL, NULL),
('user2', ARRAY['USAGE']::perm_type[], 'SCHEMA', 'appschema', NULL, NULL);
-- this should fail
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('user2', ARRAY['CREATE']::perm_type[], 'SCHEMA', 'appschema', 'sometable', NULL);
ERROR: new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL: Failing row contains (8, user2, {CREATE}, SCHEMA, appschema, sometable, null).
-- actual permissions
CREATE SCHEMA appschema;
GRANT USAGE ON SCHEMA appschema TO PUBLIC; -- missing CREATE for user1
GRANT CREATE ON SCHEMA appschema TO user2; -- too much
CREATE SCHEMA pgabc123;
GRANT USAGE ON SCHEMA pgabc123 TO user1;
/* table */
-- desired permissions
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('user1', ARRAY['SELECT','INSERT','UPDATE','DELETE']::perm_type[], 'TABLE', 'appschema', NULL, NULL),
('user2', ARRAY['SELECT']::perm_type[], 'TABLE', 'appschema', NULL, NULL),
('user1', ARRAY['SELECT']::perm_type[], 'TABLE', 'pgabc213', 'sometable', NULL);
-- this should fail
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('user2', ARRAY['INSERT']::perm_type[], 'TABLE', 'appschema', 'apptable', 'acolumn');
ERROR: new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL: Failing row contains (12, user2, {INSERT}, TABLE, appschema, apptable, acolumn).
-- actual permissions
CREATE TABLE appschema.apptable (
id integer PRIMARY KEY,
val text NOT NULL,
created timestamp with time zone NOT NULL DEFAULT current_timestamp
);
CREATE TABLE appschema.apptable2 (
id integer PRIMARY KEY,
val text NOT NULL,
created timestamp with time zone NOT NULL DEFAULT current_timestamp
); -- missing all permissions on this one
CREATE TABLE pgabc123.sometable (
id integer PRIMARY KEY,
val text NOT NULL,
created timestamp with time zone NOT NULL DEFAULT current_timestamp
);
GRANT SELECT, INSERT, UPDATE ON appschema.apptable TO user1; -- missing DELETE
GRANT SELECT, INSERT ON appschema.apptable TO user2; -- extra privilege INSERT
GRANT SELECT ON pgabc123.sometable TO user1;
/* column */
-- desired permissions
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('user1', ARRAY['SELECT','INSERT','UPDATE','REFERENCES']::perm_type[], 'COLUMN', 'appschema', 'apptable2', 'val');
-- this should fail
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('user2', ARRAY['DELETE']::perm_type[], 'COLUMN', 'appschema', 'apptable2', 'val');
ERROR: new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL: Failing row contains (14, user2, {DELETE}, COLUMN, appschema, apptable2, val).
-- actual permissions
-- missing REFERENCES for user1 on apptable2.val
GRANT UPDATE (val) ON appschema.apptable2 TO user2; -- extra privilege UPDATE
/* view */
-- desired permissions
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('user1', ARRAY['SELECT','INSERT','UPDATE','DELETE']::perm_type[], 'VIEW', 'appschema', 'appview', NULL),
('user2', ARRAY['SELECT']::perm_type[], 'VIEW', 'appschema', 'appview', NULL);
-- actual permissions
CREATE VIEW appschema.appview AS
SELECT id, val FROM appschema.apptable;
GRANT SELECT ON appschema.appview TO users; -- extra permission to "users"
GRANT INSERT, DELETE ON appschema.appview TO user1; -- missing UPDATE
/* sequence */
-- desired permissions
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('users', ARRAY['USAGE']::perm_type[], 'SEQUENCE', 'appschema', 'appseq', NULL),
('user1', ARRAY['USAGE','SELECT']::perm_type[], 'SEQUENCE', 'appschema', 'appseq', NULL),
('user2', ARRAY['USAGE']::perm_type[], 'SEQUENCE', 'appschema', 'appseq', NULL);
-- actual permissions
CREATE SEQUENCE appschema.appseq;
GRANT USAGE ON SEQUENCE appschema.appseq TO users; -- missing SELECT for user1
GRANT UPDATE ON SEQUENCE appschema.appseq TO user2; -- extra permission UPDATE
/* function */
-- desired permissions
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('user1', ARRAY['EXECUTE']::perm_type[], 'FUNCTION', 'appschema', 'appfun(integer)', NULL),
('user2', ARRAY['EXECUTE']::perm_type[], 'FUNCTION', 'appschema', 'appfun(integer)', NULL);
-- this should fail
INSERT INTO permission_target
(role_name, permissions, object_type, schema_name, object_name, column_name)
VALUES ('users', ARRAY['UPDATE']::perm_type[], 'FUNCTION', 'appschema', 'appfun(integer)', NULL);
ERROR: new row for relation "permission_target" violates check constraint "permission_target_valid"
DETAIL: Failing row contains (22, users, {UPDATE}, FUNCTION, appschema, appfun(integer), null).
-- actual permissions
CREATE FUNCTION appschema.appfun(i integer) RETURNS integer
LANGUAGE sql IMMUTABLE AS
'SELECT i + 2'; -- extra permission for "users"
/* report all permissions */
SELECT object_type, role_name, schema_name, object_name, column_name, permission
FROM all_permissions
WHERE granted
AND role_name IN ('users', 'user1', 'user2')
AND coalesce(schema_name, 'appschema') IN ('appschema', 'pgabc123')
ORDER BY object_type, role_name, schema_name, object_name, column_name, permission;
object_type | role_name | schema_name | object_name | column_name | permission
-------------+-----------+-------------+-----------------+-------------+------------
TABLE | user1 | appschema | apptable | | SELECT
TABLE | user1 | appschema | apptable | | INSERT
TABLE | user1 | appschema | apptable | | UPDATE
TABLE | user1 | pgabc123 | sometable | | SELECT
TABLE | user2 | appschema | apptable | | SELECT
TABLE | user2 | appschema | apptable | | INSERT
VIEW | user1 | appschema | appview | | SELECT
VIEW | user1 | appschema | appview | | INSERT
VIEW | user1 | appschema | appview | | DELETE
VIEW | user2 | appschema | appview | | SELECT
VIEW | users | appschema | appview | | SELECT
COLUMN | user2 | appschema | apptable2 | val | UPDATE
SEQUENCE | user1 | appschema | appseq | | USAGE
SEQUENCE | user2 | appschema | appseq | | UPDATE
SEQUENCE | user2 | appschema | appseq | | USAGE
SEQUENCE | users | appschema | appseq | | USAGE
FUNCTION | user1 | appschema | appfun(integer) | | EXECUTE
FUNCTION | user2 | appschema | appfun(integer) | | EXECUTE
FUNCTION | users | appschema | appfun(integer) | | EXECUTE
SCHEMA | user1 | appschema | | | USAGE
SCHEMA | user1 | pgabc123 | | | USAGE
SCHEMA | user2 | appschema | | | USAGE
SCHEMA | user2 | appschema | | | CREATE
SCHEMA | users | appschema | | | USAGE
DATABASE | user1 | | | | CONNECT
DATABASE | user1 | | | | TEMPORARY
DATABASE | user2 | | | | CREATE
DATABASE | user2 | | | | CONNECT
DATABASE | user2 | | | | TEMPORARY
DATABASE | users | | | | CONNECT
DATABASE | users | | | | TEMPORARY
(31 rows)
/* report differences */
SELECT * FROM permission_diffs()
WHERE role_name IN ('users', 'user1', 'user2')
ORDER BY object_type, schema_name, object_name, column_name, role_name, permission, missing;
missing | role_name | object_type | schema_name | object_name | column_name | permission
---------+-----------+-------------+-------------+-----------------+-------------+------------
t | user1 | TABLE | appschema | apptable | | DELETE
f | user2 | TABLE | appschema | apptable | | INSERT
t | user1 | TABLE | appschema | apptable2 | | SELECT
t | user1 | TABLE | appschema | apptable2 | | INSERT
t | user1 | TABLE | appschema | apptable2 | | UPDATE
t | user1 | TABLE | appschema | apptable2 | | DELETE
t | user2 | TABLE | appschema | apptable2 | | SELECT
t | user1 | VIEW | appschema | appview | | UPDATE
f | users | VIEW | appschema | appview | | SELECT
t | user1 | COLUMN | appschema | apptable2 | val | SELECT
t | user1 | COLUMN | appschema | apptable2 | val | INSERT
t | user1 | COLUMN | appschema | apptable2 | val | UPDATE
t | user1 | COLUMN | appschema | apptable2 | val | REFERENCES
f | user2 | COLUMN | appschema | apptable2 | val | UPDATE
t | user1 | SEQUENCE | appschema | appseq | | SELECT
f | user2 | SEQUENCE | appschema | appseq | | UPDATE
f | users | FUNCTION | appschema | appfun(integer) | | EXECUTE
t | user1 | SCHEMA | appschema | | | CREATE
f | user2 | SCHEMA | appschema | | | CREATE
f | user2 | DATABASE | | | | CREATE
(20 rows)
/* fix some of the differences */
UPDATE column_permissions SET
granted = TRUE
WHERE role_name = 'user1'
AND schema_name = 'appschema'
AND object_name = 'apptable2'
AND column_name = 'val'
AND permission = 'REFERENCES';
UPDATE all_permissions SET
granted = FALSE
WHERE object_type = 'TABLE'
AND role_name = 'user2'
AND schema_name = 'appschema'
AND object_name = 'apptable'
AND permission = 'INSERT';
/* check the fixed permissions */
SELECT * FROM permission_diffs()
WHERE role_name IN ('users', 'user1', 'user2')
ORDER BY object_type, schema_name, object_name, column_name, role_name, permission, missing;
missing | role_name | object_type | schema_name | object_name | column_name | permission
---------+-----------+-------------+-------------+-----------------+-------------+------------
t | user1 | TABLE | appschema | apptable | | DELETE
t | user1 | TABLE | appschema | apptable2 | | SELECT
t | user1 | TABLE | appschema | apptable2 | | INSERT
t | user1 | TABLE | appschema | apptable2 | | UPDATE
t | user1 | TABLE | appschema | apptable2 | | DELETE
t | user2 | TABLE | appschema | apptable2 | | SELECT
t | user1 | VIEW | appschema | appview | | UPDATE
f | users | VIEW | appschema | appview | | SELECT
t | user1 | COLUMN | appschema | apptable2 | val | SELECT
t | user1 | COLUMN | appschema | apptable2 | val | INSERT
t | user1 | COLUMN | appschema | apptable2 | val | UPDATE
f | user2 | COLUMN | appschema | apptable2 | val | UPDATE
t | user1 | SEQUENCE | appschema | appseq | | SELECT
f | user2 | SEQUENCE | appschema | appseq | | UPDATE
f | users | FUNCTION | appschema | appfun(integer) | | EXECUTE
t | user1 | SCHEMA | appschema | | | CREATE
f | user2 | SCHEMA | appschema | | | CREATE
f | user2 | DATABASE | | | | CREATE
(18 rows)
/* clean up */
DROP FUNCTION appschema.appfun(integer);
DROP VIEW appschema.appview;
DROP SEQUENCE appschema.appseq;
DROP TABLE appschema.apptable;
DROP TABLE appschema.apptable2;
DROP TABLE pgabc123.sometable;
DROP SCHEMA appschema;
DROP SCHEMA pgabc123;
REVOKE ALL ON DATABASE contrib_regression FROM user1, user2, users;
DROP ROLE user1;
DROP ROLE user2;
DROP ROLE users;
|
