src/test/regress/sql/privileges.sql

   1 --
   2 -- Test access privileges
   3 --
   4
   5 -- Clean up in case a prior regression run failed
   6
   7 -- Suppress NOTICE messages when users/groups don't exist
   8 SET client_min_messages TO 'error';
   9
  10 DROP ROLE IF EXISTS regressgroup1;
  11 DROP ROLE IF EXISTS regressgroup2;
  12
  13 DROP ROLE IF EXISTS regressuser1;
  14 DROP ROLE IF EXISTS regressuser2;
  15 DROP ROLE IF EXISTS regressuser3;
  16 DROP ROLE IF EXISTS regressuser4;
  17 DROP ROLE IF EXISTS regressuser5;
  18
  19 RESET client_min_messages;
  20
  21 -- test proper begins here
  22
  23 CREATE USER regressuser1;
  24 CREATE USER regressuser2;
  25 CREATE USER regressuser3;
  26 CREATE USER regressuser4;
  27 CREATE USER regressuser5;
  28 CREATE USER regressuser5;       -- duplicate
  29
  30 CREATE GROUP regressgroup1;
  31 CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;
  32
  33 ALTER GROUP regressgroup1 ADD USER regressuser4;
  34
  35 ALTER GROUP regressgroup2 ADD USER regressuser2;        -- duplicate
  36 ALTER GROUP regressgroup2 DROP USER regressuser2;
  37 ALTER GROUP regressgroup2 ADD USER regressuser4;
  38
  39
  40 -- test owner privileges
  41
  42 SET SESSION AUTHORIZATION regressuser1;
  43 SELECT session_user, current_user;
  44
  45 CREATE TABLE atest1 ( a int, b text );
  46 SELECT * FROM atest1;
  47 INSERT INTO atest1 VALUES (1, 'one');
  48 DELETE FROM atest1;
  49 UPDATE atest1 SET a = 1 WHERE b = 'blech';
  50 TRUNCATE atest1;
  51 LOCK atest1 IN ACCESS EXCLUSIVE MODE;
  52
  53 REVOKE ALL ON atest1 FROM PUBLIC;
  54 SELECT * FROM atest1;
  55
  56 GRANT ALL ON atest1 TO regressuser2;
  57 GRANT SELECT ON atest1 TO regressuser3, regressuser4;
  58 SELECT * FROM atest1;
  59
  60 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
  61 GRANT SELECT ON atest2 TO regressuser2;
  62 GRANT UPDATE ON atest2 TO regressuser3;
  63 GRANT INSERT ON atest2 TO regressuser4;
  64 GRANT TRUNCATE ON atest2 TO regressuser5;
  65
  66
  67 SET SESSION AUTHORIZATION regressuser2;
  68 SELECT session_user, current_user;
  69
  70 -- try various combinations of queries on atest1 and atest2
  71
  72 SELECT * FROM atest1; -- ok
  73 SELECT * FROM atest2; -- ok
  74 INSERT INTO atest1 VALUES (2, 'two'); -- ok
  75 INSERT INTO atest2 VALUES ('foo', true); -- fail
  76 INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
  77 UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
  78 UPDATE atest2 SET col2 = NOT col2; -- fail
  79 SELECT * FROM atest1 FOR UPDATE; -- ok
  80 SELECT * FROM atest2 FOR UPDATE; -- fail
  81 DELETE FROM atest2; -- fail
  82 TRUNCATE atest2; -- fail
  83 LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
  84 COPY atest2 FROM stdin; -- fail
  85 GRANT ALL ON atest1 TO PUBLIC; -- fail
  86
  87 -- checks in subquery, both ok
  88 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
  89 SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
  90
  91
  92 SET SESSION AUTHORIZATION regressuser3;
  93 SELECT session_user, current_user;
  94
  95 SELECT * FROM atest1; -- ok
  96 SELECT * FROM atest2; -- fail
  97 INSERT INTO atest1 VALUES (2, 'two'); -- fail
  98 INSERT INTO atest2 VALUES ('foo', true); -- fail
  99 INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
 100 UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
 101 UPDATE atest2 SET col2 = NULL; -- ok
 102 UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
 103 UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok
 104 SELECT * FROM atest1 FOR UPDATE; -- fail
 105 SELECT * FROM atest2 FOR UPDATE; -- fail
 106 DELETE FROM atest2; -- fail
 107 TRUNCATE atest2; -- fail
 108 LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
 109 COPY atest2 FROM stdin; -- fail
 110
 111 -- checks in subquery, both fail
 112 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
 113 SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
 114
 115 SET SESSION AUTHORIZATION regressuser4;
 116 COPY atest2 FROM stdin; -- ok
 117 bar     true
 118 \.
 119 SELECT * FROM atest1; -- ok
 120
 121
 122 -- groups
 123
 124 SET SESSION AUTHORIZATION regressuser3;
 125 CREATE TABLE atest3 (one int, two int, three int);
 126 GRANT DELETE ON atest3 TO GROUP regressgroup2;
 127
 128 SET SESSION AUTHORIZATION regressuser1;
 129
 130 SELECT * FROM atest3; -- fail
 131 DELETE FROM atest3; -- ok
 132
 133
 134 -- views
 135
 136 SET SESSION AUTHORIZATION regressuser3;
 137
 138 CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
 139 /* The next *should* fail, but it's not implemented that way yet. */
 140 CREATE VIEW atestv2 AS SELECT * FROM atest2;
 141 CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
 142
 143 SELECT * FROM atestv1; -- ok
 144 SELECT * FROM atestv2; -- fail
 145 GRANT SELECT ON atestv1, atestv3 TO regressuser4;
 146 GRANT SELECT ON atestv2 TO regressuser2;
 147
 148 SET SESSION AUTHORIZATION regressuser4;
 149
 150 SELECT * FROM atestv1; -- ok
 151 SELECT * FROM atestv2; -- fail
 152 SELECT * FROM atestv3; -- ok
 153
 154 CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view
 155 SELECT * FROM atestv4; -- ok
 156 GRANT SELECT ON atestv4 TO regressuser2;
 157
 158 SET SESSION AUTHORIZATION regressuser2;
 159
 160 -- Two complex cases:
 161
 162 SELECT * FROM atestv3; -- fail
 163 SELECT * FROM atestv4; -- ok (even though regressuser2 cannot access underlying atestv3)
 164
 165 SELECT * FROM atest2; -- ok
 166 SELECT * FROM atestv2; -- fail (even though regressuser2 can access underlying atest2)
 167
 168
 169 -- privileges on functions, languages
 170
 171 -- switch to superuser
 172 \c -
 173
 174 REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
 175 GRANT USAGE ON LANGUAGE sql TO regressuser1; -- ok
 176 GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
 177
 178 SET SESSION AUTHORIZATION regressuser1;
 179 GRANT USAGE ON LANGUAGE sql TO regressuser2; -- fail
 180 CREATE FUNCTION testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
 181 CREATE FUNCTION testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
 182
 183 REVOKE ALL ON FUNCTION testfunc1(int), testfunc2(int) FROM PUBLIC;
 184 GRANT EXECUTE ON FUNCTION testfunc1(int), testfunc2(int) TO regressuser2;
 185 GRANT USAGE ON FUNCTION testfunc1(int) TO regressuser3; -- semantic error
 186 GRANT ALL PRIVILEGES ON FUNCTION testfunc1(int) TO regressuser4;
 187 GRANT ALL PRIVILEGES ON FUNCTION testfunc_nosuch(int) TO regressuser4;
 188
 189 CREATE FUNCTION testfunc4(boolean) RETURNS text
 190   AS 'select col1 from atest2 where col2 = $1;'
 191   LANGUAGE sql SECURITY DEFINER;
 192 GRANT EXECUTE ON FUNCTION testfunc4(boolean) TO regressuser3;
 193
 194 SET SESSION AUTHORIZATION regressuser2;
 195 SELECT testfunc1(5), testfunc2(5); -- ok
 196 CREATE FUNCTION testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
 197
 198 SET SESSION AUTHORIZATION regressuser3;
 199 SELECT testfunc1(5); -- fail
 200 SELECT col1 FROM atest2 WHERE col2 = true; -- fail
 201 SELECT testfunc4(true); -- ok
 202
 203 SET SESSION AUTHORIZATION regressuser4;
 204 SELECT testfunc1(5); -- ok
 205
 206 DROP FUNCTION testfunc1(int); -- fail
 207
 208 \c -
 209
 210 DROP FUNCTION testfunc1(int); -- ok
 211 -- restore to sanity
 212 GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
 213
 214 -- truncate
 215 SET SESSION AUTHORIZATION regressuser5;
 216 TRUNCATE atest2; -- ok
 217 TRUNCATE atest3; -- fail
 218
 219 -- has_table_privilege function
 220
 221 -- bad-input checks
 222 select has_table_privilege(NULL,'pg_authid','select');
 223 select has_table_privilege('pg_shad','select');
 224 select has_table_privilege('nosuchuser','pg_authid','select');
 225 select has_table_privilege('pg_authid','sel');
 226 select has_table_privilege(-999999,'pg_authid','update');
 227 select has_table_privilege(1,'select');
 228
 229 -- superuser
 230 \c -
 231
 232 select has_table_privilege(current_user,'pg_authid','select');
 233 select has_table_privilege(current_user,'pg_authid','insert');
 234
 235 select has_table_privilege(t2.oid,'pg_authid','update')
 236 from (select oid from pg_roles where rolname = current_user) as t2;
 237 select has_table_privilege(t2.oid,'pg_authid','delete')
 238 from (select oid from pg_roles where rolname = current_user) as t2;
 239
 240 -- 'rule' privilege no longer exists, but for backwards compatibility
 241 -- has_table_privilege still recognizes the keyword and says FALSE
 242 select has_table_privilege(current_user,t1.oid,'rule')
 243 from (select oid from pg_class where relname = 'pg_authid') as t1;
 244 select has_table_privilege(current_user,t1.oid,'references')
 245 from (select oid from pg_class where relname = 'pg_authid') as t1;
 246
 247 select has_table_privilege(t2.oid,t1.oid,'select')
 248 from (select oid from pg_class where relname = 'pg_authid') as t1,
 249   (select oid from pg_roles where rolname = current_user) as t2;
 250 select has_table_privilege(t2.oid,t1.oid,'insert')
 251 from (select oid from pg_class where relname = 'pg_authid') as t1,
 252   (select oid from pg_roles where rolname = current_user) as t2;
 253
 254 select has_table_privilege('pg_authid','update');
 255 select has_table_privilege('pg_authid','delete');
 256 select has_table_privilege('pg_authid','truncate');
 257
 258 select has_table_privilege(t1.oid,'select')
 259 from (select oid from pg_class where relname = 'pg_authid') as t1;
 260 select has_table_privilege(t1.oid,'trigger')
 261 from (select oid from pg_class where relname = 'pg_authid') as t1;
 262
 263 -- non-superuser
 264 SET SESSION AUTHORIZATION regressuser3;
 265
 266 select has_table_privilege(current_user,'pg_class','select');
 267 select has_table_privilege(current_user,'pg_class','insert');
 268
 269 select has_table_privilege(t2.oid,'pg_class','update')
 270 from (select oid from pg_roles where rolname = current_user) as t2;
 271 select has_table_privilege(t2.oid,'pg_class','delete')
 272 from (select oid from pg_roles where rolname = current_user) as t2;
 273
 274 select has_table_privilege(current_user,t1.oid,'references')
 275 from (select oid from pg_class where relname = 'pg_class') as t1;
 276
 277 select has_table_privilege(t2.oid,t1.oid,'select')
 278 from (select oid from pg_class where relname = 'pg_class') as t1,
 279   (select oid from pg_roles where rolname = current_user) as t2;
 280 select has_table_privilege(t2.oid,t1.oid,'insert')
 281 from (select oid from pg_class where relname = 'pg_class') as t1,
 282   (select oid from pg_roles where rolname = current_user) as t2;
 283
 284 select has_table_privilege('pg_class','update');
 285 select has_table_privilege('pg_class','delete');
 286 select has_table_privilege('pg_class','truncate');
 287
 288 select has_table_privilege(t1.oid,'select')
 289 from (select oid from pg_class where relname = 'pg_class') as t1;
 290 select has_table_privilege(t1.oid,'trigger')
 291 from (select oid from pg_class where relname = 'pg_class') as t1;
 292
 293 select has_table_privilege(current_user,'atest1','select');
 294 select has_table_privilege(current_user,'atest1','insert');
 295
 296 select has_table_privilege(t2.oid,'atest1','update')
 297 from (select oid from pg_roles where rolname = current_user) as t2;
 298 select has_table_privilege(t2.oid,'atest1','delete')
 299 from (select oid from pg_roles where rolname = current_user) as t2;
 300
 301 select has_table_privilege(current_user,t1.oid,'references')
 302 from (select oid from pg_class where relname = 'atest1') as t1;
 303
 304 select has_table_privilege(t2.oid,t1.oid,'select')
 305 from (select oid from pg_class where relname = 'atest1') as t1,
 306   (select oid from pg_roles where rolname = current_user) as t2;
 307 select has_table_privilege(t2.oid,t1.oid,'insert')
 308 from (select oid from pg_class where relname = 'atest1') as t1,
 309   (select oid from pg_roles where rolname = current_user) as t2;
 310
 311 select has_table_privilege('atest1','update');
 312 select has_table_privilege('atest1','delete');
 313 select has_table_privilege('atest1','truncate');
 314
 315 select has_table_privilege(t1.oid,'select')
 316 from (select oid from pg_class where relname = 'atest1') as t1;
 317 select has_table_privilege(t1.oid,'trigger')
 318 from (select oid from pg_class where relname = 'atest1') as t1;
 319
 320
 321 -- Grant options
 322
 323 SET SESSION AUTHORIZATION regressuser1;
 324
 325 CREATE TABLE atest4 (a int);
 326
 327 GRANT SELECT ON atest4 TO regressuser2 WITH GRANT OPTION;
 328 GRANT UPDATE ON atest4 TO regressuser2;
 329 GRANT SELECT ON atest4 TO GROUP regressgroup1 WITH GRANT OPTION;
 330
 331 SET SESSION AUTHORIZATION regressuser2;
 332
 333 GRANT SELECT ON atest4 TO regressuser3;
 334 GRANT UPDATE ON atest4 TO regressuser3; -- fail
 335
 336 SET SESSION AUTHORIZATION regressuser1;
 337
 338 REVOKE SELECT ON atest4 FROM regressuser3; -- does nothing
 339 SELECT has_table_privilege('regressuser3', 'atest4', 'SELECT'); -- true
 340 REVOKE SELECT ON atest4 FROM regressuser2; -- fail
 341 REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regressuser2 CASCADE; -- ok
 342 SELECT has_table_privilege('regressuser2', 'atest4', 'SELECT'); -- true
 343 SELECT has_table_privilege('regressuser3', 'atest4', 'SELECT'); -- false
 344
 345 SELECT has_table_privilege('regressuser1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
 346
 347
 348 -- clean up
 349
 350 \c
 351
 352 DROP FUNCTION testfunc2(int);
 353 DROP FUNCTION testfunc4(boolean);
 354
 355 DROP VIEW atestv1;
 356 DROP VIEW atestv2;
 357 -- this should cascade to drop atestv4
 358 DROP VIEW atestv3 CASCADE;
 359 -- this should complain "does not exist"
 360 DROP VIEW atestv4;
 361
 362 DROP TABLE atest1;
 363 DROP TABLE atest2;
 364 DROP TABLE atest3;
 365 DROP TABLE atest4;
 366
 367 DROP GROUP regressgroup1;
 368 DROP GROUP regressgroup2;
 369
 370 REVOKE USAGE ON LANGUAGE sql FROM regressuser1;
 371 DROP USER regressuser1;
 372 DROP USER regressuser2;
 373 DROP USER regressuser3;
 374 DROP USER regressuser4;
 375 DROP USER regressuser5;