由于默认权限，PostgreSQL 删除角色失败

Question

我正在尝试删除角色“xyz”，该角色以前是同名“xyz”的模式所有者。我如下更改了架构所有权，并运行重新分配的所有权以防万一(尽管所有表都是由具有 super 用户权限的不同用户创建的)。所以我运行所有这些:

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 | {}

我错过了什么？任何帮助，将不胜感激!谢谢!!

Answer 1

取自PostgreSQL documentation关于 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.

另一个worthy mention从有关 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 BY 语句并且它起作用了。希望这对您有所帮助!