PostgreSQL中创建分区时实现权限继承的最佳实践

这确实是PostgreSQL分区表的一个常见小坑——默认情况下，新创建的分区并不会自动继承父表已有的权限配置。结合你的场景，我分享几个经过生产环境验证的最佳实践：

方法1：创建分区后立即同步父表权限 #

最直接的方式就是在触发器的分区创建逻辑后，自动同步父表的权限给新分区。你可以直接针对已知的权限进行复制，或者通过查询系统表动态获取父表的权限规则，做到通用适配。

针对你的场景修改触发器代码 #

比如你的父表给someuser赋予了ALL权限，那可以在创建分区的execute语句后，添加一条权限授予的执行语句：

_partition := 'paritioned_table_' || to_char(to_timestamp(_time),'YYYY_MM');
IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = _partition) THEN
    RAISE NOTICE 'A partition has been created %', _partition;
    select extract(epoch FROM date_trunc('month', to_timestamp(_time))) into _from;
    select extract(epoch FROM date_trunc('month', to_timestamp(_time)) + INTERVAL '1 MONTH') into _to;
    -- 创建分区
    execute 'CREATE TABLE ' || _partition || ' PARTITION OF paritioned_table FOR VALUES FROM (' || _from || ') TO (' || _to || ')';
    -- 同步父表权限给新分区
    execute 'GRANT ALL ON TABLE ' || _partition || ' TO someuser;';
END IF;

通用权限同步（适配所有父表权限） #

如果父表的权限规则比较复杂，你可以通过查询PostgreSQL的系统表pg_class、pg_namespace和pg_roles来动态生成权限授予语句，确保新分区完全继承父表的所有权限：

-- 在创建分区后添加这段逻辑
DECLARE
    _grant_stmt text;
BEGIN
    FOR _grant_stmt IN
        SELECT format('GRANT %s ON TABLE %I.%I TO %I;',
                      array_to_string(acl, ', '),
                      n.nspname,
                      _partition,
                      r.rolname)
        FROM pg_class c
        JOIN pg_namespace n ON c.relnamespace = n.oid
        JOIN pg_roles r ON r.oid = (unnest(c.relacl)).grantee
        WHERE n.nspname = 'public' AND c.relname = 'paritioned_table'
    LOOP
        EXECUTE _grant_stmt;
    END LOOP;
END;

方法2：使用ALTER DEFAULT PRIVILEGES预定义默认权限 #

如果你希望某个模式下所有未来创建的表（包括分区）都自动拥有特定权限，可以通过ALTER DEFAULT PRIVILEGES来设置。这个方法适合批量配置的场景，不需要在触发器里额外写权限逻辑。

设置默认权限的示例 #

执行以下SQL，让public模式下所有新创建的表都自动给someuser赋予ALL权限：

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO someuser;

注意：这个默认权限仅对执行CREATE TABLE语句的用户生效。如果你的触发器是用超级用户（比如postgres）执行的，那要确保是用该用户来设置默认权限；如果触发器是自定义用户执行的，需要切换到对应用户执行这条语句。

方法3：封装分区创建与权限同步为独立函数 #

如果你的分区逻辑比较复杂，或者需要在多个地方复用，建议把“创建分区+同步权限”的逻辑封装成一个独立的PL/pgSQL函数，触发器只需要调用这个函数即可，这样代码更易维护和扩展。

示例函数 #

CREATE OR REPLACE FUNCTION create_monthly_partition(_parent_table text, _time bigint)
RETURNS void AS $$
DECLARE
    _partition text;
    _from bigint;
    _to bigint;
    _grant_stmt text;
BEGIN
    _partition := _parent_table || '_' || to_char(to_timestamp(_time),'YYYY_MM');
    IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname = _partition) THEN
        RAISE NOTICE 'A partition has been created %', _partition;
        select extract(epoch FROM date_trunc('month', to_timestamp(_time))) into _from;
        select extract(epoch FROM date_trunc('month', to_timestamp(_time)) + INTERVAL '1 MONTH') into _to;
        -- 创建分区
        execute format('CREATE TABLE %I PARTITION OF %I FOR VALUES FROM (%L) TO (%L);',
                       _partition, _parent_table, _from, _to);
        -- 同步父表权限
        FOR _grant_stmt IN
            SELECT format('GRANT %s ON TABLE %I.%I TO %I;',
                          array_to_string(acl, ', '),
                          n.nspname,
                          _partition,
                          r.rolname)
            FROM pg_class c
            JOIN pg_namespace n ON c.relnamespace = n.oid
            JOIN pg_roles r ON r.oid = (unnest(c.relacl)).grantee
            WHERE n.nspname = split_part(_parent_table, '.', 1) 
              AND c.relname = split_part(_parent_table, '.', 2)
        LOOP
            EXECUTE _grant_stmt;
        END LOOP;
    END IF;
END;
$$ LANGUAGE plpgsql;

然后在触发器里调用这个函数即可：

PERFORM create_monthly_partition('public.paritioned_table', NEW._time);

方案选择建议 #

• 如果只是简单的固定权限需求，方法1最直接高效；
• 如果需要给模式下所有新表统一配置权限，方法2更省心；
• 如果分区逻辑复杂或需要复用，方法3是最佳实践，代码更易维护。

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