guarded-postgres-mcp
Provides governed, audited access to PostgreSQL databases for AI agents, with a read-only query tool and guarded write tools for executing DML/DDL statements and transactions, creating table snapshots, enforcing row limits, checking sequence integrity, and optionally writing audit logs.
Click on "Deploy Server".
Wait a few minutes for the server to deploy. Once ready, it will show a "Started" state.
In the chat, type
@followed by the MCP server name and your instructions, e.g., "@guarded-postgres-mcp find orders stuck in pending status for more than 30 days"
That's it! The server will respond to your query, and you can continue using it as needed.
Here is a step-by-step guide with screenshots.
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 ONLYqueries, 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
UPDATEorDELETEcan 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 |
| Runs one |
| Runs one DML/DDL statement in its own transaction, after the guardrails. Requires a |
| Runs a list of statements inside one transaction. Every statement goes through the same checks, and any failure rolls the whole set back. |
| Creates |
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,WITHorEXPLAIN; noINSERT,UPDATE,DELETE,MERGEorTRUNCATEanywhere in it (so no data-modifying CTE and noEXPLAIN ANALYZE DELETE); noSELECT ... 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/WITHgo throughDECLARE ... CURSORandFETCH, which caps the rows held in memory. The transaction is always rolled back, which also undoes anyset_configorSETdone inside it.
execute and execute_transaction
Input validation: strings must be non-empty strings, and
i_understandmust be a real boolean (models often send"false", which a truthiness check would read as consent).Static checks on each statement (see Guardrails below).
Sequence lookup in the catalog, and the current position of each sequence involved, before
BEGIN.A write-ahead audit entry (
*_started), thenBEGINand the statements.Row limit: a statement that changes more than
GUARD_MAX_ROWSrows (INSERT,UPDATE,DELETE,MERGE) rolls everything back unlessi_understand: true.Sequence invariant (below): a call that leaves a sequence behind
MAX()of its key is rolled back.COMMITand 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 |
| on ( | Blocked wherever they appear: after a |
| on ( | Blocked. |
| always | Blocked: they run code the checks cannot read. |
Transaction control and session commands | always |
|
Catch-all condition | always | A |
Row limit |
| Measured inside the transaction: above the limit, everything is rolled back unless |
Sequence behind | on ( | Checked inside the transaction, after the last statement. See below. |
Audit log | off; on when | 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:
Candidates (static).
findWriteTargetslists every table that receives rows (INSERT INTO,MERGE INTO, wherever they sit), with the columns anINSERTcomputes fromMAX()of themselves (MAX(id) + 1,COALESCE(MAX(id), 0) + 1,MAX(t.id) + ROW_NUMBER() OVER (...)).findSequenceChangeslists the sequences moved directly bysetval('<name>', ...),setval(pg_get_serial_sequence('<table>', '<column>'), ...)orALTER SEQUENCE. Asetvalwhose sequence cannot be read from the text (a computed name) is refused.Catalog. For each table, the integer key columns (the single-column primary key plus the
MAX()columns) and the sequence that feeds each one: aserial/identitydefault (pg_get_serial_sequence), or, if configured, the legacy naming convention described under Configuration. For each sequence moved directly, the table it feeds (ownership inpg_depend, or the same naming convention).Invariant (inside the transaction). Before
BEGIN, the server records whether each sequence is behindMAX()of its key. After the last statement and beforeCOMMIT, 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 ofMAX()or explicit values;if the explicit keys are really needed, send the statements through
execute_transaction, ending withSELECT setval('<sequence>', (SELECT MAX(<pk>) FROM <table>)).
Repair after a rollback.
setvalis not transactional in PostgreSQL: rolling back does not undo it. When a rolled-back call leaves a sequence behindMAX()(for examplesetval('gen_invoice', 1)), the server moves it toMAX()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 |
|
| Connection settings. |
|
| Gives up connecting instead of hanging when the database is down. |
|
| Server-side statement timeout ( |
|
| An |
|
| PostgreSQL 9.6+. Set |
|
| How the agent's sessions show up in |
| unset, no audit | Absolute path of the JSONL audit log. Parent directories are created. |
|
| Only the literal |
|
| Only the literal |
|
| Only the literal |
|
| Rows a single write statement may change without |
|
| Rows |
| unset | Legacy sequence convention, see below. |
|
| 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 |
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 encodingThe 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 testThis 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,WHEREonly in a subquery, DML afterWITHor inside a CTE,EXPLAIN ANALYZE,DO, every kind ofDROP) 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 everyDROP 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 needi_understand, andsetvalparsing.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 missingGUARD_ENV_FILEstops it) andtest-connloading the same.env.db.test.js: pool configuration.integration.test.js: the same flows against a real PostgreSQL, including theLATIN1round trip. PointGUARD_TEST_DATABASE_URLat a disposable database to run them; they create and drop a schema, a snapshot table and aLATIN1database. CI (.github/workflows/test.yml) runs them against apostgres:16service 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_tablecreates tables, so that role needsCREATEon the target schema.Functions are opaque. A function called from a statement (
SELECT purge_all(),dblink_exec(...)) can do anything its owner can. InquerytheREAD ONLYtransaction 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 thewhereofsnapshot_tablea function runs in a read-write transaction with no row limit at all. RestrictEXECUTEon 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 > 0is left to the row limit, and a computedsetvaltarget 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 ofMAX()and the pre-call position; a value handed out bynextvalto a concurrent, still-open transaction during that window is not known to it.SQL_ASCIIdatabases 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
Available Tools
4 toolsexecuteADestructive
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_transactionADestructive
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.
queryARead-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
Scored across 4 tools
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.
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.
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.
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
Related MCP Connectors
Deterministic safety, correctness & cost gate that vets Postgres SQL before your AI agent runs it.
Safe, read-only Postgres and MySQL access for AI agents. Audit log + column-level controls.
Query PostgreSQL databases in plain English — LLM-generated, safety-validated SQL.
PostgreSQL, MySQL, and SQL Server in one session. 26 read-only MCP tools for AI agents.
Related MCP Servers
- AlicenseNot gradedqualityDmaintenanceEnables 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 npmApache 2.0
- AlicenseNot gradedqualityDmaintenanceEnables full read-write access to PostgreSQL databases with transaction management and safety controls, allowing LLMs to query and modify database content.10 npm3MIT
- AlicenseNot gradedqualityCmaintenanceEnables 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
- AlicenseAqualityCmaintenanceLets 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.3MIT