Spire — multi-tenant SaaS pattern

Mix.install([
  {:spire, path: Path.expand("..", __DIR__)},
  {:ecto_sqlite3, "~> 0.17"}
])

Setup: a tiny multi-tenant database

This Livebook walks through Spire’s core use case — letting customers run arbitrary SQL against your shared database while you enforce tenant isolation, column allowlists, and complexity caps.

We’ll use SQLite in-memory so the notebook runs anywhere. The same code works against Postgres, ClickHouse, DuckDB, or any other Ecto adapter.

defmodule DemoRepo do
  use Ecto.Repo,
    otp_app: :demo,
    adapter: Ecto.Adapters.SQLite3
end

Application.put_env(:demo, DemoRepo, database: ":memory:")
{:ok, _} = DemoRepo.start_link()

alias Ecto.Adapters.SQL

SQL.query!(DemoRepo, """
  CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    email TEXT,
    role TEXT,
    secret TEXT,
    tenant_id INTEGER
  )
""", [])

SQL.query!(DemoRepo, """
  CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    total INTEGER,
    status TEXT,
    tenant_id INTEGER
  )
""", [])

# Tenant 1: Acme Corp
SQL.query!(DemoRepo, "INSERT INTO users VALUES (1, 'Alice', 'alice@acme.test', 'admin', 'shh-acme', 1)", [])
SQL.query!(DemoRepo, "INSERT INTO users VALUES (2, 'Bob',   'bob@acme.test',   'member','shh-acme', 1)", [])

# Tenant 2: Globex
SQL.query!(DemoRepo, "INSERT INTO users VALUES (3, 'Carol', 'carol@globex.test','admin',  'shh-globex', 2)", [])
SQL.query!(DemoRepo, "INSERT INTO users VALUES (4, 'Dave',  'dave@globex.test', 'member', 'shh-globex', 2)", [])

SQL.query!(DemoRepo, "INSERT INTO orders VALUES (10, 1, 100, 'shipped', 1)", [])
SQL.query!(DemoRepo, "INSERT INTO orders VALUES (11, 1, 250, 'pending', 1)", [])
SQL.query!(DemoRepo, "INSERT INTO orders VALUES (12, 2, 50,  'shipped', 1)", [])
SQL.query!(DemoRepo, "INSERT INTO orders VALUES (13, 3, 999, 'shipped', 2)", [])
SQL.query!(DemoRepo, "INSERT INTO orders VALUES (14, 4, 75,  'shipped', 2)", [])

:ok

The policy

Customers can read users and orders, but only for their own tenant. They can SELECT a curated subset of columns; secret and tenant_id are never exposed. They can use tenant_id for filtering, but not project it. Joins are capped.

alias Spire.Policy

policy = Policy.new(
  tables: %{
    "users" => %Policy.Table{
      columns: ["id", "name", "email", "role"],
      filter_only_columns: ["tenant_id"],
      enforced_where: Policy.where("tenant_id = :tenant_id")
    },
    "orders" => %Policy.Table{
      columns: ["id", "user_id", "total", "status"],
      filter_only_columns: ["tenant_id"],
      enforced_where: Policy.where("tenant_id = :tenant_id")
    }
  },
  max_joins: 2,
  max_result_limit: 1000,
  require_limit: false
)

src = Spire.Source.Ecto.new(repo: DemoRepo)
:ok

Helper: run as a specific tenant

This is the “your app code” piece. The :tenant_id parameter comes from the user’s session or JWT — never from their SQL.

run = fn customer_sql, tenant_id ->
  case Spire.query(customer_sql, src, policy: policy, params: %{tenant_id: tenant_id}) do
    {:ok, rows} ->
      IO.puts("✓ #{length(rows)} row(s)")
      Enum.each(Enum.take(rows, 5), &IO.inspect/1)

    {:error, %Spire.AnalyzerError{violations: vs}} ->
      codes = vs |> Enum.map(& &1.code) |> Enum.uniq()
      IO.puts("✗ rejected: #{inspect(codes)}")

    {:error, %Spire.Error{code: code}} ->
      IO.puts("✗ rejected: #{code}")
  end
end

:ok

1. Legitimate customer queries

Alice (tenant 1) runs ordinary queries:

run.("SELECT name, email, role FROM users", 1)

run.("""
  SELECT u.name, count(o.id) AS orders, sum(o.total) AS revenue
  FROM users u JOIN orders o ON u.id = o.user_id
  GROUP BY u.name
  ORDER BY revenue DESC
""", 1)

The same query as tenant 2 returns tenant 2’s rows — automatically:

run.("SELECT name, email, role FROM users", 2)

2. Tenant-scope escape attempts (all blocked)

The customer tries to see other tenants. Every attempt produces either rejection or the customer’s own data — never another tenant’s.

# Lie about which tenant
run.("SELECT name FROM users WHERE tenant_id = 2", 1)

# Universal-truth trick
run.("SELECT name FROM users WHERE 1 = 1 OR tenant_id = 2", 1)

# Negation
run.("SELECT name FROM users WHERE NOT (tenant_id = 1)", 1)

# IS NOT NULL
run.("SELECT name FROM users WHERE tenant_id IS NOT NULL", 1)

3. Column-allowlist violations

# 'secret' is not in the allowlist
run.("SELECT secret FROM users", 1)

# 'tenant_id' is filter-only — cannot appear in SELECT
run.("SELECT tenant_id FROM users", 1)

4. Table-allowlist violations

run.("SELECT 1 FROM other_secrets_table", 1)

5. Function-allowlist violations

run.("SELECT random_function(name) FROM users", 1)

6. Complexity caps

run.("""
  SELECT u.id FROM users u
  JOIN orders o ON u.id = o.user_id
  JOIN orders o2 ON u.id = o2.user_id
  JOIN orders o3 ON u.id = o3.user_id
""", 1)

7. See the SQL that actually gets sent

The injected tenant scope is visible:

{:ok, {sql, params}} = Spire.to_sql(
  "SELECT name FROM users WHERE role = 'admin'",
  src,
  policy: policy,
  params: %{tenant_id: 1}
)

IO.puts("Customer wrote:  SELECT name FROM users WHERE role = 'admin'")
IO.puts("Spire emitted:   #{sql}")
IO.puts("Params:          #{inspect(params)}")

Note how tenant_id = ?1 is the first predicate. It comes from the policy, not the customer’s SQL. The customer’s predicate is AND’d with the enforced scope; they cannot reach ?1 to override it because it’s not in their query syntax.

8. Performance

{plan_us, _} = :timer.tc(fn ->
  Spire.plan("SELECT name FROM users WHERE role = 'admin'", policy: policy)
end)

IO.puts("plan + analyze: #{Float.round(plan_us / 1000, 2)}ms")

Spire’s overhead is sub-millisecond. The database does the work; Spire just ensures the SQL it receives is one the policy permits.

For production, the typical pattern is to keep one @policy module attribute and pull tenant_id from your session:

defmodule MyApp.Reports do
  @policy Spire.Policy.new(...)

  def for_user(user, sql) do
    src = Spire.Source.Ecto.new(repo: MyApp.Repo)
    Spire.query(sql, src, policy: @policy, params: %{tenant_id: user.tenant_id})
  end
end