Row-level security (multi-tenant isolation in the data layer)

Deep dive

How it works

In: a query plus a transaction-local setting (tenant id) · Out: only the rows the policies allow; rejected writes · State: policies in the catalog, the setting per transaction · Cost: one predicate evaluation per policy per row examined; with a tenant-first index only that tenant's range is read

1. The app connects as a role that is not a superuser, has no BYPASSRLS, and does not own the tables.
2. Per request, inside a transaction: SELECT set_config('app.tenant_id', '17', true) (is_local = true, same as SET LOCAL).
3. The engine adds each applicable policy to the query: restrictive_1 AND restrictive_2 AND (permissive_1 OR permissive_2). No permissive policy means deny.
4. Existing rows that fail USING (false or NULL) are skipped silently. A new or updated row that fails WITH CHECK aborts the statement. WITH CHECK defaults to USING when omitted.
5. User-supplied conditions run after the policy, except LEAKPROOF functions, which the planner may run earlier.

ALTER TABLE docs ENABLE ROW LEVEL SECURITY;
ALTER TABLE docs FORCE ROW LEVEL SECURITY;   -- owner is subject too
CREATE POLICY tenant ON docs
  USING      (tenant_id = (SELECT current_setting('app.tenant_id')::bigint))
  WITH CHECK (tenant_id = (SELECT current_setting('app.tenant_id')::bigint));
CREATE POLICY live_only ON docs AS RESTRICTIVE FOR SELECT USING (NOT archived);
CREATE INDEX ON docs (tenant_id, id);

Code

A policy evaluator over an in-memory table (compiled and run; asserts pass): predicates in three-valued logic, permissive OR, restrictive AND, WITH CHECK falling back to USING, bypass flag.

use std::collections::HashMap;

type Row = HashMap<&'static str, i64>;
type Settings = HashMap<&'static str, i64>; // like current_setting('app.tenant_id')

#[derive(Clone)]
enum Rhs { Lit(i64), Setting(&'static str) }

#[derive(Clone)]
enum Pred { Eq(&'static str, Rhs), Not(Box<Pred>) }

/// Three-valued logic: None = NULL, which (like false) fails a policy.
fn eval(p: &Pred, row: &Row, s: &Settings) -> Option<bool> {
    match p {
        Pred::Eq(col, rhs) => {
            let r = match rhs { Rhs::Lit(v) => Some(*v), Rhs::Setting(k) => s.get(k).copied() };
            Some(row.get(col).copied()? == r?)
        }
        Pred::Not(q) => eval(q, row, s).map(|b| !b),
    }
}

#[derive(Clone, Copy, PartialEq)]
enum Cmd { All, Select, Insert, Update }
#[derive(Clone, Copy, PartialEq)]
enum Kind { Permissive, Restrictive }

struct Policy { kind: Kind, cmd: Cmd, using: Option<Pred>, check: Option<Pred> }

#[derive(Debug, PartialEq)]
struct Denied;

struct Table { rows: Vec<Row>, policies: Vec<Policy> }
// bypass: superuser, BYPASSRLS, or table owner without FORCE ROW LEVEL SECURITY
struct Session { settings: Settings, bypass: bool }

impl Table {
    /// Restrictive AND-ed, permissive OR-ed, results AND-ed; no permissive policy => deny.
    fn passes(&self, cmd: Cmd, check: bool, row: &Row, s: &Session) -> bool {
        if s.bypass { return true; }
        let ok = |p: &&Policy| (p.cmd == cmd || p.cmd == Cmd::All) && {
            // WITH CHECK falls back to USING when absent
            let e = if check { p.check.as_ref().or(p.using.as_ref()) } else { p.using.as_ref() };
            e.map_or(false, |e| eval(e, row, &s.settings) == Some(true))
        };
        let applicable = |k| self.policies.iter().filter(move |p| p.kind == k && (p.cmd == cmd || p.cmd == Cmd::All));
        applicable(Kind::Restrictive).all(|p| ok(&p))
            && applicable(Kind::Permissive).any(|p| ok(&p))
    }
    fn select(&self, s: &Session, user_where: impl Fn(&Row) -> bool) -> Vec<&Row> {
        // policy first, user predicate only on rows that passed
        self.rows.iter().filter(|r| self.passes(Cmd::Select, false, r, s) && user_where(r)).collect()
    }
    fn insert(&mut self, s: &Session, row: Row) -> Result<(), Denied> {
        if !self.passes(Cmd::Insert, true, &row, s) { return Err(Denied); }
        self.rows.push(row);
        Ok(())
    }
    fn update(&mut self, s: &Session, user_where: impl Fn(&Row) -> bool, set: (&'static str, i64)) -> Result<usize, Denied> {
        let mut staged = Vec::new();
        for (i, r) in self.rows.iter().enumerate() {
            // must be visible (SELECT) and updatable (UPDATE USING); invisible rows are skipped silently
            if !(self.passes(Cmd::Select, false, r, s) && self.passes(Cmd::Update, false, r, s) && user_where(r)) { continue; }
            let mut n = r.clone();
            n.insert(set.0, set.1);
            if !self.passes(Cmd::Update, true, &n, s) { return Err(Denied); } // WITH CHECK errors, never filters
            staged.push((i, n));
        }
        let n = staged.len();
        for (i, r) in staged { self.rows[i] = r; }
        Ok(n)
    }
}

fn row(id: i64, tenant: i64, archived: i64) -> Row {
    HashMap::from([("id", id), ("tenant", tenant), ("archived", archived)])
}
fn sess(tenant: Option<i64>) -> Session {
    Session { settings: tenant.map(|t| ("tenant", t)).into_iter().collect(), bypass: false }
}

fn main() {
    let mine = Pred::Eq("tenant", Rhs::Setting("tenant"));
    let mut t = Table {
        rows: vec![row(1, 1, 0), row(2, 1, 1), row(3, 2, 0)],
        policies: vec![
            Policy { kind: Kind::Permissive, cmd: Cmd::All, using: Some(mine.clone()), check: None },
            Policy { kind: Kind::Restrictive, cmd: Cmd::Select, using: Some(Pred::Not(Box::new(Pred::Eq("archived", Rhs::Lit(1))))), check: None },
        ],
    };
    let (a, b) = (sess(Some(1)), sess(Some(2)));
    // tenant isolation + restrictive AND: tenant 1 sees id 1 only (id 2 archived), never id 3
    assert_eq!(t.select(&a, |_| true).len(), 1);
    assert_eq!(t.select(&b, |_| true)[0]["id"], 3);
    // the user's own WHERE cannot widen the policy
    assert!(t.select(&a, |r| r["tenant"] == 2).is_empty());
    // WITH CHECK: writing into another tenant is rejected
    assert_eq!(t.insert(&a, row(9, 2, 0)), Err(Denied));
    assert_eq!(t.update(&a, |r| r["id"] == 1, ("tenant", 2)), Err(Denied));
    assert_eq!(t.insert(&a, row(9, 1, 0)), Ok(()));
    // rows of another tenant are silently skipped, not errors
    assert_eq!(t.update(&a, |r| r["id"] == 3, ("archived", 1)), Ok(0));
    // unset tenant (NULL comparison) fails closed
    assert!(t.select(&sess(None), |_| true).is_empty());
    // only restrictive policies => nothing visible
    t.policies.remove(0);
    assert!(t.select(&a, |_| true).is_empty());
    // BYPASSRLS ignores every policy
    let root = Session { settings: Settings::new(), bypass: true };
    assert_eq!(t.select(&root, |_| true).len(), 4);
    println!("ok");
}

Complexity and constants

• Each policy costs one predicate evaluation per row examined. Cost drivers: rows scanned, not rows returned.
• Measured by me (PostgreSQL 17, Apple Silicon laptop, 2M rows, 1000 tenants, parallel seq scan, count(*), no index): about 20 ms with a literal filter, 50-70 ms with tenant_id = current_setting(...)::int per row, about 25-30 ms with the (SELECT current_setting(...)) form. Single runs, noisy.
• With (tenant_id, id) indexed, the same count was an index-only scan, 0.18 ms for 2000 rows.
• Policies that consult other tables add lookups and race risk (Pitfalls); prefer the row's own columns.

Efficiency

• Cost: a comparison plus a current_setting call per row examined; with a tenant-first index only the tenant's index range is read. Bound: rows scanned (memory bandwidth), not policy logic. Measured numbers are under Complexity.
• Levers:
  • (SELECT current_setting(...)) form — about 2x faster in my 2M-row scan; Supabase docs recommend the same wrapping for auth.uid() — evaluated once as an InitPlan, not per row; untested with index scans.
  • Tenant column first in composite indexes — index range instead of full scan, 0.18 ms vs tens of ms in my run — every tenant-scoped table.
  • Policy on the row's own columns only — no per-row lookups; the docs call this the best-performing case — whenever possible.
  • Denormalise tenant_id into child tables — policy stays a column compare, no subquery — deep schemas.
• Naive → standard → state of the art:
  • Naive · app adds WHERE tenant_id by hand, one forgotten filter is a breach
  • Standard · one RLS policy per table, tenant-first indexes, SET LOCAL per transaction
  • State of the art · RLS as the backstop plus partitioning or row-based sharding by tenant (Citus) for scale, and schema or database per tenant for the few largest or regulated tenants

Implementation notes

• Roles: a migration owner (owns tables, runs DDL) and an app role (no BYPASSRLS, not owner); still FORCE RLS.
• Tenant setting: custom option with a dotted name (app.tenant_id) works without registration. Use current_setting('app.tenant_id', true) (missing_ok) to get NULL instead of an error when unset; NULL fails the policy, so it fails closed.
• One permissive policy per table is usually enough; add RESTRICTIVE ones for rules that must always hold (soft-delete, region). Restrictive alone returns nothing.
• Per-command policies: FOR SELECT USING (...) and separate INSERT/UPDATE/DELETE allow read-wide, write-narrow. UPDATE and DELETE also need a matching SELECT policy, because they read rows.
• RETURNING, ON CONFLICT and MERGE also run through the SELECT policy and raise errors instead of skipping.
• row_security = off makes a query fail instead of silently omitting rows (pg_dump sets it); it does not bypass.
• Test as the app role with two tenants: assert counts and that cross-tenant writes raise.

Pitfalls

• Connection pools. SET outlives the transaction; PgBouncer's transaction pooling table lists SET/RESET as unsupported, so a session-level tenant id leaks to the next client. Use SET LOCAL / set_config(..., true) inside a transaction.
• Empty string, not NULL: in my PostgreSQL 17 test, after the transaction ended current_setting('app.tenant_id', true) returned '' and the ::bigint cast raised an error. Fails closed, confusingly.
• Owner and BYPASSRLS connections see everything; SECURITY DEFINER functions owned by the table owner run as the owner. Without FORCE, RLS is skipped there.
• Views run with the view owner's rights and policies unless created WITH (security_invoker = true).
• Foreign-key and unique checks bypass RLS: duplicate-key and FK errors reveal that a hidden value exists. Use surrogate keys.
• TRUNCATE and REFERENCES are not subject to row security.
• Leaky functions: an expensive user function with a non-leakproof body (raising errors containing arguments) is evaluated after the policy, so it only sees allowed rows. Marking your own function LEAKPROOF (superuser only) lets the planner run it before the policy; mark only functions that reveal nothing, even through error messages.
• Policies with subqueries on other tables can race under READ COMMITTED (docs example with FOR UPDATE); use FOR SHARE in the subquery or take a stricter lock when changing the referenced table.

Variants and alternatives

In production

• SaaS and cloud: Supabase applies policies on auth.uid() as a WHERE clause on every query from browser clients (docs).
• Multi-tenant APIs: PostgREST switches to the JWT's database role per request (SET LOCAL ROLE), so policies decide what each caller sees (docs).
• Healthcare and finance: SQL Server's row-level security documents hospital policies limiting nurses to their patients' rows and bank policies limiting financial rows by business division (Microsoft); these are vendor-documented scenarios, not named deployments.

Implementations

Sources

• PostgreSQL documentation, Row Security Policies. docs; CREATE POLICY. docs; CREATE FUNCTION (LEAKPROOF). docs; ALTER TABLE (FORCE ROW LEVEL SECURITY). docs
• PostgreSQL documentation, Rules and Privileges (security barriers, leaky functions). docs; System Administration Functions (set_config, current_setting). docs
• PgBouncer, Features (pooling modes and incompatible features). docs
• Rizvi, Mendelzon, Sudarshan, Roy, Extending Query Rewriting Techniques for Fine-Grained Access Control, SIGMOD 2004. paper
• Supabase, Row Level Security. docs
• Microsoft, Row-Level Security (SQL Server). docs