Supported versions: Current (18) / 17 / 16 / 15 / 14

Development versions: 19 / 20devel

Unsupported versions: 13 / 12 / 11 / 10 / 9.6 / 9.5 / 9.4 / 9.3 / 9.2 / 9.1 / 9.0 / 8.4 / 8.3 / 8.2 / 8.1 / 8.0 / 7.4 / 7.3 / 7.2 / 7.1

Historical version. PostgreSQL 7.1 has reached end of support. See the current manual.

ALTER USER

Name

ALTER USER -- Modifies user account information

Synopsis

ALTER USER username
    [ WITH PASSWORD 'password' ]
    [ CREATEDB | NOCREATEDB ] [ CREATEUSER | NOCREATEUSER ]
    [ VALID UNTIL 'abstime' ]

Inputs

username: The name of the user whose details are to be altered.
password: The new password to be used for this account.
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.

Outputs

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.

Description

ALTER USER is used to change the attributes of a user's Postgres account. Only a database superuser can change privileges and password expiration with this command. Ordinary users can only change their own password.

Use CREATE USER to create a new user and DROP USER to remove a user.

Usage

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

SQL92

There is no ALTER USER statement in SQL92. The standard leaves the definition of users to the implementation.

Report a documentation issue

Read the upstream documentation. For corrections, first check the current manual.