无法删除 PostgreSQL 角色。错误：`无法删除，因为某些对象依赖它`
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/51256454/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Cannot drop PostgreSQL role. Error: `cannot be dropped because some objects depend on it`
提问by notalentgeek
I was trying to delete PostgreSQL user:
我试图删除 PostgreSQL 用户：
DROP USER ryan;
I received this error:
我收到此错误：
Error in query: ERROR: role "ryan" cannot be dropped because some objects depend on it DETAIL: privileges for database mydatabase
Error in query: ERROR: role "ryan" cannot be dropped because some objects depend on it DETAIL: privileges for database mydatabase
I looked for a solution from these threads:
我从这些线程中寻找解决方案：
- PostgreSQL - how to quickly drop a user with existing privileges
- How to drop user in postgres if it has depending objects
Still have the same error.
仍然有同样的错误。
This happens after I grant all permission to user "ryan" with:
在我向用户“ryan”授予所有权限后，会发生这种情况：
GRANT ALL PRIVILEGES ON DATABASE mydatabase ON SCHEMA public TO ryan;
回答by Erwin Brandstetter
Get rid of all privileges with DROP OWNED(which isn't too obvious from the wording). The manual:
摆脱所有特权 with （从措辞上看不太明显）。手册：DROP OWNED
[...] Any privileges granted to the given roles on objects in the current database and on shared objects (databases, tablespaces) will also be revoked.
[...] 在当前数据库中的对象和共享对象（数据库、表空间）上授予给定角色的任何权限也将被撤销。
So the reliable sequence of commands to drop a role is:
因此，删除角色的可靠命令序列是：
REASSIGN OWNED BY ryan TO postgres; -- or some other trusted role
DROP OWNED BY ryan;
-- repeat in ALL databases where the role owns anything or has any privileges!
DROP USER ryan;
Related:
有关的：
- Drop a role with privileges(with a function to generate commands for all relevant DBs)
- Find objects linked to a PostgreSQL role
- 删除具有特权的角色（具有为所有相关 DB 生成命令的功能）
- 查找链接到 PostgreSQL 角色的对象
回答by Samuel Anyaele
What worked for me was 1) Connecting to the database
对我有用的是 1) 连接到数据库
\c mydatabase
2) Reassigning Ownership
2) 重新分配所有权
REASSIGN OWNED BY ryan TO <newuser>;
Or/and just deleting the object
或/和只是删除对象
DROP OWNED BY ryan;
3) Executing REVOKE PRIVILEGES
3) 执行 REVOKE PRIVILEGES
REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM ryan;
REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM ryan;
REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM ryan;
4) Dropping the user
4）删除用户
DROP USER ryan;
PS: You might not need to execute both Step 2 and 3, just one of the two steps might be usually enough.
PS：您可能不需要同时执行第 2 步和第 3 步，通常只需执行这两个步骤之一即可。
回答by A Hettinger
Granting all permissions to ryan won't help, odds are he's the DB owner. You're going to need to make someone else own it:
向 ryan 授予所有权限无济于事，他很可能是数据库所有者。您将需要让其他人拥有它：
REASSIGN OWNED BY ryan TO postgres;
unless you don't need mydatabase anymore, if that's the case just drop the database (MAKE SURE YOU REALLY DON'T NEED IT BEFORE DOING THIS):
除非您不再需要 mydatabase，否则只需删除数据库（在执行此操作之前确保您真的不需要它）：
DROP DATABASE mydatabase;
