Open In App
Related Articles


Improve Article
Save Article
Like Article

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.


    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


    Whether you're preparing for your first job interview or aiming to upskill in this ever-evolving tech landscape, GeeksforGeeks Courses are your key to success. We provide top-quality content at affordable prices, all geared towards accelerating your growth in a time-bound manner. Join the millions we've already empowered, and we're here to do the same for you. Don't miss out - check it out now!

    Last Updated : 10 Feb, 2021
    Like Article
    Save Article
    Similar Reads