如何恢复含需超级权限函数的普通用户所有PostgreSQL数据库？

针对你遇到的问题——需要恢复归普通用户bob所有的数据库，其中部分函数依赖超级权限的LANGUAGE INTERNAL，这里提供几个可行方案：

方案1：先以超级用户恢复，批量修改对象所有权 #

这是最直接的方法，先利用超级用户权限完成所有对象的创建，再将所有权批量转移给bob：

1. 创建目标数据库（超级用户执行）：

   CREATE DATABASE foo OWNER bob;
   

2. 以超级用户身份完成恢复：

   pg_restore -U postgres -d foo '$outfile'
   

3. 执行批量修改所有权的SQL（超级用户在foo数据库中执行）：

   DO $$
   DECLARE
       tbl RECORD;
       view RECORD;
       seq RECORD;
       func RECORD;
   BEGIN
       -- 转移表所有权
       FOR tbl IN SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'public' AND tableowner = 'postgres' LOOP
           EXECUTE 'ALTER TABLE ' || quote_ident(tbl.schemaname) || '.' || quote_ident(tbl.tablename) || ' OWNER TO bob;';
       END LOOP;
       -- 转移视图所有权
       FOR view IN SELECT schemaname, viewname FROM pg_views WHERE schemaname = 'public' AND viewowner = 'postgres' LOOP
           EXECUTE 'ALTER VIEW ' || quote_ident(view.schemaname) || '.' || quote_ident(view.viewname) || ' OWNER TO bob;';
       END LOOP;
       -- 转移序列所有权
       FOR seq IN SELECT schemaname, sequencename FROM pg_sequences WHERE schemaname = 'public' AND sequenceowner = 'postgres' LOOP
           EXECUTE 'ALTER SEQUENCE ' || quote_ident(seq.schemaname) || '.' || quote_ident(seq.sequencename) || ' OWNER TO bob;';
       END LOOP;
       -- 转移函数所有权
       FOR func IN SELECT pronamespace, proname, oid FROM pg_proc WHERE pg_get_userbyid(proowner) = 'postgres' AND pronamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') LOOP
           EXECUTE 'ALTER FUNCTION ' || quote_ident(pg_get_namespace_name(func.pronamespace)) || '.' || quote_ident(func.proname) || '(' || pg_get_function_arguments(func.oid) || ') OWNER TO bob;';
       END LOOP;
   END $$;
   

这个方案无需修改备份文件，适合大多数场景，缺点是需要额外执行所有权转移的脚本。

方案2：编辑备份恢复列表，精细化控制权限切换 #

如果需要更精准地控制恢复过程，可以导出备份的内容列表，手动调整函数的创建逻辑：

1. 导出备份的内容列表：

   pg_restore -l '$outfile' > restore_list.txt
   

2. 编辑restore_list.txt，找到所有依赖LANGUAGE INTERNAL的函数条目，在其前后添加权限切换和所有权修改命令。例如原条目：

   35; 1255 123456 FUNCTION public.my_internal_func() internal
   

   修改为：

   35; 1255 123456 FUNCTION public.my_internal_func() internal
   \set ON_ERROR_STOP on
   SET ROLE postgres;
   CREATE FUNCTION public.my_internal_func() RETURNS void LANGUAGE internal AS $$ ... $$;
   ALTER FUNCTION public.my_internal_func() OWNER TO bob;
   SET ROLE bob;
   

   注意：需要保留原备份中函数的完整CREATE语句，仅添加权限切换和所有权修改部分。
3. 使用修改后的列表文件执行恢复：

   pg_restore -U postgres -d foo -L restore_list.txt '$outfile'
   

这个方案适合函数数量较少、需要精细化控制的场景，缺点是需要手动编辑列表，比较繁琐。

注意事项 #

• 恢复前确保bob对public模式有足够权限，可通过超级用户执行：

  GRANT ALL ON SCHEMA public TO bob;
  

• 如果你尝试过--role=bob参数无效，是因为切换到bob角色后，无法执行需要超级权限的CREATE FUNCTION操作，因此该参数仅适用于无超级权限依赖的场景。

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