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 GRANT SELECT ON atestv1, atestv3 TO regressuser4;
123 SET SESSION AUTHORIZATION regressuser4;
125 SELECT * FROM atestv1; -- ok
126 SELECT * FROM atestv3; -- ok
129 -- privileges on functions, languages
131 -- switch to superuser
133 REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
134 GRANT USAGE ON LANGUAGE sql TO regressuser1; -- ok
135 GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
137 SET SESSION AUTHORIZATION regressuser1;
138 GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail
139 CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
140 CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
142 GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regressuser2;
143 GRANT USAGE ON FUNCTION testfunc1(int) TO regressuser3; -- semantic error
144 GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regressuser4;
145 GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regressuser4;
147 SET SESSION AUTHORIZATION regressuser2;
148 SELECT testfunc1(5), testfunc2(5); -- ok
149 CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
151 SET SESSION AUTHORIZATION regressuser3;
152 SELECT testfunc1(5); -- fail
154 SET SESSION AUTHORIZATION regressuser4;
155 SELECT testfunc1(5); -- ok
157 DROP FUNCTION testfunc1(int); -- fail
160 DROP FUNCTION testfunc1(int); -- ok
162 GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
165 -- has_table_privilege function
168 select has_table_privilege(NULL,'pg_shadow','select');
169 select has_table_privilege('pg_shad','select');
170 select has_table_privilege('nosuchuser','pg_shadow','select');
171 select has_table_privilege('pg_shadow','sel');
172 select has_table_privilege(-999999,'pg_shadow','update');
173 select has_table_privilege(1,'rule');
177 select has_table_privilege(current_user,'pg_shadow','select');
178 select has_table_privilege(current_user,'pg_shadow','insert');
180 select has_table_privilege(t2.usesysid,'pg_shadow','update')
181 from (select usesysid from pg_user where usename = current_user) as t2;
182 select has_table_privilege(t2.usesysid,'pg_shadow','delete')
183 from (select usesysid from pg_user where usename = current_user) as t2;
185 select has_table_privilege(current_user,t1.oid,'rule')
186 from (select oid from pg_class where relname = 'pg_shadow') as t1;
187 select has_table_privilege(current_user,t1.oid,'references')
188 from (select oid from pg_class where relname = 'pg_shadow') as t1;
190 select has_table_privilege(t2.usesysid,t1.oid,'select')
191 from (select oid from pg_class where relname = 'pg_shadow') as t1,
192 (select usesysid from pg_user where usename = current_user) as t2;
193 select has_table_privilege(t2.usesysid,t1.oid,'insert')
194 from (select oid from pg_class where relname = 'pg_shadow') as t1,
195 (select usesysid from pg_user where usename = current_user) as t2;
197 select has_table_privilege('pg_shadow','update');
198 select has_table_privilege('pg_shadow','delete');
200 select has_table_privilege(t1.oid,'select')
201 from (select oid from pg_class where relname = 'pg_shadow') as t1;
202 select has_table_privilege(t1.oid,'trigger')
203 from (select oid from pg_class where relname = 'pg_shadow') as t1;
206 SET SESSION AUTHORIZATION regressuser3;
208 select has_table_privilege(current_user,'pg_class','select');
209 select has_table_privilege(current_user,'pg_class','insert');
211 select has_table_privilege(t2.usesysid,'pg_class','update')
212 from (select usesysid from pg_user where usename = current_user) as t2;
213 select has_table_privilege(t2.usesysid,'pg_class','delete')
214 from (select usesysid from pg_user where usename = current_user) as t2;
216 select has_table_privilege(current_user,t1.oid,'rule')
217 from (select oid from pg_class where relname = 'pg_class') as t1;
218 select has_table_privilege(current_user,t1.oid,'references')
219 from (select oid from pg_class where relname = 'pg_class') as t1;
221 select has_table_privilege(t2.usesysid,t1.oid,'select')
222 from (select oid from pg_class where relname = 'pg_class') as t1,
223 (select usesysid from pg_user where usename = current_user) as t2;
224 select has_table_privilege(t2.usesysid,t1.oid,'insert')
225 from (select oid from pg_class where relname = 'pg_class') as t1,
226 (select usesysid from pg_user where usename = current_user) as t2;
228 select has_table_privilege('pg_class','update');
229 select has_table_privilege('pg_class','delete');
231 select has_table_privilege(t1.oid,'select')
232 from (select oid from pg_class where relname = 'pg_class') as t1;
233 select has_table_privilege(t1.oid,'trigger')
234 from (select oid from pg_class where relname = 'pg_class') as t1;
236 select has_table_privilege(current_user,'atest1','select');
237 select has_table_privilege(current_user,'atest1','insert');
239 select has_table_privilege(t2.usesysid,'atest1','update')
240 from (select usesysid from pg_user where usename = current_user) as t2;
241 select has_table_privilege(t2.usesysid,'atest1','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 = 'atest1') as t1;
246 select has_table_privilege(current_user,t1.oid,'references')
247 from (select oid from pg_class where relname = 'atest1') as t1;
249 select has_table_privilege(t2.usesysid,t1.oid,'select')
250 from (select oid from pg_class where relname = 'atest1') 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 = 'atest1') as t1,
254 (select usesysid from pg_user where usename = current_user) as t2;
256 select has_table_privilege('atest1','update');
257 select has_table_privilege('atest1','delete');
259 select has_table_privilege(t1.oid,'select')
260 from (select oid from pg_class where relname = 'atest1') as t1;
261 select has_table_privilege(t1.oid,'trigger')
262 from (select oid from pg_class where relname = 'atest1') as t1;
276 DROP GROUP regressgroup1;
277 DROP GROUP regressgroup2;
279 DROP USER regressuser1;
280 DROP USER regressuser2;
281 DROP USER regressuser3;
282 DROP USER regressuser4;