Help Center/ GaussDB(DWS)/ Troubleshooting/ Account/Permission/Password/ A User Cannot Be Deleted Due to Its Dependencies
Updated on 2024-03-08 GMT+08:00
View PDF

A User Cannot Be Deleted Due to Its Dependencies

Symptom

When a user is no longer used or the role of the user changes, the user account needs to be deregistered and the permission needs to be revoked. However, when the user is deleted, an error message similar to role "u1" cannot be dropped because some objects depend on it is displayed.

For example, if you want to delete user u1, the following information is displayed:

1
2
3
4
 
testdb=# DROP USER u1;
ERROR:  role "u1" cannot be dropped because some objects depend on it 
DETAIL:  owner of database testdb 
3 objects in database gaussdb

Possible Causes

If the permissions of a user or role are complex and have many dependencies, an error message is displayed when you delete the user or role, indicating that the user or role has dependencies and cannot be deleted. Obtain the following information based on the error information:

  • The user to be deleted is the owner of database testdb.
  • It has three dependent objects are in the GaussDB database.

Handling Procedure

  • If a user to be deleted is the owner of a database. You need to reassign the object ownerships to another user. Use either of the following methods:
    Method 1: Transfer the database ownership to another user. For example, run the ALTER statement to change the owner user u1 of the testdb database to u2. 
    1
2
3
4
5
6
7
8
    		 
    testdb=# ALTER DATABASE testdb OWNER to u2;
ALTER DATABASE
testdb=# \l 
                                   List of databases    
   Name    |   Owner  | Encoding |   Collate   |    Ctype    |    Access privileges 
-----------+----------+----------+-------------+-------------+------------------------- 
 testdb    | u2       | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
(4 rows)
    After the command for deleting the u1 user is executed, the message "owner of database testdb" is not displayed. 
    1
2
3
    		 
    testdb=# DROP USER u1;
ERROR:  role "u1" cannot be dropped because some objects depend on it 
DETAIL:  3 objects in database gaussdb
    Method 2: If the testdb database is no longer required, delete it. Change the owners of all database objects owned by u1 to u2. 
    1
2
    		 
    testdb=# REASSIGN OWNED BY u1 TO u2;
REASSIGN OWNED

    Clear objects whose owner is u1. Exercise caution when running this command. Schemas with the same name will also be deleted.

    1
2
    		 
    testdb=# DROP OWNED by u1;
DROP OWNED
  • If the user to be deleted has dependencies. You need to remove the dependencies before you delete it. The method is as follows:
    1. Identify dependencies. According to the error information "3 objects in database gaussdb", three objects in the GaussDB database depend on u1. Due to the system catalog dependency in the database, detailed information about dependent objects is not printed in other databases, but is printed in the GaussDB database when DROP USER is executed in GaussDB database.

      Run the following command to connect to the GaussDB database:

      1
2
3
4
      		 
      gaussdb=# DROP USER u1;
ERROR:  role "u1" cannot be dropped because some objects depend on it
DETAIL:  privileges for table pg_class
privileges for schema u2

      The obtained dependency details are as follows:

      1. privileges for table pg_class: permissions of user u1 on pg_class.
      2. Permission of user u1 on schema u2.
    2. Revoke the permissions on dependent objects. 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
      		 
      gaussdb=# SELECT relname,relacl FROM pg_class WHERE relname = 'pg_class';
 relname  |              relacl
----------+----------------------------------
 pg_class | {=r/Ruby,u1=r/Ruby}
(1 row)

gaussdb=#SELECT nspname,nspacl FROM pg_namespace WHERE nspname = 'u2';
 nspname |           nspacl
---------+-----------------------------
 u2      | {u2=UC/u2,u2=LP/u2,u1=U/u2}

gaussdb=# REVOKE SELECT ON TABLE pg_class FROM u1;
REVOKE
gaussdb=# REVOKE USAGE ON SCHEMA u2 FROM u1;
REVOKE
    3. Delete the user again. The user can be deleted successfully, and the message indicating that dependencies exist is not displayed. 
      1
2
      		 
      gaussdb=# DROP USER u1;
DROP USER
  • In some scenarios, the dependent objects of the user to be deleted are unknown, but the deletion still fails. The following uses a constructed case to demonstrate how to handle this situation. Create user u3 and assign the SELECT permission to user u2. 
    1
2
3
    		 
    testdb2=# DROP USER u3;
ERROR:  role "u3" cannot be dropped because some objects depend on it
DETAIL:  2 objects in database gaussdb
    1. The pg_shdepend system catalog records the OIDs of dependent objects and their dependencies. Obtain the OID of the user, and then search the system catalogs for the corresponding dependency records. 
       1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
      		 
      testdb2=# SELECT oid ,rolname FROM pg_roles WHERE rolname = 'u3';
    oid     | rolname
------------+---------
 2147484573 | u3
(1 row)

gaussdb=# SELECT * FROM pg_shdepend WHERE refobjid = 2147484573;
 dbid  | classid |   objid    | objsubid | refclassid |  refobjid  | deptype | objfile
-------+---------+------------+----------+------------+------------+---------+---------
 16073 |    2615 | 2147484575 |        0 |       1260 | 2147484573 | o       |
 16073 |    2615 | 2147484025 |        0 |       1260 | 2147484573 | a       |
(2 rows)

      The values of dependType may be different. There are two records. One indicates permission dependency (a), and the other indicates that the object is the owner of another object.

    2. classid indicates the ID of the record table that records the object that depends on the user. You can use the classid to find the dependency in pg_class. 
      1
2
3
4
5
      		 
      gaussdb=# SELECT relname,relacl FROM pg_class WHERE oid = 2615;
   relname    |     relacl
--------------+----------------
 pg_namespace | {=r/role23}
(1 row)
    3. The query result shows that the record table is pg_namespace. It can be concluded that the object depends on the user is a schema. In pg_namespace, query the objid obtained in 1 to determine the specific object. 
      1
2
3
4
5
6
      		 
      gaussdb=# SELECT nspname,nspacl FROM pg_namespace WHERE oid in (2147484575,2147484025);
 nspname |                         nspacl
---------+---------------------------------------------------------
 u3      |
 u2      | {u2=UC/u2,u2=LP/u2,u3=U/u2}
(2 rows)

      There are two schemas. u3 is the schema with the same name as the user, and u2 is the schema to which the permission is granted. Revoke the permission on the schema.

      1
2
      		 
      gaussdb=# REVOKE USAGE ON SCHEMA u2 FROM u3;
REVOKE
    4. Delete user u3. The deletion is successful. 
      1
2
      		 
      gaussdb=# DROP USER u3;
DROP USER