~ubuntu-branches/ubuntu/hardy/postgresql-8.4/hardy-backports

ALTER ROLE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> { TO | = } { <replaceable>value</replaceable> | DEFAULT }

ALTER ROLE <replaceable class="PARAMETER">name</replaceable> SET <replaceable>configuration_parameter</replaceable> FROM CURRENT

ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET <replaceable>configuration_parameter</replaceable>

ALTER ROLE <replaceable class="PARAMETER">name</replaceable> RESET ALL

</synopsis>

</refsynopsisdiv>

<title>Description</title>

<para>

<command>ALTER ROLE</command> changes the attributes of a

<productname>PostgreSQL</productname> role.

</para>

<para>

The first variant of this command listed in the synopsis can change

many of the role attributes that can be specified in

<xref linkend="sql-createrole" endterm="sql-createrole-title">.

(All the possible attributes are covered,

except that there are no options for adding or removing memberships; use

<xref linkend="SQL-GRANT" endterm="SQL-GRANT-title"> and

<xref linkend="SQL-REVOKE" endterm="SQL-REVOKE-title"> for that.)

Attributes not mentioned in the command retain their previous settings.

Database superusers can change any of these settings for any role.

Roles having <literal>CREATEROLE</> privilege can change any of these

settings, but only for non-superuser roles.

Ordinary roles can only change their own password.

</para>

<para>

The second variant changes the name of the role.

Database superusers can rename any role.

Roles having <literal>CREATEROLE</> privilege can rename non-superuser

roles.

The current session user cannot be renamed.

(Connect as a different user if you need to do that.)

Because <literal>MD5</>-encrypted passwords use the role name as

cryptographic salt, renaming a role clears its password if the

password is <literal>MD5</>-encrypted.

</para>

<para>

The remaining variants change a role's session default for a

specified configuration variable. Whenever the role subsequently

starts a new session, the specified value becomes the session

default, overriding whatever setting is present in

<filename>postgresql.conf</> or has been received from the postgres

command line. This only happens at login time, so configuration

settings associated with a role to which you've <xref

linkend="sql-set-role" endterm="sql-set-role-title"> will be ignored.

Superusers can change anyone's session defaults. Roles having

<literal>CREATEROLE</> privilege can change defaults for non-superuser

roles. Certain variables cannot be set this way, or can only be

set if a superuser issues the command.

</para>

</refsect1>

<title>Parameters</title>

100

101

102

103

104

105

<para>

106

The name of the role whose attributes are to be altered.

107

</para>

108

</listitem>

109

</varlistentry>

110

111

112

<term><literal>SUPERUSER</literal></term>

113

<term><literal>NOSUPERUSER</literal></term>

114

<term><literal>CREATEDB</></term>

115

<term><literal>NOCREATEDB</></term>

116

<term><literal>CREATEROLE</literal></term>

117

<term><literal>NOCREATEROLE</literal></term>

118

<term><literal>CREATEUSER</literal></term>

119

<term><literal>NOCREATEUSER</literal></term>

120

<term><literal>INHERIT</literal></term>

121

<term><literal>NOINHERIT</literal></term>

122

<term><literal>LOGIN</literal></term>

123

<term><literal>NOLOGIN</literal></term>

124

<term><literal>CONNECTION LIMIT</literal> <replaceable class="parameter">connlimit</replaceable></term>

125

<term><literal>PASSWORD</> <replaceable class="parameter">password</replaceable></term>

126

<term><literal>ENCRYPTED</></term>

127

<term><literal>UNENCRYPTED</></term>

128

<term><literal>VALID UNTIL</literal> '<replaceable class="parameter">timestamp</replaceable>'</term>

129

130

<para>

131

These clauses alter attributes originally set by

132

<xref linkend="SQL-CREATEROLE"

133

endterm="SQL-CREATEROLE-title">. For more information, see the

134

<command>CREATE ROLE</command> reference page.

135

</para>

136

</listitem>

137

</varlistentry>

138

139

140

<term><replaceable>newname</replaceable></term>

141

142

<para>

143

The new name of the role.

144

</para>

145

</listitem>

146

</varlistentry>

147

148

149

<term><replaceable>configuration_parameter</replaceable></term>

150

<term><replaceable>value</replaceable></term>

151

152

<para>

153

Set this role's session default for the specified configuration

154

parameter to the given value. If

155

<replaceable>value</replaceable> is <literal>DEFAULT</literal>

156

or, equivalently, <literal>RESET</literal> is used, the

157

role-specific variable setting is removed, so the role will

158

inherit the system-wide default setting in new sessions. Use

159

<literal>RESET ALL</literal> to clear all role-specific settings.

160

<literal>SET FROM CURRENT</> saves the session's current value of

161

the parameter as the role-specific value.

162

</para>

163

164

<para>

165

Role-specific variable setting take effect only at login;

166

167

does not process role-specific variable settings.

168

</para>

169

170

<para>

171

See <xref linkend="sql-set" endterm="sql-set-title"> and <xref

172

linkend="runtime-config"> for more information about allowed

173

parameter names and values.

174

</para>

175

</listitem>

176

</varlistentry>

177

</variablelist>

178

</refsect1>

179

180

181

<title>Notes</title>

182

183

<para>

184

Use <xref linkend="SQL-CREATEROLE" endterm="SQL-CREATEROLE-title">

185

to add new roles, and <xref linkend="SQL-DROPROLE"

186

endterm="SQL-DROPROLE-title"> to remove a role.

187

</para>

188

189

<para>

190

<command>ALTER ROLE</command> cannot change a role's memberships.

191

Use <xref linkend="SQL-GRANT" endterm="SQL-GRANT-title"> and

192

193

to do that.

194

</para>

195

196

<para>

197

Caution must be exercised when specifying an unencrypted password

198

with this command. The password will be transmitted to the server

199

in cleartext, and it might also be logged in the client's command

200

history or the server log. <xref linkend="app-psql"

201

endterm="app-psql-title"> contains a command

202

<command>\password</command> that can be used to safely change a

203

role's password.

204

</para>

205

206

<para>

207

It is also possible to tie a

208

session default to a specific database rather than to a role; see

209

<xref linkend="sql-alterdatabase" endterm="sql-alterdatabase-title">.

210

Role-specific settings override database-specific

211

ones if there is a conflict.

212

</para>

213

</refsect1>

214

215

216

<title>Examples</title>

217

218

<para>

219

Change a role's password:

220

221

222

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';

223

</programlisting>

224

</para>

225

226

<para>

227

Remove a role's password:

228

229

230

ALTER ROLE davide WITH PASSWORD NULL;

231

</programlisting>

232

</para>

233

234

<para>

235

Change a password expiration date, specifying that the password

236

should expire at midday on 4th May 2015 using

237

the time zone which is one hour ahead of <acronym>UTC</>:

238

239

ALTER ROLE chris VALID UNTIL 'May 4 12:00:00 2015 +1';

240

</programlisting>

241

</para>

242

243

<para>

244

Make a password valid forever:

245

246

ALTER ROLE fred VALID UNTIL 'infinity';

247

</programlisting>

248

</para>

249

250

<para>

251

Give a role the ability to create other roles and new databases:

252

253

254

ALTER ROLE miriam CREATEROLE CREATEDB;

255

</programlisting>

256

</para>

257

258

<para>

259

Give a role a non-default setting of the

260

<xref linkend="guc-maintenance-work-mem"> parameter:

261

262

263

ALTER ROLE worker_bee SET maintenance_work_mem = 100000;

264

</programlisting>

265

</para>

266

</refsect1>

267

268

269

<title>Compatibility</title>

270

271

<para>

272

The <command>ALTER ROLE</command> statement is a

273

<productname>PostgreSQL</productname> extension.

274

</para>

275

</refsect1>

276

277

278

279

280

281

282

283

284

</simplelist>

285

</refsect1>

286

</refentry>

Older »