PostgreSQL撤销权限后仍无法删除read_only角色的排查求助

1. 定位权限授予者，用授予者角色执行撤销 #

从\z orgs.client的输出可见，read_only对该表的权限是由landlord角色授予的（read_only=r/landlord）。PostgreSQL中，只有权限的授予者或超级用户才能撤销对应权限，普通用户执行REVOKE ALL PRIVILEGES不会生效。

解决步骤：

• 若当前用户能切换到landlord角色，执行：

  SET ROLE landlord;
  REVOKE SELECT ON TABLE orgs.client FROM read_only;
  

• 若无法切换，联系landlord角色的持有者执行上述撤销语句。

2. 排查所有关联read_only权限的对象 #

除了orgs.client，可能还有其他对象存在read_only的权限依赖，用以下查询列出所有相关对象：

SELECT n.nspname AS schema_name,
       c.relname AS object_name,
       CASE c.relkind
           WHEN 'r' THEN 'table'
           WHEN 'v' THEN 'view'
           WHEN 'm' THEN 'materialized view'
           WHEN 'S' THEN 'sequence'
           WHEN 'f' THEN 'function'
       END AS object_type,
       pg_get_userbyid(c.relowner) AS owner,
       array_to_string(c.relacl, E'\n') AS privileges
FROM pg_class c
JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE array_to_string(c.relacl, ',') LIKE '%read_only%'
AND c.relkind IN ('r', 'v', 'm', 'S', 'f');

对查询结果中的每个对象，找到对应的权限授予者，执行针对性的撤销操作。

3. 检查默认权限设置 #

若存在针对read_only的默认权限（ALTER DEFAULT PRIVILEGES），也会导致角色无法删除，用以下查询排查：

SELECT n.nspname AS schema_name,
       pg_get_userbyid(d.defaclrole) AS grantor,
       pg_get_userbyid(d.defaclnode) AS grantee,
       array_to_string(d.defaclacl, E'\n') AS default_privileges
FROM pg_default_acl d
JOIN pg_namespace n ON d.defaclnamespace = n.oid
WHERE pg_get_userbyid(d.defaclnode) = 'read_only';

如果查询到结果，需用对应的授予者角色执行撤销：

ALTER DEFAULT PRIVILEGES FOR ROLE <grantor角色名> IN SCHEMA <schema名> 
REVOKE ALL ON TABLES FROM read_only; -- 根据实际对象类型调整，如SEQUENCES、FUNCTIONS等

4. 补充检查角色继承关系（可选） #

确认是否有其他角色继承了read_only，或read_only继承了其他角色，虽然这通常不是删除失败的直接原因，但可作为补充排查：

SELECT r.rolname AS role_name,
       array_to_string(r.rolinherit, ',') AS inherited_roles
FROM pg_roles r
WHERE r.rolname = 'read_only' OR r.rolinherit @> ARRAY(SELECT oid FROM pg_roles WHERE rolname='read_only');

内容的提问来源于stack exchange，提问作者Héctor