ALTER ROLE

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 management

Password creation and alteration is supported only in secure clusters.

Session variable precedence

When a session starts, CockroachDB determines the initial value of each session variable by evaluating the settings in the following order (items earlier in the list take precedence over later items):

Connection string parameters: A value supplied as a query parameter in the connection URL (for example, .../movr?sslmode=disable&timezone=UTC ).
Per-role, per-database defaults: A value set by ALTER ROLE {role_name} IN DATABASE {db_name} SET {var}={value}.
Per-role, all-database defaults: A value set by ALTER ROLE {role_name} SET {var}={value}.
All-role, per-database defaults: A value set by ALTER ROLE ALL IN DATABASE {db_name} SET {var}={value} or equivalently by ALTER DATABASE {db_name} SET {var}={value}.
Cluster-wide defaults for all roles and all databases: A value set by ALTER ROLE ALL SET {var}={value}.

If a session variable is not modified using any of the preceding methods, the built-in system default value is used. Note that the root user is exempt from settings 3–5. The root user is only affected by values specified in the connection string. You can also set session variables for the duration of a single transaction by using SET LOCAL {var}={value}.

Changes to defaults using the preceding methods only apply to future sessions. This is because session variable resolution happens at session start time. To change a default value in an existing open session, set the variable explicitly with SET.

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.
New in v25.4: For per-database defaults, the owner of a database can execute ALTER ROLE ALL IN DATABASE... {SET|RESET} for that database (in addition to users who meet the general requirements above).

Synopsis

Parameters

Parameter	Description
`role_name`	The name of the role to alter.
`WITH role_option`	Apply a role option to the role.
`SET {session variable}`	Set default session variable values for a role.
`RESET {session variable}` `RESET ALL`	Reset one session variable or all session variables to the default value.
`IN DATABASE database_name`	Specify a database for which to apply session variable defaults. When `IN DATABASE` is not specified, the default session variable values apply for a role in all databases. In order for a session to initialize session variable values to database defaults, the database must be specified as a connection parameter. Database default values will not appear if the database is set after connection with `USE` /`SET database=`.
`ROLE ALL ...`/`USER ALL ...`	Apply session variable settings to all roles. Exception: The `root` user is exempt from session variable settings. This is a failsafe; if you make a mistake with a session variable setting, you can still log in as `root` and be unaffected.

Role options

Role option	Description
`BYPASSRLS`/`NOBYPASSRLS`	: Allow or disallow a role to bypass row-level security (RLS) policies on a table. This option controls the access from an RLS perspective only; the user also needs sufficient `GRANT` privileges to read or write to the table.
`CANCELQUERY`/`NOCANCELQUERY`	Deprecated in v22.2: Use the `CANCELQUERY`system privilege. Allow or disallow a role to cancel queries and sessions of other roles. Without this role option, roles can only cancel their own queries and sessions. Even with the `CANCELQUERY` role option, non-`admin` roles cannot cancel `admin` queries or sessions. This option should usually be combined with `VIEWACTIVITY` so that the role can view other roles’ query and session information. By default, the role option is set to `NOCANCELQUERY` for all non-`admin` roles.
`CONTROLCHANGEFEED`/`NOCONTROLCHANGEFEED`	Deprecated in v23.1: Use the `CHANGEFEED`privilege. Allow or disallow a role to run `CREATE CHANGEFEED` on tables they have `SELECT` privileges on. By default, the role option is set to `NOCONTROLCHANGEFEED` for all non-`admin` roles.
`CONTROLJOB`/`NOCONTROLJOB`	Allow or disallow a role to pause, resume, and cancel jobs. Non-`admin` roles cannot control jobs created by `admin` roles. By default, the role option is set to `NOCONTROLJOB` for all non-`admin` roles.
`CREATEDB`/`NOCREATEDB`	Allow or disallow a role to create or rename a database. The role is assigned as the owner of the database. By default, the role option is set to `NOCREATEDB` for all non-`admin` roles.
`CREATELOGIN`/`NOCREATELOGIN`	Allow or disallow a role to manage authentication using the `WITH PASSWORD`, `VALID UNTIL`, and `LOGIN/NOLOGIN` role options. By default, the role option is set to `NOCREATELOGIN` for all non-`admin` roles.
`CREATEROLE`/`NOCREATEROLE`	Allow or disallow the new role to create, alter, and drop other non-`admin` roles. By default, the role option is set to `NOCREATEROLE` for all non-`admin` roles.
`LOGIN`/`NOLOGIN`	Allow or disallow a role to log in with one of the client authentication methods. Setting the role option to `NOLOGIN` prevents the role from logging in using any authentication method.
`MODIFYCLUSTERSETTING`/`NOMODIFYCLUSTERSETTING`	Allow or disallow a role to modify the cluster settings with the `sql.defaults` prefix. By default, the role option is set to `NOMODIFYCLUSTERSETTING` for all non-`admin` roles.
`PASSWORD password`/`PASSWORD NULL`	The credential the role uses to authenticate their access to a secure cluster. A password should be entered as a string literal. For compatibility with PostgreSQL, a password can also be entered as an identifier. To prevent a role from using password authentication and to mandate certificate-based client authentication, set the password as `NULL`.
`SQLLOGIN`/`NOSQLLOGIN`	Deprecated in v22.2: Use the `NOSQLLOGIN`system privilege. Allow or disallow a role to log in using the SQL CLI with one of the client authentication methods. The role option to `NOSQLLOGIN` prevents the role from logging in using the SQL CLI with any authentication method while retaining the ability to log in to DB Console. It is possible to have both `NOSQLLOGIN` and `LOGIN` set for a role and `NOSQLLOGIN` takes precedence on restrictions. Without any role options all login behavior is permitted.
`VALID UNTIL`	The date and time (in the `timestamp` format) after which the password is not valid.
`VIEWACTIVITY`/`NOVIEWACTIVITY`	Deprecated in v22.2: Use the `VIEWACTIVITY`system privilege. Allow or disallow a role to see other roles’ queries and sessions using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the Statements and Transactions pages in the DB Console. `VIEWACTIVITY` also permits visibility of node hostnames and IP addresses in the DB Console. With `NOVIEWACTIVITY`, the `SHOW` commands show only the role’s own data, and DB Console pages redact node hostnames and IP addresses. By default, the role option is set to `NOVIEWACTIVITY` for all non-`admin` roles.
`VIEWCLUSTERSETTING` / `NOVIEWCLUSTERSETTING`	Deprecated in v22.2: Use the `VIEWCLUSTERSETTING`system privilege. Allow or disallow a role to view the cluster settings with `SHOW CLUSTER SETTING` or to access the Cluster Settings page in the DB Console. By default, the role option is set to `NOVIEWCLUSTERSETTING` for all non-`admin` roles.
`VIEWACTIVITYREDACTED`/`NOVIEWACTIVITYREDACTED`	Deprecated in v22.2: Use the `VIEWACTIVITYREDACTED`system privilege. Allow or disallow a role to see other roles’ queries and sessions using `SHOW STATEMENTS`, `SHOW SESSIONS`, and the Statements and Transactions pages in the DB Console. With `VIEWACTIVITYREDACTED`, a user will not have access to the usage of statements diagnostics bundle (which can contain PII information) in the DB Console, and will not be able to list queries containing constants for other users when using the `listSessions` endpoint through the Cluster API. It is possible to have both `VIEWACTIVITY` and `VIEWACTIVITYREDACTED`, and `VIEWACTIVITYREDACTED` takes precedence on restrictions. If the user has `VIEWACTIVITY` but doesn’t have `VIEWACTIVITYREDACTED`, they will be able to see DB Console pages and have access to the statements diagnostics bundle. By default, the role option is set to `NOVIEWACTIVITYREDACTED` for all non-`admin` roles.

Examples

The following statements are run by the root user that is a member of the admin role and has ALL privileges.

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:

CREATE ROLE carl;

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:

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:

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:

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:

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:

ALTER ROLE carl WITH CANCELQUERY VIEWACTIVITY;

Allow a role to control changefeeds

The following example allows the role to run CREATE CHANGEFEED:

ALTER ROLE carl WITH CONTROLCHANGEFEED;

Allow a role to modify cluster settings

The following example allows the role to modify cluster settings:

ALTER ROLE carl WITH MODIFYCLUSTERSETTING;

Allow a role to bypass row-level security (RLS)

To allow a role to bypass row-level security, execute the following statement to grant the BYPASSRLS privilege:

ALTER ROLE carl WITH BYPASSRLS;

To disable the role’s ability to bypass RLS, execute the following statement:

ALTER ROLE carl WITH NOBYPASSRLS;

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.

CREATE ROLE max WITH LOGIN;

ALTER ROLE max SET timezone = 'America/New_York';

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

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:

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.

CREATE DATABASE IF NOT EXISTS movr;

CREATE ROLE IF NOT EXISTS max WITH LOGIN;

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.

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'

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. New in v25.4: The owner of a database can also execute ALTER ROLE ALL IN DATABASE... {SET|RESET} for that database.

CREATE DATABASE IF NOT EXISTS movr;

ALTER ROLE ALL IN DATABASE movr SET timezone = 'America/New_York';

This statement is identical to ALTER DATABASE movr SET timezone = 'America/New_York';.

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

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:

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

The root user is exempt from session variable settings changes. This is a failsafe; if you make a mistake with a session variable setting, you can still log in as root and be unaffected.

Use ALTER ROLE ALL SET {sessionvar} = {val} instead of the sql.defaults.* cluster settings. This allows you to set a default value for all users for any session variable that applies during login, making the sql.defaults.* cluster settings redundant.

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 max 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.

Statements

Syntax

Data Types

Spatial Data

Constraints

Considerations

Password management

Session variable precedence

Required privileges

Synopsis

Parameters

Role options

Examples

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

Prevent a role from using password authentication

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

Allow a role to create and rename databases

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

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

Allow a role to control changefeeds

Allow a role to modify cluster settings

Allow a role to bypass row-level security (RLS)

Set default session variable values for a role

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

Set default session variable values for a specific database

Set default session variable values for all users

Set the `SUBJECT` role option for certificate based authentication

See also

​Considerations

​Password management

​Session variable precedence

​Required privileges

​Synopsis

​Parameters

​Role options

​Examples

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

​Prevent a role from using password authentication

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

​Allow a role to create and rename databases

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

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

​Allow a role to control changefeeds

​Allow a role to modify cluster settings

​Allow a role to bypass row-level security (RLS)

​Set default session variable values for a role

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

​Set default session variable values for a specific database

​Set default session variable values for all users

​Set the SUBJECT role option for certificate based authentication

​See also

Considerations

Password management

Session variable precedence

Required privileges

Synopsis

Parameters

Role options

Examples

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

Prevent a role from using password authentication

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

Allow a role to create and rename databases

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

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

Allow a role to control changefeeds

Allow a role to modify cluster settings

Allow a role to bypass row-level security (RLS)

Set default session variable values for a role

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

Set default session variable values for a specific database

Set default session variable values for all users

Set the `SUBJECT` role option for certificate based authentication

See also