CREATE ROLE

Define a new database role (user or group).

Summary

CREATE ROLE name [[WITH] option [ ... ]]

Where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | CREATEUSER | NOCREATEUSER
    | CREATEEXTTABLE | NOCREATEEXTTABLE 
      [ ( attribute='value'[, ...] ) ]
           where attributes and value are:
           type='readable'|'writable'
           protocol='gpfdist'|'http'
    | INHERIT | NOINHERIT
    | LOGIN | NOLOGIN
    | REPLICATION | NOREPLICATION
    | CONNECTION LIMIT connlimit
    | [ ENCRYPTED | UNENCRYPTED ] PASSWORD 'password'
    | VALID UNTIL 'timestamp' 
    | IN ROLE rolename [, ...]
    | ROLE rolename [, ...]
    | ADMIN rolename [, ...]
    | USER rolename [, ...]
    | SYSID uid [, ...]
    | RESOURCE QUEUE queue_name
    | RESOURCE GROUP group_name
    | [ DENY deny_point ]
    | [ DENY BETWEEN deny_point AND deny_point]

describe

CREATE ROLE has added a new role to the YMatrix database system. A role is an entity that can own database objects and have database privileges. Depending on how the role is used, a role can be considered as a user, a group, or both. You must have CREATEROLE privilege or a database superuser to use this command.

Note that roles are defined at the system level and are valid for all databases in the YMatrix database system.

Parameters

name

Name of the new role.

SUPERUSER

NOSUPERUSER

If SUPERUSER is specified, the defined role will be a superuser, which can override all access restrictions in the database. Superuser status is dangerous and should only be used when it is really needed. You must be a superuser yourself to create a new superuser. The default value is NOSUPERUSER.

CREATEDB

NOCREATEDB

If CREATEDB is specified, the defined role will be allowed to create a new database. NOCREATEDB (default) will prevent the role from creating a database.

CREATEROLE

NOCREATEROLE

If CREATEROLE is specified, the defined roles are allowed to create new roles, change other roles, and delete other roles. NOCREATEROLE (default) will deny the role to create a new role or modify another role.

CREATEUSER

NOCREATEUSER

These clauses are outdated, but are still accepted by the spellings of SUPERUSER and NOSUPERUSER. Note that they are not equivalent to the CREATEROLE and NOCREATEROLE clauses.

CREATEEXTTABLE

NOCREATEEXTTABLE

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. Valid types are gpfdist, gpfdists, http and https. NOCREATEEXTTABLE (default type) rejects the role to create external tables. Note that external tables using file or execute protocols can only be created by superusers.
Using the GRANT...ON PROTOCOL command allows users to create and use external tables with custom protocol types, including the s3 and pxf protocols included with the YMatrix database.

INHERIT

NOINHERIT

If this property is specified, INHERIT (default setting) allows the role to use any database privileges granted to all roles to which it belongs directly or indirectly. When using NOINHERIT, membership of another role is granted only SET ROLE permission to that other role.

NOLOGIN

If specified, LOGIN allows roles to log in to the database. Roles with LOGIN attributes can be considered as users. Roles with NOLOGIN are useful for managing database privileges and can be considered as groups. If not specified, NOLOGIN is the default value unless CREATE ROLE is called via its substitute spelling CREATE USER.

REPLICATION

NOREPLICATION

These clauses determine whether to allow roles to initiate stream replication or to cause the system to enter and exit backup mode. Roles with REPLICATION attributes are roles with high privileges and should only be used on roles that are actually used for replication. If not specified, NOREPLICATION is the default value.

CONNECTION LIMIT connlimit

The maximum number of concurrent connections that this role can make. The default value -1 means there is no limit.

PASSWORD password

Use the LOGIN property to set the user password for the role. If you do not intend to use password authentication, you can ignore this option. If no password is specified, the password will be set to null and that user's password authentication will always fail. An empty password can be optionally explicitly written as PASSWORD NULL.

ENCRYPTED

UNENCRYPTED

These keywords control whether the password is stored in the system catalog in encrypted manner. (If not specified, the default behavior is determined by the configuration parameter password_encryption.) If the displayed password string is already in the MD5 encryption format, the encryption will be stored as is regardless of whether ENCRYPTED or UNENCRYPTED is specified (because the system cannot decrypt the specified encrypted password string). This allows the encrypted password to be reloaded during dump/restore.

VALID UNTIL 'timestamp'

VALID UNTIL clause sets the date and time, after which the password for the role will no longer be valid. If this clause is omitted, the password will never expire.

IN ROLE rolename

Add a new role as a member of the named role. Note that there are no options to add a new role as an administrator. Use a separate GRANT command to do this.

ROLE rolename

Add a named role as a member of that role, making the new role a group.

ADMIN rolename

The ADMIN clause is similar to ROLE, but the mentioned role is added to the new role using WITH ADMIN OPTION, thus giving them the right to grant membership to others for this role.

RESOURCE GROUP group_name

The name of the resource group to be assigned to the new role. This role will be limited by concurrent transactions, memory, and CPU limits configured by the resource group. You can assign a resource group to one or more roles.
If the resource group is not specified for the new role, the default resource group for the role will be automatically assigned to the role, the SUPERUSER role will be assigned to the admin_group, and the default_group will be assigned to the non-admin role.
You can assign the admin_group resource group to any role with the SUPERUSER attribute.
You can assign the default_group resource group to any role.
You cannot assign resource groups created for external components to roles.

RESOURCE QUEUE queue_name

The name of the resource queue to which the new user-level role will be assigned. Only roles with LOGIN privileges can be assigned to resource queues. The special keyword NONE means assigning a role to the default resource queue. A role can only belong to one resource queue.
Roles with SUPERUSER attribute are not restricted by resource queues. For the superuser role, the query is always run immediately regardless of what restrictions are imposed by the assigned resource queue.

DENY deny_point

DENY BETWEEN deny_point AND deny_point

DENY and DENY BETWEEN keywords set time-based constraints enforced at login. DENY Sets the date or date and time when access is denied. DENY BETWEEN Sets an access denial interval. Both use the parameter deny_point with the following format:
DAY day [ TIME 'time' ]
Two parts of the deny_point parameter 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_point## Note The preferred method to add and delete role members (administrative groups) is to use GRANT and REVOKE.

The VALID UNTIL clause defines expiration time only for passwords and not roles. When logging in with a non-password-based authentication method, expiration time is not forced.

The INHERIT attribute controls inheritance that can be granted (access privileges for database objects and role members). It does not apply to special role properties set by CREATE ROLE and ALTER ROLE. For example, even if INHERIT is set, role members with CREATEDB privileges do not immediately grant the ability to create a database. These privileges/attributes are never inherited: SUPERUSER, CREATEDB, CREATEROLE, CREATEEXTTABLE, LOGIN, RESOURCE GROUP, and RESOURCE QUEUE. Properties must be set on each user-level role.

Due to backward compatibility, the INHERIT property is the default property. In previous YMatrix database versions, users always had access to all privileges for the group they belong to. However, NOINHERIT provides a closer match to the semantics specified in the SQL standard.

Be careful when using CREATEROLE privileges. There is no concept of inheritance for the privilege of CREATEROLE-role. This means that even if one character does not have specific privileges but is allowed to create another role, it can easily create another role, which has a privilege different from its own role (except for creating a role with superuser privilege). For example, if a role has CREATEROLE privilege but does not have CREATEDB privileges, it can create a new role using CREATEDB privileges. Therefore, a role with CREATEROLE privilege is considered as an almost superuser role.

Superusers never execute the CONNECTION LIMIT option.

Care must be taken when specifying an unencrypted password using this command. Passwords will be transferred to the server in plaintext and may also be recorded in the client's command history or server log. However, the client program createuser transmits the encrypted password. Additionally, psql contains the command \password, which can be used to change passwords safely later.

Example

Create a role that can be logged in but does not provide a password:

CREATE ROLE jonathan LOGIN;

Create a role that belongs to the resource queue:

CREATE ROLE jonathan LOGIN RESOURCE QUEUE poweruser;

Create roles with passwords that are valid until the end of 2016 (CREATE USER is the same as CREATE ROLE, except that it implies LOGIN):

CREATE USER joelle WITH PASSWORD 'jw8s0F4' VALID UNTIL '2017-01-01';

Create a role that can create a database and manage other roles:

CREATE ROLE admin WITH CREATEDB CREATEROLE;

Create a role that does not allow login access on Sunday:

CREATE ROLE user3 DENY DAY 'Sunday';

Create a role that can create a readable and writable external table of type 'gpfdist':

CREATE ROLE jan WITH CREATEEXTTABLE(type='readable', protocol='gpfdist')
   CREATEEXTTABLE(type='writable', protocol='gpfdist');

Create a role and assign a resource group:

CREATE ROLE bill RESOURCE GROUP rg_light;

compatibility

The SQL standard defines the concepts of users and roles, but treats them as different concepts and leaves all commands that define users to the database implementation specification. In the YMatrix database, users and roles are unified into single type objects. Therefore, roles have more optional attributes than in the standard.

CREATE ROLE is in the SQL standard, but this standard only requires the following syntax:

CREATE ROLE name [WITH ADMIN rolename]

The YMatrix database extension is a choice that allows multiple initial administrators, as well as all other options for CREATE ROLE.