sql - 无法删除 PostgreSQL 角色。错误 : `cannot be dropped because some objects depend on it`

Question

我试图删除 PostgreSQL 用户:

DROP USER ryan;

我收到这个错误:

Error in query:
ERROR: role "ryan" cannot be dropped because some objects depend on it
DETAIL: privileges for database mydatabase

我从这些线程中寻找解决方案:

• PostgreSQL - how to quickly drop a user with existing privileges
• How to drop user in postgres if it has depending objects

还是一样的错误

这发生在我授予用户“ryan”所有权限后:

GRANT ALL PRIVILEGES ON DATABASE mydatabase ON SCHEMA public TO ryan;

Answer 1

DROP USER (或 DROP ROLE，同样的事情)当角色仍然拥有任何东西或对其他对象具有任何授予的特权时无法继续。

使用DROP OWNED 摆脱所有特权(从措辞上看不太明显)。 The manual:

[...] Any privileges granted to the given roles on objects in the currentdatabase and on shared objects (databases, tablespaces) will also be revoked.

因此删除角色的可靠命令顺序是:

REASSIGN OWNED BY ryan TO postgres;  -- or some other trusted role
DROP OWNED BY ryan;

在角色拥有任何东西或拥有任何特权的同一集群的每个数据库中运行这两个命令!
最后:

DROP USER ryan;

• REASSIGN OWNED更改角色当前拥有的所有对象的所有权。
• DROP OWNED然后只撤销特权(不妨碍所有权)。

或者，您可以跳过 REASSIGN OWNED。然后 DROP OWNED 将(也)删除用户拥有的所有对象。 (你确定吗？!)

相关:

• Drop a role with privileges _{(具有为所有相关数据库生成命令的功能)}
• Find objects linked to a PostgreSQL role