DROP USER

On this page

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.

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.

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

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;

See also

• CREATE USER
• ALTER USER
• SHOW USERS
• GRANT
• SHOW GRANTS
• Create Security Certificates
• SQL Statements

Was this helpful?