RDS PostgreSQL刪除帳號失敗 - ApsaraDB RDS

問題描述

刪除RDS PostgreSQL帳號時，報錯：

在RDS PostgreSQL控制台刪除帳號時，出現錯誤提示。其中：
- 報錯資訊：資料庫物件依賴該操作的帳號，請先解除依賴後，再操作。
- 錯誤碼：AccountActionForbidden。
使用SQL命令刪除在帳號時報錯：ERROR: role "<username>" cannot be dropped because some objects depend on it

問題原因

在RDS PostgreSQL中，如果存在資料庫物件依賴於即將被刪除的帳號，將導致刪除操作失敗。

問題重現樣本

例如，存在user_to_be_dropped帳號，並使用該帳號建立了對象。樣本SQL如下：

使用高許可權帳號，建立測試資料庫testdb01。
```
CREATE DATABASE testdb01;
```

使用高許可權帳號登入testdb01，建立測試帳號user_to_be_dropped，並賦予測試帳號對應的許可權。

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_dropped登入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_dropped登入testdb02，建立對象和新測試帳號user_to_be_dropped_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_dropped時，由於存在依賴對象，所以刪除操作會出現報錯。

RDS PostgreSQL控制台：

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命令刪除目標帳號，根據報錯中提示，先將待刪除帳號擁有的對象批量轉移給其他使用者，然後再撤銷其所有許可權。
精細處理（細粒度、每個許可權和對象透明可控）：尋找依賴目標帳號的對象，並將其逐一刪除。

批量處理

分析報錯資訊，確認目標帳號的擁有的對象及許可權。以問題重現樣本為例：
```
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
```
從報錯資訊中可以確認，目標帳號user_to_be_dropped在testdb01和testdb02中存在依賴對象。其中：
- 在資料庫testdb01中，目標帳號具備對某些對象（共4個對象）的許可權（privileges）。
- 目標帳號是資料庫testdb02的擁有者（owner）。
使用高許可權帳號（本文以testdbuser為例），分別登入報錯資訊中涉及的資料庫，將目標帳號擁有的對象批量轉移給其他使用者（本文以高許可權帳號testdbuser為例），然後刪除目標帳號擁有的所有對象的許可權。
- 登入資料庫testdb01，刪除目標帳號user_to_be_dropped擁有的對象。
```
DROP OWNED BY user_to_be_dropped;
```
- 登入資料庫testdb02，將所有權轉移給其他帳號，並刪除目標帳號user_to_be_dropped擁有的對象。
```
--轉移所有權
REASSIGN OWNED BY user_to_be_dropped TO testdbuser;
--刪除目標帳號擁有的對象
DROP OWNED BY user_to_be_dropped;
```

精細處理

步驟一：尋找依賴對象

系統資料表pg_shdepend中記錄了單個資料庫內部的對象對全域對象的依賴，可以用於查詢哪些對象依賴了目標帳號。例如，查詢哪些對象依賴帳號 user_to_be_dropped。

使用高許可權帳號登入資料庫執行個體，查詢與帳號 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);

查詢結果為：

 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

其中各個欄位的含義如下。

欄位	含義
`database`	指明依賴對象所在的庫，為空白時表示全域對象的依賴。
`classname`	指明系統資料表名稱。
`oid`	指明依賴對象的oid。
`case`	指明依賴類型，常見的類型包括： Owner：被刪除帳號是該對象的所有者。 ACL（Access Control List）：被刪除帳號在Access Control List中。

使用高許可權帳號登入到對應資料庫中，在對應的系統資料表中查詢上一步獲得的oid所對應的對象名稱，例如。

全域對象的依賴

database欄位為空白，表示全域對象的依賴。

oid=16399，testdb01的ACL中有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)

oid=16409，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)

oid=16416，pg_auth_members中的角色從屬關係，記錄了授予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)

單庫內對象的依賴

database欄位不為空白，表示該庫下存在依賴對象。

oid=2200，testdb01下public schema的ACL中有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)

oid=16403，testdb01下的表test_nsp.test_tbl的所有者為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)

步驟二：處理依賴對象

您可以根據實際需求：

如果是ACL類型的依賴，可以收回對應的許可權。
如果是Owner類型的依賴，可以將其所有權轉移給其他帳號，也可以刪除對應的依賴。

例如：

ACL類型的依賴

oid=2200，收回testdb01下public schema的許可權。

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_dropped帳號，刪除 user_to_be_dropped_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;
```

Owner類型依賴所有權轉移給其他帳號。

使用高許可權賬單登入到對應資料庫。

將對象的所有權轉移給其他帳號。

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;

刪除依賴對象。

刪除測試資料庫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)

步驟三：刪除目標帳號

在RDS PostgreSQL控制台或通過SQL命令刪除目標帳號。SQL樣本如下。

DROP USER user_to_be_dropped;

刪除目標帳號

在RDS PostgreSQL控制台或通過SQL命令刪除目標帳號。SQL樣本如下。

DROP USER user_to_be_dropped;