2 doc/src/sgml/ref/alter_role.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERROLE">
7 <indexterm zone="sql-alterrole">
8 <primary>ALTER ROLE</primary>
12 <refentrytitle>ALTER ROLE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 <refname>ALTER ROLE</refname>
19 <refpurpose>change a database role</refpurpose>
24 ALTER ROLE <replaceable class="PARAMETER">role_specification</replaceable> [ WITH ] <replaceable class="PARAMETER">option</replaceable> [ ... ]
26 <phrase>where <replaceable class="PARAMETER">option</replaceable> can be:</phrase>
28 SUPERUSER | NOSUPERUSER
29 | CREATEDB | NOCREATEDB
30 | CREATEROLE | NOCREATEROLE
33 | REPLICATION | NOREPLICATION
34 | BYPASSRLS | NOBYPASSRLS
35 | CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
36 | [ ENCRYPTED ] PASSWORD '<replaceable class="PARAMETER">password</replaceable>'
37 | VALID UNTIL '<replaceable class="PARAMETER">timestamp</replaceable>'
39 ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
41 ALTER ROLE { <replaceable class="PARAMETER">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
42 ALTER ROLE { <replaceable class="PARAMETER">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
43 ALTER ROLE { <replaceable class="PARAMETER">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
44 ALTER ROLE { <replaceable class="PARAMETER">role_specification</replaceable> | ALL } [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET ALL
46 <phrase>where <replaceable class="PARAMETER">role_specification</replaceable> can be:</phrase>
48 [ GROUP ] <replaceable class="PARAMETER">role_name</replaceable>
55 <title>Description</title>
58 <command>ALTER ROLE</command> changes the attributes of a
59 <productname>PostgreSQL</productname> role.
63 The first variant of this command listed in the synopsis can change
64 many of the role attributes that can be specified in
65 <xref linkend="sql-createrole">.
66 (All the possible attributes are covered,
67 except that there are no options for adding or removing memberships; use
68 <xref linkend="SQL-GRANT"> and
69 <xref linkend="SQL-REVOKE"> for that.)
70 Attributes not mentioned in the command retain their previous settings.
71 Database superusers can change any of these settings for any role.
72 Roles having <literal>CREATEROLE</> privilege can change any of these
73 settings, but only for non-superuser and non-replication roles.
74 Ordinary roles can only change their own password.
78 The second variant changes the name of the role.
79 Database superusers can rename any role.
80 Roles having <literal>CREATEROLE</> privilege can rename non-superuser
82 The current session user cannot be renamed.
83 (Connect as a different user if you need to do that.)
84 Because <literal>MD5</>-encrypted passwords use the role name as
85 cryptographic salt, renaming a role clears its password if the
86 password is <literal>MD5</>-encrypted.
90 The remaining variants change a role's session default for a configuration
91 variable, either for all databases or, when the <literal>IN
92 DATABASE</literal> clause is specified, only for sessions in the named
93 database. If <literal>ALL</literal> is specified instead of a role name,
94 this changes the setting for all roles. Using <literal>ALL</literal>
95 with <literal>IN DATABASE</literal> is effectively the same as using the
96 command <literal>ALTER DATABASE ... SET ...</literal>.
100 Whenever the role subsequently
101 starts a new session, the specified value becomes the session
102 default, overriding whatever setting is present in
103 <filename>postgresql.conf</> or has been received from the <command>postgres</command>
104 command line. This only happens at login time; executing
105 <xref linkend="sql-set-role"> or
106 <xref linkend="sql-set-session-authorization"> does not cause new
107 configuration values to be set.
108 Settings set for all databases are overridden by database-specific settings
109 attached to a role. Settings for specific databases or specific roles override
110 settings for all roles.
114 Superusers can change anyone's session defaults. Roles having
115 <literal>CREATEROLE</> privilege can change defaults for non-superuser
116 roles. Ordinary roles can only set defaults for themselves.
117 Certain configuration variables cannot be set this way, or can only be
118 set if a superuser issues the command. Only superusers can change a setting
119 for all roles in all databases.
124 <title>Parameters</title>
128 <term><replaceable class="PARAMETER">name</replaceable></term>
131 The name of the role whose attributes are to be altered.
137 <term><literal>CURRENT_USER</literal></term>
140 Alter the current user instead of an explicitly identified role.
146 <term><literal>SESSION_USER</literal></term>
149 Alter the current session user instead of an explicitly identified
156 <term><literal>SUPERUSER</literal></term>
157 <term><literal>NOSUPERUSER</literal></term>
158 <term><literal>CREATEDB</></term>
159 <term><literal>NOCREATEDB</></term>
160 <term><literal>CREATEROLE</literal></term>
161 <term><literal>NOCREATEROLE</literal></term>
162 <term><literal>INHERIT</literal></term>
163 <term><literal>NOINHERIT</literal></term>
164 <term><literal>LOGIN</literal></term>
165 <term><literal>NOLOGIN</literal></term>
166 <term><literal>REPLICATION</literal></term>
167 <term><literal>NOREPLICATION</literal></term>
168 <term><literal>BYPASSRLS</literal></term>
169 <term><literal>NOBYPASSRLS</literal></term>
170 <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
171 <term>[ <literal>ENCRYPTED</> ] <literal>PASSWORD</> <replaceable class="parameter">password</replaceable></term>
172 <term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term>
175 These clauses alter attributes originally set by
176 <xref linkend="SQL-CREATEROLE">. For more information, see the
177 <command>CREATE ROLE</command> reference page.
183 <term><replaceable>new_name</replaceable></term>
186 The new name of the role.
192 <term><replaceable>database_name</replaceable></term>
195 The name of the database the configuration variable should be set in.
201 <term><replaceable>configuration_parameter</replaceable></term>
202 <term><replaceable>value</replaceable></term>
205 Set this role's session default for the specified configuration
206 parameter to the given value. If
207 <replaceable>value</replaceable> is <literal>DEFAULT</literal>
208 or, equivalently, <literal>RESET</literal> is used, the
209 role-specific variable setting is removed, so the role will
210 inherit the system-wide default setting in new sessions. Use
211 <literal>RESET ALL</literal> to clear all role-specific settings.
212 <literal>SET FROM CURRENT</> saves the session's current value of
213 the parameter as the role-specific value.
214 If <literal>IN DATABASE</literal> is specified, the configuration
215 parameter is set or removed for the given role and database only.
219 Role-specific variable settings take effect only at login;
220 <xref linkend="sql-set-role"> and
221 <xref linkend="sql-set-session-authorization">
222 do not process role-specific variable settings.
226 See <xref linkend="sql-set"> and <xref
227 linkend="runtime-config"> for more information about allowed
228 parameter names and values.
239 Use <xref linkend="SQL-CREATEROLE">
240 to add new roles, and <xref linkend="SQL-DROPROLE"> to remove a role.
244 <command>ALTER ROLE</command> cannot change a role's memberships.
245 Use <xref linkend="SQL-GRANT"> and
246 <xref linkend="SQL-REVOKE">
251 Caution must be exercised when specifying an unencrypted password
252 with this command. The password will be transmitted to the server
253 in cleartext, and it might also be logged in the client's command
254 history or the server log. <xref linkend="app-psql">
256 <command>\password</command> that can be used to change a
257 role's password without exposing the cleartext password.
261 It is also possible to tie a
262 session default to a specific database rather than to a role; see
263 <xref linkend="sql-alterdatabase">.
264 If there is a conflict, database-role-specific settings override role-specific
265 ones, which in turn override database-specific ones.
270 <title>Examples</title>
273 Change a role's password:
276 ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
281 Remove a role's password:
284 ALTER ROLE davide WITH PASSWORD NULL;
289 Change a password expiration date, specifying that the password
290 should expire at midday on 4th May 2015 using
291 the time zone which is one hour ahead of <acronym>UTC</>:
293 ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
298 Make a password valid forever:
300 ALTER ROLE fred VALID UNTIL 'infinity';
305 Give a role the ability to create other roles and new databases:
308 ALTER ROLE miriam CREATEROLE CREATEDB;
313 Give a role a non-default setting of the
314 <xref linkend="guc-maintenance-work-mem"> parameter:
317 ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
322 Give a role a non-default, database-specific setting of the
323 <xref linkend="guc-client-min-messages"> parameter:
326 ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
327 </programlisting></para>
331 <title>Compatibility</title>
334 The <command>ALTER ROLE</command> statement is a
335 <productname>PostgreSQL</productname> extension.
340 <title>See Also</title>
342 <simplelist type="inline">
343 <member><xref linkend="sql-createrole"></member>
344 <member><xref linkend="sql-droprole"></member>
345 <member><xref linkend="sql-alterdatabase"></member>
346 <member><xref linkend="sql-set"></member>