Azure弹性PostgreSQL执行REASSIGN OWNED因pg_audit触发权限错误

原因分析 #

REASSIGN OWNED BY posadmn001 TO wsalesadmin命令会尝试修改源用户posadmn001拥有的所有对象的所有者，其中包括pg_audit扩展创建的事件触发器pgaudit_ddl_command_end。PostgreSQL规则明确要求事件触发器的所有者必须是超级用户，但Azure PostgreSQL弹性服务器提供的管理员账号并非真正的超级用户（仅拥有受限高权限），因此无法修改该触发器的所有者，导致整个命令执行失败，进而中断了其他表、函数等对象的所有者变更操作。

操作是否有误？ #

备份恢复流程本身没问题，但忽略了pg_audit扩展对象在Azure PaaS环境下的权限限制：直接使用REASSIGN OWNED会触发系统级权限校验，导致整个命令终止，无法完成普通对象的所有者变更。

解决方案 #

方案1：单独变更非pg_audit对象的所有者 #

通过查询系统表，生成仅针对普通业务对象（表、函数、视图、序列等）的所有者变更语句，绕过pg_audit相关对象：

-- 生成表的所有者变更语句
SELECT 'ALTER TABLE ' || schemaname || '.' || tablename || ' OWNER TO wsalesadmin;'
FROM pg_tables
WHERE tableowner = 'posadmn001'
  AND schemaname NOT IN ('pg_catalog', 'information_schema', 'pgaudit');

-- 生成函数的所有者变更语句
SELECT 'ALTER FUNCTION ' || nspname || '.' || proname || '(' || pg_get_function_identity_arguments(p.oid) || ') OWNER TO wsalesadmin;'
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE pg_get_userbyid(p.proowner) = 'posadmn001'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema', 'pgaudit');

-- 生成序列的所有者变更语句
SELECT 'ALTER SEQUENCE ' || schemaname || '.' || sequencename || ' OWNER TO wsalesadmin;'
FROM pg_sequences
WHERE sequenceowner = 'posadmn001'
  AND schemaname NOT IN ('pg_catalog', 'information_schema', 'pgaudit');

-- 生成视图的所有者变更语句
SELECT 'ALTER VIEW ' || schemaname || '.' || viewname || ' OWNER TO wsalesadmin;'
FROM pg_views
WHERE viewowner = 'posadmn001'
  AND schemaname NOT IN ('pg_catalog', 'information_schema', 'pgaudit');

将上述查询结果导出为SQL脚本，在psql中执行即可完成普通对象的所有者变更。pg_audit的事件触发器无需修改所有者，不影响其正常功能。

方案2：迁移前禁用pg_audit，恢复后重新安装 #

如果希望后续能正常使用REASSIGN OWNED命令，可按以下步骤操作：

1. 源服务器操作：若源服务器是自建PostgreSQL（有超级用户权限），先卸载pg_audit扩展：

   DROP EXTENSION pgaudit;
   

   之后再执行pg_dump备份数据。
2. 新服务器操作：用pg_restore恢复备份后，再通过Azure管理员账号安装pg_audit：

   CREATE EXTENSION pgaudit;
   

   此时pg_audit的对象会以Azure管理员账号为所有者，后续执行REASSIGN OWNED时不会触发权限错误。

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