PostgreSQL放置角色由于默认特权而失败 [英] PostgreSQL drop role fails because of default privileges

问题描述

我正在尝试删除一个角色 xyz，该角色以前是该架构的所有者，且名称相同，名称为 xyz。我如下更改了架构所有权，并运行了重新分配的所有权，以防万一（尽管所有表都是由具有超级用户权限的其他用户创建的）。因此，我运行所有这些命令：

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:

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;

仍然出现错误：

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

也供您参考：

postgres=# \du rsi
List of roles
Role name |   Attributes   | Member of   
-----------+----------------+-----------
rsi       | No inheritance | {}

我想念什么？任何帮助，将不胜感激！谢谢!!

What am I missing? Any help would be appreciated! Thanks!!

推荐答案

来自更改默认特权上的static / sql-alterdefaultprivileges.html> PostgreSQL文档，注释部分：

Taken from the PostgreSQL documentation on ALTER DEFAULT PRIVILEGES, Notes section:

如果要删除已更改其默认特权的角色，则必须撤消其默认特权的更改，或使用DROP OWNED BY摆脱该默认特权的条目。角色。

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 的-owned.html>值得一提的也是

Another worthy mention from the documentation regarding DROP OWNED BY in this case is also that

由于DROP OWNED仅影响当前数据库中的对象，因此通常需要在每个数据库中执行此命令，该数据库包含要删除的角色所拥有的对象。

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.

因此，您的里程可能会有所不同，这意味着您可能必须在更多的数据库中发出该语句。

Therefore, your mileage may vary, meaning that you may have to issue the statement in more DBs.

已经收到了与问题中提到的相同的消息，因此我尝试了 DROP OWNED BY 语句即可。希望这会有所帮助！

Having received the same messages as mentioned in the question, I've tried out the DROP OWNED BY statement and it worked. Hope this helps!

这篇关于PostgreSQL放置角色由于默认特权而失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！