granicus.if.org Git - postgresql/blob - src/test/regress/sql/privileges.sql

   1 --
   2 -- Test access privileges
   3 --
   4
   5 CREATE USER regressuser1;
   6 CREATE USER regressuser2;
   7 CREATE USER regressuser3;
   8 CREATE USER regressuser4;
   9 CREATE USER regressuser4;       -- duplicate
  10
  11 CREATE GROUP regressgroup1;
  12 CREATE GROUP regressgroup2 WITH USER regressuser1, regressuser2;
  13
  14 ALTER GROUP regressgroup1 ADD USER regressuser4;
  15
  16 ALTER GROUP regressgroup2 ADD USER regressuser2;        -- duplicate
  17 ALTER GROUP regressgroup2 DROP USER regressuser2;
  18 ALTER GROUP regressgroup2 ADD USER regressuser4;
  19
  20
  21 -- test owner privileges
  22
  23 SET SESSION AUTHORIZATION regressuser1;
  24 SELECT session_user, current_user;
  25
  26 CREATE TABLE atest1 ( a int, b text );
  27 SELECT * FROM atest1;
  28 INSERT INTO atest1 VALUES (1, 'one');
  29 DELETE FROM atest1;
  30 UPDATE atest1 SET a = 1 WHERE b = 'blech';
  31 LOCK atest1 IN ACCESS EXCLUSIVE MODE;
  32
  33 REVOKE ALL ON atest1 FROM PUBLIC;
  34 SELECT * FROM atest1;
  35
  36 GRANT ALL ON atest1 TO regressuser2;
  37 GRANT SELECT ON atest1 TO regressuser3, regressuser4;
  38 SELECT * FROM atest1;
  39
  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;
  44
  45
  46 SET SESSION AUTHORIZATION regressuser2;
  47 SELECT session_user, current_user;
  48
  49 -- try various combinations of queries on atest1 and atest2
  50
  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
  64
  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 ) );
  68
  69
  70 SET SESSION AUTHORIZATION regressuser3;
  71 SELECT session_user, current_user;
  72
  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
  87
  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 ) );
  91
  92 SET SESSION AUTHORIZATION regressuser4;
  93 COPY atest2 FROM stdin; -- ok
  94 bar     true
  95 \.
  96 SELECT * FROM atest1; -- ok
  97
  98
  99 -- groups
 100
 101 SET SESSION AUTHORIZATION regressuser3;
 102 CREATE TABLE atest3 (one int, two int, three int);
 103 GRANT DELETE ON atest3 TO GROUP regressgroup2;
 104
 105 SET SESSION AUTHORIZATION regressuser1;
 106
 107 SELECT * FROM atest3; -- fail
 108 DELETE FROM atest3; -- ok
 109
 110
 111 -- views
 112
 113 SET SESSION AUTHORIZATION regressuser3;
 114
 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
 119
 120 SELECT * FROM atestv1; -- ok
 121 GRANT SELECT ON atestv1, atestv3 TO regressuser4;
 122
 123 SET SESSION AUTHORIZATION regressuser4;
 124
 125 SELECT * FROM atestv1; -- ok
 126 SELECT * FROM atestv3; -- ok
 127
 128
 129 -- has_table_privilege function
 130
 131 -- bad-input checks
 132 select has_table_privilege(NULL,'pg_shadow','select');
 133 select has_table_privilege('pg_shad','select');
 134 select has_table_privilege('nosuchuser','pg_shadow','select');
 135 select has_table_privilege('pg_shadow','sel');
 136 select has_table_privilege(-999999,'pg_shadow','update');
 137 select has_table_privilege(1,'rule');
 138
 139 -- superuser
 140 \c regression
 141 select has_table_privilege(current_user,'pg_shadow','select');
 142 select has_table_privilege(current_user,'pg_shadow','insert');
 143
 144 select has_table_privilege(t2.usesysid,'pg_shadow','update')
 145 from (select usesysid from pg_user where usename = current_user) as t2;
 146 select has_table_privilege(t2.usesysid,'pg_shadow','delete')
 147 from (select usesysid from pg_user where usename = current_user) as t2;
 148
 149 select has_table_privilege(current_user,t1.oid,'rule')
 150 from (select oid from pg_class where relname = 'pg_shadow') as t1;
 151 select has_table_privilege(current_user,t1.oid,'references')
 152 from (select oid from pg_class where relname = 'pg_shadow') as t1;
 153
 154 select has_table_privilege(t2.usesysid,t1.oid,'select')
 155 from (select oid from pg_class where relname = 'pg_shadow') as t1,
 156   (select usesysid from pg_user where usename = current_user) as t2;
 157 select has_table_privilege(t2.usesysid,t1.oid,'insert')
 158 from (select oid from pg_class where relname = 'pg_shadow') as t1,
 159   (select usesysid from pg_user where usename = current_user) as t2;
 160
 161 select has_table_privilege('pg_shadow','update');
 162 select has_table_privilege('pg_shadow','delete');
 163
 164 select has_table_privilege(t1.oid,'select')
 165 from (select oid from pg_class where relname = 'pg_shadow') as t1;
 166 select has_table_privilege(t1.oid,'trigger')
 167 from (select oid from pg_class where relname = 'pg_shadow') as t1;
 168
 169 -- non-superuser
 170 SET SESSION AUTHORIZATION regressuser3;
 171
 172 select has_table_privilege(current_user,'pg_class','select');
 173 select has_table_privilege(current_user,'pg_class','insert');
 174
 175 select has_table_privilege(t2.usesysid,'pg_class','update')
 176 from (select usesysid from pg_user where usename = current_user) as t2;
 177 select has_table_privilege(t2.usesysid,'pg_class','delete')
 178 from (select usesysid from pg_user where usename = current_user) as t2;
 179
 180 select has_table_privilege(current_user,t1.oid,'rule')
 181 from (select oid from pg_class where relname = 'pg_class') as t1;
 182 select has_table_privilege(current_user,t1.oid,'references')
 183 from (select oid from pg_class where relname = 'pg_class') as t1;
 184
 185 select has_table_privilege(t2.usesysid,t1.oid,'select')
 186 from (select oid from pg_class where relname = 'pg_class') as t1,
 187   (select usesysid from pg_user where usename = current_user) as t2;
 188 select has_table_privilege(t2.usesysid,t1.oid,'insert')
 189 from (select oid from pg_class where relname = 'pg_class') as t1,
 190   (select usesysid from pg_user where usename = current_user) as t2;
 191
 192 select has_table_privilege('pg_class','update');
 193 select has_table_privilege('pg_class','delete');
 194
 195 select has_table_privilege(t1.oid,'select')
 196 from (select oid from pg_class where relname = 'pg_class') as t1;
 197 select has_table_privilege(t1.oid,'trigger')
 198 from (select oid from pg_class where relname = 'pg_class') as t1;
 199
 200 select has_table_privilege(current_user,'atest1','select');
 201 select has_table_privilege(current_user,'atest1','insert');
 202
 203 select has_table_privilege(t2.usesysid,'atest1','update')
 204 from (select usesysid from pg_user where usename = current_user) as t2;
 205 select has_table_privilege(t2.usesysid,'atest1','delete')
 206 from (select usesysid from pg_user where usename = current_user) as t2;
 207
 208 select has_table_privilege(current_user,t1.oid,'rule')
 209 from (select oid from pg_class where relname = 'atest1') as t1;
 210 select has_table_privilege(current_user,t1.oid,'references')
 211 from (select oid from pg_class where relname = 'atest1') as t1;
 212
 213 select has_table_privilege(t2.usesysid,t1.oid,'select')
 214 from (select oid from pg_class where relname = 'atest1') as t1,
 215   (select usesysid from pg_user where usename = current_user) as t2;
 216 select has_table_privilege(t2.usesysid,t1.oid,'insert')
 217 from (select oid from pg_class where relname = 'atest1') as t1,
 218   (select usesysid from pg_user where usename = current_user) as t2;
 219
 220 select has_table_privilege('atest1','update');
 221 select has_table_privilege('atest1','delete');
 222
 223 select has_table_privilege(t1.oid,'select')
 224 from (select oid from pg_class where relname = 'atest1') as t1;
 225 select has_table_privilege(t1.oid,'trigger')
 226 from (select oid from pg_class where relname = 'atest1') as t1;
 227
 228
 229 -- clean up
 230
 231 \c regression
 232 DROP TABLE atest1;
 233 DROP TABLE atest2;
 234 DROP TABLE atest3;
 235
 236 DROP VIEW atestv1;
 237 DROP VIEW atestv2;
 238 DROP VIEW atestv3;
 239
 240 DROP GROUP regressgroup1;
 241 DROP GROUP regressgroup2;
 242
 243 DROP USER regressuser1;
 244 DROP USER regressuser2;
 245 DROP USER regressuser3;
 246 DROP USER regressuser4;