# postgres-readonly-mcp

A small, dependency-light [MCP](https://modelcontextprotocol.io) server that gives local
coding agents (OpenCode, or anything else that speaks MCP over stdio) safe, **read-only**
access to a PostgreSQL database — without ever handing the LLM your database credential.

This is the same pattern as [`jira-readonly-mcp`](https://github.com/ranson21/jira-readonly-mcp):
the MCP server owns the credential, calls the real backend itself, and hands the model back
compact, sanitized results.

> **This project is read-only.** It cannot INSERT, UPDATE, DELETE, or run any DDL/admin
> statement. See [Threat & security model](#threat--security-model) for exactly how that's
> enforced — including a live end-to-end check, done while building this, that a real
> `DELETE` against a real Postgres database was rejected by Postgres itself.

```
OpenCode / local LLM
     │  MCP (stdio, JSON-RPC)
     ▼
postgres-readonly-mcp   ← owns the DB credential; the LLM never sees it
     │  PostgreSQL wire protocol (read-only session)
     ▼
PostgreSQL
```

## Threat & security model

Unlike a Jira API token, a Postgres credential has no built-in "this can only read" mode, and
"read-only SQL" is user-supplied arbitrary text, so this server layers three independent
protections rather than relying on any single one:

1. **Every session is opened read-only at the protocol level.** [`src/db/pool.ts`](src/db/pool.ts)
   connects with the Postgres startup option `-c default_transaction_read_only=on`. This means
   Postgres itself refuses any write statement - `INSERT`/`UPDATE`/`DELETE`/DDL/etc. all fail
   with `ERROR: cannot execute ... in a read-only transaction` - **regardless of what SQL text
   reaches it**, even if this server's own validator has a bug, and even if the connected role
   otherwise has full write privileges. This was verified live against a real Postgres 16
   instance while building this project: connecting with this exact startup option and a
   full-privilege role, a `DELETE` was rejected by Postgres with that exact error; the same
   `DELETE` without the option succeeded. This is the real enforcement boundary.
2. **Queries are always sent via the extended query protocol** (`pool.query({ text, values })`,
   never a bare string), which restricts a single `Parse` message to exactly one SQL statement.
   A stacked `SELECT 1; DROP TABLE x` payload is rejected structurally before Postgres even
   tries to run the second statement.
3. **You should connect as a Postgres role that only has `SELECT` grants.** This server can't
   create that role for you, but it's the credential-level backstop in case the two protections
   above are ever bypassed (e.g. by connecting to this same database directly, outside this
   server, with the same credential). See [Recommended: a dedicated read-only role](#recommended-a-dedicated-read-only-role).

On top of those three, [`src/security/guard.ts`](src/security/guard.ts) adds a **fail-closed,
defense-in-depth** static check on every `run_query` call, before anything is sent to Postgres:
it rejects any query that doesn't start with `SELECT`/`WITH`, contains a second statement,
contains an `INSERT`/`UPDATE`/`DELETE`/DDL/session-control keyword anywhere (which also catches
the classic "data-modifying CTE" bypass, e.g. `WITH x AS (DELETE FROM t RETURNING *) SELECT * FROM x`),
contains a row-locking clause (`FOR UPDATE`/`FOR SHARE`), or calls one of a documented list of
functions (`nextval`, `setval`, `pg_advisory_lock`, `dblink_exec`, etc.) that Postgres
specifically exempts from read-only transactions. **This validator is a heuristic, not a SQL
parser** - it can reject a legitimate query whose text merely contains a blocked keyword or a
semicolon inside a string literal (fails closed, on purpose), and it is not a substitute for
layers 1-3 above.

Output is also passed through [`src/security/sanitize.ts`](src/security/sanitize.ts), which
redacts common credential shapes (connection strings, Bearer/Basic auth headers,
`password=`/`api_key=`-style assignments, PEM private key blocks) that might otherwise be
echoed back in a verbose driver error message. **This is defense-in-depth, not a security
boundary** - it's a best-effort regex scrubber, not a guarantee, and it does nothing to protect
actual row data (that's the point of the tool - returning your own data to your own agent).

**What this does not protect against:** if the credential you give this server can write, and
somehow bypasses both the read-only session setting and the extended-protocol restriction (not
a known attack against a stock `pg`/Postgres setup, but assume nothing is bulletproof), it could
write. The role-scoping recommendation below is what protects against that residual case.
Anyone who can run this process or read your `.env` can read anything that credential can read.

### Recommended: a dedicated read-only role

```sql
CREATE ROLE mcp_readonly WITH LOGIN PASSWORD 'choose-a-real-password';
GRANT CONNECT ON DATABASE your_database TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
-- Run this too, so future tables are covered automatically:
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_readonly;
```

Point `PGUSER`/`PGPASSWORD` (or `DATABASE_URL`) at this role, not an application or admin
account. With [multiple environments](#multiple-environments), create one such role per
database and point each `DB_ENV_<NAME>_*` block at its own — don't reuse one role's credential
across environments.

## Available tools

Every tool below (except `list_environments`) takes an optional `env` argument selecting which
configured database to use — see [Multiple environments](#multiple-environments). In
single-database mode (the default), `env` never needs to be passed.

| Tool | Description |
| --- | --- |
| `list_environments()` | Lists configured environment names and non-secret connection info (never credentials). Use a returned name as `env` elsewhere. |
| `run_query(sql, env?, limit?)` | Runs a single read-only `SELECT`/`WITH ... SELECT` query. Rejects anything else before it reaches Postgres. Results are capped by `PG_MAX_ROW_LIMIT`. |
| `list_schemas(env?, include_system?)` | Lists schemas (excludes `pg_catalog`/`information_schema`/etc. by default). |
| `list_tables(env?, schema?)` | Lists tables and views in a schema (default `public`). |
| `describe_table(table, env?, schema?)` | Column names, types, nullability, defaults, and primary key, for a table. |

`describe_table`'s primary-key detection deliberately queries `pg_catalog` (`pg_index`/
`pg_class`/`pg_attribute`) rather than `information_schema.key_column_usage`: that
`information_schema` view only shows a constraint's columns to a role with more than `SELECT`
on the table, so it silently reports **no primary key at all** for exactly the kind of
SELECT-only role this README recommends. This was caught by testing against a real read-only
role while building this project, not assumed.

## Multiple environments

By default this server connects to one database (configured with `DATABASE_URL` or
`PGHOST`/etc., as below). If you work across several databases — dev/staging/prod, or several
unrelated projects — configure them all at once and select by name per tool call instead of
running separate server instances or editing `.env` to switch:

```env
DB_ENVIRONMENTS=dev,staging,prod
DB_DEFAULT_ENV=dev              # optional; required only if you want an implicit default

DB_ENV_DEV_HOST=dev-db.example.internal
DB_ENV_DEV_DATABASE=appdb
DB_ENV_DEV_USER=readonly_user
DB_ENV_DEV_PASSWORD=...

DB_ENV_STAGING_URL=postgres://readonly_user:...@staging-db.example.internal:5432/appdb

DB_ENV_PROD_HOST=prod-db.example.internal
DB_ENV_PROD_DATABASE=appdb
DB_ENV_PROD_USER=readonly_user
DB_ENV_PROD_PASSWORD=...
DB_ENV_PROD_SSLMODE=verify-full
```

Each environment can use either a `DB_ENV_<NAME>_URL` connection string or discrete
`DB_ENV_<NAME>_HOST`/`_PORT`/`_DATABASE`/`_USER`/`_PASSWORD` fields, exactly like the
single-database vars — just prefixed with `DB_ENV_<NAME>_`. `_SSLMODE` is also per-environment
(falls back to the global `PGSSLMODE` if unset), since it's common for `dev` to run
unencrypted on `localhost` while `prod` requires `verify-full`. Row limits, statement timeout,
and cell-length truncation are global settings shared by every environment.

Environment names: letters/digits/`_`/`-`, starting with a letter (e.g. `dev`, `qa-east`,
`prod`). A name with a hyphen maps to an underscored var prefix — `qa-east` reads
`DB_ENV_QA_EAST_*`.

Once configured, call `list_environments` to see what's available, then pass `env` to any
other tool: `run_query({ env: "prod", sql: "SELECT count(*) FROM orders" })`. If you configure
more than one environment and don't set `DB_DEFAULT_ENV`, omitting `env` returns a clear error
listing the valid names — it never silently guesses which database you meant.

Each named environment gets its own connection pool and its own recommended SELECT-only role
(see below) — treat each one's credential with the sensitivity of that environment (a `prod`
credential deserves more caution than a `dev` one, even though this server enforces read-only
identically for both).

## Installation on macOS

Requires Node.js 18.17+ (Apple Silicon or Intel; no native/compiled dependencies - `pg`'s JS
driver, no `libpq` required).

```bash
git clone https://github.com/ranson21/postgres-readonly-mcp.git
cd postgres-readonly-mcp
npm install
npm run build
```

This produces `dist/index.js`, a plain Node script you point OpenCode (or any MCP client) at.

## Authentication configuration

Copy the example env file and fill in your real values — **do not commit `.env`** (it's
already git-ignored):

```bash
cp .env.example .env
```

### `.env.example`

```env
# Copy this file to .env and fill in real values.
# .env is git-ignored - never commit it.
#
# All values below are FAKE placeholders for illustration only.

# ============================================================================
# SINGLE DATABASE (default) - use this section if you only have one database.
# Leave DB_ENVIRONMENTS (below) unset.
# ============================================================================

# Option A: a single connection string (takes precedence if set).
# DATABASE_URL=postgres://readonly_user:REPLACE_WITH_YOUR_PASSWORD@db.example.internal:5432/appdb

# Option B: discrete fields, used only if DATABASE_URL is not set.
PGHOST=db.example.internal
PGPORT=5432
PGDATABASE=appdb
PGUSER=readonly_user
PGPASSWORD=REPLACE_WITH_YOUR_PASSWORD

# TLS mode: disable | require | verify-full
# - disable: no encryption (fine for localhost/dev only)
# - require: encrypted, but does NOT verify the server certificate
# - verify-full: encrypted AND verifies the server certificate (recommended
#   for anything that isn't localhost)
# PGSSLMODE=disable

# ============================================================================
# MULTIPLE NAMED DATABASES - set DB_ENVIRONMENTS to a comma-separated list of
# names, then give each one its own DB_ENV_<NAME>_* vars below. When set,
# this takes over entirely from the single-database vars above. Tools take
# an `env` argument to pick which one to use. See "Multiple environments".
# ============================================================================

# DB_ENVIRONMENTS=dev,staging,prod

# --- dev ---
# DB_ENV_DEV_URL=postgres://readonly_user:REPLACE_WITH_YOUR_PASSWORD@dev-db.example.internal:5432/appdb
# or discrete fields instead of DB_ENV_DEV_URL:
# DB_ENV_DEV_HOST=dev-db.example.internal
# DB_ENV_DEV_PORT=5432
# DB_ENV_DEV_DATABASE=appdb
# DB_ENV_DEV_USER=readonly_user
# DB_ENV_DEV_PASSWORD=REPLACE_WITH_YOUR_PASSWORD
# DB_ENV_DEV_SSLMODE=disable

# --- staging ---
# DB_ENV_STAGING_HOST=staging-db.example.internal
# DB_ENV_STAGING_PORT=5432
# DB_ENV_STAGING_DATABASE=appdb
# DB_ENV_STAGING_USER=readonly_user
# DB_ENV_STAGING_PASSWORD=REPLACE_WITH_YOUR_PASSWORD
# DB_ENV_STAGING_SSLMODE=require

# --- prod ---
# DB_ENV_PROD_HOST=prod-db.example.internal
# DB_ENV_PROD_PORT=5432
# DB_ENV_PROD_DATABASE=appdb
# DB_ENV_PROD_USER=readonly_user
# DB_ENV_PROD_PASSWORD=REPLACE_WITH_YOUR_PASSWORD
# DB_ENV_PROD_SSLMODE=verify-full

# Which environment a tool call uses when it doesn't pass `env` explicitly.
# Required if you configure more than one environment and want an implicit
# default; otherwise every tool call must pass `env`. Not needed at all in
# single-database mode (DB_ENVIRONMENTS unset).
# DB_DEFAULT_ENV=dev

# --- Read-only enforcement (applies to every environment) ---
# This server always opens sessions with default_transaction_read_only=on,
# so Postgres itself rejects write statements regardless of what SQL text
# reaches it. On top of that, STRONGLY prefer connecting as a Postgres role
# that only has SELECT grants, for every environment - see README
# "Threat & security model".

# --- Optional tuning (applies to every environment) ---
# Default/maximum rows returned by run_query (an outer LIMIT is always
# applied on top of whatever the query itself requests).
# PG_DEFAULT_ROW_LIMIT=100
# PG_MAX_ROW_LIMIT=500

# Server-side statement timeout, in milliseconds. Protects against a runaway
# query; enforced by Postgres itself via the session's statement_timeout.
# PG_STATEMENT_TIMEOUT_MS=15000

# Long text/jsonb cell values are truncated to this many characters before
# being returned to the model, to avoid wasting context on huge blobs.
# PG_MAX_CELL_LENGTH=2000

# Client-side connection timeout, in milliseconds.
# PG_CONNECT_TIMEOUT_MS=10000

# Optional: diagnostic log verbosity. One of: debug | info | warn | error.
# Logs always go to stderr, never stdout (stdout is reserved for MCP JSON-RPC
# traffic). Defaults to "info".
# DB_MCP_LOG_LEVEL=info
```

Two ways to configure a single database's connection:

- **`DATABASE_URL`** (takes precedence if set): a single `postgres://user:password@host:port/db`
  connection string.
- **Discrete fields**: `PGHOST`, `PGPORT` (default `5432`), `PGDATABASE`, `PGUSER`, `PGPASSWORD`.

To connect to several databases instead (dev/staging/prod, or several unrelated projects), see
[Multiple environments](#multiple-environments) — same two options, just per-environment.

Either way, set `PGSSLMODE` appropriately for where the database lives:

- `disable` (default) - fine for `localhost`/a private network you trust.
- `require` - encrypts the connection but does not verify the server certificate.
- `verify-full` - encrypts **and** verifies the certificate. Use this for anything that isn't
  `localhost`, especially a managed/cloud Postgres instance.

No database administrator action is required beyond creating the recommended read-only role
(a normal `CREATE ROLE`/`GRANT` any role with `CREATEROLE`, or a DBA, can run) — this server
only ever uses ordinary SQL a authenticated role can run.

### Optional: macOS Keychain

Keep the password out of a plaintext `.env` file:

```bash
security add-generic-password -a "$USER" -s postgres-readonly-mcp-password -w 'your-password-here'
```

Then wrap the launch command so `PGPASSWORD` is populated from Keychain right before the server
starts:

```bash
#!/bin/sh
# run.sh
export PGPASSWORD="$(security find-generic-password -a "$USER" -s postgres-readonly-mcp-password -w)"
exec node "$(dirname "$0")/dist/index.js"
```

Point OpenCode's `command` at `run.sh` instead of `node dist/index.js` directly, and drop
`PGPASSWORD` from the `environment` block.

## OpenCode MCP configuration

Add this to your OpenCode config (e.g. `opencode.json` or `~/.config/opencode/opencode.json`),
using OpenCode's [local MCP server schema](https://opencode.ai/docs/mcp-servers/):

```jsonc
{
  "$schema": "https://opencode.ai/config.json",
  "mcp": {
    "postgres": {
      "type": "local",
      "command": ["node", "/absolute/path/to/postgres-readonly-mcp/dist/index.js"],
      "enabled": true,
      "environment": {
        "PGHOST": "db.example.internal",
        "PGPORT": "5432",
        "PGDATABASE": "appdb",
        "PGUSER": "mcp_readonly",
        "PGPASSWORD": "your-real-password-goes-here-not-in-git"
      }
    }
  }
}
```

Use an absolute path for `command` — OpenCode resolves it relative to its own working
directory, not this repo. If you're using the Keychain wrapper above, point `command` at
`run.sh` and drop `PGPASSWORD` from `environment`.

For [multiple environments](#multiple-environments), put the `DB_ENVIRONMENTS`/`DB_ENV_<NAME>_*`
vars in `environment` instead — one server instance then serves all of them, selected per tool
call by name:

```jsonc
{
  "environment": {
    "DB_ENVIRONMENTS": "dev,staging,prod",
    "DB_DEFAULT_ENV": "dev",
    "DB_ENV_DEV_HOST": "dev-db.example.internal",
    "DB_ENV_DEV_DATABASE": "appdb",
    "DB_ENV_DEV_USER": "mcp_readonly",
    "DB_ENV_DEV_PASSWORD": "...",
    "DB_ENV_STAGING_HOST": "staging-db.example.internal",
    "DB_ENV_STAGING_DATABASE": "appdb",
    "DB_ENV_STAGING_USER": "mcp_readonly",
    "DB_ENV_STAGING_PASSWORD": "...",
    "DB_ENV_PROD_HOST": "prod-db.example.internal",
    "DB_ENV_PROD_DATABASE": "appdb",
    "DB_ENV_PROD_USER": "mcp_readonly",
    "DB_ENV_PROD_PASSWORD": "...",
    "DB_ENV_PROD_SSLMODE": "verify-full"
  }
}
```

## Example tool calls

- "List the tables in the database." (`list_tables`)
- "Describe the `orders` table." (`describe_table`)
- "Run a query to find the 10 most recent orders with status 'open'." (`run_query` with
  `SELECT * FROM orders WHERE status = 'open' ORDER BY created_at DESC LIMIT 10`)
- "What schemas exist in this database?" (`list_schemas`)
- With [multiple environments](#multiple-environments) configured: "What database environments
  are available?" (`list_environments`), then "In the `prod` environment, how many orders were
  placed today?" (`run_query` with `env: "prod"`)

A `run_query` result looks like:

```json
{
  "rowCount": 2,
  "columns": ["id", "name", "price"],
  "rows": [
    { "id": 1, "name": "Fake Widget A", "price": "9.99" },
    { "id": 2, "name": "Fake Widget B", "price": "19.99" }
  ],
  "truncated": false
}
```

`truncated: true` means the row count hit the applied limit — there may be more rows than
shown. Long text/jsonb cell values are truncated (see `PG_MAX_CELL_LENGTH`); binary (`bytea`)
values are replaced with a `[binary N bytes omitted]` placeholder, never returned raw.

## Logging

Diagnostic logs go to **stderr only**. This server never writes to stdout, since stdout is the
MCP JSON-RPC transport for this stdio server — any stray line there would corrupt the protocol
stream. `console.error` is used everywhere logging happens; `console.log` is never used.

Control verbosity with `DB_MCP_LOG_LEVEL` (`debug` | `info` | `warn` | `error`, default `info`):

```
[2026-09-13T01:40:45.900Z] [INFO] [postgres-readonly-mcp] tool invoked {"tool":"run_query","env":"prod","limit":10}
[2026-09-13T01:40:46.031Z] [INFO] [postgres-readonly-mcp] query complete {"env":"prod","elapsedMs":12,"rowCount":3}
[2026-09-13T01:40:46.032Z] [INFO] [postgres-readonly-mcp] tool succeeded {"tool":"run_query"}
```

Every log line produced while handling a given environment's queries carries `"env":"<name>"`,
so with multiple environments configured you can tell at a glance (or with `grep`) which
database a given query hit — useful when `dev` and `prod` are both configured and something in
`prod` needs attention.

| Event | Level |
| --- | --- |
| Server startup (each configured environment's host/port/database, TLS mode, row limits, and the default env — never a password) | info |
| Tool invoked (tool name, resolved `env` name, table/schema/limit as applicable) | info |
| The raw SQL text passed to `run_query` | **debug only** |
| Query complete (elapsed ms, row count) | info |
| Sanitization complete (tool name only) | debug |
| Tool succeeded (tool name only) | info |
| Errors (auth failures, connection failures, rejected queries, query errors) | error |

The raw SQL text is deliberately logged at `debug`, not `info`: unlike a Jira issue key, a
`WHERE` clause can carry literal row data (emails, IDs, etc.) that shouldn't land in default
logs. Turn on `DB_MCP_LOG_LEVEL=debug` when you need to see exactly what was run.

Never logged, at any level: the database password, a full connection string, `Authorization`
headers, or raw driver error internals beyond a redacted message. As an extra safety net, every
structured log field also passes through the same redaction layer used for tool output before
being written — see [Threat & security model](#threat--security-model) for why that's
defense-in-depth, not a guarantee.

## Verifying the server without exposing credentials

```bash
npm test    # 100 tests, all against fixtures/fakes - no network, no real database required
npm run build
```

To check the live server without ever printing a real password to a terminal a shared log
might capture, use the [MCP Inspector](https://github.com/modelcontextprotocol/inspector):

```bash
PGPASSWORD="$(security find-generic-password -a "$USER" -s postgres-readonly-mcp-password -w)" \
npx @modelcontextprotocol/inspector node dist/index.js
```

Inspector lets you call `tools/list` and `tools/call` interactively in a browser UI. Try
`run_query` with an obvious mutation (e.g. `DELETE FROM some_table`) and confirm it's rejected
with "Only SELECT (or WITH ... SELECT) queries are allowed" before it ever reaches the database.

## Troubleshooting

- **"Missing database connection settings"** — you haven't set `DATABASE_URL`, or all of
  `PGHOST`/`PGDATABASE`/`PGUSER`/`PGPASSWORD`. See [Authentication configuration](#authentication-configuration).
- **`DbAuthError` (password authentication failed)** — check `PGUSER`/`PGPASSWORD`, and that the
  role is allowed to connect from wherever this server runs (`pg_hba.conf` on
  self-managed Postgres).
- **`DbConnectionError` (connection refused/timed out)** — check `PGHOST`/`PGPORT`, network
  reachability, and `PGSSLMODE` (a managed Postgres provider often requires `require` or
  `verify-full`).
- **A query is rejected with "Only SELECT (or WITH ... SELECT) queries are allowed" even though
  it looks like a SELECT** — the validator is a heuristic (see
  [Threat & security model](#threat--security-model)): a semicolon or a blocked keyword inside
  a string literal in your query text will trigger this. Rewrite to avoid the pattern.
- **`describe_table` shows every column as `isPrimaryKey: false`** — this was a real bug caught
  while building this project when connected as a SELECT-only role; it's fixed by querying
  `pg_catalog` directly (see [Available tools](#available-tools)). If you still see it, you're
  likely running an older build — `npm run build` again.
- **Row values look truncated** — increase `PG_MAX_CELL_LENGTH`, or note that `truncated: true`
  on the result means more rows exist beyond `PG_MAX_ROW_LIMIT`/the requested `limit`.
- **OpenCode doesn't see the tools** — double check `command` is an absolute path to
  `dist/index.js` and that you ran `npm run build`.

## Running entirely locally

Aside from the connection this server makes directly to your configured `PGHOST` (or the host
in `DATABASE_URL`), everything else — the MCP process, the OpenCode/local-model side, tool
schema handling, the SQL guard, and the sanitization layer — runs entirely on your machine. No
third-party service, telemetry endpoint, or analytics call is contacted. This makes it a good
fit for local model setups (OpenCode + a locally-served model, e.g. via MLX/OptiQ) where you
want to keep everything except the necessary database traffic on-device.

## Testing

```bash
npm test
```

100 tests, all against fixtures/fakes/a fake `pg`-shaped pool — no real database, no network
access. Coverage includes: the SQL guard (allowed queries, mutation rejection, the
data-modifying-CTE bypass attempt, stacked statements, locking clauses, side-effecting
functions, documented false-positive limitations), row/value normalization (dates, bigints,
buffers, truncation), config parsing (`DATABASE_URL` vs discrete fields, SSL mode mapping),
credential redaction (by pattern and by key name), error classification (auth vs. connection
vs. query errors), and schema-introspection query shape (parameterized, never string-interpolated).

On top of the unit tests, this project's read-only guarantees were verified live against a real
local Postgres 16 instance (see [Threat & security model](#threat--security-model)) — a `DELETE`
was attempted and rejected by Postgres itself under the exact startup option this server uses,
both as the recommended SELECT-only role and as a full-privilege role.

## Development

```bash
npm run dev    # run src/index.ts directly with tsx, for local iteration
npm run build  # compile to dist/
npm test       # run the test suite
```

## License

MIT — see [LICENSE](LICENSE).