begriffs open source - ai-pg/blob - full-docs/html/sql-alterrole.html

   1 <?xml version="1.0" encoding="UTF-8" standalone="no"?>
   2 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>ALTER ROLE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-alterpublication.html" title="ALTER PUBLICATION" /><link rel="next" href="sql-alterroutine.html" title="ALTER ROUTINE" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">ALTER ROLE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alterpublication.html" title="ALTER PUBLICATION">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-alterroutine.html" title="ALTER ROUTINE">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-ALTERROLE"><div class="titlepage"></div><a id="id-1.9.3.26.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER ROLE</span></h2><p>ALTER ROLE — change a database role</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
   3 ALTER ROLE <em class="replaceable"><code>role_specification</code></em> [ WITH ] <em class="replaceable"><code>option</code></em> [ ... ]
   4
   5 <span class="phrase">where <em class="replaceable"><code>option</code></em> can be:</span>
   6
   7       SUPERUSER | NOSUPERUSER
   8     | CREATEDB | NOCREATEDB
   9     | CREATEROLE | NOCREATEROLE
  10     | INHERIT | NOINHERIT
  11     | LOGIN | NOLOGIN
  12     | REPLICATION | NOREPLICATION
  13     | BYPASSRLS | NOBYPASSRLS
  14     | CONNECTION LIMIT <em class="replaceable"><code>connlimit</code></em>
  15     | [ ENCRYPTED ] PASSWORD '<em class="replaceable"><code>password</code></em>' | PASSWORD NULL
  16     | VALID UNTIL '<em class="replaceable"><code>timestamp</code></em>'
  17
  18 ALTER ROLE <em class="replaceable"><code>name</code></em> RENAME TO <em class="replaceable"><code>new_name</code></em>
  19
  20 ALTER ROLE { <em class="replaceable"><code>role_specification</code></em> | ALL } [ IN DATABASE <em class="replaceable"><code>database_name</code></em> ] SET <em class="replaceable"><code>configuration_parameter</code></em> { TO | = } { <em class="replaceable"><code>value</code></em> | DEFAULT }
  21 ALTER ROLE { <em class="replaceable"><code>role_specification</code></em> | ALL } [ IN DATABASE <em class="replaceable"><code>database_name</code></em> ] SET <em class="replaceable"><code>configuration_parameter</code></em> FROM CURRENT
  22 ALTER ROLE { <em class="replaceable"><code>role_specification</code></em> | ALL } [ IN DATABASE <em class="replaceable"><code>database_name</code></em> ] RESET <em class="replaceable"><code>configuration_parameter</code></em>
  23 ALTER ROLE { <em class="replaceable"><code>role_specification</code></em> | ALL } [ IN DATABASE <em class="replaceable"><code>database_name</code></em> ] RESET ALL
  24
  25 <span class="phrase">where <em class="replaceable"><code>role_specification</code></em> can be:</span>
  26
  27     <em class="replaceable"><code>role_name</code></em>
  28   | CURRENT_ROLE
  29   | CURRENT_USER
  30   | SESSION_USER
  31 </pre></div><div class="refsect1" id="SQL-ALTERROLE-DESC"><h2>Description</h2><p>
  32    <code class="command">ALTER ROLE</code> changes the attributes of a
  33    <span class="productname">PostgreSQL</span> role.
  34   </p><p>
  35    The first variant of this command listed in the synopsis can change
  36    many of the role attributes that can be specified in
  37    <a class="link" href="sql-createrole.html" title="CREATE ROLE"><code class="command">CREATE ROLE</code></a>.
  38    (All the possible attributes are covered,
  39    except that there are no options for adding or removing memberships; use
  40    <a class="link" href="sql-grant.html" title="GRANT"><code class="command">GRANT</code></a> and
  41    <a class="link" href="sql-revoke.html" title="REVOKE"><code class="command">REVOKE</code></a> for that.)
  42    Attributes not mentioned in the command retain their previous settings.
  43    Database superusers can change any of these settings for any role, except
  44    for changing the <code class="literal">SUPERUSER</code> property for the
  45    <a class="glossterm" href="glossary.html#GLOSSARY-BOOTSTRAP-SUPERUSER"><em class="glossterm"><a class="glossterm" href="glossary.html#GLOSSARY-BOOTSTRAP-SUPERUSER" title="Bootstrap superuser">bootstrap superuser</a></em></a>.
  46    Non-superuser roles having <code class="literal">CREATEROLE</code> privilege can
  47    change most of these properties, but only for non-superuser and
  48    non-replication roles for which they have been granted
  49    <code class="literal">ADMIN OPTION</code>. Non-superusers cannot change the
  50    <code class="literal">SUPERUSER</code> property and can change the
  51    <code class="literal">CREATEDB</code>, <code class="literal">REPLICATION</code>, and
  52    <code class="literal">BYPASSRLS</code> properties only if they possess the
  53    corresponding property themselves.
  54    Ordinary roles can only change their own password.
  55   </p><p>
  56    The second variant changes the name of the role.
  57    Database superusers can rename any role.
  58    Roles having <code class="literal">CREATEROLE</code> privilege can rename non-superuser
  59    roles for which they have been granted <code class="literal">ADMIN OPTION</code>.
  60    The current session user cannot be renamed.
  61    (Connect as a different user if you need to do that.)
  62    Because <code class="literal">MD5</code>-encrypted passwords use the role name as
  63    cryptographic salt, renaming a role clears its password if the
  64    password is <code class="literal">MD5</code>-encrypted.
  65   </p><p>
  66    The remaining variants change a role's session default for a configuration
  67    variable, either for all databases or, when the <code class="literal">IN
  68    DATABASE</code> clause is specified, only for sessions in the named
  69    database.  If <code class="literal">ALL</code> is specified instead of a role name,
  70    this changes the setting for all roles.  Using <code class="literal">ALL</code>
  71    with <code class="literal">IN DATABASE</code> is effectively the same as using the
  72    command <code class="literal">ALTER DATABASE ... SET ...</code>.
  73   </p><p>
  74    Whenever the role subsequently
  75    starts a new session, the specified value becomes the session
  76    default, overriding whatever setting is present in
  77    <code class="filename">postgresql.conf</code> or has been received from the <code class="command">postgres</code>
  78    command line. This only happens at login time; executing
  79    <a class="link" href="sql-set-role.html" title="SET ROLE"><code class="command">SET ROLE</code></a> or
  80    <a class="link" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION"><code class="command">SET SESSION AUTHORIZATION</code></a> does not cause new
  81    configuration values to be set.
  82    Settings set for all databases are overridden by database-specific settings
  83    attached to a role.  Settings for specific databases or specific roles override
  84    settings for all roles.
  85   </p><p>
  86    Superusers can change anyone's session defaults. Roles having
  87    <code class="literal">CREATEROLE</code> privilege can change defaults for non-superuser
  88    roles for which they have been granted <code class="literal">ADMIN OPTION</code>.
  89    Ordinary roles can only set defaults for themselves.
  90    Certain configuration variables cannot be set this way, or can only be
  91    set if a superuser issues the command.  Only superusers can change a setting
  92    for all roles in all databases.
  93   </p></div><div class="refsect1" id="SQL-ALTERROLE-PARAMS"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt id="SQL-ALTERROLE-PARAMS-NAME"><span class="term"><em class="replaceable"><code>name</code></em></span> <a href="#SQL-ALTERROLE-PARAMS-NAME" class="id_link">#</a></dt><dd><p>
  94         The name of the role whose attributes are to be altered.
  95        </p></dd><dt id="SQL-ALTERROLE-PARAMS-CURRENT-ROLE"><span class="term"><code class="literal">CURRENT_ROLE</code><br /></span><span class="term"><code class="literal">CURRENT_USER</code></span> <a href="#SQL-ALTERROLE-PARAMS-CURRENT-ROLE" class="id_link">#</a></dt><dd><p>
  96         Alter the current user instead of an explicitly identified role.
  97        </p></dd><dt id="SQL-ALTERROLE-PARAMS-SESSION-USER"><span class="term"><code class="literal">SESSION_USER</code></span> <a href="#SQL-ALTERROLE-PARAMS-SESSION-USER" class="id_link">#</a></dt><dd><p>
  98         Alter the current session user instead of an explicitly identified
  99         role.
 100        </p></dd><dt id="SQL-ALTERROLE-PARAMS-SUPERUSER"><span class="term"><code class="literal">SUPERUSER</code><br /></span><span class="term"><code class="literal">NOSUPERUSER</code><br /></span><span class="term"><code class="literal">CREATEDB</code><br /></span><span class="term"><code class="literal">NOCREATEDB</code><br /></span><span class="term"><code class="literal">CREATEROLE</code><br /></span><span class="term"><code class="literal">NOCREATEROLE</code><br /></span><span class="term"><code class="literal">INHERIT</code><br /></span><span class="term"><code class="literal">NOINHERIT</code><br /></span><span class="term"><code class="literal">LOGIN</code><br /></span><span class="term"><code class="literal">NOLOGIN</code><br /></span><span class="term"><code class="literal">REPLICATION</code><br /></span><span class="term"><code class="literal">NOREPLICATION</code><br /></span><span class="term"><code class="literal">BYPASSRLS</code><br /></span><span class="term"><code class="literal">NOBYPASSRLS</code><br /></span><span class="term"><code class="literal">CONNECTION LIMIT</code> <em class="replaceable"><code>connlimit</code></em><br /></span><span class="term">[ <code class="literal">ENCRYPTED</code> ] <code class="literal">PASSWORD</code> '<em class="replaceable"><code>password</code></em>'<br /></span><span class="term"><code class="literal">PASSWORD NULL</code><br /></span><span class="term"><code class="literal">VALID UNTIL</code> '<em class="replaceable"><code>timestamp</code></em>'</span> <a href="#SQL-ALTERROLE-PARAMS-SUPERUSER" class="id_link">#</a></dt><dd><p>
 101         These clauses alter attributes originally set by
 102         <a class="link" href="sql-createrole.html" title="CREATE ROLE"><code class="command">CREATE ROLE</code></a>. For more information, see the
 103         <code class="command">CREATE ROLE</code> reference page.
 104        </p></dd><dt id="SQL-ALTERROLE-PARAMS-NEW-NAME"><span class="term"><em class="replaceable"><code>new_name</code></em></span> <a href="#SQL-ALTERROLE-PARAMS-NEW-NAME" class="id_link">#</a></dt><dd><p>
 105         The new name of the role.
 106        </p></dd><dt id="SQL-ALTERROLE-PARAMS-DATABASE-NAME"><span class="term"><em class="replaceable"><code>database_name</code></em></span> <a href="#SQL-ALTERROLE-PARAMS-DATABASE-NAME" class="id_link">#</a></dt><dd><p>
 107            The name of the database the configuration variable should be set in.
 108          </p></dd><dt id="SQL-ALTERROLE-PARAMS-CONFIGURATION-PARAMETER"><span class="term"><em class="replaceable"><code>configuration_parameter</code></em><br /></span><span class="term"><em class="replaceable"><code>value</code></em></span> <a href="#SQL-ALTERROLE-PARAMS-CONFIGURATION-PARAMETER" class="id_link">#</a></dt><dd><p>
 109         Set this role's session default for the specified configuration
 110         parameter to the given value.  If
 111         <em class="replaceable"><code>value</code></em> is <code class="literal">DEFAULT</code>
 112         or, equivalently, <code class="literal">RESET</code> is used, the
 113         role-specific variable setting is removed, so the role will
 114         inherit the system-wide default setting in new sessions.  Use
 115         <code class="literal">RESET ALL</code> to clear all role-specific settings.
 116         <code class="literal">SET FROM CURRENT</code> saves the session's current value of
 117         the parameter as the role-specific value.
 118         If <code class="literal">IN DATABASE</code> is specified, the configuration
 119         parameter is set or removed for the given role and database only.
 120        </p><p>
 121         Role-specific variable settings take effect only at login;
 122         <a class="link" href="sql-set-role.html" title="SET ROLE"><code class="command">SET ROLE</code></a> and
 123         <a class="link" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION"><code class="command">SET SESSION AUTHORIZATION</code></a>
 124         do not process role-specific variable settings.
 125        </p><p>
 126         See <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a> and <a class="xref" href="runtime-config.html" title="Chapter 19. Server Configuration">Chapter 19</a> for more information about allowed
 127         parameter names and values.
 128        </p></dd></dl></div></div><div class="refsect1" id="SQL-ALTERROLE-NOTES"><h2>Notes</h2><p>
 129    Use <a class="link" href="sql-createrole.html" title="CREATE ROLE"><code class="command">CREATE ROLE</code></a>
 130    to add new roles, and <a class="link" href="sql-droprole.html" title="DROP ROLE"><code class="command">DROP ROLE</code></a> to remove a role.
 131   </p><p>
 132    <code class="command">ALTER ROLE</code> cannot change a role's memberships.
 133    Use <a class="link" href="sql-grant.html" title="GRANT"><code class="command">GRANT</code></a> and
 134    <a class="link" href="sql-revoke.html" title="REVOKE"><code class="command">REVOKE</code></a>
 135    to do that.
 136   </p><p>
 137    Caution must be exercised when specifying an unencrypted password
 138    with this command.  The password will be transmitted to the server
 139    in cleartext, and it might also be logged in the client's command
 140    history or the server log.  <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a>
 141    contains a command
 142    <code class="command">\password</code> that can be used to change a
 143    role's password without exposing the cleartext password.
 144   </p><p>
 145    It is also possible to tie a
 146    session default to a specific database rather than to a role; see
 147    <a class="xref" href="sql-alterdatabase.html" title="ALTER DATABASE"><span class="refentrytitle">ALTER DATABASE</span></a>.
 148    If there is a conflict, database-role-specific settings override role-specific
 149    ones, which in turn override database-specific ones.
 150   </p></div><div class="refsect1" id="SQL-ALTERROLE-EXAMPLES"><h2>Examples</h2><p>
 151    Change a role's password:
 152
 153 </p><pre class="programlisting">
 154 ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
 155 </pre><p>
 156   </p><p>
 157    Remove a role's password:
 158
 159 </p><pre class="programlisting">
 160 ALTER ROLE davide WITH PASSWORD NULL;
 161 </pre><p>
 162   </p><p>
 163    Change a password expiration date, specifying that the password
 164    should expire at midday on 4th May 2015 using
 165    the time zone which is one hour ahead of <acronym class="acronym">UTC</acronym>:
 166 </p><pre class="programlisting">
 167 ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';
 168 </pre><p>
 169   </p><p>
 170    Make a password valid forever:
 171 </p><pre class="programlisting">
 172 ALTER ROLE fred VALID UNTIL 'infinity';
 173 </pre><p>
 174   </p><p>
 175    Give a role the ability to manage other roles and create new databases:
 176
 177 </p><pre class="programlisting">
 178 ALTER ROLE miriam CREATEROLE CREATEDB;
 179 </pre><p>
 180   </p><p>
 181    Give a role a non-default setting of the
 182    <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">maintenance_work_mem</a> parameter:
 183
 184 </p><pre class="programlisting">
 185 ALTER ROLE worker_bee SET maintenance_work_mem = 100000;
 186 </pre><p>
 187   </p><p>
 188    Give a role a non-default, database-specific setting of the
 189    <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> parameter:
 190
 191 </p><pre class="programlisting">
 192 ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;
 193 </pre></div><div class="refsect1" id="SQL-ALTERROLE-COMPAT"><h2>Compatibility</h2><p>
 194    The <code class="command">ALTER ROLE</code> statement is a
 195    <span class="productname">PostgreSQL</span> extension.
 196   </p></div><div class="refsect1" id="SQL-ALTERROLE-SEE"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createrole.html" title="CREATE ROLE"><span class="refentrytitle">CREATE ROLE</span></a>, <a class="xref" href="sql-droprole.html" title="DROP ROLE"><span class="refentrytitle">DROP ROLE</span></a>, <a class="xref" href="sql-alterdatabase.html" title="ALTER DATABASE"><span class="refentrytitle">ALTER DATABASE</span></a>, <a class="xref" href="sql-set.html" title="SET"><span class="refentrytitle">SET</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-alterpublication.html" title="ALTER PUBLICATION">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-alterroutine.html" title="ALTER ROUTINE">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER PUBLICATION </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER ROUTINE</td></tr></table></div></body></html>