1-- default for superuser is false
2CREATE ROLE regress_test_def_superuser;
3SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_superuser';
4          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
5----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
6 regress_test_def_superuser | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
7(1 row)
8
9CREATE ROLE regress_test_superuser WITH SUPERUSER;
10SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser';
11        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
12------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
13 regress_test_superuser | t        | t          | f             | f           | f           | f              | f            |           -1 |             |
14(1 row)
15
16ALTER ROLE regress_test_superuser WITH NOSUPERUSER;
17SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser';
18        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
19------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
20 regress_test_superuser | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
21(1 row)
22
23ALTER ROLE regress_test_superuser WITH SUPERUSER;
24SELECT * FROM pg_authid WHERE rolname = 'regress_test_superuser';
25        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
26------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
27 regress_test_superuser | t        | t          | f             | f           | f           | f              | f            |           -1 |             |
28(1 row)
29
30-- default for inherit is true
31CREATE ROLE regress_test_def_inherit;
32SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_inherit';
33         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
34--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
35 regress_test_def_inherit | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
36(1 row)
37
38CREATE ROLE regress_test_inherit WITH NOINHERIT;
39SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit';
40       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
41----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
42 regress_test_inherit | f        | f          | f             | f           | f           | f              | f            |           -1 |             |
43(1 row)
44
45ALTER ROLE regress_test_inherit WITH INHERIT;
46SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit';
47       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
48----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
49 regress_test_inherit | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
50(1 row)
51
52ALTER ROLE regress_test_inherit WITH NOINHERIT;
53SELECT * FROM pg_authid WHERE rolname = 'regress_test_inherit';
54       rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
55----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
56 regress_test_inherit | f        | f          | f             | f           | f           | f              | f            |           -1 |             |
57(1 row)
58
59-- default for create role is false
60CREATE ROLE regress_test_def_createrole;
61SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_createrole';
62           rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
63-----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
64 regress_test_def_createrole | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
65(1 row)
66
67CREATE ROLE regress_test_createrole WITH CREATEROLE;
68SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole';
69         rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
70-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
71 regress_test_createrole | f        | t          | t             | f           | f           | f              | f            |           -1 |             |
72(1 row)
73
74ALTER ROLE regress_test_createrole WITH NOCREATEROLE;
75SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole';
76         rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
77-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
78 regress_test_createrole | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
79(1 row)
80
81ALTER ROLE regress_test_createrole WITH CREATEROLE;
82SELECT * FROM pg_authid WHERE rolname = 'regress_test_createrole';
83         rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
84-------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
85 regress_test_createrole | f        | t          | t             | f           | f           | f              | f            |           -1 |             |
86(1 row)
87
88-- default for create database is false
89CREATE ROLE regress_test_def_createdb;
90SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_createdb';
91          rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
92---------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
93 regress_test_def_createdb | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
94(1 row)
95
96CREATE ROLE regress_test_createdb WITH CREATEDB;
97SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb';
98        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
99-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
100 regress_test_createdb | f        | t          | f             | t           | f           | f              | f            |           -1 |             |
101(1 row)
102
103ALTER ROLE regress_test_createdb WITH NOCREATEDB;
104SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb';
105        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
106-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
107 regress_test_createdb | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
108(1 row)
109
110ALTER ROLE regress_test_createdb WITH CREATEDB;
111SELECT * FROM pg_authid WHERE rolname = 'regress_test_createdb';
112        rolname        | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
113-----------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
114 regress_test_createdb | f        | t          | f             | t           | f           | f              | f            |           -1 |             |
115(1 row)
116
117-- default for can login is false for role
118CREATE ROLE regress_test_def_role_canlogin;
119SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_role_canlogin';
120            rolname             | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
121--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
122 regress_test_def_role_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
123(1 row)
124
125CREATE ROLE regress_test_role_canlogin WITH LOGIN;
126SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
127          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
128----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
129 regress_test_role_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             |
130(1 row)
131
132ALTER ROLE regress_test_role_canlogin WITH NOLOGIN;
133SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
134          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
135----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
136 regress_test_role_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
137(1 row)
138
139ALTER ROLE regress_test_role_canlogin WITH LOGIN;
140SELECT * FROM pg_authid WHERE rolname = 'regress_test_role_canlogin';
141          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
142----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
143 regress_test_role_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             |
144(1 row)
145
146-- default for can login is true for user
147CREATE USER regress_test_def_user_canlogin;
148SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_user_canlogin';
149            rolname             | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
150--------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
151 regress_test_def_user_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             |
152(1 row)
153
154CREATE USER regress_test_user_canlogin WITH NOLOGIN;
155SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
156          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
157----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
158 regress_test_user_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
159(1 row)
160
161ALTER USER regress_test_user_canlogin WITH LOGIN;
162SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
163          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
164----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
165 regress_test_user_canlogin | f        | t          | f             | f           | t           | f              | f            |           -1 |             |
166(1 row)
167
168ALTER USER regress_test_user_canlogin WITH NOLOGIN;
169SELECT * FROM pg_authid WHERE rolname = 'regress_test_user_canlogin';
170          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
171----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
172 regress_test_user_canlogin | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
173(1 row)
174
175-- default for replication is false
176CREATE ROLE regress_test_def_replication;
177SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_replication';
178           rolname            | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
179------------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
180 regress_test_def_replication | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
181(1 row)
182
183CREATE ROLE regress_test_replication WITH REPLICATION;
184SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication';
185         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
186--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
187 regress_test_replication | f        | t          | f             | f           | f           | t              | f            |           -1 |             |
188(1 row)
189
190ALTER ROLE regress_test_replication WITH NOREPLICATION;
191SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication';
192         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
193--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
194 regress_test_replication | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
195(1 row)
196
197ALTER ROLE regress_test_replication WITH REPLICATION;
198SELECT * FROM pg_authid WHERE rolname = 'regress_test_replication';
199         rolname          | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
200--------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
201 regress_test_replication | f        | t          | f             | f           | f           | t              | f            |           -1 |             |
202(1 row)
203
204-- default for bypassrls is false
205CREATE ROLE regress_test_def_bypassrls;
206SELECT * FROM pg_authid WHERE rolname = 'regress_test_def_bypassrls';
207          rolname           | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
208----------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
209 regress_test_def_bypassrls | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
210(1 row)
211
212CREATE ROLE regress_test_bypassrls WITH BYPASSRLS;
213SELECT * FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
214        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
215------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
216 regress_test_bypassrls | f        | t          | f             | f           | f           | f              | t            |           -1 |             |
217(1 row)
218
219ALTER ROLE regress_test_bypassrls WITH NOBYPASSRLS;
220SELECT * FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
221        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
222------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
223 regress_test_bypassrls | f        | t          | f             | f           | f           | f              | f            |           -1 |             |
224(1 row)
225
226ALTER ROLE regress_test_bypassrls WITH BYPASSRLS;
227SELECT * FROM pg_authid WHERE rolname = 'regress_test_bypassrls';
228        rolname         | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcanlogin | rolreplication | rolbypassrls | rolconnlimit | rolpassword | rolvaliduntil
229------------------------+----------+------------+---------------+-------------+-------------+----------------+--------------+--------------+-------------+---------------
230 regress_test_bypassrls | f        | t          | f             | f           | f           | f              | t            |           -1 |             |
231(1 row)
232
233-- clean up roles
234DROP ROLE regress_test_def_superuser;
235DROP ROLE regress_test_superuser;
236DROP ROLE regress_test_def_inherit;
237DROP ROLE regress_test_inherit;
238DROP ROLE regress_test_def_createrole;
239DROP ROLE regress_test_createrole;
240DROP ROLE regress_test_def_createdb;
241DROP ROLE regress_test_createdb;
242DROP ROLE regress_test_def_role_canlogin;
243DROP ROLE regress_test_role_canlogin;
244DROP USER regress_test_def_user_canlogin;
245DROP USER regress_test_user_canlogin;
246DROP ROLE regress_test_def_replication;
247DROP ROLE regress_test_replication;
248DROP ROLE regress_test_def_bypassrls;
249DROP ROLE regress_test_bypassrls;
250