2
-- Test access privileges
5
CREATE USER regressuser1;
6
CREATE USER regressuser2;
7
CREATE USER regressuser3;
8
CREATE USER regressuser4;
9
CREATE USER regressuser4; -- duplicate
11
CREATE GROUP regressgroup1;
12
CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;
14
ALTER GROUP regressgroup1 ADD USER regressuser4;
16
ALTER GROUP regressgroup2 ADD USER regressuser2; -- duplicate
17
ALTER GROUP regressgroup2 DROP USER regressuser2;
18
ALTER GROUP regressgroup2 ADD USER regressuser4;
21
-- test owner privileges
23
SET SESSION AUTHORIZATION regressuser1;
24
SELECT session_user, current_user;
26
CREATE TABLE atest1 ( a int, b text );
28
INSERT INTO atest1 VALUES (1, 'one');
30
UPDATE atest1 SET a = 1 WHERE b = 'blech';
31
LOCK atest1 IN ACCESS EXCLUSIVE MODE;
33
REVOKE ALL ON atest1 FROM PUBLIC;
36
GRANT ALL ON atest1 TO regressuser2;
37
GRANT SELECT ON atest1 TO regressuser3, regressuser4;
40
CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
41
GRANT SELECT ON atest2 TO regressuser2;
42
GRANT UPDATE ON atest2 TO regressuser3;
43
GRANT INSERT ON atest2 TO regressuser4;
46
SET SESSION AUTHORIZATION regressuser2;
47
SELECT session_user, current_user;
49
-- try various combinations of queries on atest1 and atest2
51
SELECT * FROM atest1; -- ok
52
SELECT * FROM atest2; -- ok
53
INSERT INTO atest1 VALUES (2, 'two'); -- ok
54
INSERT INTO atest2 VALUES ('foo', true); -- fail
55
INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
56
UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
57
UPDATE atest2 SET col2 = NOT col2; -- fail
58
SELECT * FROM atest1 FOR UPDATE; -- ok
59
SELECT * FROM atest2 FOR UPDATE; -- fail
60
DELETE FROM atest2; -- fail
61
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
62
COPY atest2 FROM stdin; -- fail
63
GRANT ALL ON atest1 TO PUBLIC; -- fail
65
-- checks in subquery, both ok
66
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
67
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
70
SET SESSION AUTHORIZATION regressuser3;
71
SELECT session_user, current_user;
73
SELECT * FROM atest1; -- ok
74
SELECT * FROM atest2; -- fail
75
INSERT INTO atest1 VALUES (2, 'two'); -- fail
76
INSERT INTO atest2 VALUES ('foo', true); -- fail
77
INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
78
UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
79
UPDATE atest2 SET col2 = NULL; -- ok
80
UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
81
UPDATE atest2 SET col2 = true WHERE atest1.a = 5; -- ok
82
SELECT * FROM atest1 FOR UPDATE; -- fail
83
SELECT * FROM atest2 FOR UPDATE; -- fail
84
DELETE FROM atest2; -- fail
85
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
86
COPY atest2 FROM stdin; -- fail
88
-- checks in subquery, both fail
89
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
90
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
92
SET SESSION AUTHORIZATION regressuser4;
93
COPY atest2 FROM stdin; -- ok
96
SELECT * FROM atest1; -- ok
101
SET SESSION AUTHORIZATION regressuser3;
102
CREATE TABLE atest3 (one int, two int, three int);
103
GRANT DELETE ON atest3 TO GROUP regressgroup2;
105
SET SESSION AUTHORIZATION regressuser1;
107
SELECT * FROM atest3; -- fail
108
DELETE FROM atest3; -- ok
113
SET SESSION AUTHORIZATION regressuser3;
115
CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
116
/* The next *should* fail, but it's not implemented that way yet. */
117
CREATE VIEW atestv2 AS SELECT * FROM atest2;
118
CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
120
SELECT * FROM atestv1; -- ok
121
SELECT * FROM atestv2; -- fail
122
GRANT SELECT ON atestv1, atestv3 TO regressuser4;
123
GRANT SELECT ON atestv2 TO regressuser2;
125
SET SESSION AUTHORIZATION regressuser4;
127
SELECT * FROM atestv1; -- ok
128
SELECT * FROM atestv2; -- fail
129
SELECT * FROM atestv3; -- ok
131
CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view
132
SELECT * FROM atestv4; -- ok
133
GRANT SELECT ON atestv4 TO regressuser2;
135
SET SESSION AUTHORIZATION regressuser2;
137
-- Two complex cases:
139
SELECT * FROM atestv3; -- fail
140
SELECT * FROM atestv4; -- ok (even though regressuser2 cannot access underlying atestv3)
142
SELECT * FROM atest2; -- ok
143
SELECT * FROM atestv2; -- fail (even though regressuser2 can access underlying atest2)
146
-- privileges on functions, languages
148
-- switch to superuser
151
REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
152
GRANT USAGE ON LANGUAGE sql TO regressuser1; -- ok
153
GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
155
SET SESSION AUTHORIZATION regressuser1;
156
GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail
157
CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
158
CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
160
REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC;
161
GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regressuser2;
162
GRANT USAGE ON FUNCTION testfunc1(int) TO regressuser3; -- semantic error
163
GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regressuser4;
164
GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regressuser4;
166
CREATE FUNCTION testfunc4(boolean) RETURNS text
167
AS 'select col1 from atest2 where col2 = $1;'
168
LANGUAGE sql SECURITY DEFINER;
169
GRANT EXECUTE ON FUNCTION testfunc4(boolean) TO regressuser3;
171
SET SESSION AUTHORIZATION regressuser2;
172
SELECT testfunc1(5), testfunc2(5); -- ok
173
CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
175
SET SESSION AUTHORIZATION regressuser3;
176
SELECT testfunc1(5); -- fail
177
SELECT col1 FROM atest2 WHERE col2 = true; -- fail
178
SELECT testfunc4(true); -- ok
180
SET SESSION AUTHORIZATION regressuser4;
181
SELECT testfunc1(5); -- ok
183
DROP FUNCTION testfunc1(int); -- fail
187
DROP FUNCTION testfunc1(int); -- ok
189
GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
192
-- has_table_privilege function
195
select has_table_privilege(NULL,'pg_shadow','select');
196
select has_table_privilege('pg_shad','select');
197
select has_table_privilege('nosuchuser','pg_shadow','select');
198
select has_table_privilege('pg_shadow','sel');
199
select has_table_privilege(-999999,'pg_shadow','update');
200
select has_table_privilege(1,'rule');
205
select has_table_privilege(current_user,'pg_shadow','select');
206
select has_table_privilege(current_user,'pg_shadow','insert');
208
select has_table_privilege(t2.usesysid,'pg_shadow','update')
209
from (select usesysid from pg_user where usename = current_user) as t2;
210
select has_table_privilege(t2.usesysid,'pg_shadow','delete')
211
from (select usesysid from pg_user where usename = current_user) as t2;
213
select has_table_privilege(current_user,t1.oid,'rule')
214
from (select oid from pg_class where relname = 'pg_shadow') as t1;
215
select has_table_privilege(current_user,t1.oid,'references')
216
from (select oid from pg_class where relname = 'pg_shadow') as t1;
218
select has_table_privilege(t2.usesysid,t1.oid,'select')
219
from (select oid from pg_class where relname = 'pg_shadow') as t1,
220
(select usesysid from pg_user where usename = current_user) as t2;
221
select has_table_privilege(t2.usesysid,t1.oid,'insert')
222
from (select oid from pg_class where relname = 'pg_shadow') as t1,
223
(select usesysid from pg_user where usename = current_user) as t2;
225
select has_table_privilege('pg_shadow','update');
226
select has_table_privilege('pg_shadow','delete');
228
select has_table_privilege(t1.oid,'select')
229
from (select oid from pg_class where relname = 'pg_shadow') as t1;
230
select has_table_privilege(t1.oid,'trigger')
231
from (select oid from pg_class where relname = 'pg_shadow') as t1;
234
SET SESSION AUTHORIZATION regressuser3;
236
select has_table_privilege(current_user,'pg_class','select');
237
select has_table_privilege(current_user,'pg_class','insert');
239
select has_table_privilege(t2.usesysid,'pg_class','update')
240
from (select usesysid from pg_user where usename = current_user) as t2;
241
select has_table_privilege(t2.usesysid,'pg_class','delete')
242
from (select usesysid from pg_user where usename = current_user) as t2;
244
select has_table_privilege(current_user,t1.oid,'rule')
245
from (select oid from pg_class where relname = 'pg_class') as t1;
246
select has_table_privilege(current_user,t1.oid,'references')
247
from (select oid from pg_class where relname = 'pg_class') as t1;
249
select has_table_privilege(t2.usesysid,t1.oid,'select')
250
from (select oid from pg_class where relname = 'pg_class') as t1,
251
(select usesysid from pg_user where usename = current_user) as t2;
252
select has_table_privilege(t2.usesysid,t1.oid,'insert')
253
from (select oid from pg_class where relname = 'pg_class') as t1,
254
(select usesysid from pg_user where usename = current_user) as t2;
256
select has_table_privilege('pg_class','update');
257
select has_table_privilege('pg_class','delete');
259
select has_table_privilege(t1.oid,'select')
260
from (select oid from pg_class where relname = 'pg_class') as t1;
261
select has_table_privilege(t1.oid,'trigger')
262
from (select oid from pg_class where relname = 'pg_class') as t1;
264
select has_table_privilege(current_user,'atest1','select');
265
select has_table_privilege(current_user,'atest1','insert');
267
select has_table_privilege(t2.usesysid,'atest1','update')
268
from (select usesysid from pg_user where usename = current_user) as t2;
269
select has_table_privilege(t2.usesysid,'atest1','delete')
270
from (select usesysid from pg_user where usename = current_user) as t2;
272
select has_table_privilege(current_user,t1.oid,'rule')
273
from (select oid from pg_class where relname = 'atest1') as t1;
274
select has_table_privilege(current_user,t1.oid,'references')
275
from (select oid from pg_class where relname = 'atest1') as t1;
277
select has_table_privilege(t2.usesysid,t1.oid,'select')
278
from (select oid from pg_class where relname = 'atest1') as t1,
279
(select usesysid from pg_user where usename = current_user) as t2;
280
select has_table_privilege(t2.usesysid,t1.oid,'insert')
281
from (select oid from pg_class where relname = 'atest1') as t1,
282
(select usesysid from pg_user where usename = current_user) as t2;
284
select has_table_privilege('atest1','update');
285
select has_table_privilege('atest1','delete');
287
select has_table_privilege(t1.oid,'select')
288
from (select oid from pg_class where relname = 'atest1') as t1;
289
select has_table_privilege(t1.oid,'trigger')
290
from (select oid from pg_class where relname = 'atest1') as t1;
295
SET SESSION AUTHORIZATION regressuser1;
297
CREATE TABLE atest4 (a int);
299
GRANT SELECT ON atest4 TO regressuser2 WITH GRANT OPTION;
300
GRANT UPDATE ON atest4 TO regressuser2;
301
GRANT SELECT ON atest4 TO GROUP regressgroup1 WITH GRANT OPTION; -- fail
303
SET SESSION AUTHORIZATION regressuser2;
305
GRANT SELECT ON atest4 TO regressuser3;
306
GRANT UPDATE ON atest4 TO regressuser3; -- fail
308
SET SESSION AUTHORIZATION regressuser1;
310
REVOKE SELECT ON atest4 FROM regressuser3; -- does nothing
311
SELECT has_table_privilege('regressuser3', 'atest4', 'SELECT'); -- true
312
REVOKE SELECT ON atest4 FROM regressuser2; -- fail
313
REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regressuser2 CASCADE; -- ok
314
SELECT has_table_privilege('regressuser2', 'atest4', 'SELECT'); -- true
315
SELECT has_table_privilege('regressuser3', 'atest4', 'SELECT'); -- false
317
SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
324
DROP FUNCTION testfunc2(int);
325
DROP FUNCTION testfunc4(boolean);
329
-- this should cascade to drop atestv4
330
DROP VIEW atestv3 CASCADE;
331
-- this should complain "does not exist"
339
DROP GROUP regressgroup1;
340
DROP GROUP regressgroup2;
342
DROP USER regressuser1;
343
DROP USER regressuser2;
344
DROP USER regressuser3;
345
DROP USER regressuser4;