| blob | 61396b2a8054ae0b0ff8477ec287528570aaf1e0 |
1 CREATE FUNCTION chkrolattr()
2 RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool)
3 AS $$
4 SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication
5 FROM pg_roles r
6 JOIN (VALUES(CURRENT_ROLE, 'current_role'),
7 (CURRENT_USER, 'current_user'),
8 (SESSION_USER, 'session_user'),
9 ('current_role', '-'),
10 ('current_user', '-'),
11 ('session_user', '-'),
12 ('Public', '-'),
13 ('None', '-'))
14 AS v(uname, keyword)
15 ON (r.rolname = v.uname)
16 ORDER BY 1, 2;
17 $$ LANGUAGE SQL;
18 CREATE FUNCTION chksetconfig()
19 RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[])
20 AS $$
21 SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'),
22 COALESCE(v.keyword, '-'), s.setconfig
23 FROM pg_db_role_setting s
24 LEFT JOIN pg_roles r ON (r.oid = s.setrole)
25 LEFT JOIN pg_database d ON (d.oid = s.setdatabase)
26 LEFT JOIN (VALUES(CURRENT_ROLE, 'current_role'),
27 (CURRENT_USER, 'current_user'),
28 (SESSION_USER, 'session_user'))
29 AS v(uname, keyword)
30 ON (r.rolname = v.uname)
31 WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2')
32 ORDER BY 1, 2, 3;
33 $$ LANGUAGE SQL;
34 CREATE FUNCTION chkumapping()
35 RETURNS TABLE (umname name, umserver name, umoptions text[])
36 AS $$
37 SELECT r.rolname, s.srvname, m.umoptions
38 FROM pg_user_mapping m
39 LEFT JOIN pg_roles r ON (r.oid = m.umuser)
40 JOIN pg_foreign_server s ON (s.oid = m.umserver)
41 ORDER BY 2, 1;
42 $$ LANGUAGE SQL;
43 --
44 -- We test creation and use of these role names to ensure that the server
45 -- correctly distinguishes role keywords from quoted names that look like
46 -- those keywords. In a test environment, creation of these roles may
47 -- provoke warnings, so hide the warnings by raising client_min_messages.
48 --
49 SET client_min_messages = ERROR;
50 CREATE ROLE "Public";
51 CREATE ROLE "None";
52 CREATE ROLE "current_role";
53 CREATE ROLE "current_user";
54 CREATE ROLE "session_user";
55 CREATE ROLE "user";
56 RESET client_min_messages;
57 CREATE ROLE current_user; -- error
58 ERROR: CURRENT_USER cannot be used as a role name here
59 LINE 1: CREATE ROLE current_user;
60 ^
61 CREATE ROLE current_role; -- error
62 ERROR: CURRENT_ROLE cannot be used as a role name here
63 LINE 1: CREATE ROLE current_role;
64 ^
65 CREATE ROLE session_user; -- error
66 ERROR: SESSION_USER cannot be used as a role name here
67 LINE 1: CREATE ROLE session_user;
68 ^
69 CREATE ROLE user; -- error
70 ERROR: syntax error at or near "user"
71 LINE 1: CREATE ROLE user;
72 ^
73 CREATE ROLE all; -- error
74 ERROR: syntax error at or near "all"
75 LINE 1: CREATE ROLE all;
76 ^
77 CREATE ROLE public; -- error
78 ERROR: role name "public" is reserved
79 LINE 1: CREATE ROLE public;
80 ^
81 CREATE ROLE "public"; -- error
82 ERROR: role name "public" is reserved
83 LINE 1: CREATE ROLE "public";
84 ^
85 CREATE ROLE none; -- error
86 ERROR: role name "none" is reserved
87 LINE 1: CREATE ROLE none;
88 ^
89 CREATE ROLE "none"; -- error
90 ERROR: role name "none" is reserved
91 LINE 1: CREATE ROLE "none";
92 ^
93 CREATE ROLE pg_abc; -- error
94 ERROR: role name "pg_abc" is reserved
95 DETAIL: Role names starting with "pg_" are reserved.
96 CREATE ROLE "pg_abc"; -- error
97 ERROR: role name "pg_abc" is reserved
98 DETAIL: Role names starting with "pg_" are reserved.
99 CREATE ROLE pg_abcdef; -- error
100 ERROR: role name "pg_abcdef" is reserved
101 DETAIL: Role names starting with "pg_" are reserved.
102 CREATE ROLE "pg_abcdef"; -- error
103 ERROR: role name "pg_abcdef" is reserved
104 DETAIL: Role names starting with "pg_" are reserved.
105 CREATE ROLE regress_testrol0 SUPERUSER LOGIN;
106 CREATE ROLE regress_testrolx SUPERUSER LOGIN;
107 CREATE ROLE regress_testrol2 SUPERUSER;
108 CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2;
109 \c -
110 SET SESSION AUTHORIZATION regress_testrol1;
111 SET ROLE regress_testrol2;
112 -- ALTER ROLE
113 BEGIN;
114 SELECT * FROM chkrolattr();
115 role | rolekeyword | canlogin | replication
116 ------------------+--------------+----------+-------------
117 None | - | f | f
118 Public | - | f | f
119 current_role | - | f | f
120 current_user | - | f | f
121 regress_testrol1 | session_user | t | f
122 regress_testrol2 | current_role | f | f
123 regress_testrol2 | current_user | f | f
124 session_user | - | f | f
125 (8 rows)
127 ALTER ROLE CURRENT_ROLE WITH REPLICATION;
128 SELECT * FROM chkrolattr();
129 role | rolekeyword | canlogin | replication
130 ------------------+--------------+----------+-------------
131 None | - | f | f
132 Public | - | f | f
133 current_role | - | f | f
134 current_user | - | f | f
135 regress_testrol1 | session_user | t | f
136 regress_testrol2 | current_role | f | t
137 regress_testrol2 | current_user | f | t
138 session_user | - | f | f
139 (8 rows)
141 ALTER ROLE "current_role" WITH REPLICATION;
142 SELECT * FROM chkrolattr();
143 role | rolekeyword | canlogin | replication
144 ------------------+--------------+----------+-------------
145 None | - | f | f
146 Public | - | f | f
147 current_role | - | f | t
148 current_user | - | f | f
149 regress_testrol1 | session_user | t | f
150 regress_testrol2 | current_role | f | t
151 regress_testrol2 | current_user | f | t
152 session_user | - | f | f
153 (8 rows)
155 ALTER ROLE CURRENT_ROLE WITH NOREPLICATION;
156 ALTER ROLE CURRENT_USER WITH REPLICATION;
157 SELECT * FROM chkrolattr();
158 role | rolekeyword | canlogin | replication
159 ------------------+--------------+----------+-------------
160 None | - | f | f
161 Public | - | f | f
162 current_role | - | f | t
163 current_user | - | f | f
164 regress_testrol1 | session_user | t | f
165 regress_testrol2 | current_role | f | t
166 regress_testrol2 | current_user | f | t
167 session_user | - | f | f
168 (8 rows)
170 ALTER ROLE "current_user" WITH REPLICATION;
171 SELECT * FROM chkrolattr();
172 role | rolekeyword | canlogin | replication
173 ------------------+--------------+----------+-------------
174 None | - | f | f
175 Public | - | f | f
176 current_role | - | f | t
177 current_user | - | f | t
178 regress_testrol1 | session_user | t | f
179 regress_testrol2 | current_role | f | t
180 regress_testrol2 | current_user | f | t
181 session_user | - | f | f
182 (8 rows)
184 ALTER ROLE SESSION_USER WITH REPLICATION;
185 SELECT * FROM chkrolattr();
186 role | rolekeyword | canlogin | replication
187 ------------------+--------------+----------+-------------
188 None | - | f | f
189 Public | - | f | f
190 current_role | - | f | t
191 current_user | - | f | t
192 regress_testrol1 | session_user | t | t
193 regress_testrol2 | current_role | f | t
194 regress_testrol2 | current_user | f | t
195 session_user | - | f | f
196 (8 rows)
198 ALTER ROLE "session_user" WITH REPLICATION;
199 SELECT * FROM chkrolattr();
200 role | rolekeyword | canlogin | replication
201 ------------------+--------------+----------+-------------
202 None | - | f | f
203 Public | - | f | f
204 current_role | - | f | t
205 current_user | - | f | t
206 regress_testrol1 | session_user | t | t
207 regress_testrol2 | current_role | f | t
208 regress_testrol2 | current_user | f | t
209 session_user | - | f | t
210 (8 rows)
212 ALTER USER "Public" WITH REPLICATION;
213 ALTER USER "None" WITH REPLICATION;
214 SELECT * FROM chkrolattr();
215 role | rolekeyword | canlogin | replication
216 ------------------+--------------+----------+-------------
217 None | - | f | t
218 Public | - | f | t
219 current_role | - | f | t
220 current_user | - | f | t
221 regress_testrol1 | session_user | t | t
222 regress_testrol2 | current_role | f | t
223 regress_testrol2 | current_user | f | t
224 session_user | - | f | t
225 (8 rows)
227 ALTER USER regress_testrol1 WITH NOREPLICATION;
228 ALTER USER regress_testrol2 WITH NOREPLICATION;
229 SELECT * FROM chkrolattr();
230 role | rolekeyword | canlogin | replication
231 ------------------+--------------+----------+-------------
232 None | - | f | t
233 Public | - | f | t
234 current_role | - | f | t
235 current_user | - | f | t
236 regress_testrol1 | session_user | t | f
237 regress_testrol2 | current_role | f | f
238 regress_testrol2 | current_user | f | f
239 session_user | - | f | t
240 (8 rows)
242 ROLLBACK;
243 ALTER ROLE USER WITH LOGIN; -- error
244 ERROR: syntax error at or near "USER"
245 LINE 1: ALTER ROLE USER WITH LOGIN;
246 ^
247 ALTER ROLE ALL WITH REPLICATION; -- error
248 ERROR: syntax error at or near "WITH"
249 LINE 1: ALTER ROLE ALL WITH REPLICATION;
250 ^
251 ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error
252 ERROR: role "session_role" does not exist
253 ALTER ROLE PUBLIC WITH NOREPLICATION; -- error
254 ERROR: role "public" does not exist
255 ALTER ROLE "public" WITH NOREPLICATION; -- error
256 ERROR: role "public" does not exist
257 ALTER ROLE NONE WITH NOREPLICATION; -- error
258 ERROR: role name "none" is reserved
259 LINE 1: ALTER ROLE NONE WITH NOREPLICATION;
260 ^
261 ALTER ROLE "none" WITH NOREPLICATION; -- error
262 ERROR: role name "none" is reserved
263 LINE 1: ALTER ROLE "none" WITH NOREPLICATION;
264 ^
265 ALTER ROLE nonexistent WITH NOREPLICATION; -- error
266 ERROR: role "nonexistent" does not exist
267 -- ALTER USER
268 BEGIN;
269 SELECT * FROM chkrolattr();
270 role | rolekeyword | canlogin | replication
271 ------------------+--------------+----------+-------------
272 None | - | f | f
273 Public | - | f | f
274 current_role | - | f | f
275 current_user | - | f | f
276 regress_testrol1 | session_user | t | f
277 regress_testrol2 | current_role | f | f
278 regress_testrol2 | current_user | f | f
279 session_user | - | f | f
280 (8 rows)
282 ALTER USER CURRENT_ROLE WITH REPLICATION;
283 SELECT * FROM chkrolattr();
284 role | rolekeyword | canlogin | replication
285 ------------------+--------------+----------+-------------
286 None | - | f | f
287 Public | - | f | f
288 current_role | - | f | f
289 current_user | - | f | f
290 regress_testrol1 | session_user | t | f
291 regress_testrol2 | current_role | f | t
292 regress_testrol2 | current_user | f | t
293 session_user | - | f | f
294 (8 rows)
296 ALTER USER "current_role" WITH REPLICATION;
297 SELECT * FROM chkrolattr();
298 role | rolekeyword | canlogin | replication
299 ------------------+--------------+----------+-------------
300 None | - | f | f
301 Public | - | f | f
302 current_role | - | f | t
303 current_user | - | f | f
304 regress_testrol1 | session_user | t | f
305 regress_testrol2 | current_role | f | t
306 regress_testrol2 | current_user | f | t
307 session_user | - | f | f
308 (8 rows)
310 ALTER USER CURRENT_ROLE WITH NOREPLICATION;
311 ALTER USER CURRENT_USER WITH REPLICATION;
312 SELECT * FROM chkrolattr();
313 role | rolekeyword | canlogin | replication
314 ------------------+--------------+----------+-------------
315 None | - | f | f
316 Public | - | f | f
317 current_role | - | f | t
318 current_user | - | f | f
319 regress_testrol1 | session_user | t | f
320 regress_testrol2 | current_role | f | t
321 regress_testrol2 | current_user | f | t
322 session_user | - | f | f
323 (8 rows)
325 ALTER USER "current_user" WITH REPLICATION;
326 SELECT * FROM chkrolattr();
327 role | rolekeyword | canlogin | replication
328 ------------------+--------------+----------+-------------
329 None | - | f | f
330 Public | - | f | f
331 current_role | - | f | t
332 current_user | - | f | t
333 regress_testrol1 | session_user | t | f
334 regress_testrol2 | current_role | f | t
335 regress_testrol2 | current_user | f | t
336 session_user | - | f | f
337 (8 rows)
339 ALTER USER SESSION_USER WITH REPLICATION;
340 SELECT * FROM chkrolattr();
341 role | rolekeyword | canlogin | replication
342 ------------------+--------------+----------+-------------
343 None | - | f | f
344 Public | - | f | f
345 current_role | - | f | t
346 current_user | - | f | t
347 regress_testrol1 | session_user | t | t
348 regress_testrol2 | current_role | f | t
349 regress_testrol2 | current_user | f | t
350 session_user | - | f | f
351 (8 rows)
353 ALTER USER "session_user" WITH REPLICATION;
354 SELECT * FROM chkrolattr();
355 role | rolekeyword | canlogin | replication
356 ------------------+--------------+----------+-------------
357 None | - | f | f
358 Public | - | f | f
359 current_role | - | f | t
360 current_user | - | f | t
361 regress_testrol1 | session_user | t | t
362 regress_testrol2 | current_role | f | t
363 regress_testrol2 | current_user | f | t
364 session_user | - | f | t
365 (8 rows)
367 ALTER USER "Public" WITH REPLICATION;
368 ALTER USER "None" WITH REPLICATION;
369 SELECT * FROM chkrolattr();
370 role | rolekeyword | canlogin | replication
371 ------------------+--------------+----------+-------------
372 None | - | f | t
373 Public | - | f | t
374 current_role | - | f | t
375 current_user | - | f | t
376 regress_testrol1 | session_user | t | t
377 regress_testrol2 | current_role | f | t
378 regress_testrol2 | current_user | f | t
379 session_user | - | f | t
380 (8 rows)
382 ALTER USER regress_testrol1 WITH NOREPLICATION;
383 ALTER USER regress_testrol2 WITH NOREPLICATION;
384 SELECT * FROM chkrolattr();
385 role | rolekeyword | canlogin | replication
386 ------------------+--------------+----------+-------------
387 None | - | f | t
388 Public | - | f | t
389 current_role | - | f | t
390 current_user | - | f | t
391 regress_testrol1 | session_user | t | f
392 regress_testrol2 | current_role | f | f
393 regress_testrol2 | current_user | f | f
394 session_user | - | f | t
395 (8 rows)
397 ROLLBACK;
398 ALTER USER USER WITH LOGIN; -- error
399 ERROR: syntax error at or near "USER"
400 LINE 1: ALTER USER USER WITH LOGIN;
401 ^
402 ALTER USER ALL WITH REPLICATION; -- error
403 ERROR: syntax error at or near "WITH"
404 LINE 1: ALTER USER ALL WITH REPLICATION;
405 ^
406 ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error
407 ERROR: role "session_role" does not exist
408 ALTER USER PUBLIC WITH NOREPLICATION; -- error
409 ERROR: role "public" does not exist
410 ALTER USER "public" WITH NOREPLICATION; -- error
411 ERROR: role "public" does not exist
412 ALTER USER NONE WITH NOREPLICATION; -- error
413 ERROR: role name "none" is reserved
414 LINE 1: ALTER USER NONE WITH NOREPLICATION;
415 ^
416 ALTER USER "none" WITH NOREPLICATION; -- error
417 ERROR: role name "none" is reserved
418 LINE 1: ALTER USER "none" WITH NOREPLICATION;
419 ^
420 ALTER USER nonexistent WITH NOREPLICATION; -- error
421 ERROR: role "nonexistent" does not exist
422 -- ALTER ROLE SET/RESET
423 SELECT * FROM chksetconfig();
424 db | role | rolkeyword | setconfig
425 ----+------+------------+-----------
426 (0 rows)
428 ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ';
429 ALTER ROLE CURRENT_USER SET application_name to 'FOO';
430 ALTER ROLE SESSION_USER SET application_name to 'BAR';
431 ALTER ROLE "current_user" SET application_name to 'FOOFOO';
432 ALTER ROLE "Public" SET application_name to 'BARBAR';
433 ALTER ROLE ALL SET application_name to 'SLAP';
434 SELECT * FROM chksetconfig();
435 db | role | rolkeyword | setconfig
436 -----+------------------+--------------+---------------------------
437 ALL | Public | - | {application_name=BARBAR}
438 ALL | current_user | - | {application_name=FOOFOO}
439 ALL | regress_testrol1 | session_user | {application_name=BAR}
440 ALL | regress_testrol2 | current_role | {application_name=FOO}
441 ALL | regress_testrol2 | current_user | {application_name=FOO}
442 (5 rows)
444 ALTER ROLE regress_testrol1 SET application_name to 'SLAM';
445 SELECT * FROM chksetconfig();
446 db | role | rolkeyword | setconfig
447 -----+------------------+--------------+---------------------------
448 ALL | Public | - | {application_name=BARBAR}
449 ALL | current_user | - | {application_name=FOOFOO}
450 ALL | regress_testrol1 | session_user | {application_name=SLAM}
451 ALL | regress_testrol2 | current_role | {application_name=FOO}
452 ALL | regress_testrol2 | current_user | {application_name=FOO}
453 (5 rows)
455 ALTER ROLE CURRENT_ROLE RESET application_name;
456 ALTER ROLE CURRENT_USER RESET application_name;
457 ALTER ROLE SESSION_USER RESET application_name;
458 ALTER ROLE "current_user" RESET application_name;
459 ALTER ROLE "Public" RESET application_name;
460 ALTER ROLE ALL RESET application_name;
461 SELECT * FROM chksetconfig();
462 db | role | rolkeyword | setconfig
463 ----+------+------------+-----------
464 (0 rows)
466 ALTER ROLE USER SET application_name to 'BOOM'; -- error
467 ERROR: syntax error at or near "USER"
468 LINE 1: ALTER ROLE USER SET application_name to 'BOOM';
469 ^
470 ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error
471 ERROR: role "public" does not exist
472 ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error
473 ERROR: role "nonexistent" does not exist
474 -- ALTER USER SET/RESET
475 SELECT * FROM chksetconfig();
476 db | role | rolkeyword | setconfig
477 ----+------+------------+-----------
478 (0 rows)
480 ALTER USER CURRENT_ROLE SET application_name to 'BAZ';
481 ALTER USER CURRENT_USER SET application_name to 'FOO';
482 ALTER USER SESSION_USER SET application_name to 'BAR';
483 ALTER USER "current_user" SET application_name to 'FOOFOO';
484 ALTER USER "Public" SET application_name to 'BARBAR';
485 ALTER USER ALL SET application_name to 'SLAP';
486 SELECT * FROM chksetconfig();
487 db | role | rolkeyword | setconfig
488 -----+------------------+--------------+---------------------------
489 ALL | Public | - | {application_name=BARBAR}
490 ALL | current_user | - | {application_name=FOOFOO}
491 ALL | regress_testrol1 | session_user | {application_name=BAR}
492 ALL | regress_testrol2 | current_role | {application_name=FOO}
493 ALL | regress_testrol2 | current_user | {application_name=FOO}
494 (5 rows)
496 ALTER USER regress_testrol1 SET application_name to 'SLAM';
497 SELECT * FROM chksetconfig();
498 db | role | rolkeyword | setconfig
499 -----+------------------+--------------+---------------------------
500 ALL | Public | - | {application_name=BARBAR}
501 ALL | current_user | - | {application_name=FOOFOO}
502 ALL | regress_testrol1 | session_user | {application_name=SLAM}
503 ALL | regress_testrol2 | current_role | {application_name=FOO}
504 ALL | regress_testrol2 | current_user | {application_name=FOO}
505 (5 rows)
507 ALTER USER CURRENT_ROLE RESET application_name;
508 ALTER USER CURRENT_USER RESET application_name;
509 ALTER USER SESSION_USER RESET application_name;
510 ALTER USER "current_user" RESET application_name;
511 ALTER USER "Public" RESET application_name;
512 ALTER USER ALL RESET application_name;
513 SELECT * FROM chksetconfig();
514 db | role | rolkeyword | setconfig
515 ----+------+------------+-----------
516 (0 rows)
518 ALTER USER USER SET application_name to 'BOOM'; -- error
519 ERROR: syntax error at or near "USER"
520 LINE 1: ALTER USER USER SET application_name to 'BOOM';
521 ^
522 ALTER USER PUBLIC SET application_name to 'BOMB'; -- error
523 ERROR: role "public" does not exist
524 ALTER USER NONE SET application_name to 'BOMB'; -- error
525 ERROR: role name "none" is reserved
526 LINE 1: ALTER USER NONE SET application_name to 'BOMB';
527 ^
528 ALTER USER nonexistent SET application_name to 'BOMB'; -- error
529 ERROR: role "nonexistent" does not exist
530 -- CREATE SCHEMA
531 CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER;
532 CREATE SCHEMA newschema2 AUTHORIZATION "current_user";
533 CREATE SCHEMA newschema3 AUTHORIZATION CURRENT_ROLE;
534 CREATE SCHEMA newschema4 AUTHORIZATION SESSION_USER;
535 CREATE SCHEMA newschema5 AUTHORIZATION regress_testrolx;
536 CREATE SCHEMA newschema6 AUTHORIZATION "Public";
537 CREATE SCHEMA newschemax AUTHORIZATION USER; -- error
538 ERROR: syntax error at or near "USER"
539 LINE 1: CREATE SCHEMA newschemax AUTHORIZATION USER;
540 ^
541 CREATE SCHEMA newschemax AUTHORIZATION PUBLIC; -- error
542 ERROR: role "public" does not exist
543 CREATE SCHEMA newschemax AUTHORIZATION "public"; -- error
544 ERROR: role "public" does not exist
545 CREATE SCHEMA newschemax AUTHORIZATION NONE; -- error
546 ERROR: role name "none" is reserved
547 LINE 1: CREATE SCHEMA newschemax AUTHORIZATION NONE;
548 ^
549 CREATE SCHEMA newschemax AUTHORIZATION nonexistent; -- error
550 ERROR: role "nonexistent" does not exist
551 SELECT n.nspname, r.rolname FROM pg_namespace n
552 JOIN pg_roles r ON (r.oid = n.nspowner)
553 WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
554 nspname | rolname
555 ------------+------------------
556 newschema1 | regress_testrol2
557 newschema2 | current_user
558 newschema3 | regress_testrol2
559 newschema4 | regress_testrol1
560 newschema5 | regress_testrolx
561 newschema6 | Public
562 (6 rows)
564 CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER;
565 NOTICE: schema "newschema1" already exists, skipping
566 CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user";
567 NOTICE: schema "newschema2" already exists, skipping
568 CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION CURRENT_ROLE;
569 NOTICE: schema "newschema3" already exists, skipping
570 CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION SESSION_USER;
571 NOTICE: schema "newschema4" already exists, skipping
572 CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION regress_testrolx;
573 NOTICE: schema "newschema5" already exists, skipping
574 CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "Public";
575 NOTICE: schema "newschema6" already exists, skipping
576 CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION USER; -- error
577 ERROR: syntax error at or near "USER"
578 LINE 1: CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION USER;
579 ^
580 CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION PUBLIC; -- error
581 ERROR: role "public" does not exist
582 CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION "public"; -- error
583 ERROR: role "public" does not exist
584 CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION NONE; -- error
585 ERROR: role name "none" is reserved
586 LINE 1: CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION NONE;
587 ^
588 CREATE SCHEMA IF NOT EXISTS newschemax AUTHORIZATION nonexistent; -- error
589 ERROR: role "nonexistent" does not exist
590 SELECT n.nspname, r.rolname FROM pg_namespace n
591 JOIN pg_roles r ON (r.oid = n.nspowner)
592 WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
593 nspname | rolname
594 ------------+------------------
595 newschema1 | regress_testrol2
596 newschema2 | current_user
597 newschema3 | regress_testrol2
598 newschema4 | regress_testrol1
599 newschema5 | regress_testrolx
600 newschema6 | Public
601 (6 rows)
603 -- ALTER TABLE OWNER TO
604 \c -
605 SET SESSION AUTHORIZATION regress_testrol0;
606 CREATE TABLE testtab1 (a int);
607 CREATE TABLE testtab2 (a int);
608 CREATE TABLE testtab3 (a int);
609 CREATE TABLE testtab4 (a int);
610 CREATE TABLE testtab5 (a int);
611 CREATE TABLE testtab6 (a int);
612 CREATE TABLE testtab7 (a int);
613 \c -
614 SET SESSION AUTHORIZATION regress_testrol1;
615 SET ROLE regress_testrol2;
616 ALTER TABLE testtab1 OWNER TO CURRENT_USER;
617 ALTER TABLE testtab2 OWNER TO "current_user";
618 ALTER TABLE testtab3 OWNER TO CURRENT_ROLE;
619 ALTER TABLE testtab4 OWNER TO SESSION_USER;
620 ALTER TABLE testtab5 OWNER TO regress_testrolx;
621 ALTER TABLE testtab6 OWNER TO "Public";
622 ALTER TABLE testtab7 OWNER TO USER; --error
623 ERROR: syntax error at or near "USER"
624 LINE 1: ALTER TABLE testtab7 OWNER TO USER;
625 ^
626 ALTER TABLE testtab7 OWNER TO PUBLIC; -- error
627 ERROR: role "public" does not exist
628 ALTER TABLE testtab7 OWNER TO "public"; -- error
629 ERROR: role "public" does not exist
630 ALTER TABLE testtab7 OWNER TO nonexistent; -- error
631 ERROR: role "nonexistent" does not exist
632 SELECT c.relname, r.rolname
633 FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner)
634 WHERE relname LIKE 'testtab_'
635 ORDER BY 1;
636 relname | rolname
637 ----------+------------------
638 testtab1 | regress_testrol2
639 testtab2 | current_user
640 testtab3 | regress_testrol2
641 testtab4 | regress_testrol1
642 testtab5 | regress_testrolx
643 testtab6 | Public
644 testtab7 | regress_testrol0
645 (7 rows)
647 -- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are
648 -- changed their owner in the same way.
649 -- ALTER AGGREGATE
650 \c -
651 SET SESSION AUTHORIZATION regress_testrol0;
652 CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8);
653 CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8);
654 CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8);
655 CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8);
656 CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
657 CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8);
658 CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8);
659 CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8);
660 CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8);
661 CREATE AGGREGATE testagga(int2) (SFUNC = int2_sum, STYPE = int8);
662 \c -
663 SET SESSION AUTHORIZATION regress_testrol1;
664 SET ROLE regress_testrol2;
665 ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER;
666 ALTER AGGREGATE testagg2(int2) OWNER TO "current_user";
667 ALTER AGGREGATE testagg3(int2) OWNER TO CURRENT_ROLE;
668 ALTER AGGREGATE testagg4(int2) OWNER TO SESSION_USER;
669 ALTER AGGREGATE testagg5(int2) OWNER TO regress_testrolx;
670 ALTER AGGREGATE testagg6(int2) OWNER TO "Public";
671 ALTER AGGREGATE testagg6(int2) OWNER TO USER; -- error
672 ERROR: syntax error at or near "USER"
673 LINE 1: ALTER AGGREGATE testagg6(int2) OWNER TO USER;
674 ^
675 ALTER AGGREGATE testagg6(int2) OWNER TO PUBLIC; -- error
676 ERROR: role "public" does not exist
677 ALTER AGGREGATE testagg6(int2) OWNER TO "public"; -- error
678 ERROR: role "public" does not exist
679 ALTER AGGREGATE testagg6(int2) OWNER TO nonexistent; -- error
680 ERROR: role "nonexistent" does not exist
681 SELECT p.proname, r.rolname
682 FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner)
683 WHERE proname LIKE 'testagg_'
684 ORDER BY 1;
685 proname | rolname
686 ----------+------------------
687 testagg1 | regress_testrol2
688 testagg2 | current_user
689 testagg3 | regress_testrol2
690 testagg4 | regress_testrol1
691 testagg5 | regress_testrolx
692 testagg6 | Public
693 testagg7 | regress_testrol0
694 testagg8 | regress_testrol0
695 testagg9 | regress_testrol0
696 testagga | regress_testrol0
697 (10 rows)
699 -- CREATE USER MAPPING
700 CREATE FOREIGN DATA WRAPPER test_wrapper;
701 CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper;
702 CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper;
703 CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper;
704 CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper;
705 CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper;
706 CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper;
707 CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper;
708 CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper;
709 CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper;
710 CREATE SERVER sv10 FOREIGN DATA WRAPPER test_wrapper;
711 CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
712 CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
713 CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv3 OPTIONS (user 'CURRENT_ROLE');
714 CREATE USER MAPPING FOR USER SERVER sv4 OPTIONS (user 'USER');
715 CREATE USER MAPPING FOR "user" SERVER sv5 OPTIONS (user '"USER"');
716 CREATE USER MAPPING FOR SESSION_USER SERVER sv6 OPTIONS (user 'SESSION_USER');
717 CREATE USER MAPPING FOR PUBLIC SERVER sv7 OPTIONS (user 'PUBLIC');
718 CREATE USER MAPPING FOR "Public" SERVER sv8 OPTIONS (user '"Public"');
719 CREATE USER MAPPING FOR regress_testrolx SERVER sv9 OPTIONS (user 'regress_testrolx');
720 CREATE USER MAPPING FOR nonexistent SERVER sv10 OPTIONS (user 'nonexistent'); -- error;
721 ERROR: role "nonexistent" does not exist
722 SELECT * FROM chkumapping();
723 umname | umserver | umoptions
724 ------------------+----------+---------------------------
725 regress_testrol2 | sv1 | {user=CURRENT_USER}
726 current_user | sv2 | {"user=\"current_user\""}
727 regress_testrol2 | sv3 | {user=CURRENT_ROLE}
728 regress_testrol2 | sv4 | {user=USER}
729 user | sv5 | {"user=\"USER\""}
730 regress_testrol1 | sv6 | {user=SESSION_USER}
731 | sv7 | {user=PUBLIC}
732 Public | sv8 | {"user=\"Public\""}
733 regress_testrolx | sv9 | {user=regress_testrolx}
734 (9 rows)
736 -- ALTER USER MAPPING
737 ALTER USER MAPPING FOR CURRENT_USER SERVER sv1
738 OPTIONS (SET user 'CURRENT_USER_alt');
739 ALTER USER MAPPING FOR "current_user" SERVER sv2
740 OPTIONS (SET user '"current_user"_alt');
741 ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv3
742 OPTIONS (SET user 'CURRENT_ROLE_alt');
743 ALTER USER MAPPING FOR USER SERVER sv4
744 OPTIONS (SET user 'USER_alt');
745 ALTER USER MAPPING FOR "user" SERVER sv5
746 OPTIONS (SET user '"user"_alt');
747 ALTER USER MAPPING FOR SESSION_USER SERVER sv6
748 OPTIONS (SET user 'SESSION_USER_alt');
749 ALTER USER MAPPING FOR PUBLIC SERVER sv7
750 OPTIONS (SET user 'public_alt');
751 ALTER USER MAPPING FOR "Public" SERVER sv8
752 OPTIONS (SET user '"Public"_alt');
753 ALTER USER MAPPING FOR regress_testrolx SERVER sv9
754 OPTIONS (SET user 'regress_testrolx_alt');
755 ALTER USER MAPPING FOR nonexistent SERVER sv10
756 OPTIONS (SET user 'nonexistent_alt'); -- error
757 ERROR: role "nonexistent" does not exist
758 SELECT * FROM chkumapping();
759 umname | umserver | umoptions
760 ------------------+----------+-------------------------------
761 regress_testrol2 | sv1 | {user=CURRENT_USER_alt}
762 current_user | sv2 | {"user=\"current_user\"_alt"}
763 regress_testrol2 | sv3 | {user=CURRENT_ROLE_alt}
764 regress_testrol2 | sv4 | {user=USER_alt}
765 user | sv5 | {"user=\"user\"_alt"}
766 regress_testrol1 | sv6 | {user=SESSION_USER_alt}
767 | sv7 | {user=public_alt}
768 Public | sv8 | {"user=\"Public\"_alt"}
769 regress_testrolx | sv9 | {user=regress_testrolx_alt}
770 (9 rows)
772 -- DROP USER MAPPING
773 DROP USER MAPPING FOR CURRENT_USER SERVER sv1;
774 DROP USER MAPPING FOR "current_user" SERVER sv2;
775 DROP USER MAPPING FOR CURRENT_ROLE SERVER sv3;
776 DROP USER MAPPING FOR USER SERVER sv4;
777 DROP USER MAPPING FOR "user" SERVER sv5;
778 DROP USER MAPPING FOR SESSION_USER SERVER sv6;
779 DROP USER MAPPING FOR PUBLIC SERVER sv7;
780 DROP USER MAPPING FOR "Public" SERVER sv8;
781 DROP USER MAPPING FOR regress_testrolx SERVER sv9;
782 DROP USER MAPPING FOR nonexistent SERVER sv10; -- error
783 ERROR: role "nonexistent" does not exist
784 SELECT * FROM chkumapping();
785 umname | umserver | umoptions
786 --------+----------+-----------
787 (0 rows)
789 CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
790 CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
791 CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv3 OPTIONS (user 'CURRENT_ROLE');
792 CREATE USER MAPPING FOR USER SERVER sv4 OPTIONS (user 'USER');
793 CREATE USER MAPPING FOR "user" SERVER sv5 OPTIONS (user '"USER"');
794 CREATE USER MAPPING FOR SESSION_USER SERVER sv6 OPTIONS (user 'SESSION_USER');
795 CREATE USER MAPPING FOR PUBLIC SERVER sv7 OPTIONS (user 'PUBLIC');
796 CREATE USER MAPPING FOR "Public" SERVER sv8 OPTIONS (user '"Public"');
797 CREATE USER MAPPING FOR regress_testrolx SERVER sv9 OPTIONS (user 'regress_testrolx');
798 SELECT * FROM chkumapping();
799 umname | umserver | umoptions
800 ------------------+----------+---------------------------
801 regress_testrol2 | sv1 | {user=CURRENT_USER}
802 current_user | sv2 | {"user=\"current_user\""}
803 regress_testrol2 | sv3 | {user=CURRENT_ROLE}
804 regress_testrol2 | sv4 | {user=USER}
805 user | sv5 | {"user=\"USER\""}
806 regress_testrol1 | sv6 | {user=SESSION_USER}
807 | sv7 | {user=PUBLIC}
808 Public | sv8 | {"user=\"Public\""}
809 regress_testrolx | sv9 | {user=regress_testrolx}
810 (9 rows)
812 -- DROP USER MAPPING IF EXISTS
813 DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1;
814 SELECT * FROM chkumapping();
815 umname | umserver | umoptions
816 ------------------+----------+---------------------------
817 current_user | sv2 | {"user=\"current_user\""}
818 regress_testrol2 | sv3 | {user=CURRENT_ROLE}
819 regress_testrol2 | sv4 | {user=USER}
820 user | sv5 | {"user=\"USER\""}
821 regress_testrol1 | sv6 | {user=SESSION_USER}
822 | sv7 | {user=PUBLIC}
823 Public | sv8 | {"user=\"Public\""}
824 regress_testrolx | sv9 | {user=regress_testrolx}
825 (8 rows)
827 DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2;
828 SELECT * FROM chkumapping();
829 umname | umserver | umoptions
830 ------------------+----------+-------------------------
831 regress_testrol2 | sv3 | {user=CURRENT_ROLE}
832 regress_testrol2 | sv4 | {user=USER}
833 user | sv5 | {"user=\"USER\""}
834 regress_testrol1 | sv6 | {user=SESSION_USER}
835 | sv7 | {user=PUBLIC}
836 Public | sv8 | {"user=\"Public\""}
837 regress_testrolx | sv9 | {user=regress_testrolx}
838 (7 rows)
840 DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv3;
841 SELECT * FROM chkumapping();
842 umname | umserver | umoptions
843 ------------------+----------+-------------------------
844 regress_testrol2 | sv4 | {user=USER}
845 user | sv5 | {"user=\"USER\""}
846 regress_testrol1 | sv6 | {user=SESSION_USER}
847 | sv7 | {user=PUBLIC}
848 Public | sv8 | {"user=\"Public\""}
849 regress_testrolx | sv9 | {user=regress_testrolx}
850 (6 rows)
852 DROP USER MAPPING IF EXISTS FOR USER SERVER sv4;
853 SELECT * FROM chkumapping();
854 umname | umserver | umoptions
855 ------------------+----------+-------------------------
856 user | sv5 | {"user=\"USER\""}
857 regress_testrol1 | sv6 | {user=SESSION_USER}
858 | sv7 | {user=PUBLIC}
859 Public | sv8 | {"user=\"Public\""}
860 regress_testrolx | sv9 | {user=regress_testrolx}
861 (5 rows)
863 DROP USER MAPPING IF EXISTS FOR "user" SERVER sv5;
864 SELECT * FROM chkumapping();
865 umname | umserver | umoptions
866 ------------------+----------+-------------------------
867 regress_testrol1 | sv6 | {user=SESSION_USER}
868 | sv7 | {user=PUBLIC}
869 Public | sv8 | {"user=\"Public\""}
870 regress_testrolx | sv9 | {user=regress_testrolx}
871 (4 rows)
873 DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv6;
874 SELECT * FROM chkumapping();
875 umname | umserver | umoptions
876 ------------------+----------+-------------------------
877 | sv7 | {user=PUBLIC}
878 Public | sv8 | {"user=\"Public\""}
879 regress_testrolx | sv9 | {user=regress_testrolx}
880 (3 rows)
882 DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv7;
883 SELECT * FROM chkumapping();
884 umname | umserver | umoptions
885 ------------------+----------+-------------------------
886 Public | sv8 | {"user=\"Public\""}
887 regress_testrolx | sv9 | {user=regress_testrolx}
888 (2 rows)
890 DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv8;
891 SELECT * FROM chkumapping();
892 umname | umserver | umoptions
893 ------------------+----------+-------------------------
894 regress_testrolx | sv9 | {user=regress_testrolx}
895 (1 row)
897 DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv9;
898 SELECT * FROM chkumapping();
899 umname | umserver | umoptions
900 --------+----------+-----------
901 (0 rows)
903 DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv10; -- error
904 NOTICE: role "nonexistent" does not exist, skipping
905 -- GRANT/REVOKE
906 GRANT regress_testrol0 TO pg_signal_backend; -- success
907 SET ROLE pg_signal_backend; --success
908 RESET ROLE;
909 CREATE SCHEMA test_roles_schema AUTHORIZATION pg_signal_backend; --success
910 SET ROLE regress_testrol2;
911 UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
912 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
913 proname | proacl
914 ----------+--------
915 testagg1 |
916 testagg2 |
917 testagg3 |
918 testagg4 |
919 testagg5 |
920 testagg6 |
921 testagg7 |
922 testagg8 |
923 testagg9 |
924 testagga |
925 (10 rows)
927 REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
928 REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC;
929 REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC;
930 REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC;
931 REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC;
932 REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC;
933 REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC;
934 REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC;
935 GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC;
936 GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER;
937 GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user";
938 GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO CURRENT_ROLE;
939 GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO SESSION_USER;
940 GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO "Public";
941 GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO regress_testrolx;
942 GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2) TO "public";
943 GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2)
944 TO current_user, public, regress_testrolx;
945 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
946 proname | proacl
947 ----------+-----------------------------------------------------------------------------------------------------------------------------------
948 testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
949 testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
950 testagg3 | {regress_testrol2=X/regress_testrol2,current_user=X/regress_testrol2}
951 testagg4 | {regress_testrol1=X/regress_testrol1,regress_testrol2=X/regress_testrol1}
952 testagg5 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
953 testagg6 | {Public=X/Public}
954 testagg7 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
955 testagg8 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
956 testagg9 | {=X/regress_testrol0,regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
957 testagga |
958 (10 rows)
960 GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO USER; --error
961 ERROR: syntax error at or near "USER"
962 LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO USER;
963 ^
964 GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO NONE; --error
965 ERROR: role name "none" is reserved
966 LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO NONE;
967 ^
968 GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO "none"; --error
969 ERROR: role name "none" is reserved
970 LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagga(int2) TO "none";
971 ^
972 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
973 proname | proacl
974 ----------+-----------------------------------------------------------------------------------------------------------------------------------
975 testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
976 testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
977 testagg3 | {regress_testrol2=X/regress_testrol2,current_user=X/regress_testrol2}
978 testagg4 | {regress_testrol1=X/regress_testrol1,regress_testrol2=X/regress_testrol1}
979 testagg5 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
980 testagg6 | {Public=X/Public}
981 testagg7 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
982 testagg8 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
983 testagg9 | {=X/regress_testrol0,regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
984 testagga |
985 (10 rows)
987 REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
988 REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER;
989 REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user";
990 REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM CURRENT_ROLE;
991 REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM SESSION_USER;
992 REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM "Public";
993 REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM regress_testrolx;
994 REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM "public";
995 REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2)
996 FROM current_user, public, regress_testrolx;
997 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
998 proname | proacl
999 ----------+---------------------------------------
1000 testagg1 | {regress_testrol2=X/regress_testrol2}
1001 testagg2 | {current_user=X/current_user}
1002 testagg3 | {regress_testrol2=X/regress_testrol2}
1003 testagg4 | {regress_testrol1=X/regress_testrol1}
1004 testagg5 | {regress_testrolx=X/regress_testrolx}
1005 testagg6 | {}
1006 testagg7 | {regress_testrol0=X/regress_testrol0}
1007 testagg8 | {regress_testrol0=X/regress_testrol0}
1008 testagg9 | {regress_testrol0=X/regress_testrol0}
1009 testagga |
1010 (10 rows)
1012 REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM USER; --error
1013 ERROR: syntax error at or near "USER"
1014 LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM USER;
1015 ^
1016 REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM NONE; --error
1017 ERROR: role name "none" is reserved
1018 LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM NONE;
1019 ^
1020 REVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM "none"; --error
1021 ERROR: role name "none" is reserved
1022 LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagga(int2) FROM "none";
1023 ^
1024 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
1025 proname | proacl
1026 ----------+---------------------------------------
1027 testagg1 | {regress_testrol2=X/regress_testrol2}
1028 testagg2 | {current_user=X/current_user}
1029 testagg3 | {regress_testrol2=X/regress_testrol2}
1030 testagg4 | {regress_testrol1=X/regress_testrol1}
1031 testagg5 | {regress_testrolx=X/regress_testrolx}
1032 testagg6 | {}
1033 testagg7 | {regress_testrol0=X/regress_testrol0}
1034 testagg8 | {regress_testrol0=X/regress_testrol0}
1035 testagg9 | {regress_testrol0=X/regress_testrol0}
1036 testagga |
1037 (10 rows)
1039 -- DEFAULT MONITORING ROLES
1040 CREATE ROLE regress_role_haspriv;
1041 CREATE ROLE regress_role_nopriv;
1042 -- pg_read_all_stats
1043 GRANT pg_read_all_stats TO regress_role_haspriv;
1044 SET SESSION AUTHORIZATION regress_role_haspriv;
1045 -- returns true with role member of pg_read_all_stats
1046 SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
1047 WHERE query = '<insufficient privilege>';
1048 haspriv
1049 ---------
1050 t
1051 (1 row)
1053 SET SESSION AUTHORIZATION regress_role_nopriv;
1054 -- returns false with role not member of pg_read_all_stats
1055 SELECT COUNT(*) = 0 AS haspriv FROM pg_stat_activity
1056 WHERE query = '<insufficient privilege>';
1057 haspriv
1058 ---------
1059 f
1060 (1 row)
1062 RESET SESSION AUTHORIZATION;
1063 REVOKE pg_read_all_stats FROM regress_role_haspriv;
1064 -- pg_read_all_settings
1065 GRANT pg_read_all_settings TO regress_role_haspriv;
1066 BEGIN;
1067 -- A GUC using GUC_SUPERUSER_ONLY is useful for negative tests.
1068 SET LOCAL session_preload_libraries TO 'path-to-preload-libraries';
1069 SET SESSION AUTHORIZATION regress_role_haspriv;
1070 -- passes with role member of pg_read_all_settings
1071 SHOW session_preload_libraries;
1072 session_preload_libraries
1073 -----------------------------
1074 "path-to-preload-libraries"
1075 (1 row)
1077 SET SESSION AUTHORIZATION regress_role_nopriv;
1078 -- fails with role not member of pg_read_all_settings
1079 SHOW session_preload_libraries;
1080 ERROR: permission denied to examine "session_preload_libraries"
1081 DETAIL: Only roles with privileges of the "pg_read_all_settings" role may examine this parameter.
1082 RESET SESSION AUTHORIZATION;
1083 ERROR: current transaction is aborted, commands ignored until end of transaction block
1084 ROLLBACK;
1085 REVOKE pg_read_all_settings FROM regress_role_haspriv;
1086 -- clean up
1087 \c
1088 DROP SCHEMA test_roles_schema;
1089 DROP OWNED BY regress_testrol0, "Public", "current_role", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE;
1090 DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
1091 DROP ROLE "Public", "None", "current_role", "current_user", "session_user", "user";
1092 DROP ROLE regress_role_haspriv, regress_role_nopriv;