Multi-tenancy for finance agents, and why row-level security has to be forced

Somewhere in your codebase there is a query that forgot its WHERE tenant_id = $1.

You do not know which one. Neither do we. That is the entire problem, and if you are putting an AI agent in front of that database it stops being a slow-burning risk and becomes a demo you cannot give.

We were brought into an AI accounting platform with exactly this shape: multiple client companies, one Postgres, real invoices and real bank statements, and an agent that reads and writes on a user's behalf. This is how we made cross-tenant leakage structurally impossible instead of carefully avoided.

Application-level isolation is a promise, not a guarantee

Most multi-tenant products isolate in the application. Every repository method takes a tenant id, every query pins it, code review catches the ones that don't. It works, right up until it doesn't:

• A new endpoint ships with a join that filters the parent table but not the child.
• An analytics query gets written against a read replica by someone who has never seen the convention.
• A LEFT JOIN turns a filtered row into a nullable one and quietly widens the result.

Each of those is one careless afternoon. In a normal SaaS product that is a bad bug. In a finance product it is a client seeing another client's payables, which is a breach notification, an audit finding, and usually the end of the contract.

Then you add an agent, and the situation changes in kind rather than degree. The agent composes SQL, or calls tools that compose SQL, from natural language. There is no code review step between the user's sentence and the query. Whatever discipline your team had about remembering the tenant filter, the model does not have it, and cannot be given it — a prompt instruction is a request, not a constraint.

The only defence that survives contact with a language model is one the model cannot address. It has to live below the query.

The failure we were hired to fix was subtler than "no RLS"

Here is the part worth internalising, because it is the version that actually happens to competent teams.

The platform already had row-level security. Roughly seventy tables had ENABLE ROW LEVEL SECURITY and a tenant policy. On paper it was a textbook multi-tenant schema. In practice it was completely inert, for two compounding reasons:

1. The application connected as a role that bypasses RLS by design. In a Supabase-shaped stack that is service_role. Postgres never even evaluated the policies.
2. The policies keyed on a JWT claim that nothing ever set. So even on a role that did respect RLS, the clamp had nothing to clamp to.

Two independent bugs, each of which alone would have made the other invisible. Nobody wrote a bad policy. Somebody wired a good policy to a connection that was never subject to it — and because every query also carried its application-level tenant filter, the system behaved correctly in every test. The safety net was there, hanging in the air, attached to nothing.

If you have RLS today, this is the thing to go and check this afternoon: not whether the policies exist, but whether the role your application actually connects as is subject to them, and whether the thing the policy reads is actually set.

ENABLE is not FORCE

Postgres has a detail here that catches nearly everyone.

ALTER TABLE t ENABLE ROW LEVEL SECURITY does not apply to the table's owner. The owner is exempt. And in most projects the application connects as the same role that ran the migrations — which is to say, the owner. You enable RLS, your tests pass, your queries return everything they used to, and you conclude the policy is working. It isn't being consulted at all.

FORCE is the version that means what you thought ENABLE meant:

ALTER TABLE journal_entries ENABLE ROW LEVEL SECURITY;
ALTER TABLE journal_entries FORCE  ROW LEVEL SECURITY;

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

Two clauses, both required. USING filters what a query can see. WITH CHECK constrains what it can write — without it a tenant can read only its own rows and still insert a row stamped with someone else's id. Reads and writes are separate battles, and Postgres rejects the second one itself with a 42501, not with application code an agent could route around.

The mirror-image failure: enabled, with no policy

There is a second way to get this wrong, and it is the one that actually took the platform down.

RLS enabled on a table with no policy at all does not mean "no restriction." It means deny everything. Every query returns zero rows, silently, with no error.

We hit this the first time the service deployed onto a managed Postgres host. Nobody could sign in. The host publishes the public schema through its own REST API, so it helpfully enables RLS on every table it doesn't see configured — and separately grants its anon and authenticated roles on all of them. Both defaults were wrong, in opposite directions at the same time:

• RLS-on-no-policy turned the handful of deliberately policy-free tables — the pre-auth lookups, read before a tenant scope exists — into deny-all. First sign-in couldn't write its own profile row.
• The grants were the more serious half. The anon key ships in the frontend bundle. Those grants quietly made RLS the only thing standing between a published browser key and the ledger. RLS was written to separate tenants from each other, not to serve as an API gateway.

The fix was to state the schema's posture explicitly rather than inherit the host's — revoke first, disable second, so nothing is exposed in the gap between. And then to make the trap un-reintroducible: a migration that enumerates the catalog and refuses to apply if any table has RLS on with no policy attached.

If you are on a managed Postgres that fronts your schema with an auto-generated API, go and check what your anon role can reach. It is a different question from whether your policies are correct, and it is usually the more urgent one.

Then take the bypass away from yourself

A policy you can turn off is a policy an attacker can turn off. So the application gets its own role, and that role is deliberately unable to do the things that would make RLS optional:

CREATE ROLE app_rw NOLOGIN;
ALTER  ROLE app_rw NOBYPASSRLS;              -- cannot bypass RLS, ever
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_rw;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw;

Not a superuser. Not the table owner. No DDL. The migration role — the owner, the one that can bypass — is quarantined to migrations and is not reachable from any request path. In this codebase that quarantine is a type: the migration connection is a different Go interface from the query pool, so "run a live query on the privileged connection" is a compile error rather than a silent bypass.

That combination is what makes the guarantee real. FORCE closes the owner exemption. NOBYPASSRLS closes the role exemption. Owning no tables makes the first question moot. Now there is no connection in the running system that can see two tenants' rows, including the one the agent is holding.

Bind the tenant to the transaction, not the connection

The policy reads current_setting('app.current_tenant'). Something has to set it, and how you set it is where the last real bug lives.

func (p *Pool) WithTenant(ctx context.Context, tenant core.TenantID, fn func(Tx) error) error {
	tx, err := p.pool.Begin(ctx)
	if err != nil {
		return fmt.Errorf("postgres: begin: %w", err)
	}
	defer func() { _ = tx.Rollback(ctx) }() // no-op after a successful commit

	if _, err := tx.Exec(ctx,
		"SELECT set_config('app.current_tenant', $1, true)", string(tenant)); err != nil {
		return fmt.Errorf("postgres: set tenant scope: %w", err)
	}
	if err := fn(pgxTx{tx: tx}); err != nil {
		return err
	}
	return tx.Commit(ctx)
}

The whole design is in that third argument. set_config(..., true) is local — the setting dies with the transaction. A plain SET would attach the tenant to the connection, and connections come from a pool.

(It is set_config rather than SET LOCAL for a specific reason: SET LOCAL cannot take a bind parameter, so using it would mean concatenating a tenant id into SQL text. set_config's third argument is the same is_local flag, and the tenant stays a parameter.) Request A finishes, its connection goes back to the pool, request B for a different tenant picks it up and inherits A's clamp. That is the worst possible bug: silent, load-dependent, impossible to reproduce locally, and it leaks in whichever direction the pool happens to hand out connections that second.

Transaction-scoped means there is no state to leak. It also means there is exactly one way to reach data — through WithTenant — and it takes the tenant as a required argument. You cannot forget it, because there is no call that omits it.

The tenant itself comes from the verified session, never from the URL and never from the request body. A Tenant-Id header is a consistency check, not an input.

Fail closed, loudly

What happens if a query runs with no tenant set?

The wrong answer is "returns nothing" — that is a silent, confusing, data-loss-shaped bug. The much worse answer is "returns everything." The right answer is that it raises, immediately, because current_setting on an unset key errors rather than returning null.

That is asserted, not assumed:

-- No tenant context: a SELECT must ERROR, not return rows and not return none.
DO $$
DECLARE n int;
BEGIN
    BEGIN
        SELECT count(*) INTO n FROM journal_entries;
        RAISE EXCEPTION 'FAIL-CLOSED VIOLATION: tenantless query saw % row(s)', n;
    EXCEPTION
        WHEN undefined_object OR invalid_text_representation THEN NULL;  -- expected
    END;
END $$;

There is a second test right after it that sets a clamp, commits, and then re-queries on the same connection to prove the clamp did not survive the commit. That is the pool-leak bug from the previous section, pinned so it cannot come back.

The part most teams skip: proving it stays true

Getting isolation right once is an afternoon. Keeping it right across a year of migrations written by four people and a coding agent is the actual engagement. Correctness that depends on everyone remembering a convention is the same class of thing we just eliminated at the query layer — so we eliminated it at the schema layer too.

Two independent gates, both required to merge:

A schema linter over the migration text. It reads every migrations/*.up.sql, aggregates statements across files, and fails the build when a new tenant-scoped table is missing any part of the boilerplate: a tenant_id column with a foreign key, an index whose leading column is tenant_id, ENABLE, FORCE, and a policy keyed on the tenant setting. A table that is genuinely global has to say so out loud with a -- schemalint:global marker. It also fails on two specific historical scars: a second identity table competing with tenants, and the reappearance of any of the three legacy tenant-key column names the system used to have. Those had already been paid for once.

A catalog assertion against a real database. The linter reads SQL text, so anything clever enough to dodge the regex would pass. So CI applies every migration to a real Postgres and then interrogates pg_class directly: every table carrying a tenant_id must have relrowsecurity, relforcerowsecurity, and at least one policy. The same test asserts the role posture — that the app role exists, is not a superuser, does not have BYPASSRLS, and owns zero tables — and that it holds SELECT but not INSERT/UPDATE/DELETE on the pre-auth token table, so a compromised app role cannot mint credentials.

Thirty-three tables carry the forced-RLS boilerplate today. The number is not the point. The point is that adding the thirty-fourth without it fails CI, in two different ways, before a human reads the diff.

What honestly stays outside the clamp

A case study that claims a clean sweep is lying, so here is the residue.

A handful of tables cannot be tenant-clamped, because they are what resolves the tenant: the API-token table the request is authenticated against, and the workspace-identity tables read while a user is logged out — listing which workspaces they belong to, checking a signup conflict, accepting an invite. You cannot clamp to a tenant you have not established yet.

Those tables get three things instead of a policy. They are named explicitly in the coverage test's exclusion list, each with the reason written next to it, so "exempt" is a decision someone made rather than a table that slipped through. Their access rules are asserted behaviourally in their own tests. And the code that reaches them goes through separate, deliberately ugly methods — QueryRowGlobal, WithGlobalTx — whose doc comments say what they are for, used in four files. The token verifier holds an interface containing only the global read, so it structurally cannot reach the tenant-scoped path or any write helper.

The app role also holds SELECT but not INSERT, UPDATE, or DELETE on the token table, which is asserted in the same test. Tokens are minted by a separate operator binary on the migration connection. A compromised application cannot issue itself credentials.

Two more things we would tell you before you start:

• Check your pooler mode. A transaction-scoped clamp is safe under transaction pooling, but a driver's prepared-statement cache often is not — this cost us an afternoon of confusing 42P05/08P01 errors against a hosted pooler before we pinned the connection mode and put a startup warning on the known transaction-pooler ports.
• Type your tenant column deliberately. A uuid tenant column makes a missing or garbage setting fail loud with a cast error. A text one matches zero rows instead. Same isolation, worse failure mode, and you want the loud one.

What this costs

Less than teams expect.

The runtime cost is one extra statement per transaction — a set_config on a connection you were already opening. Policy evaluation is a predicate Postgres folds into the plan; because every tenant-scoped index leads with tenant_id, it lands on the index you were already using. We have not had to trade a query plan for it.

The real cost is design discipline: you get one path to data, every table looks the same, and clever schema is not allowed. That is a feature. A migration too clever for the linter is too clever for the system.

The payoff is that isolation stops being a property of your team's attention and becomes a property of the database. When a prospect's security review asks how you prevent cross-tenant access, the answer is not "we're careful in our repository layer." It is: the connection the application holds is structurally incapable of seeing another tenant's rows, here is the test that proves it, and it runs on every pull request.

An LLM-authored SELECT * FROM journal_entries returns one tenant's rows. Not because we asked it nicely.

---

Building this on your stack

If you are putting an agent in front of customer data and any of this sounds like your architecture — RLS you are not certain is active, a service role in the request path, a tenant filter that lives in application code — that is the engagement we do.

Typically two to three weeks: audit what your policies actually evaluate to, split the roles, move the clamp into a single tenant-scoped transaction path, and leave you with the CI gates so it stays true after we go. You own the code and the tests from day one.

Talk to us — 45 minutes is usually enough to tell you whether your RLS is real.