-- default for superuser is false
CREATE ROLE test_def_superuser;
SELECT * FROM pg_authid WHERE rolname = 'test_def_superuser';
      rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_def_superuser | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

CREATE ROLE test_superuser WITH SUPERUSER;
SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
    rolname     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_superuser | t        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_superuser WITH NOSUPERUSER;
SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
    rolname     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_superuser | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_superuser WITH SUPERUSER;
SELECT * FROM pg_authid WHERE rolname = 'test_superuser';
    rolname     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_superuser | t        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

-- default for inherit is true
CREATE ROLE test_def_inherit;
SELECT * FROM pg_authid WHERE rolname = 'test_def_inherit';
     rolname      | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_def_inherit | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

CREATE ROLE test_inherit WITH NOINHERIT;
SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
   rolname    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_inherit | f        | f          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_inherit WITH INHERIT;
SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
   rolname    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_inherit | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_inherit WITH NOINHERIT;
SELECT * FROM pg_authid WHERE rolname = 'test_inherit';
   rolname    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_inherit | f        | f          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

-- default for create role is false
CREATE ROLE test_def_createrole;
SELECT * FROM pg_authid WHERE rolname = 'test_def_createrole';
       rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_def_createrole | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

CREATE ROLE test_createrole WITH CREATEROLE;
SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
     rolname     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_createrole | f        | t          | t             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_createrole WITH NOCREATEROLE;
SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
     rolname     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_createrole | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_createrole WITH CREATEROLE;
SELECT * FROM pg_authid WHERE rolname = 'test_createrole';
     rolname     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_createrole | f        | t          | t             | f           | f           | f              | f            |           -1 |             | 
(1 row)

-- default for create database is false
CREATE ROLE test_def_createdb;
SELECT * FROM pg_authid WHERE rolname = 'test_def_createdb';
      rolname      | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
-------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_def_createdb | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

CREATE ROLE test_createdb WITH CREATEDB;
SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
    rolname    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_createdb | f        | t          | f             | t           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_createdb WITH NOCREATEDB;
SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
    rolname    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_createdb | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_createdb WITH CREATEDB;
SELECT * FROM pg_authid WHERE rolname = 'test_createdb';
    rolname    | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
---------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_createdb | f        | t          | f             | t           | f           | f              | f            |           -1 |             | 
(1 row)

-- default for can login is false for role
CREATE ROLE test_def_role_canlogin;
SELECT * FROM pg_authid WHERE rolname = 'test_def_role_canlogin';
        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_def_role_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

CREATE ROLE test_role_canlogin WITH LOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
      rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_role_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_role_canlogin WITH NOLOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
      rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_role_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_role_canlogin WITH LOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_role_canlogin';
      rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_role_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             | 
(1 row)

-- default for can login is true for user
CREATE USER test_def_user_canlogin;
SELECT * FROM pg_authid WHERE rolname = 'test_def_user_canlogin';
        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_def_user_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             | 
(1 row)

CREATE USER test_user_canlogin WITH NOLOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
      rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_user_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER USER test_user_canlogin WITH LOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
      rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_user_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             | 
(1 row)

ALTER USER test_user_canlogin WITH NOLOGIN;
SELECT * FROM pg_authid WHERE rolname = 'test_user_canlogin';
      rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_user_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

-- default for replication is false
CREATE ROLE test_def_replication;
SELECT * FROM pg_authid WHERE rolname = 'test_def_replication';
       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_def_replication | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

CREATE ROLE test_replication WITH REPLICATION;
SELECT * FROM pg_authid WHERE rolname = 'test_replication';
     rolname      | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_replication | f        | t          | f             | f           | f           | t              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_replication WITH NOREPLICATION;
SELECT * FROM pg_authid WHERE rolname = 'test_replication';
     rolname      | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_replication | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_replication WITH REPLICATION;
SELECT * FROM pg_authid WHERE rolname = 'test_replication';
     rolname      | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_replication | f        | t          | f             | f           | f           | t              | f            |           -1 |             | 
(1 row)

-- default for bypassrls is false
CREATE ROLE test_def_bypassrls;
SELECT * FROM pg_authid WHERE rolname = 'test_def_bypassrls';
      rolname       | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
--------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_def_bypassrls | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

CREATE ROLE test_bypassrls WITH BYPASSRLS;
SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
    rolname     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_bypassrls | f        | t          | f             | f           | f           | f              | t            |           -1 |             | 
(1 row)

ALTER ROLE test_bypassrls WITH NOBYPASSRLS;
SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
    rolname     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_bypassrls | f        | t          | f             | f           | f           | f              | f            |           -1 |             | 
(1 row)

ALTER ROLE test_bypassrls WITH BYPASSRLS;
SELECT * FROM pg_authid WHERE rolname = 'test_bypassrls';
    rolname     | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil 
----------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
 test_bypassrls | f        | t          | f             | f           | f           | f              | t            |           -1 |             | 
(1 row)

-- clean up roles
DROP ROLE test_def_superuser;
DROP ROLE test_superuser;
DROP ROLE test_def_inherit;
DROP ROLE test_inherit;
DROP ROLE test_def_createrole;
DROP ROLE test_createrole;
DROP ROLE test_def_createdb;
DROP ROLE test_createdb;
DROP ROLE test_def_role_canlogin;
DROP ROLE test_role_canlogin;
DROP USER test_def_user_canlogin;
DROP USER test_user_canlogin;
DROP ROLE test_def_replication;
DROP ROLE test_replication;
DROP ROLE test_def_bypassrls;
DROP ROLE test_bypassrls;