DROP USER

The DROP USER statement removes one or more SQL users. You can use the keywords ROLE and USER interchangeably. DROP USER is an alias for DROP ROLE.

The DROP USER statement performs a schema change. For more information about how online schema changes work in CockroachDB, see Online Schema Changes.

Considerations

The admin user/role cannot be dropped, and root must always be a member of admin.
A user/role cannot be dropped if it has privileges. Use REVOKE to remove privileges.
Users/roles that own objects (such as databases, tables, schemas, and types) cannot be dropped until the ownership is transferred to another user/role.
If a user/role is logged in while a different session drops that user, CockroachDB checks that the user exists before allowing it to inherit privileges from the public role. In addition, any active web sessions are revoked when a user is dropped.
To bulk-drop auto-provisioned users (for example, dormant users provisioned through LDAP, OIDC, or JWT authentication), use DROP PROVISIONED ROLES instead.

Required privileges

Non-admin users cannot drop admin users. To drop non-admin users, the user must be a member of the admin role or have the CREATEROLE parameter set.

Synopsis

See DROP ROLE: Synopsis.

Parameters

Parameter	Description
`user_name`	The name of the user to remove. To remove multiple users, use a comma-separate list of usernames. You can use `SHOW USERS` to find usernames.

Example

Remove privileges

All of a user’s privileges must be revoked before the user can be dropped. In this example, first check a user’s privileges. Then, revoke the user’s privileges before removing the user.

CREATE DATABASE test;
CREATE TABLE customers (k int, v int);
CREATE USER max;
GRANT ALL ON TABLE customers TO max;

SHOW GRANTS ON customers FOR max;

  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  test          | public      | customers  | max     | ALL            |      f
(1 row)

REVOKE CREATE,INSERT,UPDATE ON customers FROM max;

Remove default privileges

In addition to removing a user’s privileges, a user’s default privileges must be removed prior to dropping the user. If you attempt to drop a user with modified default privileges, you will encounter an error like the following:

DROP USER max;

ERROR: cannot drop role/user max: grants still exist on test.public.customers
SQLSTATE: 2BP01

To see what privileges the user still has remaining on the table, issue the following statement:

SHOW GRANTS ON TABLE test.customers FOR max;

  database_name | schema_name | table_name | grantee | privilege_type | is_grantable
----------------+-------------+------------+---------+----------------+---------------
  test          | public      | customers  | max     | BACKUP         |      f
  test          | public      | customers  | max     | CHANGEFEED     |      f
  test          | public      | customers  | max     | DELETE         |      f
  test          | public      | customers  | max     | DROP           |      f
  test          | public      | customers  | max     | SELECT         |      f
  test          | public      | customers  | max     | ZONECONFIG     |      f
(6 rows)

To drop the user you must revoke all of the user’s remaining privileges:

REVOKE ALL ON TABLE public.customers FROM max;

Now dropping the user should succeed:

DROP USER max;

Statements

Syntax

Data Types

Spatial Data

Constraints

Considerations

Required privileges

Synopsis

Parameters

Example

Remove privileges

Remove default privileges

See also

​Considerations

​Required privileges

​Synopsis

​Parameters

​Example

​Remove privileges

​Remove default privileges

​See also

Considerations

Required privileges

Synopsis

Parameters

Example

Remove privileges

Remove default privileges

See also