--- title: Custom MCP Connector for the Posts Database description: Spec for a small remote MCP server that lets scheduled Claude sessions read and upsert blog posts on the self-hosted VPS Postgres, replacing the Neon connector. date: 2026-09-18 lang: en-US author: Julien Béranger model: Claude Sonnet 5 source: https://julienberanger.com/vps-db-custom-mcp-spec --- # Custom MCP Connector for the Posts Database ## Why The `posts` table used to live on [Neon](https://neon.com/), reachable through Neon's own hosted [MCP](https://modelcontextprotocol.io/) server. It's [now on a self-hosted Postgres instance](https://julienberanger.com/operating-posts-database-vps) 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](https://modelcontextprotocol.io/specification) 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: | Tool | Purpose | Maps to | | -------------- | -------------------------------------------------------------------------- | ---------------------------- | | `posts_latest` | Fetch the most recent post whose slug matches a prefix (e.g. `eth-daily-`) | `pnpm posts list` (filtered) | | `posts_upsert` | Insert or update a post by slug | `pnpm posts add` | | `posts_list` | List recent posts, optionally filtered by slug prefix | `pnpm 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](https://nodejs.org/) process, built with the [MCP TypeScript SDK](https://github.com/modelcontextprotocol/typescript-sdk), using the [Streamable HTTP transport](https://modelcontextprotocol.io/specification/2025-03-26/basic/transports) — 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](https://node-postgres.com/) 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 `. 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](https://restic.net/) 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](https://zod.dev/) for input schemas, `pg` for the database client. Follow the [MCP quickstart](https://modelcontextprotocol.io/quickstart/server) 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](https://github.com/modelcontextprotocol/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 - [Model Context Protocol specification](https://modelcontextprotocol.io/specification) - [MCP TypeScript SDK](https://github.com/modelcontextprotocol/typescript-sdk) - [MCP Inspector](https://github.com/modelcontextprotocol/inspector) - [Operating the posts database on a VPS](https://julienberanger.com/operating-posts-database-vps)