Postgres批量修改表所有者后系统表查询显示属主未变更的异常问题

现象核心原因 #

你运行的SELECT format(...)语句仅完成了SQL文本的拼接与结果展示，从头到尾没有真正执行这些生成的属主变更命令，因此pg_tables中查询到的属主信息不会发生任何变化。

背后的数据库运行逻辑 #

• SELECT查询的执行边界
  普通SELECT属于只读查询范畴，整个执行过程只会读取系统表元数据、完成字符串拼接等计算后将结果返回给客户端，不会自动把查询生成的字符串解析为DDL命令送入数据库的执行流程。你看到的ALTER TABLE my_db.my_schema.my_tableA OWNER TO foo这类输出，和执行SELECT 'test'得到的test文本没有本质区别，仅作为结果集的一部分返回，不会对数据库的实际存储状态产生任何修改。
• 原查询的过滤逻辑漏洞
  即便你手动复制所有拼接出的语句逐行执行，当前查询的过滤条件也存在明显缺陷：仅限制了表所属的schema为myschema，没有通过table_owner = 'bar'条件筛选出原本属于bar账号的表，最终的拼接结果里混杂了大量属主已经是foo的表，对这类表执行变更属主的操作也不会产生任何实际效果。
• 权限校验的规则误区
  为bar用户授予数据库层级的全部权限，并不代表bar可以随意修改任意表的属主。PostgreSQL中执行ALTER TABLE ... OWNER TO有严格的权限校验：执行语句的当前账号必须是该表的现有属主，或是拥有超级用户权限、或是目标属主角色的成员，仅靠数据库级别的权限无法跳过这一校验流程。

正确的批量实现方式 #

如果要真正完成bar所属表的属主变更，需要通过动态SQL将拼接好的语句送入执行引擎，可参考如下写法：

DO $$
DECLARE
  alter_stmt text;
BEGIN
  FOR alter_stmt IN
    SELECT format(
      'ALTER TABLE %I.%I OWNER TO %I;',
      table_schema,
      table_name,
      'foo'
    )
    FROM information_schema.tables
    WHERE
      table_schema = 'myschema'
      -- 筛选出属主为bar的表，避免无效操作
      AND table_owner = 'bar'
  LOOP
    EXECUTE alter_stmt;
  END LOOP;
END $$;

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