PostgreSQL 逻辑复制安全性深度指南

概述

逻辑复制是 PostgreSQL 的一项强大功能，它允许在不同的数据库实例之间实时同步数据。然而，这一功能涉及复杂的安全机制，需要正确配置权限和访问控制，以确保数据的安全性和一致性。

INFO

逻辑复制的安全性主要涉及两个方面：**发布者（Publisher）的权限控制和订阅者（Subscriber）**的权限管理。理解这些机制对于构建安全的数据复制系统至关重要。

复制连接角色权限要求

基础权限要求

用于复制连接的角色必须满足以下条件之一：

具有 REPLICATION 属性
或者是超级用户

sql

-- 创建具有复制权限的角色
CREATE ROLE replication_user WITH REPLICATION LOGIN PASSWORD 'secure_password';

-- 或者为现有用户授予复制权限
ALTER ROLE existing_user REPLICATION;

行级安全策略处理

当复制角色缺少 SUPERUSER 和 BYPASSRLS 权限时，发布者的行级安全策略将被执行。

WARNING

如果不信任所有表所有者，应在连接字符串中包含 options=-crow_security=off 设置。这样，当表所有者添加行级安全策略时，复制将停止而不是执行该策略。

实际业务场景示例：

假设您有一个多租户应用，其中每个租户只能看到自己的数据：

sql

-- 在发布者上设置行级安全策略
CREATE TABLE tenant_data (
    id SERIAL PRIMARY KEY,
    tenant_id INTEGER,
    sensitive_data TEXT
);

ALTER TABLE tenant_data ENABLE ROW LEVEL SECURITY;

-- 创建行级安全策略
CREATE POLICY tenant_isolation ON tenant_data
    FOR ALL TO public
    USING (tenant_id = current_setting('app.current_tenant_id')::INTEGER);

-- 复制连接字符串示例
host=publisher_host port=5432 dbname=source_db user=repl_user options=-crow_security=off

发布（Publication）权限管理

创建发布的权限要求

操作类型	所需权限	说明
创建发布	数据库 CREATE 权限	基础发布创建权限
添加特定表到发布	表的拥有权	必须是表的所有者
添加模式中所有表	SUPERUSER	需要超级用户权限
创建全表发布	SUPERUSER	自动发布所有表或模式中所有表

完整示例：发布创建与权限配置

sql

-- 1. 创建普通用户并授予CREATE权限
CREATE USER pub_admin PASSWORD 'secure_pass';
GRANT CREATE ON DATABASE company_db TO pub_admin;

-- 2. 切换到pub_admin用户
SET ROLE pub_admin;

-- 3. 创建发布（成功）
CREATE PUBLICATION employee_pub;

-- 4. 尝试添加表到发布
CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(50)
);

-- 添加自己拥有的表（成功）
ALTER PUBLICATION employee_pub ADD TABLE employees;

-- 尝试添加其他用户的表（失败）
-- ALTER PUBLICATION employee_pub ADD TABLE other_user.products; -- 权限不足错误

发布访问控制的局限性

DANGER

当前 PostgreSQL 的发布没有任何权限控制机制。任何能够连接的订阅都可以访问任何发布。这意味着：

相同数据库中的其他发布可能暴露敏感信息
行过滤器和列列表并不能真正隐藏数据
需要在数据库级别进行访问控制

安全风险示例：

sql

-- 敏感数据发布
CREATE PUBLICATION sensitive_pub FOR TABLE employee_salaries;

-- 公开数据发布（在同一数据库中）
CREATE PUBLICATION public_pub FOR TABLE employee_info;

-- 风险：恶意订阅者可以同时访问两个发布
-- 即使 sensitive_pub 有行过滤器，public_pub 可能暴露相同的敏感信息

订阅（Subscription）权限管理

创建订阅的权限

要创建订阅，用户需要：

拥有 pg_create_subscription 角色的权限
具有数据库上的 CREATE 权限

sql

-- 授予用户创建订阅的权限
GRANT pg_create_subscription TO subscription_admin;
GRANT CREATE ON DATABASE target_db TO subscription_admin;

-- 创建订阅
CREATE SUBSCRIPTION emp_sub
CONNECTION 'host=source_host port=5432 dbname=source_db user=repl_user'
PUBLICATION employee_pub;

订阅执行时的权限切换

订阅应用进程的权限模型比较复杂：

默认权限模式示例：

sql

-- 订阅所有者需要能够SET ROLE为每个表所有者
CREATE USER sub_owner;
CREATE USER table_owner_1;
CREATE USER table_owner_2;

-- 订阅所有者需要能够切换到表所有者角色
GRANT table_owner_1 TO sub_owner;
GRANT table_owner_2 TO sub_owner;

-- 创建订阅
CREATE SUBSCRIPTION multi_owner_sub
CONNECTION 'host=source dbname=src user=repl_user'
PUBLICATION multi_table_pub
WITH (slot_name = 'multi_owner_slot');

run_as_owner 模式的安全考虑

WARNING

使用 run_as_owner = true 时存在重大安全风险：

sql

-- 启用run_as_owner模式
ALTER SUBSCRIPTION risky_sub SET (run_as_owner = true);

安全风险场景：

sql

-- 恶意表所有者可以创建触发器
CREATE TABLE user_owned_table (
    id INTEGER,
    data TEXT
);

-- 恶意触发器，在复制时执行任意代码
CREATE OR REPLACE FUNCTION malicious_function()
RETURNS TRIGGER AS $$
BEGIN
    -- 恶意代码：访问敏感数据或执行危险操作
    PERFORM pg_read_file('/etc/passwd');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER malicious_trigger
    BEFORE INSERT ON user_owned_table
    FOR EACH ROW
    EXECUTE FUNCTION malicious_function();

DANGER

在 run_as_owner = true 模式下，恶意触发器将以订阅所有者的权限执行，可能导致严重的安全漏洞。

权限检查时机

发布者权限检查

在发布者上，权限检查的时机：

订阅者权限检查

在订阅者上，权限检查更加频繁：

并发所有权变更场景：

sql

-- 会话1：正在应用复制事务
BEGIN;
-- 复制操作正在进行...

-- 会话2：同时更改订阅所有权
ALTER SUBSCRIPTION test_sub OWNER TO new_owner;

-- 结果：当前事务继续以旧所有者权限执行
-- 下一个事务将以新所有者权限执行

安全配置最佳实践

1. 复制用户安全配置

sql

-- 创建专用复制用户
CREATE USER replication_service WITH 
    REPLICATION 
    LOGIN 
    PASSWORD 'complex_secure_password_2024!'
    VALID UNTIL '2025-12-31';

-- 限制连接来源
-- 在 pg_hba.conf 中配置
-- host replication replication_service 192.168.1.0/24 scram-sha-256

2. 发布权限最小化

sql

-- 创建专门的发布管理用户
CREATE USER pub_manager;
GRANT CREATE ON DATABASE production_db TO pub_manager;

-- 只授予必要表的所有权或访问权限
GRANT SELECT ON critical_table TO pub_manager;

-- 避免使用FOR ALL TABLES
-- CREATE PUBLICATION bad_pub FOR ALL TABLES; -- 避免这样做
CREATE PUBLICATION good_pub FOR TABLE specific_table1, specific_table2;

3. 订阅安全配置

sql

-- 创建专门的订阅用户
CREATE USER sub_manager;
GRANT pg_create_subscription TO sub_manager;
GRANT CREATE ON DATABASE replica_db TO sub_manager;

-- 配置表权限（避免使用run_as_owner = true）
GRANT SELECT, INSERT, UPDATE, DELETE ON target_table TO table_owner;
GRANT table_owner TO sub_manager; -- 允许角色切换

4. 监控和审计

sql

-- 监控复制状态
SELECT 
    slot_name,
    plugin,
    slot_type,
    datoid,
    active,
    active_pid,
    catalog_xmin,
    restart_lsn
FROM pg_replication_slots;

-- 监控订阅状态
SELECT 
    subname,
    pid,
    received_lsn,
    last_msg_send_time,
    last_msg_receipt_time,
    latest_end_lsn,
    latest_end_time
FROM pg_stat_subscription;

-- 审计发布访问
SELECT 
    pubname,
    puballtables,
    pubinsert,
    pubupdate,
    pubdelete,
    pubtruncate
FROM pg_publication;

故障排除常见安全问题

权限不足错误

sql

-- 错误：权限被拒绝
ERROR: permission denied for table sensitive_data

-- 解决方案：检查并授予必要权限
GRANT SELECT ON sensitive_data TO replication_user;

复制槽访问错误

sql

-- 错误：复制槽不存在或无权限访问
ERROR: replication slot "my_slot" does not exist

-- 解决方案：检查槽的所有权和权限
SELECT slot_name, active, active_pid FROM pg_replication_slots;

行级安全策略冲突

sql

-- 错误：行级安全策略阻止复制
ERROR: new row violates row-level security policy

-- 解决方案：调整连接参数或禁用RLS
-- 连接字符串添加：options=-crow_security=off

总结

PostgreSQL 逻辑复制的安全性是一个多层次的体系，涉及：

连接级安全：复制用户权限和网络访问控制
发布级安全：表所有权和发布权限管理
订阅级安全：角色切换和权限检查机制
运行时安全：权限检查时机和并发控制

正确理解和配置这些安全机制，对于构建安全可靠的数据复制系统至关重要。在生产环境中，应该采用最小权限原则，避免使用超级用户进行复制操作，并定期审计复制权限配置。

PostgreSQL 逻辑复制安全性深度指南 ​

概述 ​

复制连接角色权限要求 ​

基础权限要求 ​

行级安全策略处理 ​

发布（Publication）权限管理 ​

创建发布的权限要求 ​

发布访问控制的局限性 ​

订阅（Subscription）权限管理 ​

创建订阅的权限 ​

订阅执行时的权限切换 ​

run_as_owner 模式的安全考虑 ​

权限检查时机 ​

发布者权限检查 ​

订阅者权限检查 ​

安全配置最佳实践 ​

1. 复制用户安全配置 ​

2. 发布权限最小化 ​

3. 订阅安全配置 ​

4. 监控和审计 ​

故障排除常见安全问题 ​

权限不足错误 ​

复制槽访问错误 ​

行级安全策略冲突 ​

总结 ​