Practical PostgreSQL

Alter User

Modifies user properties and permissions.

Synopsis

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

Parameters & Keywords

username

The name of the PostgreSQL database user to be modified.

password

An optional new password to assign to the modified PostgreSQL user.

CREATEDB | NOCREATEDB

The privilege to create new databases. Use CREATEDB, to give the user permission to create databases. Use NOCREATEDB to explicitly deny that permission (which is the default).

CREATEUSER | NOCREATEUSER

The superuser privilege. The use of CREATEUSER allows access to both the CREATE USER and DROP USER commands, as well as makes the user a superuser (with universal rights across all databases). NOCREATEUSER is the default.

Specifying that a PostgreSQL user is able to create other users also automatically classifies the user as a superuser in the database; this can be a security risk if unintentional. A superuser can override all other access restrictions.

abstime

The timestamp that defines when a user’s password expires. When the date and time defined by abstime is reached, the user’s defined password will become invalid. If unset, the password never expires.

Results

ALTER USER

The message returned when the ALTER USER command is successful.

ERROR: ALTER USER: user "username" does not exist

The error returned if username does not exist in the pg_shadow users table.

Description

Use the ALTER USER to change the attributes and permissions ...