ALTER ROLE

The ALTER ROLE statement changes an existing role: its attributes (LOGIN, CREATEDB, …), its password, or its name. ALTER USER is an alias.

Examples

Start with a login role:

SQL

CREATE ROLE doc_worker LOGIN PASSWORD 'worker_secret';

Add an attribute:

SQL

ALTER ROLE doc_worker CREATEDB;

Several attributes can be changed in one statement; the NO-prefixed form removes an attribute:

SQL

ALTER ROLE doc_worker NOCREATEDB NOLOGIN;

Rotate the password:

SQL

ALTER ROLE doc_worker PASSWORD 'rotated_secret';

Set a password expiry — after this timestamp, password authentication fails:

SQL

ALTER ROLE doc_worker VALID UNTIL '2099-12-31 00:00:00+00';

Rename the role:

SQL

ALTER ROLE doc_worker RENAME TO doc_worker_renamed;

Contradictory attributes are rejected:

SQL

ALTER ROLE doc_worker_renamed SUPERUSER NOSUPERUSER;

error db error: ERROR: conflicting or redundant options

Altering a role that does not exist is an error:

SQL

ALTER ROLE doc_missing LOGIN;

error db error: ERROR: Role with name doc_missing does not exist!

Changing role attributes requires the CREATEROLE attribute (or superuser); only a superuser can grant or remove SUPERUSER.
PASSWORD NULL removes the stored password, disabling password authentication for the role.
CONNECTION LIMIT is accepted and stored but is not currently enforced at connect time.