ApsaraDB RDS for PostgreSQL account deletion failure - ApsaraDB RDS

Deleting an account fails when other database objects depend on it — for example, the account owns tables, schemas, or databases, or appears in the access control list (ACL) of another object. Remove those dependencies first, then delete the account.

Identify the error

The failure surfaces in two places:

ApsaraDB RDS console: error code AccountActionForbidden, with a message stating that database objects depend on the account.
SQL: ERROR: role "<username>" cannot be dropped because some objects depend on it

The SQL error includes a DETAIL block that lists the affected objects and databases. Use that output to guide your next steps.

Choose an approach

Approach	When to use
Batch processing	The account owns objects across a small number of databases and you want to transfer or drop everything quickly.
Fine-grained processing	You need full visibility into each dependency before acting — for example, in a production environment where some objects must be reassigned rather than dropped.

Batch processing

Use the error output to identify the affected databases, then run REASSIGN OWNED and DROP OWNED in each one.

REASSIGN OWNED transfers object ownership but does not remove ACL entries. DROP OWNED removes both remaining owned objects and ACL entries for the current database. Run both commands in sequence in each affected database.

Important

REASSIGN OWNED and DROP OWNED operate within a single database. Run them separately in each database that contains dependent objects.

Example

The following example uses an account named user_to_be_dropped. Running DROP USER returns:

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 shows dependencies in testdb01 and testdb02. Log in using the privileged account (testdbuser in this example) and process each database:

Connect to testdb01 and drop the account's objects and privileges:
```
DROP OWNED BY user_to_be_dropped;
```
Connect to testdb02, transfer ownership, then drop the account's remaining objects and privileges:

testdb02 is owned by user_to_be_dropped, so REASSIGN OWNED is needed here to transfer the database ownership before DROP OWNED can clean up the rest.
```
-- Transfer ownership of all objects to the privileged account.
REASSIGN OWNED BY user_to_be_dropped TO testdbuser;
-- Remove remaining objects and ACL entries.
DROP OWNED BY user_to_be_dropped;
```
Delete the account:
```
DROP USER user_to_be_dropped;
```

Fine-grained processing

Use this approach to inspect each dependency individually before deciding whether to reassign or drop it.

Step 1: Query dependent objects

The pg_shdepend system table records dependencies between objects in a single database and global objects such as roles and databases. Query it to list all objects that depend on the account.

Connect to the instance using the privileged account and run:

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 AS dependency_type
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);

Sample output:

 database |    classname    |  oid  |     dependency_type
----------+-----------------+-------+------------------------
 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 fields in the output:

Field	Description
`database`	The database containing the dependent object. Empty means the object is global (for example, a database or role membership).
`classname`	The system table where the object is stored.
`oid`	The object identifier (OID) of the dependent object. Use this to look up the object name in the next step.
`dependency_type`	`Object Owner` — the account owns the object. `In Access Control List` — the account has privileges listed in the object's ACL.

Step 2: Look up and handle each dependency

Use the OID to identify each object, then handle it based on its dependency type.

Global objects (empty database field)

These are databases, tablespaces, or role memberships that exist outside any single database.

OID 16399 — the account is in the ACL of testdb01:

SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16399;

Revoke the privilege:

REVOKE ALL ON DATABASE testdb01 FROM user_to_be_dropped;

OID 16409 — the account owns testdb02:
```
SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16409;
```
Transfer ownership or drop the database (see the Owner-type section below for options).
OID 16416 — a role membership entry in pg_auth_members:
```
SELECT oid, roleid::regrole, member::regrole, grantor::regrole FROM pg_auth_members WHERE oid = 16416;
```
The result shows that user_to_be_dropped granted user_to_be_dropped_2 to testdbuser. On PostgreSQL 16 or later, the grantor must revoke this grant. Connect as user_to_be_dropped and run:
```
\c testdb01 user_to_be_dropped;
REVOKE user_to_be_dropped_2 FROM testdbuser CASCADE;
```

Database-level objects (non-empty database field)

Connect to the relevant database using the privileged account before running the queries below.

OID 2200 — the account is in the ACL of the public schema in testdb01:

SELECT nspname, nspowner::regrole, nspacl FROM pg_namespace WHERE oid = 2200;

Revoke the privilege:

REVOKE ALL ON SCHEMA public FROM user_to_be_dropped;

OID 16402 — the account owns schema test_nsp in testdb01:

SELECT nspname, nspowner::regrole, nspacl FROM pg_namespace WHERE oid = 16402;

Transfer ownership or drop the schema:

-- Option 1: Drop the schema and all objects it contains.
DROP SCHEMA test_nsp CASCADE;

OID 16403 — the account owns table test_nsp.test_tbl in testdb01:

SELECT relname, relnamespace::regnamespace, relowner::regrole, relacl FROM pg_class WHERE oid = 16403;

Transfer ownership:

ALTER TABLE test_nsp.test_tbl OWNER TO testdbuser;

OID 16406 — the account owns table public.test_tbl in testdb01:

SELECT relname, relnamespace::regnamespace, relowner::regrole, relacl FROM pg_class WHERE oid = 16406;

Drop the table:

DROP TABLE public.test_tbl;

After handling all dependencies, re-run the pg_shdepend query to confirm no rows remain:

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

Step 3: Delete the account

Delete the account from the ApsaraDB RDS console, or run:

DROP USER user_to_be_dropped;