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:
