無法在 PgSQL AWS RDS 中撤銷權限或刪除使用者

我想asd從數據庫中刪除使用者。所以，我有兩個數據庫：foo並且bar使用者顯然對兩者都有依賴關係。數據庫是從 root 使用者創建的postgres，但裡面的所有表和序列都是由使用者foo_migration和bar_migration分別創建的。

首先，我以postgres使用者身份登錄foo數據庫。當我嘗試刪除使用者時，我得到：

ERROR: role "asd" cannot be dropped because some objects depend on it包含有關來自多個序列、表、bar數據庫和數據庫中的一些對象的權限的詳細資訊postgres。

因此，我嘗試一一清除（同時仍以數據庫postgres使用者身份登錄foo）。我首先嘗試REASSIGN OWNED BY asd TO foo_migration;。這返回ERROR: permission denied，這很奇怪，因為我以postgres使用者身份登錄。無論如何，那我試試

REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM asd;
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM asd;

我得到一堆WARNING: no privileges could be revoked使用者有權訪問的所有序列和表格。我也嘗試DROP OWNED BY asd;了也以ERROR: permission denied.

這一切都不成功，所以我嘗試以使用者身份登錄postgres數據庫postgres並執行相同的步驟。REVOKE命令成功執行而沒有警告，但實際上沒有權限被更改/影響。並且REASSIGN仍然DROP OWNED BY導致ERROR: permission denied.

錯誤消息是裸露的。沒有提供任何提示或細節。

我沒有asd使用者的密碼，所以我無法嘗試使用它登錄並執行任何操作。

您對如何解決此問題有任何想法嗎？我是否需要查看一些顯示所有權的表格並從那裡開始？使用者不是postgres“全能”的管理員使用者嗎？

我在 Postgres RDS 上遇到了類似的問題。這是我用來將對像從一個使用者重新分配給另一個使用者然後刪除使用者的解決方法。對你來說，它會像：

-- logged in as the rds_superuser role you defined when you created your RDS instance
CREATE ROLE change_owner LOGIN;
GRANT asd TO change_owner;
GRANT foo_migration TO change_owner;

-- log out of current user and log in as `change_owner`
REASSIGN OWNED BY asd TO foo_migration;

-- log out of current user and log in as rds_superuser role
DROP USER asd;

我首先使用腳本撤銷所有內容來解決這個問題。

我用python（sqlalchemy）編寫它並保持postgresql明確；遷移到另一種語言應該足夠清楚。

all_schemas = db_conn.execute(
   "SELECT schema_name FROM information_schema.schemata "
   "WHERE schema_name != 'information_schema' "
   "AND schema_name != 'pg_toast' "
   "AND schema_name != 'pg_catalog';"
).fetchall()
for schem in all_schemas:
   if any([s in schem[0] for s in ["pg_temp_", "pg_toast_temp_"]]):
       continue  # skip over these.  This is kinda what '\dn' does in psql.
   db_conn.execute(f"REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA {schem[0]} FROM {user}")
   db_conn.execute(f"REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA {schem[0]} FROM {user}")
   db_conn.execute(f"REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA {schem[0]} FROM {user}")
   db_conn.execute(f"REVOKE USAGE ON SCHEMA {schem[0]} FROM {user}")

# this is response from \d
all_resources = db_conn.execute("""
SELECT n.nspname as "Schema",
 c.relname as "Name",
 CASE c.relkind 
 WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' 
 WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' 
 END as "Type",
 pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
    LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','m','S','f','')
     AND n.nspname <> 'pg_catalog'
     AND n.nspname <> 'information_schema'
     AND n.nspname !~ '^pg_toast'
 AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
""").fetchall()
for resource in all_resources:
   resource_name = resource[1]
   db_conn.execute(f"REVOKE ALL ON {resource_name} FROM {user}")

all_databases = db_conn.execute(
   "SELECT datname FROM pg_database "
   "WHERE NOT datacl ~ 'rdsadmin=CTc/rdsadmin';"
).fetchall()
for db_tup in all_databases:
   db_conn.execute(f"REVOKE ALL PRIVILEGES ON DATABASE {db_tup[0]} FROM {user}")

# And finally (good god this shouldn't be this hard)
db_conn.execute(f"DROP USER IF EXISTS {user}")

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