Existing users may only be modified by PostgreSQL superusers. Possible modifications include each of the options
available at the creation of the user (e.g., password, password expiration date, global rights), except for the system ID
of an existing user, which may not be modified. Modification of existing users is achieved through the use of the
ALTER USER SQL statement.
Here is the syntax for ALTER USER:
ALTER USER
username
[ WITH PASSWORD '
password
' ]
[ CREATEDB | NOCREATEDB ]
[ CREATEUSER | NOCREATEUSER ]
[ VALID UNTIL '
abstime
' ]
The required
username
argument specifies which user is to be modified. Any of the following
parameters may additionally be specified:
-
WITH PASSWORD '
password
'
-
Sets
username
's password to
password
.
-
CREATEDB | NOCREATEDB
-
Grants or revokes from
username
the right to create databases.
-
CREATEUSER | NOCREATEUSER
-
Grants or revokes from
username
the status of superuser, which enables
all
possible right within PostgreSQL (most notably the ability to create and destroy users and superusers).
-
VALID UNTIL '
abstime
'
-
Sets
username
's password to expire at
abstime
, which must be of some valid
timestamp format. This value is only relevant for systems requiring password authentication, and is otherwise ignored
(e.g., for trusted sites).
A common function of ALTER USER is to reset the password (and potentially the
expiration date) of a user. If a PostgreSQL user had an expiration date set when their user was originally added, and that date has passed, and the user
requires password-based authentication, a superuser will have to reset both the password
and
the
expiration date to re-activate a user's ability to connect. If you want to cause a user's password to never expire, set it
to the special timestamp
infinity
.
Example 10-6 modifies a user named salesuser. The user's
password is set to
n3Wp4s4
by the WITH PASSWORD clause, and set to
expire on January 1st, 2003 by the VALID UNTIL clause.
Example 10-6. Resetting a password
template1=#
ALTER USER salesuser
template1-#
WITH PASSWORD 'n3WP4s4'
template1-#
VALID UNTIL '2003-01-01';
ALTER USER
At times you may wish to grant a user additional rights beyond those originally granted to them. The use of the
CREATEUSER keyword in Example 10-7 modifies the user
salesuser to have all rights in PostgreSQL, making the user into a superuser. Note
that this makes the CREATEDB right moot, as superusers can create databases
implicitly.
Example 10-7. Adding superuser rights
template1=#
ALTER USER salesuser
template1-#
CREATEUSER;
ALTER USER
Conversely, there may be times when a user no longer deserves rights that have been granted in the past. These rights
may be just as easily removed by a superuser with the NOCREATEDB and
NOCREATEUSER keywords.
Example 10-8. Removing superuser rights
template1=#
ALTER USER salesuser
template1-#
NOCREATEDB NOCREATEUSER;
ALTER USER
|
Warning
|
|
As any superuser may revoke rights from another superuser, or even remove another superuser, it is wise to be
extremely careful
when granting the CREATEUSER right.
|
