Skip to content
Related Articles

Related Articles

Improve Article
PostgreSQL – ALTER ROLE
  • Last Updated : 10 Feb, 2021

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:

    My Personal Notes arrow_drop_up
  • Recommended Articles
    Page :