1 CREATE OR REPLACE FUNCTION chkrolattr()
2 RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool)
4 SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication
6 JOIN (VALUES(CURRENT_USER, 'current_user'),
7 (SESSION_USER, 'session_user'),
13 ON (r.rolname = v.uname)
16 CREATE OR REPLACE FUNCTION chksetconfig()
17 RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[])
19 SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'),
20 COALESCE(v.keyword, '-'), s.setconfig
21 FROM pg_db_role_setting s
22 LEFT JOIN pg_roles r ON (r.oid = s.setrole)
23 LEFT JOIN pg_database d ON (d.oid = s.setdatabase)
24 LEFT JOIN (VALUES(CURRENT_USER, 'current_user'),
25 (SESSION_USER, 'session_user'))
27 ON (r.rolname = v.uname)
28 WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2')
31 CREATE OR REPLACE FUNCTION chkumapping()
32 RETURNS TABLE (umname name, umserver name, umoptions text[])
34 SELECT r.rolname, s.srvname, m.umoptions
35 FROM pg_user_mapping m
36 LEFT JOIN pg_roles r ON (r.oid = m.umuser)
37 JOIN pg_foreign_server s ON (s.oid = m.umserver)
42 CREATE ROLE "current_user";
43 CREATE ROLE "session_user";
45 CREATE ROLE current_user; -- error
46 ERROR: CURRENT_USER cannot be used as a role name here
47 LINE 1: CREATE ROLE current_user;
49 CREATE ROLE current_role; -- error
50 ERROR: syntax error at or near "current_role"
51 LINE 1: CREATE ROLE current_role;
53 CREATE ROLE session_user; -- error
54 ERROR: SESSION_USER cannot be used as a role name here
55 LINE 1: CREATE ROLE session_user;
57 CREATE ROLE user; -- error
58 ERROR: syntax error at or near "user"
59 LINE 1: CREATE ROLE user;
61 CREATE ROLE all; -- error
62 ERROR: syntax error at or near "all"
63 LINE 1: CREATE ROLE all;
65 CREATE ROLE public; -- error
66 ERROR: role name "public" is reserved
67 LINE 1: CREATE ROLE public;
69 CREATE ROLE "public"; -- error
70 ERROR: role name "public" is reserved
71 LINE 1: CREATE ROLE "public";
73 CREATE ROLE none; -- error
74 ERROR: role name "none" is reserved
75 LINE 1: CREATE ROLE none;
77 CREATE ROLE "none"; -- error
78 ERROR: role name "none" is reserved
79 LINE 1: CREATE ROLE "none";
81 CREATE ROLE pg_abc; -- error
82 ERROR: role name "pg_abc" is reserved
83 DETAIL: Role names starting with "pg_" are reserved.
84 CREATE ROLE "pg_abc"; -- error
85 ERROR: role name "pg_abc" is reserved
86 DETAIL: Role names starting with "pg_" are reserved.
87 CREATE ROLE pg_abcdef; -- error
88 ERROR: role name "pg_abcdef" is reserved
89 DETAIL: Role names starting with "pg_" are reserved.
90 CREATE ROLE "pg_abcdef"; -- error
91 ERROR: role name "pg_abcdef" is reserved
92 DETAIL: Role names starting with "pg_" are reserved.
93 CREATE ROLE regress_testrol0 SUPERUSER LOGIN;
94 CREATE ROLE regress_testrolx SUPERUSER LOGIN;
95 CREATE ROLE regress_testrol2 SUPERUSER;
96 CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2;
98 SET SESSION AUTHORIZATION regress_testrol1;
99 SET ROLE regress_testrol2;
102 SELECT * FROM chkrolattr();
103 role | rolekeyword | canlogin | replication
104 ------------------+--------------+----------+-------------
107 current_user | - | f | f
108 regress_testrol1 | session_user | t | f
109 regress_testrol2 | current_user | f | f
110 session_user | - | f | f
113 ALTER ROLE CURRENT_USER WITH REPLICATION;
114 SELECT * FROM chkrolattr();
115 role | rolekeyword | canlogin | replication
116 ------------------+--------------+----------+-------------
119 current_user | - | f | f
120 regress_testrol1 | session_user | t | f
121 regress_testrol2 | current_user | f | t
122 session_user | - | f | f
125 ALTER ROLE "current_user" WITH REPLICATION;
126 SELECT * FROM chkrolattr();
127 role | rolekeyword | canlogin | replication
128 ------------------+--------------+----------+-------------
131 current_user | - | f | t
132 regress_testrol1 | session_user | t | f
133 regress_testrol2 | current_user | f | t
134 session_user | - | f | f
137 ALTER ROLE SESSION_USER WITH REPLICATION;
138 SELECT * FROM chkrolattr();
139 role | rolekeyword | canlogin | replication
140 ------------------+--------------+----------+-------------
143 current_user | - | f | t
144 regress_testrol1 | session_user | t | t
145 regress_testrol2 | current_user | f | t
146 session_user | - | f | f
149 ALTER ROLE "session_user" WITH REPLICATION;
150 SELECT * FROM chkrolattr();
151 role | rolekeyword | canlogin | replication
152 ------------------+--------------+----------+-------------
155 current_user | - | f | t
156 regress_testrol1 | session_user | t | t
157 regress_testrol2 | current_user | f | t
158 session_user | - | f | t
161 ALTER USER "Public" WITH REPLICATION;
162 ALTER USER "None" WITH REPLICATION;
163 SELECT * FROM chkrolattr();
164 role | rolekeyword | canlogin | replication
165 ------------------+--------------+----------+-------------
168 current_user | - | f | t
169 regress_testrol1 | session_user | t | t
170 regress_testrol2 | current_user | f | t
171 session_user | - | f | t
174 ALTER USER regress_testrol1 WITH NOREPLICATION;
175 ALTER USER regress_testrol2 WITH NOREPLICATION;
176 SELECT * FROM chkrolattr();
177 role | rolekeyword | canlogin | replication
178 ------------------+--------------+----------+-------------
181 current_user | - | f | t
182 regress_testrol1 | session_user | t | f
183 regress_testrol2 | current_user | f | f
184 session_user | - | f | t
188 ALTER ROLE USER WITH LOGIN; -- error
189 ERROR: syntax error at or near "USER"
190 LINE 1: ALTER ROLE USER WITH LOGIN;
192 ALTER ROLE CURRENT_ROLE WITH LOGIN; --error
193 ERROR: syntax error at or near "CURRENT_ROLE"
194 LINE 1: ALTER ROLE CURRENT_ROLE WITH LOGIN;
196 ALTER ROLE ALL WITH REPLICATION; -- error
197 ERROR: syntax error at or near "WITH"
198 LINE 1: ALTER ROLE ALL WITH REPLICATION;
200 ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error
201 ERROR: role "session_role" does not exist
202 ALTER ROLE PUBLIC WITH NOREPLICATION; -- error
203 ERROR: role "public" does not exist
204 ALTER ROLE "public" WITH NOREPLICATION; -- error
205 ERROR: role "public" does not exist
206 ALTER ROLE NONE WITH NOREPLICATION; -- error
207 ERROR: role name "none" is reserved
208 LINE 1: ALTER ROLE NONE WITH NOREPLICATION;
210 ALTER ROLE "none" WITH NOREPLICATION; -- error
211 ERROR: role name "none" is reserved
212 LINE 1: ALTER ROLE "none" WITH NOREPLICATION;
214 ALTER ROLE nonexistent WITH NOREPLICATION; -- error
215 ERROR: role "nonexistent" does not exist
218 SELECT * FROM chkrolattr();
219 role | rolekeyword | canlogin | replication
220 ------------------+--------------+----------+-------------
223 current_user | - | f | f
224 regress_testrol1 | session_user | t | f
225 regress_testrol2 | current_user | f | f
226 session_user | - | f | f
229 ALTER USER CURRENT_USER WITH REPLICATION;
230 SELECT * FROM chkrolattr();
231 role | rolekeyword | canlogin | replication
232 ------------------+--------------+----------+-------------
235 current_user | - | f | f
236 regress_testrol1 | session_user | t | f
237 regress_testrol2 | current_user | f | t
238 session_user | - | f | f
241 ALTER USER "current_user" WITH REPLICATION;
242 SELECT * FROM chkrolattr();
243 role | rolekeyword | canlogin | replication
244 ------------------+--------------+----------+-------------
247 current_user | - | f | t
248 regress_testrol1 | session_user | t | f
249 regress_testrol2 | current_user | f | t
250 session_user | - | f | f
253 ALTER USER SESSION_USER WITH REPLICATION;
254 SELECT * FROM chkrolattr();
255 role | rolekeyword | canlogin | replication
256 ------------------+--------------+----------+-------------
259 current_user | - | f | t
260 regress_testrol1 | session_user | t | t
261 regress_testrol2 | current_user | f | t
262 session_user | - | f | f
265 ALTER USER "session_user" WITH REPLICATION;
266 SELECT * FROM chkrolattr();
267 role | rolekeyword | canlogin | replication
268 ------------------+--------------+----------+-------------
271 current_user | - | f | t
272 regress_testrol1 | session_user | t | t
273 regress_testrol2 | current_user | f | t
274 session_user | - | f | t
277 ALTER USER "Public" WITH REPLICATION;
278 ALTER USER "None" WITH REPLICATION;
279 SELECT * FROM chkrolattr();
280 role | rolekeyword | canlogin | replication
281 ------------------+--------------+----------+-------------
284 current_user | - | f | t
285 regress_testrol1 | session_user | t | t
286 regress_testrol2 | current_user | f | t
287 session_user | - | f | t
290 ALTER USER regress_testrol1 WITH NOREPLICATION;
291 ALTER USER regress_testrol2 WITH NOREPLICATION;
292 SELECT * FROM chkrolattr();
293 role | rolekeyword | canlogin | replication
294 ------------------+--------------+----------+-------------
297 current_user | - | f | t
298 regress_testrol1 | session_user | t | f
299 regress_testrol2 | current_user | f | f
300 session_user | - | f | t
304 ALTER USER USER WITH LOGIN; -- error
305 ERROR: syntax error at or near "USER"
306 LINE 1: ALTER USER USER WITH LOGIN;
308 ALTER USER CURRENT_ROLE WITH LOGIN; -- error
309 ERROR: syntax error at or near "CURRENT_ROLE"
310 LINE 1: ALTER USER CURRENT_ROLE WITH LOGIN;
312 ALTER USER ALL WITH REPLICATION; -- error
313 ERROR: syntax error at or near "WITH"
314 LINE 1: ALTER USER ALL WITH REPLICATION;
316 ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error
317 ERROR: role "session_role" does not exist
318 ALTER USER PUBLIC WITH NOREPLICATION; -- error
319 ERROR: role "public" does not exist
320 ALTER USER "public" WITH NOREPLICATION; -- error
321 ERROR: role "public" does not exist
322 ALTER USER NONE WITH NOREPLICATION; -- error
323 ERROR: role name "none" is reserved
324 LINE 1: ALTER USER NONE WITH NOREPLICATION;
326 ALTER USER "none" WITH NOREPLICATION; -- error
327 ERROR: role name "none" is reserved
328 LINE 1: ALTER USER "none" WITH NOREPLICATION;
330 ALTER USER nonexistent WITH NOREPLICATION; -- error
331 ERROR: role "nonexistent" does not exist
332 -- ALTER ROLE SET/RESET
333 SELECT * FROM chksetconfig();
334 db | role | rolkeyword | setconfig
335 ----+------+------------+-----------
338 ALTER ROLE CURRENT_USER SET application_name to 'FOO';
339 ALTER ROLE SESSION_USER SET application_name to 'BAR';
340 ALTER ROLE "current_user" SET application_name to 'FOOFOO';
341 ALTER ROLE "Public" SET application_name to 'BARBAR';
342 ALTER ROLE ALL SET application_name to 'SLAP';
343 SELECT * FROM chksetconfig();
344 db | role | rolkeyword | setconfig
345 -----+------------------+--------------+---------------------------
346 ALL | Public | - | {application_name=BARBAR}
347 ALL | current_user | - | {application_name=FOOFOO}
348 ALL | regress_testrol1 | session_user | {application_name=BAR}
349 ALL | regress_testrol2 | current_user | {application_name=FOO}
352 ALTER ROLE regress_testrol1 SET application_name to 'SLAM';
353 SELECT * FROM chksetconfig();
354 db | role | rolkeyword | setconfig
355 -----+------------------+--------------+---------------------------
356 ALL | Public | - | {application_name=BARBAR}
357 ALL | current_user | - | {application_name=FOOFOO}
358 ALL | regress_testrol1 | session_user | {application_name=SLAM}
359 ALL | regress_testrol2 | current_user | {application_name=FOO}
362 ALTER ROLE CURRENT_USER RESET application_name;
363 ALTER ROLE SESSION_USER RESET application_name;
364 ALTER ROLE "current_user" RESET application_name;
365 ALTER ROLE "Public" RESET application_name;
366 ALTER ROLE ALL RESET application_name;
367 SELECT * FROM chksetconfig();
368 db | role | rolkeyword | setconfig
369 ----+------+------------+-----------
372 ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error
373 ERROR: syntax error at or near "CURRENT_ROLE"
374 LINE 1: ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ';
376 ALTER ROLE USER SET application_name to 'BOOM'; -- error
377 ERROR: syntax error at or near "USER"
378 LINE 1: ALTER ROLE USER SET application_name to 'BOOM';
380 ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error
381 ERROR: role "public" does not exist
382 ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error
383 ERROR: role "nonexistent" does not exist
384 -- ALTER USER SET/RESET
385 SELECT * FROM chksetconfig();
386 db | role | rolkeyword | setconfig
387 ----+------+------------+-----------
390 ALTER USER CURRENT_USER SET application_name to 'FOO';
391 ALTER USER SESSION_USER SET application_name to 'BAR';
392 ALTER USER "current_user" SET application_name to 'FOOFOO';
393 ALTER USER "Public" SET application_name to 'BARBAR';
394 ALTER USER ALL SET application_name to 'SLAP';
395 SELECT * FROM chksetconfig();
396 db | role | rolkeyword | setconfig
397 -----+------------------+--------------+---------------------------
398 ALL | Public | - | {application_name=BARBAR}
399 ALL | current_user | - | {application_name=FOOFOO}
400 ALL | regress_testrol1 | session_user | {application_name=BAR}
401 ALL | regress_testrol2 | current_user | {application_name=FOO}
404 ALTER USER regress_testrol1 SET application_name to 'SLAM';
405 SELECT * FROM chksetconfig();
406 db | role | rolkeyword | setconfig
407 -----+------------------+--------------+---------------------------
408 ALL | Public | - | {application_name=BARBAR}
409 ALL | current_user | - | {application_name=FOOFOO}
410 ALL | regress_testrol1 | session_user | {application_name=SLAM}
411 ALL | regress_testrol2 | current_user | {application_name=FOO}
414 ALTER USER CURRENT_USER RESET application_name;
415 ALTER USER SESSION_USER RESET application_name;
416 ALTER USER "current_user" RESET application_name;
417 ALTER USER "Public" RESET application_name;
418 ALTER USER ALL RESET application_name;
419 SELECT * FROM chksetconfig();
420 db | role | rolkeyword | setconfig
421 ----+------+------------+-----------
424 ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error
425 ALTER USER USER SET application_name to 'BOOM'; -- error
426 ERROR: syntax error at or near "USER"
427 LINE 1: ALTER USER USER SET application_name to 'BOOM';
429 ALTER USER PUBLIC SET application_name to 'BOMB'; -- error
430 ERROR: role "public" does not exist
431 ALTER USER NONE SET application_name to 'BOMB'; -- error
432 ERROR: role name "none" is reserved
433 LINE 1: ALTER USER NONE SET application_name to 'BOMB';
435 ALTER USER nonexistent SET application_name to 'BOMB'; -- error
436 ERROR: role "nonexistent" does not exist
438 set client_min_messages to error;
439 CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER;
440 CREATE SCHEMA newschema2 AUTHORIZATION "current_user";
441 CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER;
442 CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx;
443 CREATE SCHEMA newschema5 AUTHORIZATION "Public";
444 CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error
445 ERROR: syntax error at or near "USER"
446 LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION USER;
448 CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error
449 ERROR: syntax error at or near "CURRENT_ROLE"
450 LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE;
452 CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error
453 ERROR: role "public" does not exist
454 CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error
455 ERROR: role "public" does not exist
456 CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error
457 ERROR: role name "none" is reserved
458 LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION NONE;
460 CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error
461 ERROR: role "nonexistent" does not exist
462 SELECT n.nspname, r.rolname FROM pg_namespace n
463 JOIN pg_roles r ON (r.oid = n.nspowner)
464 WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
466 ------------+------------------
467 newschema1 | regress_testrol2
468 newschema2 | current_user
469 newschema3 | regress_testrol1
470 newschema4 | regress_testrolx
474 CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER;
475 CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user";
476 CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER;
477 CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx;
478 CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public";
479 CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error
480 ERROR: syntax error at or near "USER"
481 LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER;
483 CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error
484 ERROR: syntax error at or near "CURRENT_ROLE"
485 LINE 1: ...ATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_RO...
487 CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error
488 ERROR: role "public" does not exist
489 CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error
490 ERROR: role "public" does not exist
491 CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error
492 ERROR: role name "none" is reserved
493 LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE;
495 CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error
496 ERROR: role "nonexistent" does not exist
497 SELECT n.nspname, r.rolname FROM pg_namespace n
498 JOIN pg_roles r ON (r.oid = n.nspowner)
499 WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
501 ------------+------------------
502 newschema1 | regress_testrol2
503 newschema2 | current_user
504 newschema3 | regress_testrol1
505 newschema4 | regress_testrolx
509 -- ALTER TABLE OWNER TO
511 SET SESSION AUTHORIZATION regress_testrol0;
512 set client_min_messages to error;
513 CREATE TABLE testtab1 (a int);
514 CREATE TABLE testtab2 (a int);
515 CREATE TABLE testtab3 (a int);
516 CREATE TABLE testtab4 (a int);
517 CREATE TABLE testtab5 (a int);
518 CREATE TABLE testtab6 (a int);
520 SET SESSION AUTHORIZATION regress_testrol1;
521 SET ROLE regress_testrol2;
522 ALTER TABLE testtab1 OWNER TO CURRENT_USER;
523 ALTER TABLE testtab2 OWNER TO "current_user";
524 ALTER TABLE testtab3 OWNER TO SESSION_USER;
525 ALTER TABLE testtab4 OWNER TO regress_testrolx;
526 ALTER TABLE testtab5 OWNER TO "Public";
527 ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error
528 ERROR: syntax error at or near "CURRENT_ROLE"
529 LINE 1: ALTER TABLE testtab6 OWNER TO CURRENT_ROLE;
531 ALTER TABLE testtab6 OWNER TO USER; --error
532 ERROR: syntax error at or near "USER"
533 LINE 1: ALTER TABLE testtab6 OWNER TO USER;
535 ALTER TABLE testtab6 OWNER TO PUBLIC; -- error
536 ERROR: role "public" does not exist
537 ALTER TABLE testtab6 OWNER TO "public"; -- error
538 ERROR: role "public" does not exist
539 ALTER TABLE testtab6 OWNER TO nonexistent; -- error
540 ERROR: role "nonexistent" does not exist
541 SELECT c.relname, r.rolname
542 FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner)
543 WHERE relname LIKE 'testtab_'
546 ----------+------------------
547 testtab1 | regress_testrol2
548 testtab2 | current_user
549 testtab3 | regress_testrol1
550 testtab4 | regress_testrolx
552 testtab6 | regress_testrol0
555 -- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are
556 -- changed their owner in the same way.
559 SET SESSION AUTHORIZATION regress_testrol0;
560 CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8);
561 CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8);
562 CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8);
563 CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8);
564 CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
565 CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
566 ERROR: function "testagg5" already exists with same argument types
567 CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8);
568 CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8);
569 CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8);
570 CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8);
572 SET SESSION AUTHORIZATION regress_testrol1;
573 SET ROLE regress_testrol2;
574 ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER;
575 ALTER AGGREGATE testagg2(int2) OWNER TO "current_user";
576 ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER;
577 ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx;
578 ALTER AGGREGATE testagg5(int2) OWNER TO "Public";
579 ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error
580 ERROR: syntax error at or near "CURRENT_ROLE"
581 LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE;
583 ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error
584 ERROR: syntax error at or near "USER"
585 LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO USER;
587 ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error
588 ERROR: role "public" does not exist
589 ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error
590 ERROR: role "public" does not exist
591 ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error
592 ERROR: role "nonexistent" does not exist
593 SELECT p.proname, r.rolname
594 FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner)
595 WHERE proname LIKE 'testagg_'
598 ----------+------------------
599 testagg1 | regress_testrol2
600 testagg2 | current_user
601 testagg3 | regress_testrol1
602 testagg4 | regress_testrolx
604 testagg6 | regress_testrol0
605 testagg7 | regress_testrol0
606 testagg8 | regress_testrol0
607 testagg9 | regress_testrol0
610 -- CREATE USER MAPPING
611 CREATE FOREIGN DATA WRAPPER test_wrapper;
612 CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper;
613 CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper;
614 CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper;
615 CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper;
616 CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper;
617 CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper;
618 CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper;
619 CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper;
620 CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper;
621 CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
622 CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
623 CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
624 CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
625 CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
626 CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
627 CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
628 CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
629 CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
630 OPTIONS (user 'CURRENT_ROLE'); -- error
631 ERROR: syntax error at or near "CURRENT_ROLE"
632 LINE 1: CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
634 CREATE USER MAPPING FOR nonexistent SERVER sv9
635 OPTIONS (user 'nonexistent'); -- error;
636 ERROR: role "nonexistent" does not exist
637 SELECT * FROM chkumapping();
638 umname | umserver | umoptions
639 ------------------+----------+---------------------------
640 regress_testrol2 | sv1 | {user=CURRENT_USER}
641 current_user | sv2 | {"user=\"current_user\""}
642 regress_testrol2 | sv3 | {user=USER}
643 user | sv4 | {"user=\"USER\""}
644 regress_testrol1 | sv5 | {user=SESSION_USER}
645 | sv6 | {user=PUBLIC}
646 Public | sv7 | {"user=\"Public\""}
647 regress_testrolx | sv8 | {user=regress_testrolx}
650 -- ALTER USER MAPPING
651 ALTER USER MAPPING FOR CURRENT_USER SERVER sv1
652 OPTIONS (SET user 'CURRENT_USER_alt');
653 ALTER USER MAPPING FOR "current_user" SERVER sv2
654 OPTIONS (SET user '"current_user"_alt');
655 ALTER USER MAPPING FOR USER SERVER sv3
656 OPTIONS (SET user 'USER_alt');
657 ALTER USER MAPPING FOR "user" SERVER sv4
658 OPTIONS (SET user '"user"_alt');
659 ALTER USER MAPPING FOR SESSION_USER SERVER sv5
660 OPTIONS (SET user 'SESSION_USER_alt');
661 ALTER USER MAPPING FOR PUBLIC SERVER sv6
662 OPTIONS (SET user 'public_alt');
663 ALTER USER MAPPING FOR "Public" SERVER sv7
664 OPTIONS (SET user '"Public"_alt');
665 ALTER USER MAPPING FOR regress_testrolx SERVER sv8
666 OPTIONS (SET user 'regress_testrolx_alt');
667 ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
668 OPTIONS (SET user 'CURRENT_ROLE_alt');
669 ERROR: syntax error at or near "CURRENT_ROLE"
670 LINE 1: ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
672 ALTER USER MAPPING FOR nonexistent SERVER sv9
673 OPTIONS (SET user 'nonexistent_alt'); -- error
674 ERROR: role "nonexistent" does not exist
675 SELECT * FROM chkumapping();
676 umname | umserver | umoptions
677 ------------------+----------+-------------------------------
678 regress_testrol2 | sv1 | {user=CURRENT_USER_alt}
679 current_user | sv2 | {"user=\"current_user\"_alt"}
680 regress_testrol2 | sv3 | {user=USER_alt}
681 user | sv4 | {"user=\"user\"_alt"}
682 regress_testrol1 | sv5 | {user=SESSION_USER_alt}
683 | sv6 | {user=public_alt}
684 Public | sv7 | {"user=\"Public\"_alt"}
685 regress_testrolx | sv8 | {user=regress_testrolx_alt}
689 DROP USER MAPPING FOR CURRENT_USER SERVER sv1;
690 DROP USER MAPPING FOR "current_user" SERVER sv2;
691 DROP USER MAPPING FOR USER SERVER sv3;
692 DROP USER MAPPING FOR "user" SERVER sv4;
693 DROP USER MAPPING FOR SESSION_USER SERVER sv5;
694 DROP USER MAPPING FOR PUBLIC SERVER sv6;
695 DROP USER MAPPING FOR "Public" SERVER sv7;
696 DROP USER MAPPING FOR regress_testrolx SERVER sv8;
697 DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error
698 ERROR: syntax error at or near "CURRENT_ROLE"
699 LINE 1: DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9;
701 DROP USER MAPPING FOR nonexistent SERVER sv; -- error
702 ERROR: role "nonexistent" does not exist
703 SELECT * FROM chkumapping();
704 umname | umserver | umoptions
705 --------+----------+-----------
708 CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
709 CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
710 CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
711 CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
712 CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
713 CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
714 CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
715 CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
716 SELECT * FROM chkumapping();
717 umname | umserver | umoptions
718 ------------------+----------+---------------------------
719 regress_testrol2 | sv1 | {user=CURRENT_USER}
720 current_user | sv2 | {"user=\"current_user\""}
721 regress_testrol2 | sv3 | {user=USER}
722 user | sv4 | {"user=\"USER\""}
723 regress_testrol1 | sv5 | {user=SESSION_USER}
724 | sv6 | {user=PUBLIC}
725 Public | sv7 | {"user=\"Public\""}
726 regress_testrolx | sv8 | {user=regress_testrolx}
729 -- DROP USER MAPPING IF EXISTS
730 DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1;
731 SELECT * FROM chkumapping();
732 umname | umserver | umoptions
733 ------------------+----------+---------------------------
734 current_user | sv2 | {"user=\"current_user\""}
735 regress_testrol2 | sv3 | {user=USER}
736 user | sv4 | {"user=\"USER\""}
737 regress_testrol1 | sv5 | {user=SESSION_USER}
738 | sv6 | {user=PUBLIC}
739 Public | sv7 | {"user=\"Public\""}
740 regress_testrolx | sv8 | {user=regress_testrolx}
743 DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2;
744 SELECT * FROM chkumapping();
745 umname | umserver | umoptions
746 ------------------+----------+-------------------------
747 regress_testrol2 | sv3 | {user=USER}
748 user | sv4 | {"user=\"USER\""}
749 regress_testrol1 | sv5 | {user=SESSION_USER}
750 | sv6 | {user=PUBLIC}
751 Public | sv7 | {"user=\"Public\""}
752 regress_testrolx | sv8 | {user=regress_testrolx}
755 DROP USER MAPPING IF EXISTS FOR USER SERVER sv3;
756 SELECT * FROM chkumapping();
757 umname | umserver | umoptions
758 ------------------+----------+-------------------------
759 user | sv4 | {"user=\"USER\""}
760 regress_testrol1 | sv5 | {user=SESSION_USER}
761 | sv6 | {user=PUBLIC}
762 Public | sv7 | {"user=\"Public\""}
763 regress_testrolx | sv8 | {user=regress_testrolx}
766 DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4;
767 SELECT * FROM chkumapping();
768 umname | umserver | umoptions
769 ------------------+----------+-------------------------
770 regress_testrol1 | sv5 | {user=SESSION_USER}
771 | sv6 | {user=PUBLIC}
772 Public | sv7 | {"user=\"Public\""}
773 regress_testrolx | sv8 | {user=regress_testrolx}
776 DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5;
777 SELECT * FROM chkumapping();
778 umname | umserver | umoptions
779 ------------------+----------+-------------------------
780 | sv6 | {user=PUBLIC}
781 Public | sv7 | {"user=\"Public\""}
782 regress_testrolx | sv8 | {user=regress_testrolx}
785 DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6;
786 SELECT * FROM chkumapping();
787 umname | umserver | umoptions
788 ------------------+----------+-------------------------
789 Public | sv7 | {"user=\"Public\""}
790 regress_testrolx | sv8 | {user=regress_testrolx}
793 DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7;
794 SELECT * FROM chkumapping();
795 umname | umserver | umoptions
796 ------------------+----------+-------------------------
797 regress_testrolx | sv8 | {user=regress_testrolx}
800 DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8;
801 SELECT * FROM chkumapping();
802 umname | umserver | umoptions
803 --------+----------+-----------
806 DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error
807 ERROR: syntax error at or near "CURRENT_ROLE"
808 LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9;
810 DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9; -- error
811 NOTICE: role "nonexistent" does not exist, skipping
813 GRANT regress_testrol0 TO pg_signal_backend; -- success
814 SET ROLE pg_signal_backend; --success
816 CREATE SCHEMA test_schema AUTHORIZATION pg_signal_backend; --success
817 SET ROLE regress_testrol2;
818 UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
819 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
833 REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
834 REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC;
835 REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC;
836 REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC;
837 REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC;
838 REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC;
839 REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC;
840 REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC;
841 GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC;
842 GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER;
843 GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user";
844 GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER;
845 GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public";
846 GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx;
847 GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public";
848 GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2)
849 TO current_user, public, regress_testrolx;
850 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
852 ----------+-----------------------------------------------------------------------------------------------------------------------------------
853 testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
854 testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
855 testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1}
856 testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
857 testagg5 | {Public=X/Public}
858 testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
859 testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
860 testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
864 GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error
865 ERROR: syntax error at or near "CURRENT_ROLE"
866 LINE 1: ...RANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_RO...
868 GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error
869 ERROR: syntax error at or near "USER"
870 LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER;
872 GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error
873 ERROR: role name "none" is reserved
874 LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE;
876 GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error
877 ERROR: role name "none" is reserved
878 LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none";
880 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
882 ----------+-----------------------------------------------------------------------------------------------------------------------------------
883 testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
884 testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
885 testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1}
886 testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
887 testagg5 | {Public=X/Public}
888 testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
889 testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
890 testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
894 REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
895 REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER;
896 REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user";
897 REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER;
898 REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public";
899 REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx;
900 REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public";
901 REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2)
902 FROM current_user, public, regress_testrolx;
903 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
905 ----------+---------------------------------------
906 testagg1 | {regress_testrol2=X/regress_testrol2}
907 testagg2 | {current_user=X/current_user}
908 testagg3 | {regress_testrol1=X/regress_testrol1}
909 testagg4 | {regress_testrolx=X/regress_testrolx}
911 testagg6 | {regress_testrol0=X/regress_testrol0}
912 testagg7 | {regress_testrol0=X/regress_testrol0}
913 testagg8 | {regress_testrol0=X/regress_testrol0}
917 REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error
918 ERROR: syntax error at or near "CURRENT_ROLE"
919 LINE 1: ...KE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_RO...
921 REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error
922 ERROR: syntax error at or near "USER"
923 LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER;
925 REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error
926 ERROR: role name "none" is reserved
927 LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE;
929 REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error
930 ERROR: role name "none" is reserved
931 LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none";
933 SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
935 ----------+---------------------------------------
936 testagg1 | {regress_testrol2=X/regress_testrol2}
937 testagg2 | {current_user=X/current_user}
938 testagg3 | {regress_testrol1=X/regress_testrol1}
939 testagg4 | {regress_testrolx=X/regress_testrolx}
941 testagg6 | {regress_testrol0=X/regress_testrol0}
942 testagg7 | {regress_testrol0=X/regress_testrol0}
943 testagg8 | {regress_testrol0=X/regress_testrol0}
949 DROP SCHEMA test_schema;
950 DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE;
951 DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
952 DROP ROLE "Public", "None", "current_user", "session_user", "user";