PostgreSQL逻辑复制与RLS多租户治理实践

概述目标：以逻辑复制实现读写分离或数据下游同步，并用RLS隔离多租户访问，确保一致性与安全合规。适用：交易主库→报表库同步、多区域数据复制、SaaS多租户隔离访问。核心与实战开启逻辑复制（主库）：-- pg_hba.conf 与 postgresql.conf 需允许逻辑复制（wal_level=logical）

ALTER SYSTEM SET wal_level = logical;

SELECT pg_reload_conf();

CREATE PUBLICATION pub_orders FOR TABLE public.orders, public.users;

订阅（从库）：CREATE SUBSCRIPTION sub_orders CONNECTION 'host=primary dbname=app user=replicator password=secret' PUBLICATION pub_orders WITH (copy_data = true);

RLS多租户隔离：ALTER TABLE public.orders ENABLE ROW LEVEL SECURITY;

ALTER TABLE public.users  ENABLE ROW LEVEL SECURITY;

-- 会话设定当前租户

SET app.tenant_id = '11111111-1111-1111-1111-111111111111';

-- 策略：仅允许访问自身租户数据

CREATE POLICY orders_tenant_isolation ON public.orders

USING (tenant_id = current_setting('app.tenant_id')::uuid)

WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY users_tenant_isolation ON public.users

USING (tenant_id = current_setting('app.tenant_id')::uuid)

WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

示例验证RLS隔离：SET app.tenant_id = '22222222-2222-2222-2222-222222222222';

SELECT COUNT(*) FROM public.orders; -- 仅返回该租户记录计数

复制状态与延迟：SELECT * FROM pg_stat_replication; -- 主库查看同步状态

SELECT * FROM pg_stat_subscription;     -- 从库查看订阅状态

SELECT * FROM pg_publication_tables;    -- 发布的表列表

DDL变更治理：ALTER PUBLICATION pub_orders ADD TABLE public.invoices;

验证与监控逻辑复制槽与WAL：SELECT * FROM pg_replication_slots;

SELECT pg_size_pretty(pg_current_wal_lsn()::text::pg_lsn - restart_lsn) AS wal_pending FROM pg_replication_slots;

RLS生效性：SELECT relrowsecurity, relforcerowsecurity FROM pg_class WHERE relname='orders';

-- relrowsecurity=t 表示启用；如需强制超类也受RLS，设置 relforcerowsecurity

连接与延迟：SELECT * FROM pg_stat_activity WHERE application_name LIKE 'sub_orders%';

常见误区忽视DDL同步，新增表未加入publication导致数据缺失；需变更时维护publication。RLS依赖会话变量，超级用户或绕过策略可能泄露数据；生产环境需最小权限与避免绕过。未清理复制槽导致WAL积压占满磁盘；需监控并定期清理不再使用的订阅。结语结合逻辑复制与RLS可实现安全的多租户治理与读写解耦，通过系统视图与复制槽监控保障可用与一致性。