codekingpro/portable-devtools
115k
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 16.3 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">3ALTER 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 | NOSUPERUSER8 | CREATEDB | NOCREATEDB9 | CREATEROLE | NOCREATEROLE10 | INHERIT | NOINHERIT11 | LOGIN | NOLOGIN12 | REPLICATION | NOREPLICATION13 | BYPASSRLS | NOBYPASSRLS14 | CONNECTION LIMIT <em class="replaceable"><code>connlimit</code></em>15 | [ ENCRYPTED ] PASSWORD '<em class="replaceable"><code>password</code></em>' | PASSWORD NULL16 | VALID UNTIL '<em class="replaceable"><code>timestamp</code></em>'17 18ALTER ROLE <em class="replaceable"><code>name</code></em> RENAME TO <em class="replaceable"><code>new_name</code></em>19 20ALTER 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 }21ALTER 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 CURRENT22ALTER 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>23ALTER ROLE { <em class="replaceable"><code>role_specification</code></em> | ALL } [ IN DATABASE <em class="replaceable"><code>database_name</code></em> ] RESET ALL24 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_ROLE29 | CURRENT_USER30 | SESSION_USER31</pre></div><div class="refsect1" id="SQL-ALTERROLE-DESC"><h2>Description</h2><p>32 <code class="command">ALTER ROLE</code> changes the attributes of a33 <span class="productname">PostgreSQL</span> role.34 </p><p>35 The first variant of this command listed in the synopsis can change36 many of the role attributes that can be specified in37 <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; use40 <a class="link" href="sql-grant.html" title="GRANT"><code class="command">GRANT</code></a> and41 <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.44 Non-superuser roles having <code class="literal">CREATEROLE</code> privilege can45 change most of these properties, but only for non-superuser and46 non-replication roles for which they have been granted47 <code class="literal">ADMIN OPTION</code>. Non-superusers cannot change the48 <code class="literal">SUPERUSER</code> property and can change the49 <code class="literal">CREATEDB</code>, <code class="literal">REPLICATION</code>, and50 <code class="literal">BYPASSRLS</code> properties only if they possess the51 corresponding property themselves.52 Ordinary roles can only change their own password.53 </p><p>54 The second variant changes the name of the role.55 Database superusers can rename any role.56 Roles having <code class="literal">CREATEROLE</code> privilege can rename non-superuser57 roles for which they have been granted <code class="literal">ADMIN OPTION</code>.58 The current session user cannot be renamed.59 (Connect as a different user if you need to do that.)60 Because <code class="literal">MD5</code>-encrypted passwords use the role name as61 cryptographic salt, renaming a role clears its password if the62 password is <code class="literal">MD5</code>-encrypted.63 </p><p>64 The remaining variants change a role's session default for a configuration65 variable, either for all databases or, when the <code class="literal">IN66 DATABASE</code> clause is specified, only for sessions in the named67 database. If <code class="literal">ALL</code> is specified instead of a role name,68 this changes the setting for all roles. Using <code class="literal">ALL</code>69 with <code class="literal">IN DATABASE</code> is effectively the same as using the70 command <code class="literal">ALTER DATABASE ... SET ...</code>.71 </p><p>72 Whenever the role subsequently73 starts a new session, the specified value becomes the session74 default, overriding whatever setting is present in75 <code class="filename">postgresql.conf</code> or has been received from the <code class="command">postgres</code>76 command line. This only happens at login time; executing77 <a class="link" href="sql-set-role.html" title="SET ROLE"><code class="command">SET ROLE</code></a> or78 <a class="link" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION"><code class="command">SET SESSION AUTHORIZATION</code></a> does not cause new79 configuration values to be set.80 Settings set for all databases are overridden by database-specific settings81 attached to a role. Settings for specific databases or specific roles override82 settings for all roles.83 </p><p>84 Superusers can change anyone's session defaults. Roles having85 <code class="literal">CREATEROLE</code> privilege can change defaults for non-superuser86 roles for which they have been granted <code class="literal">ADMIN OPTION</code>.87 Ordinary roles can only set defaults for themselves.88 Certain configuration variables cannot be set this way, or can only be89 set if a superuser issues the command. Only superusers can change a setting90 for all roles in all databases.91 </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>92 The name of the role whose attributes are to be altered.93 </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>94 Alter the current user instead of an explicitly identified role.95 </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>96 Alter the current session user instead of an explicitly identified97 role.98 </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>99 These clauses alter attributes originally set by100 <a class="link" href="sql-createrole.html" title="CREATE ROLE"><code class="command">CREATE ROLE</code></a>. For more information, see the101 <code class="command">CREATE ROLE</code> reference page.102 </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>103 The new name of the role.104 </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>105 The name of the database the configuration variable should be set in.106 </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>107 Set this role's session default for the specified configuration108 parameter to the given value. If109 <em class="replaceable"><code>value</code></em> is <code class="literal">DEFAULT</code>110 or, equivalently, <code class="literal">RESET</code> is used, the111 role-specific variable setting is removed, so the role will112 inherit the system-wide default setting in new sessions. Use113 <code class="literal">RESET ALL</code> to clear all role-specific settings.114 <code class="literal">SET FROM CURRENT</code> saves the session's current value of115 the parameter as the role-specific value.116 If <code class="literal">IN DATABASE</code> is specified, the configuration117 parameter is set or removed for the given role and database only.118 </p><p>119 Role-specific variable settings take effect only at login;120 <a class="link" href="sql-set-role.html" title="SET ROLE"><code class="command">SET ROLE</code></a> and121 <a class="link" href="sql-set-session-authorization.html" title="SET SESSION AUTHORIZATION"><code class="command">SET SESSION AUTHORIZATION</code></a>122 do not process role-specific variable settings.123 </p><p>124 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 20. Server Configuration">Chapter 20</a> for more information about allowed125 parameter names and values.126 </p></dd></dl></div></div><div class="refsect1" id="SQL-ALTERROLE-NOTES"><h2>Notes</h2><p>127 Use <a class="link" href="sql-createrole.html" title="CREATE ROLE"><code class="command">CREATE ROLE</code></a>128 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.129 </p><p>130 <code class="command">ALTER ROLE</code> cannot change a role's memberships.131 Use <a class="link" href="sql-grant.html" title="GRANT"><code class="command">GRANT</code></a> and132 <a class="link" href="sql-revoke.html" title="REVOKE"><code class="command">REVOKE</code></a>133 to do that.134 </p><p>135 Caution must be exercised when specifying an unencrypted password136 with this command. The password will be transmitted to the server137 in cleartext, and it might also be logged in the client's command138 history or the server log. <a class="xref" href="app-psql.html" title="psql"><span class="refentrytitle"><span class="application">psql</span></span></a>139 contains a command140 <code class="command">\password</code> that can be used to change a141 role's password without exposing the cleartext password.142 </p><p>143 It is also possible to tie a144 session default to a specific database rather than to a role; see145 <a class="xref" href="sql-alterdatabase.html" title="ALTER DATABASE"><span class="refentrytitle">ALTER DATABASE</span></a>.146 If there is a conflict, database-role-specific settings override role-specific147 ones, which in turn override database-specific ones.148 </p></div><div class="refsect1" id="SQL-ALTERROLE-EXAMPLES"><h2>Examples</h2><p>149 Change a role's password:150 151</p><pre class="programlisting">152ALTER ROLE davide WITH PASSWORD 'hu8jmn3';153</pre><p>154 </p><p>155 Remove a role's password:156 157</p><pre class="programlisting">158ALTER ROLE davide WITH PASSWORD NULL;159</pre><p>160 </p><p>161 Change a password expiration date, specifying that the password162 should expire at midday on 4th May 2015 using163 the time zone which is one hour ahead of <acronym class="acronym">UTC</acronym>:164</p><pre class="programlisting">165ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';166</pre><p>167 </p><p>168 Make a password valid forever:169</p><pre class="programlisting">170ALTER ROLE fred VALID UNTIL 'infinity';171</pre><p>172 </p><p>173 Give a role the ability to manage other roles and create new databases:174 175</p><pre class="programlisting">176ALTER ROLE miriam CREATEROLE CREATEDB;177</pre><p>178 </p><p>179 Give a role a non-default setting of the180 <a class="xref" href="runtime-config-resource.html#GUC-MAINTENANCE-WORK-MEM">maintenance_work_mem</a> parameter:181 182</p><pre class="programlisting">183ALTER ROLE worker_bee SET maintenance_work_mem = 100000;184</pre><p>185 </p><p>186 Give a role a non-default, database-specific setting of the187 <a class="xref" href="runtime-config-client.html#GUC-CLIENT-MIN-MESSAGES">client_min_messages</a> parameter:188 189</p><pre class="programlisting">190ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;191</pre></div><div class="refsect1" id="SQL-ALTERROLE-COMPAT"><h2>Compatibility</h2><p>192 The <code class="command">ALTER ROLE</code> statement is a193 <span class="productname">PostgreSQL</span> extension.194 </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 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER ROUTINE</td></tr></table></div></body></html>