Skip to content

Latest commit

 

History

History
183 lines (137 loc) · 5.54 KB

alter-role.md

File metadata and controls

183 lines (137 loc) · 5.54 KB
title summary toc
ALTER ROLE
The ALTER ROLE statement can be used to add or change a role's password.
true

New in v20.1: The ALTER ROLE statement can be used to add, change, or remove a role's password and to change the login 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

New in v20.1: To alter other roles, the role must 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 password you want to create or add.
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.
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.
CREATEROLE/NOCREATEROLE Allow or disallow the role to create, alter, and drop other roles.

By default, the parameter is set to NOCREATEROLE for all non-admin and non-root roles.

Examples

Change password using a string literal

{% include copy-clipboard.html %}

> ALTER ROLE carl WITH PASSWORD 'ilov3beefjerky';
ALTER ROLE 1

Change password using an identifier

The following statement changes the password to ilov3beefjerky, as above:

{% include copy-clipboard.html %}

> ALTER ROLE carl WITH PASSWORD ilov3beefjerky;

This is equivalent to the example in the previous section because the password contains only lowercase characters.

In contrast, the following statement changes the password to thereisnotomorrow, even though the password in the syntax contains capitals, because identifiers are normalized automatically:

{% include copy-clipboard.html %}

> ALTER ROLE carl WITH PASSWORD ThereIsNoTomorrow;

To preserve case in a password specified using identifier syntax, use double quotes:

{% include copy-clipboard.html %}

> ALTER ROLE carl WITH PASSWORD "ThereIsNoTomorrow";

Set password validity

The following statement sets the date and time after which the password is not valid:

{% include copy-clipboard.html %}

> ALTER ROLE carl VALID UNTIL '2021-01-01';

Prevent a role from using password authentication

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

{% include copy-clipboard.html %}

> ALTER ROLE carl WITH PASSWORD NULL;

Change login privileges for a role

The following statement prevents the role from logging in with any client authentication method:

{% include copy-clipboard.html %}

> ALTER ROLE carl NOLOGIN;

{% include copy-clipboard.html %}

> SHOW ROLES;
  username |  options   | member_of
-----------+------------+------------
  admin    | CREATEROLE | {}
  carl     | NOLOGIN    | {}
  root     | CREATEROLE | {admin}
(3 rows)

The following statement allows the role to log in with one of the client authentication methods:

{% include copy-clipboard.html %}

> ALTER ROLE carl LOGIN;

{% include copy-clipboard.html %}

> SHOW ROLES;
  username |  options   | member_of
-----------+------------+------------
  admin    | CREATEROLE | {}
  carl     |            | {}
  root     | CREATEROLE | {admin}
(3 rows)

Allow the role to create other roles

{% include copy-clipboard.html %}

> SHOW ROLES;
  username |  options   | member_of
-----------+------------+------------
  admin    | CREATEROLE | {}
  carl     |            | {}
  root     | CREATEROLE | {admin}
(3 rows)

{% include copy-clipboard.html %}

> ALTER ROLE carl with CREATEROLE;

{% include copy-clipboard.html %}

> SHOW ROLES;
  username |  options   | member_of
-----------+------------+------------
  admin    | CREATEROLE | {}
  carl     | CREATEROLE | {}
  root     | CREATEROLE | {admin}
(3 rows)

See also