mcp-rls-auth-pattern

A reference implementation of one thing: authenticating an MCP server against a multi-tenant PostgreSQL database so that tenant isolation is enforced by the database rather than by the tool handlers, without a service-role key anywhere on the request path. It is written for someone who is building an MCP server over data belonging to more than one customer and has to decide where the tenant boundary lives. It is not a library and not a starting template — it is a worked example with its reasoning written down, meant to be read and adapted.

The problem

Here is the shape almost every multi-tenant MCP server takes, and it is the shape the tutorials show.

You authenticate the caller somehow — an API key, a bearer token — and resolve them to a user and an organization. You hold a service-role database key, because the server needs to read data on behalf of whoever is calling. Then each tool handler queries with that key and filters by the tenant you resolved:

// The common approach. Not a strawman — this is what most examples do.
const { data } = await supabaseAdmin
  .from('documents')
  .select('*')
  .eq('org_id', caller.orgId)   // <- the entire security model
  .limit(limit);

This works. It is easy to follow, it is what the client libraries make natural, and if you have written it, you have written the thing everyone writes. The problem is not that it is wrong today. The problem is what it depends on.

The security property lives in application code. A service-role key carries the BYPASSRLS attribute, so every row-level security policy on the database is simply not consulted for any query it makes. Whatever policies exist are decoration. The only thing standing between one customer and another customer's data is that .eq() on line four.

Every new tool is a fresh chance to forget it. The first tool has the filter, because you were thinking about tenancy when you wrote it. The eleventh tool is written eight months later by someone onboarding, who copies the tenth tool, which had a filter, so that is fine. Then someone adds a tool that aggregates across a join, or one that takes a document id directly, or one that searches. The filter has to be correct in every one of them, forever, including in the ones added under time pressure.

The failure mode is silent and looks like working software. A forgotten filter does not throw. It does not log a warning. It returns rows — more rows than it should, from tenants the caller has never heard of — and the tool returns them to a language model, which summarises them helpfully. There is no error to notice, no alert to fire, and nothing in a test suite catches it unless someone thought to write a test with two tenants' data present. In the overwhelmingly common case where a development database has one organization in it, the missing filter changes nothing at all until production.

And the direction of failure is the worst one. When the tenant filter is the security model, a bug in it exposes data. That is the wrong way round. You want the failure mode where a bug returns nothing, because nothing is visible and gets reported in an hour, whereas silently returning everything is discovered by a customer.

None of this is an argument that the developers writing it are careless. It is an argument that the design puts a correctness obligation in the place where obligations are least reliably met: repeated by hand, in every new piece of code, indefinitely.

Related MCP server: PostgreSQL MCP Server

The approach

Move the tenant boundary into the database, and make every request run as the calling user rather than as an administrator.

A bearer token arrives. It is hashed and looked up — the only step that needs privilege, because no identity exists yet. That yields a user, whose status is rechecked, and an encrypted refresh token, which is decrypted and exchanged for a short-lived access token. That access token is put in the Authorization header of an anon-key client, and every subsequent query runs through it. PostgREST verifies the JWT, auth.uid() resolves to the caller, and the row-level security policies add the tenant predicate to every statement.

   MCP client
       |
       |  POST /mcp    Authorization: Bearer <raw token>
       v
   +-------------------------------------------------------------------+
   |  handleMcpRequest                     src/server/handler.ts       |
   +-------------------------------------------------------------------+
       |
       v
   +-------------------------------------------------------------------+
   |  resolveCaller                        src/auth/resolve-caller.ts  |
   +-------------------------------------------------------------------+
       |
       |  (1) loadConnection(rawToken)      PRIVILEGED -- service role
       |      +----------------------------------------------------+
       |      |  read 1  mcp_tokens         by sha256(token)       |
       |      |  read 2  app_users          by id -> org, status   |
       |      |  read 3  mcp_token_secrets  by token_id            |
       |      +----------------------------------------------------+
       |          src/auth/token-store.ts
       |          the only file that touches a service-role key
       |
       |  (2) decrypt the refresh token     src/auth/crypto.ts
       |  (3) exchange it for an access token   src/auth/session.ts
       |  (4) write back the rotated refresh token  -- before returning
       |  (5) touch last_used_at            -- awaited, not fire-and-forget
       |
       v
   +-------------------------------------------------------------------+
   |  anon-key client                      src/auth/anon-client.ts     |
   |  Authorization: Bearer <the caller's access token>                |
   +-------------------------------------------------------------------+
       |
       |   from here on, nothing is privileged
       v
   +-------------------------------------------------------------------+
   |  list_documents                       src/server/tools.ts         |
   |                                                                   |
   |    select id, title, body, created_at                             |
   |      from documents                                               |
   |     order by created_at desc                                      |
   |     limit $1                                                      |
   |                                                                   |
   |    no org filter. no tenant predicate. none.                      |
   +-------------------------------------------------------------------+
       |
       v
   +-------------------------------------------------------------------+
   |  PostgreSQL                                                       |
   |                                                                   |
   |    the policy appends:  where org_id = current_org_id()           |
   |    current_org_id() reads auth.uid(), which comes from the JWT    |
   +-------------------------------------------------------------------+

Open src/server/tools.ts and look at the query in listDocuments. There is no .eq('org_id', ...). There is no tenant argument. There is nothing in that function that knows or can discover which organization is asking.

That absence is the whole design. It is not an omission to be fixed. A handler cannot forget a filter that does not exist, and the eleventh tool added in eight months' time is scoped correctly for the same reason the first one is: because the database scopes it. The failure direction inverts too — if the caller's identity is somehow wrong or missing, an unfiltered query matches no policy and returns zero rows. Broken authentication shows up as an empty list, not as a breach.

Three tests in test/tools.test.ts assert the absence directly, against a recording stub, so that adding a filter fails CI.

Why these specific choices

Two tables for the token and its secret, not two columns

A user is allowed to list their own tokens; that is an ordinary product feature and it should not require a privileged client. But the refresh token behind a connection must never be reachable through that read.

Keeping both in one table would make that boundary a column-selection convention — every query, forever, remembering not to select *. Splitting them makes it structural: mcp_tokens has an owner-read policy, mcp_token_secrets has no authenticated policy and no grant at all. There is no column list a user could ask for that reaches the secret, because the secret is not in a table they can read.

Relatedly, mcp_tokens deliberately has no org_id column. The organization resolves through user_id -> app_users.org_id at request time. A second stored copy of the tenant is a copy that can disagree with the first — when a user is moved between organizations, when a backfill misses rows, when one write path updates only one of them. The failure is not a crash; it is a token that keeps authorising access to a tenant the user left.

Three sequential reads instead of one embedded join

PostgREST can express the token lookup as a single request with embedded relations, and it is tempting: one round trip instead of three.

Embedded selects resolve against PostgREST's schema cache. When that cache is stale — right after a migration, after a replica swap, whenever a relationship was added but the cache not reloaded — the request fails with a generic "could not find a relationship" error. On the authentication path, that converts every clean typed rejection into an opaque 500 for every user at once, and the stack trace points at a select rather than at a cache. Three primary-key reads against a warm connection are cheap. Trading that for an auth outage during a deploy is not a good trade.

Rechecking user status on every request

A valid token proves which identity is calling. It says nothing about whether that identity is still allowed in.

Those are different questions with different lifetimes. The token was minted once, months ago. The answer to "is this person still an active member of this organization?" changes the moment someone is offboarded. Nothing upstream will do this for you: an external auth provider knows about identities, not about your application's status column, and will keep vouching for a user you disabled this morning. Deactivation is an application fact, so it is enforced on the application's own request path, on every request.

Writing the rotated refresh token back before returning

Refresh tokens rotate on use: redeeming one invalidates it and issues a replacement. After the exchange, the replacement exists only in a local variable.

If that write is skipped, deferred until after the response, or fired without being awaited, the database still holds the retired token — and the next request on that connection redeems something the provider has already invalidated. The symptom is a connection that works exactly once and then breaks, which is genuinely hard to debug because nothing errored at the moment the damage was done. So the write is ordered before the return and awaited, and a failure there is a 500 rather than a success, because completing the request would consume a refresh token whose replacement was never stored.

Lazy key derivation, from a dedicated variable

Two separate decisions, both departures from what this pattern is commonly built on.

A dedicated ENCRYPTION_KEY. The shortcut is to derive the encryption key by hashing a secret the service already has — often the service-role key. Do not. It welds two secrets' lifecycles together: rotating the database key silently renders every encrypted column undecryptable, and leaking either one compromises both. Independent secrets should be independently rotatable.

Derived lazily, on first use, never at module load. A module-load throw is contagious in a way that is hard to debug: anything that so much as imports the file — a test exercising only the hashing helper, a bundler tracing the import graph — explodes at import time with a configuration error, and the stack trace points at the import rather than at the missing variable. Deferring the check turns "the build is broken" into "this one call needs a key". A block of tests in test/crypto.test.ts pins this down: moving derivation back to module load turns six of them red.

The two scrub rules

npm run scrub enforces two separate boundaries, reported separately.

The privilege rule is the real one. SUPABASE_SERVICE_ROLE_KEY may appear only in src/auth/admin-client.ts. That key is what grants BYPASSRLS — wherever it can be read, every policy in supabase/migrations stops applying. Constructing a client is harmless without it; possessing it is the privilege.

The construction rule is a reflex guard. createClient and value imports of the client library are confined to admin-client.ts and anon-client.ts. A violation here is not by itself a security bug — building an anon client somewhere new is merely untidy. It is worth catching because every Supabase tutorial opens with createClient(), so reaching for it in a new file is muscle memory rather than a decision, and the same reflex is how a privileged client ends up somewhere it should not be. That case is caught by the privilege rule.

Both are enforced in CI and both have been proven to fail by deliberately violating them. See the limitations section for what they do not catch.

Running it

Requires Node 20 or newer and a Supabase project (or PostgreSQL with the Supabase auth schema, an auth.uid() reading request.jwt.claims, and anon / authenticated / service_role roles).

npm install

Generate an encryption key:

npm run keygen

Set four environment variables. Only one of them is a secret:

Apply the migrations in order, through the Supabase CLI or psql:

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f supabase/migrations/0001_schema.sql
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f supabase/migrations/0002_tokens.sql

Typecheck, test, and run the boundary checks. The whole test suite runs offline — no credentials, no network, no database:

npm run typecheck
npm test
npm run scrub

The cross-tenant isolation harness needs a real database and is run separately. See supabase/verification/README.md, and read the limitation below before trusting it.

psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f supabase/verification/isolation_fixture.sql
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f supabase/verification/isolation_harness.sql
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f supabase/verification/isolation_teardown.sql

To serve it, adapt src/server/handler.ts. It takes a standard Request and returns a standard Response, so wiring it into a Next.js route handler, a Hono app, or a Cloudflare Worker is a one-line adapter.

Limitations

Stated plainly, because a reference implementation that oversells itself is worse than none.

Concurrent requests on one connection can race

Two requests carrying the same bearer token will both call loadConnection, both read the same stored refresh token, and both redeem it. The first redemption succeeds; the second presents a token the provider has already retired. Both then write back their own result, so whichever write lands last may store a refresh token that the other request already invalidated. The connection is dead on the next request, and the user sees a working integration break for no visible reason.

This is documented rather than fixed. The obvious fix — a lock around the refresh — serialises every request on a connection and puts a distributed locking dependency on the hot path. The real fix is to stop refreshing on every request: cache the short-lived access token, keyed by token id, until shortly before it expires, and touch the refresh token only on a cache miss. Concurrent requests then share a cached access token and never race, because almost no request redeems anything. That sidesteps the race rather than locking against it. It is left out to keep the auth flow readable end to end, and it is the first thing to build before running this at any real concurrency.

The isolation harness has never been run

supabase/verification/ has not been executed against any database. It was written and checked against the PostgreSQL grammar. That is all. Nothing in this repository should be read as evidence that cross-tenant isolation has been empirically verified.

The gap is wider than "it parses" suggests. The outer SQL of all three files parses cleanly against the real PostgreSQL 17 grammar. The PL/pgSQL inside every $$ ... $$ block does not — to the SQL grammar, a function body is an opaque string literal. This parses without complaint:

do $$ begin this is definitely not valid plpgsql at all ((( ; end; $$;

Most of the harness's logic lives inside exactly such a block, so a clean parse says almost nothing about it. Treat the first real run as a debugging session, not a verification. The verification README lists what that run is most likely to catch.

The scrub rules are greps, not proofs

They will not notice a client hand-rolled some other way — a raw fetch against the PostgREST endpoint with a privileged key in a header matches none of the construction patterns. They will not notice a key read indirectly, through process.env[name] or a config module. They will not notice an aliased re-export, a privileged client passed in as an argument, anything outside src/, or a deliberately obfuscated module name.

Their value is narrower and still real: they make the ordinary, accidental version of the mistake fail in CI rather than in review.

This is a reference implementation, not a library

There is no package published, no semantic versioning, no stability guarantee, and no upgrade path. File layout and function signatures will change if the example is improved. Read it, take the parts that apply, and adapt them to your schema. Depending on it directly is not a supported thing to do.

What this is not

• Not a drop-in dependency. See above.

• Not audited. No third party has reviewed this. The reasoning is written down so you can evaluate it yourself, which is not the same as it being correct.

• Not a complete MCP server. One tool, list_documents, exists to show where the tenant filter is not. There are no resources, no prompts, no streaming, no pagination.

• No OAuth flow. Tokens are assumed to exist already. Issuing them, the consent screen, the callback, and the initial refresh-token exchange are all out of scope; issueToken and rotateSecret are where that flow would connect.

• Not a performance reference. The request path does three database reads and a token exchange before any tool runs. That is a deliberate trade for legibility, and the access-token cache described above is the first thing to add.

License

MIT. See LICENSE.