ApsaraDB RDS for PostgreSQLインスタンスのアカウントの削除に失敗した場合はどうすればよいですか。 - ApsaraDB RDS

問題の説明

ApsaraDB RDS for PostgreSQLインスタンスからアカウントを削除すると、エラーが報告されます。

ApsaraDB RDSコンソールでアカウントを削除すると、エラーメッセージが表示されます。
- エラーメッセージは、データベースオブジェクトがアカウントに依存していることを示し、アカウントは依存関係が削除された後にのみ削除できます。
- エラーコードはAccountActionForbiddenです。
SQLステートメントを実行してアカウントを削除すると、次のエラーメッセージが表示されます。error: role "<username>" cannot be drop because some objects depends on it.

原因

RDSインスタンスのデータベースオブジェクトが依存するアカウントを削除すると、削除は失敗します。

問題の例

RDSインスタンスにuser_to_be_dropedという名前のアカウントを作成し、そのアカウントを使用してオブジェクトを作成します。サンプルSQL文:

特権アカウントを使用して、testdb01という名前のテストデータベースを作成します。
```
CREATE DATABASE testdb01;
```
特権アカウントを使用してtestdb01データベースにログインし、user_to_be_dropedという名前のテストアカウントを作成し、テストアカウントに必要な権限を付与します。
```
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;
```
user_to_be_dropedアカウントを使用してtestdb01データベースにログインし、オブジェクトとtestdb02という名前のテストデータベースを作成します。
```
CREATE SCHEMA test_nsp;
CREATE TABLE test_nsp.test_tbl(a1 int);
CREATE TABLE test_tbl(a2 int);
CREATE DATABASE testdb02;
```
user_to_be_dropedアカウントを使用してtestdb02データベースにログインし、オブジェクトとuser_to_be_droped_2という名前のテストアカウントを作成します。次に、テストアカウントに特権アカウントの権限を付与します。この例では、特権アカウントの名前は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;
```

user_to_be_dropedアカウントを削除すると、アカウントに依存するオブジェクトが存在するため、エラーが発生します。

次の図は、ApsaraDB RDSコンソールに表示されるエラーメッセージを示しています。

SQL 文：

説明

サンプルSQL文は、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

ソリューション

バッチ処理。この方法は、粗く、簡潔で、効率的です。 必要なSQL文を実行してアカウントを削除し、エラーメッセージが表示された場合は、プロンプトに従ってアカウントのオブジェクトを別のアカウントにバッチで転送できます。その後、削除するアカウントからすべての権限を取り消すことができます。
きめの細かい処理。各許可とオブジェクトは透明で制御可能です。 削除するアカウントに依存するオブジェクトを照会し、オブジェクトを1つずつ削除できます。

バッチ処理

エラーメッセージに基づいて、削除するアカウントのオブジェクトと権限を確認します。この例では、sample issueが使用されています。
```
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
```
エラーメッセージは、testdb01およびtestdb02データベースのオブジェクトがuser_to_be_dropedアカウントに依存していることを示しています。
- testdb01データベースでは、アカウントには4つのオブジェクトに対する権限があります。
- アカウントはtestdb02データベースの所有者です。
特権アカウントを使用して、エラーメッセージに関連するデータベースにログインし、削除するアカウントのオブジェクトを別のアカウントに転送してから、削除するアカウントからオブジェクトに対する権限を削除します。この例では、testdbuserアカウントが特権アカウントとして使用され、オブジェクトがアカウントに転送されます。
- testdb01データベースにログインし、user_to_be_dropedアカウントのオブジェクトを削除します。
```
DROP OWNED BY user_to_be_dropped;
```
- testdb02データベースにログインし、データベースの所有権を別のアカウントに転送してから、user_to_be_dropedアカウントのオブジェクトを削除します。
```
-- 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;
```

きめの細かい処理

ステップ1: 依存オブジェクトの照会

pg_shdependシステムテーブルは、グローバルオブジェクトに対する単一データベース内のオブジェクトの依存関係を記録します。削除するアカウントに依存するオブジェクトを照会できます。この例では、user_to_be_dropedアカウントに依存するオブジェクトが照会されます。

特権アカウントを使用してRDSインスタンスにログインし、user_to_be_dropedアカウントに依存するオブジェクトを照会します。

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

次の結果が返されます。

 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

次の表に、結果のフィールドを示します。

フィールド	説明
`データベース`	依存オブジェクトが存在するデータベース。このフィールドが空の場合、依存オブジェクトはグローバルオブジェクトです。
`classname`	システムテーブルの名前。
`oid`	依存オブジェクトのオブジェクト識別子 (OID) 。
`ケース`	依存タイプ。以下の一般的な依存関係タイプが提供されます。所有者: 削除するアカウントは、オブジェクトの所有者です。 ACL: 削除するアカウントはアクセス制御リスト (ACL) にあります。

特権アカウントを使用して、依存オブジェクトが存在するデータベースにログインし、クエリ結果のオブジェクトのOIDに基づいて、対応するシステムテーブルのオブジェクト名をクエリします。
- グローバルオブジェクトの依存関係
  データベースフィールドが空の場合、依存オブジェクトはグローバルオブジェクトです。
  - oidが16399オブジェクトを照会します。この結果は、user_to_be_droppedアカウントがtestdb01データベースのACLにあることを示しています。
```
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)
```
  - oidが16409オブジェクトを照会します。結果は、user_to_be_dropedアカウントがtestdb02データベースの所有者であることを示しています。
```
SELECT datname, datdba::regrole, datacl FROM pg_database WHERE oid = 16409;
 datname  |       datdba       | datacl
----------+--------------------+--------
 testdb02 | user_to_be_dropped |
(1 row)
```
  - oidが16416オブジェクトを照会します。結果は、pg_auth_membersテーブルのロールメンバーシップが、user_to_be_dropedアカウントに付与された権限を記録することを示しています。
```
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)
```
- 単一データベース内のオブジェクトの依存関係
  databaseフィールドが空でない場合、依存オブジェクトがデータベースに存在します。
  - oidが2200オブジェクトを照会します。この結果は、user_to_be_droppedアカウントがtestdb01データベースのパブリックスキーマのACLにあることを示しています。
```
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)
```
  - oidが16403設定であるオブジェクトを照会します。結果は、user_to_be_dropedアカウントがtestdb01データベースのtest_nsp.test_tblテーブルの所有者であることを示しています。
```
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)
```

ステップ2: 依存オブジェクトの処理

ビジネス要件に基づいて、次の方法を使用できます。

依存関係タイプがACLの場合、対応する権限を取り消すことができます。
依存関係タイプが所有者の場合、所有権を別のアカウントに譲渡するか、アカウントの依存関係を削除できます。

例：

ACLタイプの依存関係

oidが2200されているオブジェクトを照会し、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;

oidが16399されているオブジェクトを照会し、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オブジェクトを照会します。ロールメンバーシップが関係しており、RDSインスタンスがPostgreSQL 16以降を実行している場合は、user_to_be_dropedアカウントを使用して、user_to_be_droped_2アカウントに対する特権アカウントの権限を取り消す必要があります。この例では、特権アカウントの名前はtestdbuserです。
```
\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;
```

所有者タイプの依存関係

特権アカウントを使用して、依存オブジェクトが存在するデータベースにログインします。

オブジェクトの所有権を別のアカウントに転送します。
oidが16403されているオブジェクトを照会し、testdb01データベースのtest_nsp.test_tblテーブルの所有権を別のアカウントに転送します。この例では、所有権は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;
```

依存オブジェクトの削除Delete dependent objects

testdb02データベースを削除します。
```
DROP DATABASE testdb02;
```

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;

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;

次の応答が返されます。

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

ステップ3: アカウントを削除する

ApsaraDB RDSコンソールで、またはSQLステートメントを実行して、必要なアカウントを削除します。サンプルSQL文:

DROP USER user_to_be_dropped;

アカウントの削除

ApsaraDB RDSコンソールで、またはSQLステートメントを実行して、必要なアカウントを削除します。サンプルSQL文:

DROP USER user_to_be_dropped;