ALTER USER(l)SQL - Language Statements (2002-11-22) ALTER USER(l)
NAME
ALTER USER - change a database user account
SYNOPSIS
ALTER USER username [ [ WITH ] option [ ... ] ]
where option can be:
[ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
| CREATEDB | NOCREATEDB
| CREATEUSER | NOCREATEUSER
| VALID UNTIL 'abstime'
ALTER USER username SET variable { TO | = } { value | DEFAULT }
ALTER USER username RESET variable
DESCRIPTION
ALTER USER is used to change the attributes of a PostgreSQL
user account. Attributes not mentioned in the command retain
their previous settings.
The first variant of this command in the synopsis changes
certain global user privileges and authentication settings.
(See below for details.) Only a database superuser can
change privileges and password expiration with this command.
Ordinary users can only change their own password.
The second and the third variant change a user's session
default for a specified configuration variable. Whenever the
user subsequently starts a new session, the specified value
becomes the session default, overriding whatever setting is
present in postgresql.conf or has been received from the
postmaster. Ordinary users can change their own session
defaults. Superusers can change anyone's session defaults.
PARAMETERS
username
The name of the user whose attributes are to be
altered.
password
The new password to be used for this account.
ENCRYPTED
UNENCRYPTED
These key words control whether the password is stored
encrypted in pg_shadow. (See CREATE USER
[create_user(l)] for more information about this
choice.)
Page 1 (printed 3/24/03)
ALTER USER(l)SQL - Language Statements (2002-11-22) ALTER USER(l)
CREATEDB
NOCREATEDB
These clauses define a user's ability to create
databases. If CREATEDB is specified, the user being
defined will be allowed to create his own databases.
Using NOCREATEDB will deny a user the ability to create
databases.
CREATEUSER
NOCREATEUSER
These clauses determine whether a user will be
permitted to create new users himself. This option will
also make the user a superuser who can override all
access restrictions.
abstime
The date (and, optionally, the time) at which this
user's password is to expire.
variable
value
Set this user's session default for the specified
configuration variable to the given value. If value is
DEFAULT or, equivalently, RESET is used, the user-
specific variable setting is removed and the user will
inherit the system-wide default setting in new
sessions. Use RESET ALL to clear all settings.
See SET [set(l)] and the Administrator's Guide for more
information about allowed variable names and values.
DIAGNOSTICS
ALTER USER
Message returned if the alteration was successful.
ERROR: ALTER USER: user "username" does not exist
Error message returned if the specified user is not
known to the database.
NOTES
Use CREATE USER [create_user(l)] to add new users, and DROP
USER [drop_user(l)] to remove a user.
ALTER USER cannot change a user's group memberships. Use
ALTER GROUP [alter_group(l)] to do that.
Using ALTER DATABASE [alter_database(l)], it is also
possible to tie a session default to a specific database
rather than a user.
Page 2 (printed 3/24/03)
ALTER USER(l)SQL - Language Statements (2002-11-22) ALTER USER(l)
EXAMPLES
Change a user password:
ALTER USER davide WITH PASSWORD 'hu8jmn3';
Change a user's valid until date:
ALTER USER manuel VALID UNTIL 'Jan 31 2030';
Change a user's valid until date, specifying that his
authorization should expire at midday on 4th May 1998 using
the time zone which is one hour ahead of UTC:
ALTER USER chris VALID UNTIL 'May 4 12:00:00 1998 +1';
Give a user the ability to create other users and new
databases:
ALTER USER miriam CREATEUSER CREATEDB;
COMPATIBILITY
The ALTER USER statement is a PostgreSQL extension. The SQL
standard leaves the definition of users to the
implementation.
SEE ALSO
CREATE USER [create_user(l)], DROP USER [drop_user(l)], SET
[set(l)]
Page 3 (printed 3/24/03)
