PostgreSQL ALTER ROLE (original) (raw)

Last Updated : 15 Jul, 2025

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.