2 -- Test access privileges
4 CREATE USER regressuser1;
5 CREATE USER regressuser2;
6 CREATE USER regressuser3;
7 CREATE USER regressuser4;
8 CREATE USER regressuser4; -- duplicate
9 ERROR: CREATE USER: user name "regressuser4" already exists
10 CREATE GROUP regressgroup1;
11 CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;
12 ALTER GROUP regressgroup1 ADD USER regressuser4;
13 ALTER GROUP regressgroup2 ADD USER regressuser2; -- duplicate
14 NOTICE: ALTER GROUP: user "regressuser2" is already in group "regressgroup2"
15 ALTER GROUP regressgroup2 DROP USER regressuser2;
16 ALTER GROUP regressgroup2 ADD USER regressuser4;
17 -- test owner privileges
18 SET SESSION AUTHORIZATION regressuser1;
19 SELECT session_user, current_user;
20 session_user | current_user
21 --------------+--------------
22 regressuser1 | regressuser1
25 CREATE TABLE atest1 ( a int, b text );
31 INSERT INTO atest1 VALUES (1, 'one');
33 UPDATE atest1 SET a = 1 WHERE b = 'blech';
34 LOCK atest1 IN ACCESS EXCLUSIVE MODE;
35 REVOKE ALL ON atest1 FROM PUBLIC;
41 GRANT ALL ON atest1 TO regressuser2;
42 GRANT SELECT ON atest1 TO regressuser3, regressuser4;
48 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
49 GRANT SELECT ON atest2 TO regressuser2;
50 GRANT UPDATE ON atest2 TO regressuser3;
51 GRANT INSERT ON atest2 TO regressuser4;
52 SET SESSION AUTHORIZATION regressuser2;
53 SELECT session_user, current_user;
54 session_user | current_user
55 --------------+--------------
56 regressuser2 | regressuser2
59 -- try various combinations of queries on atest1 and atest2
60 SELECT * FROM atest1; -- ok
65 SELECT * FROM atest2; -- ok
70 INSERT INTO atest1 VALUES (2, 'two'); -- ok
71 INSERT INTO atest2 VALUES ('foo', true); -- fail
72 ERROR: atest2: Permission denied.
73 INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
74 UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
75 UPDATE atest2 SET col2 = NOT col2; -- fail
76 ERROR: atest2: Permission denied.
77 SELECT * FROM atest1 FOR UPDATE; -- ok
84 SELECT * FROM atest2 FOR UPDATE; -- fail
85 ERROR: atest2: Permission denied.
86 DELETE FROM atest2; -- fail
87 ERROR: atest2: Permission denied.
88 LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
89 ERROR: LOCK TABLE: permission denied
90 COPY atest2 FROM stdin; -- fail
91 ERROR: atest2: Permission denied.
92 GRANT ALL ON atest1 TO PUBLIC; -- fail
93 ERROR: permission denied
94 -- checks in subquery, both ok
95 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
100 SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
105 SET SESSION AUTHORIZATION regressuser3;
106 SELECT session_user, current_user;
107 session_user | current_user
108 --------------+--------------
109 regressuser3 | regressuser3
112 SELECT * FROM atest1; -- ok
119 SELECT * FROM atest2; -- fail
120 ERROR: atest2: Permission denied.
121 INSERT INTO atest1 VALUES (2, 'two'); -- fail
122 ERROR: atest1: Permission denied.
123 INSERT INTO atest2 VALUES ('foo', true); -- fail
124 ERROR: atest2: Permission denied.
125 INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
126 ERROR: atest1: Permission denied.
127 UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
128 ERROR: atest1: Permission denied.
129 UPDATE atest2 SET col2 = NULL; -- ok
130 UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
131 ERROR: atest2: Permission denied.
132 UPDATE atest2 SET col2 = true WHERE atest1.a = 5; -- ok
133 SELECT * FROM atest1 FOR UPDATE; -- fail
134 ERROR: atest1: Permission denied.
135 SELECT * FROM atest2 FOR UPDATE; -- fail
136 ERROR: atest2: Permission denied.
137 DELETE FROM atest2; -- fail
138 ERROR: atest2: Permission denied.
139 LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
140 COPY atest2 FROM stdin; -- fail
141 ERROR: atest2: Permission denied.
142 -- checks in subquery, both fail
143 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
144 ERROR: atest2: Permission denied.
145 SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
146 ERROR: atest2: Permission denied.
147 SET SESSION AUTHORIZATION regressuser4;
148 COPY atest2 FROM stdin; -- ok
149 SELECT * FROM atest1; -- ok
157 SET SESSION AUTHORIZATION regressuser3;
158 CREATE TABLE atest3 (one int, two int, three int);
159 GRANT DELETE ON atest3 TO GROUP regressgroup2;
160 SET SESSION AUTHORIZATION regressuser1;
161 SELECT * FROM atest3; -- fail
162 ERROR: atest3: Permission denied.
163 DELETE FROM atest3; -- ok
165 SET SESSION AUTHORIZATION regressuser3;
166 CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
167 /* The next *should* fail, but it's not implemented that way yet. */
168 CREATE VIEW atestv2 AS SELECT * FROM atest2;
169 CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
170 SELECT * FROM atestv1; -- ok
177 GRANT SELECT ON atestv1, atestv3 TO regressuser4;
178 SET SESSION AUTHORIZATION regressuser4;
179 SELECT * FROM atestv1; -- ok
186 SELECT * FROM atestv3; -- ok
191 -- has_table_privilege function
193 select has_table_privilege(NULL,'pg_shadow','select');
195 ---------------------
199 select has_table_privilege('pg_shad','select');
200 ERROR: has_table_privilege: relation "pg_shad" does not exist
201 select has_table_privilege('nosuchuser','pg_shadow','select');
202 ERROR: user "nosuchuser" does not exist
203 select has_table_privilege('pg_shadow','sel');
204 ERROR: has_table_privilege: invalid privilege type sel
205 select has_table_privilege(-999999,'pg_shadow','update');
206 ERROR: pg_aclcheck: invalid user id 4293967297
207 select has_table_privilege(1,'rule');
208 ERROR: has_table_privilege: invalid relation oid 1
211 select has_table_privilege(current_user,'pg_shadow','select');
213 ---------------------
217 select has_table_privilege(current_user,'pg_shadow','insert');
219 ---------------------
223 select has_table_privilege(t2.usesysid,'pg_shadow','update')
224 from (select usesysid from pg_user where usename = current_user) as t2;
226 ---------------------
230 select has_table_privilege(t2.usesysid,'pg_shadow','delete')
231 from (select usesysid from pg_user where usename = current_user) as t2;
233 ---------------------
237 select has_table_privilege(current_user,t1.oid,'rule')
238 from (select oid from pg_class where relname = 'pg_shadow') as t1;
240 ---------------------
244 select has_table_privilege(current_user,t1.oid,'references')
245 from (select oid from pg_class where relname = 'pg_shadow') as t1;
247 ---------------------
251 select has_table_privilege(t2.usesysid,t1.oid,'select')
252 from (select oid from pg_class where relname = 'pg_shadow') as t1,
253 (select usesysid from pg_user where usename = current_user) as t2;
255 ---------------------
259 select has_table_privilege(t2.usesysid,t1.oid,'insert')
260 from (select oid from pg_class where relname = 'pg_shadow') as t1,
261 (select usesysid from pg_user where usename = current_user) as t2;
263 ---------------------
267 select has_table_privilege('pg_shadow','update');
269 ---------------------
273 select has_table_privilege('pg_shadow','delete');
275 ---------------------
279 select has_table_privilege(t1.oid,'select')
280 from (select oid from pg_class where relname = 'pg_shadow') as t1;
282 ---------------------
286 select has_table_privilege(t1.oid,'trigger')
287 from (select oid from pg_class where relname = 'pg_shadow') as t1;
289 ---------------------
294 SET SESSION AUTHORIZATION regressuser3;
295 select has_table_privilege(current_user,'pg_class','select');
297 ---------------------
301 select has_table_privilege(current_user,'pg_class','insert');
303 ---------------------
307 select has_table_privilege(t2.usesysid,'pg_class','update')
308 from (select usesysid from pg_user where usename = current_user) as t2;
310 ---------------------
314 select has_table_privilege(t2.usesysid,'pg_class','delete')
315 from (select usesysid from pg_user where usename = current_user) as t2;
317 ---------------------
321 select has_table_privilege(current_user,t1.oid,'rule')
322 from (select oid from pg_class where relname = 'pg_class') as t1;
324 ---------------------
328 select has_table_privilege(current_user,t1.oid,'references')
329 from (select oid from pg_class where relname = 'pg_class') as t1;
331 ---------------------
335 select has_table_privilege(t2.usesysid,t1.oid,'select')
336 from (select oid from pg_class where relname = 'pg_class') as t1,
337 (select usesysid from pg_user where usename = current_user) as t2;
339 ---------------------
343 select has_table_privilege(t2.usesysid,t1.oid,'insert')
344 from (select oid from pg_class where relname = 'pg_class') as t1,
345 (select usesysid from pg_user where usename = current_user) as t2;
347 ---------------------
351 select has_table_privilege('pg_class','update');
353 ---------------------
357 select has_table_privilege('pg_class','delete');
359 ---------------------
363 select has_table_privilege(t1.oid,'select')
364 from (select oid from pg_class where relname = 'pg_class') as t1;
366 ---------------------
370 select has_table_privilege(t1.oid,'trigger')
371 from (select oid from pg_class where relname = 'pg_class') as t1;
373 ---------------------
377 select has_table_privilege(current_user,'atest1','select');
379 ---------------------
383 select has_table_privilege(current_user,'atest1','insert');
385 ---------------------
389 select has_table_privilege(t2.usesysid,'atest1','update')
390 from (select usesysid from pg_user where usename = current_user) as t2;
392 ---------------------
396 select has_table_privilege(t2.usesysid,'atest1','delete')
397 from (select usesysid from pg_user where usename = current_user) as t2;
399 ---------------------
403 select has_table_privilege(current_user,t1.oid,'rule')
404 from (select oid from pg_class where relname = 'atest1') as t1;
406 ---------------------
410 select has_table_privilege(current_user,t1.oid,'references')
411 from (select oid from pg_class where relname = 'atest1') as t1;
413 ---------------------
417 select has_table_privilege(t2.usesysid,t1.oid,'select')
418 from (select oid from pg_class where relname = 'atest1') as t1,
419 (select usesysid from pg_user where usename = current_user) as t2;
421 ---------------------
425 select has_table_privilege(t2.usesysid,t1.oid,'insert')
426 from (select oid from pg_class where relname = 'atest1') as t1,
427 (select usesysid from pg_user where usename = current_user) as t2;
429 ---------------------
433 select has_table_privilege('atest1','update');
435 ---------------------
439 select has_table_privilege('atest1','delete');
441 ---------------------
445 select has_table_privilege(t1.oid,'select')
446 from (select oid from pg_class where relname = 'atest1') as t1;
448 ---------------------
452 select has_table_privilege(t1.oid,'trigger')
453 from (select oid from pg_class where relname = 'atest1') as t1;
455 ---------------------
467 DROP GROUP regressgroup1;
468 DROP GROUP regressgroup2;
469 DROP USER regressuser1;
470 DROP USER regressuser2;
471 DROP USER regressuser3;
472 DROP USER regressuser4;