guarded-postgres-mcp

A Model Context Protocol (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).

Related MCP server: Postgres Scout MCP

Tools

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.

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:

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. 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.

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.

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):

{
  "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

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, pg, 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