ApsaraDB RDS for PostgreSQL account deletion failure - ApsaraDB RDS

La eliminación de una cuenta falla cuando otros objetos de la base de datos dependen de ella; por ejemplo, si la cuenta es propietaria de tablas, esquemas o bases de datos, o si aparece en la lista de control de acceso (ACL) de otro objeto. Elimine primero esas dependencias y luego elimine la cuenta.

Identificar el error

El error se manifiesta en dos lugares:

Consola de ApsaraDB RDS: código de error AccountActionForbidden, con un mensaje que indica que existen objetos de base de datos dependientes de la cuenta.
SQL: ERROR: role "<username>" cannot be dropped because some objects depend on it

El error de SQL incluye un bloque DETAIL que enumera los objetos y las bases de datos afectados. Utilice esa salida para determinar los siguientes pasos.

Elegir un enfoque

Enfoque	Cuándo utilizarlo
Procesamiento por lotes	La cuenta posee objetos en pocas bases de datos y desea transferirlos o eliminarlos todos rápidamente.
Procesamiento detallado	Se requiere visibilidad completa de cada dependencia antes de actuar; por ejemplo, en un entorno de producción donde algunos objetos deben reasignarse en lugar de eliminarse.

Procesamiento por lotes

Utilice la salida del error para identificar las bases de datos afectadas y luego ejecute REASSIGN OWNED y DROP OWNED en cada una.

REASSIGN OWNED transfiere la propiedad de los objetos, pero no elimina las entradas de ACL. DROP OWNED elimina tanto los objetos restantes como las entradas de ACL correspondientes a la base de datos actual. Ejecute ambos comandos en secuencia dentro de cada base de datos afectada.

Importante

REASSIGN OWNED y DROP OWNED operan dentro de una sola base de datos. Ejecútelos por separado en cada base de datos que contenga objetos dependientes.

Ejemplo

El siguiente ejemplo utiliza una cuenta llamada user_to_be_dropped. Al ejecutar DROP USER se obtiene:

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

El error muestra dependencias en testdb01 y testdb02. Inicie sesión con la cuenta privilegiada (testdbuser en este ejemplo) y procese cada base de datos:

Conéctese a testdb01 y elimine los objetos y privilegios de la cuenta:
```
DROP OWNED BY user_to_be_dropped;
```
Conéctese a testdb02, transfiera la propiedad y luego elimine los objetos y privilegios restantes de la cuenta:

testdb02 es propiedad de user_to_be_dropped , por lo que REASSIGN OWNED es necesario aquí para transferir la propiedad de la base de datos antes de que DROP OWNED pueda limpiar el resto.
```
-- 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;
```
Elimine la cuenta:
```
DROP USER user_to_be_dropped;
```

Procesamiento detallado

Utilice este enfoque para inspeccionar cada dependencia individualmente antes de decidir si debe reasignarla o eliminarla.

Paso 1: Consultar los objetos dependientes

La tabla del sistema pg_shdepend registra las dependencias entre los objetos de una base de datos y los objetos globales, como roles y bases de datos. Consúltela para obtener una lista de todos los objetos que dependen de la cuenta.

Conéctese a la instancia mediante la cuenta privilegiada y ejecute:

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

Salida de ejemplo:

 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

Los campos de la salida son:

Campo	Descripción
`database`	Base de datos que contiene el objeto dependiente. Si está vacío, significa que el objeto es global (por ejemplo, una base de datos o una membresía de rol).
`classname`	Tabla del sistema donde se almacena el objeto.
`oid`	Identificador de objeto (OID) del objeto dependiente. Úselo para buscar el nombre del objeto en el siguiente paso.
`dependency_type`	`Object Owner`: la cuenta es propietaria del objeto. `In Access Control List`: la cuenta tiene privilegios listados en la ACL del objeto.

Paso 2: Buscar y gestionar cada dependencia

Utilice el OID para identificar cada objeto y luego gestiónele según su tipo de dependencia.

Objetos globales (campo database vacío)

Se trata de bases de datos, tablespaces o membresías de rol que existen fuera de cualquier base de datos específica.

OID 16399: la cuenta está en la ACL de testdb01:

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

Revoque el privilegio:

REVOKE ALL ON DATABASE testdb01 FROM user_to_be_dropped;

OID 16409: la cuenta es propietaria de testdb02:
```
SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16409;
```
Transfiera la propiedad o elimine la base de datos (consulte la sección de tipo Owner a continuación para ver las opciones).
OID 16416: una entrada de membresía de rol en pg_auth_members:
```
SELECT oid, roleid::regrole, member::regrole, grantor::regrole FROM pg_auth_members WHERE oid = 16416;
```
El resultado muestra que user_to_be_dropped otorgó user_to_be_dropped_2 a testdbuser. En PostgreSQL 16 o posterior, quien otorgó el privilegio debe revocarlo. Conéctese como user_to_be_dropped y ejecute:
```
\c testdb01 user_to_be_dropped;
REVOKE user_to_be_dropped_2 FROM testdbuser CASCADE;
```

Objetos a nivel de base de datos (campo database no vacío)

Conéctese a la base de datos correspondiente mediante la cuenta privilegiada antes de ejecutar las siguientes consultas.

OID 2200: la cuenta está en la ACL del esquema public en testdb01:

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

Revoque el privilegio:

REVOKE ALL ON SCHEMA public FROM user_to_be_dropped;

OID 16402: la cuenta es propietaria del esquema test_nsp en testdb01:

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

Transfiera la propiedad o elimine el esquema:

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

OID 16403: la cuenta es propietaria de la tabla test_nsp.test_tbl en testdb01:

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

Transfiera la propiedad:

ALTER TABLE test_nsp.test_tbl OWNER TO testdbuser;

OID 16406: la cuenta es propietaria de la tabla public.test_tbl en testdb01:

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

Elimine la tabla:

DROP TABLE public.test_tbl;

Después de gestionar todas las dependencias, vuelva a ejecutar la consulta de pg_shdepend para confirmar que no queden filas:

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

Paso 3: Eliminar la cuenta

Elimine la cuenta desde la consola de ApsaraDB RDS o ejecute:

DROP USER user_to_be_dropped;