PostgreSQL – ALTER ROLE

In PostgreSQL, the ALTER ROLE statement is used to manage and modify roles. It can be used to modify the name of a role, the attributes of a role, and alter a role’s session default for a configuration variable.

Syntax: ALTER ROLE role_name [WITH] option;

The following functions are available with ALTER ROLE statement:

• SUPERUSER | NOSUPERUSER – It is used to determine if the role is a superuser.
• VALID UNTIL ‘timestamp’ – It is used to specify the expiry date and time of a role’s password.

CREATEDB | NOCREATEDB– It is used to provide permissions to a role for creating new databases.

REPLICATION | NOREPLICATION – It is used to check if a role is a replication role.

CREATEROLE | NOCREATEROLE –It is used to provide permissions to a role for creating or modifying roles.

PASSWORD ‘password’ | PASSWORD NULL – It is used to change a role’s password.

INHERIT | NOINHERIT – It is used to determine if the inherited role has all the inherited privileges of the parent role.

BYPASSRLS | NOBYPASSRLS – It is used to check if a role can bypass a row-level security (RLS) policy.

LOGIN | NOLOGIN – As the name suggests itself, it is used to allow the role to log in.

CONNECTION LIMIT limit – It is used to set the number of concurrent connections that a role can make. Here -1 means the role can create an unlimited number of rows.

The following are the set of rules that must be followed while using the ALTER ROLE statement:

• Superusers can modify the attributes for any role.
• Only non-superusers and no-replication roles can be modified is a role has CREATE ROLE attribute to it.
• Ordinal roles can only change their passwords.

Example:

First, log in to the PostgreSQL using the Postgres role.

Now, create a new role called ‘Ravi’ using the CREATE ROLE statement:

create role ravi login password 'geeks12345';

Now modify the role of ravi to be a superuser using the below statement:

alter role ravi superuser;

To view the role use the following command:

\du ravi

Output: