PostgreSQL 16超级用户无法执行GRANT角色授权的问题解决

问题描述 #

PostgreSQL 16.4环境中，超级用户ingmar的角色定义如下：

CREATE ROLE ingmar WITH
  LOGIN
  SUPERUSER
  INHERIT
  CREATEDB
  CREATEROLE
  NOREPLICATION
  BYPASSRLS
  ENCRYPTED PASSWORD 'md5aaaaaaa790012b7aa47017f124e263d8';

GRANT "240316_owner" TO ingmar;
GRANT eeva_owner TO ingmar WITH ADMIN OPTION;

ingmar创建新角色ingmar.e：

CREATE ROLE "ingmar.e" LOGIN

执行授权语句时抛出权限错误：

GRANT "eeva_owner" TO "ingmar.e"

ERROR: permission denied to grant role "eeva_owner" DETAIL: Only roles with the ADMIN option on role "eeva_owner" may grant this role.; Error while executing the query

环境与特殊情况 #

• PostgreSQL版本：16.4 (Debian 16.4-1.pgdg120+1)，x86_64-pc-linux-gnu平台，gcc 12.2.0编译
• 新创建的PostgreSQL 16非超级用户可正常完成此类授权操作
• ingmar从旧版本迁移而来，旧版本仅拥有创建角色权限，迁移后设置为超级用户仍无法解决问题
• 数据库通过pg_dump/pg_restore迁移，全局对象由pgAdmin导出后恢复
• 旧数据库中ingmar并不具备eeva_owner角色的WITH ADMIN OPTION权限

解决步骤 #

1. 重新授予ADMIN权限 #

迁移过程可能导致权限元数据异常，即使角色定义显示已授权，仍需重新执行。以超级用户（如postgres）身份运行：

GRANT eeva_owner TO ingmar WITH ADMIN OPTION;

完成后让ingmar再次尝试授权操作。

2. 验证权限状态 #

如果重新授权后问题依旧，以ingmar身份执行以下语句检查实际权限：

-- 查看eeva_owner角色信息
SELECT rolname, rolsuper, rolcreaterole FROM pg_roles WHERE rolname = 'eeva_owner';

-- 查看ingmar对eeva_owner的权限记录
SELECT roleid, member, admin_option 
FROM pg_auth_members 
WHERE member = (SELECT oid FROM pg_roles WHERE rolname = 'ingmar') 
  AND roleid = (SELECT oid FROM pg_roles WHERE rolname = 'eeva_owner');

确认pg_auth_members中的admin_option字段值为t，若为f则需再次执行步骤1的授权语句。

3. 临时直接授权（应急方案） #

若超级用户权限判定异常，可直接以超级用户身份为ingmar.e授权：

GRANT eeva_owner TO "ingmar.e";

此方案仅用于应急，后续需排查ingmar的权限异常根源。

4. 修复迁移导致的元数据损坏 #

若上述方法均无效，说明迁移过程中角色权限元数据受损，可尝试：

• 使用pg_dumpall --roles-only导出所有角色定义
• 删除现有相关角色后，重新导入导出的角色定义
• 确保导入过程中使用超级用户权限，避免权限丢失

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