The ALTER USER statement can be used to add, change, or remove a user's password and to change the login privileges for a user.

Note:

New in v20.1: Since the keywords ROLE and USER can now be used interchangeably in SQL statements for enhanced Postgres compatibility, ALTER USER is now an alias for ALTER ROLE.

Considerations

  • Password creation and alteration is supported only in secure clusters.

Required privileges

New in v20.1: To alter other users, the user must have the CREATEROLE parameter set.

Synopsis

ALTER USER IF EXISTS name WITH PASSWORD password

Parameters

Parameter Description
name The name of the user whose password you want to create or add.
password Let the user authenticate their access to a secure cluster using this new password. Passwords should be entered as a string literal. For compatibility with PostgreSQL, a password can also be entered as an identifier.

To prevent a user from using password authentication and to mandate certificate-based client authentication, set the password as NULL.
VALID UNTIL New in v20.1: The date and time (in the timestamp format) after which the password is not valid.
LOGIN/NOLOGIN New in v20.1: The LOGIN parameter allows a user to login with one of the client authentication methods. Setting the parameter to NOLOGIN prevents the user from logging in using any authentication method.
CREATEROLE/NOCREATEROLE New in v20.1: Allow or disallow the user to create, alter, and drop other users.

By default, the parameter is set to NOCREATEROLE for all non-admin and non-root users.

Examples

Change password using a string literal

copy
icon/buttons/copy
> ALTER USER carl WITH PASSWORD 'ilov3beefjerky';
ALTER USER 1

Change password using an identifier

The following statement changes the password to ilov3beefjerky, as above:

copy
icon/buttons/copy
> ALTER USER carl WITH PASSWORD ilov3beefjerky;

This is equivalent to the example in the previous section because the password contains only lowercase characters.

In contrast, the following statement changes the password to thereisnotomorrow, even though the password in the syntax contains capitals, because identifiers are normalized automatically:

copy
icon/buttons/copy
> ALTER USER carl WITH PASSWORD ThereIsNoTomorrow;

To preserve case in a password specified using identifier syntax, use double quotes:

copy
icon/buttons/copy
> ALTER USER carl WITH PASSWORD "ThereIsNoTomorrow";

Set password validity

The following statement sets the date and time after which the password is not valid:

copy
icon/buttons/copy
> ALTER USER carl VALID UNTIL '2021-01-01';

Prevent a user from using password authentication

The following statement prevents the user from using password authentication and mandates certificate-based client authentication:

copy
icon/buttons/copy
> ALTER USER carl WITH PASSWORD NULL;

Change login privileges for a user

The following statement prevents the user from logging in with any client authentication method:

copy
icon/buttons/copy
> ALTER USER carl NOLOGIN;
copy
icon/buttons/copy
> SHOW USERS;
  username |  options   | member_of
-----------+------------+------------
  admin    | CREATEROLE | {}
  carl     | NOLOGIN    | {}
  root     | CREATEROLE | {admin}
(3 rows)

The following statement allows the user to log in with one of the client authentication methods:

copy
icon/buttons/copy
> ALTER USER carl LOGIN;
copy
icon/buttons/copy
> SHOW USERS;
  username |  options   | member_of
-----------+------------+------------
  admin    | CREATEROLE | {}
  carl     |            | {}
  root     | CREATEROLE | {admin}
(3 rows)

See also



Yes No