PostgreSQL 8.0.0rc2 Documentation
Prev | Fast Backward |  | Fast Forward | Next

# ALTER USER

## Name

ALTER USER -- change a database user account

## Synopsis

    ALTER USER name [ [ WITH ] option [ ... ] ]

    where option can be:

        [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
        | CREATEDB | NOCREATEDB
        | CREATEUSER | NOCREATEUSER
        | VALID UNTIL 'abstime'

    ALTER USER name RENAME TO newname

    ALTER USER name SET parameter { TO | = } { value | DEFAULT }
    ALTER USER name RESET parameter

## 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 these privileges and the password expiration with this command. Ordinary users can only change their own password.

The second variant changes the name of the user. Only a database superuser can rename user accounts. The session user cannot be renamed. (Connect as a different user if you need to do that.) Because `MD5`-encrypted passwords use the user name as cryptographic salt, renaming a user clears their `MD5` password.

The third and the fourth 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` command line. Ordinary users can change their own session defaults. Superusers can change anyone's session defaults.

## Parameters

name

  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 for more information about this choice.)

`CREATEDB`
`NOCREATEDB`

  These clauses define a user's ability to create databases. If `CREATEDB` is specified, the user 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. To set the password never to expire, use `'infinity'`.

newname

  The new name of the user.

parameter
value

  Set this user's session default for the specified configuration parameter 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 and Section 16.4 for more information about allowed parameter names and values.

## Notes

Use CREATE USER to add new users, and DROP USER to remove a user.

`ALTER USER` cannot change a user's group memberships. Use ALTER GROUP to do that.

Using ALTER DATABASE, it is also possible to tie a session default to a specific database rather than a user.

## 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 2005 using the time zone which is one hour ahead of UTC:

    ALTER USER chris VALID UNTIL 'May 4 12:00:00 2005 +1';

Make a user valid forever:

    ALTER USER fred VALID UNTIL 'infinity';

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, DROP USER, SET

Prev | Home | Next
ALTER TYPE | Up | ANALYZE