[Draft] Postgres RLS bridge

[vincentvanbush]

Disclaimer: It's Claude's writeup on implementing Postgres RLS in which we focused on the question of Permit-first or DB-first approaches to rule propagation. I originally wrote some musings about it after the 2025 ElixirConf EU, as I was approached by a few people hinting me at this idea.

I expect that to propagate Permit-defined rules to Postgres RLS we'll need to come up with a migration mechanism to translate them to DDL, whereas via introspection of existing Postgres RLS configuration we can provide a means to quickly recreate Permit equivalent rules from an existing database, living in Supabase or what not.

I'm open to discussing this idea further, but will leave you with this for now as it's a complex topic and certainly not a low-hanging fruit. I might come up with better ideas in the future when I find some time to play around with real apps leveraging Postgres RLS to see if it makes sense.

Permit ↔ Postgres RLS Bridge

Directions

Both directions are viable with clearly different purposes:

• Permit-first (Permit → Postgres): For greenfield or Permit-native projects. Permit is the canonical source of truth; RLS is a derived, defense-in-depth layer generated via migrations. Rules stay in idiomatic Elixir.
• Postgres-first (Postgres → Permit): A migration tool for projects that already have RLS policies (e.g. inherited from a Supabase/Bolt vibe-coded prototype). It reads existing pg_policies, parses the qual/with_check expressions, and generates a Permit permissions module scaffold. The developer reviews and refines it, then Permit takes over as the ongoing source of truth.

Recommendation: Permit remains the runtime authority in both cases. The introspection path is a one-time bootstrapping tool, not a continuous sync.

Versioning via Migrations

Yes, versioning is required. Since RLS policies are DDL, they must be treated exactly like schema migrations. The flow:

mix permit_rls.diff MyApp.Authorization  →  generates Ecto migration
  ALTER TABLE articles ENABLE ROW LEVEL SECURITY;
  CREATE POLICY ...
  DROP POLICY ...   (for removed rules)

The diff computes a hash of the current serialized conditions_map and compares against a permit_rls_versions tracking table (similar to schema_migrations).

Architecture Overview

flowchart TD
    subgraph permitFirst [Permit-first path]
        PermitPermissions["Permissions Module\ncan/1 callback"]
        ConditionsMap["conditions_map\nDNF per action+resource"]
        PolicyGenerator["Permit.RLS.PolicyGenerator"]
        ConditionTranslator["Permit.RLS.ConditionTranslator"]
        MixDiff["mix permit_rls.diff"]
        Migration["Ecto Migration\nCREATE/DROP POLICY"]
    end

    subgraph postgresFirst [Postgres-first path]
        PgPolicies["pg_policies\nqual / with_check strings"]
        ExprParser["Permit.RLS.ExprParser\nNimbleParsec subset"]
        ConditionBuilder["Permit.RLS.ConditionBuilder\nSQL AST → Permit DSL"]
        MixIntrospect["mix permit_rls.introspect"]
        GeneratedModule["Generated permissions.ex\nscaffold for review"]
    end

    RLSPlug["Permit.RLS.Plug\nset_config per request"]
    Postgres["Postgres RLS\nper-row enforcement"]

    PermitPermissions -->|"can(placeholder)"| ConditionsMap
    ConditionsMap --> PolicyGenerator
    PolicyGenerator --> ConditionTranslator
    PolicyGenerator --> MixDiff
    MixDiff --> Migration
    Migration --> Postgres

    Postgres -->|"SELECT * FROM pg_policies"| PgPolicies
    PgPolicies --> ExprParser
    ExprParser --> ConditionBuilder
    ConditionBuilder --> MixIntrospect
    MixIntrospect --> GeneratedModule

    RLSPlug -->|"set_config('app.current_user_id', ...)"| Postgres

Loading

New Package: permit_rls

A new hex package depending on permit, permit_ecto, ecto_sql, and postgrex.

Key Modules

**Permit.RLS.PolicyGenerator**

Takes a permissions module and a subject placeholder descriptor, walks conditions_map, and produces a list of %Permit.RLS.Policy{} structs:

%Permit.RLS.Policy{
  table: :articles,
  name: "permit_read_articles",
  command: :select,           # :select | :insert | :update | :delete | :all
  using: "user_id = current_setting('app.current_user_id')::bigint",
  with_check: nil
}

Action → SQL command mapping (default CRUD, overridable):

• :read → FOR SELECT (USING clause)
• :create → FOR INSERT (WITH CHECK clause)
• :update → FOR UPDATE (USING + WITH CHECK)
• :delete → FOR DELETE (USING clause)
• :all / grouped → FOR ALL

**Permit.RLS.ConditionTranslator**

Mirrors permit_ecto's DynamicQueryJoiner + ParsedCondition, but emits SQL strings instead of Ecto.Query.dynamic(). Works from the same ParsedCondition structs already in conditions_map.

DNF → SQL: each ParsedConditionList becomes (cond_a AND cond_b), disjuncts joined with OR:

(user_id = current_setting('app.current_user_id')::bigint)
OR
(team_id = current_setting('app.current_team_id')::bigint AND published = true)

Subject field references (e.g. &(&1.id)) are resolved by a subject schema configuration (see below). Untranslatable conditions (:match, bare :function_1/:function_2 without an explicit SQL counterpart) raise Permit.RLS.UntranslatableConditionError at generation time — not silently skipped — so the developer is explicitly aware of what can't be enforced at the DB level.

**Permit.RLS.SubjectSchema**

A required configuration that tells the translator how to map subject fields to Postgres session variables and types:

use Permit.RLS,
  permissions_module: MyApp.Authorization.Permissions,
  subject_schema: [
    id: [session_var: "app.current_user_id", type: :bigint],
    role: [session_var: "app.current_user_role", type: :text]
  ]

This is the key piece that bridges &(&1.id) in Permit to current_setting('app.current_user_id')::bigint in SQL.

**Permit.RLS.Plug**

A Plug that injects session variables at the start of each request, within a transaction or via set_config(..., true) (local to transaction):

plug Permit.RLS.Plug,
  repo: MyApp.Repo,
  subject_fn: fn conn -> conn.assigns.current_scope.user end,
  fields: [:id, :role]

Internally executes: SELECT set_config('app.current_user_id', $1, true) via Ecto.Adapters.SQL.query!/3. When the connection is returned to the pool, the true (local) flag ensures the variable resets automatically.

**Mix.Tasks.PermitRls.Diff**

• Serializes the current conditions_map (using :erlang.term_to_binary + :crypto.hash(:sha256, ...))
• Queries permit_rls_policies tracking table in the DB for the last applied hash + policy names
• Diffs: produces CREATE POLICY, DROP POLICY, ALTER POLICY, and ENABLE/DISABLE ROW LEVEL SECURITY statements
• Writes a timestamped Ecto.Migration file with execute/1 calls

**Mix.Tasks.PermitRls.GenerateAll**

Non-diff variant: drops all permit_-prefixed policies and recreates from scratch. Useful for initial setup.

---

Postgres-first: Introspection Path

What pg_policies Gives Us

Postgres exposes all RLS policies via the pg_policies system view. The qual (USING) and with_check columns contain deparsed SQL expression strings produced by pg_get_expr() — e.g.:

(user_id = (current_setting('app.current_user_id'::text))::bigint)
(deleted_at IS NULL)
((owner_id = auth.uid()) OR (status = 'public'::text))
true

These are valid SQL expression fragments, fully parenthesized, with explicit ::type casts on constants and current_setting() references.

Permit.RLS.ExprParser (NimbleParsec)

A pure-Elixir, dependency-free expression parser built with NimbleParsec that handles the common subset of RLS expression patterns. It outputs a lightweight intermediate AST (plain Elixir maps/tuples):

# Input:  "(user_id = (current_setting('app.current_user_id'))::bigint)"
# Output:
{:eq, {:column, :user_id}, {:setting, "app.current_user_id", :bigint}}

# Input:  "((owner_id = auth.uid()) OR (status = 'public'::text))"
# Output:
{:or,
  {:eq, {:column, :owner_id}, {:func_call, "auth.uid"}},
  {:eq, {:column, :status}, {:literal, "public", :text}}}

Supported patterns:

SQL pattern	Intermediate AST node
col = value / col != value	{:eq/:neq, {:column, c}, val}
col > val / col >= val / col < val / col <= val	{:gt/:ge/:lt/:le, ...}
col IN (v1, v2)	{:in, {:column, c}, [vals]}
col IS NULL / col IS NOT NULL	{:is_nil, {:column, c}} / {:not_nil, ...}
col LIKE 'pat' / col ILIKE 'pat'	{:like/:ilike, {:column, c}, pat}
(expr) AND (expr)	{:and, left, right}
(expr) OR (expr)	{:or, left, right}
true / false	{:literal, true}
current_setting('var')::type	{:setting, "var", type}
'value'::type	{:literal, value, type}

Anything outside this set (subqueries, auth.uid(), now(), auth.jwt() ->>, etc.) parses to {:unparseable, raw_string}.

Permit.RLS.ConditionBuilder

Takes the intermediate AST and the SubjectSchema config, and produces Permit DSL keyword fragments as Elixir source code strings:

# {:eq, {:column, :user_id}, {:setting, "app.current_user_id", :bigint}}
# + subject_schema: [id: [session_var: "app.current_user_id"]]
→  "user_id: user.id"

# {:or, {:eq, :status, {:literal, "active", :text}}, {:eq, :status, {:literal, "pending", :text}}}
→  two separate conditions on distinct disjuncts

OR nodes at the top level are split into separate |> action(Resource, ...) chains (each disjunct in Permit is a separate read/all/... call). AND nodes become multiple keyword conditions on the same call.

{:unparseable, raw} nodes emit a # TODO: manual translation needed: <raw sql> comment placeholder.

Mix.Tasks.PermitRls.Introspect

mix permit_rls.introspect \
  --repo MyApp.Repo \
  --schema public \
  --output lib/my_app/authorization/generated_permissions.ex \
  --module MyApp.Authorization.GeneratedPermissions \
  --table-map "articles:MyApp.Article,users:MyApp.User"

Steps:

1. Connects to the DB via the configured Repo
2. Queries pg_policies filtered by schema (and optionally table names)
3. Groups policies by (tablename, cmd) — multiple permissive policies on the same table+action are OR'd (separate Permit calls); RESTRICTIVE policies are flagged with a comment
4. Runs each qual/with_check through ExprParser → ConditionBuilder
5. Generates a complete defmodule with a can/1 function body
6. Writes to --output path (or prints to stdout if omitted)

Example output for two policies on articles:

defmodule MyApp.Authorization.GeneratedPermissions do
  # Generated by mix permit_rls.introspect on 2026-04-15
  # Review and adjust before committing. Move to your main permissions module.
  use Permit.Ecto.Permissions, actions_module: Permit.Actions.CrudActions

  def can(%MyApp.User{} = user) do
    permit()
    # policy: "read_own_articles" on articles FOR SELECT
    |> read(MyApp.Article, user_id: user.id)
    # policy: "read_public_articles" on articles FOR SELECT
    |> read(MyApp.Article, status: "public")
    # policy: "update_own_articles" on articles FOR UPDATE
    |> update(MyApp.Article, user_id: user.id)
    # policy: "complex_policy" on articles FOR SELECT
    # TODO: manual translation needed: (org_id IN (SELECT org_id FROM memberships WHERE user_id = current_setting('app.current_user_id')::bigint))
    |> read(MyApp.Article)
  end
end

Parser dependency decision

NimbleParsec is the right choice for a "basic use cases" MVP:

• Pure Elixir, no NIF, no C compiler required
• Sufficient to cover equality, comparisons, IN, IS NULL, LIKE, AND, OR — the patterns that make up the vast majority of real-world simple RLS policies
• The {:unparseable, ...} escape hatch handles everything else gracefully with # TODO comments

For users who want fuller SQL expression coverage (subqueries, auth.uid(), JWT claims), the upgrade path is to swap ExprParser for [pg_query_ex](https://hex.pm/packages/pg_query_ex) (a NIF wrapping libpg_query) — but this is out of scope for v1.

---

Handling the can/1 Problem

can/1 takes a concrete subject. For policy generation, we need to call it with a placeholder subject to get the rule structure without subject-specific values. Two approaches:

1. Placeholder struct: call can(%MyApp.User{}) — works if rules reference subject fields via anonymous functions (most common case). The generator substitutes actual values with session variable references.
2. Dedicated policy introspection callback: policy_rules/0 that returns can(%MyApp.User{id: :rls_subject_id}) with sentinel atoms — more explicit, but adds API surface.

The placeholder struct approach (option 1) is simpler and non-breaking. The ConditionTranslator detects functions of arity 1 (subject extractors) and substitutes the appropriate current_setting(...) call.

---

What's In Scope vs Out

In scope for v1:

• Simple field equality/comparison conditions translatable to SQL
• CRUD action → SQL command mapping
• set_config plug for request-scoped user context
• Migration generation via mix permit_rls.diff
• Tracking table for versioning

Out of scope / deferred:

• Association-based conditions (join conditions in RLS are non-trivial)
• JWT-based auth (Supabase-style) — requires separate PostgREST / pgcrypto infrastructure
• :function_1/:function_2/:match conditions in Permit → SQL (raise UntranslatableConditionError)
• Multi-tenancy / row-level roles beyond current_user_id
• SQL subquery patterns in introspection (emitted as # TODO comments)
• pg_query_ex (full SQL parser) — deferred; NimbleParsec subset is sufficient for v1
• RESTRICTIVE policy semantics in introspection (annotated as comment warnings; Permit has no direct equivalent)

---

Files to Create

Permit-first path:

• mix.exs — new permit_rls package (deps: permit, permit_ecto, ecto_sql, postgrex, nimble_parsec)
• lib/permit_rls.ex — use Permit.RLS, ... macro
• lib/permit_rls/policy.ex — %Permit.RLS.Policy{} struct
• lib/permit_rls/policy_generator.ex — conditions_map → [%Policy{}]
• lib/permit_rls/condition_translator.ex — ParsedCondition DNF → SQL string
• lib/permit_rls/plug.ex — request-level set_config injection
• lib/permit_rls/migration_generator.ex — writes .exs migration files
• lib/permit_rls/errors/untranslatable_condition_error.ex
• lib/mix/tasks/permit_rls.diff.ex
• lib/mix/tasks/permit_rls.generate_all.ex

Postgres-first (introspection) path:

• lib/permit_rls/expr_parser.ex — NimbleParsec SQL subset parser → intermediate AST
• lib/permit_rls/condition_builder.ex — intermediate AST → Permit DSL source strings
• lib/permit_rls/policy_reader.ex — queries pg_policies via Repo
• lib/permit_rls/code_generator.ex — renders complete defmodule source from policy list
• lib/mix/tasks/permit_rls.introspect.ex