Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

CREATE ROLE

Updated on 2024-10-14 GMT+08:00

Function

CREATE ROLE 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

  • CREATE ROLE adds a role to a 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

1
CREATE ROLE role_name [ [ WITH ] option [ ... ] ] [ ENCRYPTED | UNENCRYPTED ] { PASSWORD | IDENTIFIED BY } { 'password' [EXPIRED] | DISABLE };
The syntax of role information configuration clause option is as follows:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
{SYSADMIN | NOSYSADMIN}
    | {MONADMIN | NOMONADMIN}
    | {OPRADMIN | NOOPRADMIN}
    | {POLADMIN | NOPOLADMIN}
    | {AUDITADMIN | NOAUDITADMIN}
    | {CREATEDB | NOCREATEDB}
    | {USEFT | NOUSEFT}
    | {CREATEROLE | NOCREATEROLE}
    | {INHERIT | NOINHERIT}
    | {LOGIN | NOLOGIN}
    | {REPLICATION | NOREPLICATION}
    | {INDEPENDENT | NOINDEPENDENT}
    | {VCADMIN | NOVCADMIN}
    | {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 rol e_name [, ...]
    | USER role_name [, ...]
    | SYSID uid
    | DEFAULT TABLESPACE tablespace_name
    | PROFILE DEFAULT
    | PROFILE profile_name
    | PGUSER

Parameter Description

  • role_name

    Specifies the name of a role.

    Value range: a string. It must comply with the identifier naming convention and 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 ("").

  • password

    Specifies the login password.

    The new password must:

    • Contain at least eight characters. This is the default length.
    • Differ from the role name or the role name spelled backward.
    • Contain at least three of the following character types: uppercase characters, lowercase characters, digits, and special characters (limited to ~!@#$ %^&*()-_=+\|[{}];:,<.>/?). If the password contains characters other than the preceding characters, an error will be reported during statement execution.
    • 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 not advised 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 plaintext 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 select EXPIRED. That is, you can create a user whose password is invalid. The user cannot perform simple query or extended query. 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

    Determines whether a new role is a system administrator. Roles having the SYSADMIN attribute have the highest permission.

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

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

  • MONADMIN | NOMONADMIN

    Determines whether a role is a monitoring administrator.

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

  • OPRADMIN | NOOPRADMIN

    Determines whether a role is an O&M administrator.

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

  • POLADMIN | NOPOLADMIN

    Determines whether a role is a security policy administrator.

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

  • AUDITADMIN | NOAUDITADMIN

    Determines whether a role has the audit and management attributes.

    If not specified, NOAUDITADMIN is the default.

  • CREATEDB | NOCREATEDB

    Determines 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

    Determines 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

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

  • LOGIN | NOLOGIN

    Determines 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

    Determines 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.

  • INDEPENDENT | NOINDEPENDENT

    Defines private, independent roles. For a role with the INDEPENDENT attribute, administrators' permissions to control and access this role are separated. The rules are as follows:

    • Administrators have no permission to add, delete, query, modify, copy, or authorize the corresponding table objects without the authorization from the INDEPENDENT role.
    • If permissions related to private user tables are granted to non-private users, the system administrator will obtain the same permissions.
    • System administrators and security administrators with the CREATEROLE attribute have no permission to modify the inheritance relationship of the INDEPENDENT role without the authorization of the INDEPENDENT role.
    • System administrators have no permission to modify the owner of the table objects for the INDEPENDENT role.
    • System administrators and security administrators with the CREATEROLE attribute have no permission to remove the INDEPENDENT attribute of the INDEPENDENT role.
    • System administrators and security administrators with the CREATEROLE attribute have no permission to change the database password of the INDEPENDENT role. The INDEPENDENT role must manage its own password. If the password is lost, it cannot be reset.
    • The SYSADMIN attribute of a user cannot be changed to the INDEPENDENT attribute.
    • Operations performed by users in the INDEPENDENT role are recorded in audit logs based on the audit policy.
  • VCADMIN | NOVCADMIN

    Defines the role of a logical cluster administrator. A logical cluster administrator has the following permissions other than permissions of common users:

    • Create, modify, and delete resource pools in the associated logical cluster. (The current feature is a lab feature. Contact Huawei technical support before using it.)
    • Grant the access permission on the associated logical cluster to other users or roles, or revoke the access permission from those users or roles.
  • 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.

    NOTICE:
    • The system administrator is not restricted by this parameter.
    • connlimit is calculated for each CN. The number of connections in a cluster is calculated using the following formula: Number of connections in a cluster = connlimit x Number of normal CNs.

    Value range: an integer greater than or equal to -1. The default value is -1, which means unlimited.

  • 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.

  • RESOURCE POOL

    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.

  • 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

    Specifies the name of the logical cluster associated with a user. (The current feature is a lab feature. Contact Huawei technical support before using it.) If the name of the logical cluster contains uppercase characters or special characters, enclose the name with double quotation marks ("").

  • 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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
-- Create role manager whose password is xxxxxxxxxx:
openGauss=# CREATE ROLE manager IDENTIFIED BY 'xxxxxxxxxxx';

-- Create a role with its validity from January 1, 2015 to January 1, 2026.
openGauss=# CREATE ROLE miriam WITH LOGIN PASSWORD 'xxxxxxxxxxxx' VALID BEGIN '2015-01-01' VALID UNTIL '2026-01-01';

-- Change the password of role manager to abcd@123.
openGauss=# ALTER ROLE manager IDENTIFIED BY 'abcd@123' REPLACE 'xxxxxxxxxxx';

-- Change role manager to the system administrator.
openGauss=# ALTER ROLE manager SYSADMIN;

-- Delete role manager.
openGauss=# DROP ROLE manager;

-- Delete role miriam.
openGauss=# DROP ROLE miriam;

Helpful Links

SET ROLE, ALTER ROLE, DROP ROLE, GRANT, and REVOKE

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback