ALTER ROLE

ALTER ROLE alters the attributes of an existing role.¹

Syntax

{{< tabs >}}

{{< tab "Cloud" >}}

Cloud

{{% include-syntax file="examples/rbac-cloud/alter_roles" example="alter-role-syntax" %}}

Note: {{% include-example file="examples/rbac-cloud/alter_roles" example="alter-role-details" %}} {{< /tab >}} {{< tab "Self-Managed" >}}

Self-Managed

{{% include-syntax file="examples/rbac-sm/alter_roles" example="alter-role-syntax" %}}

Note: {{% include-example file="examples/rbac-sm/alter_roles" example="alter-role-details" %}} {{< /tab >}} {{< /tabs >}}

Restrictions

You may not specify redundant or conflicting sets of options. For example, Materialize will reject the statement ALTER ROLE ... INHERIT INHERIT.

Examples

Altering the attributes of a role

ALTER ROLE rj INHERIT;

SELECT name, inherit FROM mz_roles WHERE name = 'rj';

rj  true

Setting configuration parameters for a role

SHOW cluster;
quickstart

ALTER ROLE rj SET cluster TO rj_compute;

-- Role parameters only take effect for new sessions.
SHOW cluster;
quickstart

-- Start a new SQL session with the role 'rj'.
SHOW cluster;
rj_compute

-- In a new SQL session with a role that is not 'rj'.
SHOW cluster;
quickstart

Making a role a superuser (Self-Managed)

Unlike regular roles, superusers have unrestricted access to all objects in the system and can perform any action on them.

ALTER ROLE rj SUPERUSER;

To verify that the role has superuser privileges, you can query the pg_authid system catalog:

SELECT name, rolsuper FROM pg_authid WHERE rolname = 'rj';

rj  t

Removing the superuser attribute from a role (Self-Managed)

NOSUPERUSER will remove the superuser attribute from a role, preventing it from having unrestricted access to all objects in the system.

ALTER ROLE rj NOSUPERUSER;

SELECT name, rolsuper FROM pg_authid WHERE rolname = 'rj';

rj  f

Removing a role's password (Self-Managed)

{{< warning >}} Setting a NULL password removes the password. {{< /warning >}}

ALTER ROLE rj PASSWORD NULL;

Changing a role's password (Self-Managed)

ALTER ROLE rj PASSWORD 'new_password';

Privileges

The privileges required to execute this statement are:

{{% include-headless "/headless/sql-command-privileges/alter-role" %}}

Related pages

• CREATE ROLE
• DROP ROLE
• DROP USER
• GRANT ROLE
• REVOKE ROLE
• ALTER OWNER
• GRANT PRIVILEGE
• REVOKE PRIVILEGE