如何在PostgreSQL中以幂等方式定义行级安全（RLS）策略

你提到的「先删除目标表所有现有策略、再重新创建全部所需策略」是目前最常用的声明式RLS管理方案，完全可以满足和API代码同流程部署的需求。

方案1：单表独立管理（推荐中小项目使用） #

适用单表策略不多、或不同表的策略由不同团队维护的场景：

BEGIN;
-- 清空指定表的所有现有RLS策略
DO $$
DECLARE
    target_table regclass := 'public.patterns'; -- 替换为你的目标表名
    policy_record record;
BEGIN
    FOR policy_record IN SELECT policyname FROM pg_policy WHERE polrelid = target_table
    LOOP
        EXECUTE format('DROP POLICY IF EXISTS %I ON %s', policy_record.policyname, target_table);
    END LOOP;
END $$;

-- 重新创建该表所有需要的策略
CREATE POLICY "everyone can read" ON patterns FOR SELECT USING (auth.role() = 'anon');
CREATE POLICY "authenticated users can edit own patterns" ON patterns FOR UPDATE USING (auth.uid() = owner_id);
-- 其他策略依次补充在此处

COMMIT;

整个逻辑包裹在单个事务中执行，可避免删策略到重建完成之间的空窗期出现安全问题：PostgreSQL中开启RLS的表无匹配策略时默认拒绝所有访问，事务模式下所有变更同时生效，不会出现中间状态。

方案2：全库RLS统一管理（推荐中大型项目使用） #

如果所有RLS策略都统一存放在Git的schema文件中，可以一次性清空全库所有自定义RLS策略，再批量重建所有策略：

BEGIN;
-- 清空全库所有非系统内置的RLS策略
DO $$
DECLARE
    policy_record record;
BEGIN
    FOR policy_record IN SELECT polrelid, policyname FROM pg_policy WHERE polname NOT LIKE 'pg_%'
    LOOP
        EXECUTE format('DROP POLICY IF EXISTS %I ON %s', policy_record.policyname, policy_record.polrelid);
    END LOOP;
END $$;

-- 在这里写入全库所有需要的RLS策略
-- patterns表策略
CREATE POLICY "everyone can read" ON patterns FOR SELECT USING (auth.role() = 'anon');
CREATE POLICY "authenticated users can edit own patterns" ON patterns FOR UPDATE USING (auth.uid() = owner_id);
-- 其他表的策略依次写在下方
-- ...

COMMIT;

其他可选方案 #

如果你更倾向于增量变更的管理方式，可以使用数据库迁移工具（如Flyway、Liquibase、Sqitch）管理RLS变更：每次调整策略时新建一个迁移文件，将旧策略删除和新策略创建的逻辑写入同一个迁移文件，由迁移工具保证变更的顺序和幂等性，适合需要严格追溯每一次策略变更的生产场景。

内容的提问来源于stack exchange，提问作者Evan Summers