ALTER ROLE

Change a database role (user or group).

Summary

ALTER ROLE name [ [ WITH ] option [ ... ] ]

Where option can be:

    SUPERUSER | NOSUPERUSER
  | CREATEDB | NOCREATEDB
  | CREATEROLE | NOCREATEROLE
  | CREATEEXTTABLE | NOCREATEEXTTABLE  [ ( attribute='value' [, ...] )
     where attributes and values are:
       type='readable'|'writable'
       protocol='gpfdist'|'http'
  | INHERIT | NOINHERIT
  | LOGIN | NOLOGIN
  | REPLICATION | NOREPLICATION
  | CONNECTION LIMIT connlimit
  | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
  | VALID UNTIL 'timestamp'

ALTER ROLE name RENAME TO new_name

ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter { TO | = } { value | DEFAULT }
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] SET configuration_parameter FROM CURRENT
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET configuration_parameter
ALTER ROLE { name | ALL } [ IN DATABASE database_name ] RESET ALL
ALTER ROLE name RESOURCE QUEUE {queue_name | NONE}
ALTER ROLE name RESOURCE GROUP {group_name | NONE}

describe

ALTER ROLE Changes the properties of the YMatrix database role, and there are several variations of this command.

WITH option

Modify Most role attributes that can be specified in CREATE ROLE contain all possible attributes, but do not include options to add or delete membership; use GRANT and REVOKE for those options. Properties not mentioned in this command will retain their original values. Superusers can modify any settings for any role. Roles with CREATEROLE permissions can modify any of these settings, but are limited to non-superuser and non-replication roles. Ordinary users can only modify their passwords.

RENAME

Change the name of the role. Database superusers can rename any role. Roles have CREATEROLE privileges to rename non-superuser roles. Unable to rename the current session user (rename the role as a connection as another user). Because MD5 encrypted passwords use the role name as the key, if the password is MD5 encrypted, renaming the role will clear its password.

SET | RESET

Change the role's session default value for the specified configuration parameters, only sessions in the named database are changed for all databases, or when the database is specified in the IN DATABASE clause. If all is specified instead of the role name, the settings for all roles are changed. Using ALL in IN DATABASE is actually the same as using the command ALTER DATABASE...SET....
Whenever a role subsequently initiates a new session, the specified value becomes the session default value, overriding any settings that exist in the server configuration file (postgresql.conf), or received from the postgres command line. This only happens when login, executing SET ROLE or SET SESSION AUTHORIZATION does not trigger the setting of a new value.
Database specific settings attached to the role will override all database settings. Settings for a specific database or role will override all roles' settings.
For roles without LOGIN permissions, the session default value is invalid. Normal characters can change their own session default values. Superusers can change the default value of anyone's session. Roles with CREATEROLE permissions can change the default values for non-superuser roles. Ordinary characters can only set default values for themselves. Some configuration variables cannot be set like this, or can only be set when the superuser issues a command.

RESOURCE QUEUE

Assign roles to workload management resource queues. When queries are issued, roles are restricted by allocating resource queues. Specify NONE to assign roles to the default resource queue. A role can only belong to one resource queue. For roles without LOGIN privileges, the session defaults have no effect.

RESOURCE GROUP

Assign resource groups to roles. The role will then be subject to concurrent transactions, memory, and CPU limits configured for the resource group. A single resource group can be assigned to one or more roles. Resource groups created for external components cannot be assigned to roles.

Parameters

name

The role name that will be modified.

new_name

The new name of the role.

database_name

The database name where the configuration parameters will be set.

config_parameter=value

Sets the default value of this role session for the specified configuration parameter to the given value. If value is DEFAULT refers to or specify RESET, the specified parameter settings of the role will be deleted, and the role will inherit the system-level default value in the new session. RESET ALL can clear all role special configurations. SET FROM CURRENT Saves the current parameter value of the session as the value specified by the role. If IN DATABASE is specified, it will only be the specified role database. When a new session is opened by the role, the specified parameter value becomes the default value of the session, overriding any settings that exist in the server configuration file (postgresql.conf), or received from the postgres command line.
The variable settings specified by the role will only take effect on login; SET ROLE and SET SESSION AUTHORIZATION do not handle variable settings for the specified role.

group_name

The name of the resource group assigned to the role. Specify group_name to delete the resource group currently allocated by the role for NONE and assign a default resource group based on the role's ability. The SUPERUSER role is assigned to the admin_group resource group, while the default_group resource group is assigned to the non-admin role.
Resource groups created for external components cannot be assigned to roles.

queue_name

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

SUPERUSER | NOSUPERUSER

CREATEDB | NOCREATEDB

CREATEROLE | NOCREATEROLE

CREATEUSER | NOCREATEUSER

CREATEUSER and NOCREATEUSER have expired, but are still accepted as SUPERUSER and NOSUPERUSER. Note that they are not equivalent to the CREATEROLE and NOCREATEROLE clauses.

CREATEEXTTABLE | NOCREATEEXTTABLE [(attribute='value')]

If CREATEEXTTABLE is specified, the defined roles are allowed to create external tables. If not specified, the default type is readable, and the default protocol is gpfdist. NOCREATEEXTTABLE (default) rejects the role's ability to create external tables. Note that the external table file or execute protocol used can only be created by superusers.

INHERIT | NOINHERIT

REPLICATION

NOREPLICATION

CONNECTION LIMIT connlimit

PASSWORD password

ENCRYPTED | UNENCRYPTED

VALID UNTIL 'timestamp'

These clauses change the original set role properties through 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. DENY Sets a day or day to deny access. DENY BETWEEN Sets the interval for access denied. Both use the following format parameters deny_point:
DAY day [ TIME 'time' ]
deny_point The two-part parameters use the following format:
For day:{'Sunday' | 'Monday' | 'Tuesday' |'Wednesday' | 'Thursday' | 'Friday' | 'Saturday' | 0-6 }
For time:
{ 00-23 : 00-59 | 01-12 : 00-59 { AM | PM }}
The DENY BETWEEN clause uses two deny_point parameters.DENY BETWEEN deny_point AND deny_pointDROP DENY FOR deny_point
The DROP DENY FOR clause removes time-based constraints from the role. It uses the deny_point parameter mentioned above.

Notice

Use CREATE ROLE to add a role, and use DROP ROLE to delete the role.

Use GRANT and REVOKE to add and delete role members.

Care must be taken when specifying an unencrypted password using this command. The password will be sent to the server in plain text, and may also be recorded in the client's command history or server log. The psql command line client contains a meta command \password that can be used to safely change the role's password.

You can also bind session defaults to a specific database instead of a role. If there is a conflict, role-specific settings override database-specific settings. See ALTER DATABASE.

Example

Change the role's password:

ALTER ROLE daria WITH PASSWORD 'passwd123';

Delete the role's password:

ALTER ROLE daria WITH PASSWORD NULL;

Change password expiration date:

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

Make the password permanently valid:

ALTER ROLE luke VALID UNTIL 'infinity';

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

ALTER ROLE joelle CREATEROLE CREATEDB;

Give the role a non-default setting maintenance_work_mem parameter:

ALTER ROLE admin SET maintenance_work_mem = 100000;

Give the role a non-default, specifying the database parameter client_min_messages value:

ALTER ROLE fred IN DATABASE devel SET client_min_messages = DEBUG;

Assign roles to resource queues:

ALTER ROLE sammy RESOURCE QUEUE poweruser;

Grant the role permission to create writable external tables:

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

Changing roles does not allow login access on Sunday:

ALTER ROLE user3 DENY DAY 'Sunday';

Change the role to remove the constraints that Sunday does not allow login access:

ALTER ROLE user3 DROP DENY FOR DAY 'Sunday';

Specify a new resource group to the role:

ALTER ROLE parttime_user RESOURCE GROUP rg_light;

compatibility

The ALTER ROLE statement is an extension of the YMatrix database