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)

For a list of the session variables that have been updated from default values, see SHOW DEFAULT SESSION VARIABLES FOR ROLE.

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)

For a list of the session variables that have been updated from default values, see SHOW DEFAULT SESSION VARIABLES FOR ROLE.

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)

For a list of the session variables that have been updated from default values, see SHOW DEFAULT SESSION VARIABLES FOR ROLE.

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

For a list of the session variables that have been updated from default values, see SHOW DEFAULT SESSION VARIABLES FOR ROLE.

Set the SUBJECT role option for certificate based authentication

You can associate an X.509 certificate's Subject with a role as shown below. Note that the Subject fields in the certificate have to be an exact match with what you pass in via the SQL statement. By exact match, we mean that the order of attributes passed in via the SQL statement must match the order of attributes in the certificate.

ALTER ROLE maxroach WITH SUBJECT 'CN=myName2,OU=myOrgUnit2,O=myOrg2,L=myLocality2,ST=myState2,C=myCountry2' LOGIN;

If you manage your own Certificate Authority (CA) infrastructure, CockroachDB supports mapping between the Subject field of your X.509 certificates and SQL roles. For more information, see Certificate-based authentication using multiple values from the X.509 Subject field.

See also

• DROP ROLE
• SHOW ROLES
• GRANT
• SHOW GRANTS
• cockroach cert
• SQL Statements
• Authorization Best Practices
• SHOW DEFAULT SESSION VARIABLES FOR ROLE

Was this helpful?