Skip to main content
Glama
guarded-postgres-mcp

guarded-postgres-mcp

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: mcp-postgres-full-access-extended

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:

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.

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.

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

Available Tools

4 tools
executeA
Destructive

Runs ONE DML/DDL statement (UPDATE, INSERT, DELETE, ALTER, CREATE) in its own transaction, behind guardrails. Blocked: DELETE/UPDATE without their own WHERE (anywhere in the statement), TRUNCATE, DROP (except snapshot tables), ALTER ... DROP COLUMN, DO/CALL, BEGIN/COMMIT, SET and EXPLAIN. Requires i_understand=true when the statement changes more than GUARD_MAX_ROWS rows (default 1000), has a WHERE made only of constants (e.g. WHERE 1=1), or hides DML inside a WITH. Rolled back if it leaves a sequence behind MAX of its key. Commits only when every check passes, and writes an audit log entry.

Inputs: sql (required), description (required), i_understand.

execute_transactionA
Destructive

Runs several statements inside ONE transaction: all of them commit, or none. Every statement goes through the same checks as execute. The sequence check runs after the last statement, so an INSERT with explicit keys can be followed by its setval. Use it for operations that must be atomic (e.g. backup + delete + insert).

Inputs: statements (required), description (required), i_understand.

queryA
Read-onlyIdempotent

Runs ONE SELECT/WITH/EXPLAIN inside a READ ONLY transaction that is always rolled back. Statements that modify data are rejected. Returns at most GUARD_QUERY_MAX_ROWS rows (default 1000) and says when the result was cut. Use it for investigation and analysis.

Inputs: sql (required).

snapshot_tableA

Runs CREATE TABLE tmp_[_]_backup_YYYYMMDD AS SELECT * FROM [WHERE ...]. Use it BEFORE a destructive operation so a manual rollback is possible afterwards. The name must fit in 63 bytes. Returns the snapshot table name and the number of rows copied.

Inputs: source (required), where, suffix.

Tool Schema Changelog

Recent tool additions, removals, and schema changes observed during successful MCP inspections.

Loading tool schema changelog…

    TDQS

    A4.1/5.0

    Scored across 4 tools

    Disambiguation4/5

    Each tool has a distinct primary role: snapshot_table creates backups, query is read-only, execute runs a single write, and execute_transaction runs multiple atomic writes. The main overlap is between execute and execute_transaction, but the 'ONE statement' vs 'several statements' distinction is clearly stated.

    Naming Consistency3/5

    Names are all snake_case, which helps, but the structural pattern is mixed: snapshot_table and execute_transaction are verb_noun, while query and execute are bare verbs. It remains readable but not a fully predictable convention.

    Tool Count5/5

    Four tools is well-scoped for a guarded Postgres server. Each tool covers a distinct operational need (read, single write, atomic multi-write, pre-destructive snapshot) without redundancy.

    Completeness4/5

    Core lifecycle coverage is strong: read, write, atomic write, and backup via snapshot. Minor gaps exist around schema introspection (e.g., listing tables/columns) and an explicit restore-from-snapshot tool, though agents can work around these using query and execute.

    Maintenance

    ActivitySlowing
    ResponsivenessNo recent community issues

    Related MCP Connectors

    Related MCP Servers

    • A
      license
      Not graded
      quality
      D
      maintenance
      Enables AI assistants to safely explore, analyze, and maintain PostgreSQL databases with read-only mode by default, SQL injection prevention, query performance analysis, and optional write operations.
      87 npm
      Apache 2.0
    • A
      license
      Not graded
      quality
      D
      maintenance
      Enables full read-write access to PostgreSQL databases with transaction management and safety controls, allowing LLMs to query and modify database content.
      10 npm
      3
      MIT
    • A
      license
      Not graded
      quality
      C
      maintenance
      Enables AI agents to safely query PostgreSQL by intercepting and auditing every generated SQL statement before it reaches the database, blocking destructive commands like DROP/TRUNCATE, unbounded DELETE/UPDATE statements, stacked queries, and SQL injection probes. It exposes tools for validating query safety and enforcing a strict read-only (SELECT/EXPLAIN) policy.
      MIT
    • A
      license
      A
      quality
      C
      maintenance
      Lets an LLM explore a cryptic legacy ERP schema in plain language through a semantic layer (table listing, column descriptions, published dictionary) and run single read-only SELECT queries that return capped results. Enforces safety at the database engine level: only reads are authorized, sensitive columns are masked to NULL, queries are aborted after a few seconds, and every call is written to an append-only audit log.
      3
      MIT