How RLS Works

UseBetter Tenant relies on a Postgres feature called Row-Level Security (RLS) to enforce tenant isolation at the database level. This page walks through exactly what happens on every request so you can see why this approach is safe, how it handles edge cases, and what Postgres guarantees you get for free.

If you just want to get started, skip to Quick Start. Come back here when you want to understand what is happening beneath the surface.

The problem with WHERE clauses

The most common way to implement multi-tenancy is to add WHERE tenant_id = ? to every query. It works, but it has a critical flaw: a single missing WHERE clause exposes all tenants’ data. The more queries your application has, the more likely someone will forget one, especially across teams and over time.

UseBetter Tenant moves tenant filtering from your application code into the database itself. Even if your code has a bug — a forgotten filter, a bad JOIN, a raw SQL query — Postgres blocks access to other tenants’ rows before they ever leave the database.

What happens on every request

Here is the full lifecycle, from HTTP request to database query and back:

GET /acme/projects

▼

1Middleware

Your framework adapter intercepts the request

▼

2Resolve tenant

"acme" → look up UUID → "550e8400-..."

from path, header, subdomain, JWT, or custom fn

▼

3Open transaction

BEGIN

SET LOCAL app.current_tenant = '550e8400-...'

scoped to this transaction only — cleared on commit

▼

4Your code runs

db.select().from(projects)

Postgres checks every row against the RLS policy:

tenant_id = '550e8400-...' → row visible

tenant_id = anything else → row hidden

▼

5Commit

COMMIT

Session variable erased — connection returns to pool, clean

▼

200 OK [{...}, {...}]

Every step is deliberate. The next sections explain the “why” behind each one.

Try it yourself

Switch tenants, run different queries, and see exactly what SQL executes and which rows come back. This is a browser simulation of the same logic UseBetter Tenant runs on every request.

Run as:Acme CorpGlobex IncInitech LLC

Scenario:SELECT rowsINSERT a rowCross-tenant INSERTAdmin bypassRun query

Query all projects — RLS filters to the current tenant.

projects table

id	tenant	name
1	acme	Website Redesign
2	acme	Mobile App
3	globex	Data Pipeline
4	globex	API Gateway
5	initech	Legacy Migration
6	initech	Cloud Setup

Transaction log

Click "Run query" to see what SQL executes.

Simulated in-browser — no real database. Demonstrates the same SQL that UseBetter Tenant executes on every request.

Step by step

1. Middleware intercepts the request

Your framework adapter (Hono, Express, or Next.js) wraps your route handler. Before your code runs, the middleware calls tenant.handleRequest(), which kicks off tenant resolution and sets up the database context. Your handler only executes after the tenant is confirmed.

2. Tenant resolution: identifier to UUID

The resolver extracts a raw identifier from the request. This could be a header value, a path segment, a subdomain, a JWT claim, or the return value of a custom function. Whatever it is, RLS needs a UUID — so the library normalizes it:

• Already a UUID? Pass it through.
• A slug like "acme"? Look it up in the tenants table and return its UUID.
• Custom resolveToId configured? Call your function — it has full control.

Slug lookups run inside a separate runAsSystem transaction so that the tenants table (which has its own RLS policies) is accessible. This lookup happens once per request, before the user-facing transaction starts.

3. Transaction + SET LOCAL: the key mechanism

This is the core of the approach. The adapter (Drizzle or Prisma) does two things:

1. Opens a database transaction — BEGIN
2. Sets a session variable scoped to that transaction:

SELECT set_config('app.current_tenant', '550e8400-...', true);

The third argument, true, is what makes this safe. It tells Postgres to scope this variable to the current transaction only. When the transaction ends — whether by COMMIT or ROLLBACK — the variable disappears. This is the SQL standard SET LOCAL behavior, just invoked via the function form so ORMs can call it as a regular query.

Why this matters:

• Connection pool safety. When the transaction finishes, the connection returns to the pool with no leftover state. The next request that picks up the same connection gets its own transaction and its own app.current_tenant value.
• No cross-request leakage. Even if two requests run concurrently on the same connection pool, each has its own transaction and its own session variable. Postgres guarantees transaction isolation.
• No global state. Unlike SET (without LOCAL), which persists for the entire session/connection, SET LOCAL is confined to the transaction.

4. RLS policy enforcement

With app.current_tenant set inside the transaction, Postgres RLS takes over. The CLI generates a policy on each tenant-scoped table that looks like this:

CREATE POLICY "rls_tenant_projects" ON "projects"
  FOR ALL
  USING (
    (tenant_id)::text = current_setting('app.current_tenant', true)
    OR current_setting('app.bypass_rls', true) = 'true'
  )
  WITH CHECK (
    (tenant_id)::text = current_setting('app.current_tenant', true)
    OR current_setting('app.bypass_rls', true) = 'true'
  );

Here is what each clause does:

Clause	When it runs	What it checks
USING	On SELECT, UPDATE, DELETE	”Can this row be seen?” — only rows whose tenant_id matches app.current_tenant
WITH CHECK	On INSERT, UPDATE	”Can this row be written?” — prevents inserting or updating rows with a different tenant_id

Both clauses also check app.bypass_rls — this is the escape hatch for admin operations (covered below).

What Postgres guarantees:

• The policy is evaluated for every row, on every query, regardless of how the query is written. A SELECT *, a complex JOIN, a subquery, a CTE — they all go through RLS.
• FORCE ROW LEVEL SECURITY ensures the policy applies even to the role that owns the table. Without FORCE, table owners would bypass RLS silently.

5. Auto-population of tenant_id

The CLI also generates a trigger on each tenant-scoped table:

CREATE OR REPLACE FUNCTION set_tenant_id()
RETURNS TRIGGER AS $$
BEGIN
  IF NEW.tenant_id IS NULL
     AND current_setting('app.current_tenant', true) IS NOT NULL THEN
    NEW.tenant_id := current_setting('app.current_tenant', true)::uuid;
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER set_tenant_id_trigger
  BEFORE INSERT ON "projects"
  FOR EACH ROW
  EXECUTE PROCEDURE set_tenant_id();

This means your application code never has to set tenant_id manually. When you insert a row, the trigger reads the current tenant from the session variable and stamps it automatically. And because the WITH CHECK clause also validates, even if your code tries to set a wrong tenant_id, Postgres rejects it.

6. Commit and cleanup

When the transaction commits, the SET LOCAL variable is gone. The connection goes back to the pool with zero tenant-related state. The next request starts clean.

Why you can trust this

Here are the properties that make this approach reliable, and the Postgres mechanisms behind each one.

Isolation is enforced by the database, not your code

RLS policies run inside Postgres, after your query is planned, before rows are returned. Your ORM, your application code, and your middleware cannot circumvent them (unless you connect as a superuser — see the caveat below).

A missing WHERE clause cannot leak data

With the WHERE tenant_id = ? pattern, a forgotten filter leaks data. With RLS, Postgres adds the filter implicitly. Write SELECT * FROM projects — you only get the current tenant’s rows. This is the single biggest advantage.

Connection pooling is safe

The true parameter in set_config(..., true) scopes the variable to the transaction. When the transaction ends, the variable is erased. The next request on that connection starts fresh. This has been a core Postgres behavior since RLS was introduced in Postgres 9.5.

Concurrent requests cannot interfere

Two requests running simultaneously each have their own transaction. Postgres transaction isolation guarantees that one transaction’s SET LOCAL is invisible to another, even on the same connection (which cannot happen anyway — connection pools serialize transactions on a single connection).

Admin operations use a session flag, not a superuser

When you need to work across tenants (creating a tenant, running a cron job, seeding data), runAsSystem does not escalate privileges. It sets a second transaction-scoped variable:

SELECT set_config('app.bypass_rls', 'true', true);

The RLS policies include an OR clause that checks this flag. This means:

• The bypass is still transaction-scoped — it cannot leak to other requests.
• The database role stays the same — no superuser, no role switching.
• If an attacker compromises a normal request, they cannot set app.bypass_rls because only runAsSystem does that in code, and the setting is cleared when the transaction ends.

Caution

If you expose an endpoint that calls runAsSystem, that endpoint can read and write all tenant data. Protect it with strong authentication and access control.

The tenants table has its own defense layer

The tenants lookup table uses two separate policies:

1. Read: open to all (USING (true)) — your application needs to resolve slugs and list tenants.
2. Write: requires app.bypass_rls = 'true' — only runAsSystem can create, update, or delete tenants.

Even if an attacker can execute raw SQL as the application role, they cannot modify the tenants table without the bypass flag.

The superuser caveat

PostgreSQL superusers bypass all RLS policies, regardless of FORCE ROW LEVEL SECURITY. This is by design in Postgres — superusers are unrestricted.

Your application must connect as a regular (non-superuser) role. If you are using the default postgres user in Docker, create a dedicated application role:

CREATE ROLE app_user WITH LOGIN PASSWORD 'app_password';
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT USAGE ON SCHEMA public TO app_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

Then use DATABASE_URL=postgresql://app_user:app_password@localhost:5432/mydb.

Danger

If your DATABASE_URL connects as a superuser, RLS policies are silently ignored and tenant isolation does not work. The CLI check command detects this — run npx @usebetterdev/tenant-cli check to verify your setup.

Tables without RLS

RLS is opt-in per table. Only tables listed in tenantTables in your better-tenant.config.json (and processed by the CLI migrate command) get RLS policies, a tenant_id column, and the auto-populate trigger. Everything else behaves like a normal Postgres table.

This means you can mix tenant-scoped and shared tables in the same transaction:

const db = tenant.getDatabase();

// projects has RLS → filtered to current tenant
const projects = await db.select().from(projectsTable);

// categories has no RLS → all rows visible
const categories = await db.select().from(categoriesTable);

The session variable (app.current_tenant) is still set, but tables without policies simply ignore it.

Verifying your setup

The CLI includes a check command that validates your RLS configuration:

npx @usebetterdev/tenant-cli check --database-url $DATABASE_URL

It verifies:

• RLS is enabled and forced on all configured tables
• Policies exist with the correct USING and WITH CHECK clauses
• The set_tenant_id trigger is present
• The database role is not a superuser

Run this in CI or after migrations to catch configuration drift.

Summary

What	How	Why it is safe
Tenant context	SET LOCAL app.current_tenant inside a transaction	Transaction-scoped — cleared on commit, invisible to other transactions
Row filtering	RLS policies with USING and WITH CHECK	Postgres enforces on every row, every query, regardless of how the query is written
Auto-populate	BEFORE INSERT trigger reads session variable	Application cannot forget or override tenant_id
Admin bypass	SET LOCAL app.bypass_rls in a separate transaction	Same transaction-scoping guarantees — cannot leak
Connection pooling	set_config(..., true) = SET LOCAL	Variable is erased when transaction ends — next request starts clean
Table owner bypass	FORCE ROW LEVEL SECURITY	Even the table owner role goes through RLS

Next steps

• Architecture — detailed reference for every mechanism: policies, triggers, slug resolution, and the summary table
• Quick Start — get a working multi-tenant app in 5 minutes
• Configuration — resolver strategies, tenant API, and admin operations
• CLI & Migrations — generate and verify your RLS setup