ALTER ROLE

Changes a database role (user or group).

Synopsis

  1. ALTER ROLE <name> RENAME TO <newname>
  3. ALTER ROLE <name> RESOURCE QUEUE {<queue_name> | NONE}
  5. ALTER ROLE <name> [ [WITH] <option> [ ... ] ]

where <option> can be:

  1.       SUPERUSER | NOSUPERUSER
  2.     | CREATEDB | NOCREATEDB
  3.     | CREATEROLE | NOCREATEROLE
  4.     | CREATEEXTTABLE | NOCREATEEXTTABLE
  5.     [ ( <attribute>='<value>'[, ...] ) ]
  6.            where attribute and value are:
  7.            type='readable'|'writable'
  8.            protocol='gpfdist'|'http'
  10.     | INHERIT | NOINHERIT
  11.     | LOGIN | NOLOGIN
  12.     | CONNECTION LIMIT <connlimit>
  13.     | [ENCRYPTED | UNENCRYPTED] PASSWORD '<password>'
  14.     | VALID UNTIL '<timestamp>'
  15.     | [ DENY <deny_point> ]
  16.     | [ DENY BETWEEN <deny_point> AND <deny_point>]
  17.     | [ DROP DENY FOR <deny_point> ]

Description

ALTER ROLE changes the attributes of a HAWQ role. There are several variants of this command:

  • RENAME — Changes the name of the role. Database superusers can rename any role. Roles having CREATEROLE privilege can rename non-superuser roles. The current session user cannot be renamed (connect as a different user to rename a role). Because MD5-encrypted passwords use the role name as cryptographic salt, renaming a role clears its password if the password is MD5-encrypted.
  • RESOURCE QUEUE — Assigns the role to a workload management resource queue. The role would then be subject to the limits assigned to the resource queue when issuing queries. Specify NONE to assign the role to the default resource queue. A role can only belong to one resource queue. For a role without LOGIN privilege, resource queues have no effect. See CREATE RESOURCE QUEUE for more information.
  • WITH <option> — Changes many of the role attributes that can be specified in CREATE ROLE. Attributes not mentioned in the command retain their previous settings. Database superusers can change any of these settings for any role. Roles having CREATEROLE privilege can change any of these settings, but only for non-superuser roles. Ordinary roles can only change their own password.

Note: SET and RESET commands are currently not supported in connection with ALTER ROLE and will result in an error. See SET and About Server Configuration Parameters for information about user-settable configuration parameters.

Parameters

<name>

The name of the role whose attributes are to be altered.

<newname>

The new name of the role.

<queue_name>

The name of the resource queue to which the user-level role is to be assigned. Only roles with LOGIN privilege can be assigned to a resource queue. To unassign a role from a resource queue and put it in the default resource queue, specify NONE. A role can only belong to one resource queue.

SUPERUSER | NOSUPERUSER
CREATEDB | NOCREATEDB
CREATEROLE | NOCREATEROLE
CREATEEXTTABLE | NOCREATEEXTTABLE [(<attribute>=’<value>’)]

If CREATEEXTTABLE is specified, the role being defined is allowed to create external tables. The default type is readable and the default protocol is gpfdist if not specified. NOCREATEEXTTABLE (the default) denies the role the ability to create external tables. Using the file protocol when creating external tables is not supported. This is because HAWQ cannot guarantee scheduling executors on a specific host. Likewise, you cannot use the execute command with ON ALL and ON HOST for the same reason. Use the ON MASTER/<number>/SEGMENT <segment_id> to specify which segment instances are to execute the command.

INHERIT | NOINHERIT
LOGIN | NOLOGIN
CONNECTION LIMIT <connlimit>
PASSWORD ’<password>’
ENCRYPTED | UNENCRYPTED
VALID UNTIL ’<timestamp>’

These clauses alter role attributes originally set by CREATE ROLE.

DENY <deny_point>
DENY BETWEEN <deny_point> AND <deny_point>

The DENY and DENY BETWEEN keywords set time-based constraints that are enforced at login. DENYsets a day or a day and time to deny access. DENY BETWEEN sets an interval during which access is denied. Both use the parameter <deny_point> that has following format:

  1. DAY <day> [ TIME '<time>' ]

The two parts of the <deny_point> parameter use the following formats:

For <day>:

  1. {'Sunday' | 'Monday' | 'Tuesday' |'Wednesday' | 'Thursday' | 'Friday' |
  2. 'Saturday' | 0-6 }

For <time>:

  1. { 00-23 : 00-59 | 01-12 : 00-59 { AM | PM }}

The DENY BETWEEN clause uses two <deny_point> parameters.

  1. DENY BETWEEN <deny_point> AND <deny_point>

DROP DENY FOR <deny_point>

The DROP DENY FOR clause removes a time-based constraint from the role. It uses the <deny_point> parameter described above.

Notes

Use GRANT and REVOKE for adding and removing role memberships.

Caution must be exercised when specifying an unencrypted password with this command. The password will be transmitted to the server in clear text, and it might also be logged in the client’s command history or the server log. The psql command-line client contains a meta-command \password that can be used to safely change a role’s password.

It is also possible to tie a session default to a specific database rather than to a role. Role-specific settings override database-specific ones if there is a conflict.

Examples

Change the password for a role:

  1. ALTER ROLE daria WITH PASSWORD 'passwd123';

Change a password expiration date:

  1. ALTER ROLE scott VALID UNTIL 'May 4 12:00:00 2015 +1';

Make a password valid forever:

  1. ALTER ROLE luke VALID UNTIL 'infinity';

Give a role the ability to create other roles and new databases:

  1. ALTER ROLE joelle CREATEROLE CREATEDB;

Give a role a non-default setting of the maintenance_work_mem parameter:

  1. ALTER ROLE admin SET maintenance_work_mem = 100000;

Assign a role to a resource queue:

  1. ALTER ROLE sammy RESOURCE QUEUE poweruser;

Give a role permission to create writable external tables:

  1. ALTER ROLE load CREATEEXTTABLE (type='writable');

Alter a role so it does not allow login access on Sundays:

  1. ALTER ROLE user3 DENY DAY 'Sunday';

Alter a role to remove the constraint that does not allow login access on Sundays:

  1. ALTER ROLE user3 DROP DENY FOR DAY 'Sunday';

Compatibility

The ALTER ROLE statement is a HAWQ extension.

See Also

CREATE ROLE, DROP ROLE, SET, CREATE RESOURCE QUEUE, GRANT, REVOKE