path: root/src/test/regress/expected/rolenames.out

CREATE OR REPLACE FUNCTION chkrolattr()
 RETURNS TABLE ("role" name, rolekeyword text, canlogin bool, replication bool)
 AS $$
SELECT r.rolname, v.keyword, r.rolcanlogin, r.rolreplication
 FROM pg_roles r
 JOIN (VALUES(CURRENT_USER, 'current_user'),
             (SESSION_USER, 'session_user'),
             ('current_user', '-'),
             ('session_user', '-'),
             ('Public', '-'),
             ('None', '-'))
      AS v(uname, keyword)
      ON (r.rolname = v.uname)
 ORDER BY 1;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION chksetconfig()
 RETURNS TABLE (db name, "role" name, rolkeyword text, setconfig text[])
 AS $$
SELECT COALESCE(d.datname, 'ALL'), COALESCE(r.rolname, 'ALL'),
	   COALESCE(v.keyword, '-'), s.setconfig
 FROM pg_db_role_setting s
 LEFT JOIN pg_roles r ON (r.oid = s.setrole)
 LEFT JOIN pg_database d ON (d.oid = s.setdatabase)
 LEFT JOIN (VALUES(CURRENT_USER, 'current_user'),
             (SESSION_USER, 'session_user'))
      AS v(uname, keyword)
      ON (r.rolname = v.uname)
   WHERE (r.rolname) IN ('Public', 'current_user', 'regress_testrol1', 'regress_testrol2')
ORDER BY 1, 2;
$$ LANGUAGE SQL;
CREATE OR REPLACE FUNCTION chkumapping()
 RETURNS TABLE (umname name, umserver name, umoptions text[])
 AS $$
SELECT r.rolname, s.srvname, m.umoptions
 FROM pg_user_mapping m
 LEFT JOIN pg_roles r ON (r.oid = m.umuser)
 JOIN pg_foreign_server s ON (s.oid = m.umserver)
 ORDER BY 2;
$$ LANGUAGE SQL;
CREATE ROLE "Public";
CREATE ROLE "None";
CREATE ROLE "current_user";
CREATE ROLE "session_user";
CREATE ROLE "user";
CREATE ROLE current_user; -- error
ERROR:  CURRENT_USER cannot be used as a role name here
LINE 1: CREATE ROLE current_user;
                    ^
CREATE ROLE current_role; -- error
ERROR:  syntax error at or near "current_role"
LINE 1: CREATE ROLE current_role;
                    ^
CREATE ROLE session_user; -- error
ERROR:  SESSION_USER cannot be used as a role name here
LINE 1: CREATE ROLE session_user;
                    ^
CREATE ROLE user; -- error
ERROR:  syntax error at or near "user"
LINE 1: CREATE ROLE user;
                    ^
CREATE ROLE all; -- error
ERROR:  syntax error at or near "all"
LINE 1: CREATE ROLE all;
                    ^
CREATE ROLE public; -- error
ERROR:  role name "public" is reserved
LINE 1: CREATE ROLE public;
                    ^
CREATE ROLE "public"; -- error
ERROR:  role name "public" is reserved
LINE 1: CREATE ROLE "public";
                    ^
CREATE ROLE none; -- error
ERROR:  role name "none" is reserved
LINE 1: CREATE ROLE none;
                    ^
CREATE ROLE "none"; -- error
ERROR:  role name "none" is reserved
LINE 1: CREATE ROLE "none";
                    ^
CREATE ROLE pg_abc; -- error
ERROR:  role name "pg_abc" is reserved
DETAIL:  Role names starting with "pg_" are reserved.
CREATE ROLE "pg_abc"; -- error
ERROR:  role name "pg_abc" is reserved
DETAIL:  Role names starting with "pg_" are reserved.
CREATE ROLE pg_abcdef; -- error
ERROR:  role name "pg_abcdef" is reserved
DETAIL:  Role names starting with "pg_" are reserved.
CREATE ROLE "pg_abcdef"; -- error
ERROR:  role name "pg_abcdef" is reserved
DETAIL:  Role names starting with "pg_" are reserved.
CREATE ROLE regress_testrol0 SUPERUSER LOGIN;
CREATE ROLE regress_testrolx SUPERUSER LOGIN;
CREATE ROLE regress_testrol2 SUPERUSER;
CREATE ROLE regress_testrol1 SUPERUSER LOGIN IN ROLE regress_testrol2;
\c -
SET SESSION AUTHORIZATION regress_testrol1;
SET ROLE regress_testrol2;
--  ALTER ROLE
BEGIN;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | f
 regress_testrol1 | session_user | t        | f
 regress_testrol2 | current_user | f        | f
 session_user     | -            | f        | f
(6 rows)

ALTER ROLE CURRENT_USER WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | f
 regress_testrol1 | session_user | t        | f
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | f
(6 rows)

ALTER ROLE "current_user" WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | f
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | f
(6 rows)

ALTER ROLE SESSION_USER WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | t
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | f
(6 rows)

ALTER ROLE "session_user" WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | t
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | t
(6 rows)

ALTER USER "Public" WITH REPLICATION;
ALTER USER "None" WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | t
 Public           | -            | f        | t
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | t
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | t
(6 rows)

ALTER USER regress_testrol1 WITH NOREPLICATION;
ALTER USER regress_testrol2 WITH NOREPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | t
 Public           | -            | f        | t
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | f
 regress_testrol2 | current_user | f        | f
 session_user     | -            | f        | t
(6 rows)

ROLLBACK;
ALTER ROLE USER WITH LOGIN; -- error
ERROR:  syntax error at or near "USER"
LINE 1: ALTER ROLE USER WITH LOGIN;
                   ^
ALTER ROLE CURRENT_ROLE WITH LOGIN; --error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: ALTER ROLE CURRENT_ROLE WITH LOGIN;
                   ^
ALTER ROLE ALL WITH REPLICATION; -- error
ERROR:  syntax error at or near "WITH"
LINE 1: ALTER ROLE ALL WITH REPLICATION;
                       ^
ALTER ROLE SESSION_ROLE WITH NOREPLICATION; -- error
ERROR:  role "session_role" does not exist
ALTER ROLE PUBLIC WITH NOREPLICATION; -- error
ERROR:  role "public" does not exist
ALTER ROLE "public" WITH NOREPLICATION; -- error
ERROR:  role "public" does not exist
ALTER ROLE NONE WITH NOREPLICATION; -- error
ERROR:  role name "none" is reserved
LINE 1: ALTER ROLE NONE WITH NOREPLICATION;
                   ^
ALTER ROLE "none" WITH NOREPLICATION; -- error
ERROR:  role name "none" is reserved
LINE 1: ALTER ROLE "none" WITH NOREPLICATION;
                   ^
ALTER ROLE nonexistent WITH NOREPLICATION; -- error
ERROR:  role "nonexistent" does not exist
--  ALTER USER
BEGIN;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | f
 regress_testrol1 | session_user | t        | f
 regress_testrol2 | current_user | f        | f
 session_user     | -            | f        | f
(6 rows)

ALTER USER CURRENT_USER WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | f
 regress_testrol1 | session_user | t        | f
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | f
(6 rows)

ALTER USER "current_user" WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | f
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | f
(6 rows)

ALTER USER SESSION_USER WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | t
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | f
(6 rows)

ALTER USER "session_user" WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | f
 Public           | -            | f        | f
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | t
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | t
(6 rows)

ALTER USER "Public" WITH REPLICATION;
ALTER USER "None" WITH REPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | t
 Public           | -            | f        | t
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | t
 regress_testrol2 | current_user | f        | t
 session_user     | -            | f        | t
(6 rows)

ALTER USER regress_testrol1 WITH NOREPLICATION;
ALTER USER regress_testrol2 WITH NOREPLICATION;
SELECT * FROM chkrolattr();
       role       | rolekeyword  | canlogin | replication 
------------------+--------------+----------+-------------
 None             | -            | f        | t
 Public           | -            | f        | t
 current_user     | -            | f        | t
 regress_testrol1 | session_user | t        | f
 regress_testrol2 | current_user | f        | f
 session_user     | -            | f        | t
(6 rows)

ROLLBACK;
ALTER USER USER WITH LOGIN; -- error
ERROR:  syntax error at or near "USER"
LINE 1: ALTER USER USER WITH LOGIN;
                   ^
ALTER USER CURRENT_ROLE WITH LOGIN; -- error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: ALTER USER CURRENT_ROLE WITH LOGIN;
                   ^
ALTER USER ALL WITH REPLICATION; -- error
ERROR:  syntax error at or near "WITH"
LINE 1: ALTER USER ALL WITH REPLICATION;
                       ^
ALTER USER SESSION_ROLE WITH NOREPLICATION; -- error
ERROR:  role "session_role" does not exist
ALTER USER PUBLIC WITH NOREPLICATION; -- error
ERROR:  role "public" does not exist
ALTER USER "public" WITH NOREPLICATION; -- error
ERROR:  role "public" does not exist
ALTER USER NONE WITH NOREPLICATION; -- error
ERROR:  role name "none" is reserved
LINE 1: ALTER USER NONE WITH NOREPLICATION;
                   ^
ALTER USER "none" WITH NOREPLICATION; -- error
ERROR:  role name "none" is reserved
LINE 1: ALTER USER "none" WITH NOREPLICATION;
                   ^
ALTER USER nonexistent WITH NOREPLICATION; -- error
ERROR:  role "nonexistent" does not exist
--  ALTER ROLE SET/RESET
SELECT * FROM chksetconfig();
 db | role | rolkeyword | setconfig 
----+------+------------+-----------
(0 rows)

ALTER ROLE CURRENT_USER SET application_name to 'FOO';
ALTER ROLE SESSION_USER SET application_name to 'BAR';
ALTER ROLE "current_user" SET application_name to 'FOOFOO';
ALTER ROLE "Public" SET application_name to 'BARBAR';
ALTER ROLE ALL SET application_name to 'SLAP';
SELECT * FROM chksetconfig();
 db  |       role       |  rolkeyword  |         setconfig         
-----+------------------+--------------+---------------------------
 ALL | Public           | -            | {application_name=BARBAR}
 ALL | current_user     | -            | {application_name=FOOFOO}
 ALL | regress_testrol1 | session_user | {application_name=BAR}
 ALL | regress_testrol2 | current_user | {application_name=FOO}
(4 rows)

ALTER ROLE regress_testrol1 SET application_name to 'SLAM';
SELECT * FROM chksetconfig();
 db  |       role       |  rolkeyword  |         setconfig         
-----+------------------+--------------+---------------------------
 ALL | Public           | -            | {application_name=BARBAR}
 ALL | current_user     | -            | {application_name=FOOFOO}
 ALL | regress_testrol1 | session_user | {application_name=SLAM}
 ALL | regress_testrol2 | current_user | {application_name=FOO}
(4 rows)

ALTER ROLE CURRENT_USER RESET application_name;
ALTER ROLE SESSION_USER RESET application_name;
ALTER ROLE "current_user" RESET application_name;
ALTER ROLE "Public" RESET application_name;
ALTER ROLE ALL RESET application_name;
SELECT * FROM chksetconfig();
 db | role | rolkeyword | setconfig 
----+------+------------+-----------
(0 rows)

ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ'; -- error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: ALTER ROLE CURRENT_ROLE SET application_name to 'BAZ';
                   ^
ALTER ROLE USER SET application_name to 'BOOM'; -- error
ERROR:  syntax error at or near "USER"
LINE 1: ALTER ROLE USER SET application_name to 'BOOM';
                   ^
ALTER ROLE PUBLIC SET application_name to 'BOMB'; -- error
ERROR:  role "public" does not exist
ALTER ROLE nonexistent SET application_name to 'BOMB'; -- error
ERROR:  role "nonexistent" does not exist
--  ALTER USER SET/RESET
SELECT * FROM chksetconfig();
 db | role | rolkeyword | setconfig 
----+------+------------+-----------
(0 rows)

ALTER USER CURRENT_USER SET application_name to 'FOO';
ALTER USER SESSION_USER SET application_name to 'BAR';
ALTER USER "current_user" SET application_name to 'FOOFOO';
ALTER USER "Public" SET application_name to 'BARBAR';
ALTER USER ALL SET application_name to 'SLAP';
SELECT * FROM chksetconfig();
 db  |       role       |  rolkeyword  |         setconfig         
-----+------------------+--------------+---------------------------
 ALL | Public           | -            | {application_name=BARBAR}
 ALL | current_user     | -            | {application_name=FOOFOO}
 ALL | regress_testrol1 | session_user | {application_name=BAR}
 ALL | regress_testrol2 | current_user | {application_name=FOO}
(4 rows)

ALTER USER regress_testrol1 SET application_name to 'SLAM';
SELECT * FROM chksetconfig();
 db  |       role       |  rolkeyword  |         setconfig         
-----+------------------+--------------+---------------------------
 ALL | Public           | -            | {application_name=BARBAR}
 ALL | current_user     | -            | {application_name=FOOFOO}
 ALL | regress_testrol1 | session_user | {application_name=SLAM}
 ALL | regress_testrol2 | current_user | {application_name=FOO}
(4 rows)

ALTER USER CURRENT_USER RESET application_name;
ALTER USER SESSION_USER RESET application_name;
ALTER USER "current_user" RESET application_name;
ALTER USER "Public" RESET application_name;
ALTER USER ALL RESET application_name;
SELECT * FROM chksetconfig();
 db | role | rolkeyword | setconfig 
----+------+------------+-----------
(0 rows)

ALTER USER CURRENT_USER SET application_name to 'BAZ'; -- error
ALTER USER USER SET application_name to 'BOOM'; -- error
ERROR:  syntax error at or near "USER"
LINE 1: ALTER USER USER SET application_name to 'BOOM';
                   ^
ALTER USER PUBLIC SET application_name to 'BOMB'; -- error
ERROR:  role "public" does not exist
ALTER USER NONE SET application_name to 'BOMB'; -- error
ERROR:  role name "none" is reserved
LINE 1: ALTER USER NONE SET application_name to 'BOMB';
                   ^
ALTER USER nonexistent SET application_name to 'BOMB'; -- error
ERROR:  role "nonexistent" does not exist
-- CREATE SCHEMA
set client_min_messages to error;
CREATE SCHEMA newschema1 AUTHORIZATION CURRENT_USER;
CREATE SCHEMA newschema2 AUTHORIZATION "current_user";
CREATE SCHEMA newschema3 AUTHORIZATION SESSION_USER;
CREATE SCHEMA newschema4 AUTHORIZATION regress_testrolx;
CREATE SCHEMA newschema5 AUTHORIZATION "Public";
CREATE SCHEMA newschema6 AUTHORIZATION USER; -- error
ERROR:  syntax error at or near "USER"
LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION USER;
                                               ^
CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE; -- error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION CURRENT_ROLE;
                                               ^
CREATE SCHEMA newschema6 AUTHORIZATION PUBLIC; -- error
ERROR:  role "public" does not exist
CREATE SCHEMA newschema6 AUTHORIZATION "public"; -- error
ERROR:  role "public" does not exist
CREATE SCHEMA newschema6 AUTHORIZATION NONE; -- error
ERROR:  role name "none" is reserved
LINE 1: CREATE SCHEMA newschema6 AUTHORIZATION NONE;
                                               ^
CREATE SCHEMA newschema6 AUTHORIZATION nonexistent; -- error
ERROR:  role "nonexistent" does not exist
SELECT n.nspname, r.rolname FROM pg_namespace n
 JOIN pg_roles r ON (r.oid = n.nspowner)
 WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
  nspname   |     rolname      
------------+------------------
 newschema1 | regress_testrol2
 newschema2 | current_user
 newschema3 | regress_testrol1
 newschema4 | regress_testrolx
 newschema5 | Public
(5 rows)

CREATE SCHEMA IF NOT EXISTS newschema1 AUTHORIZATION CURRENT_USER;
CREATE SCHEMA IF NOT EXISTS newschema2 AUTHORIZATION "current_user";
CREATE SCHEMA IF NOT EXISTS newschema3 AUTHORIZATION SESSION_USER;
CREATE SCHEMA IF NOT EXISTS newschema4 AUTHORIZATION regress_testrolx;
CREATE SCHEMA IF NOT EXISTS newschema5 AUTHORIZATION "Public";
CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER; -- error
ERROR:  syntax error at or near "USER"
LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION USER;
                                                             ^
CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_ROLE; -- error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: ...ATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION CURRENT_RO...
                                                             ^
CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION PUBLIC; -- error
ERROR:  role "public" does not exist
CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION "public"; -- error
ERROR:  role "public" does not exist
CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE; -- error
ERROR:  role name "none" is reserved
LINE 1: CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION NONE;
                                                             ^
CREATE SCHEMA IF NOT EXISTS newschema6 AUTHORIZATION nonexistent; -- error
ERROR:  role "nonexistent" does not exist
SELECT n.nspname, r.rolname FROM pg_namespace n
 JOIN pg_roles r ON (r.oid = n.nspowner)
 WHERE n.nspname LIKE 'newschema_' ORDER BY 1;
  nspname   |     rolname      
------------+------------------
 newschema1 | regress_testrol2
 newschema2 | current_user
 newschema3 | regress_testrol1
 newschema4 | regress_testrolx
 newschema5 | Public
(5 rows)

-- ALTER TABLE OWNER TO
\c -
SET SESSION AUTHORIZATION regress_testrol0;
set client_min_messages to error;
CREATE TABLE testtab1 (a int);
CREATE TABLE testtab2 (a int);
CREATE TABLE testtab3 (a int);
CREATE TABLE testtab4 (a int);
CREATE TABLE testtab5 (a int);
CREATE TABLE testtab6 (a int);
\c -
SET SESSION AUTHORIZATION regress_testrol1;
SET ROLE regress_testrol2;
ALTER TABLE testtab1 OWNER TO CURRENT_USER;
ALTER TABLE testtab2 OWNER TO "current_user";
ALTER TABLE testtab3 OWNER TO SESSION_USER;
ALTER TABLE testtab4 OWNER TO regress_testrolx;
ALTER TABLE testtab5 OWNER TO "Public";
ALTER TABLE testtab6 OWNER TO CURRENT_ROLE; -- error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: ALTER TABLE testtab6 OWNER TO CURRENT_ROLE;
                                      ^
ALTER TABLE testtab6 OWNER TO USER; --error
ERROR:  syntax error at or near "USER"
LINE 1: ALTER TABLE testtab6 OWNER TO USER;
                                      ^
ALTER TABLE testtab6 OWNER TO PUBLIC; -- error
ERROR:  role "public" does not exist
ALTER TABLE testtab6 OWNER TO "public"; -- error
ERROR:  role "public" does not exist
ALTER TABLE testtab6 OWNER TO nonexistent; -- error
ERROR:  role "nonexistent" does not exist
SELECT c.relname, r.rolname
 FROM pg_class c JOIN pg_roles r ON (r.oid = c.relowner)
 WHERE relname LIKE 'testtab_'
 ORDER BY 1;
 relname  |     rolname      
----------+------------------
 testtab1 | regress_testrol2
 testtab2 | current_user
 testtab3 | regress_testrol1
 testtab4 | regress_testrolx
 testtab5 | Public
 testtab6 | regress_testrol0
(6 rows)

-- ALTER TABLE, VIEW, MATERIALIZED VIEW, FOREIGN TABLE, SEQUENCE are
-- changed their owner in the same way.
-- ALTER AGGREGATE
\c -
SET SESSION AUTHORIZATION regress_testrol0;
CREATE AGGREGATE testagg1(int2) (SFUNC = int2_sum, STYPE = int8);
CREATE AGGREGATE testagg2(int2) (SFUNC = int2_sum, STYPE = int8);
CREATE AGGREGATE testagg3(int2) (SFUNC = int2_sum, STYPE = int8);
CREATE AGGREGATE testagg4(int2) (SFUNC = int2_sum, STYPE = int8);
CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
CREATE AGGREGATE testagg5(int2) (SFUNC = int2_sum, STYPE = int8);
ERROR:  function "testagg5" already exists with same argument types
CREATE AGGREGATE testagg6(int2) (SFUNC = int2_sum, STYPE = int8);
CREATE AGGREGATE testagg7(int2) (SFUNC = int2_sum, STYPE = int8);
CREATE AGGREGATE testagg8(int2) (SFUNC = int2_sum, STYPE = int8);
CREATE AGGREGATE testagg9(int2) (SFUNC = int2_sum, STYPE = int8);
\c -
SET SESSION AUTHORIZATION regress_testrol1;
SET ROLE regress_testrol2;
ALTER AGGREGATE testagg1(int2) OWNER TO CURRENT_USER;
ALTER AGGREGATE testagg2(int2) OWNER TO "current_user";
ALTER AGGREGATE testagg3(int2) OWNER TO SESSION_USER;
ALTER AGGREGATE testagg4(int2) OWNER TO regress_testrolx;
ALTER AGGREGATE testagg5(int2) OWNER TO "Public";
ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE; -- error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO CURRENT_ROLE;
                                                ^
ALTER AGGREGATE testagg5(int2) OWNER TO USER; -- error
ERROR:  syntax error at or near "USER"
LINE 1: ALTER AGGREGATE testagg5(int2) OWNER TO USER;
                                                ^
ALTER AGGREGATE testagg5(int2) OWNER TO PUBLIC; -- error
ERROR:  role "public" does not exist
ALTER AGGREGATE testagg5(int2) OWNER TO "public"; -- error
ERROR:  role "public" does not exist
ALTER AGGREGATE testagg5(int2) OWNER TO nonexistent; -- error
ERROR:  role "nonexistent" does not exist
SELECT p.proname, r.rolname
 FROM pg_proc p JOIN pg_roles r ON (r.oid = p.proowner)
 WHERE proname LIKE 'testagg_'
 ORDER BY 1;
 proname  |     rolname      
----------+------------------
 testagg1 | regress_testrol2
 testagg2 | current_user
 testagg3 | regress_testrol1
 testagg4 | regress_testrolx
 testagg5 | Public
 testagg6 | regress_testrol0
 testagg7 | regress_testrol0
 testagg8 | regress_testrol0
 testagg9 | regress_testrol0
(9 rows)

-- CREATE USER MAPPING
CREATE FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support FOREIGN DATA WRAPPER yet
DETAIL:  The feature is not currently supported
CREATE SERVER sv1 FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support SERVER yet
DETAIL:  The feature is not currently supported
CREATE SERVER sv2 FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support SERVER yet
DETAIL:  The feature is not currently supported
CREATE SERVER sv3 FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support SERVER yet
DETAIL:  The feature is not currently supported
CREATE SERVER sv4 FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support SERVER yet
DETAIL:  The feature is not currently supported
CREATE SERVER sv5 FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support SERVER yet
DETAIL:  The feature is not currently supported
CREATE SERVER sv6 FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support SERVER yet
DETAIL:  The feature is not currently supported
CREATE SERVER sv7 FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support SERVER yet
DETAIL:  The feature is not currently supported
CREATE SERVER sv8 FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support SERVER yet
DETAIL:  The feature is not currently supported
CREATE SERVER sv9 FOREIGN DATA WRAPPER test_wrapper;
ERROR:  Postgres-XL does not support SERVER yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
	    OPTIONS (user 'CURRENT_ROLE'); -- error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: CREATE USER MAPPING FOR CURRENT_ROLE SERVER sv9
                                ^
CREATE USER MAPPING FOR nonexistent SERVER sv9
	    OPTIONS (user 'nonexistent'); -- error;
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

-- ALTER USER MAPPING
ALTER USER MAPPING FOR CURRENT_USER SERVER sv1
 OPTIONS (SET user 'CURRENT_USER_alt');
ERROR:  server "sv1" does not exist
ALTER USER MAPPING FOR "current_user" SERVER sv2
 OPTIONS (SET user '"current_user"_alt');
ERROR:  server "sv2" does not exist
ALTER USER MAPPING FOR USER SERVER sv3
 OPTIONS (SET user 'USER_alt');
ERROR:  server "sv3" does not exist
ALTER USER MAPPING FOR "user" SERVER sv4
 OPTIONS (SET user '"user"_alt');
ERROR:  server "sv4" does not exist
ALTER USER MAPPING FOR SESSION_USER SERVER sv5
 OPTIONS (SET user 'SESSION_USER_alt');
ERROR:  server "sv5" does not exist
ALTER USER MAPPING FOR PUBLIC SERVER sv6
 OPTIONS (SET user 'public_alt');
ERROR:  server "sv6" does not exist
ALTER USER MAPPING FOR "Public" SERVER sv7
 OPTIONS (SET user '"Public"_alt');
ERROR:  server "sv7" does not exist
ALTER USER MAPPING FOR regress_testrolx SERVER sv8
 OPTIONS (SET user 'regress_testrolx_alt');
ERROR:  server "sv8" does not exist
ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
 OPTIONS (SET user 'CURRENT_ROLE_alt');
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: ALTER USER MAPPING FOR CURRENT_ROLE SERVER sv9
                               ^
ALTER USER MAPPING FOR nonexistent SERVER sv9
 OPTIONS (SET user 'nonexistent_alt'); -- error
ERROR:  role "nonexistent" does not exist
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

-- DROP USER MAPPING
DROP USER MAPPING FOR CURRENT_USER SERVER sv1;
ERROR:  server "sv1" does not exist
DROP USER MAPPING FOR "current_user" SERVER sv2;
ERROR:  server "sv2" does not exist
DROP USER MAPPING FOR USER SERVER sv3;
ERROR:  server "sv3" does not exist
DROP USER MAPPING FOR "user" SERVER sv4;
ERROR:  server "sv4" does not exist
DROP USER MAPPING FOR SESSION_USER SERVER sv5;
ERROR:  server "sv5" does not exist
DROP USER MAPPING FOR PUBLIC SERVER sv6;
ERROR:  server "sv6" does not exist
DROP USER MAPPING FOR "Public" SERVER sv7;
ERROR:  server "sv7" does not exist
DROP USER MAPPING FOR regress_testrolx SERVER sv8;
ERROR:  server "sv8" does not exist
DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9; -- error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: DROP USER MAPPING FOR CURRENT_ROLE SERVER sv9;
                              ^
DROP USER MAPPING FOR nonexistent SERVER sv;  -- error
ERROR:  role "nonexistent" does not exist
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

CREATE USER MAPPING FOR CURRENT_USER SERVER sv1 OPTIONS (user 'CURRENT_USER');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR "current_user" SERVER sv2 OPTIONS (user '"current_user"');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR USER SERVER sv3 OPTIONS (user 'USER');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR "user" SERVER sv4 OPTIONS (user '"USER"');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR SESSION_USER SERVER sv5 OPTIONS (user 'SESSION_USER');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR PUBLIC SERVER sv6 OPTIONS (user 'PUBLIC');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR "Public" SERVER sv7 OPTIONS (user '"Public"');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
CREATE USER MAPPING FOR regress_testrolx SERVER sv8 OPTIONS (user 'regress_testrolx');
ERROR:  Postgres-XL does not support USER MAPPING yet
DETAIL:  The feature is not currently supported
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

-- DROP USER MAPPING IF EXISTS
DROP USER MAPPING IF EXISTS FOR CURRENT_USER SERVER sv1;
NOTICE:  server does not exist, skipping
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

DROP USER MAPPING IF EXISTS FOR "current_user" SERVER sv2;
NOTICE:  server does not exist, skipping
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

DROP USER MAPPING IF EXISTS FOR USER SERVER sv3;
NOTICE:  server does not exist, skipping
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

DROP USER MAPPING IF EXISTS FOR "user" SERVER sv4;
NOTICE:  server does not exist, skipping
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

DROP USER MAPPING IF EXISTS FOR SESSION_USER SERVER sv5;
NOTICE:  server does not exist, skipping
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

DROP USER MAPPING IF EXISTS FOR PUBLIC SERVER sv6;
NOTICE:  server does not exist, skipping
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

DROP USER MAPPING IF EXISTS FOR "Public" SERVER sv7;
NOTICE:  server does not exist, skipping
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

DROP USER MAPPING IF EXISTS FOR regress_testrolx SERVER sv8;
NOTICE:  server does not exist, skipping
SELECT * FROM chkumapping();
 umname | umserver | umoptions 
--------+----------+-----------
(0 rows)

DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9; --error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: DROP USER MAPPING IF EXISTS FOR CURRENT_ROLE SERVER sv9;
                                        ^
DROP USER MAPPING IF EXISTS FOR nonexistent SERVER sv9;  -- error
NOTICE:  role "nonexistent" does not exist, skipping
-- GRANT/REVOKE
GRANT regress_testrol0 TO pg_signal_backend; -- success
SET ROLE pg_signal_backend; --success
RESET ROLE;
CREATE SCHEMA test_schema AUTHORIZATION pg_signal_backend; --success
SET ROLE regress_testrol2;
UPDATE pg_proc SET proacl = null WHERE proname LIKE 'testagg_';
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
 proname  | proacl 
----------+--------
 testagg1 | 
 testagg2 | 
 testagg3 | 
 testagg4 | 
 testagg5 | 
 testagg6 | 
 testagg7 | 
 testagg8 | 
 testagg9 | 
(9 rows)

REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM PUBLIC;
REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM PUBLIC;
REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM PUBLIC;
REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM PUBLIC;
REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM PUBLIC;
REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM PUBLIC;
REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2) FROM PUBLIC;
GRANT ALL PRIVILEGES ON FUNCTION testagg1(int2) TO PUBLIC;
GRANT ALL PRIVILEGES ON FUNCTION testagg2(int2) TO CURRENT_USER;
GRANT ALL PRIVILEGES ON FUNCTION testagg3(int2) TO "current_user";
GRANT ALL PRIVILEGES ON FUNCTION testagg4(int2) TO SESSION_USER;
GRANT ALL PRIVILEGES ON FUNCTION testagg5(int2) TO "Public";
GRANT ALL PRIVILEGES ON FUNCTION testagg6(int2) TO regress_testrolx;
GRANT ALL PRIVILEGES ON FUNCTION testagg7(int2) TO "public";
GRANT ALL PRIVILEGES ON FUNCTION testagg8(int2)
	   TO current_user, public, regress_testrolx;
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
 proname  |                                                              proacl                                                               
----------+-----------------------------------------------------------------------------------------------------------------------------------
 testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
 testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
 testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1}
 testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
 testagg5 | {Public=X/Public}
 testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
 testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
 testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
 testagg9 | 
(9 rows)

GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_ROLE; --error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: ...RANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO CURRENT_RO...
                                                             ^
GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER; --error
ERROR:  syntax error at or near "USER"
LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO USER;
                                                           ^
GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE; --error
ERROR:  role name "none" is reserved
LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO NONE;
                                                           ^
GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none"; --error
ERROR:  role name "none" is reserved
LINE 1: GRANT ALL PRIVILEGES ON FUNCTION testagg9(int2) TO "none";
                                                           ^
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
 proname  |                                                              proacl                                                               
----------+-----------------------------------------------------------------------------------------------------------------------------------
 testagg1 | {regress_testrol2=X/regress_testrol2,=X/regress_testrol2}
 testagg2 | {current_user=X/current_user,regress_testrol2=X/current_user}
 testagg3 | {regress_testrol1=X/regress_testrol1,current_user=X/regress_testrol1}
 testagg4 | {regress_testrolx=X/regress_testrolx,regress_testrol1=X/regress_testrolx}
 testagg5 | {Public=X/Public}
 testagg6 | {regress_testrol0=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
 testagg7 | {regress_testrol0=X/regress_testrol0,=X/regress_testrol0}
 testagg8 | {regress_testrol0=X/regress_testrol0,regress_testrol2=X/regress_testrol0,=X/regress_testrol0,regress_testrolx=X/regress_testrol0}
 testagg9 | 
(9 rows)

REVOKE ALL PRIVILEGES ON FUNCTION testagg1(int2) FROM PUBLIC;
REVOKE ALL PRIVILEGES ON FUNCTION testagg2(int2) FROM CURRENT_USER;
REVOKE ALL PRIVILEGES ON FUNCTION testagg3(int2) FROM "current_user";
REVOKE ALL PRIVILEGES ON FUNCTION testagg4(int2) FROM SESSION_USER;
REVOKE ALL PRIVILEGES ON FUNCTION testagg5(int2) FROM "Public";
REVOKE ALL PRIVILEGES ON FUNCTION testagg6(int2) FROM regress_testrolx;
REVOKE ALL PRIVILEGES ON FUNCTION testagg7(int2) FROM "public";
REVOKE ALL PRIVILEGES ON FUNCTION testagg8(int2)
	   FROM current_user, public, regress_testrolx;
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
 proname  |                proacl                 
----------+---------------------------------------
 testagg1 | {regress_testrol2=X/regress_testrol2}
 testagg2 | {current_user=X/current_user}
 testagg3 | {regress_testrol1=X/regress_testrol1}
 testagg4 | {regress_testrolx=X/regress_testrolx}
 testagg5 | {}
 testagg6 | {regress_testrol0=X/regress_testrol0}
 testagg7 | {regress_testrol0=X/regress_testrol0}
 testagg8 | {regress_testrol0=X/regress_testrol0}
 testagg9 | 
(9 rows)

REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_ROLE; --error
ERROR:  syntax error at or near "CURRENT_ROLE"
LINE 1: ...KE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM CURRENT_RO...
                                                             ^
REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER; --error
ERROR:  syntax error at or near "USER"
LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM USER;
                                                              ^
REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE; --error
ERROR:  role name "none" is reserved
LINE 1: REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM NONE;
                                                              ^
REVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none"; --error
ERROR:  role name "none" is reserved
LINE 1: ...EVOKE ALL PRIVILEGES ON FUNCTION testagg9(int2) FROM "none";
                                                                ^
SELECT proname, proacl FROM pg_proc WHERE proname LIKE 'testagg_';
 proname  |                proacl                 
----------+---------------------------------------
 testagg1 | {regress_testrol2=X/regress_testrol2}
 testagg2 | {current_user=X/current_user}
 testagg3 | {regress_testrol1=X/regress_testrol1}
 testagg4 | {regress_testrolx=X/regress_testrolx}
 testagg5 | {}
 testagg6 | {regress_testrol0=X/regress_testrol0}
 testagg7 | {regress_testrol0=X/regress_testrol0}
 testagg8 | {regress_testrol0=X/regress_testrol0}
 testagg9 | 
(9 rows)

-- clean up
\c
DROP SCHEMA test_schema;
DROP OWNED BY regress_testrol0, "Public", "current_user", regress_testrol1, regress_testrol2, regress_testrolx CASCADE;
DROP ROLE regress_testrol0, regress_testrol1, regress_testrol2, regress_testrolx;
DROP ROLE "Public", "None", "current_user", "session_user", "user";