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可实现安全的多租户治理与读写解耦，通过系统视图与复制槽监控保障可用与一致性。