刪除角色 postgresql

我剛剛繼承了一個 AWS RDS PostgreSQL 12.5 實例（因此，沒有超級使用者訪問權限），我需要進行一些清理並刪除一堆組/角色/使用者。我正在使用 AWS 提供的使用者“master”在唯一可用的數據庫上執行以下命令（除了postgres db）

嘗試時：

DROP ROLE xxx

我得到：

錯誤：無法刪除角色“xxx”，因為某些對象依賴於它

詳細資訊：數據庫 xxx 中有 N 個對象

經過大量Google搜尋後，我最終得到：

REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA some_schema FROM "xxx";
REVOKE ALL PRIVILEGES ON SCHEMA some_schema FROM "xxx";
REVOKE USAGE ON SCHEMA some_schema FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON SEQUENCES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON TABLES FROM "xxx";
ALTER DEFAULT PRIVILEGES IN SCHEMA some_schema REVOKE ALL ON FUNCTIONS FROM "xxx";
REVOKE ALL PRIVILEGES ON DATABASE xxx FROM "xxx";

但是， DROP 仍然失敗：

錯誤：無法刪除角色“xxx”，因為某些對象依賴於它詳細資訊：在架構 some_schema SQL 狀態

中屬於角色 master 的新類型的預設權限的權限：2BP01

嘗試使用（取自一些stackoverflow問題）檢查依賴關係，但查詢一無所獲：

SELECT *
FROM
   --r = ordinary table, i = index, S = sequence, v = view, m = materialized view, c = composite type, t = TOAST table, f = foreign table
   (SELECT N.NSPNAME AS SCHEMA_NAME,
           C.RELNAME AS REL_NAME,
           C.RELKIND AS REL_KIND,
           PG_GET_USERBYID(C.RELOWNER) AS OWNER_NAME
       FROM PG_CLASS C
       JOIN PG_NAMESPACE N ON N.OID = C.RELNAMESPACE
   UNION ALL
   -- functions (or procedures)
   SELECT N.NSPNAME AS SCHEMA_NAME,
           P.PRONAME,
           'p',
           PG_GET_USERBYID(P.PROOWNER)
       FROM PG_PROC P
       JOIN PG_NAMESPACE N ON N.OID = P.PRONAMESPACE) AS FOO
WHERE OWNER_NAME = 'xxx'

然後嘗試使用（再次出現空）檢查權限：

SELECT
   grantee AS user,
   CONCAT(table_schema, '.', table_name) AS table, 
   CASE 
       WHEN COUNT(privilege_type) = 7 THEN 'ALL'
       ELSE ARRAY_TO_STRING(ARRAY_AGG(privilege_type), ', ')
   END AS grants
FROM information_schema.role_table_grants
GROUP BY table_name, table_schema, grantee
HAVING grantee = 'xxx'

這是我第一次使用 PostgreSQL，來自 SQL Server。

您必須撤銷預設權限：

ALTER DEFAULT PRIVILEGES FOR ROLE master IN SCHEMA some_schema
  REVOKE ALL ON TYPES FROM xxx;

引用自：https://dba.stackexchange.com/questions/292406