PostgreSQL无法删除拥有默认权限的用户问题求助

问题原因 #

你执行的ALTER DEFAULT PRIVILEGES FOR ROLE xyz REVOKE ALL ON FUNCTIONS FROM xyz;仅作用于当前会话的默认schema（通常是public），如果用户xyz在其他schema也配置了函数默认权限，这条命令无法覆盖所有场景，导致权限残留进而无法删除用户。

解决步骤 #

1. 查询全量默认权限
   执行以下SQL，找出xyz在所有schema中配置的默认权限：

   SELECT nspname AS schema_name, defaclobjtype AS object_type, defaclacl AS privileges
   FROM pg_default_acl
   JOIN pg_namespace ON pg_default_acl.defaclnamespace = pg_namespace.oid
   WHERE defaclrole = (SELECT oid FROM pg_roles WHERE rolname = 'xyz');
   

   结果会明确列出xyz拥有默认权限的schema、对象类型及具体权限内容。

2. 逐个schema撤销权限
   针对查询结果中的每个schema，执行对应撤销命令，例如针对publicschema：

   ALTER DEFAULT PRIVILEGES FOR ROLE xyz IN SCHEMA public REVOKE ALL ON FUNCTIONS FROM xyz;
   

   若存在多个schema，替换public为对应的schema名称，逐一执行。

3. 清理其他依赖对象
   执行以下SQL，排查是否存在其他依赖xyz的数据库对象：

   SELECT
       pg_class.relname AS object_name,
       pg_class.relkind AS object_type,
       pg_namespace.nspname AS schema_name
   FROM pg_depend
   JOIN pg_class ON pg_depend.objid = pg_class.oid
   JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
   WHERE pg_depend.refobjid = (SELECT oid FROM pg_roles WHERE rolname = 'xyz');
   

   对于查出的对象，可选择转移所有权给其他用户（如postgres）：

   -- 转移表所有权
   ALTER TABLE schema_name.table_name OWNER TO postgres;
   -- 转移函数所有权
   ALTER FUNCTION schema_name.function_name(param_types) OWNER TO postgres;
   

   或直接删除不再需要的对象。

4. 执行用户删除
   完成所有清理操作后，执行删除命令：

   DROP ROLE xyz;
   

注意事项 #

• 默认权限是数据库级别的，若实例存在多个数据库，需连接每个数据库重复上述步骤。
• 操作时需确保拥有超级用户或足够的权限。

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