Failed to delete an account in ApsaraDB RDS for PostgreSQL - ApsaraDB RDS: Central de Documentação do Alibaba Cloud

Sintomas

Ao tentar excluir uma conta da instância do ApsaraDB RDS for PostgreSQL, o sistema relata um erro.

Ao excluir a conta no console do ApsaraDB RDS, o sistema exibe uma mensagem de erro.
- A mensagem indica que objetos de banco de dados dependem da conta e que a exclusão só pode ocorrer após a remoção dessa dependência.
- Código de erro: AccountActionForbidden.
A execução de uma instrução SQL para excluir a conta retorna o seguinte erro: ERROR: role "<username>" cannot be dropped because some objects depend on it

Causa

A exclusão falha se você tentar remover uma conta da qual objetos de banco de dados da instância RDS dependem.

Exemplo de problema

Por exemplo, suponha que uma conta chamada user_to_be_dropped seja usada para criar objetos, conforme demonstrado nas instruções SQL a seguir:

Use uma conta privilegiada para criar um banco de dados de teste chamado testdb01.
```
CREATE DATABASE testdb01;
```

Utilize uma conta privilegiada para conectar-se ao testdb01, crie uma conta de teste chamada user_to_be_dropped e conceda as permissões necessárias a essa conta.

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;

Conecte-se ao testdb01 com a conta user_to_be_dropped para criar objetos e um banco de dados de teste chamado testdb02.

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

Acesse o testdb02 usando a conta user_to_be_dropped, crie objetos, defina uma nova conta de teste chamada user_to_be_dropped_2 e conceda a função da nova conta a uma conta privilegiada (neste exemplo, testdbuser).
```
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;
```

Ao tentar excluir a conta user_to_be_dropped, a operação falha devido à existência de objetos dependentes.

A figura a seguir mostra a mensagem de erro exibida no console do ApsaraDB RDS.

A mensagem informa "Database objects depend on the account for this operation. Please remove the dependencies and try again." e o código de erro é AccountActionForbidden.

Instruções SQL:

Nota

As instruções SQL de exemplo são executadas para excluir uma conta do banco de dados padrão chamado 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

Importante

A conta privilegiada inicial não pode ser excluída.

Soluções

Processamento em lote. Este método é de granularidade grossa, conciso e eficiente. Caso a execução da instrução SQL necessária para excluir uma conta resulte em erro, transfira os objetos dessa conta para outra em lotes, conforme indicado na mensagem. Em seguida, revogue todas as permissões da conta que deseja excluir.
Processamento refinado. Cada permissão e objeto ficam transparentes e controláveis. Consulte os objetos que dependem da conta alvo e remova-os individualmente.

Processamento em lote

Identifique os objetos e permissões da conta a ser excluída com base na mensagem de erro. Neste cenário, utilizamos o exemplo de problema.
```
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
```
O erro confirma que a conta alvo user_to_be_dropped possui objetos dependentes nos bancos de dados testdb01 e testdb02. Especificamente:
- No banco de dados testdb01, a conta tem privilégios sobre quatro objetos.
- A conta é proprietária do banco de dados testdb02.
Faça login nos bancos de dados mencionados no erro usando a conta privilegiada, transfira os objetos da conta a ser excluída para outra conta e remova as permissões sobre esses objetos. Neste exemplo, a conta testdbuser atua como privilegiada e recebe a transferência dos objetos.
- Conecte-se ao banco de dados testdb01 e remova os objetos pertencentes à conta user_to_be_dropped.
```
DROP OWNED BY user_to_be_dropped;
```
- Acesse o banco de dados testdb02, reatribua a propriedade para outra conta e elimine os objetos da conta user_to_be_dropped.
```
-- 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;
```

Processamento refinado

Etapa 1: Consultar objetos dependentes

A tabela de sistema pg_shdepend registra as dependências de objetos de banco de dados em relação a objetos compartilhados, como funções (roles). Consulte essa tabela para descobrir quais objetos dependem da conta alvo. Para localizar objetos dependentes da conta user_to_be_dropped, siga os passos abaixo:

Conecte-se à instância de banco de dados com uma conta privilegiada e busque os objetos associados à conta user_to_be_dropped.

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

O sistema retorna os seguintes resultados:

 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

A tabela a seguir descreve os campos presentes nos resultados.

Campo	Descrição
`database`	Banco de dados onde reside o objeto dependente. Se este campo estiver vazio, o objeto dependente é global.
`classname`	Nome da tabela de sistema.
`oid`	Identificador de objeto (OID) do objeto dependente.
`case`	Tipo de dependência. Os tipos mais comuns incluem: Owner: A conta que você deseja excluir é proprietária do objeto. ACL: A conta que você deseja excluir consta na lista de controle de acesso (ACL).

Para cada oid retornado na etapa anterior, conecte-se ao banco de dados correspondente usando uma conta privilegiada e consulte a tabela de sistema relevante para obter o nome do objeto. Por exemplo:

Dependências de objetos globais

Um valor vazio no campo database indica dependência de um objeto global.

Para oid=16399, a lista de controle de acesso de testdb01 contém uma entrada para 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)

Referente ao oid=16409, o proprietário de testdb02 é 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)

Quanto ao oid=16416, a associação de função em pg_auth_members registra as permissões concedidas pela conta user_to_be_dropped.

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)

Dependências de objetos em um único banco de dados

Um campo database preenchido especifica o banco de dados que contém os objetos dependentes.

No caso do oid=2200, a lista de controle de acesso do public schema em testdb01 inclui uma entrada para 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)

Para o oid=16403, o proprietário da tabela test_nsp.test_tbl no testdb01 é 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)

Etapa 2: Tratar objetos dependentes

Escolha um dos métodos abaixo conforme suas necessidades de negócio:

Caso o tipo de dependência seja ACL, revogue as permissões correspondentes.
Se a dependência for do tipo Owner, transfira a propriedade para outra conta ou elimine as dependências vinculadas à conta.

Exemplos:

Dependências do tipo ACL

Para oid=2200, revogue as permissões sobre o public schema em 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;

Referente ao oid=16399, revogue as permissões do banco de dados testdb01.

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. No ApsaraDB RDS for PostgreSQL 16 ou superior, se houver associações de função envolvidas, alterne para a conta user_to_be_dropped e revogue as permissões da conta privilegiada (por exemplo, testdbuser) sobre 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;
```

Dependências do tipo Owner

Acesse o banco de dados onde o objeto dependente reside utilizando a conta privilegiada.

Transfira a propriedade do objeto para outra conta.

No caso do oid=16403, transfira a propriedade da tabela test_nsp.test_tbl em testdb01 para outra conta (neste exemplo, testdbuser).

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;

Excluir objetos dependentes

Exclua o banco de dados testdb02.
```
DROP DATABASE testdb02;
```

Remova o objeto com 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;

Elimine o objeto com 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;

O sistema retorna o seguinte resultado:

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

Etapa 3: Excluir a conta

Exclua a conta desejada no console do ApsaraDB RDS ou executando uma instrução SQL. Exemplo de instrução SQL:

DROP USER user_to_be_dropped;

Excluir uma conta

Exclua a conta necessária no console do ApsaraDB RDS ou via instrução SQL. Exemplo de instrução SQL:

DROP USER user_to_be_dropped;