Postgres Row-Level Security for Multi-Tenant SaaS: The Pattern We Use at DexcyJet

Postgres Row-Level Security (RLS) for multi-tenant SaaS is the most underused PostgreSQL feature in the ecosystem. Most multi-tenant applications implement tenant isolation at the application layer — a WHERE org_id = $1 in every query, enforced by code conventions and hoping nobody ever misses it. RLS moves that enforcement into the database itself, making it impossible for a misconfigured query to accidentally expose one tenant’s data to another.

This post describes the exact RLS pattern DexcyJet uses: a with_admin role, FORCE RLS, and GUC-based tenant context propagation. It’s written for Elixir/Ecto, but the PostgreSQL patterns apply to any stack.

The Problem RLS Solves

Consider a typical multi-tenant SaaS table:

CREATE TABLE subscribers (
  id         uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id     uuid NOT NULL REFERENCES organisations(id),
  email      text NOT NULL,
  status     text NOT NULL DEFAULT 'active',
  inserted_at timestamptz NOT NULL DEFAULT now()
);

In an application without RLS, tenant isolation is the application’s responsibility. Every query that touches subscribers must include WHERE org_id = current_org_id. Miss one (in a bulk update, a background job, an admin tool, a new engineer’s first PR) and you have a data leakage incident.

RLS makes this a database-level invariant instead of an application-level convention.

Step 1: Create the Roles

Use two PostgreSQL roles:

• dexcyjet_app: The application user. Has SELECT, INSERT, UPDATE, DELETE on tables but cannot bypass RLS.
• dexcyjet_admin: The migration and backfill user. Has BYPASSRLS for administrative operations. Never used by the application at runtime.

-- Application role (cannot bypass RLS)
CREATE ROLE dexcyjet_app WITH LOGIN PASSWORD 'your_app_password';

-- Admin role (can bypass RLS for migrations/backfills)
CREATE ROLE dexcyjet_admin WITH LOGIN PASSWORD 'your_admin_password' BYPASSRLS;

-- Grant table-level permissions to app role
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO dexcyjet_app;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO dexcyjet_app;

Step 2: Enable RLS and Create Policies

-- Enable RLS on the subscribers table
ALTER TABLE subscribers ENABLE ROW LEVEL SECURITY;

-- FORCE RLS applies policies even to table owners
-- Without FORCE, the table owner bypasses RLS automatically
ALTER TABLE subscribers FORCE ROW LEVEL SECURITY;

-- Policy: the app role can only see rows where org_id matches
-- the current_setting GUC value we'll set per-request
CREATE POLICY tenant_isolation ON subscribers
  AS PERMISSIVE
  FOR ALL
  TO dexcyjet_app
  USING (org_id = current_setting('app.current_org_id')::uuid)
  WITH CHECK (org_id = current_setting('app.current_org_id')::uuid);

The current_setting('app.current_org_id') call reads a PostgreSQL GUC (Grand Unified Configuration) variable that we’ll set at the start of every database connection or transaction. This is the key mechanism: instead of hardcoding the tenant ID in every query, we set it once on the connection and RLS enforces it everywhere automatically.

Step 3: Propagate Tenant Context in Elixir/Ecto

We need to set app.current_org_id at the start of every request that touches the database. In Phoenix, we do this in a custom Ecto repository module:

defmodule DexcyJet.Repo do
  use Ecto.Repo,
    otp_app: :dexcy_jet,
    adapter: Ecto.Adapters.Postgres

  @doc """
  Runs the given function with the org_id set as the RLS context.
  All queries inside the function will be filtered to that org_id.
  """
  def with_org(org_id, fun) when is_binary(org_id) do
    transaction(fn ->
      query!("SET LOCAL app.current_org_id = '#{org_id}'")
      fun.()
    end)
  end
end

And in the Phoenix controller or a Plug:

defmodule DexcyJetWeb.Plugs.SetTenantContext do
  import Plug.Conn

  def init(opts), do: opts

  def call(%Plug.Conn{assigns: %{current_user: user}} = conn, _opts) when not is_nil(user) do
    # org_id comes from the JWT token, validated by auth middleware
    assign(conn, :org_id, user.org_id)
  end

  def call(conn, _opts), do: conn
end

In a controller action:

def index(conn, _params) do
  org_id = conn.assigns.org_id

  {:ok, result} =
    DexcyJet.Repo.with_org(org_id, fn ->
      DexcyJet.Repo.all(DexcyJet.Subscribers.Subscriber)
    end)

  render(conn, :index, subscribers: result)
end

The SET LOCAL app.current_org_id command sets the GUC for the duration of the current transaction only. Using SET LOCAL (rather than SET SESSION) is important — it ensures the value is reset when the transaction ends, preventing any possibility of the context leaking between requests on a pooled connection.

Step 4: Handling the Superuser Case

DexcyJet’s own admin operations (support tooling, billing, system-level jobs) need to query across all tenants. These use the dexcyjet_admin role, which has BYPASSRLS.

Never expose the admin role to the web-facing application. It’s used only in:

• Ecto migrations (which run as the owner/admin role)
• Background jobs that explicitly require cross-tenant access (e.g., billing summary generation)
• The internal admin panel, which is a separate Phoenix application with its own authentication

# This module explicitly uses admin credentials — clearly named to make
# cross-tenant access an intentional, visible choice
defmodule DexcyJet.AdminRepo do
  use Ecto.Repo,
    otp_app: :dexcy_jet,
    adapter: Ecto.Adapters.Postgres

  # Configured in config/runtime.exs to use dexcyjet_admin credentials
  # This repo bypasses RLS — only use it in explicitly admin-scoped contexts
end

Step 5: Testing RLS

RLS policies should be tested at the database level. A useful pattern:

-- Confirm that the app role cannot see rows from other tenants
SET ROLE dexcyjet_app;
SET app.current_org_id = 'org-uuid-a';

-- Should only return rows where org_id = 'org-uuid-a'
SELECT count(*) FROM subscribers;

-- Reset
RESET ROLE;

In Elixir, write ExUnit tests that explicitly verify cross-tenant queries return empty results:

defmodule DexcyJet.RLSTest do
  use DexcyJet.DataCase

  test "cannot read another org's subscribers" do
    org_a = insert(:organisation)
    org_b = insert(:organisation)
    _sub_a = insert(:subscriber, org_id: org_a.id)

    {:ok, result} =
      DexcyJet.Repo.with_org(org_b.id, fn ->
        DexcyJet.Repo.all(DexcyJet.Subscribers.Subscriber)
      end)

    assert result == []
  end
end

Caveats and Pitfalls

Connection pool warming: SET LOCAL works per-transaction. Using SET (without LOCAL) would persist on the connection. With SET LOCAL, RLS context is tied to the transaction, which is what you want — but ensure your Repo is always using transaction/2 or the connection is fresh.

Ecto’s Repo.all/2 without a transaction: Calling Repo.all outside a with_org transaction will fail at the database level if there’s no app.current_org_id set. This is intentional — it forces you to be explicit. You can set a fallback with current_setting('app.current_org_id', true) (the second argument suppresses the error if the setting is missing, returning NULL instead — your policy then returns no rows).

Migrations: Ecto migrations run as the migration role (admin), which bypasses RLS. No special handling needed.

N+1 patterns: RLS doesn’t change query planning or indexing. Ensure org_id is indexed alongside any other frequently filtered column.

This is the pattern we run in production at DexcyJet. For more on the architecture, see the engineering section of our features page, or read our post on why we chose Elixir/Phoenix for email infrastructure.

Try DexcyJet: Multi-tenant architecture built on Postgres RLS — your subscriber data is isolated at the database layer, not the application layer. Start free.