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.
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
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
> ALTER USER carl WITH PASSWORD 'ilov3beefjerky';
ALTER USER 1
Change password using an identifier
The following statement changes the password to ilov3beefjerky
, as above:
> 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:
> ALTER USER carl WITH PASSWORD ThereIsNoTomorrow;
To preserve case in a password specified using identifier syntax, use double quotes:
> ALTER USER carl WITH PASSWORD "ThereIsNoTomorrow";
Set password validity
The following statement sets the date and time after which the password is not valid:
> 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:
> 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:
> ALTER USER carl NOLOGIN;
> 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:
> ALTER USER carl LOGIN;
> SHOW USERS;
username | options | member_of
-----------+------------+------------
admin | CREATEROLE | {}
carl | | {}
root | CREATEROLE | {admin}
(3 rows)