| blob | 75912f690c28704203c26f89f0408f0a121ee749 |
1 --
2 -- Test access privileges
3 --
4 -- Clean up in case a prior regression run failed
5 -- Suppress NOTICE messages when users/groups don't exist
6 SET client_min_messages TO 'warning';
7 DROP ROLE IF EXISTS regress_priv_group1;
8 DROP ROLE IF EXISTS regress_priv_group2;
9 DROP ROLE IF EXISTS regress_priv_user1;
10 DROP ROLE IF EXISTS regress_priv_user2;
11 DROP ROLE IF EXISTS regress_priv_user3;
12 DROP ROLE IF EXISTS regress_priv_user4;
13 DROP ROLE IF EXISTS regress_priv_user5;
14 DROP ROLE IF EXISTS regress_priv_user6;
15 DROP ROLE IF EXISTS regress_priv_user7;
16 SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
17 lo_unlink
18 -----------
19 (0 rows)
21 RESET client_min_messages;
22 -- test proper begins here
23 CREATE USER regress_priv_user1;
24 CREATE USER regress_priv_user2;
25 CREATE USER regress_priv_user3;
26 CREATE USER regress_priv_user4;
27 CREATE USER regress_priv_user5;
28 CREATE USER regress_priv_user5; -- duplicate
29 ERROR: role "regress_priv_user5" already exists
30 CREATE USER regress_priv_user6;
31 CREATE USER regress_priv_user7;
32 CREATE USER regress_priv_user8;
33 CREATE USER regress_priv_user9;
34 CREATE USER regress_priv_user10;
35 CREATE ROLE regress_priv_role;
36 -- circular ADMIN OPTION grants should be disallowed
37 GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION;
38 GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION GRANTED BY regress_priv_user2;
39 GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION GRANTED BY regress_priv_user3;
40 ERROR: ADMIN option cannot be granted back to your own grantor
41 -- need CASCADE to revoke grant or admin option if dependent grants exist
42 REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2; -- fail
43 ERROR: dependent privileges exist
44 HINT: Use CASCADE to revoke them too.
45 REVOKE regress_priv_user1 FROM regress_priv_user2; -- fail
46 ERROR: dependent privileges exist
47 HINT: Use CASCADE to revoke them too.
48 SELECT member::regrole, admin_option FROM pg_auth_members WHERE roleid = 'regress_priv_user1'::regrole;
49 member | admin_option
50 --------------------+--------------
51 regress_priv_user2 | t
52 regress_priv_user3 | t
53 (2 rows)
55 BEGIN;
56 REVOKE ADMIN OPTION FOR regress_priv_user1 FROM regress_priv_user2 CASCADE;
57 SELECT member::regrole, admin_option FROM pg_auth_members WHERE roleid = 'regress_priv_user1'::regrole;
58 member | admin_option
59 --------------------+--------------
60 regress_priv_user2 | f
61 (1 row)
63 ROLLBACK;
64 REVOKE regress_priv_user1 FROM regress_priv_user2 CASCADE;
65 SELECT member::regrole, admin_option FROM pg_auth_members WHERE roleid = 'regress_priv_user1'::regrole;
66 member | admin_option
67 --------+--------------
68 (0 rows)
70 -- inferred grantor must be a role with ADMIN OPTION
71 GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION;
72 GRANT regress_priv_user2 TO regress_priv_user3;
73 SET ROLE regress_priv_user3;
74 GRANT regress_priv_user1 TO regress_priv_user4;
75 SELECT grantor::regrole FROM pg_auth_members WHERE roleid = 'regress_priv_user1'::regrole and member = 'regress_priv_user4'::regrole;
76 grantor
77 --------------------
78 regress_priv_user2
79 (1 row)
81 RESET ROLE;
82 REVOKE regress_priv_user2 FROM regress_priv_user3;
83 REVOKE regress_priv_user1 FROM regress_priv_user2 CASCADE;
84 -- test GRANTED BY with DROP OWNED and REASSIGN OWNED
85 GRANT regress_priv_user1 TO regress_priv_user2 WITH ADMIN OPTION;
86 GRANT regress_priv_user1 TO regress_priv_user3 GRANTED BY regress_priv_user2;
87 DROP ROLE regress_priv_user2; -- fail, dependency
88 ERROR: role "regress_priv_user2" cannot be dropped because some objects depend on it
89 DETAIL: privileges for membership of role regress_priv_user3 in role regress_priv_user1
90 REASSIGN OWNED BY regress_priv_user2 TO regress_priv_user4;
91 DROP ROLE regress_priv_user2; -- still fail, REASSIGN OWNED doesn't help
92 ERROR: role "regress_priv_user2" cannot be dropped because some objects depend on it
93 DETAIL: privileges for membership of role regress_priv_user3 in role regress_priv_user1
94 DROP OWNED BY regress_priv_user2;
95 DROP ROLE regress_priv_user2; -- ok now, DROP OWNED does the job
96 -- test that removing granted role or grantee role removes dependency
97 GRANT regress_priv_user1 TO regress_priv_user3 WITH ADMIN OPTION;
98 GRANT regress_priv_user1 TO regress_priv_user4 GRANTED BY regress_priv_user3;
99 DROP ROLE regress_priv_user3; -- should fail, dependency
100 ERROR: role "regress_priv_user3" cannot be dropped because some objects depend on it
101 DETAIL: privileges for membership of role regress_priv_user4 in role regress_priv_user1
102 DROP ROLE regress_priv_user4; -- ok
103 DROP ROLE regress_priv_user3; -- ok now
104 GRANT regress_priv_user1 TO regress_priv_user5 WITH ADMIN OPTION;
105 GRANT regress_priv_user1 TO regress_priv_user6 GRANTED BY regress_priv_user5;
106 DROP ROLE regress_priv_user5; -- should fail, dependency
107 ERROR: role "regress_priv_user5" cannot be dropped because some objects depend on it
108 DETAIL: privileges for membership of role regress_priv_user6 in role regress_priv_user1
109 DROP ROLE regress_priv_user1, regress_priv_user5; -- ok, despite order
110 -- recreate the roles we just dropped
111 CREATE USER regress_priv_user1;
112 CREATE USER regress_priv_user2;
113 CREATE USER regress_priv_user3;
114 CREATE USER regress_priv_user4;
115 CREATE USER regress_priv_user5;
116 GRANT pg_read_all_data TO regress_priv_user6;
117 GRANT pg_write_all_data TO regress_priv_user7;
118 GRANT pg_read_all_settings TO regress_priv_user8 WITH ADMIN OPTION;
119 GRANT regress_priv_user9 TO regress_priv_user8;
120 SET SESSION AUTHORIZATION regress_priv_user8;
121 GRANT pg_read_all_settings TO regress_priv_user9 WITH ADMIN OPTION;
122 SET SESSION AUTHORIZATION regress_priv_user9;
123 GRANT pg_read_all_settings TO regress_priv_user10;
124 SET SESSION AUTHORIZATION regress_priv_user8;
125 REVOKE pg_read_all_settings FROM regress_priv_user10 GRANTED BY regress_priv_user9;
126 REVOKE ADMIN OPTION FOR pg_read_all_settings FROM regress_priv_user9;
127 REVOKE pg_read_all_settings FROM regress_priv_user9;
128 RESET SESSION AUTHORIZATION;
129 REVOKE regress_priv_user9 FROM regress_priv_user8;
130 REVOKE ADMIN OPTION FOR pg_read_all_settings FROM regress_priv_user8;
131 SET SESSION AUTHORIZATION regress_priv_user8;
132 SET ROLE pg_read_all_settings;
133 RESET ROLE;
134 RESET SESSION AUTHORIZATION;
135 REVOKE SET OPTION FOR pg_read_all_settings FROM regress_priv_user8;
136 GRANT pg_read_all_stats TO regress_priv_user8 WITH SET FALSE;
137 SET SESSION AUTHORIZATION regress_priv_user8;
138 SET ROLE pg_read_all_settings; -- fail, no SET option any more
139 ERROR: permission denied to set role "pg_read_all_settings"
140 SET ROLE pg_read_all_stats; -- fail, granted without SET option
141 ERROR: permission denied to set role "pg_read_all_stats"
142 RESET ROLE;
143 RESET SESSION AUTHORIZATION;
144 -- test interaction of SET SESSION AUTHORIZATION and SET ROLE,
145 -- as well as propagation of these settings to parallel workers
146 GRANT regress_priv_user9 TO regress_priv_user8;
147 SET SESSION AUTHORIZATION regress_priv_user8;
148 SET ROLE regress_priv_user9;
149 SET debug_parallel_query = 0;
150 SELECT session_user, current_role, current_user, current_setting('role') as role;
151 session_user | current_role | current_user | role
152 --------------------+--------------------+--------------------+--------------------
153 regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9
154 (1 row)
156 SET debug_parallel_query = 1;
157 SELECT session_user, current_role, current_user, current_setting('role') as role;
158 session_user | current_role | current_user | role
159 --------------------+--------------------+--------------------+--------------------
160 regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9
161 (1 row)
163 BEGIN;
164 SET SESSION AUTHORIZATION regress_priv_user10;
165 SET debug_parallel_query = 0;
166 SELECT session_user, current_role, current_user, current_setting('role') as role;
167 session_user | current_role | current_user | role
168 ---------------------+---------------------+---------------------+------
169 regress_priv_user10 | regress_priv_user10 | regress_priv_user10 | none
170 (1 row)
172 SET debug_parallel_query = 1;
173 SELECT session_user, current_role, current_user, current_setting('role') as role;
174 session_user | current_role | current_user | role
175 ---------------------+---------------------+---------------------+------
176 regress_priv_user10 | regress_priv_user10 | regress_priv_user10 | none
177 (1 row)
179 ROLLBACK;
180 SET debug_parallel_query = 0;
181 SELECT session_user, current_role, current_user, current_setting('role') as role;
182 session_user | current_role | current_user | role
183 --------------------+--------------------+--------------------+--------------------
184 regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9
185 (1 row)
187 SET debug_parallel_query = 1;
188 SELECT session_user, current_role, current_user, current_setting('role') as role;
189 session_user | current_role | current_user | role
190 --------------------+--------------------+--------------------+--------------------
191 regress_priv_user8 | regress_priv_user9 | regress_priv_user9 | regress_priv_user9
192 (1 row)
194 RESET SESSION AUTHORIZATION;
195 -- session_user at this point is installation-dependent
196 SET debug_parallel_query = 0;
197 SELECT session_user = current_role as c_r_ok, session_user = current_user as c_u_ok, current_setting('role') as role;
198 c_r_ok | c_u_ok | role
199 --------+--------+------
200 t | t | none
201 (1 row)
203 SET debug_parallel_query = 1;
204 SELECT session_user = current_role as c_r_ok, session_user = current_user as c_u_ok, current_setting('role') as role;
205 c_r_ok | c_u_ok | role
206 --------+--------+------
207 t | t | none
208 (1 row)
210 RESET debug_parallel_query;
211 REVOKE pg_read_all_settings FROM regress_priv_user8;
212 DROP USER regress_priv_user10;
213 DROP USER regress_priv_user9;
214 DROP USER regress_priv_user8;
215 CREATE GROUP regress_priv_group1;
216 CREATE GROUP regress_priv_group2 WITH ADMIN regress_priv_user1 USER regress_priv_user2;
217 ALTER GROUP regress_priv_group1 ADD USER regress_priv_user4;
218 GRANT regress_priv_group2 TO regress_priv_user2 GRANTED BY regress_priv_user1;
219 SET SESSION AUTHORIZATION regress_priv_user1;
220 ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2;
221 NOTICE: role "regress_priv_user2" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user1"
222 ALTER GROUP regress_priv_group2 ADD USER regress_priv_user2; -- duplicate
223 NOTICE: role "regress_priv_user2" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user1"
224 ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;
225 ALTER USER regress_priv_user2 PASSWORD 'verysecret'; -- not permitted
226 ERROR: permission denied to alter role
227 DETAIL: To change another role's password, the current user must have the CREATEROLE attribute and the ADMIN option on the role.
228 RESET SESSION AUTHORIZATION;
229 ALTER GROUP regress_priv_group2 DROP USER regress_priv_user2;
230 REVOKE ADMIN OPTION FOR regress_priv_group2 FROM regress_priv_user1;
231 GRANT regress_priv_group2 TO regress_priv_user4 WITH ADMIN OPTION;
232 -- prepare non-leakproof function for later
233 CREATE FUNCTION leak(integer,integer) RETURNS boolean
234 AS 'int4lt'
235 LANGUAGE internal IMMUTABLE STRICT; -- but deliberately not LEAKPROOF
236 ALTER FUNCTION leak(integer,integer) OWNER TO regress_priv_user1;
237 -- test owner privileges
238 GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY regress_priv_role; -- error, doesn't have ADMIN OPTION
239 ERROR: permission denied to grant privileges as role "regress_priv_role"
240 DETAIL: The grantor must have the ADMIN option on role "regress_priv_role".
241 GRANT regress_priv_role TO regress_priv_user1 WITH ADMIN OPTION GRANTED BY CURRENT_ROLE;
242 REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY foo; -- error
243 ERROR: role "foo" does not exist
244 REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY regress_priv_user2; -- warning, noop
245 WARNING: role "regress_priv_user1" has not been granted membership in role "regress_priv_role" by role "regress_priv_user2"
246 REVOKE ADMIN OPTION FOR regress_priv_role FROM regress_priv_user1 GRANTED BY CURRENT_USER;
247 REVOKE regress_priv_role FROM regress_priv_user1 GRANTED BY CURRENT_ROLE;
248 DROP ROLE regress_priv_role;
249 SET SESSION AUTHORIZATION regress_priv_user1;
250 SELECT session_user, current_user;
251 session_user | current_user
252 --------------------+--------------------
253 regress_priv_user1 | regress_priv_user1
254 (1 row)
256 CREATE TABLE atest1 ( a int, b text );
257 SELECT * FROM atest1;
258 a | b
259 ---+---
260 (0 rows)
262 INSERT INTO atest1 VALUES (1, 'one');
263 DELETE FROM atest1;
264 UPDATE atest1 SET a = 1 WHERE b = 'blech';
265 TRUNCATE atest1;
266 BEGIN;
267 LOCK atest1 IN ACCESS EXCLUSIVE MODE;
268 COMMIT;
269 REVOKE ALL ON atest1 FROM PUBLIC;
270 SELECT * FROM atest1;
271 a | b
272 ---+---
273 (0 rows)
275 GRANT ALL ON atest1 TO regress_priv_user2;
276 GRANT SELECT ON atest1 TO regress_priv_user3, regress_priv_user4;
277 SELECT * FROM atest1;
278 a | b
279 ---+---
280 (0 rows)
282 CREATE TABLE atest2 (col1 varchar(10), col2 boolean);
283 SELECT pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid, 0);
284 pg_get_acl
285 ------------
287 (1 row)
289 GRANT SELECT ON atest2 TO regress_priv_user2;
290 GRANT UPDATE ON atest2 TO regress_priv_user3;
291 GRANT INSERT ON atest2 TO regress_priv_user4 GRANTED BY CURRENT_USER;
292 GRANT TRUNCATE ON atest2 TO regress_priv_user5 GRANTED BY CURRENT_ROLE;
293 SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest2'::regclass::oid, 0));
294 unnest
295 ------------------------------------------------
296 regress_priv_user1=arwdDxtm/regress_priv_user1
297 regress_priv_user2=r/regress_priv_user1
298 regress_priv_user3=w/regress_priv_user1
299 regress_priv_user4=a/regress_priv_user1
300 regress_priv_user5=D/regress_priv_user1
301 (5 rows)
303 -- Invalid inputs
304 SELECT pg_get_acl('pg_class'::regclass, 0, 0); -- null
305 pg_get_acl
306 ------------
308 (1 row)
310 SELECT pg_get_acl(0, 0, 0); -- null
311 pg_get_acl
312 ------------
314 (1 row)
316 GRANT TRUNCATE ON atest2 TO regress_priv_user4 GRANTED BY regress_priv_user5; -- error
317 ERROR: grantor must be current user
318 SET SESSION AUTHORIZATION regress_priv_user2;
319 SELECT session_user, current_user;
320 session_user | current_user
321 --------------------+--------------------
322 regress_priv_user2 | regress_priv_user2
323 (1 row)
325 -- try various combinations of queries on atest1 and atest2
326 SELECT * FROM atest1; -- ok
327 a | b
328 ---+---
329 (0 rows)
331 SELECT * FROM atest2; -- ok
332 col1 | col2
333 ------+------
334 (0 rows)
336 INSERT INTO atest1 VALUES (2, 'two'); -- ok
337 INSERT INTO atest2 VALUES ('foo', true); -- fail
338 ERROR: permission denied for table atest2
339 INSERT INTO atest1 SELECT 1, b FROM atest1; -- ok
340 UPDATE atest1 SET a = 1 WHERE a = 2; -- ok
341 UPDATE atest2 SET col2 = NOT col2; -- fail
342 ERROR: permission denied for table atest2
343 SELECT * FROM atest1 FOR UPDATE; -- ok
344 a | b
345 ---+-----
346 1 | two
347 1 | two
348 (2 rows)
350 SELECT * FROM atest2 FOR UPDATE; -- fail
351 ERROR: permission denied for table atest2
352 DELETE FROM atest2; -- fail
353 ERROR: permission denied for table atest2
354 TRUNCATE atest2; -- fail
355 ERROR: permission denied for table atest2
356 BEGIN;
357 LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- fail
358 ERROR: permission denied for table atest2
359 COMMIT;
360 COPY atest2 FROM stdin; -- fail
361 ERROR: permission denied for table atest2
362 GRANT ALL ON atest1 TO PUBLIC; -- fail
363 WARNING: no privileges were granted for "atest1"
364 -- checks in subquery, both ok
365 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
366 a | b
367 ---+---
368 (0 rows)
370 SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
371 col1 | col2
372 ------+------
373 (0 rows)
375 SET SESSION AUTHORIZATION regress_priv_user6;
376 SELECT * FROM atest1; -- ok
377 a | b
378 ---+-----
379 1 | two
380 1 | two
381 (2 rows)
383 SELECT * FROM atest2; -- ok
384 col1 | col2
385 ------+------
386 (0 rows)
388 INSERT INTO atest2 VALUES ('foo', true); -- fail
389 ERROR: permission denied for table atest2
390 SET SESSION AUTHORIZATION regress_priv_user7;
391 SELECT * FROM atest1; -- fail
392 ERROR: permission denied for table atest1
393 SELECT * FROM atest2; -- fail
394 ERROR: permission denied for table atest2
395 INSERT INTO atest2 VALUES ('foo', true); -- ok
396 UPDATE atest2 SET col2 = true; -- ok
397 DELETE FROM atest2; -- ok
398 -- Make sure we are not able to modify system catalogs
399 UPDATE pg_catalog.pg_class SET relname = '123'; -- fail
400 ERROR: permission denied for table pg_class
401 DELETE FROM pg_catalog.pg_class; -- fail
402 ERROR: permission denied for table pg_class
403 UPDATE pg_toast.pg_toast_1213 SET chunk_id = 1; -- fail
404 ERROR: permission denied for table pg_toast_1213
405 SET SESSION AUTHORIZATION regress_priv_user3;
406 SELECT session_user, current_user;
407 session_user | current_user
408 --------------------+--------------------
409 regress_priv_user3 | regress_priv_user3
410 (1 row)
412 SELECT * FROM atest1; -- ok
413 a | b
414 ---+-----
415 1 | two
416 1 | two
417 (2 rows)
419 SELECT * FROM atest2; -- fail
420 ERROR: permission denied for table atest2
421 INSERT INTO atest1 VALUES (2, 'two'); -- fail
422 ERROR: permission denied for table atest1
423 INSERT INTO atest2 VALUES ('foo', true); -- fail
424 ERROR: permission denied for table atest2
425 INSERT INTO atest1 SELECT 1, b FROM atest1; -- fail
426 ERROR: permission denied for table atest1
427 UPDATE atest1 SET a = 1 WHERE a = 2; -- fail
428 ERROR: permission denied for table atest1
429 UPDATE atest2 SET col2 = NULL; -- ok
430 UPDATE atest2 SET col2 = NOT col2; -- fails; requires SELECT on atest2
431 ERROR: permission denied for table atest2
432 UPDATE atest2 SET col2 = true FROM atest1 WHERE atest1.a = 5; -- ok
433 SELECT * FROM atest1 FOR UPDATE; -- fail
434 ERROR: permission denied for table atest1
435 SELECT * FROM atest2 FOR UPDATE; -- fail
436 ERROR: permission denied for table atest2
437 DELETE FROM atest2; -- fail
438 ERROR: permission denied for table atest2
439 TRUNCATE atest2; -- fail
440 ERROR: permission denied for table atest2
441 BEGIN;
442 LOCK atest2 IN ACCESS EXCLUSIVE MODE; -- ok
443 COMMIT;
444 COPY atest2 FROM stdin; -- fail
445 ERROR: permission denied for table atest2
446 -- checks in subquery, both fail
447 SELECT * FROM atest1 WHERE ( b IN ( SELECT col1 FROM atest2 ) );
448 ERROR: permission denied for table atest2
449 SELECT * FROM atest2 WHERE ( col1 IN ( SELECT b FROM atest1 ) );
450 ERROR: permission denied for table atest2
451 SET SESSION AUTHORIZATION regress_priv_user4;
452 COPY atest2 FROM stdin; -- ok
453 SELECT * FROM atest1; -- ok
454 a | b
455 ---+-----
456 1 | two
457 1 | two
458 (2 rows)
460 -- test leaky-function protections in selfuncs
461 -- regress_priv_user1 will own a table and provide views for it.
462 SET SESSION AUTHORIZATION regress_priv_user1;
463 CREATE TABLE atest12 as
464 SELECT x AS a, 10001 - x AS b FROM generate_series(1,10000) x;
465 CREATE INDEX ON atest12 (a);
466 CREATE INDEX ON atest12 (abs(a));
467 -- results below depend on having quite accurate stats for atest12, so...
468 ALTER TABLE atest12 SET (autovacuum_enabled = off);
469 SET default_statistics_target = 10000;
470 VACUUM ANALYZE atest12;
471 RESET default_statistics_target;
472 CREATE OPERATOR <<< (procedure = leak, leftarg = integer, rightarg = integer,
473 restrict = scalarltsel);
474 -- views with leaky operator
475 CREATE VIEW atest12v AS
476 SELECT * FROM atest12 WHERE b <<< 5;
477 CREATE VIEW atest12sbv WITH (security_barrier=true) AS
478 SELECT * FROM atest12 WHERE b <<< 5;
479 GRANT SELECT ON atest12v TO PUBLIC;
480 GRANT SELECT ON atest12sbv TO PUBLIC;
481 -- This plan should use nestloop, knowing that few rows will be selected.
482 EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
483 QUERY PLAN
484 -------------------------------------------------
485 Nested Loop
486 -> Seq Scan on atest12 atest12_1
487 Filter: (b <<< 5)
488 -> Index Scan using atest12_a_idx on atest12
489 Index Cond: (a = atest12_1.b)
490 Filter: (b <<< 5)
491 (6 rows)
493 -- And this one.
494 EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y
495 WHERE x.a = y.b and abs(y.a) <<< 5;
496 QUERY PLAN
497 ---------------------------------------------------
498 Nested Loop
499 -> Seq Scan on atest12 y
500 Filter: (abs(a) <<< 5)
501 -> Index Scan using atest12_a_idx on atest12 x
502 Index Cond: (a = y.b)
503 (5 rows)
505 -- This should also be a nestloop, but the security barrier forces the inner
506 -- scan to be materialized
507 EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b;
508 QUERY PLAN
509 -------------------------------------------
510 Nested Loop
511 Join Filter: (atest12.a = atest12_1.b)
512 -> Seq Scan on atest12
513 Filter: (b <<< 5)
514 -> Materialize
515 -> Seq Scan on atest12 atest12_1
516 Filter: (b <<< 5)
517 (7 rows)
519 -- Check if regress_priv_user2 can break security.
520 SET SESSION AUTHORIZATION regress_priv_user2;
521 CREATE FUNCTION leak2(integer,integer) RETURNS boolean
522 AS $$begin raise notice 'leak % %', $1, $2; return $1 > $2; end$$
523 LANGUAGE plpgsql immutable;
524 CREATE OPERATOR >>> (procedure = leak2, leftarg = integer, rightarg = integer,
525 restrict = scalargtsel);
526 -- This should not show any "leak" notices before failing.
527 EXPLAIN (COSTS OFF) SELECT * FROM atest12 WHERE a >>> 0;
528 ERROR: permission denied for table atest12
529 -- These plans should continue to use a nestloop, since they execute with the
530 -- privileges of the view owner.
531 EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
532 QUERY PLAN
533 -------------------------------------------------
534 Nested Loop
535 -> Seq Scan on atest12 atest12_1
536 Filter: (b <<< 5)
537 -> Index Scan using atest12_a_idx on atest12
538 Index Cond: (a = atest12_1.b)
539 Filter: (b <<< 5)
540 (6 rows)
542 EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y WHERE x.a = y.b;
543 QUERY PLAN
544 -------------------------------------------
545 Nested Loop
546 Join Filter: (atest12.a = atest12_1.b)
547 -> Seq Scan on atest12
548 Filter: (b <<< 5)
549 -> Materialize
550 -> Seq Scan on atest12 atest12_1
551 Filter: (b <<< 5)
552 (7 rows)
554 -- A non-security barrier view does not guard against information leakage.
555 EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y
556 WHERE x.a = y.b and abs(y.a) <<< 5;
557 QUERY PLAN
558 -------------------------------------------------
559 Nested Loop
560 -> Seq Scan on atest12 atest12_1
561 Filter: ((b <<< 5) AND (abs(a) <<< 5))
562 -> Index Scan using atest12_a_idx on atest12
563 Index Cond: (a = atest12_1.b)
564 Filter: (b <<< 5)
565 (6 rows)
567 -- But a security barrier view isolates the leaky operator.
568 EXPLAIN (COSTS OFF) SELECT * FROM atest12sbv x, atest12sbv y
569 WHERE x.a = y.b and abs(y.a) <<< 5;
570 QUERY PLAN
571 -------------------------------------
572 Nested Loop
573 Join Filter: (atest12_1.a = y.b)
574 -> Subquery Scan on y
575 Filter: (abs(y.a) <<< 5)
576 -> Seq Scan on atest12
577 Filter: (b <<< 5)
578 -> Seq Scan on atest12 atest12_1
579 Filter: (b <<< 5)
580 (8 rows)
582 -- Now regress_priv_user1 grants sufficient access to regress_priv_user2.
583 SET SESSION AUTHORIZATION regress_priv_user1;
584 GRANT SELECT (a, b) ON atest12 TO PUBLIC;
585 SET SESSION AUTHORIZATION regress_priv_user2;
586 -- regress_priv_user2 should continue to get a good row estimate.
587 EXPLAIN (COSTS OFF) SELECT * FROM atest12v x, atest12v y WHERE x.a = y.b;
588 QUERY PLAN
589 -------------------------------------------------
590 Nested Loop
591 -> Seq Scan on atest12 atest12_1
592 Filter: (b <<< 5)
593 -> Index Scan using atest12_a_idx on atest12
594 Index Cond: (a = atest12_1.b)
595 Filter: (b <<< 5)
596 (6 rows)
598 -- But not for this, due to lack of table-wide permissions needed
599 -- to make use of the expression index's statistics.
600 EXPLAIN (COSTS OFF) SELECT * FROM atest12 x, atest12 y
601 WHERE x.a = y.b and abs(y.a) <<< 5;
602 QUERY PLAN
603 --------------------------------------
604 Hash Join
605 Hash Cond: (x.a = y.b)
606 -> Seq Scan on atest12 x
607 -> Hash
608 -> Seq Scan on atest12 y
609 Filter: (abs(a) <<< 5)
610 (6 rows)
612 -- clean up (regress_priv_user1's objects are all dropped later)
613 DROP FUNCTION leak2(integer, integer) CASCADE;
614 NOTICE: drop cascades to operator >>>(integer,integer)
615 -- groups
616 SET SESSION AUTHORIZATION regress_priv_user3;
617 CREATE TABLE atest3 (one int, two int, three int);
618 GRANT DELETE ON atest3 TO GROUP regress_priv_group2;
619 SET SESSION AUTHORIZATION regress_priv_user1;
620 SELECT * FROM atest3; -- fail
621 ERROR: permission denied for table atest3
622 DELETE FROM atest3; -- ok
623 BEGIN;
624 RESET SESSION AUTHORIZATION;
625 ALTER ROLE regress_priv_user1 NOINHERIT;
626 SET SESSION AUTHORIZATION regress_priv_user1;
627 SAVEPOINT s1;
628 DELETE FROM atest3; -- ok because grant-level option is unchanged
629 ROLLBACK TO s1;
630 RESET SESSION AUTHORIZATION;
631 GRANT regress_priv_group2 TO regress_priv_user1 WITH INHERIT FALSE;
632 SET SESSION AUTHORIZATION regress_priv_user1;
633 DELETE FROM atest3; -- fail
634 ERROR: permission denied for table atest3
635 ROLLBACK TO s1;
636 RESET SESSION AUTHORIZATION;
637 REVOKE INHERIT OPTION FOR regress_priv_group2 FROM regress_priv_user1;
638 SET SESSION AUTHORIZATION regress_priv_user1;
639 DELETE FROM atest3; -- also fail
640 ERROR: permission denied for table atest3
641 ROLLBACK;
642 -- views
643 SET SESSION AUTHORIZATION regress_priv_user3;
644 CREATE VIEW atestv1 AS SELECT * FROM atest1; -- ok
645 /* The next *should* fail, but it's not implemented that way yet. */
646 CREATE VIEW atestv2 AS SELECT * FROM atest2;
647 CREATE VIEW atestv3 AS SELECT * FROM atest3; -- ok
648 /* Empty view is a corner case that failed in 9.2. */
649 CREATE VIEW atestv0 AS SELECT 0 as x WHERE false; -- ok
650 SELECT * FROM atestv1; -- ok
651 a | b
652 ---+-----
653 1 | two
654 1 | two
655 (2 rows)
657 SELECT * FROM atestv2; -- fail
658 ERROR: permission denied for table atest2
659 GRANT SELECT ON atestv1, atestv3 TO regress_priv_user4;
660 GRANT SELECT ON atestv2 TO regress_priv_user2;
661 SET SESSION AUTHORIZATION regress_priv_user4;
662 SELECT * FROM atestv1; -- ok
663 a | b
664 ---+-----
665 1 | two
666 1 | two
667 (2 rows)
669 SELECT * FROM atestv2; -- fail
670 ERROR: permission denied for view atestv2
671 SELECT * FROM atestv3; -- ok
672 one | two | three
673 -----+-----+-------
674 (0 rows)
676 SELECT * FROM atestv0; -- fail
677 ERROR: permission denied for view atestv0
678 -- Appendrels excluded by constraints failed to check permissions in 8.4-9.2.
679 select * from
680 ((select a.q1 as x from int8_tbl a offset 0)
681 union all
682 (select b.q2 as x from int8_tbl b offset 0)) ss
683 where false;
684 ERROR: permission denied for table int8_tbl
685 set constraint_exclusion = on;
686 select * from
687 ((select a.q1 as x, random() from int8_tbl a where q1 > 0)
688 union all
689 (select b.q2 as x, random() from int8_tbl b where q2 > 0)) ss
690 where x < 0;
691 ERROR: permission denied for table int8_tbl
692 reset constraint_exclusion;
693 CREATE VIEW atestv4 AS SELECT * FROM atestv3; -- nested view
694 SELECT * FROM atestv4; -- ok
695 one | two | three
696 -----+-----+-------
697 (0 rows)
699 GRANT SELECT ON atestv4 TO regress_priv_user2;
700 SET SESSION AUTHORIZATION regress_priv_user2;
701 -- Two complex cases:
702 SELECT * FROM atestv3; -- fail
703 ERROR: permission denied for view atestv3
704 SELECT * FROM atestv4; -- ok (even though regress_priv_user2 cannot access underlying atestv3)
705 one | two | three
706 -----+-----+-------
707 (0 rows)
709 SELECT * FROM atest2; -- ok
710 col1 | col2
711 ------+------
712 bar | t
713 (1 row)
715 SELECT * FROM atestv2; -- fail (even though regress_priv_user2 can access underlying atest2)
716 ERROR: permission denied for table atest2
717 -- Test column level permissions
718 SET SESSION AUTHORIZATION regress_priv_user1;
719 CREATE TABLE atest5 (one int, two int unique, three int, four int unique);
720 CREATE TABLE atest6 (one int, two int, blue int);
721 GRANT SELECT (one), INSERT (two), UPDATE (three) ON atest5 TO regress_priv_user4;
722 GRANT ALL (one) ON atest5 TO regress_priv_user3;
723 SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 1));
724 unnest
725 --------------------------------------------
726 regress_priv_user4=r/regress_priv_user1
727 regress_priv_user3=arwx/regress_priv_user1
728 (2 rows)
730 SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 2));
731 unnest
732 -----------------------------------------
733 regress_priv_user4=a/regress_priv_user1
734 (1 row)
736 SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 3));
737 unnest
738 -----------------------------------------
739 regress_priv_user4=w/regress_priv_user1
740 (1 row)
742 SELECT unnest(pg_get_acl('pg_class'::regclass, 'atest5'::regclass::oid, 4));
743 unnest
744 --------
745 (0 rows)
747 INSERT INTO atest5 VALUES (1,2,3);
748 SET SESSION AUTHORIZATION regress_priv_user4;
749 SELECT * FROM atest5; -- fail
750 ERROR: permission denied for table atest5
751 SELECT one FROM atest5; -- ok
752 one
753 -----
754 1
755 (1 row)
757 COPY atest5 (one) TO stdout; -- ok
758 1
759 SELECT two FROM atest5; -- fail
760 ERROR: permission denied for table atest5
761 COPY atest5 (two) TO stdout; -- fail
762 ERROR: permission denied for table atest5
763 SELECT atest5 FROM atest5; -- fail
764 ERROR: permission denied for table atest5
765 COPY atest5 (one,two) TO stdout; -- fail
766 ERROR: permission denied for table atest5
767 SELECT 1 FROM atest5; -- ok
768 ?column?
769 ----------
770 1
771 (1 row)
773 SELECT 1 FROM atest5 a JOIN atest5 b USING (one); -- ok
774 ?column?
775 ----------
776 1
777 (1 row)
779 SELECT 1 FROM atest5 a JOIN atest5 b USING (two); -- fail
780 ERROR: permission denied for table atest5
781 SELECT 1 FROM atest5 a NATURAL JOIN atest5 b; -- fail
782 ERROR: permission denied for table atest5
783 SELECT * FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail
784 ERROR: permission denied for table atest5
785 SELECT j.* FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail
786 ERROR: permission denied for table atest5
787 SELECT (j.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)) j; -- fail
788 ERROR: permission denied for table atest5
789 SELECT one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok
790 one
791 -----
792 1
793 (1 row)
795 SELECT j.one FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- ok
796 one
797 -----
798 1
799 (1 row)
801 SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail
802 ERROR: permission denied for table atest5
803 SELECT j.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail
804 ERROR: permission denied for table atest5
805 SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail
806 ERROR: permission denied for table atest5
807 SELECT j.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)) j; -- fail
808 ERROR: permission denied for table atest5
809 SELECT * FROM (atest5 a JOIN atest5 b USING (one)); -- fail
810 ERROR: permission denied for table atest5
811 SELECT a.* FROM (atest5 a JOIN atest5 b USING (one)); -- fail
812 ERROR: permission denied for table atest5
813 SELECT (a.*) IS NULL FROM (atest5 a JOIN atest5 b USING (one)); -- fail
814 ERROR: permission denied for table atest5
815 SELECT two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail
816 ERROR: permission denied for table atest5
817 SELECT a.two FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail
818 ERROR: permission denied for table atest5
819 SELECT y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail
820 ERROR: permission denied for table atest5
821 SELECT b.y FROM (atest5 a JOIN atest5 b(one,x,y,z) USING (one)); -- fail
822 ERROR: permission denied for table atest5
823 SELECT y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail
824 ERROR: permission denied for table atest5
825 SELECT b.y FROM (atest5 a LEFT JOIN atest5 b(one,x,y,z) USING (one)); -- fail
826 ERROR: permission denied for table atest5
827 SELECT y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail
828 ERROR: permission denied for table atest5
829 SELECT b.y FROM (atest5 a FULL JOIN atest5 b(one,x,y,z) USING (one)); -- fail
830 ERROR: permission denied for table atest5
831 SELECT 1 FROM atest5 WHERE two = 2; -- fail
832 ERROR: permission denied for table atest5
833 SELECT * FROM atest1, atest5; -- fail
834 ERROR: permission denied for table atest5
835 SELECT atest1.* FROM atest1, atest5; -- ok
836 a | b
837 ---+-----
838 1 | two
839 1 | two
840 (2 rows)
842 SELECT atest1.*,atest5.one FROM atest1, atest5; -- ok
843 a | b | one
844 ---+-----+-----
845 1 | two | 1
846 1 | two | 1
847 (2 rows)
849 SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.two); -- fail
850 ERROR: permission denied for table atest5
851 SELECT atest1.*,atest5.one FROM atest1 JOIN atest5 ON (atest1.a = atest5.one); -- ok
852 a | b | one
853 ---+-----+-----
854 1 | two | 1
855 1 | two | 1
856 (2 rows)
858 SELECT one, two FROM atest5; -- fail
859 ERROR: permission denied for table atest5
860 SET SESSION AUTHORIZATION regress_priv_user1;
861 GRANT SELECT (one,two) ON atest6 TO regress_priv_user4;
862 SET SESSION AUTHORIZATION regress_priv_user4;
863 SELECT one, two FROM atest5 NATURAL JOIN atest6; -- fail still
864 ERROR: permission denied for table atest5
865 SET SESSION AUTHORIZATION regress_priv_user1;
866 GRANT SELECT (two) ON atest5 TO regress_priv_user4;
867 SET SESSION AUTHORIZATION regress_priv_user4;
868 SELECT one, two FROM atest5 NATURAL JOIN atest6; -- ok now
869 one | two
870 -----+-----
871 (0 rows)
873 -- test column-level privileges for INSERT and UPDATE
874 INSERT INTO atest5 (two) VALUES (3); -- ok
875 COPY atest5 FROM stdin; -- fail
876 ERROR: permission denied for table atest5
877 COPY atest5 (two) FROM stdin; -- ok
878 INSERT INTO atest5 (three) VALUES (4); -- fail
879 ERROR: permission denied for table atest5
880 INSERT INTO atest5 VALUES (5,5,5); -- fail
881 ERROR: permission denied for table atest5
882 UPDATE atest5 SET three = 10; -- ok
883 UPDATE atest5 SET one = 8; -- fail
884 ERROR: permission denied for table atest5
885 UPDATE atest5 SET three = 5, one = 2; -- fail
886 ERROR: permission denied for table atest5
887 -- Check that column level privs are enforced in RETURNING
888 -- Ok.
889 INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10;
890 -- Error. No SELECT on column three.
891 INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.three;
892 ERROR: permission denied for table atest5
893 -- Ok. May SELECT on column "one":
894 INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = 10 RETURNING atest5.one;
895 one
896 -----
898 (1 row)
900 -- Check that column level privileges are enforced for EXCLUDED
901 -- Ok. we may select one
902 INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.one;
903 -- Error. No select rights on three
904 INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set three = EXCLUDED.three;
905 ERROR: permission denied for table atest5
906 INSERT INTO atest5(two) VALUES (6) ON CONFLICT (two) DO UPDATE set one = 8; -- fails (due to UPDATE)
907 ERROR: permission denied for table atest5
908 INSERT INTO atest5(three) VALUES (4) ON CONFLICT (two) DO UPDATE set three = 10; -- fails (due to INSERT)
909 ERROR: permission denied for table atest5
910 -- Check that the columns in the inference require select privileges
911 INSERT INTO atest5(four) VALUES (4); -- fail
912 ERROR: permission denied for table atest5
913 SET SESSION AUTHORIZATION regress_priv_user1;
914 GRANT INSERT (four) ON atest5 TO regress_priv_user4;
915 SET SESSION AUTHORIZATION regress_priv_user4;
916 INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- fails (due to SELECT)
917 ERROR: permission denied for table atest5
918 INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- fails (due to SELECT)
919 ERROR: permission denied for table atest5
920 INSERT INTO atest5(four) VALUES (4); -- ok
921 SET SESSION AUTHORIZATION regress_priv_user1;
922 GRANT SELECT (four) ON atest5 TO regress_priv_user4;
923 SET SESSION AUTHORIZATION regress_priv_user4;
924 INSERT INTO atest5(four) VALUES (4) ON CONFLICT (four) DO UPDATE set three = 3; -- ok
925 INSERT INTO atest5(four) VALUES (4) ON CONFLICT ON CONSTRAINT atest5_four_key DO UPDATE set three = 3; -- ok
926 SET SESSION AUTHORIZATION regress_priv_user1;
927 REVOKE ALL (one) ON atest5 FROM regress_priv_user4;
928 GRANT SELECT (one,two,blue) ON atest6 TO regress_priv_user4;
929 SET SESSION AUTHORIZATION regress_priv_user4;
930 SELECT one FROM atest5; -- fail
931 ERROR: permission denied for table atest5
932 UPDATE atest5 SET one = 1; -- fail
933 ERROR: permission denied for table atest5
934 SELECT atest6 FROM atest6; -- ok
935 atest6
936 --------
937 (0 rows)
939 COPY atest6 TO stdout; -- ok
940 -- test column privileges with MERGE
941 SET SESSION AUTHORIZATION regress_priv_user1;
942 CREATE TABLE mtarget (a int, b text);
943 CREATE TABLE msource (a int, b text);
944 INSERT INTO mtarget VALUES (1, 'init1'), (2, 'init2');
945 INSERT INTO msource VALUES (1, 'source1'), (2, 'source2'), (3, 'source3');
946 GRANT SELECT (a) ON msource TO regress_priv_user4;
947 GRANT SELECT (a) ON mtarget TO regress_priv_user4;
948 GRANT INSERT (a,b) ON mtarget TO regress_priv_user4;
949 GRANT UPDATE (b) ON mtarget TO regress_priv_user4;
950 SET SESSION AUTHORIZATION regress_priv_user4;
951 --
952 -- test source privileges
953 --
954 -- fail (no SELECT priv on s.b)
955 MERGE INTO mtarget t USING msource s ON t.a = s.a
956 WHEN MATCHED THEN
957 UPDATE SET b = s.b
958 WHEN NOT MATCHED THEN
959 INSERT VALUES (a, NULL);
960 ERROR: permission denied for table msource
961 -- fail (s.b used in the INSERTed values)
962 MERGE INTO mtarget t USING msource s ON t.a = s.a
963 WHEN MATCHED THEN
964 UPDATE SET b = 'x'
965 WHEN NOT MATCHED THEN
966 INSERT VALUES (a, b);
967 ERROR: permission denied for table msource
968 -- fail (s.b used in the WHEN quals)
969 MERGE INTO mtarget t USING msource s ON t.a = s.a
970 WHEN MATCHED AND s.b = 'x' THEN
971 UPDATE SET b = 'x'
972 WHEN NOT MATCHED THEN
973 INSERT VALUES (a, NULL);
974 ERROR: permission denied for table msource
975 -- this should be ok since only s.a is accessed
976 BEGIN;
977 MERGE INTO mtarget t USING msource s ON t.a = s.a
978 WHEN MATCHED THEN
979 UPDATE SET b = 'ok'
980 WHEN NOT MATCHED THEN
981 INSERT VALUES (a, NULL);
982 ROLLBACK;
983 SET SESSION AUTHORIZATION regress_priv_user1;
984 GRANT SELECT (b) ON msource TO regress_priv_user4;
985 SET SESSION AUTHORIZATION regress_priv_user4;
986 -- should now be ok
987 BEGIN;
988 MERGE INTO mtarget t USING msource s ON t.a = s.a
989 WHEN MATCHED THEN
990 UPDATE SET b = s.b
991 WHEN NOT MATCHED THEN
992 INSERT VALUES (a, b);
993 ROLLBACK;
994 --
995 -- test target privileges
996 --
997 -- fail (no SELECT priv on t.b)
998 MERGE INTO mtarget t USING msource s ON t.a = s.a
999 WHEN MATCHED THEN
1000 UPDATE SET b = t.b
1001 WHEN NOT MATCHED THEN
1002 INSERT VALUES (a, NULL);
1003 ERROR: permission denied for table mtarget
1004 -- fail (no UPDATE on t.a)
1005 MERGE INTO mtarget t USING msource s ON t.a = s.a
1006 WHEN MATCHED THEN
1007 UPDATE SET b = s.b, a = t.a + 1
1008 WHEN NOT MATCHED THEN
1009 INSERT VALUES (a, b);
1010 ERROR: permission denied for table mtarget
1011 -- fail (no SELECT on t.b)
1012 MERGE INTO mtarget t USING msource s ON t.a = s.a
1013 WHEN MATCHED AND t.b IS NOT NULL THEN
1014 UPDATE SET b = s.b
1015 WHEN NOT MATCHED THEN
1016 INSERT VALUES (a, b);
1017 ERROR: permission denied for table mtarget
1018 -- ok
1019 BEGIN;
1020 MERGE INTO mtarget t USING msource s ON t.a = s.a
1021 WHEN MATCHED THEN
1022 UPDATE SET b = s.b;
1023 ROLLBACK;
1024 -- fail (no DELETE)
1025 MERGE INTO mtarget t USING msource s ON t.a = s.a
1026 WHEN MATCHED AND t.b IS NOT NULL THEN
1027 DELETE;
1028 ERROR: permission denied for table mtarget
1029 -- grant delete privileges
1030 SET SESSION AUTHORIZATION regress_priv_user1;
1031 GRANT DELETE ON mtarget TO regress_priv_user4;
1032 -- should be ok now
1033 BEGIN;
1034 MERGE INTO mtarget t USING msource s ON t.a = s.a
1035 WHEN MATCHED AND t.b IS NOT NULL THEN
1036 DELETE;
1037 ROLLBACK;
1038 -- check error reporting with column privs
1039 SET SESSION AUTHORIZATION regress_priv_user1;
1040 CREATE TABLE t1 (c1 int, c2 int, c3 int check (c3 < 5), primary key (c1, c2));
1041 GRANT SELECT (c1) ON t1 TO regress_priv_user2;
1042 GRANT INSERT (c1, c2, c3) ON t1 TO regress_priv_user2;
1043 GRANT UPDATE (c1, c2, c3) ON t1 TO regress_priv_user2;
1044 -- seed data
1045 INSERT INTO t1 VALUES (1, 1, 1);
1046 INSERT INTO t1 VALUES (1, 2, 1);
1047 INSERT INTO t1 VALUES (2, 1, 2);
1048 INSERT INTO t1 VALUES (2, 2, 2);
1049 INSERT INTO t1 VALUES (3, 1, 3);
1050 SET SESSION AUTHORIZATION regress_priv_user2;
1051 INSERT INTO t1 (c1, c2) VALUES (1, 1); -- fail, but row not shown
1052 ERROR: duplicate key value violates unique constraint "t1_pkey"
1053 UPDATE t1 SET c2 = 1; -- fail, but row not shown
1054 ERROR: duplicate key value violates unique constraint "t1_pkey"
1055 INSERT INTO t1 (c1, c2) VALUES (null, null); -- fail, but see columns being inserted
1056 ERROR: null value in column "c1" of relation "t1" violates not-null constraint
1057 DETAIL: Failing row contains (c1, c2) = (null, null).
1058 INSERT INTO t1 (c3) VALUES (null); -- fail, but see columns being inserted or have SELECT
1059 ERROR: null value in column "c1" of relation "t1" violates not-null constraint
1060 DETAIL: Failing row contains (c1, c3) = (null, null).
1061 INSERT INTO t1 (c1) VALUES (5); -- fail, but see columns being inserted or have SELECT
1062 ERROR: null value in column "c2" of relation "t1" violates not-null constraint
1063 DETAIL: Failing row contains (c1) = (5).
1064 UPDATE t1 SET c3 = 10; -- fail, but see columns with SELECT rights, or being modified
1065 ERROR: new row for relation "t1" violates check constraint "t1_c3_check"
1066 DETAIL: Failing row contains (c1, c3) = (1, 10).
1067 SET SESSION AUTHORIZATION regress_priv_user1;
1068 DROP TABLE t1;
1069 -- check error reporting with column privs on a partitioned table
1070 CREATE TABLE errtst(a text, b text NOT NULL, c text, secret1 text, secret2 text) PARTITION BY LIST (a);
1071 CREATE TABLE errtst_part_1(secret2 text, c text, a text, b text NOT NULL, secret1 text);
1072 CREATE TABLE errtst_part_2(secret1 text, secret2 text, a text, c text, b text NOT NULL);
1073 ALTER TABLE errtst ATTACH PARTITION errtst_part_1 FOR VALUES IN ('aaa');
1074 ALTER TABLE errtst ATTACH PARTITION errtst_part_2 FOR VALUES IN ('aaaa');
1075 GRANT SELECT (a, b, c) ON TABLE errtst TO regress_priv_user2;
1076 GRANT UPDATE (a, b, c) ON TABLE errtst TO regress_priv_user2;
1077 GRANT INSERT (a, b, c) ON TABLE errtst TO regress_priv_user2;
1078 INSERT INTO errtst_part_1 (a, b, c, secret1, secret2)
1079 VALUES ('aaa', 'bbb', 'ccc', 'the body', 'is in the attic');
1080 SET SESSION AUTHORIZATION regress_priv_user2;
1081 -- Perform a few updates that violate the NOT NULL constraint. Make sure
1082 -- the error messages don't leak the secret fields.
1083 -- simple insert.
1084 INSERT INTO errtst (a, b) VALUES ('aaa', NULL);
1085 ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint
1086 DETAIL: Failing row contains (a, b, c) = (aaa, null, null).
1087 -- simple update.
1088 UPDATE errtst SET b = NULL;
1089 ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint
1090 DETAIL: Failing row contains (a, b, c) = (aaa, null, ccc).
1091 -- partitioning key is updated, doesn't move the row.
1092 UPDATE errtst SET a = 'aaa', b = NULL;
1093 ERROR: null value in column "b" of relation "errtst_part_1" violates not-null constraint
1094 DETAIL: Failing row contains (a, b, c) = (aaa, null, ccc).
1095 -- row is moved to another partition.
1096 UPDATE errtst SET a = 'aaaa', b = NULL;
1097 ERROR: null value in column "b" of relation "errtst_part_2" violates not-null constraint
1098 DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc).
1099 -- row is moved to another partition. This differs from the previous case in
1100 -- that the new partition is excluded by constraint exclusion, so its
1101 -- ResultRelInfo is not created at ExecInitModifyTable, but needs to be
1102 -- constructed on the fly when the updated tuple is routed to it.
1103 UPDATE errtst SET a = 'aaaa', b = NULL WHERE a = 'aaa';
1104 ERROR: null value in column "b" of relation "errtst_part_2" violates not-null constraint
1105 DETAIL: Failing row contains (a, b, c) = (aaaa, null, ccc).
1106 SET SESSION AUTHORIZATION regress_priv_user1;
1107 DROP TABLE errtst;
1108 -- test column-level privileges when involved with DELETE
1109 SET SESSION AUTHORIZATION regress_priv_user1;
1110 ALTER TABLE atest6 ADD COLUMN three integer;
1111 GRANT DELETE ON atest5 TO regress_priv_user3;
1112 GRANT SELECT (two) ON atest5 TO regress_priv_user3;
1113 REVOKE ALL (one) ON atest5 FROM regress_priv_user3;
1114 GRANT SELECT (one) ON atest5 TO regress_priv_user4;
1115 SET SESSION AUTHORIZATION regress_priv_user4;
1116 SELECT atest6 FROM atest6; -- fail
1117 ERROR: permission denied for table atest6
1118 SELECT one FROM atest5 NATURAL JOIN atest6; -- fail
1119 ERROR: permission denied for table atest5
1120 SET SESSION AUTHORIZATION regress_priv_user1;
1121 ALTER TABLE atest6 DROP COLUMN three;
1122 SET SESSION AUTHORIZATION regress_priv_user4;
1123 SELECT atest6 FROM atest6; -- ok
1124 atest6
1125 --------
1126 (0 rows)
1128 SELECT one FROM atest5 NATURAL JOIN atest6; -- ok
1129 one
1130 -----
1131 (0 rows)
1133 SET SESSION AUTHORIZATION regress_priv_user1;
1134 ALTER TABLE atest6 DROP COLUMN two;
1135 REVOKE SELECT (one,blue) ON atest6 FROM regress_priv_user4;
1136 SET SESSION AUTHORIZATION regress_priv_user4;
1137 SELECT * FROM atest6; -- fail
1138 ERROR: permission denied for table atest6
1139 SELECT 1 FROM atest6; -- fail
1140 ERROR: permission denied for table atest6
1141 SET SESSION AUTHORIZATION regress_priv_user3;
1142 DELETE FROM atest5 WHERE one = 1; -- fail
1143 ERROR: permission denied for table atest5
1144 DELETE FROM atest5 WHERE two = 2; -- ok
1145 -- check inheritance cases
1146 SET SESSION AUTHORIZATION regress_priv_user1;
1147 CREATE TABLE atestp1 (f1 int, f2 int);
1148 CREATE TABLE atestp2 (fx int, fy int);
1149 CREATE TABLE atestc (fz int) INHERITS (atestp1, atestp2);
1150 GRANT SELECT(fx,fy,tableoid) ON atestp2 TO regress_priv_user2;
1151 GRANT SELECT(fx) ON atestc TO regress_priv_user2;
1152 SET SESSION AUTHORIZATION regress_priv_user2;
1153 SELECT fx FROM atestp2; -- ok
1154 fx
1155 ----
1156 (0 rows)
1158 SELECT fy FROM atestp2; -- ok
1159 fy
1160 ----
1161 (0 rows)
1163 SELECT atestp2 FROM atestp2; -- ok
1164 atestp2
1165 ---------
1166 (0 rows)
1168 SELECT tableoid FROM atestp2; -- ok
1169 tableoid
1170 ----------
1171 (0 rows)
1173 SELECT fy FROM atestc; -- fail
1174 ERROR: permission denied for table atestc
1175 SET SESSION AUTHORIZATION regress_priv_user1;
1176 GRANT SELECT(fy,tableoid) ON atestc TO regress_priv_user2;
1177 SET SESSION AUTHORIZATION regress_priv_user2;
1178 SELECT fx FROM atestp2; -- still ok
1179 fx
1180 ----
1181 (0 rows)
1183 SELECT fy FROM atestp2; -- ok
1184 fy
1185 ----
1186 (0 rows)
1188 SELECT atestp2 FROM atestp2; -- ok
1189 atestp2
1190 ---------
1191 (0 rows)
1193 SELECT tableoid FROM atestp2; -- ok
1194 tableoid
1195 ----------
1196 (0 rows)
1198 -- child's permissions do not apply when operating on parent
1199 SET SESSION AUTHORIZATION regress_priv_user1;
1200 REVOKE ALL ON atestc FROM regress_priv_user2;
1201 GRANT ALL ON atestp1 TO regress_priv_user2;
1202 SET SESSION AUTHORIZATION regress_priv_user2;
1203 SELECT f2 FROM atestp1; -- ok
1204 f2
1205 ----
1206 (0 rows)
1208 SELECT f2 FROM atestc; -- fail
1209 ERROR: permission denied for table atestc
1210 DELETE FROM atestp1; -- ok
1211 DELETE FROM atestc; -- fail
1212 ERROR: permission denied for table atestc
1213 UPDATE atestp1 SET f1 = 1; -- ok
1214 UPDATE atestc SET f1 = 1; -- fail
1215 ERROR: permission denied for table atestc
1216 TRUNCATE atestp1; -- ok
1217 TRUNCATE atestc; -- fail
1218 ERROR: permission denied for table atestc
1219 BEGIN;
1220 LOCK atestp1;
1221 END;
1222 BEGIN;
1223 LOCK atestc;
1224 ERROR: permission denied for table atestc
1225 END;
1226 -- privileges on functions, languages
1227 -- switch to superuser
1228 \c -
1229 REVOKE ALL PRIVILEGES ON LANGUAGE sql FROM PUBLIC;
1230 GRANT USAGE ON LANGUAGE sql TO regress_priv_user1; -- ok
1231 GRANT USAGE ON LANGUAGE c TO PUBLIC; -- fail
1232 ERROR: language "c" is not trusted
1233 DETAIL: GRANT and REVOKE are not allowed on untrusted languages, because only superusers can use untrusted languages.
1234 SET SESSION AUTHORIZATION regress_priv_user1;
1235 GRANT USAGE ON LANGUAGE sql TO regress_priv_user2; -- fail
1236 WARNING: no privileges were granted for "sql"
1237 CREATE FUNCTION priv_testfunc1(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql;
1238 CREATE FUNCTION priv_testfunc2(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
1239 CREATE AGGREGATE priv_testagg1(int) (sfunc = int4pl, stype = int4);
1240 CREATE PROCEDURE priv_testproc1(int) AS 'select $1;' LANGUAGE sql;
1241 REVOKE ALL ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) FROM PUBLIC;
1242 GRANT EXECUTE ON FUNCTION priv_testfunc1(int), priv_testfunc2(int), priv_testagg1(int) TO regress_priv_user2;
1243 REVOKE ALL ON FUNCTION priv_testproc1(int) FROM PUBLIC; -- fail, not a function
1244 ERROR: priv_testproc1(integer) is not a function
1245 REVOKE ALL ON PROCEDURE priv_testproc1(int) FROM PUBLIC;
1246 GRANT EXECUTE ON PROCEDURE priv_testproc1(int) TO regress_priv_user2;
1247 GRANT USAGE ON FUNCTION priv_testfunc1(int) TO regress_priv_user3; -- semantic error
1248 ERROR: invalid privilege type USAGE for function
1249 GRANT USAGE ON FUNCTION priv_testagg1(int) TO regress_priv_user3; -- semantic error
1250 ERROR: invalid privilege type USAGE for function
1251 GRANT USAGE ON PROCEDURE priv_testproc1(int) TO regress_priv_user3; -- semantic error
1252 ERROR: invalid privilege type USAGE for procedure
1253 GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc1(int) TO regress_priv_user4;
1254 GRANT ALL PRIVILEGES ON FUNCTION priv_testfunc_nosuch(int) TO regress_priv_user4;
1255 ERROR: function priv_testfunc_nosuch(integer) does not exist
1256 GRANT ALL PRIVILEGES ON FUNCTION priv_testagg1(int) TO regress_priv_user4;
1257 GRANT ALL PRIVILEGES ON PROCEDURE priv_testproc1(int) TO regress_priv_user4;
1258 CREATE FUNCTION priv_testfunc4(boolean) RETURNS text
1259 AS 'select col1 from atest2 where col2 = $1;'
1260 LANGUAGE sql SECURITY DEFINER;
1261 GRANT EXECUTE ON FUNCTION priv_testfunc4(boolean) TO regress_priv_user3;
1262 SET SESSION AUTHORIZATION regress_priv_user2;
1263 SELECT priv_testfunc1(5), priv_testfunc2(5); -- ok
1264 priv_testfunc1 | priv_testfunc2
1265 ----------------+----------------
1266 10 | 15
1267 (1 row)
1269 CREATE FUNCTION priv_testfunc3(int) RETURNS int AS 'select 2 * $1;' LANGUAGE sql; -- fail
1270 ERROR: permission denied for language sql
1271 SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
1272 priv_testagg1
1273 ---------------
1274 6
1275 (1 row)
1277 CALL priv_testproc1(6); -- ok
1278 SET SESSION AUTHORIZATION regress_priv_user3;
1279 SELECT priv_testfunc1(5); -- fail
1280 ERROR: permission denied for function priv_testfunc1
1281 SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- fail
1282 ERROR: permission denied for aggregate priv_testagg1
1283 CALL priv_testproc1(6); -- fail
1284 ERROR: permission denied for procedure priv_testproc1
1285 SELECT col1 FROM atest2 WHERE col2 = true; -- fail
1286 ERROR: permission denied for table atest2
1287 SELECT priv_testfunc4(true); -- ok
1288 priv_testfunc4
1289 ----------------
1290 bar
1291 (1 row)
1293 SET SESSION AUTHORIZATION regress_priv_user4;
1294 SELECT priv_testfunc1(5); -- ok
1295 priv_testfunc1
1296 ----------------
1297 10
1298 (1 row)
1300 SELECT priv_testagg1(x) FROM (VALUES (1), (2), (3)) _(x); -- ok
1301 priv_testagg1
1302 ---------------
1303 6
1304 (1 row)
1306 CALL priv_testproc1(6); -- ok
1307 DROP FUNCTION priv_testfunc1(int); -- fail
1308 ERROR: must be owner of function priv_testfunc1
1309 DROP AGGREGATE priv_testagg1(int); -- fail
1310 ERROR: must be owner of aggregate priv_testagg1
1311 DROP PROCEDURE priv_testproc1(int); -- fail
1312 ERROR: must be owner of procedure priv_testproc1
1313 \c -
1314 DROP FUNCTION priv_testfunc1(int); -- ok
1315 -- restore to sanity
1316 GRANT ALL PRIVILEGES ON LANGUAGE sql TO PUBLIC;
1317 -- verify privilege checks on array-element coercions
1318 BEGIN;
1319 SELECT '{1}'::int4[]::int8[];
1320 int8
1321 ------
1322 {1}
1323 (1 row)
1325 REVOKE ALL ON FUNCTION int8(integer) FROM PUBLIC;
1326 SELECT '{1}'::int4[]::int8[]; --superuser, succeed
1327 int8
1328 ------
1329 {1}
1330 (1 row)
1332 SET SESSION AUTHORIZATION regress_priv_user4;
1333 SELECT '{1}'::int4[]::int8[]; --other user, fail
1334 ERROR: permission denied for function int8
1335 ROLLBACK;
1336 -- privileges on types
1337 -- switch to superuser
1338 \c -
1339 CREATE TYPE priv_testtype1 AS (a int, b text);
1340 REVOKE USAGE ON TYPE priv_testtype1 FROM PUBLIC;
1341 GRANT USAGE ON TYPE priv_testtype1 TO regress_priv_user2;
1342 GRANT USAGE ON TYPE _priv_testtype1 TO regress_priv_user2; -- fail
1343 ERROR: cannot set privileges of array types
1344 HINT: Set the privileges of the element type instead.
1345 GRANT USAGE ON DOMAIN priv_testtype1 TO regress_priv_user2; -- fail
1346 ERROR: "priv_testtype1" is not a domain
1347 CREATE DOMAIN priv_testdomain1 AS int;
1348 REVOKE USAGE on DOMAIN priv_testdomain1 FROM PUBLIC;
1349 GRANT USAGE ON DOMAIN priv_testdomain1 TO regress_priv_user2;
1350 GRANT USAGE ON TYPE priv_testdomain1 TO regress_priv_user2; -- ok
1351 SET SESSION AUTHORIZATION regress_priv_user1;
1352 -- commands that should fail
1353 CREATE AGGREGATE priv_testagg1a(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
1354 ERROR: permission denied for type priv_testdomain1
1355 CREATE DOMAIN priv_testdomain2a AS priv_testdomain1;
1356 ERROR: permission denied for type priv_testdomain1
1357 CREATE DOMAIN priv_testdomain3a AS int;
1358 CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3a AS $$ SELECT $1::priv_testdomain3a $$ LANGUAGE SQL;
1359 CREATE CAST (priv_testdomain1 AS priv_testdomain3a) WITH FUNCTION castfunc(int);
1360 ERROR: permission denied for type priv_testdomain1
1361 DROP FUNCTION castfunc(int) CASCADE;
1362 DROP DOMAIN priv_testdomain3a;
1363 CREATE FUNCTION priv_testfunc5a(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$;
1364 ERROR: permission denied for type priv_testdomain1
1365 CREATE FUNCTION priv_testfunc6a(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$;
1366 ERROR: permission denied for type priv_testdomain1
1367 CREATE OPERATOR !+! (PROCEDURE = int4pl, LEFTARG = priv_testdomain1, RIGHTARG = priv_testdomain1);
1368 ERROR: permission denied for type priv_testdomain1
1369 CREATE TABLE test5a (a int, b priv_testdomain1);
1370 ERROR: permission denied for type priv_testdomain1
1371 CREATE TABLE test6a OF priv_testtype1;
1372 ERROR: permission denied for type priv_testtype1
1373 CREATE TABLE test10a (a int[], b priv_testtype1[]);
1374 ERROR: permission denied for type priv_testtype1
1375 CREATE TABLE test9a (a int, b int);
1376 ALTER TABLE test9a ADD COLUMN c priv_testdomain1;
1377 ERROR: permission denied for type priv_testdomain1
1378 ALTER TABLE test9a ALTER COLUMN b TYPE priv_testdomain1;
1379 ERROR: permission denied for type priv_testdomain1
1380 CREATE TYPE test7a AS (a int, b priv_testdomain1);
1381 ERROR: permission denied for type priv_testdomain1
1382 CREATE TYPE test8a AS (a int, b int);
1383 ALTER TYPE test8a ADD ATTRIBUTE c priv_testdomain1;
1384 ERROR: permission denied for type priv_testdomain1
1385 ALTER TYPE test8a ALTER ATTRIBUTE b TYPE priv_testdomain1;
1386 ERROR: permission denied for type priv_testdomain1
1387 CREATE TABLE test11a AS (SELECT 1::priv_testdomain1 AS a);
1388 ERROR: permission denied for type priv_testdomain1
1389 REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
1390 ERROR: permission denied for type priv_testtype1
1391 SET SESSION AUTHORIZATION regress_priv_user2;
1392 -- commands that should succeed
1393 CREATE AGGREGATE priv_testagg1b(priv_testdomain1) (sfunc = int4_sum, stype = bigint);
1394 CREATE DOMAIN priv_testdomain2b AS priv_testdomain1;
1395 CREATE DOMAIN priv_testdomain3b AS int;
1396 CREATE FUNCTION castfunc(int) RETURNS priv_testdomain3b AS $$ SELECT $1::priv_testdomain3b $$ LANGUAGE SQL;
1397 CREATE CAST (priv_testdomain1 AS priv_testdomain3b) WITH FUNCTION castfunc(int);
1398 WARNING: cast will be ignored because the source data type is a domain
1399 CREATE FUNCTION priv_testfunc5b(a priv_testdomain1) RETURNS int LANGUAGE SQL AS $$ SELECT $1 $$;
1400 CREATE FUNCTION priv_testfunc6b(b int) RETURNS priv_testdomain1 LANGUAGE SQL AS $$ SELECT $1::priv_testdomain1 $$;
1401 CREATE OPERATOR !! (PROCEDURE = priv_testfunc5b, RIGHTARG = priv_testdomain1);
1402 CREATE TABLE test5b (a int, b priv_testdomain1);
1403 CREATE TABLE test6b OF priv_testtype1;
1404 CREATE TABLE test10b (a int[], b priv_testtype1[]);
1405 CREATE TABLE test9b (a int, b int);
1406 ALTER TABLE test9b ADD COLUMN c priv_testdomain1;
1407 ALTER TABLE test9b ALTER COLUMN b TYPE priv_testdomain1;
1408 CREATE TYPE test7b AS (a int, b priv_testdomain1);
1409 CREATE TYPE test8b AS (a int, b int);
1410 ALTER TYPE test8b ADD ATTRIBUTE c priv_testdomain1;
1411 ALTER TYPE test8b ALTER ATTRIBUTE b TYPE priv_testdomain1;
1412 CREATE TABLE test11b AS (SELECT 1::priv_testdomain1 AS a);
1413 REVOKE ALL ON TYPE priv_testtype1 FROM PUBLIC;
1414 WARNING: no privileges could be revoked for "priv_testtype1"
1415 \c -
1416 DROP AGGREGATE priv_testagg1b(priv_testdomain1);
1417 DROP DOMAIN priv_testdomain2b;
1418 DROP OPERATOR !! (NONE, priv_testdomain1);
1419 DROP FUNCTION priv_testfunc5b(a priv_testdomain1);
1420 DROP FUNCTION priv_testfunc6b(b int);
1421 DROP TABLE test5b;
1422 DROP TABLE test6b;
1423 DROP TABLE test9b;
1424 DROP TABLE test10b;
1425 DROP TYPE test7b;
1426 DROP TYPE test8b;
1427 DROP CAST (priv_testdomain1 AS priv_testdomain3b);
1428 DROP FUNCTION castfunc(int) CASCADE;
1429 DROP DOMAIN priv_testdomain3b;
1430 DROP TABLE test11b;
1431 DROP TYPE priv_testtype1; -- ok
1432 DROP DOMAIN priv_testdomain1; -- ok
1433 -- truncate
1434 SET SESSION AUTHORIZATION regress_priv_user5;
1435 TRUNCATE atest2; -- ok
1436 TRUNCATE atest3; -- fail
1437 ERROR: permission denied for table atest3
1438 -- has_table_privilege function
1439 -- bad-input checks
1440 select has_table_privilege(NULL,'pg_authid','select');
1441 has_table_privilege
1442 ---------------------
1444 (1 row)
1446 select has_table_privilege('pg_shad','select');
1447 ERROR: relation "pg_shad" does not exist
1448 select has_table_privilege('nosuchuser','pg_authid','select');
1449 ERROR: role "nosuchuser" does not exist
1450 select has_table_privilege('pg_authid','sel');
1451 ERROR: unrecognized privilege type: "sel"
1452 select has_table_privilege(-999999,'pg_authid','update');
1453 has_table_privilege
1454 ---------------------
1455 f
1456 (1 row)
1458 select has_table_privilege(1,'select');
1459 has_table_privilege
1460 ---------------------
1462 (1 row)
1464 -- superuser
1465 \c -
1466 select has_table_privilege(current_user,'pg_authid','select');
1467 has_table_privilege
1468 ---------------------
1469 t
1470 (1 row)
1472 select has_table_privilege(current_user,'pg_authid','insert');
1473 has_table_privilege
1474 ---------------------
1475 t
1476 (1 row)
1478 select has_table_privilege(t2.oid,'pg_authid','update')
1479 from (select oid from pg_roles where rolname = current_user) as t2;
1480 has_table_privilege
1481 ---------------------
1482 t
1483 (1 row)
1485 select has_table_privilege(t2.oid,'pg_authid','delete')
1486 from (select oid from pg_roles where rolname = current_user) as t2;
1487 has_table_privilege
1488 ---------------------
1489 t
1490 (1 row)
1492 select has_table_privilege(current_user,t1.oid,'references')
1493 from (select oid from pg_class where relname = 'pg_authid') as t1;
1494 has_table_privilege
1495 ---------------------
1496 t
1497 (1 row)
1499 select has_table_privilege(t2.oid,t1.oid,'select')
1500 from (select oid from pg_class where relname = 'pg_authid') as t1,
1501 (select oid from pg_roles where rolname = current_user) as t2;
1502 has_table_privilege
1503 ---------------------
1504 t
1505 (1 row)
1507 select has_table_privilege(t2.oid,t1.oid,'insert')
1508 from (select oid from pg_class where relname = 'pg_authid') as t1,
1509 (select oid from pg_roles where rolname = current_user) as t2;
1510 has_table_privilege
1511 ---------------------
1512 t
1513 (1 row)
1515 select has_table_privilege('pg_authid','update');
1516 has_table_privilege
1517 ---------------------
1518 t
1519 (1 row)
1521 select has_table_privilege('pg_authid','delete');
1522 has_table_privilege
1523 ---------------------
1524 t
1525 (1 row)
1527 select has_table_privilege('pg_authid','truncate');
1528 has_table_privilege
1529 ---------------------
1530 t
1531 (1 row)
1533 select has_table_privilege(t1.oid,'select')
1534 from (select oid from pg_class where relname = 'pg_authid') as t1;
1535 has_table_privilege
1536 ---------------------
1537 t
1538 (1 row)
1540 select has_table_privilege(t1.oid,'trigger')
1541 from (select oid from pg_class where relname = 'pg_authid') as t1;
1542 has_table_privilege
1543 ---------------------
1544 t
1545 (1 row)
1547 -- non-superuser
1548 SET SESSION AUTHORIZATION regress_priv_user3;
1549 select has_table_privilege(current_user,'pg_class','select');
1550 has_table_privilege
1551 ---------------------
1552 t
1553 (1 row)
1555 select has_table_privilege(current_user,'pg_class','insert');
1556 has_table_privilege
1557 ---------------------
1558 f
1559 (1 row)
1561 select has_table_privilege(t2.oid,'pg_class','update')
1562 from (select oid from pg_roles where rolname = current_user) as t2;
1563 has_table_privilege
1564 ---------------------
1565 f
1566 (1 row)
1568 select has_table_privilege(t2.oid,'pg_class','delete')
1569 from (select oid from pg_roles where rolname = current_user) as t2;
1570 has_table_privilege
1571 ---------------------
1572 f
1573 (1 row)
1575 select has_table_privilege(current_user,t1.oid,'references')
1576 from (select oid from pg_class where relname = 'pg_class') as t1;
1577 has_table_privilege
1578 ---------------------
1579 f
1580 (1 row)
1582 select has_table_privilege(t2.oid,t1.oid,'select')
1583 from (select oid from pg_class where relname = 'pg_class') as t1,
1584 (select oid from pg_roles where rolname = current_user) as t2;
1585 has_table_privilege
1586 ---------------------
1587 t
1588 (1 row)
1590 select has_table_privilege(t2.oid,t1.oid,'insert')
1591 from (select oid from pg_class where relname = 'pg_class') as t1,
1592 (select oid from pg_roles where rolname = current_user) as t2;
1593 has_table_privilege
1594 ---------------------
1595 f
1596 (1 row)
1598 select has_table_privilege('pg_class','update');
1599 has_table_privilege
1600 ---------------------
1601 f
1602 (1 row)
1604 select has_table_privilege('pg_class','delete');
1605 has_table_privilege
1606 ---------------------
1607 f
1608 (1 row)
1610 select has_table_privilege('pg_class','truncate');
1611 has_table_privilege
1612 ---------------------
1613 f
1614 (1 row)
1616 select has_table_privilege(t1.oid,'select')
1617 from (select oid from pg_class where relname = 'pg_class') as t1;
1618 has_table_privilege
1619 ---------------------
1620 t
1621 (1 row)
1623 select has_table_privilege(t1.oid,'trigger')
1624 from (select oid from pg_class where relname = 'pg_class') as t1;
1625 has_table_privilege
1626 ---------------------
1627 f
1628 (1 row)
1630 select has_table_privilege(current_user,'atest1','select');
1631 has_table_privilege
1632 ---------------------
1633 t
1634 (1 row)
1636 select has_table_privilege(current_user,'atest1','insert');
1637 has_table_privilege
1638 ---------------------
1639 f
1640 (1 row)
1642 select has_table_privilege(t2.oid,'atest1','update')
1643 from (select oid from pg_roles where rolname = current_user) as t2;
1644 has_table_privilege
1645 ---------------------
1646 f
1647 (1 row)
1649 select has_table_privilege(t2.oid,'atest1','delete')
1650 from (select oid from pg_roles where rolname = current_user) as t2;
1651 has_table_privilege
1652 ---------------------
1653 f
1654 (1 row)
1656 select has_table_privilege(current_user,t1.oid,'references')
1657 from (select oid from pg_class where relname = 'atest1') as t1;
1658 has_table_privilege
1659 ---------------------
1660 f
1661 (1 row)
1663 select has_table_privilege(t2.oid,t1.oid,'select')
1664 from (select oid from pg_class where relname = 'atest1') as t1,
1665 (select oid from pg_roles where rolname = current_user) as t2;
1666 has_table_privilege
1667 ---------------------
1668 t
1669 (1 row)
1671 select has_table_privilege(t2.oid,t1.oid,'insert')
1672 from (select oid from pg_class where relname = 'atest1') as t1,
1673 (select oid from pg_roles where rolname = current_user) as t2;
1674 has_table_privilege
1675 ---------------------
1676 f
1677 (1 row)
1679 select has_table_privilege('atest1','update');
1680 has_table_privilege
1681 ---------------------
1682 f
1683 (1 row)
1685 select has_table_privilege('atest1','delete');
1686 has_table_privilege
1687 ---------------------
1688 f
1689 (1 row)
1691 select has_table_privilege('atest1','truncate');
1692 has_table_privilege
1693 ---------------------
1694 f
1695 (1 row)
1697 select has_table_privilege(t1.oid,'select')
1698 from (select oid from pg_class where relname = 'atest1') as t1;
1699 has_table_privilege
1700 ---------------------
1701 t
1702 (1 row)
1704 select has_table_privilege(t1.oid,'trigger')
1705 from (select oid from pg_class where relname = 'atest1') as t1;
1706 has_table_privilege
1707 ---------------------
1708 f
1709 (1 row)
1711 -- has_column_privilege function
1712 -- bad-input checks (as non-super-user)
1713 select has_column_privilege('pg_authid',NULL,'select');
1714 has_column_privilege
1715 ----------------------
1717 (1 row)
1719 select has_column_privilege('pg_authid','nosuchcol','select');
1720 ERROR: column "nosuchcol" of relation "pg_authid" does not exist
1721 select has_column_privilege(9999,'nosuchcol','select');
1722 has_column_privilege
1723 ----------------------
1725 (1 row)
1727 select has_column_privilege(9999,99::int2,'select');
1728 has_column_privilege
1729 ----------------------
1731 (1 row)
1733 select has_column_privilege('pg_authid',99::int2,'select');
1734 has_column_privilege
1735 ----------------------
1737 (1 row)
1739 select has_column_privilege(9999,99::int2,'select');
1740 has_column_privilege
1741 ----------------------
1743 (1 row)
1745 create temp table mytable(f1 int, f2 int, f3 int);
1746 alter table mytable drop column f2;
1747 select has_column_privilege('mytable','f2','select');
1748 ERROR: column "f2" of relation "mytable" does not exist
1749 select has_column_privilege('mytable','........pg.dropped.2........','select');
1750 has_column_privilege
1751 ----------------------
1753 (1 row)
1755 select has_column_privilege('mytable',2::int2,'select');
1756 has_column_privilege
1757 ----------------------
1759 (1 row)
1761 select has_column_privilege('mytable',99::int2,'select');
1762 has_column_privilege
1763 ----------------------
1765 (1 row)
1767 revoke select on table mytable from regress_priv_user3;
1768 select has_column_privilege('mytable',2::int2,'select');
1769 has_column_privilege
1770 ----------------------
1772 (1 row)
1774 select has_column_privilege('mytable',99::int2,'select');
1775 has_column_privilege
1776 ----------------------
1778 (1 row)
1780 drop table mytable;
1781 -- Grant options
1782 SET SESSION AUTHORIZATION regress_priv_user1;
1783 CREATE TABLE atest4 (a int);
1784 GRANT SELECT ON atest4 TO regress_priv_user2 WITH GRANT OPTION;
1785 GRANT UPDATE ON atest4 TO regress_priv_user2;
1786 GRANT SELECT ON atest4 TO GROUP regress_priv_group1 WITH GRANT OPTION;
1787 SET SESSION AUTHORIZATION regress_priv_user2;
1788 GRANT SELECT ON atest4 TO regress_priv_user3;
1789 GRANT UPDATE ON atest4 TO regress_priv_user3; -- fail
1790 WARNING: no privileges were granted for "atest4"
1791 SET SESSION AUTHORIZATION regress_priv_user1;
1792 REVOKE SELECT ON atest4 FROM regress_priv_user3; -- does nothing
1793 SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- true
1794 has_table_privilege
1795 ---------------------
1796 t
1797 (1 row)
1799 REVOKE SELECT ON atest4 FROM regress_priv_user2; -- fail
1800 ERROR: dependent privileges exist
1801 HINT: Use CASCADE to revoke them too.
1802 REVOKE GRANT OPTION FOR SELECT ON atest4 FROM regress_priv_user2 CASCADE; -- ok
1803 SELECT has_table_privilege('regress_priv_user2', 'atest4', 'SELECT'); -- true
1804 has_table_privilege
1805 ---------------------
1806 t
1807 (1 row)
1809 SELECT has_table_privilege('regress_priv_user3', 'atest4', 'SELECT'); -- false
1810 has_table_privilege
1811 ---------------------
1812 f
1813 (1 row)
1815 SELECT has_table_privilege('regress_priv_user1', 'atest4', 'SELECT WITH GRANT OPTION'); -- true
1816 has_table_privilege
1817 ---------------------
1818 t
1819 (1 row)
1821 -- security-restricted operations
1822 \c -
1823 CREATE ROLE regress_sro_user;
1824 -- Check that index expressions and predicates are run as the table's owner
1825 -- A dummy index function checking current_user
1826 CREATE FUNCTION sro_ifun(int) RETURNS int AS $$
1827 BEGIN
1828 -- Below we set the table's owner to regress_sro_user
1829 ASSERT current_user = 'regress_sro_user',
1830 format('sro_ifun(%s) called by %s', $1, current_user);
1831 RETURN $1;
1832 END;
1833 $$ LANGUAGE plpgsql IMMUTABLE;
1834 -- Create a table owned by regress_sro_user
1835 CREATE TABLE sro_tab (a int);
1836 ALTER TABLE sro_tab OWNER TO regress_sro_user;
1837 INSERT INTO sro_tab VALUES (1), (2), (3);
1838 -- Create an expression index with a predicate
1839 CREATE INDEX sro_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0)))
1840 WHERE sro_ifun(a + 10) > sro_ifun(10);
1841 DROP INDEX sro_idx;
1842 -- Do the same concurrently
1843 CREATE INDEX CONCURRENTLY sro_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0)))
1844 WHERE sro_ifun(a + 10) > sro_ifun(10);
1845 -- REINDEX
1846 REINDEX TABLE sro_tab;
1847 REINDEX INDEX sro_idx;
1848 REINDEX TABLE CONCURRENTLY sro_tab;
1849 DROP INDEX sro_idx;
1850 -- CLUSTER
1851 CREATE INDEX sro_cluster_idx ON sro_tab ((sro_ifun(a) + sro_ifun(0)));
1852 CLUSTER sro_tab USING sro_cluster_idx;
1853 DROP INDEX sro_cluster_idx;
1854 -- BRIN index
1855 CREATE INDEX sro_brin ON sro_tab USING brin ((sro_ifun(a) + sro_ifun(0)));
1856 SELECT brin_desummarize_range('sro_brin', 0);
1857 brin_desummarize_range
1858 ------------------------
1860 (1 row)
1862 SELECT brin_summarize_range('sro_brin', 0);
1863 brin_summarize_range
1864 ----------------------
1865 1
1866 (1 row)
1868 DROP TABLE sro_tab;
1869 -- Check with a partitioned table
1870 CREATE TABLE sro_ptab (a int) PARTITION BY RANGE (a);
1871 ALTER TABLE sro_ptab OWNER TO regress_sro_user;
1872 CREATE TABLE sro_part PARTITION OF sro_ptab FOR VALUES FROM (1) TO (10);
1873 ALTER TABLE sro_part OWNER TO regress_sro_user;
1874 INSERT INTO sro_ptab VALUES (1), (2), (3);
1875 CREATE INDEX sro_pidx ON sro_ptab ((sro_ifun(a) + sro_ifun(0)))
1876 WHERE sro_ifun(a + 10) > sro_ifun(10);
1877 REINDEX TABLE sro_ptab;
1878 REINDEX INDEX CONCURRENTLY sro_pidx;
1879 SET SESSION AUTHORIZATION regress_sro_user;
1880 CREATE FUNCTION unwanted_grant() RETURNS void LANGUAGE sql AS
1881 'GRANT regress_priv_group2 TO regress_sro_user';
1882 CREATE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS
1883 'DECLARE c CURSOR WITH HOLD FOR SELECT public.unwanted_grant(); SELECT true';
1884 -- REFRESH of this MV will queue a GRANT at end of transaction
1885 CREATE MATERIALIZED VIEW sro_mv AS SELECT mv_action() WITH NO DATA;
1886 REFRESH MATERIALIZED VIEW sro_mv;
1887 ERROR: cannot create a cursor WITH HOLD within security-restricted operation
1888 CONTEXT: SQL function "mv_action" statement 1
1889 \c -
1890 REFRESH MATERIALIZED VIEW sro_mv;
1891 ERROR: cannot create a cursor WITH HOLD within security-restricted operation
1892 CONTEXT: SQL function "mv_action" statement 1
1893 SET SESSION AUTHORIZATION regress_sro_user;
1894 -- INSERT to this table will queue a GRANT at end of transaction
1895 CREATE TABLE sro_trojan_table ();
1896 CREATE FUNCTION sro_trojan() RETURNS trigger LANGUAGE plpgsql AS
1897 'BEGIN PERFORM public.unwanted_grant(); RETURN NULL; END';
1898 CREATE CONSTRAINT TRIGGER t AFTER INSERT ON sro_trojan_table
1899 INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE sro_trojan();
1900 -- Now, REFRESH will issue such an INSERT, queueing the GRANT
1901 CREATE OR REPLACE FUNCTION mv_action() RETURNS bool LANGUAGE sql AS
1902 'INSERT INTO public.sro_trojan_table DEFAULT VALUES; SELECT true';
1903 REFRESH MATERIALIZED VIEW sro_mv;
1904 ERROR: cannot fire deferred trigger within security-restricted operation
1905 CONTEXT: SQL function "mv_action" statement 1
1906 \c -
1907 REFRESH MATERIALIZED VIEW sro_mv;
1908 ERROR: cannot fire deferred trigger within security-restricted operation
1909 CONTEXT: SQL function "mv_action" statement 1
1910 BEGIN; SET CONSTRAINTS ALL IMMEDIATE; REFRESH MATERIALIZED VIEW sro_mv; COMMIT;
1911 ERROR: permission denied to grant role "regress_priv_group2"
1912 DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
1913 CONTEXT: SQL function "unwanted_grant" statement 1
1914 SQL statement "SELECT public.unwanted_grant()"
1915 PL/pgSQL function public.sro_trojan() line 1 at PERFORM
1916 SQL function "mv_action" statement 1
1917 -- REFRESH MATERIALIZED VIEW CONCURRENTLY use of eval_const_expressions()
1918 SET SESSION AUTHORIZATION regress_sro_user;
1919 CREATE FUNCTION unwanted_grant_nofail(int) RETURNS int
1920 IMMUTABLE LANGUAGE plpgsql AS $$
1921 BEGIN
1922 PERFORM public.unwanted_grant();
1923 RAISE WARNING 'owned';
1924 RETURN 1;
1925 EXCEPTION WHEN OTHERS THEN
1926 RETURN 2;
1927 END$$;
1928 CREATE MATERIALIZED VIEW sro_index_mv AS SELECT 1 AS c;
1929 CREATE UNIQUE INDEX ON sro_index_mv (c) WHERE unwanted_grant_nofail(1) > 0;
1930 \c -
1931 REFRESH MATERIALIZED VIEW CONCURRENTLY sro_index_mv;
1932 REFRESH MATERIALIZED VIEW sro_index_mv;
1933 DROP OWNED BY regress_sro_user;
1934 DROP ROLE regress_sro_user;
1935 -- Admin options
1936 SET SESSION AUTHORIZATION regress_priv_user4;
1937 CREATE FUNCTION dogrant_ok() RETURNS void LANGUAGE sql SECURITY DEFINER AS
1938 'GRANT regress_priv_group2 TO regress_priv_user5';
1939 GRANT regress_priv_group2 TO regress_priv_user5; -- ok: had ADMIN OPTION
1940 SET ROLE regress_priv_group2;
1941 GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE suspended privilege
1942 ERROR: permission denied to grant role "regress_priv_group2"
1943 DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
1944 SET SESSION AUTHORIZATION regress_priv_user1;
1945 GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no ADMIN OPTION
1946 ERROR: permission denied to grant role "regress_priv_group2"
1947 DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
1948 SELECT dogrant_ok(); -- ok: SECURITY DEFINER conveys ADMIN
1949 NOTICE: role "regress_priv_user5" has already been granted membership in role "regress_priv_group2" by role "regress_priv_user4"
1950 dogrant_ok
1951 ------------
1953 (1 row)
1955 SET ROLE regress_priv_group2;
1956 GRANT regress_priv_group2 TO regress_priv_user5; -- fails: SET ROLE did not help
1957 ERROR: permission denied to grant role "regress_priv_group2"
1958 DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
1959 SET SESSION AUTHORIZATION regress_priv_group2;
1960 GRANT regress_priv_group2 TO regress_priv_user5; -- fails: no self-admin
1961 ERROR: permission denied to grant role "regress_priv_group2"
1962 DETAIL: Only roles with the ADMIN option on role "regress_priv_group2" may grant this role.
1963 SET SESSION AUTHORIZATION regress_priv_user4;
1964 DROP FUNCTION dogrant_ok();
1965 REVOKE regress_priv_group2 FROM regress_priv_user5;
1966 -- has_sequence_privilege tests
1967 \c -
1968 CREATE SEQUENCE x_seq;
1969 GRANT USAGE on x_seq to regress_priv_user2;
1970 SELECT has_sequence_privilege('regress_priv_user1', 'atest1', 'SELECT');
1971 ERROR: "atest1" is not a sequence
1972 SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'INSERT');
1973 ERROR: unrecognized privilege type: "INSERT"
1974 SELECT has_sequence_privilege('regress_priv_user1', 'x_seq', 'SELECT');
1975 has_sequence_privilege
1976 ------------------------
1977 f
1978 (1 row)
1980 SET SESSION AUTHORIZATION regress_priv_user2;
1981 SELECT has_sequence_privilege('x_seq', 'USAGE');
1982 has_sequence_privilege
1983 ------------------------
1984 t
1985 (1 row)
1987 -- largeobject privilege tests
1988 \c -
1989 SET SESSION AUTHORIZATION regress_priv_user1;
1990 SELECT lo_create(1001);
1991 lo_create
1992 -----------
1993 1001
1994 (1 row)
1996 SELECT lo_create(1002);
1997 lo_create
1998 -----------
1999 1002
2000 (1 row)
2002 SELECT lo_create(1003);
2003 lo_create
2004 -----------
2005 1003
2006 (1 row)
2008 SELECT lo_create(1004);
2009 lo_create
2010 -----------
2011 1004
2012 (1 row)
2014 SELECT lo_create(1005);
2015 lo_create
2016 -----------
2017 1005
2018 (1 row)
2020 GRANT ALL ON LARGE OBJECT 1001 TO PUBLIC;
2021 GRANT SELECT ON LARGE OBJECT 1003 TO regress_priv_user2;
2022 GRANT SELECT,UPDATE ON LARGE OBJECT 1004 TO regress_priv_user2;
2023 GRANT ALL ON LARGE OBJECT 1005 TO regress_priv_user2;
2024 GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user2 WITH GRANT OPTION;
2025 GRANT SELECT, INSERT ON LARGE OBJECT 1001 TO PUBLIC; -- to be failed
2026 ERROR: invalid privilege type INSERT for large object
2027 GRANT SELECT, UPDATE ON LARGE OBJECT 1001 TO nosuchuser; -- to be failed
2028 ERROR: role "nosuchuser" does not exist
2029 GRANT SELECT, UPDATE ON LARGE OBJECT 999 TO PUBLIC; -- to be failed
2030 ERROR: large object 999 does not exist
2031 \c -
2032 SET SESSION AUTHORIZATION regress_priv_user2;
2033 SELECT lo_create(2001);
2034 lo_create
2035 -----------
2036 2001
2037 (1 row)
2039 SELECT lo_create(2002);
2040 lo_create
2041 -----------
2042 2002
2043 (1 row)
2045 SELECT loread(lo_open(1001, x'20000'::int), 32); -- allowed, for now
2046 loread
2047 --------
2048 \x
2049 (1 row)
2051 SELECT lowrite(lo_open(1001, x'40000'::int), 'abcd'); -- fail, wrong mode
2052 ERROR: large object descriptor 0 was not opened for writing
2053 SELECT loread(lo_open(1001, x'40000'::int), 32);
2054 loread
2055 --------
2056 \x
2057 (1 row)
2059 SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied
2060 ERROR: permission denied for large object 1002
2061 SELECT loread(lo_open(1003, x'40000'::int), 32);
2062 loread
2063 --------
2064 \x
2065 (1 row)
2067 SELECT loread(lo_open(1004, x'40000'::int), 32);
2068 loread
2069 --------
2070 \x
2071 (1 row)
2073 SELECT lowrite(lo_open(1001, x'20000'::int), 'abcd');
2074 lowrite
2075 ---------
2076 4
2077 (1 row)
2079 SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied
2080 ERROR: permission denied for large object 1002
2081 SELECT lowrite(lo_open(1003, x'20000'::int), 'abcd'); -- to be denied
2082 ERROR: permission denied for large object 1003
2083 SELECT lowrite(lo_open(1004, x'20000'::int), 'abcd');
2084 lowrite
2085 ---------
2086 4
2087 (1 row)
2089 GRANT SELECT ON LARGE OBJECT 1005 TO regress_priv_user3;
2090 GRANT UPDATE ON LARGE OBJECT 1006 TO regress_priv_user3; -- to be denied
2091 ERROR: large object 1006 does not exist
2092 REVOKE ALL ON LARGE OBJECT 2001, 2002 FROM PUBLIC;
2093 GRANT ALL ON LARGE OBJECT 2001 TO regress_priv_user3;
2094 SELECT lo_unlink(1001); -- to be denied
2095 ERROR: must be owner of large object 1001
2096 SELECT lo_unlink(2002);
2097 lo_unlink
2098 -----------
2099 1
2100 (1 row)
2102 \c -
2103 -- confirm ACL setting
2104 SELECT oid, pg_get_userbyid(lomowner) ownername, lomacl FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
2105 oid | ownername | lomacl
2106 ------+--------------------+------------------------------------------------------------------------------------------------------------------------------
2107 1001 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,=rw/regress_priv_user1}
2108 1002 | regress_priv_user1 |
2109 1003 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r/regress_priv_user1}
2110 1004 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=rw/regress_priv_user1}
2111 1005 | regress_priv_user1 | {regress_priv_user1=rw/regress_priv_user1,regress_priv_user2=r*w/regress_priv_user1,regress_priv_user3=r/regress_priv_user2}
2112 2001 | regress_priv_user2 | {regress_priv_user2=rw/regress_priv_user2,regress_priv_user3=rw/regress_priv_user2}
2113 (6 rows)
2115 SET SESSION AUTHORIZATION regress_priv_user3;
2116 SELECT loread(lo_open(1001, x'40000'::int), 32);
2117 loread
2118 ------------
2119 \x61626364
2120 (1 row)
2122 SELECT loread(lo_open(1003, x'40000'::int), 32); -- to be denied
2123 ERROR: permission denied for large object 1003
2124 SELECT loread(lo_open(1005, x'40000'::int), 32);
2125 loread
2126 --------
2127 \x
2128 (1 row)
2130 SELECT lo_truncate(lo_open(1005, x'20000'::int), 10); -- to be denied
2131 ERROR: permission denied for large object 1005
2132 SELECT lo_truncate(lo_open(2001, x'20000'::int), 10);
2133 lo_truncate
2134 -------------
2135 0
2136 (1 row)
2138 -- has_largeobject_privilege function
2139 -- superuser
2140 \c -
2141 SELECT has_largeobject_privilege(1001, 'SELECT');
2142 has_largeobject_privilege
2143 ---------------------------
2144 t
2145 (1 row)
2147 SELECT has_largeobject_privilege(1002, 'SELECT');
2148 has_largeobject_privilege
2149 ---------------------------
2150 t
2151 (1 row)
2153 SELECT has_largeobject_privilege(1003, 'SELECT');
2154 has_largeobject_privilege
2155 ---------------------------
2156 t
2157 (1 row)
2159 SELECT has_largeobject_privilege(1004, 'SELECT');
2160 has_largeobject_privilege
2161 ---------------------------
2162 t
2163 (1 row)
2165 SELECT has_largeobject_privilege(1001, 'UPDATE');
2166 has_largeobject_privilege
2167 ---------------------------
2168 t
2169 (1 row)
2171 SELECT has_largeobject_privilege(1002, 'UPDATE');
2172 has_largeobject_privilege
2173 ---------------------------
2174 t
2175 (1 row)
2177 SELECT has_largeobject_privilege(1003, 'UPDATE');
2178 has_largeobject_privilege
2179 ---------------------------
2180 t
2181 (1 row)
2183 SELECT has_largeobject_privilege(1004, 'UPDATE');
2184 has_largeobject_privilege
2185 ---------------------------
2186 t
2187 (1 row)
2189 -- not-existing large object
2190 SELECT has_largeobject_privilege(9999, 'SELECT'); -- NULL
2191 has_largeobject_privilege
2192 ---------------------------
2194 (1 row)
2196 -- non-superuser
2197 SET SESSION AUTHORIZATION regress_priv_user2;
2198 SELECT has_largeobject_privilege(1001, 'SELECT');
2199 has_largeobject_privilege
2200 ---------------------------
2201 t
2202 (1 row)
2204 SELECT has_largeobject_privilege(1002, 'SELECT'); -- false
2205 has_largeobject_privilege
2206 ---------------------------
2207 f
2208 (1 row)
2210 SELECT has_largeobject_privilege(1003, 'SELECT');
2211 has_largeobject_privilege
2212 ---------------------------
2213 t
2214 (1 row)
2216 SELECT has_largeobject_privilege(1004, 'SELECT');
2217 has_largeobject_privilege
2218 ---------------------------
2219 t
2220 (1 row)
2222 SELECT has_largeobject_privilege(1001, 'UPDATE');
2223 has_largeobject_privilege
2224 ---------------------------
2225 t
2226 (1 row)
2228 SELECT has_largeobject_privilege(1002, 'UPDATE'); -- false
2229 has_largeobject_privilege
2230 ---------------------------
2231 f
2232 (1 row)
2234 SELECT has_largeobject_privilege(1003, 'UPDATE'); -- false
2235 has_largeobject_privilege
2236 ---------------------------
2237 f
2238 (1 row)
2240 SELECT has_largeobject_privilege(1004, 'UPDATE');
2241 has_largeobject_privilege
2242 ---------------------------
2243 t
2244 (1 row)
2246 SELECT has_largeobject_privilege('regress_priv_user3', 1001, 'SELECT');
2247 has_largeobject_privilege
2248 ---------------------------
2249 t
2250 (1 row)
2252 SELECT has_largeobject_privilege('regress_priv_user3', 1003, 'SELECT'); -- false
2253 has_largeobject_privilege
2254 ---------------------------
2255 f
2256 (1 row)
2258 SELECT has_largeobject_privilege('regress_priv_user3', 1005, 'SELECT');
2259 has_largeobject_privilege
2260 ---------------------------
2261 t
2262 (1 row)
2264 SELECT has_largeobject_privilege('regress_priv_user3', 1005, 'UPDATE'); -- false
2265 has_largeobject_privilege
2266 ---------------------------
2267 f
2268 (1 row)
2270 SELECT has_largeobject_privilege('regress_priv_user3', 2001, 'UPDATE');
2271 has_largeobject_privilege
2272 ---------------------------
2273 t
2274 (1 row)
2276 -- compatibility mode in largeobject permission
2277 \c -
2278 SET lo_compat_privileges = false; -- default setting
2279 SET SESSION AUTHORIZATION regress_priv_user4;
2280 SELECT has_largeobject_privilege(1002, 'SELECT'); -- false
2281 has_largeobject_privilege
2282 ---------------------------
2283 f
2284 (1 row)
2286 SELECT has_largeobject_privilege(1002, 'UPDATE'); -- false
2287 has_largeobject_privilege
2288 ---------------------------
2289 f
2290 (1 row)
2292 SELECT loread(lo_open(1002, x'40000'::int), 32); -- to be denied
2293 ERROR: permission denied for large object 1002
2294 SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd'); -- to be denied
2295 ERROR: permission denied for large object 1002
2296 SELECT lo_truncate(lo_open(1002, x'20000'::int), 10); -- to be denied
2297 ERROR: permission denied for large object 1002
2298 SELECT lo_put(1002, 1, 'abcd'); -- to be denied
2299 ERROR: permission denied for large object 1002
2300 SELECT lo_unlink(1002); -- to be denied
2301 ERROR: must be owner of large object 1002
2302 SELECT lo_export(1001, '/dev/null'); -- to be denied
2303 ERROR: permission denied for function lo_export
2304 SELECT lo_import('/dev/null'); -- to be denied
2305 ERROR: permission denied for function lo_import
2306 SELECT lo_import('/dev/null', 2003); -- to be denied
2307 ERROR: permission denied for function lo_import
2308 \c -
2309 SET lo_compat_privileges = true; -- compatibility mode
2310 SET SESSION AUTHORIZATION regress_priv_user4;
2311 SELECT has_largeobject_privilege(1002, 'SELECT'); -- true
2312 has_largeobject_privilege
2313 ---------------------------
2314 t
2315 (1 row)
2317 SELECT has_largeobject_privilege(1002, 'UPDATE'); -- true
2318 has_largeobject_privilege
2319 ---------------------------
2320 t
2321 (1 row)
2323 SELECT loread(lo_open(1002, x'40000'::int), 32);
2324 loread
2325 --------
2326 \x
2327 (1 row)
2329 SELECT lowrite(lo_open(1002, x'20000'::int), 'abcd');
2330 lowrite
2331 ---------
2332 4
2333 (1 row)
2335 SELECT lo_truncate(lo_open(1002, x'20000'::int), 10);
2336 lo_truncate
2337 -------------
2338 0
2339 (1 row)
2341 SELECT lo_unlink(1002);
2342 lo_unlink
2343 -----------
2344 1
2345 (1 row)
2347 SELECT lo_export(1001, '/dev/null'); -- to be denied
2348 ERROR: permission denied for function lo_export
2349 -- don't allow unpriv users to access pg_largeobject contents
2350 \c -
2351 SELECT * FROM pg_largeobject LIMIT 0;
2352 loid | pageno | data
2353 ------+--------+------
2354 (0 rows)
2356 SET SESSION AUTHORIZATION regress_priv_user1;
2357 SELECT * FROM pg_largeobject LIMIT 0; -- to be denied
2358 ERROR: permission denied for table pg_largeobject
2359 -- pg_signal_backend can't signal superusers
2360 RESET SESSION AUTHORIZATION;
2361 BEGIN;
2362 CREATE OR REPLACE FUNCTION terminate_nothrow(pid int) RETURNS bool
2363 LANGUAGE plpgsql SECURITY DEFINER SET client_min_messages = error AS $$
2364 BEGIN
2365 RETURN pg_terminate_backend($1);
2366 EXCEPTION WHEN OTHERS THEN
2367 RETURN false;
2368 END$$;
2369 ALTER FUNCTION terminate_nothrow OWNER TO pg_signal_backend;
2370 SELECT backend_type FROM pg_stat_activity
2371 WHERE CASE WHEN COALESCE(usesysid, 10) = 10 THEN terminate_nothrow(pid) END;
2372 backend_type
2373 --------------
2374 (0 rows)
2376 ROLLBACK;
2377 -- test pg_database_owner
2378 RESET SESSION AUTHORIZATION;
2379 GRANT pg_database_owner TO regress_priv_user1;
2380 ERROR: role "pg_database_owner" cannot have explicit members
2381 GRANT regress_priv_user1 TO pg_database_owner;
2382 ERROR: role "pg_database_owner" cannot be a member of any role
2383 CREATE TABLE datdba_only ();
2384 ALTER TABLE datdba_only OWNER TO pg_database_owner;
2385 REVOKE DELETE ON datdba_only FROM pg_database_owner;
2386 SELECT
2387 pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
2388 pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
2389 pg_has_role('regress_priv_user1', 'pg_database_owner',
2390 'MEMBER WITH ADMIN OPTION') as admin;
2391 priv | mem | admin
2392 ------+-----+-------
2393 f | f | f
2394 (1 row)
2396 BEGIN;
2397 DO $$BEGIN EXECUTE format(
2398 'ALTER DATABASE %I OWNER TO regress_priv_group2', current_catalog); END$$;
2399 SELECT
2400 pg_has_role('regress_priv_user1', 'pg_database_owner', 'USAGE') as priv,
2401 pg_has_role('regress_priv_user1', 'pg_database_owner', 'MEMBER') as mem,
2402 pg_has_role('regress_priv_user1', 'pg_database_owner',
2403 'MEMBER WITH ADMIN OPTION') as admin;
2404 priv | mem | admin
2405 ------+-----+-------
2406 t | t | f
2407 (1 row)
2409 SET SESSION AUTHORIZATION regress_priv_user1;
2410 TABLE information_schema.enabled_roles ORDER BY role_name COLLATE "C";
2411 role_name
2412 ---------------------
2413 pg_database_owner
2414 regress_priv_group2
2415 regress_priv_user1
2416 (3 rows)
2418 TABLE information_schema.applicable_roles ORDER BY role_name COLLATE "C";
2419 grantee | role_name | is_grantable
2420 ---------------------+---------------------+--------------
2421 regress_priv_group2 | pg_database_owner | NO
2422 regress_priv_user1 | regress_priv_group2 | NO
2423 (2 rows)
2425 INSERT INTO datdba_only DEFAULT VALUES;
2426 SAVEPOINT q; DELETE FROM datdba_only; ROLLBACK TO q;
2427 ERROR: permission denied for table datdba_only
2428 SET SESSION AUTHORIZATION regress_priv_user2;
2429 TABLE information_schema.enabled_roles;
2430 role_name
2431 --------------------
2432 regress_priv_user2
2433 (1 row)
2435 INSERT INTO datdba_only DEFAULT VALUES;
2436 ERROR: permission denied for table datdba_only
2437 ROLLBACK;
2438 -- test default ACLs
2439 \c -
2440 CREATE SCHEMA testns;
2441 GRANT ALL ON SCHEMA testns TO regress_priv_user1;
2442 CREATE TABLE testns.acltest1 (x int);
2443 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no
2444 has_table_privilege
2445 ---------------------
2446 f
2447 (1 row)
2449 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
2450 has_table_privilege
2451 ---------------------
2452 f
2453 (1 row)
2455 -- placeholder for test with duplicated schema and role names
2456 ALTER DEFAULT PRIVILEGES IN SCHEMA testns,testns GRANT SELECT ON TABLES TO public,public;
2457 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- no
2458 has_table_privilege
2459 ---------------------
2460 f
2461 (1 row)
2463 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
2464 has_table_privilege
2465 ---------------------
2466 f
2467 (1 row)
2469 DROP TABLE testns.acltest1;
2470 CREATE TABLE testns.acltest1 (x int);
2471 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes
2472 has_table_privilege
2473 ---------------------
2474 t
2475 (1 row)
2477 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
2478 has_table_privilege
2479 ---------------------
2480 f
2481 (1 row)
2483 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT INSERT ON TABLES TO regress_priv_user1;
2484 DROP TABLE testns.acltest1;
2485 CREATE TABLE testns.acltest1 (x int);
2486 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes
2487 has_table_privilege
2488 ---------------------
2489 t
2490 (1 row)
2492 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- yes
2493 has_table_privilege
2494 ---------------------
2495 t
2496 (1 row)
2498 ALTER DEFAULT PRIVILEGES IN SCHEMA testns REVOKE INSERT ON TABLES FROM regress_priv_user1;
2499 DROP TABLE testns.acltest1;
2500 CREATE TABLE testns.acltest1 (x int);
2501 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'SELECT'); -- yes
2502 has_table_privilege
2503 ---------------------
2504 t
2505 (1 row)
2507 SELECT has_table_privilege('regress_priv_user1', 'testns.acltest1', 'INSERT'); -- no
2508 has_table_privilege
2509 ---------------------
2510 f
2511 (1 row)
2513 ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE EXECUTE ON FUNCTIONS FROM public;
2514 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON SCHEMAS TO regress_priv_user2; -- error
2515 ERROR: cannot use IN SCHEMA clause when using GRANT/REVOKE ON SCHEMAS
2516 -- Test makeaclitem()
2517 SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole,
2518 'SELECT', TRUE); -- single privilege
2519 makeaclitem
2520 ------------------------------------------
2521 regress_priv_user1=r*/regress_priv_user2
2522 (1 row)
2524 SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole,
2525 'SELECT, INSERT, UPDATE , DELETE ', FALSE); -- multiple privileges
2526 makeaclitem
2527 --------------------------------------------
2528 regress_priv_user1=arwd/regress_priv_user2
2529 (1 row)
2531 SELECT makeaclitem('regress_priv_user1'::regrole, 'regress_priv_user2'::regrole,
2532 'SELECT, fake_privilege', FALSE); -- error
2533 ERROR: unrecognized privilege type: "fake_privilege"
2534 -- Test non-throwing aclitem I/O
2535 SELECT pg_input_is_valid('regress_priv_user1=r/regress_priv_user2', 'aclitem');
2536 pg_input_is_valid
2537 -------------------
2538 t
2539 (1 row)
2541 SELECT pg_input_is_valid('regress_priv_user1=r/', 'aclitem');
2542 pg_input_is_valid
2543 -------------------
2544 f
2545 (1 row)
2547 SELECT * FROM pg_input_error_info('regress_priv_user1=r/', 'aclitem');
2548 message | detail | hint | sql_error_code
2549 ---------------------------------+--------+------+----------------
2550 a name must follow the "/" sign | | | 22P02
2551 (1 row)
2553 SELECT pg_input_is_valid('regress_priv_user1=r/regress_no_such_user', 'aclitem');
2554 pg_input_is_valid
2555 -------------------
2556 f
2557 (1 row)
2559 SELECT * FROM pg_input_error_info('regress_priv_user1=r/regress_no_such_user', 'aclitem');
2560 message | detail | hint | sql_error_code
2561 --------------------------------------------+--------+------+----------------
2562 role "regress_no_such_user" does not exist | | | 42704
2563 (1 row)
2565 SELECT pg_input_is_valid('regress_priv_user1=rY', 'aclitem');
2566 pg_input_is_valid
2567 -------------------
2568 f
2569 (1 row)
2571 SELECT * FROM pg_input_error_info('regress_priv_user1=rY', 'aclitem');
2572 message | detail | hint | sql_error_code
2573 ----------------------------------------------------------+--------+------+----------------
2574 invalid mode character: must be one of "arwdDxtXUCTcsAm" | | | 22P02
2575 (1 row)
2577 --
2578 -- Testing blanket default grants is very hazardous since it might change
2579 -- the privileges attached to objects created by concurrent regression tests.
2580 -- To avoid that, be sure to revoke the privileges again before committing.
2581 --
2582 BEGIN;
2583 ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO regress_priv_user2;
2584 CREATE SCHEMA testns2;
2585 SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'USAGE'); -- yes
2586 has_schema_privilege
2587 ----------------------
2588 t
2589 (1 row)
2591 SELECT has_schema_privilege('regress_priv_user6', 'testns2', 'USAGE'); -- yes
2592 has_schema_privilege
2593 ----------------------
2594 t
2595 (1 row)
2597 SELECT has_schema_privilege('regress_priv_user2', 'testns2', 'CREATE'); -- no
2598 has_schema_privilege
2599 ----------------------
2600 f
2601 (1 row)
2603 ALTER DEFAULT PRIVILEGES REVOKE USAGE ON SCHEMAS FROM regress_priv_user2;
2604 CREATE SCHEMA testns3;
2605 SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'USAGE'); -- no
2606 has_schema_privilege
2607 ----------------------
2608 f
2609 (1 row)
2611 SELECT has_schema_privilege('regress_priv_user2', 'testns3', 'CREATE'); -- no
2612 has_schema_privilege
2613 ----------------------
2614 f
2615 (1 row)
2617 ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2;
2618 CREATE SCHEMA testns4;
2619 SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'USAGE'); -- yes
2620 has_schema_privilege
2621 ----------------------
2622 t
2623 (1 row)
2625 SELECT has_schema_privilege('regress_priv_user2', 'testns4', 'CREATE'); -- yes
2626 has_schema_privilege
2627 ----------------------
2628 t
2629 (1 row)
2631 ALTER DEFAULT PRIVILEGES REVOKE ALL ON SCHEMAS FROM regress_priv_user2;
2632 COMMIT;
2633 -- Test for DROP OWNED BY with shared dependencies. This is done in a
2634 -- separate, rollbacked, transaction to avoid any trouble with other
2635 -- regression sessions.
2636 BEGIN;
2637 ALTER DEFAULT PRIVILEGES GRANT ALL ON FUNCTIONS TO regress_priv_user2;
2638 ALTER DEFAULT PRIVILEGES GRANT ALL ON SCHEMAS TO regress_priv_user2;
2639 ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO regress_priv_user2;
2640 ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO regress_priv_user2;
2641 ALTER DEFAULT PRIVILEGES GRANT ALL ON TYPES TO regress_priv_user2;
2642 SELECT count(*) FROM pg_shdepend
2643 WHERE deptype = 'a' AND
2644 refobjid = 'regress_priv_user2'::regrole AND
2645 classid = 'pg_default_acl'::regclass;
2646 count
2647 -------
2648 5
2649 (1 row)
2651 DROP OWNED BY regress_priv_user2, regress_priv_user2;
2652 SELECT count(*) FROM pg_shdepend
2653 WHERE deptype = 'a' AND
2654 refobjid = 'regress_priv_user2'::regrole AND
2655 classid = 'pg_default_acl'::regclass;
2656 count
2657 -------
2658 0
2659 (1 row)
2661 ROLLBACK;
2662 CREATE SCHEMA testns5;
2663 SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'USAGE'); -- no
2664 has_schema_privilege
2665 ----------------------
2666 f
2667 (1 row)
2669 SELECT has_schema_privilege('regress_priv_user2', 'testns5', 'CREATE'); -- no
2670 has_schema_privilege
2671 ----------------------
2672 f
2673 (1 row)
2675 SET ROLE regress_priv_user1;
2676 CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
2677 CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
2678 CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
2679 SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- no
2680 has_function_privilege
2681 ------------------------
2682 f
2683 (1 row)
2685 SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- no
2686 has_function_privilege
2687 ------------------------
2688 f
2689 (1 row)
2691 SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- no
2692 has_function_privilege
2693 ------------------------
2694 f
2695 (1 row)
2697 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT EXECUTE ON ROUTINES to public;
2698 DROP FUNCTION testns.foo();
2699 CREATE FUNCTION testns.foo() RETURNS int AS 'select 1' LANGUAGE sql;
2700 DROP AGGREGATE testns.agg1(int);
2701 CREATE AGGREGATE testns.agg1(int) (sfunc = int4pl, stype = int4);
2702 DROP PROCEDURE testns.bar();
2703 CREATE PROCEDURE testns.bar() AS 'select 1' LANGUAGE sql;
2704 SELECT has_function_privilege('regress_priv_user2', 'testns.foo()', 'EXECUTE'); -- yes
2705 has_function_privilege
2706 ------------------------
2707 t
2708 (1 row)
2710 SELECT has_function_privilege('regress_priv_user2', 'testns.agg1(int)', 'EXECUTE'); -- yes
2711 has_function_privilege
2712 ------------------------
2713 t
2714 (1 row)
2716 SELECT has_function_privilege('regress_priv_user2', 'testns.bar()', 'EXECUTE'); -- yes (counts as function here)
2717 has_function_privilege
2718 ------------------------
2719 t
2720 (1 row)
2722 DROP FUNCTION testns.foo();
2723 DROP AGGREGATE testns.agg1(int);
2724 DROP PROCEDURE testns.bar();
2725 ALTER DEFAULT PRIVILEGES FOR ROLE regress_priv_user1 REVOKE USAGE ON TYPES FROM public;
2726 CREATE DOMAIN testns.priv_testdomain1 AS int;
2727 SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- no
2728 has_type_privilege
2729 --------------------
2730 f
2731 (1 row)
2733 ALTER DEFAULT PRIVILEGES IN SCHEMA testns GRANT USAGE ON TYPES to public;
2734 DROP DOMAIN testns.priv_testdomain1;
2735 CREATE DOMAIN testns.priv_testdomain1 AS int;
2736 SELECT has_type_privilege('regress_priv_user2', 'testns.priv_testdomain1', 'USAGE'); -- yes
2737 has_type_privilege
2738 --------------------
2739 t
2740 (1 row)
2742 DROP DOMAIN testns.priv_testdomain1;
2743 RESET ROLE;
2744 SELECT count(*)
2745 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
2746 WHERE nspname = 'testns';
2747 count
2748 -------
2749 3
2750 (1 row)
2752 DROP SCHEMA testns CASCADE;
2753 NOTICE: drop cascades to table testns.acltest1
2754 DROP SCHEMA testns2 CASCADE;
2755 DROP SCHEMA testns3 CASCADE;
2756 DROP SCHEMA testns4 CASCADE;
2757 DROP SCHEMA testns5 CASCADE;
2758 SELECT d.* -- check that entries went away
2759 FROM pg_default_acl d LEFT JOIN pg_namespace n ON defaclnamespace = n.oid
2760 WHERE nspname IS NULL AND defaclnamespace != 0;
2761 oid | defaclrole | defaclnamespace | defaclobjtype | defaclacl
2762 -----+------------+-----------------+---------------+-----------
2763 (0 rows)
2765 -- Grant on all objects of given type in a schema
2766 \c -
2767 CREATE SCHEMA testns;
2768 CREATE TABLE testns.t1 (f1 int);
2769 CREATE TABLE testns.t2 (f1 int);
2770 SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false
2771 has_table_privilege
2772 ---------------------
2773 f
2774 (1 row)
2776 GRANT ALL ON ALL TABLES IN SCHEMA testns TO regress_priv_user1;
2777 SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- true
2778 has_table_privilege
2779 ---------------------
2780 t
2781 (1 row)
2783 SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- true
2784 has_table_privilege
2785 ---------------------
2786 t
2787 (1 row)
2789 REVOKE ALL ON ALL TABLES IN SCHEMA testns FROM regress_priv_user1;
2790 SELECT has_table_privilege('regress_priv_user1', 'testns.t1', 'SELECT'); -- false
2791 has_table_privilege
2792 ---------------------
2793 f
2794 (1 row)
2796 SELECT has_table_privilege('regress_priv_user1', 'testns.t2', 'SELECT'); -- false
2797 has_table_privilege
2798 ---------------------
2799 f
2800 (1 row)
2802 CREATE FUNCTION testns.priv_testfunc(int) RETURNS int AS 'select 3 * $1;' LANGUAGE sql;
2803 CREATE AGGREGATE testns.priv_testagg(int) (sfunc = int4pl, stype = int4);
2804 CREATE PROCEDURE testns.priv_testproc(int) AS 'select 3' LANGUAGE sql;
2805 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true by default
2806 has_function_privilege
2807 ------------------------
2808 t
2809 (1 row)
2811 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true by default
2812 has_function_privilege
2813 ------------------------
2814 t
2815 (1 row)
2817 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true by default
2818 has_function_privilege
2819 ------------------------
2820 t
2821 (1 row)
2823 REVOKE ALL ON ALL FUNCTIONS IN SCHEMA testns FROM PUBLIC;
2824 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- false
2825 has_function_privilege
2826 ------------------------
2827 f
2828 (1 row)
2830 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- false
2831 has_function_privilege
2832 ------------------------
2833 f
2834 (1 row)
2836 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- still true, not a function
2837 has_function_privilege
2838 ------------------------
2839 t
2840 (1 row)
2842 REVOKE ALL ON ALL PROCEDURES IN SCHEMA testns FROM PUBLIC;
2843 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- now false
2844 has_function_privilege
2845 ------------------------
2846 f
2847 (1 row)
2849 GRANT ALL ON ALL ROUTINES IN SCHEMA testns TO PUBLIC;
2850 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testfunc(int)', 'EXECUTE'); -- true
2851 has_function_privilege
2852 ------------------------
2853 t
2854 (1 row)
2856 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testagg(int)', 'EXECUTE'); -- true
2857 has_function_privilege
2858 ------------------------
2859 t
2860 (1 row)
2862 SELECT has_function_privilege('regress_priv_user1', 'testns.priv_testproc(int)', 'EXECUTE'); -- true
2863 has_function_privilege
2864 ------------------------
2865 t
2866 (1 row)
2868 DROP SCHEMA testns CASCADE;
2869 NOTICE: drop cascades to 5 other objects
2870 DETAIL: drop cascades to table testns.t1
2871 drop cascades to table testns.t2
2872 drop cascades to function testns.priv_testfunc(integer)
2873 drop cascades to function testns.priv_testagg(integer)
2874 drop cascades to function testns.priv_testproc(integer)
2875 -- Change owner of the schema & and rename of new schema owner
2876 \c -
2877 CREATE ROLE regress_schemauser1 superuser login;
2878 CREATE ROLE regress_schemauser2 superuser login;
2879 SET SESSION ROLE regress_schemauser1;
2880 CREATE SCHEMA testns;
2881 SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
2882 nspname | rolname
2883 ---------+---------------------
2884 testns | regress_schemauser1
2885 (1 row)
2887 ALTER SCHEMA testns OWNER TO regress_schemauser2;
2888 ALTER ROLE regress_schemauser2 RENAME TO regress_schemauser_renamed;
2889 SELECT nspname, rolname FROM pg_namespace, pg_roles WHERE pg_namespace.nspname = 'testns' AND pg_namespace.nspowner = pg_roles.oid;
2890 nspname | rolname
2891 ---------+----------------------------
2892 testns | regress_schemauser_renamed
2893 (1 row)
2895 set session role regress_schemauser_renamed;
2896 DROP SCHEMA testns CASCADE;
2897 -- clean up
2898 \c -
2899 DROP ROLE regress_schemauser1;
2900 DROP ROLE regress_schemauser_renamed;
2901 -- test that dependent privileges are revoked (or not) properly
2902 \c -
2903 set session role regress_priv_user1;
2904 create table dep_priv_test (a int);
2905 grant select on dep_priv_test to regress_priv_user2 with grant option;
2906 grant select on dep_priv_test to regress_priv_user3 with grant option;
2907 set session role regress_priv_user2;
2908 grant select on dep_priv_test to regress_priv_user4 with grant option;
2909 set session role regress_priv_user3;
2910 grant select on dep_priv_test to regress_priv_user4 with grant option;
2911 set session role regress_priv_user4;
2912 grant select on dep_priv_test to regress_priv_user5;
2913 \dp dep_priv_test
2914 Access privileges
2915 Schema | Name | Type | Access privileges | Column privileges | Policies
2916 --------+---------------+-------+------------------------------------------------+-------------------+----------
2917 public | dep_priv_test | table | regress_priv_user1=arwdDxtm/regress_priv_user1+| |
2918 | | | regress_priv_user2=r*/regress_priv_user1 +| |
2919 | | | regress_priv_user3=r*/regress_priv_user1 +| |
2920 | | | regress_priv_user4=r*/regress_priv_user2 +| |
2921 | | | regress_priv_user4=r*/regress_priv_user3 +| |
2922 | | | regress_priv_user5=r/regress_priv_user4 | |
2923 (1 row)
2925 set session role regress_priv_user2;
2926 revoke select on dep_priv_test from regress_priv_user4 cascade;
2927 \dp dep_priv_test
2928 Access privileges
2929 Schema | Name | Type | Access privileges | Column privileges | Policies
2930 --------+---------------+-------+------------------------------------------------+-------------------+----------
2931 public | dep_priv_test | table | regress_priv_user1=arwdDxtm/regress_priv_user1+| |
2932 | | | regress_priv_user2=r*/regress_priv_user1 +| |
2933 | | | regress_priv_user3=r*/regress_priv_user1 +| |
2934 | | | regress_priv_user4=r*/regress_priv_user3 +| |
2935 | | | regress_priv_user5=r/regress_priv_user4 | |
2936 (1 row)
2938 set session role regress_priv_user3;
2939 revoke select on dep_priv_test from regress_priv_user4 cascade;
2940 \dp dep_priv_test
2941 Access privileges
2942 Schema | Name | Type | Access privileges | Column privileges | Policies
2943 --------+---------------+-------+------------------------------------------------+-------------------+----------
2944 public | dep_priv_test | table | regress_priv_user1=arwdDxtm/regress_priv_user1+| |
2945 | | | regress_priv_user2=r*/regress_priv_user1 +| |
2946 | | | regress_priv_user3=r*/regress_priv_user1 | |
2947 (1 row)
2949 set session role regress_priv_user1;
2950 drop table dep_priv_test;
2951 -- clean up
2952 \c
2953 drop sequence x_seq;
2954 DROP AGGREGATE priv_testagg1(int);
2955 DROP FUNCTION priv_testfunc2(int);
2956 DROP FUNCTION priv_testfunc4(boolean);
2957 DROP PROCEDURE priv_testproc1(int);
2958 DROP VIEW atestv0;
2959 DROP VIEW atestv1;
2960 DROP VIEW atestv2;
2961 -- this should cascade to drop atestv4
2962 DROP VIEW atestv3 CASCADE;
2963 NOTICE: drop cascades to view atestv4
2964 -- this should complain "does not exist"
2965 DROP VIEW atestv4;
2966 ERROR: view "atestv4" does not exist
2967 DROP TABLE atest1;
2968 DROP TABLE atest2;
2969 DROP TABLE atest3;
2970 DROP TABLE atest4;
2971 DROP TABLE atest5;
2972 DROP TABLE atest6;
2973 DROP TABLE atestc;
2974 DROP TABLE atestp1;
2975 DROP TABLE atestp2;
2976 SELECT lo_unlink(oid) FROM pg_largeobject_metadata WHERE oid >= 1000 AND oid < 3000 ORDER BY oid;
2977 lo_unlink
2978 -----------
2979 1
2980 1
2981 1
2982 1
2983 1
2984 (5 rows)
2986 DROP GROUP regress_priv_group1;
2987 DROP GROUP regress_priv_group2;
2988 -- these are needed to clean up permissions
2989 REVOKE USAGE ON LANGUAGE sql FROM regress_priv_user1;
2990 DROP OWNED BY regress_priv_user1;
2991 DROP USER regress_priv_user1;
2992 DROP USER regress_priv_user2;
2993 DROP USER regress_priv_user3;
2994 DROP USER regress_priv_user4;
2995 DROP USER regress_priv_user5;
2996 DROP USER regress_priv_user6;
2997 DROP USER regress_priv_user7;
2998 DROP USER regress_priv_user8; -- does not exist
2999 ERROR: role "regress_priv_user8" does not exist
3000 -- permissions with LOCK TABLE
3001 CREATE USER regress_locktable_user;
3002 CREATE TABLE lock_table (a int);
3003 -- LOCK TABLE and SELECT permission
3004 GRANT SELECT ON lock_table TO regress_locktable_user;
3005 SET SESSION AUTHORIZATION regress_locktable_user;
3006 BEGIN;
3007 LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
3008 COMMIT;
3009 BEGIN;
3010 LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should fail
3011 ERROR: permission denied for table lock_table
3012 ROLLBACK;
3013 BEGIN;
3014 LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
3015 ERROR: permission denied for table lock_table
3016 ROLLBACK;
3017 \c
3018 REVOKE SELECT ON lock_table FROM regress_locktable_user;
3019 -- LOCK TABLE and INSERT permission
3020 GRANT INSERT ON lock_table TO regress_locktable_user;
3021 SET SESSION AUTHORIZATION regress_locktable_user;
3022 BEGIN;
3023 LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
3024 ROLLBACK;
3025 BEGIN;
3026 LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
3027 COMMIT;
3028 BEGIN;
3029 LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should fail
3030 ERROR: permission denied for table lock_table
3031 ROLLBACK;
3032 \c
3033 REVOKE INSERT ON lock_table FROM regress_locktable_user;
3034 -- LOCK TABLE and UPDATE permission
3035 GRANT UPDATE ON lock_table TO regress_locktable_user;
3036 SET SESSION AUTHORIZATION regress_locktable_user;
3037 BEGIN;
3038 LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
3039 ROLLBACK;
3040 BEGIN;
3041 LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
3042 COMMIT;
3043 BEGIN;
3044 LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
3045 COMMIT;
3046 \c
3047 REVOKE UPDATE ON lock_table FROM regress_locktable_user;
3048 -- LOCK TABLE and DELETE permission
3049 GRANT DELETE ON lock_table TO regress_locktable_user;
3050 SET SESSION AUTHORIZATION regress_locktable_user;
3051 BEGIN;
3052 LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
3053 ROLLBACK;
3054 BEGIN;
3055 LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
3056 COMMIT;
3057 BEGIN;
3058 LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
3059 COMMIT;
3060 \c
3061 REVOKE DELETE ON lock_table FROM regress_locktable_user;
3062 -- LOCK TABLE and TRUNCATE permission
3063 GRANT TRUNCATE ON lock_table TO regress_locktable_user;
3064 SET SESSION AUTHORIZATION regress_locktable_user;
3065 BEGIN;
3066 LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
3067 ROLLBACK;
3068 BEGIN;
3069 LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
3070 COMMIT;
3071 BEGIN;
3072 LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
3073 COMMIT;
3074 \c
3075 REVOKE TRUNCATE ON lock_table FROM regress_locktable_user;
3076 -- LOCK TABLE and MAINTAIN permission
3077 GRANT MAINTAIN ON lock_table TO regress_locktable_user;
3078 SET SESSION AUTHORIZATION regress_locktable_user;
3079 BEGIN;
3080 LOCK TABLE lock_table IN ACCESS SHARE MODE; -- should pass
3081 ROLLBACK;
3082 BEGIN;
3083 LOCK TABLE lock_table IN ROW EXCLUSIVE MODE; -- should pass
3084 COMMIT;
3085 BEGIN;
3086 LOCK TABLE lock_table IN ACCESS EXCLUSIVE MODE; -- should pass
3087 COMMIT;
3088 \c
3089 REVOKE MAINTAIN ON lock_table FROM regress_locktable_user;
3090 -- clean up
3091 DROP TABLE lock_table;
3092 DROP USER regress_locktable_user;
3093 -- test to check privileges of system views pg_shmem_allocations and
3094 -- pg_backend_memory_contexts.
3095 -- switch to superuser
3096 \c -
3097 CREATE ROLE regress_readallstats;
3098 SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- no
3099 has_table_privilege
3100 ---------------------
3101 f
3102 (1 row)
3104 SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- no
3105 has_table_privilege
3106 ---------------------
3107 f
3108 (1 row)
3110 GRANT pg_read_all_stats TO regress_readallstats;
3111 SELECT has_table_privilege('regress_readallstats','pg_backend_memory_contexts','SELECT'); -- yes
3112 has_table_privilege
3113 ---------------------
3114 t
3115 (1 row)
3117 SELECT has_table_privilege('regress_readallstats','pg_shmem_allocations','SELECT'); -- yes
3118 has_table_privilege
3119 ---------------------
3120 t
3121 (1 row)
3123 -- run query to ensure that functions within views can be executed
3124 SET ROLE regress_readallstats;
3125 SELECT COUNT(*) >= 0 AS ok FROM pg_backend_memory_contexts;
3126 ok
3127 ----
3128 t
3129 (1 row)
3131 SELECT COUNT(*) >= 0 AS ok FROM pg_shmem_allocations;
3132 ok
3133 ----
3134 t
3135 (1 row)
3137 RESET ROLE;
3138 -- clean up
3139 DROP ROLE regress_readallstats;
3140 -- test role grantor machinery
3141 CREATE ROLE regress_group;
3142 CREATE ROLE regress_group_direct_manager;
3143 CREATE ROLE regress_group_indirect_manager;
3144 CREATE ROLE regress_group_member;
3145 GRANT regress_group TO regress_group_direct_manager WITH INHERIT FALSE, ADMIN TRUE;
3146 GRANT regress_group_direct_manager TO regress_group_indirect_manager;
3147 SET SESSION AUTHORIZATION regress_group_direct_manager;
3148 GRANT regress_group TO regress_group_member;
3149 SELECT member::regrole::text, CASE WHEN grantor = 10 THEN 'BOOTSTRAP SUPERUSER' ELSE grantor::regrole::text END FROM pg_auth_members WHERE roleid = 'regress_group'::regrole ORDER BY 1, 2;
3150 member | grantor
3151 ------------------------------+------------------------------
3152 regress_group_direct_manager | BOOTSTRAP SUPERUSER
3153 regress_group_member | regress_group_direct_manager
3154 (2 rows)
3156 REVOKE regress_group FROM regress_group_member;
3157 SET SESSION AUTHORIZATION regress_group_indirect_manager;
3158 GRANT regress_group TO regress_group_member;
3159 SELECT member::regrole::text, CASE WHEN grantor = 10 THEN 'BOOTSTRAP SUPERUSER' ELSE grantor::regrole::text END FROM pg_auth_members WHERE roleid = 'regress_group'::regrole ORDER BY 1, 2;
3160 member | grantor
3161 ------------------------------+------------------------------
3162 regress_group_direct_manager | BOOTSTRAP SUPERUSER
3163 regress_group_member | regress_group_direct_manager
3164 (2 rows)
3166 REVOKE regress_group FROM regress_group_member;
3167 RESET SESSION AUTHORIZATION;
3168 DROP ROLE regress_group;
3169 DROP ROLE regress_group_direct_manager;
3170 DROP ROLE regress_group_indirect_manager;
3171 DROP ROLE regress_group_member;
3172 -- test SET and INHERIT options with object ownership changes
3173 CREATE ROLE regress_roleoption_protagonist;
3174 CREATE ROLE regress_roleoption_donor;
3175 CREATE ROLE regress_roleoption_recipient;
3176 CREATE SCHEMA regress_roleoption;
3177 GRANT CREATE, USAGE ON SCHEMA regress_roleoption TO PUBLIC;
3178 GRANT regress_roleoption_donor TO regress_roleoption_protagonist WITH INHERIT TRUE, SET FALSE;
3179 GRANT regress_roleoption_recipient TO regress_roleoption_protagonist WITH INHERIT FALSE, SET TRUE;
3180 SET SESSION AUTHORIZATION regress_roleoption_protagonist;
3181 CREATE TABLE regress_roleoption.t1 (a int);
3182 CREATE TABLE regress_roleoption.t2 (a int);
3183 SET SESSION AUTHORIZATION regress_roleoption_donor;
3184 CREATE TABLE regress_roleoption.t3 (a int);
3185 SET SESSION AUTHORIZATION regress_roleoption_recipient;
3186 CREATE TABLE regress_roleoption.t4 (a int);
3187 SET SESSION AUTHORIZATION regress_roleoption_protagonist;
3188 ALTER TABLE regress_roleoption.t1 OWNER TO regress_roleoption_donor; -- fails, can't be come donor
3189 ERROR: must be able to SET ROLE "regress_roleoption_donor"
3190 ALTER TABLE regress_roleoption.t2 OWNER TO regress_roleoption_recipient; -- works
3191 ALTER TABLE regress_roleoption.t3 OWNER TO regress_roleoption_protagonist; -- works
3192 ALTER TABLE regress_roleoption.t4 OWNER TO regress_roleoption_protagonist; -- fails, we don't inherit from recipient
3193 ERROR: must be owner of table t4
3194 RESET SESSION AUTHORIZATION;
3195 DROP TABLE regress_roleoption.t1;
3196 DROP TABLE regress_roleoption.t2;
3197 DROP TABLE regress_roleoption.t3;
3198 DROP TABLE regress_roleoption.t4;
3199 DROP SCHEMA regress_roleoption;
3200 DROP ROLE regress_roleoption_protagonist;
3201 DROP ROLE regress_roleoption_donor;
3202 DROP ROLE regress_roleoption_recipient;
3203 -- MAINTAIN
3204 CREATE ROLE regress_no_maintain;
3205 CREATE ROLE regress_maintain;
3206 CREATE ROLE regress_maintain_all IN ROLE pg_maintain;
3207 CREATE TABLE maintain_test (a INT);
3208 CREATE INDEX ON maintain_test (a);
3209 GRANT MAINTAIN ON maintain_test TO regress_maintain;
3210 CREATE MATERIALIZED VIEW refresh_test AS SELECT 1;
3211 GRANT MAINTAIN ON refresh_test TO regress_maintain;
3212 CREATE SCHEMA reindex_test;
3213 -- negative tests; should fail
3214 SET ROLE regress_no_maintain;
3215 VACUUM maintain_test;
3216 WARNING: permission denied to vacuum "maintain_test", skipping it
3217 ANALYZE maintain_test;
3218 WARNING: permission denied to analyze "maintain_test", skipping it
3219 VACUUM (ANALYZE) maintain_test;
3220 WARNING: permission denied to vacuum "maintain_test", skipping it
3221 CLUSTER maintain_test USING maintain_test_a_idx;
3222 ERROR: permission denied for table maintain_test
3223 REFRESH MATERIALIZED VIEW refresh_test;
3224 ERROR: permission denied for materialized view refresh_test
3225 REINDEX TABLE maintain_test;
3226 ERROR: permission denied for table maintain_test
3227 REINDEX INDEX maintain_test_a_idx;
3228 ERROR: permission denied for index maintain_test_a_idx
3229 REINDEX SCHEMA reindex_test;
3230 ERROR: must be owner of schema reindex_test
3231 RESET ROLE;
3232 SET ROLE regress_maintain;
3233 VACUUM maintain_test;
3234 ANALYZE maintain_test;
3235 VACUUM (ANALYZE) maintain_test;
3236 CLUSTER maintain_test USING maintain_test_a_idx;
3237 REFRESH MATERIALIZED VIEW refresh_test;
3238 REINDEX TABLE maintain_test;
3239 REINDEX INDEX maintain_test_a_idx;
3240 REINDEX SCHEMA reindex_test;
3241 ERROR: must be owner of schema reindex_test
3242 RESET ROLE;
3243 SET ROLE regress_maintain_all;
3244 VACUUM maintain_test;
3245 ANALYZE maintain_test;
3246 VACUUM (ANALYZE) maintain_test;
3247 CLUSTER maintain_test USING maintain_test_a_idx;
3248 REFRESH MATERIALIZED VIEW refresh_test;
3249 REINDEX TABLE maintain_test;
3250 REINDEX INDEX maintain_test_a_idx;
3251 REINDEX SCHEMA reindex_test;
3252 RESET ROLE;
3253 DROP TABLE maintain_test;
3254 DROP MATERIALIZED VIEW refresh_test;
3255 DROP SCHEMA reindex_test;
3256 DROP ROLE regress_no_maintain;
3257 DROP ROLE regress_maintain;
3258 DROP ROLE regress_maintain_all;