Skip to content

Latest commit

 

History

History
253 lines (184 loc) · 8.97 KB

alter-role.md

File metadata and controls

253 lines (184 loc) · 8.97 KB
Raw
title summary toc
ALTER ROLE
The ALTER ROLE statement can be used to add or change a role's password.
true

The ALTER ROLE statement can be used to add, change, or remove a role's password and to change the privileges for a role.

{{site.data.alerts.callout_info}} Since the keywords ROLE and USER can now be used interchangeably in SQL statements for enhanced Postgres compatibility, ALTER ROLE is now an alias for ALTER USER. {{site.data.alerts.end}}

Considerations

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

Required privileges

To alter other roles, the role must be a member of the admin role or have the CREATEROLE parameter set.

Synopsis

{% include {{ page.version.version }}/sql/diagrams/alter_role.html %}

Parameters

<style> table td:first-child { min-width: 225px; } </style>
Parameter Description
name The name of the role whose role options you want to alter.
CREATELOGIN/NOCREATELOGIN Allow or disallow the role to manage authentication using the WITH PASSWORD, VALID UNTIL, and LOGIN/NOLOGIN parameters.

By default, the parameter is set to NOCREATELOGIN for all non-admin roles.
LOGIN/NOLOGIN The LOGIN parameter allows a role to login with one of the client authentication methods. Setting the parameter to NOLOGIN prevents the role from logging in using any authentication method.
password Let the role authenticate their access to a secure cluster using this new password. Passwords 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.
VALID UNTIL The date and time (in the timestamp format) after which the password is not valid.
CREATEROLE/NOCREATEROLE Allow or disallow the role to create, alter, and drop other non-admin roles.

By default, the parameter is set to NOCREATEROLE 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 parameter is set to NOCREATEDB 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 parameter is set to NOCONTROLJOB for all non-admin roles.
CANCELQUERY/NOCANCELQUERY Allow or disallow the role to cancel queries and sessions of other roles. Without this privilege, roles can only cancel their own queries and sessions. Even with this privilege, 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 parameter is set to NOCANCELQUERY for all non-admin roles.
VIEWACTIVITY/NOVIEWACTIVITY Allow or disallow a role to see other roles' queries and sessions using SHOW QUERIES, SHOW SESSIONS, and the Statements and Transactions pages in the Admin UI. Without this privilege, the SHOW commands only show the role's own data and the Admin UI pages are unavailable.

By default, the parameter is set to NOVIEWACTIVITY 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 parameter is set to NOCONTROLCHANGEFEED for all non-admin roles.
MODIFYCLUSTERSETTING/NOMODIFYCLUSTERSETTING Allow or disallow the role to to modify the cluster settings with the sql.defaults prefix.

By default, the parameter is set to NOMODIFYCLUSTERSETTING for all non-admin roles.

Examples

{{site.data.alerts.callout_info}} The following statements are run by the root user that is a member of the admin role and has ALL privileges. {{site.data.alerts.end}}

Allow a role to log in to the database 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:

{% include copy-clipboard.html %}

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

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

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

Allow a role to only manage authentication for other roles

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

Allow a role to create and rename databases

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

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

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

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

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

Allow a role to control changefeeds

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

Allow a role to modify cluster settings

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

See also