Failed to delete an account in ApsaraDB RDS for PostgreSQL - ApsaraDB RDS

Symptoms

When I delete an account from my ApsaraDB RDS for PostgreSQL instance, an error is reported.

When I delete the account in the ApsaraDB RDS console, an error message is displayed.
- The error message indicates that database objects depend on the account and the account can be deleted only after the dependency is removed.
- Error code: AccountActionForbidden.
Executing an SQL statement to delete the account returns the following error: ERROR: role "<username>" cannot be dropped because some objects depend on it

Cause

If you delete the account on which database objects of the RDS instance depend, the deletion fails.

Sample issue

For example, an account named user_to_be_dropped is used to create objects, as shown in the following SQL statements:

Use a privileged account to create a test database named testdb01.
```
CREATE DATABASE testdb01;
```

Use a privileged account to connect to testdb01, create a test account named user_to_be_dropped, and grant the necessary permissions to the test account.

CREATE USER user_to_be_dropped WITH PASSWORD 'your_password' CREATEROLE CREATEDB;
GRANT ALL ON SCHEMA public TO user_to_be_dropped;
GRANT ALL ON DATABASE testdb01 TO user_to_be_dropped;

Use the user_to_be_dropped account to connect to testdb01, and create objects and a test database named testdb02.

CREATE SCHEMA test_nsp;
CREATE TABLE test_nsp.test_tbl(a1 int);
CREATE TABLE test_tbl(a2 int);
CREATE DATABASE testdb02;

Use the user_to_be_dropped account to connect to testdb02, create objects, create a new test account named user_to_be_dropped_2, and grant the new account's role to a privileged account (testdbuser in this example).
```
CREATE SCHEMA testnsp;
CREATE TABLE testnsp.tbl(a3 int);
CREATE USER user_to_be_dropped_2 WITH PASSWORD 'your_password';
GRANT user_to_be_dropped_2 TO testdbuser;
```

When you try to delete the user_to_be_dropped account, the operation fails because dependent objects exist.

The following figure shows the error message that is displayed in the ApsaraDB RDS console.

The error message is "Database objects depend on the account for this operation. Please remove the dependencies and try again.", and the error code is AccountActionForbidden.

SQL statements:

Note

The sample SQL statements are executed to delete an account from the default database named postgres.

DROP USER user_to_be_dropped;
ERROR:  role "user_to_be_dropped" cannot be dropped because some objects depend on it
DETAIL:  privileges for database testdb01
owner of database testdb02
privileges for membership of role testdbuser in role user_to_be_dropped_2
4 objects in database testdb01
2 objects in database testdb02

Important

The initial privileged account cannot be deleted.

Solutions

Batch processing. This method is coarse-grained, concise, and efficient. If you execute the required SQL statement to delete an account and an error message is displayed, you can transfer the objects of the account to another account in batches as prompted. Then, you can revoke all permissions from the account that you want to delete.
Fine-grained processing. Each permission and object are transparent and controllable. You can query the objects that depend on the account that you want to delete and delete the objects one by one.

Batch processing

Confirm the objects and permissions of the account that you want to delete based on the error message. In this example, the sample issue is used.
```
DROP USER user_to_be_dropped;
ERROR:  role "user_to_be_dropped" cannot be dropped because some objects depend on it
DETAIL:  privileges for database testdb01
owner of database testdb02
privileges for membership of role testdbuser in role user_to_be_dropped_2
4 objects in database testdb01
2 objects in database testdb02
```
The error message confirms that the target account user_to_be_dropped has dependent objects in the testdb01 and testdb02 databases. Specifically:
- In the testdb01 database, the account has privileges on four objects.
- The account is the owner of the testdb02 database.
Use the privileged account to log on to the databases that are involved in the error message, transfer the objects of the account that you want to delete to another account, and then delete the permissions on the objects from the account that you want to delete. In this example, the testdbuser account is used as the privileged account and objects are transferred to the account.
- Connect to the testdb01 database and drop the objects owned by the user_to_be_dropped account.
```
DROP OWNED BY user_to_be_dropped;
```
- Connect to the testdb02 database, reassign ownership to another account, and drop the objects owned by the user_to_be_dropped account.
```
-- Transfer the database ownership.
REASSIGN OWNED BY user_to_be_dropped TO testdbuser;
-- Delete the objects of the account.
DROP OWNED BY user_to_be_dropped;
```

Fine-grained processing

Step 1: Query dependent objects

The pg_shdepend system table records dependencies of database objects on shared objects, such as roles. You can query this table to find which objects depend on the target account. For example, to find objects that depend on the user_to_be_dropped account, do the following:

Use a privileged account to connect to the database instance and query for objects associated with the user_to_be_dropped account.

WITH role as (SELECT oid FROM pg_roles WHERE rolname = 'user_to_be_dropped')
SELECT
    db.datname AS database,
    pg_class.relname AS classname,
    shp.objid AS oid,
    CASE
        WHEN shp.deptype = 'o' THEN 'Object Owner'
        WHEN shp.deptype = 'a' THEN 'In Access Control List'
        WHEN shp.deptype = 'r' THEN 'Policy Object'
    ELSE
        'CANNOT HAPPEN'  
    END
FROM
    pg_shdepend shp
        LEFT JOIN pg_database db ON shp.dbid = db.oid
        JOIN pg_class ON shp.classid = pg_class.oid
WHERE
    shp.refclassid = 1260 AND shp.refobjid IN (SELECT oid FROM role);

The following results are returned:

 database |    classname    |  oid  |          case
----------+-----------------+-------+------------------------
 testdb01 | pg_namespace    |  2200 | In Access Control List
          | pg_database     | 16399 | In Access Control List
 testdb01 | pg_namespace    | 16402 | Object Owner
 testdb01 | pg_class        | 16403 | Object Owner
 testdb01 | pg_class        | 16406 | Object Owner
          | pg_database     | 16409 | Object Owner
 testdb02 | pg_namespace    | 16410 | Object Owner
 testdb02 | pg_class        | 16411 | Object Owner
          | pg_auth_members | 16416 | In Access Control List

The following table describes the fields in the results.

Field	Description
`database`	The database in which the dependent object resides. If this field is empty, the dependent object is a global object.
`classname`	The name of the system table.
`oid`	The object identifier (OID) of the dependent object.
`case`	The dependency type. The following common dependency types are provided: Owner: The account that you want to delete is the owner of the object. ACL: The account that you want to delete is in the access control list (ACL).

For each oid returned in the previous step, use a privileged account to connect to the corresponding database and query the relevant system table to find the object name. For example:

Dependencies of global objects

An empty value in the database field indicates a dependency on a global object.

For oid=16399, the access control list of testdb01 contains an entry for user_to_be_dropped.

SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16399;
 datname  |   datdba   |                                    datacl
----------+------------+------------------------------------------------------------------------------
 testdb01 | testdbuser | {=Tc/testdbuser,testdbuser=CTc/testdbuser,user_to_be_dropped=CTc/testdbuser}
(1 row)

For oid=16409, the owner of testdb02 is user_to_be_dropped.

SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16409;
 datname  |       datdba       | datacl
----------+--------------------+--------
 testdb02 | user_to_be_dropped |
(1 row)

For oid=16416, the role membership in pg_auth_members records the permissions granted by the user_to_be_dropped account.

SELECT oid, roleid::regrole, member::regrole, grantor::regrole FROM pg_auth_members WHERE oid = 16416;
  oid  |        roleid        |   member   |      grantor
-------+----------------------+------------+--------------------
 16416 | user_to_be_dropped_2 | testdbuser | user_to_be_dropped
(1 row)

Dependencies of objects in a single database

A non-empty database field specifies the database that contains the dependent objects.

For oid=2200, the access control list of the public schema in testdb01 contains an entry for user_to_be_dropped.

SELECT nspname, nspowner::regrole, nspacl FROM pg_namespace WHERE oid = 2200;
 nspname |  nspowner  |                                  nspacl
---------+------------+---------------------------------------------------------------------------
 public  | testdbuser | {testdbuser=UC/testdbuser,=U/testdbuser,user_to_be_dropped=UC/testdbuser}
(1 row)

For oid=16403, the owner of the test_nsp.test_tbl table in testdb01 is user_to_be_dropped.

SELECT relname, relnamespace::regnamespace, relowner::regrole, relacl FROM pg_class WHERE oid = 16403;
 relname  | relnamespace |      relowner      | relacl
----------+--------------+--------------------+--------
 test_tbl | test_nsp     | user_to_be_dropped |
(1 row)

Step 2: Handle dependent objects

You can use the following methods based on your business requirements:

If the dependency type is ACL, you can revoke the corresponding permissions.
If the dependency type is Owner, you can transfer the ownership to another account or remove the dependencies on the account.

Examples:

ACL-type dependencies

For oid=2200, revoke permissions on the public schema in testdb01.

SELECT nspname, nspowner::regrole, nspacl FROM pg_namespace WHERE oid = 2200;
 nspname |  nspowner  |                                  nspacl
---------+------------+---------------------------------------------------------------------------
 public  | testdbuser | {testdbuser=UC/testdbuser,=U/testdbuser,user_to_be_dropped=UC/testdbuser}
(1 row)
REVOKE ALL ON SCHEMA public FROM user_to_be_dropped;

For oid=16399, revoke permissions on the testdb01 database.

SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16399;
 datname  |   datdba   |                                    datacl
----------+------------+------------------------------------------------------------------------------
 testdb01 | testdbuser | {=Tc/testdbuser,testdbuser=CTc/testdbuser,user_to_be_dropped=CTc/testdbuser}
(1 row)
REVOKE ALL ON DATABASE testdb01 from user_to_be_dropped;

oid=16416. For ApsaraDB RDS for PostgreSQL 16 and later, if role memberships are involved, you must switch to the user_to_be_dropped account and revoke the permissions from the privileged account (for example, testdbuser) of user_to_be_dropped_2.
```
\c testdb01 user_to_be_dropped;
You are now connected to database "testdb01" as user "user_to_be_dropped".
REVOKE user_to_be_dropped_2 FROM testdbuser cascade;
```

Owner-type dependencies

Log on to the database in which the dependent object resides by using the privileged account.

Transfer the ownership of the object to another account.

For oid=16403, transfer the ownership of the test_nsp.test_tbl table in testdb01 to another account (testdbuser in this example).

SELECT relname, relnamespace::regnamespace, relowner::regrole, relacl FROM pg_class WHERE oid = 16403;
 relname  | relnamespace |      relowner      | relacl
----------+--------------+--------------------+--------
 test_tbl | test_nsp     | user_to_be_dropped |
(1 row)
ALTER TABLE test_nsp.test_tbl OWNER TO testdbuser;

Delete dependent objects

Delete the testdb02 database.
```
DROP DATABASE testdb02;
```

Drop the object with oid=16402.

SELECT nspname, nspowner::regrole, nspacl FROM pg_namespace WHERE oid = 16402;
 nspname  |      nspowner      | nspacl
----------+--------------------+--------
 test_nsp | user_to_be_dropped |
(1 row)
DROP SCHEMA test_nsp cascade;

Drop the object with oid=16406.

SELECT relname, relnamespace::regnamespace, relowner::regrole, relacl FROM pg_class WHERE oid = 16406;
 relname  | relnamespace |      relowner      | relacl
----------+--------------+--------------------+--------
 test_tbl | public       | user_to_be_dropped |
(1 row)
DROP TABLE public.test_tbl;

The following result is returned:

 database | classname | oid | case
----------+-----------+-----+------
(0 rows)

Step 3: Delete the account

Delete the required account in the ApsaraDB RDS console or by executing an SQL statement. Sample SQL statement:

DROP USER user_to_be_dropped;

Delete an account

Delete the required account in the ApsaraDB RDS console or by executing an SQL statement. Sample SQL statement:

DROP USER user_to_be_dropped;