New in v20.1: The ALTER ROLE
statement can be used to add, change, or remove a role's password and to change the login privileges for a role.
Since the keywords ROLE
and USER
can now be used interchangeably in SQL statements for enhanced Postgres compatibility, ALTER ROLE
is now an alias for ALTER USER
.
Considerations
- Password creation and alteration is supported only in secure clusters.
Required privileges
New in v20.1: To alter other roles, the role must have the CREATEROLE
parameter set.
Synopsis
Parameters
Parameter | Description |
---|---|
name |
The name of the role whose password you want to create or add. |
password |
Let the role 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 role from using password authentication and to mandate certificate-based client authentication, set the password as NULL . |
VALID UNTIL |
The date and time (in the timestamp format) after which the password is not valid. |
LOGIN /NOLOGIN |
The LOGIN parameter allows a role to login with one of the client authentication methods. Setting the parameter to NOLOGIN prevents the role from logging in using any authentication method. |
CREATEROLE /NOCREATEROLE |
Allow or disallow the role to create, alter, and drop other roles. By default, the parameter is set to NOCREATEROLE for all non-admin and non-root roles. |
Examples
Change password using a string literal
> ALTER ROLE carl WITH PASSWORD 'ilov3beefjerky';
ALTER ROLE 1
Change password using an identifier
The following statement changes the password to ilov3beefjerky
, as above:
> ALTER ROLE 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 ROLE carl WITH PASSWORD ThereIsNoTomorrow;
To preserve case in a password specified using identifier syntax, use double quotes:
> ALTER ROLE carl WITH PASSWORD "ThereIsNoTomorrow";
Set password validity
The following statement sets the date and time after which the password is not valid:
> ALTER ROLE carl VALID UNTIL '2021-01-01';
Prevent a role from using password authentication
The following statement prevents the role from using password authentication and mandates certificate-based client authentication:
> ALTER ROLE carl WITH PASSWORD NULL;
Change login privileges for a role
The following statement prevents the role from logging in with any client authentication method:
> ALTER ROLE carl NOLOGIN;
> SHOW ROLES;
username | options | member_of
-----------+------------+------------
admin | CREATEROLE | {}
carl | NOLOGIN | {}
root | CREATEROLE | {admin}
(3 rows)
The following statement allows the role to log in with one of the client authentication methods:
> ALTER ROLE carl LOGIN;
> SHOW ROLES;
username | options | member_of
-----------+------------+------------
admin | CREATEROLE | {}
carl | | {}
root | CREATEROLE | {admin}
(3 rows)
Allow the role to create other roles
> SHOW ROLES;
username | options | member_of
-----------+------------+------------
admin | CREATEROLE | {}
carl | | {}
root | CREATEROLE | {admin}
(3 rows)
> ALTER ROLE carl with CREATEROLE;
> SHOW ROLES;
username | options | member_of
-----------+------------+------------
admin | CREATEROLE | {}
carl | CREATEROLE | {}
root | CREATEROLE | {admin}
(3 rows)