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.
Since the keywords ROLE
and USER
can now be used interchangeably in SQL statements for enhanced PostgreSQL compatibility, ALTER ROLE
is now an alias for ALTER USER
.
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 theadmin
role. - To alter other roles, the user must be a member of the
admin
role or have theCREATEROLE
role option set.
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} |
New in v21.2: Set default session variable values for a role. |
RESET {session variable} RESET ALL |
New in v21.2: Reset one session variable or all session variables to the default value. |
IN DATABASE database_name |
New in v21.2: 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.Note that, 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 <dbname> /SET database=<dbname> . |
ROLE ALL ... /USER ALL ... |
New in v21.2: Apply session variable settings to all roles. Exception: The root user is exempt from session variable settings. |
Role options
Role option | Description |
---|---|
CANCELQUERY /NOCANCELQUERY |
Allow or disallow the role to cancel queries and sessions of other roles. Without this role option, roles can only cancel their own queries and sessions. Even with this role option, non-admins 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 |
Allow or disallow the 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 the role to pause, resume, and cancel jobs. Non-admin roles cannot control jobs created by admins. By default, the role option is set to NOCONTROLJOB for all non-admin roles. |
CREATEDB /NOCREATEDB |
Allow or disallow the 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 the 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 users. |
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 the 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 |
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 |
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. |
VIEWACTIVITYREDACTED /NOVIEWACTIVITYREDACTED |
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 won't have access to the usage of statements diagnostics bundle, which can contain PII information, in the DB Console. 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:
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 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
:
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)