Building an MCP server for a database

I published turkerdev/mysql-mcp in April: a server exposing a MySQL database over the Model Context Protocol. The interesting part is not the protocol, which is small — it is that the specification deliberately has no opinion about authorisation, which means every constraint is the server’s to invent.

The symptom

the thing this replaced: answering schema questions by
hand, from a terminal, repeatedly.

  "which table holds the delivery address?"
  "is there an index on orders.customer_id?"
  "how many rows in events?"
  "what does retention_class = 2 mean?"

all of which are answerable from the schema, none of
which are in any document, and each of which
interrupted somebody.

Why a protocol rather than a script

A script is written for one client and a protocol server is written once and consumed by any client that speaks it. That is worth the additional structure only if the surface is specified — and specifying the surface is the part that makes it safe rather than the part that makes it reusable.

The fix

The three primitives, and which a database needs

  tools       callable, with a JSON Schema for the
              arguments. the client decides when to
              invoke one.
  resources   readable, addressed by URI. offered
              rather than invoked.
  prompts     templates the client can present.

for a database:

  the schema      a resource. it is a fact, it does not
                  change per request, and it can be
                  read without being asked for.
  a query         a tool. it takes an argument and has
                  a cost.
  prompts         none. this server has no opinion
                  about how it is used.

Exposing the schema as a resource rather than as a describe_table tool removes an entire class of round trip — the client has it before it asks anything. It also means the schema is fetched whether or not it is needed, which on a database with four hundred tables would be the wrong choice and on ours is forty kilobytes.

The first design, which was wrong

// v0.1
server.tool(
  'query',
  { sql: z.string() },
  async ({ sql }) => ({ content: [{ type: 'text', text: await run(sql) }] }),
)

// which is a shell. the credential is the only
// constraint, and a read-only credential permits:
//   SELECT * FROM events            -- 900M rows
//   SELECT ... a cartesian join     -- until the
//                                      connection dies
//   SELECT ... INTO OUTFILE         -- if the grant
//                                      allows it

A read-only credential is the floor rather than the protection. It prevents writes and permits a query that reads every row of every table, which is a denial of service against the production database expressed in one line of the most natural possible tool design.

Authorisation, which the protocol delegates entirely

the specification's position: authorisation is the
host's concern. a server is invoked by a client the
host has configured, and what the server exposes is
whatever it was given access to.

which is the right layering and means:

  the credential is not a security boundary
  the client is not a security boundary
  the server is the only place a constraint can live

so the server has to have all of them.

Five constraints

const REFUSED: Array<[RegExp, string]> = [
  [/^s*(INSERT|UPDATE|DELETE|REPLACE|TRUNCATE)b/i, 'writes'],
  [/^s*(CREATE|ALTER|DROP|RENAME)b/i, 'DDL'],
  [/^s*(GRANT|REVOKE|SET|FLUSH|KILL)b/i, 'administration'],
  [/bintos+(out|dump)fileb/i, 'file access'],
  [/;s*S/, 'more than one statement'],
]

// and the four that are not pattern matching
await conn.query('SET SESSION MAX_EXECUTION_TIME = 10000')

const sql = `SELECT * FROM (${userSql}) AS q LIMIT 5000`

if (!ALLOWED_SCHEMAS.includes(conn.config.database!)) throw new Error(...)

// plus a credential with SELECT on one schema and
// nothing else — which is the floor, not the ceiling.

each constraint is individually bypassable:

  the pattern list      a comment, a leading newline,
                        a CTE. this is the weakest one
                        and it is a fast rejection
                        rather than a boundary.
  the subquery limit    defeated by a query whose
                        cost is in the scan rather
                        than in the result set.
  MAX_EXECUTION_TIME    the real protection against
                        that.
  the schema allow-list a cross-schema reference in a
                        query still needs the grant,
                        which is not there.

the server is exactly as safe as its narrowest
constraint, and that is the execution timeout.

Being explicit about which constraint is load-bearing is the useful part of this. The pattern list looks like the security and is the least reliable of the five — it is there to reject obvious mistakes quickly, and the execution timeout plus the grant are what actually bound the damage.

The schema resource

server.resource('schema', 'schema://tables', async () => ({
  contents: [{
    uri: 'schema://tables',
    mimeType: 'application/json',
    text: JSON.stringify(await describeAllTables()),
  }],
}))

// and per table, for a client that wants one
server.resource(
  'table',
  new ResourceTemplate('schema://tables/{name}', { list: undefined }),
  async (uri, { name }) => ({ contents: [await describeTable(uri, name)] }),
)

A resource template with a variable is how the protocol expresses a family of addresses, and setting list to undefined means the client cannot enumerate them — it has the index from the first resource and does not need a second listing. That is a small decision and it halves the surface.

What it will not do

  write anything                    ever
  DDL                               ever
  run against production            the configuration
                                    names a replica,
                                    and the production
                                    credential does not
                                    exist in it
  return more than 5,000 rows
  run for more than 10 seconds
  reach a schema outside the list

and the one that is a judgement rather than a
constraint: it returns row data. a server that
returned only aggregates would be safer and would not
answer the questions it exists for.

Testing a server whose client is a model

// a harness that speaks the protocol, not a mock
const client = new Client({ name: 'test', version: '1.0.0' })
await client.connect(new StdioClientTransport({
  command: 'node', args: ['dist/index.js'],
  env: { DATABASE_URL: fixtureUrl },
}))

test('refuses a write', async () => {
  await expect(
    client.callTool({ name: 'query', arguments: { sql: 'DELETE FROM t' } }),
  ).rejects.toThrow(/writes are not permitted/)
})

the refusal cases are the suite. 41 of 52 tests assert
that something is NOT possible:

  every REFUSED pattern, plus obvious evasions
  a query returning 5,001 rows
  a query that runs for 11 seconds
  a cross-schema reference
  a second statement after a semicolon
  a comment before a write

and the 11 that assert something works.

A suite that is eighty per cent refusals is the shape of a security-relevant server and is the opposite of the usual ratio. The evasion cases are the ones worth writing carefully — a comment before a write is the first thing anybody tries and the pattern list handles it only because somebody thought to test it.

A week of real use

  queries run             188
  refused                  22
    of which: a write        1  (a genuine mistake)
              over the limit  14
              timed out        7
  questions answered
    without interrupting
    anybody                  ~40, by estimate

the fourteen over-limit rejections are the interesting
number: a client asking for everything is the default
behaviour, and the LIMIT wrapper is what makes that
harmless rather than an incident.

Verifying it worked

$ npm test
  52 passed (41 refusal cases)

$ npx @modelcontextprotocol/inspector node dist/index.js
  resources: 2   tools: 1   prompts: 0

# the credential, asserted
$ mysql -u mcp_reader -e "SHOW GRANTS" | tail -1
GRANT SELECT ON `app_replica`.* TO `mcp_reader`@`%`

# and a deliberate check that the timeout is the one
# doing the work
$ ./bin/probe --sql 'SELECT COUNT(*) FROM events e1, events e2'
  refused after 10.0s: query execution was interrupted

Probing with a cartesian join is the test that confirms which constraint is actually load-bearing — the pattern list permits it, the row limit does not apply because the result is one row, and the timeout is what stops it. That is the case the design document names and it is worth proving rather than asserting.

What this costs

A server that is exactly as safe as its narrowest constraint, and four of the five constraints are things I wrote. The execution timeout is the only one enforced by something other than this code, and it is a session variable that a future change could remove without any test noticing — which is why there is a test that asserts it is set.

It is also a TypeScript server in a repository that is otherwise PHP, maintained by two of three people, whose reference implementations and tooling are all in the JavaScript ecosystem. That is a defensible reason for a second language and it is a second language, and if the two people who can maintain it are unavailable the answer is to turn it off.

The larger discomfort is that this is a database connection exposed to a process whose behaviour is not deterministic. Every constraint here is designed on the assumption that the client will eventually attempt the worst query it can express, which is a reasonable assumption about any client and is a different posture from the one a developer’s own terminal gets.