Open In App

PostgreSQL – ALTER ROLE

Improve
Improve
Like Article
Like
Save
Share
Report

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:


    Last Updated : 10 Feb, 2021
    Like Article
    Save Article
    Previous
    Next
    Share your thoughts in the comments
    Similar Reads