CREATE ROLE

Description

Creates a role.

A role is an entity that owns database objects and permissions. In different environments, a role can be considered a user, a group, or both.

Precautions

• If a role is added to the database, the role does not have the login permission.
• Only the user who has the CREATE ROLE permission or a system administrator is allowed to create roles.

Syntax

CREATE ROLE role_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [EXPIRED] | DISABLE };

{SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {PERSISTENCE | NOPERSISTENCE}
    | CONNECTION LIMIT connlimit
    | VALID BEGIN 'timestamp'
    | VALID UNTIL 'timestamp'
    | RESOURCE POOL 'respool'
    | USER GROUP 'groupuser'
    | PERM SPACE 'spacelimit'
    | TEMP SPACE 'tmpspacelimit'
    | SPILL SPACE 'spillspacelimit'
    | NODE GROUP logic_cluster_name
    | IN ROLE role_name [, ...]
    | IN GROUP role_name [, ...]
    | ROLE role_name [, ...]
    | ADMIN role_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
    | DEFAULT TABLESPACE tablespace_name
    | PROFILE DEFAULT
    | PROFILE profile_name
    | PGUSER

Parameters

• role_name

  Specifies the name of a role.

  Value range: a string. It must comply with the naming convention. A value can contain a maximum of 63 characters. If the value contains more than 63 characters, the database truncates it and retains the first 63 characters as the role name. If a role name contains uppercase letters, the database automatically converts the uppercase letters into lowercase letters. To create a role name that contains uppercase letters, enclose the role name with double quotation marks ("").

  

  The identifier must be letters, underscores (_), digits (0–9), or dollar signs ($) and must start with a letter (a–z) or underscore (_).

• password

  Specifies the login password.

  A new password must:

  • Contain at least eight characters. This is the default length.
  • Differ from the username or the username spelled backward.
  • Contain at least three of the following character types: uppercase characters, lowercase characters, digits, and special characters (limited to ~!@#$%^&*()-_=+\|[{}];:,<.>/?).
  • The password can also be a ciphertext character string that meets the format requirements. This mode is mainly used to import user data. You are advised not to use it directly. If a ciphertext password is used, the user must know the plaintext corresponding to the ciphertext password and ensure that the password meets the complexity requirements. The database does not verify the complexity of the ciphertext password. Instead, the security of the ciphertext password is ensured by the user.
  • Be enclosed by single quotation marks when a role is created.

  Value range: a character string that cannot be empty.

• EXPIRED

  When creating a user, you can specify the EXPIRED parameter to create a user whose password is invalid. The user cannot perform simple or extended queries. The statement can be executed only after the password is changed.

• DISABLE

  By default, you can change your password unless it is disabled. To disable the password of a user, use this parameter. After the password of a user is disabled, the password will be deleted from the system. The user can connect to the database only through external authentication, for example, Kerberos authentication. Only administrators can enable or disable a password. Common users cannot disable the password of an initial user. To enable a password, run ALTER USER and specify the password.

• ENCRYPTED | UNENCRYPTED

  Determines whether a password stored in a system catalog is encrypted. According to product security requirement, the password must be stored encrypted. Therefore, UNENCRYPTED is forbidden in GaussDB. If the password string has already been encrypted in the SHA256 format, it is stored as it was, regardless of whether ENCRYPTED or UNENCRYPTED is specified (since the system cannot decrypt the specified encrypted password string). This allows reloading of encrypted passwords during dump/restore.

• SYSADMIN | NOSYSADMIN

  Specifies whether a new role is a system administrator. Roles with the SYSADMIN attribute have the highest permission.

  Value range: If not specified, NOSYSADMIN is the default.

  When separation of duties is disabled, users with the SYSADMIN permission can create users with the SYSADMIN, REPLICATION, CREATEROLE, AUDITADMIN, MONADMIN, POLADMIN, or CREATEDB permission and common users.

  When separation of duties is enabled, users with the SYSADMIN permission do not have the permission to create users.

• MONADMIN | NOMONADMIN

  Specifies whether a role is a monitor administrator.

  Value range: If not specified, NOMONADMIN is the default.

• OPRADMIN | NOOPRADMIN

  Specifies whether a role is an O&M administrator.

  Value range: If not specified, NOOPRADMIN is the default.

• POLADMIN | NOPOLADMIN

  Specifies whether a role is a security policy administrator.

  Value range: If not specified, NOPOLADMIN is the default.

• AUDITADMIN | NOAUDITADMIN

  Specifies whether a role has the audit and management attributes.

  If not specified, NOAUDITADMIN is the default.

• CREATEDB | NOCREATEDB

  Specifies a role's permission to create databases.

  A new role does not have the permission to create databases.

  Value range: If not specified, NOCREATEDB is the default.

• USEFT | NOUSEFT

  This parameter is reserved and not used in this version.

• CREATEROLE | NOCREATEROLE

  Specifies whether a role will be permitted to create new roles (that is, execute CREATE ROLE and CREATE USER). A role with the CREATEROLE permission can also modify and delete other roles.

  Value range: If not specified, NOCREATEROLE is the default.

  When separation of duties is disabled, users with the CREATEROLE permission can create users with the CREATEROLE, AUDITADMIN, MONADMIN, POLADMIN, or CREATEDB permission and common users.

  When separation of duties is enabled, users with the CREATEROLE permission can create users with the CREATEROLE, MONADMIN, POLADMIN, or CREATEDB permission and common users.

• INHERIT | NOINHERIT

  Specifies whether a role "inherits" the permissions of roles in the same group. It is not recommended.

• LOGIN | NOLOGIN

  Specifies whether a role is allowed to log in to a database. A role having the LOGIN attribute can be considered as a user.

  Value range: If not specified, NOLOGIN is the default.

• REPLICATION | NOREPLICATION

  Specifies whether a role is allowed to initiate streaming replication or put the system in and out of backup mode. A role having the REPLICATION attribute is specific to replication.

  If not specified, NOREPLICATION is the default.

• PERSISTENCE | NOPERSISTENCE

  Defines a permanent user. Only the initial user is allowed to create, modify, and delete permanent users with the PERSISTENCE attribute.

• CONNECTION LIMIT connlimit

  Specifies how many concurrent connections the role can make.

  

  • The system administrator is not restricted by this parameter.
  • connlimit is calculated separately for each primary database node. Number of connections of the database = Value of connlimit x Number of normal primary database nodes.

  Value range: an integer in the range [–1, 2³¹ – 1]. The default value –1 means no limit.

• VALID BEGIN 'timestamp'

  Sets the timestamp when a role takes effect. If this clause is omitted, the role has no valid start time. timestamp indicates the start time. The format is 'YYYY-MM-DD HH:mm:ss'.

• VALID UNTIL 'timestamp'

  Sets a date and time after which the role's password is no longer valid. If this clause is omitted, the role has no valid end time. timestamp indicates the end time. The format is 'YYYY-MM-DD HH:mm:ss'.

• RESOURCE POOL 'respool'

  Sets the name of resource pool used by the role. The name belongs to the system catalog pg_resource_pool.

• USER GROUP 'groupuser'

  Creates a sub-user. This function is not supported in the current version.

• PERM SPACE 'spacelimit'

  Sets the space available for a user.

• TEMP SPACE 'tmpspacelimit'

  Sets the space allocated to the temporary table of a user.

• SPILL SPACE 'spillspacelimit'

  Sets the operator disk flushing space of a user.

• IN ROLE role_name

  Lists one or more existing roles whose permissions will be inherited by a new role. It is not recommended.

• IN GROUP role_name

  Specifies an obsolete spelling of IN ROLE. It is not recommended.

• ROLE role_name

  Lists one or more existing roles which are automatically added as members of the new role.

• ADMIN role_name

  Similar to ROLE. However, ADMIN grants permissions of new roles to other roles.

• USER role_name

  Specifies an obsolete spelling of the ROLE clause.

• SYSID uid

  The SYSID clause is ignored.

• DEFAULT TABLESPACE tablespace_name

  The DEFAULT TABLESPACE clause is ignored.

• PROFILE profile_name

  The PROFILE clause is ignored.

• PGUSER

  In the current version, this attribute is reserved only for forward compatibility.

Examples

• Differences between CREATE ROLE and CREATE USER:

  -- Run CREATE ROLE to create role test_role.
  gaussdb=# CREATE ROLE test_role PASSWORD '********';
  -- Run CREATE USER to create user test_user.
  gaussdb=# CREATE USER test_user PASSWORD '********';
  
  -- View the information. Roles created by CREATE ROLE are not allowed to log in to the database by default.
  gaussdb=# \du test*
              List of roles
   Role name |  Attributes  | Member of 
  -----------+--------------+-----------
   test_role | Cannot login | {}
   test_user |              | {}
  
  -- Enable the test_role role to log in to the database.
  gaussdb=# ALTER ROLE test_role WITH LOGIN;
  
  gaussdb=# \du test*
             List of roles
   Role name | Attributes | Member of 
  -----------+------------+-----------
   test_role |            | {}
   test_user |            | {}
  
  -- View schema information. When CREATE USER is executed to create a user, a schema with the same name is automatically created.
  gaussdb=# \dn test*
      List of schemas
     Name    |   Owner   
  -----------+-----------
   test_user | test_user
  (1 row)
  
  -- Delete.
  gaussdb=# DROP ROLE test_role;
  gaussdb=# DROP USER test_user;

• Create a role whose password is invalid.

  -- Create the role test_role2 whose password is valid.
  gaussdb=# CREATE ROLE test_role2 PASSWORD '********' EXPIRED;
  gaussdb=# ALTER ROLE test_role2 WITH LOGIN;
  
  -- test_role2 cannot perform any operation after logging in to the database. You can perform operations only after changing the password as prompted.
  gaussdb=# SET ROLE test_role2 PASSWORD '********';
  gaussdb=> \d
  ERROR:  Please use "ALTER ROLE user_name IDENTIFIED BY 'password' REPLACE 'old password';" to modify the expired password of user test_role2 before operation!
  
  -- Change the password of test_role2.
  gaussdb=> ALTER ROLE test_role2 IDENTIFIED BY '********' REPLACE '********';
  
  -- Delete.
  gaussdb=> RESET ROLE;
  gaussdb=# DROP ROLE test_role2;

• Create a role and specify the effective date and expiration date.

  -- Create a role with its validity from January 1, 2015 to January 1, 2026.
  gaussdb=# CREATE ROLE test_role3 WITH LOGIN PASSWORD '********' VALID BEGIN '2015-01-01' VALID UNTIL '2026-01-01';
  -- Delete.
  gaussdb=# DROP ROLE test_role3;

Helpful Links

SET ROLE, ALTER ROLE, DROP ROLE, and GRANT