| 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> |