Row-Level Security

Row-Level Security (RLS) is Postgres-native row filtering enforced at the engine level. Unlike WHERE clauses sprinkled through application code, RLS cannot be bypassed by JOINs, subqueries, CTEs (Common Table Expressions — the named subquery blocks written with WITH), or views. The database itself refuses to return rows that violate the policy. Forget a WHERE clause and you leak data; forget nothing with RLS because the engine enforces it for you.

ALTER TABLE areas ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON areas
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

USING vs WITH CHECK

Two predicates control different directions of data flow:

Predicate	Applies to
USING	Reads: SELECT, plus the WHERE phase of UPDATE/DELETE
WITH CHECK	Writes: INSERT, plus the new row of UPDATE

Common pattern: set both to the same expression for symmetric tenant isolation.

CREATE POLICY tenant_isolation ON areas
    USING      (tenant_id = current_setting('app.current_tenant')::uuid)
    WITH CHECK (tenant_id = current_setting('app.current_tenant')::uuid);

If only USING is specified (as in the Implementation example below), Postgres reuses it as the WITH CHECK predicate for INSERT and UPDATE.

Policy types

• PERMISSIVE (default) — policies are OR’d together. If any permissive policy matches, the row is allowed.
• RESTRICTIVE — policies are AND’d together. All restrictive policies must match.

A row must pass at least one permissive policy and every restrictive policy. Use restrictive policies to layer additional constraints (e.g., soft-delete filtering) on top of tenant isolation.

-- Every tenant-scoped query also excludes soft-deleted rows
CREATE POLICY no_deleted_rows ON areas AS RESTRICTIVE
    USING (deleted_at IS NULL);

The session variable pattern

The standard multi-tenant approach:

1. Set a session variable at the start of each transaction.
2. Reference that variable in the policy predicate.

SET LOCAL app.current_tenant = 'a1b2c3d4-...';

SET LOCAL is critical with connection poolers

A connection pooler (like PgBouncer) sits between the application and Postgres, multiplexing many app connections onto a smaller pool of real database connections. When a transaction ends, the pooler may reuse the underlying connection for the next request from a different tenant. SET LOCAL scopes the setting to the current transaction and is cleared automatically when the transaction ends. Without LOCAL, the setting persists on the connection — the next tenant’s query runs with the previous tenant’s identity. This is a catastrophic data leak.

In practice, set_config('app.current_tenant', $1, true) is equivalent to SET LOCAL and is easier to parameterize from application code. The third argument true means transaction-local.

Implementation example

The scoped_tx() function demonstrates the pattern in Rust:

pub(crate) async fn scoped_tx<'a>(
    pool: &'a PgPool,
    tenant_id: &Uuid,
) -> Result<Transaction<'a, Postgres>, AppError> {
    let mut tx = pool.begin().await.map_err(db_err)?;
 
    sqlx::query::<Postgres>("SELECT set_config('app.current_tenant', $1, true)")
        .bind(tenant_id.to_string())
        .execute(&mut *tx)
        .await
        .map_err(db_err)?;
 
    Ok(tx)
}

Rust's ownership model makes this safe

If the caller never calls tx.commit(), the Transaction’s Drop impl runs and auto-rollbacks. There is no try/finally block, no cleanup code to forget. The pool is shared across all requests; only the transaction is tenant-scoped.

The migration that enables RLS on the areas table:

ALTER TABLE areas ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON areas
    USING (tenant_id = current_setting('app.current_tenant')::uuid);
 
GRANT SELECT, INSERT, UPDATE, DELETE ON areas TO app_role;

FORCE ROW LEVEL SECURITY

Table owners and superusers bypass RLS by default. If the application role owns the table (or you want defense-in-depth), force it:

ALTER TABLE areas FORCE ROW LEVEL SECURITY;

When to use FORCE

If your app connects as a non-owner role (like app_role above, which only has GRANT privileges), FORCE is not strictly necessary — RLS already applies. But adding it is cheap insurance against future permission changes.

Performance

RLS adds a qual (filter predicate) to every query plan. You can see it in EXPLAIN:

EXPLAIN SELECT * FROM areas;
-- Shows: Filter: (tenant_id = 'some-uuid'::uuid)

Negligible overhead for tenant isolation

The tenant_id column is already indexed for foreign key lookups and query patterns. The RLS qual piggybacks on that index. In practice the overhead is unmeasurable for typical OLTP workloads.

Alternatives and why RLS wins

Approach	Drawback
Application-level WHERE	Every query must remember to add it. One miss = data leak. Unreviewable at scale.
Separate schema per tenant	Every migration must run N times. Schema drift is inevitable. Tooling breaks.
Separate database per tenant	Resource waste (connections, memory, vacuum). Operational nightmare past ~50 tenants.
RLS	Single schema, single pool, engine-enforced isolation. Sweet spot for most SaaS.

When RLS is not enough

If tenants need genuinely different schemas, different Postgres versions, or regulatory data residency in separate regions, you need physical separation. For everything else, RLS is the answer.

See also

• PgBouncer — connection pooling and why SET LOCAL matters
• Extensions and Grants — role and privilege model
• MySQL vs Postgres — MySQL has no equivalent of RLS