PostgreSQL无法删除角色求助：依赖对象导致删除失败

问题场景 #

切换到test_database后尝试删除角色user1：

postgres=# \c test_database;
drop role user1;

触发如下错误：

ERROR: 无法删除角色"user1"，因为存在依赖对象
DETAIL: schema public的权限
角色user1在schema public中拥有新序列的默认权限所有权
角色user1在schema public中拥有新关系的默认权限所有权

查看依赖的默认权限 #

通过以下查询可以确认user1持有的默认权限：

SELECT
    pg_get_userbyid(defaclrole) AS owner,
    nspname AS schema,
    defaclobjtype AS type,
    defaclacl AS privileges
FROM pg_default_acl a
LEFT JOIN pg_namespace b ON a.defaclnamespace = b.oid
WHERE pg_get_userbyid(defaclrole) = 'user1';

查询返回结果：

owner    | schema | type |      privileges
------------+--------+------+-----------------------
 user1    | public | S    | {backup=r/confluence}
 user1    | public | r    | {backup=r/confluence}

解决步骤 #

要删除角色，需先清理这些依赖的默认权限：

• 撤销user1对public schema的所有权限：

REVOKE ALL PRIVILEGES ON SCHEMA public FROM user1;

• 删除user1拥有的默认权限条目（精准匹配查询结果）：

DELETE FROM pg_default_acl
WHERE defaclrole = (SELECT oid FROM pg_roles WHERE rolname = 'user1')
  AND defaclnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public')
  AND defaclobjtype IN ('S', 'r');

• 再次执行删除角色命令：

DROP ROLE user1;

内容的提问来源于stack exchange，提问作者BioRod