Edit this page

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:

Query

CREATE ROLE doc_worker LOGIN PASSWORD 'worker_secret';

Add an attribute:

Query

ALTER ROLE doc_worker CREATEDB;

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

Query

ALTER ROLE doc_worker NOCREATEDB NOLOGIN;

Rotate the password:

Query

ALTER ROLE doc_worker PASSWORD 'rotated_secret';

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

Query

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

Rename the role:

Query

ALTER ROLE doc_worker RENAME TO doc_worker_renamed;

Contradictory attributes are rejected:

Query

ALTER ROLE doc_worker_renamed SUPERUSER NOSUPERUSER;

Result

error db error: ERROR: conflicting or redundant options

Altering a role that does not exist is an error:

Query

ALTER ROLE doc_missing LOGIN;

Result

error db error: ERROR: role "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.