Custom MCP Connector for the Posts Database

Julien Béranger

+ Claude Sonnet 5

Why

The posts table used to live on Neon, reachable through Neon's own hosted MCP server. It's now on a self-hosted Postgres instance on the VPS at db.w3hc.org, driven day to day by a custom pnpm posts CLI.

Scheduled Claude sessions run inside Anthropic's cloud sandboxes, and those sandboxes only have outbound HTTPS through a managed proxy — no raw TCP. Postgres's wire protocol is raw TCP on port 5432, so a direct connection string, even a correct one with valid credentials, never reaches the box. This has nothing to do with firewall rules or pg_hba config on the VPS side; the block is on the client's egress path.

The fix is a small server that already sits where it can reach Postgres over TCP, and that itself speaks HTTPS outward. That's exactly the role a remote MCP server plays — it's the same shape as Neon's own hosted connector, just pointed at this VPS instead.

Scope

Three tools, mirroring what the pnpm posts CLI already does, not a generic SQL-execution tool:

ToolPurposeMaps to
posts_latestFetch the most recent post whose slug matches a prefix (e.g. eth-daily-)pnpm posts list (filtered)
posts_upsertInsert or update a post by slugpnpm posts add
posts_listList recent posts, optionally filtered by slug prefixpnpm posts list

Deliberately leaving out raw SQL execution and delete. A narrow, purpose-built surface is safer to expose over the internet than a general query tool, and it's enough for the daily-post workflow this is built for. Delete can stay CLI-only if it's rarely used.

Architecture

Claude session (cloud sandbox, HTTPS-only egress)
        │  HTTPS + bearer token
        ▼
Reverse proxy on the VPS (existing TLS termination)
        │  localhost
        ▼
Node.js MCP server (Streamable HTTP transport, stateless)
        │  TCP, SSL verify-full, existing role
        ▼
Postgres on db.w3hc.org:5432

The MCP server is a small Node.js process, built with the MCP TypeScript SDK, using the Streamable HTTP transport — the recommended transport for remote servers (stateless JSON, no long-lived session to manage, easier to run behind a reverse proxy than SSE). It reuses the same connection approach as the existing CLI: postgresql://website@db.w3hc.org:5432/website?sslmode=verify-full&sslrootcert=system, ideally through node-postgres with a small connection pool, and the same website role, so it inherits the CONNECTION LIMIT and pg_hba restrictions already in place — no new database privileges to design.

It sits behind whatever already terminates TLS for db.w3hc.org today, on its own subdomain or path (e.g. mcp.w3hc.org or db.w3hc.org/mcp), and only ever listens on localhost — the reverse proxy is the only thing exposed publicly.

Authentication

A single bearer token, checked with a constant-time comparison (crypto.timingSafeEqual in Node), passed as Authorization: Bearer <token>. No OAuth flow needed for a single-consumer server like this. The token is generated once, stored on the VPS as an environment variable for the server process, and stored on the Claude side as the connector's credential when it's added.

Resource footprint

This is the part worth being concrete about, since it's the open question:

  • Idle: a Node process holding a small Postgres connection pool (2-3 connections) sits at roughly 40-80 MB of resident memory and effectively zero CPU. That's comparable to a small systemd service, not a database or a build process.
  • Under load: call volume here is a handful of requests a day — one scheduled run, occasional interactive use. Each request is a fast, indexed read or a single-row upsert; CPU cost per call is negligible, on the order of milliseconds.
  • Comparison: this is lighter than Postgres itself, which is already running on the same box, and far lighter than the nightly pg_dumpall and restic backup jobs mentioned in the VPS write-up.

In short: no, this isn't resource-consuming at the scale this is used. The realistic cost isn't compute, it's the setup and maintenance — running one more systemd unit, one more thing to keep patched, one more secret to rotate. For a VPS already running Postgres and serving the blog, one more small Node process is well within existing headroom.

Build steps

  1. Scaffold: npm init, add @modelcontextprotocol/sdk, Zod for input schemas, pg for the database client. Follow the MCP quickstart for the base server shape.
  2. Port the CLI logic: the init/add/list functions from pnpm posts become the implementation behind posts_upsert and posts_list; add posts_latest as a thin query on top (WHERE slug LIKE $1 ORDER BY date DESC LIMIT 1).
  3. Wire up Streamable HTTP: stateless mode, bearer-token middleware in front of the MCP handler.
  4. Test locally with MCP Inspector (npx @modelcontextprotocol/inspector) against localhost before it's anywhere near the internet.
  5. Deploy: systemd unit (Restart=on-failure, runs as an unprivileged user), reverse-proxied with TLS, firewalled to only accept local connections from the proxy.
  6. Register the connector: once it's live over HTTPS, add it as a custom MCP connector, using the server's URL and the bearer token as its credential.
  7. Swap the scheduled task: point the Ethereum Daily job's read/upsert calls at the new tools instead of the retired Neon ones.

Open questions

  • Subdomain vs. path for the server's public URL — whichever is less friction alongside the existing reverse-proxy config.
  • Whether posts_list needs pagination now or can stay a fixed small limit until the table grows enough to matter.
  • Token rotation cadence — a manual rotation every few months is probably enough at this call volume.

Further reading