DROP USER

Description

DROP USER deletes users in the GaussDB. This permission can be executed only when you have the permission to create users. After the command for deleting a user is executed successfully, the schema with the same name is deleted.

Precautions

CASCADE is used to delete the objects (excluding databases) that depend on the user. It cannot delete locked objects unless the objects are unlocked or the threads locking the objects are terminated.
If the dependent objects are other databases or reside in other databases, manually delete them before deleting the user from the current database. DROP USER cannot delete objects across databases.
Before deleting a user, you need to delete all the objects owned by the user and revoke the user's permissions on other objects. Alternatively, you can specify CASCADE to delete the objects owned by the user and the granted permissions.

Syntax

DROP USER [ IF EXISTS ] user_name [, ...] [ CASCADE | RESTRICT ];

Parameters

IF EXISTS
When this parameter is used, if the specified user does not exist, a notice instead of an error is sent. Therefore, this parameter can be used to avoid errors.
user_name
Specifies the name of the user to be deleted.

Value range: an existing username in the database.
CASCADE | RESTRICT
- CASCADE: automatically deletes the objects that depend on the user.
- RESTRICT: refuses to delete the user if any objects depend on it. This is the default action.
  In GaussDB, the enable_kill_query configuration parameter exists in the gaussdb.conf file. This parameter affects CASCADE.
  - If enable_kill_query is on and CASCADE is used, the statement automatically kills the threads locking dependent objects and then deletes the specified user.
  - If enable_kill_query is off and CASCADE is used, the statement waits until the threads locking dependent objects end and then deletes the specified user.

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;

-- 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;