PostgreSQL - ALTER ROLE

In PostgreSQL, the ALTER ROLE statement is a versatile tool used to manage and modify roles. It allows administrators to change a role's name, attributes, and session defaults for configuration variables.

Let us take a look at the ALTER ROLE statement, its syntax, and various functions available with examples to help you manage PostgreSQL roles effectively.

Syntax

The basic syntax for the ALTER ROLE statement is:

ALTER ROLE role_name [WITH] option;

Functions Available with ALTER ROLE Statement

The ALTER ROLE statement includes several options to manage role attributes:

• SUPERUSER | NOSUPERUSER: Determines if the role is a superuser.
• VALID UNTIL 'timestamp': Specifies the expiry date and time of a role’s password.
• CREATEDB | NOCREATEDB: Grants or revokes permissions for creating new databases.
• REPLICATION | NOREPLICATION: Indicates if a role is a replication role.
• CREATEROLE | NOCREATEROLE: Grants or revokes permissions for creating or modifying roles.
• PASSWORD 'password' | PASSWORD NULL: Changes the role’s password.
• INHERIT | NOINHERIT: Determines if the role inherits privileges from parent roles.
• BYPASSRLS | NOBYPASSRLS: Checks if a role can bypass row-level security (RLS) policies.
• LOGIN | NOLOGIN: Allows or disallows the role to log in.
• CONNECTION LIMIT limit: Sets the number of concurrent connections a role can make (-1 means unlimited connections).

Rules for Using ALTER ROLE Statement

When using the ALTER ROLE statement, certain rules must be followed:

1. Superusers can modify the attributes for any role.
2. Non-superusers and no-replication roles can be modified if a role has the CREATE ROLE attribute.
3. Ordinary roles can only change their passwords.

PostgreSQL ALTER ROLE Statement Examples

Let us take a look at some of the examples of ALTER ROLE in PostgreSQL to better understand the concept.

Example 1: Creating and Modifying a Role

First, log in to PostgreSQL using the postgres role. Then, create a new role called 'Ravi' with the following statement:

CREATE ROLE ravi LOGIN PASSWORD 'geeks12345';

Now, modify the role 'Ravi' to be a superuser:

ALTER ROLE ravi SUPERUSER;

To view the role attributes, use the following command:

\du ravi

Output:

Example 2: Setting a Password Expiry Date

To set an expiry date for the role 'Ravi':

ALTER ROLE ravi VALID UNTIL '2024-12-31';

Example 3: Granting Database Creation Permissions

To grant the role 'Ravi' permission to create databases:

ALTER ROLE ravi CREATEDB;

Important Points About PostgreSQL ALTER ROLE Statement

• You can combine multiple options in a single ALTER ROLE statement, which helps in consolidating role modifications into one command for efficiency.
• You can use ALTER ROLE to set default values for configuration variables that apply every time the role logs in.
• The INHERIT attribute allows a role to inherit privileges of roles it is a member of. Without INHERIT, the role must explicitly set SET ROLE to use the privileges.
• The VALID UNTIL clause with the ALTER ROLE allows you to set an expiration date for the role's password.