# guarded-postgres-mcp

A [Model Context Protocol](https://modelcontextprotocol.io) (MCP) server that gives AI agents governed, audited access to the PostgreSQL database behind a legacy ERP. Agents can investigate through a read-only tool, and every change goes through guardrails aimed at the mistakes that actually take production down.

> **Status:** this is a public rewrite of an internal tool used with a legacy ERP. The internal version in use still has an earlier, regex-based sequence guard, added after the incident described below. The design in this repository (SQL lexer, sequence invariant measured inside the transaction and repaired after a rollback, `READ ONLY` queries, measured row limit) is covered by the test suite and CI; it has not replaced the internal version.

## The problem

AI coding agents are good at investigating data problems and drafting the SQL that fixes them. In a legacy ERP, the slow and risky part comes after that: copying scripts between the chat and a SQL client, running them by hand against production, and remembering later which statement ran, when, and why.

Handing the agent a raw database connection removes the friction, and also every safety net. Legacy ERP schemas make that worse:

- They often have few foreign keys, no column defaults and no triggers that would stop a bad write.
- Primary keys are frequently generated by standalone sequences that the application calls explicitly, so nothing in the database keeps the sequence and the data in sync.
- A single unqualified `UPDATE` or `DELETE` can rewrite years of fiscal or inventory history.

guarded-postgres-mcp sits between the agent and the database. It exposes four tools, checks each statement before it runs and again inside the transaction, and, when `GUARD_AUDIT_LOG_PATH` is set, keeps an audit trail of what was executed (auditing is off by default).

## Tools

| Tool | What it does |
|---|---|
| `query` | Runs one `SELECT`, `WITH` or `EXPLAIN` in a `READ ONLY` transaction that is always rolled back. Returns at most `GUARD_QUERY_MAX_ROWS` rows and says when the result was cut. |
| `execute` | Runs one DML/DDL statement in its own transaction, after the guardrails. Requires a `description`, which goes to the audit log. |
| `execute_transaction` | Runs a list of statements inside one transaction. Every statement goes through the same checks, and any failure rolls the whole set back. |
| `snapshot_table` | Creates `tmp_<source>[_<suffix>]_backup_YYYYMMDD` as `SELECT * FROM <source> [WHERE ...]` before a destructive change, so it can be undone by hand. |

The tools declare MCP annotations (`readOnlyHint` on `query`, `destructiveHint` on the two write tools), so clients can treat them differently.

## What each call guarantees

Every call holds one connection from the pool for its whole duration, in a transaction that the server opens and closes itself. Each statement from the agent is sent through the extended query protocol (`queryMode: 'extended'`), which accepts exactly one statement: a `;` cannot smuggle a second one in, whatever the static checks miss. Any failure ends in `ROLLBACK`. After the `COMMIT` or `ROLLBACK`, the server runs `DISCARD ALL`, which resets session settings, temporary tables, session advisory locks and prepared statements, so what a call changed in its session does not reach the next call on that connection (the pool's timeouts are connection startup parameters, which `DISCARD ALL` returns to). A connection whose rollback or reset fails is discarded instead of going back to the pool.

**`query`**

- Static: exactly one statement, starting with `SELECT`, `WITH` or `EXPLAIN`; no `INSERT`, `UPDATE`, `DELETE`, `MERGE` or `TRUNCATE` anywhere in it (so no data-modifying CTE and no `EXPLAIN ANALYZE DELETE`); no `SELECT ... INTO`.
- At run time: `BEGIN TRANSACTION READ ONLY`, so the server refuses any write, including one hidden in a function call (`SELECT nextval(...)` fails). `SELECT`/`WITH` go through `DECLARE ... CURSOR` and `FETCH`, which caps the rows held in memory. The transaction is always rolled back, which also undoes any `set_config` or `SET` done inside it.

**`execute` and `execute_transaction`**

1. Input validation: strings must be non-empty strings, and `i_understand` must be a real boolean (models often send `"false"`, which a truthiness check would read as consent).
2. Static checks on each statement (see Guardrails below).
3. Sequence lookup in the catalog, and the current position of each sequence involved, before `BEGIN`.
4. A write-ahead audit entry (`*_started`), then `BEGIN` and the statements.
5. Row limit: a statement that changes more than `GUARD_MAX_ROWS` rows (`INSERT`, `UPDATE`, `DELETE`, `MERGE`) rolls everything back unless `i_understand: true`.
6. Sequence invariant (below): a call that leaves a sequence behind `MAX()` of its key is rolled back.
7. `COMMIT` and the audit entry with the SQL, row counts and elapsed time.

**`snapshot_table`**: the source and suffix are validated against `[\w."]+` and `\w+`, the name must fit PostgreSQL's 63-byte limit (longer names would be truncated silently and lose the date), and the optional `where` is raw SQL from the agent: a second statement, an `INSERT`, `UPDATE`, `DELETE`, `MERGE` or `TRUNCATE`, or a `setval` in it is refused before anything reaches the database. Other function calls in it are not inspected (see Threat model).

## Guardrails

The static checks run on the tokens of a small PostgreSQL lexer (`src/sql-lexer.js`), not on raw text. It knows line and nested block comments (a comment counts as whitespace, so `DELETE/**/FROM` is still a `DELETE`), `'...'`, `E'...'` and dollar-quoted strings, quoted identifiers and parentheses. Plain strings depend on `standard_conforming_strings` (a backslash escapes a quote when it is off, which old ERP databases still use), so the checks must hold under both readings.

| Guardrail | Default | Behavior |
|---|---|---|
| `DELETE` / `UPDATE` without their own `WHERE` | on (`GUARD_BLOCK_DML_WITHOUT_WHERE`) | Blocked wherever they appear: after a `WITH`, inside a CTE, after `EXPLAIN`, in a rule action. A `WHERE` inside a subquery or a string does not count. `TRUNCATE` is blocked by the same flag. |
| `DROP` of any object, `ALTER ... DROP [COLUMN]` | on (`GUARD_BLOCK_DROP`) | Blocked. `DROP TABLE` of snapshot tables (`tmp_*_backup_YYYYMMDD`, without `CASCADE`) is allowed. `ALTER ... DROP CONSTRAINT / DEFAULT / NOT NULL / EXPRESSION / IDENTITY` is allowed: they change no rows. Like `DROP DEFAULT` on a `serial` column, `DROP IDENTITY` stops new keys from being generated (and drops the column's identity sequence). |
| `DO`, `CALL` | always | Blocked: they run code the checks cannot read. |
| Transaction control and session commands | always | `BEGIN`, `COMMIT`, `ROLLBACK`, `SAVEPOINT`, `SET`, `RESET`, `DISCARD`, `PREPARE`, `EXECUTE`, `LISTEN` and `EXPLAIN` are refused in the write tools (`SET CONSTRAINTS` is allowed). A function can still change the session (`set_config(..., false)`, a temporary table, a session advisory lock); that lasts until the end of the call, because every call ends with `DISCARD ALL` (see above). |
| Catch-all condition | always | A `WHERE` (or a `MERGE ... ON`) made only of literals, operators and the words `TRUE`, `FALSE`, `NULL`, `NOT`, `AND`, `OR`, `IS`, `IN`, `BETWEEN`, `LIKE`, `ILIKE` (`1=1`, `(2=2)`, `'a'='a'`, `NOT FALSE`, `TRUE`) requires `i_understand: true`. A condition without columns that uses a function call, a cast or a subquery (`now() IS NOT NULL`, `EXISTS (SELECT 1)`) is not recognized and is left to the row limit. DML inside a `WITH` or subquery, whose row count the server does not report, also requires `i_understand: true`. |
| Row limit | `GUARD_MAX_ROWS=1000` | Measured inside the transaction: above the limit, everything is rolled back unless `i_understand: true`. This is the real net for conditions such as `WHERE id > 0`. |
| Sequence behind `MAX()` of its key | on (`GUARD_BLOCK_MANUAL_PK`) | Checked inside the transaction, after the last statement. See below. |
| Audit log | off; on when `GUARD_AUDIT_LOG_PATH` is set | JSON lines with the SQL: two per write (one before it runs, one after), one per snapshot, block and error. |

### Why the sequence guard exists

The internal tool got its first, regex-based version of this guard after a production incident; the guard described here is its rewrite.

A recovery script had to backfill rows that a failed integration never wrote. It generated the new primary keys the way many legacy scripts do:

```sql
INSERT INTO tab_reading (reading_id, device_id, quantity, ...)
SELECT (SELECT MAX(reading_id) FROM tab_reading) + ROW_NUMBER() OVER (...), ...
```

The rows went in without errors. The application, however, does not use `MAX()`: it calls `nextval()` on the table's sequence, and nobody ran `setval` after the backfill. The sequence was now behind `MAX(reading_id)`, so the next `nextval()` returned a key that already existed. From that point on, every INSERT from the application failed with a duplicate key error, and writes to that table stayed down for hours until the sequence was resynchronized.

Nothing in that schema prevented it: the sequence is a standalone object, not a column default, so PostgreSQL has no way to know that the two belong together.

How the guard works:

1. **Candidates (static).** `findWriteTargets` lists every table that receives rows (`INSERT INTO`, `MERGE INTO`, wherever they sit), with the columns an `INSERT` computes from `MAX()` of themselves (`MAX(id) + 1`, `COALESCE(MAX(id), 0) + 1`, `MAX(t.id) + ROW_NUMBER() OVER (...)`). `findSequenceChanges` lists the sequences moved directly by `setval('<name>', ...)`, `setval(pg_get_serial_sequence('<table>', '<column>'), ...)` or `ALTER SEQUENCE`. A `setval` whose sequence cannot be read from the text (a computed name) is refused.
2. **Catalog.** For each table, the integer key columns (the single-column primary key plus the `MAX()` columns) and the sequence that feeds each one: a `serial` / `identity` default (`pg_get_serial_sequence`), or, if configured, the legacy naming convention described under [Configuration](#legacy-sequence-convention). For each sequence moved directly, the table it feeds (ownership in `pg_depend`, or the same naming convention).
3. **Invariant (inside the transaction).** Before `BEGIN`, the server records whether each sequence is behind `MAX()` of its key. After the last statement and before `COMMIT`, it measures again. If a sequence that was in step is now behind, the whole call is rolled back with a message that tells the agent how to fix it:
   - preferred: use `nextval('<sequence>')` instead of `MAX()` or explicit values;
   - if the explicit keys are really needed, send the statements through `execute_transaction`, ending with `SELECT setval('<sequence>', (SELECT MAX(<pk>) FROM <table>))`.
4. **Repair after a rollback.** `setval` is not transactional in PostgreSQL: rolling back does not undo it. When a rolled-back call leaves a sequence behind `MAX()` (for example `setval('gen_invoice', 1)`), the server moves it to `MAX()` of the key, or back to its position before the call if that is higher, and says so in the error and in the audit log.

Because the decision is the measured state and not the text, a `setval` with the wrong value, a `setval` placed before the `INSERT`, an `INSERT` without a column list, the second `INSERT` of a script and an explicit key on a `serial` column are all caught. A sequence that was already behind before the call is reported as a warning rather than blocked, since the call did not cause it.

Tables without a sequence are not checked. There, `MAX() + 1` is the legitimate idiom, and composite keys such as `(header_id, line_no)` are the common case.

Sequences named after a column (`gen_<column>`) are deliberately not matched. In legacy schemas the same column name is often reused by dozens of tables, so that mapping is ambiguous, and the guard would end up comparing against the wrong table's `MAX`.

## Architecture

```
MCP client (Claude Code, Claude Desktop, ...)
        |  stdio, JSON-RPC (stdout carries nothing else)
        v
src/index.js        bootstrap: env, MCP server, tool routing
src/tools.js        tool schemas and handlers: transactions, row limit,
        |           sequence invariant and repair, audit
        +--> src/guardrails.js   pure static checks, no I/O
        +--> src/sql-lexer.js    PostgreSQL tokens: comments, strings, depth
        +--> src/audit.js        append-only JSONL audit log
        +--> src/db.js           pg pool: 5 connections, timeouts, error listener
        +--> src/env.js          .env loading shared with test-conn
        v
PostgreSQL (legacy ERP database)
```

The handlers receive `{ env, getPool }`, so the unit tests drive them with a fake pool; the same flows run against a real PostgreSQL in `test/integration.test.js`.

## Configuration

Copy `.env.example` to `.env` in the package root, or pass the same variables through the MCP client's environment.

| Variable | Default | Purpose |
|---|---|---|
| `PG_HOST`, `PG_PORT`, `PG_DATABASE`, `PG_USER`, `PG_PASSWORD` | `PG_PORT=5432` | Connection settings. |
| `PG_CONNECT_TIMEOUT_MS` | `10000` | Gives up connecting instead of hanging when the database is down. |
| `PG_STATEMENT_TIMEOUT_MS` | `120000` | Server-side statement timeout (`0` disables it). |
| `PG_LOCK_TIMEOUT_MS` | `5000` | An `ALTER TABLE` waiting on a lock would queue every ERP session behind it. |
| `PG_IDLE_IN_TRANSACTION_TIMEOUT_MS` | `60000` | PostgreSQL 9.6+. Set `0` on older servers, which reject the parameter at connection time. |
| `PG_APPLICATION_NAME` | `guarded-postgres-mcp` | How the agent's sessions show up in `pg_stat_activity`. |
| `GUARD_AUDIT_LOG_PATH` | unset, no audit | Absolute path of the JSONL audit log. Parent directories are created. |
| `GUARD_BLOCK_DML_WITHOUT_WHERE` | `true` | Only the literal `false` disables it. |
| `GUARD_BLOCK_DROP` | `true` | Only the literal `false` disables it. |
| `GUARD_BLOCK_MANUAL_PK` | `true` | Only the literal `false` disables it. |
| `GUARD_MAX_ROWS` | `1000` | Rows a single write statement may change without `i_understand: true`. |
| `GUARD_QUERY_MAX_ROWS` | `1000` | Rows `query` returns. |
| `GUARD_LEGACY_TABLE_PREFIX`, `GUARD_LEGACY_SEQUENCE_PREFIX` | unset | Legacy sequence convention, see below. |
| `GUARD_ENV_FILE` | `.env` in the package root | Loads another env file (for example a staging database) without touching the production one. The server refuses to start if the file does not exist, rather than falling back to whatever `PG_*` the client has. |

**Database encoding.** There is no client encoding setting on purpose. node-postgres always talks UTF8, and the server converts from the database encoding (`LATIN1`, `WIN1252`, ...) on its own, so a `LATIN1` ERP database works without configuration (the integration suite checks a round trip). Forcing another `client_encoding` would make the driver decode `LATIN1` bytes as UTF-8 and corrupt accented text in both directions. A `SQL_ASCII` database stores bytes without any conversion, so non-ASCII text written by other clients comes back as whatever bytes they used: a known limitation.

### Legacy sequence convention

Many legacy ERPs never attach sequences to columns. A table such as `tab_invoice` has no default on its key; a standalone sequence `gen_invoice` exists, and the application calls `nextval('gen_invoice')` itself. PostgreSQL cannot tell that the two belong together, so the guard needs the naming rule:

```
GUARD_LEGACY_TABLE_PREFIX=tab_
GUARD_LEGACY_SEQUENCE_PREFIX=gen_
```

With both set, a table named `<table prefix><base>` is considered to be fed by the sequence `<sequence prefix><base>` in the same schema. Matching uses the table name as resolved by the catalog and is case sensitive. With either one empty, only `serial` / `identity` defaults are detected.

### Setup and client registration

Node.js 22 or later.

```bash
npm ci
cp .env.example .env   # then edit it
npm run test-conn      # checks connectivity, prints the server version and encoding
```

The server and `test-conn` load `.env` from the package root (or `GUARD_ENV_FILE`), so credentials do not need to live in the client configuration. A minimal `mcpServers` entry (Claude Code, Claude Desktop and other MCP clients use the same shape):

```json
{
  "mcpServers": {
    "erp-db": {
      "command": "node",
      "args": ["/path/to/guarded-postgres-mcp/src/index.js"]
    }
  }
}
```

### Example calls

```
snapshot_table({ source: "tab_invoice_item", where: "invoice_id IN (1001, 1002)", suffix: "fix_42" })

execute({
  sql: "DELETE FROM tab_invoice_item WHERE invoice_id = 1001 AND line_no = 3",
  description: "Remove duplicated line from invoice 1001"
})

execute_transaction({
  statements: [
    "INSERT INTO tab_invoice (invoice_id, ...) SELECT MAX(invoice_id) + ROW_NUMBER() OVER (...), ...",
    "SELECT setval('gen_invoice', (SELECT MAX(invoice_id) FROM tab_invoice))"
  ],
  description: "Backfill invoices with explicit IDs and resync the sequence"
})
```

Audit log lines look like this (`execute` logs `sql`, `execute_transaction` logs `statements`):

```
{"ts":"2026-01-15T22:30:00.100Z","event":"execute_started","description":"Remove duplicated line from invoice 1001","sql":"DELETE FROM ..."}
{"ts":"2026-01-15T22:30:00.123Z","event":"execute_ok","description":"Remove duplicated line from invoice 1001","sql":"DELETE FROM ...","results":[{"idx":1,"command":"DELETE","rowCount":1}],"total_rowCount":1,"elapsed_ms":42}
{"ts":"2026-01-15T22:31:15.789Z","event":"transaction_blocked","description":"...","statements":["INSERT INTO ..."],"reason":"GUARDRAIL: these statements leave the sequence public.gen_invoice ..."}
```

Events: `execute_*` and `transaction_*` (`started`, `ok`, `blocked`, `error`), `snapshot_ok` / `snapshot_blocked` / `snapshot_error`, `query_blocked` / `query_error`, and `internal_error`. Successful queries are not logged.

## Running the tests

```bash
npm test
```

This runs the Node.js built-in test runner over `test/*.test.js`. Without a database, 192 tests run and the 15 integration tests are skipped:

- `bypasses.test.js`: a table of ways around the static checks (comment as whitespace, comment markers inside strings, `WHERE` only in a subquery, DML after `WITH` or inside a CTE, `EXPLAIN ANALYZE`, `DO`, every kind of `DROP`) and of false positives that must pass. 25 of the 26 "must block" rows got through the regex-only predecessor mentioned under Status; it blocked every `DROP TABLE`, so it caught the remaining one, which pins the snapshot-drop exception added since. 5 of the 17 "must pass" rows were false positives of it.
- `guardrails.test.js`: the write targets from the incident's statement shape, the gaps of the old detector, the shape each tool accepts, the statements that need `i_understand`, and `setval` parsing.
- `tools.test.js`: the handlers against a fake pool: what reaches the database and in which order, the row limit, the sequence invariant and its repair, the session reset, the audit log.
- `process.test.js`: the server as a real process (stdout carries only JSON-RPC, a missing `GUARD_ENV_FILE` stops it) and `test-conn` loading the same `.env`.
- `db.test.js`: pool configuration.
- `integration.test.js`: the same flows against a real PostgreSQL, including the `LATIN1` round trip. Point `GUARD_TEST_DATABASE_URL` at a disposable database to run them; they create and drop a schema, a snapshot table and a `LATIN1` database. CI (`.github/workflows/test.yml`) runs them against a `postgres:16` service container.

## Threat model and known limits

- **Guardrails are a seatbelt, not a permission system.** They stop the common and costly mistakes an agent (or a person) makes, and the bypasses listed in `test/bypasses.test.js`. They do not stop a deliberate attacker with SQL access. Real limits belong in the database.
- **Use a least-privilege role.** Connect with a dedicated role that holds only the grants the agent needs: no superuser, no table ownership, no `CREATEROLE`. `snapshot_table` creates tables, so that role needs `CREATE` on the target schema.
- **Functions are opaque.** A function called from a statement (`SELECT purge_all()`, `dblink_exec(...)`) can do anything its owner can. In `query` the `READ ONLY` transaction stops writes to this database, but not a function that opens its own connection (dblink); in the write tools only the row limit on the statement itself applies, and in the `where` of `snapshot_table` a function runs in a read-write transaction with no row limit at all. Restrict `EXECUTE` on such functions for the agent's role.
- **The lexer is not a parser.** It does not resolve which table a name refers to, or evaluate expressions: a condition such as `WHERE id > 0` is left to the row limit, and a computed `setval` target is refused rather than guessed. A full parser (libpg_query) would close more cases at the cost of a native dependency.
- **Some DDL cannot run.** Because every call runs inside a transaction, statements that refuse one (`VACUUM`, `CREATE INDEX CONCURRENTLY`) fail with PostgreSQL's own error. Run them by hand.
- **Sequence checks only cover what they can see.** A table without a sequence, a sequence linked only by a convention that is not configured, or a table without a single-column primary key and without a `MAX()` pattern is not checked. If the catalog lookup fails before the transaction, the call proceeds and the error goes to stderr (fail open); a measurement that fails inside the transaction rolls back (fail closed). The repair after a rollback restores the higher of `MAX()` and the pre-call position; a value handed out by `nextval` to a concurrent, still-open transaction during that window is not known to it.
- **`SQL_ASCII` databases** perform no encoding conversion (see Configuration).
- **The audit log contains SQL**, which can include business data. Store it with restricted permissions. It is excluded from git (`*.jsonl`). A failing audit write is reported on stderr and does not block the operation.
- **Credentials** live in `.env` (git-ignored) or in the client's environment, never in the repository.

## Stack

Node.js 22+, [`@modelcontextprotocol/sdk`](https://www.npmjs.com/package/@modelcontextprotocol/sdk), [`pg`](https://www.npmjs.com/package/pg), [`dotenv`](https://www.npmjs.com/package/dotenv).

Built with AI-assisted development (Claude Code). Each guardrail is pinned by tests, including the table of bypass attempts in `test/bypasses.test.js`.

## License

[MIT](LICENSE)