How we isolate tenants with Postgres RLS

Why not just add a WHERE clause?

The obvious way to build a multi-tenant SaaS backend is application-level filtering: every query gets a WHERE tenant_id = $1, and every route handler is responsible for remembering to add it. This works right up until one route doesn't. A missed join condition, a copy-pasted query that dropped the filter, a new endpoint written by someone unfamiliar with the convention, an admin-only query mistakenly reused on a tenant path — any one of these leaks another tenant's rows, and the bug lives entirely in code that has to be reviewed by humans, forever, on every PR that touches a query.

Row-Level Security (RLS) moves that guarantee out of application code and into the database. Instead of trusting every call site to filter correctly, you tell Postgres: this role may only ever see rows where tenant_id matches a session variable, full stop, for every SELECT, INSERT, UPDATE, and DELETE against that table. The enforcement point moves from "every engineer, every time" to "the database, always." A query that forgets its WHERE clause doesn't leak — it just returns zero rows, or fails a check constraint on write.

The shape of it: tenant_id everywhere, one policy

Every tenant-owned table in this platform's schema carries a tenant_id text NOT NULL column — environment, contract, mcp_server, tool, audit_event, and the newer tables added for API contracts, tool policies, credentials, and roles (api_provider, api_operation, mcp_server_version, mcp_resource, mcp_prompt, tool_policy, oauth_provider, oauth_client, credential_reference, secret_reference, rate_limit_policy, role, membership). All of these get the identical policy, applied in server/db.js by looping over the table list and running the same CREATE POLICY template:

Two clauses, doing two different jobs. USING governs what the role can read — any row where tenant_id doesn't match the session variable simply isn't visible, as if it doesn't exist. WITH CHECK governs what the role can write — an INSERT or UPDATE that would produce a row failing that condition is rejected outright. Together they mean a connection scoped to tenant A cannot read tenant B's rows, and cannot write a row that claims to belong to tenant B either.

Not every table gets this policy. mcp_client, marketplace_listing, subscription, and a handful of other genuinely platform-owned tables are deliberately left out of the RLS loop — they're cross-tenant by design (a marketplace listing, for instance, is meant to be visible across the whole platform) and are read through the plain, non-tenant-scoped pool instead. The comment in the schema calls this out explicitly so it isn't accidental.

Setting the session variable: withTenant()

A Postgres session variable like app.tenant_id doesn't set itself — something in the request path has to set it, per request, before any query runs. That's the job of withTenant() in server/db.js:

Every tenant-facing route wraps its DB work in this function — server/routes/contracts.js, server/routes/credentials.js, server/routes/marketplace.js, and server/routes/operate.js all call withTenant(req.tenantId, ...) rather than touching the pool directly. req.tenantId itself comes from the session middleware in server/session.js, which verifies the signed mcp_sess cookie and sets req.tenantId = payload.tenant — the tenant ID is never taken from a client-supplied header or body field on an authenticated route; it comes out of a value the server itself signed at login.

The true third argument to set_config is what makes this safe to run on a pooled connection: it scopes the setting to the current transaction, not the whole session. Once the transaction commits or rolls back, the setting is gone. Because pg.Pool hands the same physical connection to different logical requests over time, a session-scoped (non-transactional) app.tenant_id would risk one request's tenant context leaking into the next request that happens to reuse the same connection. Transaction-scoped set_config closes that gap — every unit of work gets its own BEGIN ... set_config ... COMMIT, so there's no window where a connection carries stale tenant context into someone else's query.

Two Postgres roles, and why RLS needs both

RLS policies only bite for roles the database actually enforces them against. Postgres exempts table owners from RLS unless a table is explicitly set to FORCE ROW LEVEL SECURITY, and any role with BYPASSRLS skips row security entirely regardless of ownership. That's not a bug to work around — it's the exact mechanism this platform uses to separate two legitimately different jobs:

• The owner role (mcp) creates tables, runs migrations, and seeds data. It needs to see and write every tenant's rows unconditionally, and Postgres' owner-exemption gives it that for free without a special grant.
• The runtime role (mcp_app) is what the actual request-handling process connects as. It is not the table owner and does not have BYPASSRLS — which means the tenant_isolation policy applies to every query it runs, no exceptions.

The schema setup uses ENABLE ROW LEVEL SECURITY with NO FORCE (not FORCE) specifically so the owner role can keep running migrations and seed scripts unhindered, while the app role — which is never the owner — gets full enforcement. On Cloud SQL, no unprivileged role can hold BYPASSRLS anyway, so FORCE would make owner-run migrations impossible without buying any additional safety; NO FORCE plus a strict non-owner app role gets the same guarantee more simply.

Because that split is easy to get wrong at deploy time — imagine a misconfigured DATABASE_URL that accidentally points the app at the owner credentials — server/db.js also ships assertRlsEnforced(), a boot-time check that queries pg_roles for the connected role and refuses to start if it's a superuser or has BYPASSRLS:

It goes a step further than checking rolsuper/rolbypassrls directly, too. RLS's owner-exemption doesn't just apply to the literal owner — it exempts any role that has the owner's privileges via role membership (pg_has_role(..., 'USAGE')). Cloud SQL makes every role a member of cloudsqlsuperuser by default, so if an RLS-protected table ever ended up owned by that shared role — say, after a gcloud sql import run with elevated credentials — the app role would silently inherit owner-exemption and stop being subject to RLS even though it's neither a superuser nor BYPASSRLS on paper. assertRlsEnforced() checks for exactly this by scanning pg_class for RLS-enabled tables the current role has owner-equivalent privileges on, and refuses to boot if it finds any. This check exists because that gap is real and has previously let one tenant's mcp_server/tool rows become visible to another.

The deliberate exception: the admin BYPASSRLS pool

Not every legitimate use case fits inside a single tenant's boundary. A super-admin console needs cross-tenant aggregates — total server counts across every tenant, platform-wide usage, and so on — and RLS, working as designed, would make that impossible from the normal tenant-scoped connection. The platform's answer is a second, entirely separate connection pool: server/admin-db.js.

Three things make this a safe exception rather than a backdoor. First, it's a completely distinct pool from the tenant-facing one in db.js — no shared connections, no code path where a tenant request could accidentally pick up the admin pool. Second, its own header comment is explicit and repeated about the boundary: only admin.js, only behind requireAdmin, never on a tenant route. Third, and most importantly, it doesn't disable the isolation model — it deliberately steps outside it, in exactly one narrow place, for exactly one job (cross-tenant reporting for platform operators), while every tenant-facing route continues to go through withTenant() and the enforced mcp_app role. The existence of a documented, narrowly-scoped bypass is itself evidence the default path is genuinely locked down — if RLS could be trivially routed around from tenant code, there'd be no need for a separate pool, a separate role, and a separate set of comments warning engineers away from it.

What this actually defends against

Concretely: imagine a new route is added to server/routes/ that lists a tenant's tools, and the engineer writing it forgets to scope the query — or copies a query from an admin script that never had a tenant filter to begin with. In an application-level-filtering world, that ships, passes review because nothing looks obviously wrong, and quietly returns every tenant's tools to whoever calls that endpoint. In this platform's world, that same buggy handler still runs inside withTenant(req.tenantId, ...) (or, if it doesn't, it's making a query as a role that Postgres itself won't let see other tenants' rows). The missing WHERE clause doesn't matter — the database was never going to hand back those rows regardless of what SQL the missing filter would have produced. The bug becomes a functional issue for that one tenant to catch in QA, not a cross-tenant data breach.

Verify it yourself

Sign up for two tenants, note each tenant's ID, and hit GET /v1/bootstrap with one tenant's session cookie. You'll get back that tenant's contracts, servers, and tools — never the other's, no matter how the query behind that endpoint is written. That's the guarantee: it holds at the database, not in the route handler you happen to be reading today.