# 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:

```ts
// 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.

## 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`](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`](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`](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).

```sh
npm install
```

Generate an encryption key:

```sh
npm run keygen
```

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

| Variable | What it is |
| --- | --- |
| `SUPABASE_URL` | Your project URL. |
| `SUPABASE_ANON_KEY` | The public key. Not a secret — it ships in browser bundles, and grants nothing on its own because RLS denies by default. |
| `SUPABASE_SERVICE_ROLE_KEY` | Bypasses RLS entirely. Read by exactly one file. Treat as a database password. |
| `ENCRYPTION_KEY` | 32 bytes, base64, from `npm run keygen`. Encrypts stored refresh tokens. Rotating it makes existing ciphertext unreadable. |

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

```sh
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:

```sh
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`](supabase/verification/README.md), and
read the limitation below before trusting it.

```sh
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`](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:

```sql
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](LICENSE).