ALTER USER

Name

ALTER USER — Modifies user account information.
     ALTER USER username
     [WITH PASSWORD password]
     [CREATEDB | NOCREATEDB]
     [CREATEUSER | NOCREATEUSER]
     [IN GROUP groupname [, ...] ]
     [VALID UNTIL 'abstime']
    

Inputs

Refer to CREATE USER statement for a detailed description of each clause.

username

The Postgres account name of the user whose details are to be altered.

password

The new password to be used for this account.

groupname

The name of an access group into which this account is to be put.

abstime

The date (and, optionally, the time) at which this user's access is to be terminated.

Outputs

status

ALTER USER

Message returned if the alteration was successful.

ERROR: alterUser: user "username" does not exist

Error message returned if the user specified doesn't exist.

Description

ALTER USER is used to change the attributes of a user's PostgreSQL account. Please note that it is not possible to alter a user's "usesysid" via the alter user statement. Also, it is only possible for the PostgreSQL user or any user with read and modify permissions on "pg_shadow" to alter user passwords.

If any of the clauses of the alter user statement are omitted, the corresponding value in the "pg_shadow" table is left unchanged.

Notes

ALTER USER statement is a PostgreSQL language extension.

Refer to CREATE/DROP USER statements to create/remove an user account.

At the current release (6.3.2), the IN GROUP clause is parsed but has no effect. When it is fully implemented, it is intended to modify the pg_group relation.

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 authorisation 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;
    

Place a user in two groups

     ALTER USER miriam IN GROUP sales, payroll;
    

Compatibility

SQL92

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