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} | {VCADMIN | NOVCADMIN} | {PERSISTENCE | NOPERSISTENCE} | CONNECTION LIMIT connlimit | VALID BEGIN 'timestamp' | VALID UNTIL 'timestamp' | 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 rol e_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 that follows the Identifier Naming Conventions with a maximum of 63 characters. If a value contains more than 63 characters, the database truncates it and retains the first 63 characters as the role name. When a role is created, the database will display a message.
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, you need to know the plaintext corresponding to the ciphertext password and ensure a complex plaintext password. The database does not verify the complexity of the ciphertext password, so you should ensure the password security.
- When creating a role, enclose the user password in single quotation marks.
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
Controls whether the password is stored encrypted in the system catalogs. 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 encrypted 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 SYSADMIN, which has the highest permission.
The default value is NOSYSADMIN.
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 MONADMIN.
The default value is NOMONADMIN.
- OPRADMIN | NOOPRADMIN
Specifies whether a role is an OPRADMIN.
The default value is NOOPRADMIN.
- POLADMIN | NOPOLADMIN
Specifies whether a role is a POLADMIN.
The default value is NOPOLADMIN.
- AUDITADMIN | NOAUDITADMIN
Specifies whether a role has the audit and management attributes.
The default value is NOAUDITADMIN.
- CREATEDB | NOCREATEDB
Specifies a role's permission to create databases.
A new role does not have the permission to create databases.
The default value is NOCREATEDB.
- 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.
The default value is NOCREATEROLE.
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 with the LOGIN attribute can be considered as a user.
The default value is NOLOGIN.
- REPLICATION | NOREPLICATION
Specifies whether a role is allowed to initiate streaming replication or put the system in and out of backup mode. A role with REPLICATION attribute is specific to replication.
The default value is NOREPLICATION.
- VCADMIN | NOVCADMIN
This parameter has no actual meaning.
- 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
Specifies how many concurrent connections the role can make.
- System administrators are not restricted by this parameter.
- The number of concurrent connections of each primary database node is calculated separately (which is the value of connlimit). Number of all connections of the database = Value of connlimit x Number of normal primary database nodes.
Value range: an integer in the range [–1, 2^31–1]. The default value is –1, indicating that there is no limit.
- VALID BEGIN
Sets a date and time when the role's password takes effect. If this clause is omitted, the password takes effect immediately.
- VALID UNTIL
Sets a date and time after which the role's password is no longer valid. If this clause is omitted, the password will be valid for all time.
- USER GROUP
Creates a sub-user. This function is not supported in the current version.
- PERM SPACE
Sets the space available for a user.
- TEMP SPACE
Sets the space allocated to the temporary table of a user.
- SPILL SPACE
Sets the operator disk flushing space of a user.
- NODE GROUP
Name of the node group associated with a user. This function is not supported in the current version.
- IN ROLE
Lists one or more existing roles to which the new role will be immediately added as a new member. It is not recommended.
- IN GROUP
Specifies an obsolete spelling of IN ROLE. It is not recommended.
- ROLE
Lists one or more existing roles which are automatically added as members of the new role.
- ADMIN
Similar to ROLE. However, ADMIN grants permissions of new roles to other roles.
- USER
Specifies an obsolete spelling of the ROLE clause.
- SYSID
The SYSID clause is ignored.
- DEFAULT TABLESPACE
The DEFAULT TABLESPACE clause is ignored.
- PROFILE
The PROFILE clause is ignored.
- PGUSER
In the current version, this attribute is reserved only for forward compatibility.
Examples
-- Create a role manager whose password is ********. openGauss=# CREATE ROLE manager IDENTIFIED BY '********'; -- Create a role with its validity from January 1, 2015 to January 1, 2026. openGauss=# CREATE ROLE miriam WITH LOGIN PASSWORD '********' VALID BEGIN '2015-01-01' VALID UNTIL '2026-01-01'; -- Change the password of role manager to **********. openGauss=# ALTER ROLE manager IDENTIFIED BY '**********' REPLACE '********'; -- Change role manager to SYSADMIN. openGauss=# ALTER ROLE manager SYSADMIN; -- Drop role manager. openGauss=# DROP ROLE manager; -- Drop role miriam. openGauss=# DROP GROUP miriam;
Helpful Links
SET ROLE, ALTER ROLE, DROP ROLE, and GRANT
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot