Updated on 2025-05-29 GMT+08:00

CREATE USER

Description

Creates a user with a specified password. A user is a basic element for GaussDB authentication. You can use the correct username and password to log in to the GaussDB and grant different permissions to different users so that different users can perform different operations.

Precautions

  • A user created using the CREATE USER statement has the LOGIN permission by default.
  • When you run the CREATE USER command to create a user, the system creates a schema with the same name as the user in the database where the command is executed.
  • The owner of an object created by a system administrator in a schema with the same name as a common user is the common user, not the system administrator.

Syntax

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

The option clause is used to configure information, including permissions and properties.

{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

  • user_name

    Name of the user to be created.

    Value range: a string. It must comply with Identifier Naming Conventions and 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 username. If a username contains uppercase letters, the database automatically converts the uppercase letters into lowercase letters. To create a username that contains uppercase letters, enclose the username with double quotation marks ("").

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

  • password

    Specifies the login password.

    The 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 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 user is created.

    Value range: a string

For other parameters, see CREATE ROLE.

Examples

-- Create user jim whose login password is ********.
gaussdb=# CREATE USER jim PASSWORD '********';

-- Create user kim whose login password is ********.
gaussdb=# CREATE USER kim IDENTIFIED BY '********';

-- Create user tom whose login password is ********.
gaussdb=# CREATE USER TOM PASSWORD '**********';

-- Create user TOM whose login password is ********.
gaussdb=# CREATE USER "TOM" PASSWORD '**********';

-- To create a user with the CREATEDB permission, add the CREATEDB keyword.
gaussdb=# CREATE USER dim CREATEDB PASSWORD '********';

-- Query the permissions of the dim user.
gaussdb=#  \du dim
           List of roles
 Role name | Attributes | Member of 
-----------+------------+-----------
 dim       | Create DB  | {}
(You can see that the dim user has the CREATEDB permission.)

-- Change the login password of user jim.
gaussdb=# ALTER USER jim IDENTIFIED BY '**********' REPLACE '********';

-- Add the CREATEROLE permission to jim.
gaussdb=# ALTER USER jim CREATEROLE;

-- View the CREATEROLE permission added to user jim.
gaussdb=# \du jim
            List of roles
 Role name | Attributes  | Member of 
-----------+-------------+-----------
 jim       | Create role | {}

-- Set enable_seqscan to on. (The setting will take effect in the next session.)
gaussdb=# ALTER USER jim SET enable_seqscan TO on;

-- Reset the enable_seqscan parameter for jim.
gaussdb=# ALTER USER jim RESET enable_seqscan;

-- Lock jim.
gaussdb=# ALTER USER jim ACCOUNT LOCK;

-- Unlock jim.
gaussdb=# ALTER USER jim ACCOUNT UNLOCK;

-- Change the user password.
gaussdb=# ALTER USER dim WITH PASSWORD '********';

-- Change the username.
gaussdb=# ALTER USER dim RENAME TO lisa;

-- Create user user1 with the OPRADMIN permission and user user2 with the SYSADMIN permission.
gaussdb=# CREATE USER user1 WITH OPRADMIN PASSWORD '********';
gaussdb=# CREATE USER user2 WITH SYSADMIN PASSWORD '********';

-- Query the permissions.
gaussdb=# \du user1
                    List of roles
 Role name |         Attributes          | Member of 
-----------+-----------------------------+-----------
 user1     |        Operatoradmin        | {}

gaussdb=# \du user2
                 List of roles
 Role name |       Attributes       | Member of 
-----------+------------------------+-----------
 user2     |       Sysadmin         | {}

-- Create role user3 with the CREATEDB permission.
gaussdb=# CREATE USER user3 CREATEDB PASSWORD '********';
CREATE ROLE

-- Query permissions.
gaussdb=# \du user3
                  List of roles
 Role name |       Attributes        | Member of 
-----------+-------------------------+-----------
 user3     |       Create DB         | {}

-- Create role user4 using the ADMIN clause.
gaussdb=# CREATE USER user4 WITH  CREATEDB  ADMIN user3  PASSWORD '********';
CREATE ROLE
-- Query permissions.
gaussdb=# \du user3
                  List of roles
 Role name |       Attributes        | Member of 
-----------+-------------------------+-----------
 user3     |       Create DB         | {user4}

gaussdb=# \du user4
                  List of roles
 Role name |       Attributes        | Member of 
-----------+-------------------------+-----------
 user4     |       Create DB         | {}

-- Delete the roles.
gaussdb=# DROP USER user1;
gaussdb=# DROP USER user2;
gaussdb=# DROP USER user3;
gaussdb=# DROP USER user4;

-- Configuring the separation of duties: If the parameter enableSeparationOfDuty is set to on, the separation of duties is enabled to restrict the permissions of system administrators. System administrators are not allowed to create or modify user configurations. This effectively controls system administrators' access to private user data.

-- Check whether separation of duties is enabled.
gaussdb=# SHOW enableSeparationOfDuty;
 enableSeparationOfDuty 
------------------------
 off
(1 row)
-- Run the following statement to modify parameters:
gs_guc set -Z coordinator -Z datanode -N all -I all -c "enableSeparationOfDuty = on"

-- After the setting is successful, the setting takes effect after a restart.
gs_om -t stop 
gs_om -t start

-- Check the separation of duties again.
gaussdb=# SHOW enableSeparationOfDuty;
 enableSeparationOfDuty 
------------------------
 on
(1 row)

-- Delete the user.
gaussdb=# DROP USER kim CASCADE;
gaussdb=# DROP USER jim CASCADE;
gaussdb=# DROP USER lisa CASCADE;
gaussdb=# DROP USER TOM CASCADE;
gaussdb=# DROP USER "TOM" CASCADE;

Helpful Links

ALTER USER, CREATE ROLE, and DROP USER