PostgreSQL ALTER ROLE Statement

Summary: In this tutorial, you’ll learn how to use the PostgreSQL ALTER ROLE statement to modify existing roles in the database.

The PostgreSQL ALTER ROLE statement allows you to:

• Change role attributes.
• Rename a role.
• Modify the configuration parameters.

Changing Role Attributes #

Granting or Revoking SUPERUSER Privileges #

The SUPERUSER privilege allows a role to have full privileges to the database.

ALTER ROLE role_name SUPERUSER;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To revoke:

ALTER ROLE role_name NOSUPERUSER;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Granting or Revoking CREATEDB Privileges #

The CREATEDB option allows a role to create new databases:

ALTER ROLE role_name CREATEDB;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To revoke:

ALTER ROLE role_name NOCREATEDB;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Granting or Revoking CREATEROLE Privileges #

A role with CREATEROLE can create and manage other roles:

ALTER ROLE role_name CREATEROLE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To revoke:

ALTER ROLE role_name NOCREATEROLE;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Enabling or Disabling Role Inheritance #

A role with INHERIT option can inherit privileges from other roles:

ALTER ROLE role_name INHERIT;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To disable inheritance:

ALTER ROLE role_name NOINHERIT;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Enabling or Disabling Role Login #

A role with a LOGIN privilege can log in to the PostgreSQL:

ALTER ROLE role_name LOGIN;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To disable login:

ALTER ROLE role_name NOLOGIN;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Granting or Revoking REPLICATION Privileges #

A role with REPLICATION can initiate streaming replication:

ALTER ROLE role_name REPLICATION;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To revoke:

ALTER ROLE role_name NOREPLICATION;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Granting or Revoking Row-Level Security Bypass #

Use BYPASSRLS to allow a role to bypass row-level security (RLS) policies:

ALTER ROLE role_name BYPASSRLS;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To revoke:

ALTER ROLE role_name NOBYPASSRLS;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Setting Connection Limits #

If you want to set the role’s concurrent connections, you can use the CONNECTION LIMIT option:

ALTER ROLE role_name CONNECTION LIMIT 10;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To remove the limit, set the value to -1:

ALTER ROLE role_name CONNECTION LIMIT -1;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Changing the Role Password #

Setting a new password

ALTER ROLE role_name PASSWORD 'newpassword';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To disable password-based authentication, you can set the PASSWORD to NULL:

ALTER ROLE role_name PASSWORD NULL;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Note that the role might still be able to log in using other authentication methods such as trust, peer, or certificate authentication.

Setting Role Expiration #

A role can be set to expire after a certain timestamp.

ALTER ROLE role_name VALID UNTIL '2050-12-31';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

To make it never expire:

ALTER ROLE role_name VALID UNTIL 'infinity';Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Renaming a Role #

To change the name of an existing role, you use the RENAME TO clause:

ALTER ROLE oldname RENAME TO newname;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Setting Configuration Parameters #

You can configure per-role settings using SET clause.

Setting a role-specific parameter:

ALTER ROLE role_name SET search_path TO schema1, schema2;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Resetting a rarameter to default:

ALTER ROLE role_name RESET search_path;Code language: PostgreSQL SQL dialect and PL/pgSQL (pgsql)

Summary #

• Use the PostgreSQL ALTER ROLE statement to modify roles.