🔐 Multi-Tenant Isolation With Postgres Row-Level Security and AI

In a multi-tenant application, the scariest bug is the one where tenant A sees tenant B’s data because someone forgot a WHERE tenant_id = ? clause. You can try to enforce that filter in every query in your codebase and audit it forever, or you can push the rule down into the database where it cannot be forgotten. Postgres Row-Level Security does exactly that: it attaches a mandatory filter to a table that the database applies to every query automatically. Done right, it makes cross-tenant leakage a database-level impossibility rather than a code-review hope. Done wrong, it gives you a false sense of safety while a connection pool quietly leaks tenant context between requests. AI is a strong partner for drafting policies and, more importantly, for generating the adversarial tests that prove they hold.

What RLS actually does

RLS lets you define policies on a table that restrict which rows a given role can see or modify. Once a table has RLS enabled and a policy defined, every SELECT, INSERT, UPDATE, and DELETE from a non-owning role silently gains the policy’s condition. The application keeps writing ordinary queries; Postgres injects the tenant filter underneath.

CREATE TABLE invoices (
    id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    tenant_id  uuid NOT NULL,
    amount_cents bigint NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now()
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

Enabling RLS with no policy is a deny-all by default for non-owners: the table appears empty. That is the safe failure mode, and it is intentional. You then add policies to open up exactly the access you want.

Tenant context with current_setting

The policy needs to know which tenant the current request belongs to. The clean pattern is a session-local configuration variable set at the start of each request, read back inside the policy with current_setting.

CREATE POLICY tenant_isolation ON invoices
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

-- At the start of every request, before any tenant query:
SELECT set_config('app.tenant_id', '7c9e6679-...-uuid', true);

The third argument true to set_config makes it transaction-local, so it resets at commit or rollback. Use current_setting('app.tenant_id', true) (the second argument suppresses the error when the variable is unset) if you want a missing context to evaluate to NULL rather than throw. NULL is the safer default here because tenant_id = NULL is never true, so an unset context returns zero rows instead of leaking everything. Decide that behavior deliberately and write a test for it.

USING versus WITH CHECK

This is the distinction that trips people up, and getting it wrong creates a real vulnerability. A policy has two clauses. USING filters which existing rows are visible for reads, UPDATEs, and DELETEs. WITH CHECK validates rows being written by INSERT and UPDATE. If you only write USING, an INSERT is unrestricted, which means a tenant can create rows belonging to another tenant.

DROP POLICY tenant_isolation ON invoices;

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

For an UPDATE, USING decides which rows you may target and WITH CHECK decides what the row may look like afterward. Without WITH CHECK, a tenant could update a row they own and reassign its tenant_id to someone else, smuggling data across the boundary. Always specify both unless you have a deliberate reason not to. I asked an assistant to draft this policy set and the first pass had exactly this gap.

Prompt: Write a row-level security policy for an invoices table so each tenant can only access their own rows, using a session variable for the tenant id.

Output (excerpt): “CREATE POLICY tenant_isolation ON invoices USING (tenant_id = current_setting('app.tenant_id')::uuid); This restricts each tenant to their own invoices.”

It described the policy as complete, but it omitted WITH CHECK entirely, leaving inserts and tenant-id reassignment unguarded.

That is the failure mode to watch for: the AI produces a policy that reads correctly and is genuinely insecure on writes. The fix is one clause, but you only catch it if you know to look. The Postgres row-level security policy design prompt is structured to force both clauses and the write-path tests into the conversation so this gap does not survive to production.

FORCE ROW LEVEL SECURITY and the owner trap

RLS does not apply to the table owner by default, and it never applies to a superuser. This is the trap that makes people think their policies are broken or, worse, makes them think isolation holds when it does not. If your application connects as the role that owns the table, RLS is silently bypassed and every query sees every tenant.

ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

FORCE ROW LEVEL SECURITY makes the policies apply to the table owner too. The cleaner approach is to never let the application connect as the owner: create a dedicated application role with SELECT/INSERT/UPDATE/DELETE grants but no ownership, and reserve the owner for migrations. Either way, confirm your runtime role is actually subject to RLS, because a role with the BYPASSRLS attribute or superuser status sails straight through every policy. Audit for those: SELECT rolname FROM pg_roles WHERE rolbypassrls OR rolsuper; and make sure your app role is not on that list.

The connection pooling pitfall

This is the one that causes production incidents. Connection poolers like PgBouncer reuse physical connections across requests. If you set app.tenant_id with a session-scoped set_config and the connection is handed to a different tenant’s request without resetting it, you leak. Two defenses, and you want both.

First, set the variable transaction-locally (the true third argument) and wrap each request’s queries in a transaction, so the context cannot survive past commit. Second, in PgBouncer use transaction pooling mode rather than session mode, so a connection is only assigned to a client for the duration of a transaction.

BEGIN;
SELECT set_config('app.tenant_id', $1, true);  -- true = local to this txn
SELECT * FROM invoices WHERE amount_cents > 1000;
COMMIT;  -- app.tenant_id is now cleared

Never set the tenant context with a SET statement that persists for the session and expect a pooler to clean it up for you. The pooler does not know about your application semantics. Bind the context to the transaction and the database enforces the reset.

Testing isolation like an attacker

A policy you have not attacked is a policy you do not trust. Write tests that deliberately try to cross the boundary: set tenant A’s context and assert you cannot read, update, insert, or delete tenant B’s rows.

-- As the application role:
SELECT set_config('app.tenant_id', '<tenant-A>', false);
SELECT count(*) FROM invoices;                          -- only A's rows
INSERT INTO invoices (tenant_id, amount_cents)
  VALUES ('<tenant-B>', 100);                            -- must FAIL (WITH CHECK)
UPDATE invoices SET tenant_id = '<tenant-B>' WHERE id = <A-row>; -- must affect 0 rows

Run these in CI on every migration. AI is excellent at generating this adversarial matrix because it will enumerate the cases you would skip: empty context, NULL context, the owner role, a BYPASSRLS role, cross-tenant insert, cross-tenant update reassignment, and delete. Have it draft the suite, then read every assertion, because a test that passes for the wrong reason is worse than no test.

Performance

RLS adds the policy predicate to every query, so it must be indexable. With tenant_id = current_setting(...)::uuid, ensure tenant_id is indexed, ideally as the leading column of your common composite indexes so the planner can use it for both the policy filter and your WHERE clauses. Watch out for current_setting being treated as volatile in some plans; a STABLE wrapper function or careful query shape keeps the predicate sargable. Measure with EXPLAIN (ANALYZE, BUFFERS) and confirm the tenant filter resolves to an index scan, not a sequential scan over every tenant’s rows. The broader Postgres tuning playbooks in the Postgres category cover the indexing side in depth.

RLS is one of the strongest isolation tools Postgres offers, and it fails quietly when misconfigured. Let AI draft the policies and the adversarial tests, then verify the four things that actually matter: both USING and WITH CHECK are present, FORCE or a non-owner app role is in place, no BYPASSRLS/superuser sneaks through at runtime, and the tenant context resets per transaction under your pooler. Get those right and cross-tenant leakage stops being a bug you can ship.