privileges.sql 10.3 KB
Newer Older
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
--
-- Test access privileges
--

CREATE USER regressuser1;
CREATE USER regressuser2;
CREATE USER regressuser3;
CREATE USER regressuser4;
CREATE USER regressuser4;	-- duplicate

CREATE GROUP regressgroup1;
CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;

ALTER GROUP regressgroup1 ADD USER regressuser4;

ALTER GROUP regressgroup2 ADD USER regressuser2;	-- duplicate
ALTER GROUP regressgroup2 DROP USER regressuser2;
ALTER GROUP regressgroup2 ADD USER regressuser4;


-- test owner privileges

SET SESSION AUTHORIZATION regressuser1;
SELECT session_user, current_user;

CREATE TABLE atest1 ( a int, b text );
SELECT * FROM atest1;
INSERT INTO atest1 VALUES (1, 'one');
DELETE FROM atest1;
UPDATE atest1 SET a = 1 WHERE b = 'blech';
LOCK atest1 IN ACCESS EXCLUSIVE MODE;

REVOKE ALL ON atest1 FROM PUBLIC;
SELECT * FROM atest1;

GRANT ALL ON atest1 TO regressuser2;
37
GRANT SELECT ON atest1 TO regressuser3, regressuser4;
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
SELECT * FROM atest1;

CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
GRANT SELECT ON atest2 TO regressuser2;
GRANT UPDATE ON atest2 TO regressuser3;
GRANT INSERT ON atest2 TO regressuser4;


SET SESSION AUTHORIZATION regressuser2;
SELECT session_user, current_user;

-- try various combinations of queries on atest1 and atest2

SELECT * FROM atest1; -- ok
SELECT * FROM atest2; -- ok
INSERT INTO atest1 VALUES (2, 'two'); -- ok
INSERT INTO atest2 VALUES ('foo', true); -- fail
INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
UPDATE atest2 SET col2 = NOT col2; -- fail
SELECT * FROM atest1 FOR UPDATE; -- ok
SELECT * FROM atest2 FOR UPDATE; -- fail
DELETE FROM atest2; -- fail
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
COPY atest2 FROM stdin; -- fail
GRANT ALL ON atest1 TO PUBLIC; -- fail

-- checks in subquery, both ok
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );


SET SESSION AUTHORIZATION regressuser3;
SELECT session_user, current_user;

SELECT * FROM atest1; -- ok
SELECT * FROM atest2; -- fail
INSERT INTO atest1 VALUES (2, 'two'); -- fail
INSERT INTO atest2 VALUES ('foo', true); -- fail
INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
UPDATE atest2 SET col2 = NULL; -- ok
UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
UPDATE atest2 SET col2 = true WHERE atest1.a = 5; -- ok
SELECT * FROM atest1 FOR UPDATE; -- fail
SELECT * FROM atest2 FOR UPDATE; -- fail
DELETE FROM atest2; -- fail
LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
COPY atest2 FROM stdin; -- fail

-- checks in subquery, both fail
SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );

SET SESSION AUTHORIZATION regressuser4;
COPY atest2 FROM stdin; -- ok
bar	true
\.
96
SELECT * FROM atest1; -- ok
97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120


-- groups

SET SESSION AUTHORIZATION regressuser3;
CREATE TABLE atest3 (one int, two int, three int);
GRANT DELETE ON atest3 TO GROUP regressgroup2;

SET SESSION AUTHORIZATION regressuser1;

SELECT * FROM atest3; -- fail
DELETE FROM atest3; -- ok


-- views

SET SESSION AUTHORIZATION regressuser3;

CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
/* The next *should* fail, but it's not implemented that way yet. */
CREATE VIEW atestv2 AS SELECT * FROM atest2;
CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok

SELECT * FROM atestv1; -- ok
121
SELECT * FROM atestv2; -- fail
122
GRANT SELECT ON atestv1, atestv3 TO regressuser4;
123
GRANT SELECT ON atestv2 TO regressuser2;
124 125 126 127

SET SESSION AUTHORIZATION regressuser4;

SELECT * FROM atestv1; -- ok
128
SELECT * FROM atestv2; -- fail
129 130
SELECT * FROM atestv3; -- ok

131 132 133 134 135 136 137 138 139 140 141 142 143 144
CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view
SELECT * FROM atestv4; -- ok
GRANT SELECT ON atestv4 TO regressuser2;

SET SESSION AUTHORIZATION regressuser2;

-- Two complex cases:

SELECT * FROM atestv3; -- fail
SELECT * FROM atestv4; -- ok (even though regressuser2 cannot access underlying atestv3)

SELECT * FROM atest2; -- ok
SELECT * FROM atestv2; -- fail (even though regressuser2 can access underlying atest2)

145

146 147 148 149 150 151 152 153 154 155 156 157 158
-- privileges on functions, languages

-- switch to superuser
\c -
REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
GRANT USAGE ON LANGUAGE sql TO regressuser1; -- ok
GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail

SET SESSION AUTHORIZATION regressuser1;
GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail
CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;

159
REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC;
160 161 162 163 164
GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regressuser2;
GRANT USAGE ON FUNCTION testfunc1(int) TO regressuser3; -- semantic error
GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regressuser4;
GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regressuser4;

165 166 167 168 169
CREATE FUNCTION testfunc4(boolean) RETURNS text
  AS 'select col1 from atest2 where col2 = $1;'
  LANGUAGE sql SECURITY DEFINER;
GRANT EXECUTE ON FUNCTION testfunc4(boolean) TO regressuser3;

170 171 172 173 174 175
SET SESSION AUTHORIZATION regressuser2;
SELECT testfunc1(5), testfunc2(5); -- ok
CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail

SET SESSION AUTHORIZATION regressuser3;
SELECT testfunc1(5); -- fail
176 177
SELECT col1 FROM atest2 WHERE col2 = true; -- fail
SELECT testfunc4(true); -- ok
178 179 180 181 182 183 184 185 186 187 188 189

SET SESSION AUTHORIZATION regressuser4;
SELECT testfunc1(5); -- ok

DROP FUNCTION testfunc1(int); -- fail

\c -
DROP FUNCTION testfunc1(int); -- ok
-- restore to sanity
GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;


190 191 192 193 194 195 196 197 198 199 200
-- has_table_privilege function

-- bad-input checks
select has_table_privilege(NULL,'pg_shadow','select');
select has_table_privilege('pg_shad','select');
select has_table_privilege('nosuchuser','pg_shadow','select');
select has_table_privilege('pg_shadow','sel');
select has_table_privilege(-999999,'pg_shadow','update');
select has_table_privilege(1,'rule');

-- superuser
201
\c -
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 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289
select has_table_privilege(current_user,'pg_shadow','select');
select has_table_privilege(current_user,'pg_shadow','insert');

select has_table_privilege(t2.usesysid,'pg_shadow','update')
from (select usesysid from pg_user where usename = current_user) as t2;
select has_table_privilege(t2.usesysid,'pg_shadow','delete')
from (select usesysid from pg_user where usename = current_user) as t2;

select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
select has_table_privilege(current_user,t1.oid,'references')
from (select oid from pg_class where relname = 'pg_shadow') as t1;

select has_table_privilege(t2.usesysid,t1.oid,'select')
from (select oid from pg_class where relname = 'pg_shadow') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;
select has_table_privilege(t2.usesysid,t1.oid,'insert')
from (select oid from pg_class where relname = 'pg_shadow') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;

select has_table_privilege('pg_shadow','update');
select has_table_privilege('pg_shadow','delete');

select has_table_privilege(t1.oid,'select')
from (select oid from pg_class where relname = 'pg_shadow') as t1;
select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'pg_shadow') as t1;

-- non-superuser
SET SESSION AUTHORIZATION regressuser3;

select has_table_privilege(current_user,'pg_class','select');
select has_table_privilege(current_user,'pg_class','insert');

select has_table_privilege(t2.usesysid,'pg_class','update')
from (select usesysid from pg_user where usename = current_user) as t2;
select has_table_privilege(t2.usesysid,'pg_class','delete')
from (select usesysid from pg_user where usename = current_user) as t2;

select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'pg_class') as t1;
select has_table_privilege(current_user,t1.oid,'references')
from (select oid from pg_class where relname = 'pg_class') as t1;

select has_table_privilege(t2.usesysid,t1.oid,'select')
from (select oid from pg_class where relname = 'pg_class') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;
select has_table_privilege(t2.usesysid,t1.oid,'insert')
from (select oid from pg_class where relname = 'pg_class') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;

select has_table_privilege('pg_class','update');
select has_table_privilege('pg_class','delete');

select has_table_privilege(t1.oid,'select')
from (select oid from pg_class where relname = 'pg_class') as t1;
select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'pg_class') as t1;

select has_table_privilege(current_user,'atest1','select');
select has_table_privilege(current_user,'atest1','insert');

select has_table_privilege(t2.usesysid,'atest1','update')
from (select usesysid from pg_user where usename = current_user) as t2;
select has_table_privilege(t2.usesysid,'atest1','delete')
from (select usesysid from pg_user where usename = current_user) as t2;

select has_table_privilege(current_user,t1.oid,'rule')
from (select oid from pg_class where relname = 'atest1') as t1;
select has_table_privilege(current_user,t1.oid,'references')
from (select oid from pg_class where relname = 'atest1') as t1;

select has_table_privilege(t2.usesysid,t1.oid,'select')
from (select oid from pg_class where relname = 'atest1') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;
select has_table_privilege(t2.usesysid,t1.oid,'insert')
from (select oid from pg_class where relname = 'atest1') as t1,
  (select usesysid from pg_user where usename = current_user) as t2;

select has_table_privilege('atest1','update');
select has_table_privilege('atest1','delete');

select has_table_privilege(t1.oid,'select')
from (select oid from pg_class where relname = 'atest1') as t1;
select has_table_privilege(t1.oid,'trigger')
from (select oid from pg_class where relname = 'atest1') as t1;


290 291 292
-- clean up

\c regression
293 294 295
DROP FUNCTION testfunc2(int);
DROP FUNCTION testfunc4(boolean);

296 297
DROP VIEW atestv1;
DROP VIEW atestv2;
298 299 300
-- this should cascade to drop atestv4
DROP VIEW atestv3 CASCADE;
-- this should complain "does not exist"
301
DROP VIEW atestv4;
302

303 304 305 306
DROP TABLE atest1;
DROP TABLE atest2;
DROP TABLE atest3;

307 308 309 310 311 312 313
DROP GROUP regressgroup1;
DROP GROUP regressgroup2;

DROP USER regressuser1;
DROP USER regressuser2;
DROP USER regressuser3;
DROP USER regressuser4;