2 doc/src/sgml/ref/alter_role.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-ALTERROLE">
8 <refentrytitle>ALTER ROLE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>ALTER ROLE</refname>
15 <refpurpose>change a database role</refpurpose>
18 <indexterm zone="sql-alterrole">
19 <primary>ALTER ROLE</primary>
24 ALTER ROLE <replaceable class="PARAMETER">name</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
31 | CREATEUSER | NOCREATEUSER
34 | REPLICATION | NOREPLICATION
35 | CONNECTION LIMIT <replaceable class="PARAMETER">connlimit</replaceable>
36 | [ ENCRYPTED | UNENCRYPTED ] 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">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }
42 ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] SET <replaceable>configuration_parameter</replaceable> FROM CURRENT
43 ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET <replaceable>configuration_parameter</replaceable>
44 ALTER ROLE <replaceable class="PARAMETER">name</replaceable> [ IN DATABASE <replaceable class="PARAMETER">database_name</replaceable> ] RESET ALL
49 <title>Description</title>
52 <command>ALTER ROLE</command> changes the attributes of a
53 <productname>PostgreSQL</productname> role.
57 The first variant of this command listed in the synopsis can change
58 many of the role attributes that can be specified in
59 <xref linkend="sql-createrole">.
60 (All the possible attributes are covered,
61 except that there are no options for adding or removing memberships; use
62 <xref linkend="SQL-GRANT"> and
63 <xref linkend="SQL-REVOKE"> for that.)
64 Attributes not mentioned in the command retain their previous settings.
65 Database superusers can change any of these settings for any role.
66 Roles having <literal>CREATEROLE</> privilege can change any of these
67 settings, but only for non-superuser and non-replication roles.
68 Ordinary roles can only change their own password.
72 The second variant changes the name of the role.
73 Database superusers can rename any role.
74 Roles having <literal>CREATEROLE</> privilege can rename non-superuser
76 The current session user cannot be renamed.
77 (Connect as a different user if you need to do that.)
78 Because <literal>MD5</>-encrypted passwords use the role name as
79 cryptographic salt, renaming a role clears its password if the
80 password is <literal>MD5</>-encrypted.
84 The remaining variants change a role's session default for a configuration
85 variable, either for all databases or, when the <literal>IN
86 DATABASE</literal> clause is specified, only for sessions in
87 the named database. Whenever the role subsequently
88 starts a new session, the specified value becomes the session
89 default, overriding whatever setting is present in
90 <filename>postgresql.conf</> or has been received from the <command>postgres</command>
91 command line. This only happens at login time; executing
92 <xref linkend="sql-set-role"> or
93 <xref linkend="sql-set-session-authorization"> does not cause new
94 configuration values to be set.
95 Settings set for all databases are overridden by database-specific settings
97 Superusers can change anyone's session defaults. Roles having
98 <literal>CREATEROLE</> privilege can change defaults for non-superuser
99 roles. Ordinary roles can only set defaults for themselves.
100 Certain configuration variables cannot be set this way, or can only be
101 set if a superuser issues the command.
106 <title>Parameters</title>
110 <term><replaceable class="PARAMETER">name</replaceable></term>
113 The name of the role whose attributes are to be altered.
119 <term><literal>SUPERUSER</literal></term>
120 <term><literal>NOSUPERUSER</literal></term>
121 <term><literal>CREATEDB</></term>
122 <term><literal>NOCREATEDB</></term>
123 <term><literal>CREATEROLE</literal></term>
124 <term><literal>NOCREATEROLE</literal></term>
125 <term><literal>CREATEUSER</literal></term>
126 <term><literal>NOCREATEUSER</literal></term>
127 <term><literal>INHERIT</literal></term>
128 <term><literal>NOINHERIT</literal></term>
129 <term><literal>LOGIN</literal></term>
130 <term><literal>NOLOGIN</literal></term>
131 <term><literal>REPLICATION</literal></term>
132 <term><literal>NOREPLICATION</literal></term>
133 <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
134 <term><literal>PASSWORD</> <replaceable class="parameter">password</replaceable></term>
135 <term><literal>ENCRYPTED</></term>
136 <term><literal>UNENCRYPTED</></term>
137 <term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term>
140 These clauses alter attributes originally set by
141 <xref linkend="SQL-CREATEROLE">. For more information, see the
142 <command>CREATE ROLE</command> reference page.
148 <term><replaceable>new_name</replaceable></term>
151 The new name of the role.
157 <term><replaceable>database_name</replaceable></term>
160 The name of the database the configuration variable should be set in.
166 <term><replaceable>configuration_parameter</replaceable></term>
167 <term><replaceable>value</replaceable></term>
170 Set this role's session default for the specified configuration
171 parameter to the given value. If
172 <replaceable>value</replaceable> is <literal>DEFAULT</literal>
173 or, equivalently, <literal>RESET</literal> is used, the
174 role-specific variable setting is removed, so the role will
175 inherit the system-wide default setting in new sessions. Use
176 <literal>RESET ALL</literal> to clear all role-specific settings.
177 <literal>SET FROM CURRENT</> saves the session's current value of
178 the parameter as the role-specific value.
179 If <literal>IN DATABASE</literal> is specified, the configuration
180 parameter is set or removed for the given role and database only.
184 Role-specific variable settings take effect only at login;
185 <xref linkend="sql-set-role"> and
186 <xref linkend="sql-set-session-authorization">
187 do not process role-specific variable settings.
191 See <xref linkend="sql-set"> and <xref
192 linkend="runtime-config"> for more information about allowed
193 parameter names and values.
204 Use <xref linkend="SQL-CREATEROLE">
205 to add new roles, and <xref linkend="SQL-DROPROLE"> to remove a role.
209 <command>ALTER ROLE</command> cannot change a role's memberships.
210 Use <xref linkend="SQL-GRANT"> and
211 <xref linkend="SQL-REVOKE">
216 Caution must be exercised when specifying an unencrypted password
217 with this command. The password will be transmitted to the server
218 in cleartext, and it might also be logged in the client's command
219 history or the server log. <xref linkend="app-psql">
221 <command>\password</command> that can be used to change a
222 role's password without exposing the cleartext password.
226 It is also possible to tie a
227 session default to a specific database rather than to a role; see
228 <xref linkend="sql-alterdatabase">.
229 If there is a conflict, database-role-specific settings override role-specific
230 ones, which in turn override database-specific ones.
235 <title>Examples</title>
238 Change a role's password:
241 ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
246 Remove a role's password:
249 ALTER ROLE davide WITH PASSWORD NULL;
254 Change a password expiration date, specifying that the password
255 should expire at midday on 4th May 2015 using
256 the time zone which is one hour ahead of <acronym>UTC</>:
258 ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
263 Make a password valid forever:
265 ALTER ROLE fred VALID UNTIL 'infinity';
270 Give a role the ability to create other roles and new databases:
273 ALTER ROLE miriam CREATEROLE CREATEDB;
278 Give a role a non-default setting of the
279 <xref linkend="guc-maintenance-work-mem"> parameter:
282 ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
287 Give a role a non-default, database-specific setting of the
288 <xref linkend="guc-client-min-messages"> parameter:
291 ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
292 </programlisting></para>
296 <title>Compatibility</title>
299 The <command>ALTER ROLE</command> statement is a
300 <productname>PostgreSQL</productname> extension.
305 <title>See Also</title>
307 <simplelist type="inline">
308 <member><xref linkend="sql-createrole"></member>
309 <member><xref linkend="sql-droprole"></member>
310 <member><xref linkend="sql-set"></member>