如何从PostgreSQL/Supabase导出行级安全（RLS）策略？

直接通过PostgreSQL系统表查询，就能生成可复现现有RLS策略的CREATE POLICY语句，以下是实用的查询语句：

SELECT 
    format(
        'CREATE POLICY %I ON %I.%I FOR %s TO %s USING (%s) WITH CHECK (%s);',
        polname,
        n.nspname,
        c.relname,
        CASE polcmd
            WHEN 'r' THEN 'SELECT'
            WHEN 'a' THEN 'INSERT'
            WHEN 'w' THEN 'UPDATE'
            WHEN 'd' THEN 'DELETE'
            ELSE 'ALL'
        END,
        CASE polroles
            WHEN '{}' THEN 'PUBLIC'
            ELSE array_to_string(polroles, ', ')
        END,
        polqual::TEXT,
        polwithcheck::TEXT
    ) AS create_policy_statement
FROM 
    pg_policy p
JOIN 
    pg_class c ON p.polrelid = c.oid
JOIN 
    pg_namespace n ON c.relnamespace = n.oid
WHERE 
    n.nspname NOT IN ('pg_catalog', 'information_schema');

使用说明： #

1. 在Supabase的SQL编辑器中执行上述查询，会返回所有自定义RLS策略对应的CREATE POLICY语句
2. 每条结果都是可直接运行的SQL语句，用来复现原策略
3. 若需要导出到本地文件，可通过psql命令行执行（替换成你的Supabase数据库连接信息）：

psql -d "postgresql://postgres:your_password@db.your_project_ref.supabase.co:5432/postgres" -c "SELECT format('CREATE POLICY %I ON %I.%I FOR %s TO %s USING (%s) WITH CHECK (%s);', polname, n.nspname, c.relname, CASE polcmd WHEN 'r' THEN 'SELECT' WHEN 'a' THEN 'INSERT' WHEN 'w' THEN 'UPDATE' WHEN 'd' THEN 'DELETE' ELSE 'ALL' END, CASE polroles WHEN '{}' THEN 'PUBLIC' ELSE array_to_string(polroles, ', ') END, polqual::TEXT, polwithcheck::TEXT) AS create_policy_statement FROM pg_policy p JOIN pg_class c ON p.polrelid = c.oid JOIN pg_namespace n ON c.relnamespace = n.oid WHERE n.nspname NOT IN ('pg_catalog', 'information_schema');" -t > rls_policies.sql

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