由于默认权限，PostgreSQL 删除角色失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9840955/
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
PostgreSQL drop role fails because of default privileges
提问by Ruxandra Palmtag
I am trying to drop a role 'xyz' that was previously the owner of the schema with the same name 'xyz'. I altered the schema ownership as below, and run reassigned ownership just in case (although all tables were created by a different user with superuser power). So I run all these:
我正在尝试删除一个角色“xyz”，该角色以前是具有相同名称“xyz”的架构所有者。我更改了架构所有权，如下所示，并运行重新分配的所有权以防万一（尽管所有表都是由具有超级用户权限的不同用户创建的）。所以我运行所有这些：
alter schema xyz owner to postgres;
reassign owned by xyz to postgres;
alter default privileges in schema seeds revoke all on tables from xyz cascade;
alter default privileges in schema seeds revoke all on sequences from xyz cascade;
alter default privileges in schema seeds revoke all on functions from xyz cascade;
And still getting the error:
并且仍然收到错误：
drop role xyz;
ERROR: role "xyz" cannot be dropped because some objects depend on it
DETAIL: owner of default privileges on new relations belonging to role xyz in schema xyz
Also FYI:
也仅供参考：
postgres=# \du rsi
List of roles
Role name | Attributes | Member of
-----------+----------------+-----------
rsi | No inheritance | {}
What am I missing? Any help would be appreciated! Thanks!!
我错过了什么？任何帮助，将不胜感激！谢谢！！
回答by Dr1Ku
Taken from the PostgreSQL documentationon ALTER DEFAULT PRIVILEGES, Notes section:
摘自PostgreSQL文档上ALTER DEFAULT PRIVILEGES，说明部分：
If you wish to drop a role for which the default privileges have been altered, it is necessary to reverse the changes in its default privileges or use DROP OWNED BY to get rid of the default privileges entry for the role.
如果您希望删除已更改默认权限的角色，则需要撤销其默认权限中的更改或使用 DROP OWNED BY 删除角色的默认权限条目。
Another worthy mentionfrom the documentation regarding DROP OWNED BYin this case is also that
在这种情况下，文档中另一个值得一提的DROP OWNED BY是
Because DROP OWNED only affects the objects in the current database, it is usually necessary to execute this command in each database that contains objects owned by a role that is to be removed.
因为DROP OWNED 只影响当前数据库中的对象，所以通常需要在每个包含要删除的角色拥有的对象的数据库中执行此命令。
Therefore, your mileage may vary, meaning that you may have to issue the statement in more DBs.
因此，您的里程可能会有所不同，这意味着您可能需要在更多 DB 中发出语句。
Having received the same messages as mentioned in the question, I've tried out the DROP OWNED BYstatement and it worked. Hope this helps!
收到与问题中提到的相同的消息后，我尝试了该DROP OWNED BY语句并且它起作用了。希望这可以帮助！
回答by Shrinivas
First run command :
首先运行命令：
DROP OWNED BY xyz;
then:
然后：
DROP ROLE xyz;
Read PostgreSQL Documentation regarding Drop Owned By.
阅读有关Drop Ownered By 的PostgreSQL 文档。
