Debian Codegraph @

doc/src/sgml/ref/alter_role.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_role.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-alterrole">
7 <indexterm zone="sql-alterrole">
8 <primary>ALTER ROLE</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER ROLE</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER ROLE</refname>
19 <refpurpose>change a database role</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 ALTER ROLE <replaceable class="parameter">role_specification</replaceable> [ WITH ] <replaceable class="parameter">option</replaceable> [ ... ]
25
26 <phrase>where <replaceable class="parameter">option</replaceable> can be:</phrase>
27
28 SUPERUSER | NOSUPERUSER
29 | CREATEDB | NOCREATEDB
30 | CREATEROLE | NOCREATEROLE
31 | INHERIT | NOINHERIT
32 | LOGIN | NOLOGIN
33 | REPLICATION | NOREPLICATION
34 | BYPASSRLS | NOBYPASSRLS
35 | CONNECTION LIMIT <replaceable class="parameter">connlimit</replaceable>
36 | [ ENCRYPTED ] PASSWORD '<replaceable class="parameter">password</replaceable>' | PASSWORD NULL
37 | VALID UNTIL '<replaceable class="parameter">timestamp</replaceable>'
38
39 ALTER ROLE <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
40
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
45
46 <phrase>where <replaceable class="parameter">role_specification</replaceable> can be:</phrase>
47
48 <replaceable class="parameter">role_name</replaceable>
49 | CURRENT_ROLE
50 | CURRENT_USER
51 | SESSION_USER
52 </synopsis>
53 </refsynopsisdiv>
54
55 <refsect1 id="sql-alterrole-desc">
56 <title>Description</title>
57
58 <para>
59 <command>ALTER ROLE</command> changes the attributes of a
60 <productname>PostgreSQL</productname> role.
61 </para>
62
63 <para>
64 The first variant of this command listed in the synopsis can change
65 many of the role attributes that can be specified in
66 <link linkend="sql-createrole"><command>CREATE ROLE</command></link>.
67 (All the possible attributes are covered,
68 except that there are no options for adding or removing memberships; use
69 <link linkend="sql-grant"><command>GRANT</command></link> and
70 <link linkend="sql-revoke"><command>REVOKE</command></link> for that.)
71 Attributes not mentioned in the command retain their previous settings.
72 Database superusers can change any of these settings for any role, except
73 for changing the <literal>SUPERUSER</literal> property for the
74 <glossterm linkend="glossary-bootstrap-superuser">bootstrap superuser</glossterm>.
75 Non-superuser roles having <literal>CREATEROLE</literal> privilege can
76 change most of these properties, but only for non-superuser and
77 non-replication roles for which they have been granted
78 <literal>ADMIN OPTION</literal>. Non-superusers cannot change the
79 <literal>SUPERUSER</literal> property and can change the
80 <literal>CREATEDB</literal>, <literal>REPLICATION</literal>, and
81 <literal>BYPASSRLS</literal> properties only if they possess the
82 corresponding property themselves.
83 Ordinary roles can only change their own password.
84 </para>
85
86 <para>
87 The second variant changes the name of the role.
88 Database superusers can rename any role.
89 Roles having <literal>CREATEROLE</literal> privilege can rename non-superuser
90 roles for which they have been granted <literal>ADMIN OPTION</literal>.
91 The current session user cannot be renamed.
92 (Connect as a different user if you need to do that.)
93 Because <literal>MD5</literal>-encrypted passwords use the role name as
94 cryptographic salt, renaming a role clears its password if the
95 password is <literal>MD5</literal>-encrypted.
96 </para>
97
98 <para>
99 The remaining variants change a role's session default for a configuration
100 variable, either for all databases or, when the <literal>IN
101 DATABASE</literal> clause is specified, only for sessions in the named
102 database. If <literal>ALL</literal> is specified instead of a role name,
103 this changes the setting for all roles. Using <literal>ALL</literal>
104 with <literal>IN DATABASE</literal> is effectively the same as using the
105 command <literal>ALTER DATABASE ... SET ...</literal>.
106 </para>
107
108 <para>
109 Whenever the role subsequently
110 starts a new session, the specified value becomes the session
111 default, overriding whatever setting is present in
112 <filename>postgresql.conf</filename> or has been received from the <command>postgres</command>
113 command line. This only happens at login time; executing
114 <link linkend="sql-set-role"><command>SET ROLE</command></link> or
115 <link linkend="sql-set-session-authorization"><command>SET SESSION AUTHORIZATION</command></link> does not cause new
116 configuration values to be set.
117 Settings set for all databases are overridden by database-specific settings
118 attached to a role. Settings for specific databases or specific roles override
119 settings for all roles.
120 </para>
121
122 <para>
123 Superusers can change anyone's session defaults. Roles having
124 <literal>CREATEROLE</literal> privilege can change defaults for non-superuser
125 roles for which they have been granted <literal>ADMIN OPTION</literal>.
126 Ordinary roles can only set defaults for themselves.
127 Certain configuration variables cannot be set this way, or can only be
128 set if a superuser issues the command. Only superusers can change a setting
129 for all roles in all databases.
130 </para>
131 </refsect1>
132
133 <refsect1 id="sql-alterrole-params">
134 <title>Parameters</title>
135
136 <variablelist>
137 <varlistentry id="sql-alterrole-params-name">
138 <term><replaceable class="parameter">name</replaceable></term>
139 <listitem>
140 <para>
141 The name of the role whose attributes are to be altered.
142 </para>
143 </listitem>
144 </varlistentry>
145
146 <varlistentry id="sql-alterrole-params-current-role">
147 <term><literal>CURRENT_ROLE</literal></term>
148 <term><literal>CURRENT_USER</literal></term>
149 <listitem>
150 <para>
151 Alter the current user instead of an explicitly identified role.
152 </para>
153 </listitem>
154 </varlistentry>
155
156 <varlistentry id="sql-alterrole-params-session-user">
157 <term><literal>SESSION_USER</literal></term>
158 <listitem>
159 <para>
160 Alter the current session user instead of an explicitly identified
161 role.
162 </para>
163 </listitem>
164 </varlistentry>
165
166 <varlistentry id="sql-alterrole-params-superuser">
167 <term><literal>SUPERUSER</literal></term>
168 <term><literal>NOSUPERUSER</literal></term>
169 <term><literal>CREATEDB</literal></term>
170 <term><literal>NOCREATEDB</literal></term>
171 <term><literal>CREATEROLE</literal></term>
172 <term><literal>NOCREATEROLE</literal></term>
173 <term><literal>INHERIT</literal></term>
174 <term><literal>NOINHERIT</literal></term>
175 <term><literal>LOGIN</literal></term>
176 <term><literal>NOLOGIN</literal></term>
177 <term><literal>REPLICATION</literal></term>
178 <term><literal>NOREPLICATION</literal></term>
179 <term><literal>BYPASSRLS</literal></term>
180 <term><literal>NOBYPASSRLS</literal></term>
181 <term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>
182 <term>[ <literal>ENCRYPTED</literal> ] <literal>PASSWORD</literal> '<replaceable class="parameter">password</replaceable>'</term>
183 <term><literal>PASSWORD NULL</literal></term>
184 <term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term>
185 <listitem>
186 <para>
187 These clauses alter attributes originally set by
188 <link linkend="sql-createrole"><command>CREATE ROLE</command></link>. For more information, see the
189 <command>CREATE ROLE</command> reference page.
190 </para>
191 </listitem>
192 </varlistentry>
193
194 <varlistentry id="sql-alterrole-params-new-name">
195 <term><replaceable>new_name</replaceable></term>
196 <listitem>
197 <para>
198 The new name of the role.
199 </para>
200 </listitem>
201 </varlistentry>
202
203 <varlistentry id="sql-alterrole-params-database-name">
204 <term><replaceable>database_name</replaceable></term>
205 <listitem>
206 <para>
207 The name of the database the configuration variable should be set in.
208 </para>
209 </listitem>
210 </varlistentry>
211
212 <varlistentry id="sql-alterrole-params-configuration-parameter">
213 <term><replaceable>configuration_parameter</replaceable></term>
214 <term><replaceable>value</replaceable></term>
215 <listitem>
216 <para>
217 Set this role's session default for the specified configuration
218 parameter to the given value. If
219 <replaceable>value</replaceable> is <literal>DEFAULT</literal>
220 or, equivalently, <literal>RESET</literal> is used, the
221 role-specific variable setting is removed, so the role will
222 inherit the system-wide default setting in new sessions. Use
223 <literal>RESET ALL</literal> to clear all role-specific settings.
224 <literal>SET FROM CURRENT</literal> saves the session's current value of
225 the parameter as the role-specific value.
226 If <literal>IN DATABASE</literal> is specified, the configuration
227 parameter is set or removed for the given role and database only.
228 </para>
229
230 <para>
231 Role-specific variable settings take effect only at login;
232 <link linkend="sql-set-role"><command>SET ROLE</command></link> and
233 <link linkend="sql-set-session-authorization"><command>SET SESSION AUTHORIZATION</command></link>
234 do not process role-specific variable settings.
235 </para>
236
237 <para>
238 See <xref linkend="sql-set"/> and <xref
239 linkend="runtime-config"/> for more information about allowed
240 parameter names and values.
241 </para>
242 </listitem>
243 </varlistentry>
244 </variablelist>
245 </refsect1>
246
247 <refsect1 id="sql-alterrole-notes">
248 <title>Notes</title>
249
250 <para>
251 Use <link linkend="sql-createrole"><command>CREATE ROLE</command></link>
252 to add new roles, and <link linkend="sql-droprole"><command>DROP ROLE</command></link> to remove a role.
253 </para>
254
255 <para>
256 <command>ALTER ROLE</command> cannot change a role's memberships.
257 Use <link linkend="sql-grant"><command>GRANT</command></link> and
258 <link linkend="sql-revoke"><command>REVOKE</command></link>
259 to do that.
260 </para>
261
262 <para>
263 Caution must be exercised when specifying an unencrypted password
264 with this command. The password will be transmitted to the server
265 in cleartext, and it might also be logged in the client's command
266 history or the server log. <xref linkend="app-psql"/>
267 contains a command
268 <command>\password</command> that can be used to change a
269 role's password without exposing the cleartext password.
270 </para>
271
272 <para>
273 It is also possible to tie a
274 session default to a specific database rather than to a role; see
275 <xref linkend="sql-alterdatabase"/>.
276 If there is a conflict, database-role-specific settings override role-specific
277 ones, which in turn override database-specific ones.
278 </para>
279 </refsect1>
280
281 <refsect1 id="sql-alterrole-examples">
282 <title>Examples</title>
283
284 <para>
285 Change a role's password:
286
287 <programlisting>
288 ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
289 </programlisting>
290 </para>
291
292 <para>
293 Remove a role's password:
294
295 <programlisting>
296 ALTER ROLE davide WITH PASSWORD NULL;
297 </programlisting>
298 </para>
299
300 <para>
301 Change a password expiration date, specifying that the password
302 should expire at midday on 4th May 2015 using
303 the time zone which is one hour ahead of <acronym>UTC</acronym>:
304 <programlisting>
305 ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
306 </programlisting>
307 </para>
308
309 <para>
310 Make a password valid forever:
311 <programlisting>
312 ALTER ROLE fred VALID UNTIL 'infinity';
313 </programlisting>
314 </para>
315
316 <para>
317 Give a role the ability to manage other roles and create new databases:
318
319 <programlisting>
320 ALTER ROLE miriam CREATEROLE CREATEDB;
321 </programlisting>
322 </para>
323
324 <para>
325 Give a role a non-default setting of the
326 <xref linkend="guc-maintenance-work-mem"/> parameter:
327
328 <programlisting>
329 ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
330 </programlisting>
331 </para>
332
333 <para>
334 Give a role a non-default, database-specific setting of the
335 <xref linkend="guc-client-min-messages"/> parameter:
336
337 <programlisting>
338 ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
339 </programlisting></para>
340 </refsect1>
341
342 <refsect1 id="sql-alterrole-compat">
343 <title>Compatibility</title>
344
345 <para>
346 The <command>ALTER ROLE</command> statement is a
347 <productname>PostgreSQL</productname> extension.
348 </para>
349 </refsect1>
350
351 <refsect1 id="sql-alterrole-see">
352 <title>See Also</title>
353
354 <simplelist type="inline">
355 <member><xref linkend="sql-createrole"/></member>
356 <member><xref linkend="sql-droprole"/></member>
357 <member><xref linkend="sql-alterdatabase"/></member>
358 <member><xref linkend="sql-set"/></member>
359 </simplelist>
360 </refsect1>
361 </refentry>