ALTER ROLE

On this page

Use the ALTER ROLE statement to add, change, or remove a role's password, change the role options for a role, and set default session variable values for a role.

You can use the keywords ROLE and USER interchangeably. ALTER USER is an alias for ALTER ROLE.

Considerations

• Password creation and alteration is supported only in secure clusters.

Required privileges

• To alter an admin role, the user must be a member of the admin role.
• To alter other roles, the user must be a member of the admin role or have the CREATEROLE role option.

Synopsis

Parameters

Role options

Examples

Allow a role to log in to the database using a password

The following example allows a role to log in to the database with a password:

root@:26257/defaultdb> ALTER ROLE carl WITH LOGIN PASSWORD 'An0ther$tr0nGpassW0rD' VALID UNTIL '2021-10-10';

Prevent a role from using password authentication

The following statement prevents the user from using password authentication and mandates certificate-based client authentication:

root@:26257/defaultdb> ALTER ROLE carl WITH PASSWORD NULL;

Allow a role to create other roles and manage authentication methods for the new roles

The following example allows the role to create other roles and manage authentication methods for them:

root@:26257/defaultdb> ALTER ROLE carl WITH CREATEROLE CREATELOGIN;

Allow a role to create and rename databases

The following example allows the role to create or rename databases:

root@:26257/defaultdb> ALTER ROLE carl WITH CREATEDB;

Allow a role to pause, resume, and cancel non-admin jobs

The following example allows the role to pause, resume, and cancel jobs:

root@:26257/defaultdb> ALTER ROLE carl WITH CONTROLJOB;

Allow a role to see and cancel non-admin queries and sessions

The following example allows the role to cancel queries and sessions for other non-admin roles:

root@:26257/defaultdb> ALTER ROLE carl WITH CANCELQUERY VIEWACTIVITY;

Allow a role to control changefeeds

The following example allows the role to run CREATE CHANGEFEED:

root@:26257/defaultdb> ALTER ROLE carl WITH CONTROLCHANGEFEED;

Allow a role to modify cluster settings

The following example allows the role to modify cluster settings:

root@:26257/defaultdb> ALTER ROLE carl WITH MODIFYCLUSTERSETTING;

Set default session variable values for a role

In the following example, the root user creates a role named max, and sets the default value of the timezone session variable for the max role.

root@:26257/defaultdb> CREATE ROLE max WITH LOGIN;

root@:26257/defaultdb> ALTER ROLE max SET timezone = 'America/New_York';

This statement does not affect the default timezone value for any role other than max:

root@:26257/defaultdb> SHOW timezone;

  timezone
------------
  UTC
(1 row)

To see the default timezone value for the max role, run the SHOW statement as a member of the max role:

max@:26257/defaultdb> SHOW timezone;

      timezone
--------------------
  America/New_York
(1 row)

Set default session variable values for a role in a specific database

In the following example, the root user creates a role named max and a database named movr, and sets the default value of the statement_timeout session variable for the max role in the movr database.

root@:26257/defaultdb> CREATE DATABASE movr;

root@:26257/defaultdb> CREATE ROLE max WITH LOGIN;

root@:26257/defaultdb> ALTER ROLE max IN DATABASE movr SET statement_timeout = '10s';

This statement does not affect the default statement_timeout value for any role other than max, or in any database other than movr.

root@:26257/defaultdb> SHOW statement_timeout;

  statement_timeout
---------------------
  0
(1 row)

To see the new default statement_timeout value for the max role, run the SHOW statement as a member of the max role that has connected to the cluster, with the database movr specified in the connection string.

cockroach sql --url 'postgresql://max@localhost:26257/movr?sslmode=disable'

max@:26257/movr> SHOW statement_timeout;

  statement_timeout
---------------------
  10000
(1 row)

Set default session variable values for a specific database

In the following example, the root user creates a database named movr, and sets the default value of the timezone session variable for all roles in that database.

root@:26257/defaultdb> CREATE DATABASE movr;

root@:26257/defaultdb> ALTER ROLE ALL IN DATABASE movr SET timezone = 'America/New_York';

This statement does not affect the default timezone value for any database other than movr:

root@:26257/defaultdb> SHOW timezone;

  timezone
------------
  UTC
(1 row)

To see the default timezone value for the max role, run the SHOW statement as a member of the max role:

root@:26257/movr> SHOW timezone;

      timezone
--------------------
  America/New_York
(1 row)

Set default session variable values for all users

To set a default value for all users for any session variable that applies during login, issue a statement like the following:

ALTER ROLE ALL SET sql.spatial.experimental_box2d_comparison_operators.enabled = "on";

ALTER ROLE

See also

• DROP ROLE
• SHOW ROLES
• GRANT
• SHOW GRANTS
• cockroach cert
• SQL Statements
• Authorization Best Practices

Was this helpful?