postgressql 如何修改模式的所有表的所有者

在 PostgreSQL 中，如果需要批量修改某个模式（Schema）下所有表的所有权（即修改所有者），可以通过以下步骤实现：

方法一：使用 DO 语句生成动态 SQL

以下脚本将生成并执行批量修改指定模式中所有表所有者的 SQL 命令：

-- 修改 schema_name 替换为模式名，new_owner 替换为目标所有者
DO $$
DECLARE
    tbl_name TEXT;
BEGIN
    FOR tbl_name IN
        SELECT tablename
        FROM pg_tables
        WHERE schemaname = 'schema_name'  -- 指定模式名
    LOOP
        EXECUTE format('ALTER TABLE %I.%I OWNER TO %I', 'schema_name', tbl_name, 'new_owner');
    END LOOP;
END $$;

方法二：直接生成并执行 SQL 命令

首先生成所有修改命令的 SQL 语句，再复制执行：

-- 生成修改表所有权的 SQL 列表
SELECT format('ALTER TABLE %I.%I OWNER TO %I;', schemaname, tablename, 'new_owner')
FROM pg_tables
WHERE schemaname = 'schema_name';  -- 指定模式名

输出示例：

ALTER TABLE "public"."users" OWNER TO "new_owner";
ALTER TABLE "public"."orders" OWNER TO "new_owner";
-- 复制这些语句并在事务中执行

方法三：修改整个模式及其所有对象的所有者

除了表，一并修改模式及其下属的视图、序列等其他对象：

-- 1. 修改模式本身的所有者
ALTER SCHEMA schema_name OWNER TO new_owner;
-- 2. 修改模式中所有表的所有者
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN
        SELECT 'ALTER TABLE "' || schemaname || '"."' || tablename || '" OWNER TO new_owner;' AS sql
        FROM pg_tables
        WHERE schemaname = 'schema_name'
        UNION ALL
        SELECT 'ALTER SEQUENCE "' || schemaname || '"."' || sequencename || '" OWNER TO new_owner;' AS sql
        FROM pg_sequences
        WHERE schemaname = 'schema_name'
        UNION ALL
        SELECT 'ALTER VIEW "' || schemaname || '"."' || viewname || '" OWNER TO new_owner;' AS sql
        FROM pg_views
        WHERE schemaname = 'schema_name'
    LOOP
        EXECUTE r.sql;
    END LOOP;
END $$;

注意事项

1. 权限要求：

   • 执行操作的用户必须是 当前表的所有者 或 超级用户。

2. 特殊字符处理：

   • 如果表名或用户包含大写或特殊字符，需使用双引号包裹（如 "New_Owner"）。

3. 安全性：

   • 在操作前先备份数据，或在测试环境中验证脚本。

4. 其他对象：

   • 除了表，视图、序列等也需要单独处理（见方法三）。 

示例验证

检查修改结果：

-- 查询某个表的所有者是否已变更
SELECT schemaname, tablename, tableowner
FROM pg_tables
WHERE schemaname = 'schema_name';

输出示例：

schemaname | tablename | tableowner
------------+-----------+------------
 public     | users     | new_owner
 public     | orders    | new_owner

通过上述任一方法，即可批量修改一个模式下的所有表及其相关对象的所有者。