pgwarden-mcp

A Postgres MCP server that an LLM agent can point at production without reading your customers' data.

pgwarden-mcp puts a deny-by-default policy layer between an AI agent and a Postgres database: a YAML file allowlists schemas, tables and columns, caps how many rows a single query may return, requires that certain columns be constrained, masks PII on the way out, and routes one server across several databases. It is a hard fork of crystaldba/postgres-mcp and keeps everything that server does - index tuning, health checks, top-query analysis, EXPLAIN plans.

• Why this exists

• Fork notice and attribution

• Quickstart

• What is enforced, and where

• Policy configuration reference

• Masking strategies

• Rejections you will hit, and how to rewrite

• Multiple databases

• Security notes and limitations

• Inherited capabilities

• Development

• License and credits

Why this exists

An MCP server hands an LLM agent a database connection. Access modes stop the agent from writing, but a read-only agent connected to a production database can still:

• Exfiltrate PII. SELECT email, phone, national_id FROM customers is a perfectly ordinary read. The values land in the model's context, in the client's chat history, and in whatever logs sit along that path.

• Sweep a multi-tenant table. A query the user meant as "my orders" is one forgotten WHERE tenant_id = ... away from being "everyone's orders". The agent has no way to know the difference, and the database will happily answer. (This layer can insist the predicate is there. Deciding which tenant it may name is Postgres's job, not this layer's.)

• Pull whole tables into a context window. SELECT * FROM events on a table with 40 million rows is a denial-of-wallet event at best, and it is one prompt away at all times.

• Read the server's own furniture. pg_authid, pg_shadow, pg_stat_activity and friends expose password hashes, role membership and every other session's SQL text - none of which are application data, all of which are reachable from an ordinary read-only connection.

• Be talked into it. Prompt injection through data the agent reads (a support ticket body, a user-supplied name) can turn any of the above into an instruction the agent follows.

The policy layer is a deny-by-default filter between the agent and the database, applied by parsing every statement with pglast before it is sent. It is not a substitute for Postgres roles and RLS - see Security notes and limitations - it is the layer that stops the ordinary, unmalicious version of each of the failures above, and it produces errors an agent can read and act on.

Related MCP server: Postgres MCP Pro

Fork notice and attribution

This project is a hard fork of crystaldba/postgres-mcp ("Postgres MCP Pro"), created and maintained by Crystal DBA and originally authored by Johann Schleier-Smith. Upstream is MIT licensed; this fork keeps that license and upstream's copyright notice intact - see LICENSE.

Everything in Inherited capabilities is upstream's work: the index tuning advisor, the database health checks, the query-plan tooling, the read-only SQL execution driver, and the MCP tool surface they hang off. What this fork adds is the policy layer described in the rest of this document, plus multi-database routing.

The fork is renamed rather than versioned on top of upstream: the distribution and CLI are pgwarden-mcp, the Python package is pgwarden_mcp, environment variables are PGWARDEN_*, and version numbering restarts at 0.1.0. Upstream's health checks are in turn adapted from PgHero, and the index advisor follows Microsoft's Anytime Algorithm.

Quickstart

Install

The distribution is not published to PyPI or Docker Hub yet, so install from source:

git clone https://github.com/gokiwitech/pgwarden-mcp.git pgwarden-mcp
cd pgwarden-mcp
uv sync
uv run pgwarden-mcp --help

Or build the container image locally:

docker build -t pgwarden-mcp .

Python 3.12 or newer is required. If you need uv, see the uv installation instructions.

1. Write a policy file

# policy.yaml
databases:
  main:
    connection_url: "postgresql://${PGUSER}:${PGPASSWORD}@localhost:5432/app"
    allowed_schemas: [public]
    tables:
      orders: {}
      customers:
        columns:
          mode: exclude
          list: [password_hash]
        require_predicate_on: [tenant_id]
        mask:
          email:
            strategy: email

${VAR} references are substituted from the process environment after the YAML is parsed, so a value containing quotes or newlines can only ever become the content of the scalar that referenced it - it can never add or change a policy key. A reference to an unset variable is a startup error. Keep credentials and salts out of the file.

A fully commented configuration, including a second database and every masking strategy, is in examples/policy.example.yaml.

2. Start the server

export PGUSER=app_readonly PGPASSWORD=...
uv run pgwarden-mcp --config ./policy.yaml

or set PGWARDEN_CONFIG=/path/to/policy.yaml (the flag wins if both are given).

With a policy file, PGWARDEN_DATABASE_URI and the positional connection URL are not consulted at all - every connection comes from the policy file. A policy file that cannot be read, parsed or validated is a fatal startup error, on purpose.

--config also implies read-only: a policy can only be enforced by parsing the SQL, so supplying a config file selects the parsing driver even under the default --access-mode=unrestricted. See Access modes.

3. Connect an MCP client

For Claude Desktop, edit ~/Library/Application Support/Claude/claude_desktop_config.json (macOS) or %APPDATA%/Claude/claude_desktop_config.json (Windows):

{
  "mcpServers": {
    "pgwarden": {
      "command": "pgwarden-mcp",
      "args": ["--config", "/etc/pgwarden/policy.yaml"],
      "env": {
        "PGUSER": "app_readonly",
        "PGPASSWORD": "...",
        "PGWARDEN_MASK_SALT": "..."
      }
    }
  }
}

With Docker, mount the policy file and pass the same flag:

{
  "mcpServers": {
    "pgwarden": {
      "command": "docker",
      "args": [
        "run", "-i", "--rm",
        "-v", "/etc/pgwarden:/etc/pgwarden:ro",
        "-e", "PGUSER", "-e", "PGPASSWORD", "-e", "PGWARDEN_MASK_SALT",
        "pgwarden-mcp",
        "--config", "/etc/pgwarden/policy.yaml"
      ],
      "env": {
        "PGUSER": "app_readonly",
        "PGPASSWORD": "...",
        "PGWARDEN_MASK_SALT": "..."
      }
    }
  }
}

The container entrypoint remaps a localhost host in the connection URI to host.docker.internal (Docker Desktop) or 172.17.0.1 (Linux) automatically.

Other clients use the same shape:

• Cursor: Command Palette → Cursor Settings → MCP tab.

• Windsurf: Command Palette → Open Windsurf Settings Page.

• Goose: goose configure, then Add Extension.

• Qodo Gen: Chat panel → Connect more tools → + Add new MCP.

4. What the agent can and cannot do now

Environment variables

Running with no policy file preserves upstream's behaviour exactly: a single database from PGWARDEN_DATABASE_URI (or the positional argument), every schema and table allowed, all system catalogs allowed, no row limits and no masking. The policy engine is not constructed at all in that mode.

What is enforced, and where

The policy is applied by parsing each statement before execution. Two different things happen, and the distinction matters.

Checks run on every statement the server sends on a policed connection - including the SQL the server's own analysis tools generate:

• the statement kind: only SELECT, EXPLAIN, SHOW, VACUUM / ANALYZE and the cursor statements (DECLARE, FETCH, CLOSE) are modelled, and everything else - DML, COPY, PREPARE / EXECUTE, all DDL - is refused rather than passed through unchecked,

• the schema and table allowlists, including the refusal of any relation or schema name that is not already lower case,

• the system catalog denylist,

• require_predicate_on,

• column include / exclude rules,

• the ban on using a masked column as a filter, join key, grouping, partitioning or ordering key - which covers a GROUP BY GROUPING SETS / CUBE / ROLLUP key, an aggregate's FILTER (WHERE ...) condition, a JOIN ... USING (col) column, a NATURAL JOIN over any table that carries column or mask rules, and a positional key (ORDER BY 2) whether the position belongs to a plain target list or to the merged output of a set operation,

• the ban on reading the value-bearing columns of pg_stats and the pg_stats_ext pair.

Rewrites and result filtering run only on SQL whose provenance is an agent:

• the one-statement rule: agent SQL that holds more than one statement is refused, because Postgres executes all of them and returns only the last one's rows - a ; would otherwise smuggle a second query past both the row limit and the mask plan,

• row limit injection and clamping, and the refusal of DECLARE ... CURSOR that comes with it,

• PII masking of the returned rows, and the refusal of any shape whose provenance cannot be resolved: a masked column inside an expression (including inside a count(CASE WHEN ...) or count(nullif(...)), which are predicates dressed as values), a whole-row reference, a renamed wildcard, a SELECT * arm beside a named-column arm in a set operation, and a set operation that puts a masked column and a caller-written constant in the same output position.

Provenance is a property of the SQL, not of the call site. Exactly three places in the server run agent SQL: execute_sql, and the two ExplainPlanTool entry points that back explain_query and the plan simulation the index advisor performs on the queries handed to analyze_query_indexes. Everything else (analyze_db_health, analyze_workload_indexes, get_top_queries, list_objects, get_object_details, list_schemas, and the statistics queries underneath them) runs server-authored SQL: injecting a LIMIT into a statistics scan would silently truncate it and masking catalog output would corrupt it, so those results are never row-limited or masked. They are still subject to every check in the first list. The classification is pinned by a test that enumerates every execute_query call site in src/ and fails on any new one that has not been classified (tests/unit/test_agent_sql_invariant.py).

One consequence worth knowing: explain_query is checked but not masked or limited, because EXPLAIN returns a plan rather than rows, and neither the row-limit rewrite nor the mask planner applies to a statement whose body is not a SELECT. EXPLAIN ANALYZE does execute the query, so a require_predicate_on violation inside it is still rejected, but the plan it returns is not filtered. explain_query is still treated as agent SQL for the one-statement rule and for provenance: the SQL it is given is parsed on its own and re-attached as an AST node rather than concatenated into a string.

Policy configuration reference

The file is a YAML mapping with one top-level key, databases, mapping an alias to a database policy. At least one database is required. Unknown keys are rejected, so a typo fails at startup instead of silently disabling a restriction.

Database options

Table options

A table written as {} is fully readable - listing it under tables is what makes it referenceable at all, and the fields above only narrow that grant.

A mask rule is strategy: plus that strategy's parameters, written either nested under params: or inline beside strategy: (the nested form wins on conflict). Every rule is fully validated at startup: an unknown parameter, a bad type, an out-of-range value, an unsupported digest, an invalid regex or a missing salt stops the server rather than surfacing halfway through a query.

Once a table restricts its columns, a wildcard over it (SELECT *, t.*, a whole-row reference such as SELECT c FROM customers c) is refused rather than expanded: the policy layer has no catalog to expand it against, and a stale expansion would fail open. A hidden column may not be selected, filtered on, joined on, grouped by or ordered by - a WHERE clause on it still leaks its values through which rows come back.

require_predicate_on in detail

The rule accepts only a predicate that pins the column to values the query itself names, and it must be reachable through AND only, at the same query level as the table reference.

Accepted: col = <literal>, col = $1, col IN (<literal>, <literal>), col = ANY (<array literal>) and col = ALL (<array literal>). A cast around the literal (col = '5'::int) is fine.

Not accepted, deliberately:

• col IS NOT NULL and col IS NULL - on a NOT NULL column the first is every row of the table, so it bounds nothing;

• col IN (SELECT ...) and col = ANY (SELECT ...) - the agent writes that subquery too, so IN (SELECT tenant_id FROM tenants) is the whole table again;

• a range such as col > 5, and anything computed by the database (a function call, another column) on the non-literal side;

• a predicate inside OR or NOT, one in an outer join's ON clause, and one in a different subquery or CTE.

An equijoin propagates the constraint, so a.tenant_id = b.tenant_id AND b.tenant_id = 5 satisfies the requirement for both tables. Every arm of a set operation, every CTE body, every FROM-subquery and every occurrence in a self-join is checked separately - a predicate at one level cannot vouch for another.

This setting was called required_filters before release. There is no deprecated alias: the old key is refused at startup with an error that names the new one and explains the rename.

System catalogs in detail

Under deny, the built-in allowed_system_catalogs list covers exactly what this server's own tools need - pg_stat_statements, the pg_stat_user_* and pg_statio_user_* views, pg_stats, pg_indexes, the replication views, the structural catalogs used by the health checks, the extension-discovery catalogs, and information_schema.*. Everything else in pg_catalog is unreachable, and a hard denylist (roles and passwords, other sessions' activity, raw statistics, foreign-server credentials, host-based auth rules) is never granted by a schema.* wildcard - only by naming the catalog exactly. The full lists are in src/pgwarden_mcp/policy/catalogs.py.

Views and sequences are relations too, and the sequence health check reads sequence relations directly. See Health checks report their own failures for the naming-convention exemption that keeps that check working.

Reading pg_stats does not give access to the sample values it carries; see the rejection table.

Contradictions rejected at load time

Beyond the per-field validation above, seven combinations are refused when the file is read, because each one is a rule that would either never apply, make a table unqueryable, or read as a restriction while silently granting access. Each raises a startup error naming the table (or database), the column (or field) and the fix.

The second one is enforced twice: once by the config model and again when the policy engine is constructed, so it holds even for a policy built in code rather than loaded from YAML.

Other startup errors in the same spirit: a schema-qualified table key whose schema is not in allowed_schemas, a default_row_limit greater than max_row_limit, an empty connection_url, two mapping keys that collide once ${VAR} references are expanded, and any reference to an environment variable that is not set.

Config that is warned about, not rejected

Two patterns are logged as warnings rather than refused, because each has a legitimate reading:

• an allowed_system_catalogs list written next to system_catalog_access: allow, which already grants everything, so the list has no effect;

• a bare (schema-unqualified) table key carrying rules while more than one schema is allowed - the rules, and the access they grant, then apply to a same-named table in any allowed schema, whose different column set an exclude list would leave exposed.

Masking strategies

Masking is applied to the values of an output column after the rows come back, not by rewriting SQL. A masked column may be selected on its own (SELECT email, SELECT c.email AS contact, SELECT * over a table with no column rules) but not wrapped in an expression, and it may not be used as a filter, join key, grouping, partitioning or ordering key, nor as an aggregate's FILTER (WHERE ...) condition - see the rejection table.

hash accepts algorithm values blake2b, blake2s, sha256, sha384, sha512, sha3_256, sha3_384 and sha3_512 (intersected with what the interpreter actually offers). Variable-length digests and MD5/SHA-1 are not available. The digests shown here are illustrative - the real output depends on your salt.

Worked examples

Text is sliced by grapheme cluster, so a "show 2 characters" rule never cuts a combining accent or an emoji sequence in half and never reveals a third character.

Rejections you will hit, and how to rewrite

The policy layer has no catalog of your columns, so wherever it cannot resolve a reference with certainty it refuses rather than guessing. These refusals are the ones users hit in practice. Every message below is what the agent actually receives, abbreviated; each one already names the fix.

Masking still flows correctly through the constructs it can follow: subqueries, CTEs (including recursive ones and column alias lists over named columns), lateral joins, set operations whose arms line up, and * over a masked table are all masked, keyed by the output name the query gives the column. It follows a rename, too - (SELECT email AS e FROM users) s masks s.e - which is what lets the predicate ban follow one as well.

Multiple databases

Each key under databases is an alias. Every alias gets its own connection pool and its own policy engine; nothing is shared between them, including mask_salt. Every pool is warmed at startup and otherwise created on first use; a database that is unreachable is logged and retried on the next call rather than preventing the server from starting.

• list_databases returns each alias with its effective access_mode, a policy_enforced boolean, allowed_schemas, allowed tables, system_catalog_access, default_row_limit and max_row_limit. It reads configuration only: it never connects to a database and never returns a connection URL.

• Every database-touching tool takes an optional database parameter. With exactly one database configured it can be omitted. With several configured, omitting it is an error that names the valid aliases so the model can retry: This server is configured with 2 databases, so the 'database' parameter is required. Valid values: analytics, support. Call list_databases for details about each one. An unknown alias is reported the same way, and never with a connection URL attached.

databases:
  analytics:
    connection_url: "postgresql://${PGUSER}:${PGPASSWORD}@analytics.internal:5432/analytics"
    tables:
      daily_revenue: {}
  support:
    connection_url: "${SUPPORT_DATABASE_URI}"
    tables:
      tickets: {}

Per-database access mode

Each database under databases can set its own access_mode, independent of the others:

databases:
  dev:
    connection_url: "${DEV_URI}"
    access_mode: unrestricted    # no policy rules permitted below

  prod:
    connection_url: "${PROD_URI}"
    access_mode: restricted
    allowed_schemas: [public]
    tables:
      customers:
        mask:
          email: {strategy: email}

This lets one server serve a writable development database and a policy-guarded production database side by side.

access_mode: unrestricted means the database is completely unpoliced: no policy engine is built for it at all, it gets a plain read/write SQL driver, and none of the checks in What is enforced, and where run against it (subject to the CLI floor below). Because of that, an unrestricted database may not also carry any policy-only field - tables, allowed_schemas, default_row_limit, max_row_limit, enforce_row_limits, system_catalog_access, allowed_system_catalogs or mask_salt. Setting any of those alongside access_mode: unrestricted is a config-load error: the policy engine only understands SELECT, so a rule written for an unpoliced database would silently never apply to the INSERT / UPDATE / DELETE it actually allows, and the validator would rather stop the server at load time than let that fail open.

access_mode: restricted, or leaving the field unset entirely under a policy file, keeps the database policy-enforced exactly as described in the rest of this document. Unset is not the same as unrestricted: a database with no access_mode at all still gets the full policy engine even under the server's default --access-mode=unrestricted, because supplying --config selects the parsing driver regardless (see --config implies read-only). The only way to get an unpoliced database is to write access_mode: unrestricted explicitly.

The --access-mode CLI flag is a floor, never a ceiling. A per-database access_mode can only tighten it, never loosen it:

--access-mode=restricted therefore remains a reliable blanket kill switch: no policy file can hand write access back to a database once the flag says restricted.

list_databases reports the effective access_mode for each alias - after the floor above is applied - plus a policy_enforced boolean, alongside the fields it already returns.

Hot reload via SIGHUP

An operator can edit the policy file and send the running server process SIGHUP to reload it without a restart:

kill -HUP <pid>
# or, containerized:
docker kill -s HUP <container>

There is deliberately no MCP tool for this. Changing an agent's own guardrails stays an operator action off the agent's clock - the agent being guarded gets no lever on the timing of its own constraints.

Reload is only wired up when the server was started with --config / PGWARDEN_CONFIG in the first place. A bare PGWARDEN_DATABASE_URI run has no policy file to reread - the SIGHUP handler is not even registered - so sending the signal does nothing.

Reload fails closed. The new file is fully read, ${VAR}-interpolated and validated - the same load-time checks a fresh startup runs - before anything about the running server changes. If the edit is broken (bad YAML, an unset ${VAR}, a validation error such as the access_mode: unrestricted contradiction above), the reload is aborted, logged as an error, and the previously running policy stays in effect, untouched. Nothing is ever applied partially.

Pools are reused when possible. A database whose connection_url did not change keeps its existing connection pool - no dropped connections, no reconnect. Only a database whose DSN changed gets a fresh pool, and its old one is closed only after the new one is in place. Databases added to or removed from the file between reloads are picked up and torn down the same way.

A query already holding a connection is not interrupted. It finishes under the policy it started with; the new policy takes effect starting with the next tool call. A tool call still queued for a connection slot when its database's DSN changes may instead see a transient connection error and should be retried - the DSN change itself means a new pool is now in place.

Security notes and limitations

Read this section before pointing the policy layer at anything that matters. It is a defense-in-depth layer at the SQL text level, and it is honest about what that cannot do.

It does not replace Postgres roles or RLS

The policy layer decides what SQL it will send. Postgres decides what that SQL is allowed to do. Only the second one is enforced by the database, survives a bug in this parser, and applies to every other client of the same database.

Run the MCP server as a dedicated, least-privilege Postgres role regardless of the policy file: GRANT SELECT on exactly the tables the policy allows, nothing else, and use row-level security for tenant isolation - which require_predicate_on does not provide, as the next section explains. The policy layer then becomes what it is good at: a fast, agent-legible guardrail that produces actionable errors, sits in front of the database's own enforcement, and covers things SQL grants cannot express (row limits, PII masking, catalog denial).

Note the asymmetry in how the two kinds of failure behave:

• A bug in the policy layer fails closed. Anything it cannot resolve with certainty - an unqualified column, an unbounded LIMIT, a renamed wildcard, a cursor, a pg_stats value column, a statement kind it does not model, a mixed-case relation name - is rejected rather than executed.

• A misconfiguration fails open. columns.mode: all on a PII table with no mask rules is a valid, silent, wide-open policy. So is tables: {"*": {}}, and so is system_catalog_access: allow. Nothing warns you. (A rule that would never apply, or would make a table unqueryable, is rejected - see Contradictions rejected at load time - but a policy that simply restricts nothing is valid.) Review policy files the way you review GRANT statements.

require_predicate_on is a guardrail, not tenant isolation

This is the single most important thing to get right about the policy file.

require_predicate_on: [tenant_id] forces every query level that touches the table to constrain tenant_id against values the query itself names. It checks the shape of the predicate and nothing else. It never sees, and never could see, which tenant is asking:

-- Rejected: no predicate on tenant_id at all.
SELECT id, total FROM orders;
-- Accepted.
SELECT id, total FROM orders WHERE tenant_id = 42;
-- Also accepted. The policy layer has no idea 99 is somebody else's tenant.
SELECT id, total FROM orders WHERE tenant_id = 99;
-- Also accepted, and returns every tenant the agent cares to list.
SELECT id, total FROM orders WHERE tenant_id IN (1, 2, 3, 4, 5, 6, 7, 8, 9);

So what it buys you is real but narrow: an agent cannot read the table whole by forgetting a WHERE clause, and the error it gets back names the column it has to constrain. That stops the ordinary accident. It stops nothing that is trying.

Per-tenant isolation has to be enforced somewhere that knows who the caller is. Two places do:

• Postgres row-level security, with the tenant established per session (SET LOCAL app.tenant_id = ... inside the transaction, and a policy reading current_setting('app.tenant_id')). The database then filters the rows, whatever SQL arrives.

• A server-injected predicate - your own code rewriting the query to add AND tenant_id = <the caller's tenant> before it is sent.

This layer does neither. It does not know the caller's tenant, it never rewrites a predicate onto a query (the only rewrite it performs is LIMIT), and there is no configuration that makes it do either. Deploying require_predicate_on as if it were tenant isolation gives you a multi-tenant database that any agent can read across, with a config file that reads as though it could not.

(The setting was called required_filters before release. It was renamed precisely because "required filters" reads as a guarantee of which rows come back. The old key is refused at startup with a message pointing at the new one; there is no deprecated alias.)

A masked column may be returned, but not probed

A mask is applied to the cells of an output column, after the rows come back. That alone would leave the column open as an oracle: a predicate over it answers a guess through which rows come back, and an ordering exposes the true order, both without ever defeating the mask. So masking carries the same predicate ban that column exclusion does:

-- Allowed. Returning the de-identified value is the point of masking.
SELECT id, email FROM contacts;
-- Rejected. The row that comes back would confirm the address.
SELECT id FROM contacts WHERE email = 'ceo@acme.com';
-- Rejected. Sorting happens on the real value, before the mask runs.
SELECT id, email FROM contacts ORDER BY email;

A masked column is refused in WHERE, HAVING, JOIN ... ON (including the implicit ON of JOIN ... USING and of NATURAL JOIN), GROUP BY (including GROUPING SETS / CUBE / ROLLUP), ORDER BY, DISTINCT ON, a window's PARTITION BY / ORDER BY, an aggregate's FILTER (WHERE ...), and inside a sublink - everywhere an excluded column is refused, and allowed in exactly the one place an excluded column is not: the target list. The check also follows the ways a name can be smuggled into one of those positions: an output alias (SELECT email AS contact ... ORDER BY contact), a target-list position (ORDER BY 1), an unqualified name that might be the masked column when several relations are in scope, and a masked column re-exported from a CTE or a FROM-subquery - under its own name or under a new one.

The column checks and the mask planner resolve against the same provenance machinery, so all of these are refused:

WITH c AS (SELECT email AS e FROM users) SELECT * FROM c WHERE c.e = 'x';
WITH c AS (SELECT email AS e FROM users) SELECT e FROM c ORDER BY e;
WITH c(e) AS (SELECT email FROM users)   SELECT e FROM c WHERE e = 'x';
SELECT s.e FROM (SELECT email AS e FROM users) s WHERE s.e = 'x';
WITH a AS (SELECT email AS e FROM users), b AS (SELECT e AS f FROM a) SELECT f FROM b WHERE f = 'x';

Selecting the renamed column is still fine (WITH c AS (SELECT email AS e FROM users) SELECT e FROM c runs, masked); it is comparing and ordering that are refused.

Positional keys over a set operation are resolved the same way. A UNION / INTERSECT / EXCEPT node has no target list of its own - its arms do - so a position is resolved against the merged shape the mask plan itself is built from, which takes output names from the leftmost arm (as Postgres does) and marks a position masked if either arm masks it. All of these are refused:

SELECT id, email FROM users UNION ALL SELECT id, email FROM contacts ORDER BY 2;
SELECT id, email FROM users EXCEPT    SELECT id, email FROM contacts ORDER BY 2;
-- masked in one arm only ('orders.status' is not masked), and nested arbitrarily deep
SELECT id, status FROM orders UNION ALL SELECT id, email FROM users ORDER BY 2;
(SELECT id, email FROM users UNION ALL SELECT id, email FROM contacts) UNION ALL SELECT id, status FROM orders ORDER BY 2;
-- the merged column's name comes from the leftmost arm, so the alias is refused too
SELECT id, email AS e FROM users UNION ALL SELECT id, email FROM contacts ORDER BY e;

ORDER BY 1 on the same queries still runs: the ban is per output position, not per query. GROUP BY and DISTINCT ON never had this gap - Postgres' grammar attaches both to a leaf SELECT, never to the set-operation node, so they were already checked against the arm's own target list.

A position behind a * is refused, because it cannot be resolved at all. SELECT * FROM users ORDER BY 2 sorts on whatever the second column of users turns out to be; this layer has no catalog, so it can tell that the wildcard covers a masked column but not which position that column lands in. Guessing would fail open, so any ORDER BY <n> / GROUP BY <n> over a wildcard that expands over a masking table is refused - including ORDER BY 1, and including the wildcard reaching the position through a subquery, a column alias list, a CTE or a TABLE users UNION ALL TABLE contacts. Name the output columns and the position resolves normally. A wildcard over a table with no mask rules is unaffected.

Two gaps are known and not caught:

• A comparison written in the target list is caught by a different rule. SELECT (email = 'x') AS hit FROM users is an oracle with no predicate in it. The statement-level check does not see it, because a target list is not a filtering position; it is refused instead as an expression over a masked column, and that rule runs on agent SQL only (execute_sql, and the agent text explain_query embeds). Every agent statement goes through one of those, so in practice it is covered - but the two rules do not overlap, and a future caller that skipped the masking pass would not get this one.

• SELECT DISTINCT email FROM users runs. DISTINCT over the target list is not a filtering position and is not banned, so the query returns one masked row per distinct real value: the column's distinct cardinality leaks, and under a hash mask so do its equivalence classes. If that matters, exclude the column rather than masking it.

If a column must not be readable at all, exclude it (columns.mode: exclude) rather than masking it. Exclusion refuses the column in the target list too, and takes SELECT * on the table with it. Masking is for columns an agent legitimately needs to see in a de-identified form. Either way, tenant isolation and column-level confidentiality belong in Postgres grants and RLS; this layer is the guardrail in front of them.

hash is a pseudonym, not a secret

hash is an HMAC of the value under mask_salt, truncated to length hex characters (default 12, which is 48 bits).

What it gives you is linkability, not confidentiality. Equal inputs hash alike, so a client can still group, count and join on the values it got back without ever seeing a real one - that is the whole point, and it is genuinely useful. What it does not give you is a guarantee that the real value cannot be recovered, and that limitation is inherent to a deterministic function over a guessable domain:

• An agent that can get chosen values hashed under your salt can build a rainbow table offline. It does not need the salt itself, only the ability to submit a value and see its digest. It then hashes a plausible keyspace - every phone number in a region, every date of birth, every national ID format, the customer list it already has - and reverses the real hashes it was shown. Nothing about the mask is defeated directly; the digests simply match.

• The easy path to that is closed. A set operation that puts a masked column and a caller-written constant in the same output position - SELECT phone FROM users WHERE false UNION ALL SELECT '555-0100' - would have masked the constant, handing back the deployment's own hash of a value the caller picked, one guess per query and as many queries as it liked. That is now refused, in UNION, INTERSECT and EXCEPT alike. Closing that path does not change the underlying property, though: any channel that ever hashes an attacker-chosen value under the same salt reopens it, and this layer cannot promise there is no such channel.

• Low-cardinality columns are reversible even without a chosen-plaintext channel, by anyone who learns the salt. Truncation is not what makes that possible; a full-length digest of a small keyspace is just as reversible. Truncation does add collisions on top (expect them around ~16M distinct values at 48 bits).

• Raise length for large domains. The maximum is 64 hex characters, the minimum 8. length: 32 costs nothing but output width and removes the collision concern; it does not remove the reversibility one.

• The salt is mandatory and there is no default, precisely so that this strategy cannot silently degrade into a reversible no-op. It must be at least 8 characters; below 16 the server logs a warning.

• Keep the salt secret and out of the config file (${PGWARDEN_MASK_SALT}), give each database its own if hashed values must not be correlated across them, and remember that rotating it changes every hashed value.

Use hash when the agent needs to correlate rows without reading values, and the domain is large enough that guessing it is impractical. For anything that must stay confidential - a national ID, a phone number, a card number, any column with a small or enumerable keyspace - use redact or null, which return no function of the real value at all. Treat hash output as pseudonymized data (a persistent, stable identifier for a person), never as anonymized data.

email preserves the domain by design

ajay.ranwa@gokiwi.in becomes aj********@gokiwi.in. The domain is kept because it is usually not the sensitive part and is genuinely useful for analysis (per-tenant, per-provider breakdowns). A rare or personal domain can still identify an individual. Use redact, null or hash for columns where the domain matters.

regex passes non-matching values through unchanged

A regex rule replaces matches. A value with no match is returned raw. That is correct for scrubbing a pattern out of free text and dangerous if you assumed the rule was total:

# Scrubs account numbers out of free text. Anything else is returned as written.
body:
  strategy: regex
  params:
    pattern: "ACC-[0-9]{6,}"
    replacement: "ACC-******"

If a column must never be emitted raw, anchor a catch-all - pattern: "^.*$" with flags: "s" so that . also matches newlines - or use redact, which is what a total regex amounts to. Two further notes: replacement supports backreferences, so do not capture the part you meant to hide; and because Python's re has no timeout, values longer than max_input_length (4096 by default) are fully redacted instead of matched, which keeps a catastrophically backtracking pattern from stalling the server on one hostile row.

Row limits cap a response, not total exfiltration

default_row_limit and max_row_limit bound a single result set. They do not bound what an agent can accumulate:

• OFFSET paging walks a whole table 500 rows at a time.

• Several small queries add up to one large one, and nothing correlates them.

Treat row limits as a context-window and cost guard, not as a data-loss control.

Two holes that are closed:

• DECLARE ... CURSOR used to walk past the limit entirely, because the injected LIMIT only ever lands on a SELECT and the rows arrive through a later FETCH ALL that this layer never sees. Declaring a cursor is now refused outright while enforce_row_limits is on, over any table. Set enforce_row_limits: false if an agent genuinely needs cursors - at which point nothing bounds a result set, except that a cursor over a masked column is still refused, because masking cannot follow rows into a later FETCH.

• A second statement after a ;. psycopg executes every statement in the string it is given and returns only the last one's rows, so SELECT 1; SELECT email FROM customers used to run a query that neither the injected LIMIT nor the mask plan applied to - the limit and the plan were computed for the statement the caller thought it was sending. Agent SQL is now held to exactly one statement, and the text is parsed and re-attached as an AST node rather than concatenated into a larger query. (Server-authored SQL may still be multi-statement; the one legitimate case is the HypoPG prologue that explain_query needs, which is recognised structurally rather than by a flag a future caller could reach for.)

Some things remain readable that you might not expect

• pg_stats is allowed by default, but its sample values are not. The view's most_common_vals, most_common_elems, histogram_bounds and elem_count_histogram columns hold values copied verbatim out of the tables they describe - including out of columns you excluded or masked, since the policy's column rules apply to customers, not to a catalog view about customers. Those four columns are therefore refused on pg_stats, pg_stats_ext and pg_stats_ext_exprs whenever any table in the policy configures columns or mask, and so is a SELECT * that would expand over them. The WHERE tablename = '...' predicate is deliberately not consulted: it is agent-controlled and may be absent, negated or OR-ed, so it can never prove which table the rows describe. The summary columns (n_distinct, null_frac, avg_width, correlation, schemaname, tablename, attname, ...) stay readable, which is what the health checks and the index advisor read. One server-side feature does lose something: when a query from pg_stat_statements still has $1 placeholders, the parameter substitution used by explain_query and the index advisor tries to pick a realistic value out of most_common_vals / histogram_bounds; under a restrictive policy that lookup is refused, logged as a warning, and generic placeholder values are used instead. A policy where no table restricts or masks anything leaves the view untouched. (pg_statistic, the underlying table, is denied outright.)

• information_schema is fully readable by default (information_schema.*), so the existence and shape of tables outside the allowlist is discoverable even though their contents are not.

• EXPLAIN output is not masked or limited, as described in What is enforced, and where.

• Postgres still filters pg_stats by what the connecting role may read, which is another reason to run as a least-privilege role.

Health checks report their own failures, one line at a time

analyze_db_health runs each check independently. A check that raises - because the policy denied a relation it needs, or for any other reason - reports on its own line and every other check still contributes its normal output:

...
Connection health: Check failed - PolicyViolation: Access to system catalog 'pg_catalog.pg_stat_activity' is denied ...
...
Health check summary: 1 of 11 checks failed (Connection health). All other results above are valid.

The failing line is prefixed Check failed - <ExceptionType>: <message>, and the trailing summary names every check that failed.

That matters for two checks in particular:

• connection reads pg_stat_activity, which is on the hard denylist because it exposes every other session's user, database and current SQL text. Under a default policy that one check fails and the rest of the report is unaffected. Opt in with one line, accepting that agents can then see other sessions:

      allowed_system_catalogs:
        # ... the rest of the built-in list, which this key replaces ...
        - pg_stat_activity

  Or scope the call and skip it: analyze_db_health(health_type="index,vacuum,buffer,constraint,replication").

• sequence reads each sequence relation directly, and a sequence is never written under tables:. The policy engine therefore permits a sequence named by Postgres's serial / identity convention - <allowlisted_table>_<column>_seq, in a schema the policy allows - without it being listed: users_id_seq and user_accounts_external_id_seq resolve as long as users and user_accounts are allowlisted. Anything outside that convention (a hand-created CREATE SEQUENCE order_numbers, a renamed sequence, one owned by a table that is not allowlisted) is still refused - as is a sequence whose SELECT grant Postgres itself withholds. The check skips those and accounts for them in a trailing line (N sequences could not be read and were skipped, so their usage is unknown: ...) rather than failing or, worse, reporting every sequence as healthy. List such sequences under tables: if the check needs to cover them. The exemption is name-based, not catalog-based: an ordinary table called <allowlisted-table>_<something>_seq becomes readable too.

If every check fails the summary says so explicitly (all N checks failed ... No health data could be collected.), so an empty-looking report is never mistaken for a clean bill of health.

Configuration traps

• A bare table key matches that table name in any allowed schema. tables: {orders: {}} with allowed_schemas: [public, reporting] allows both public.orders and reporting.orders. Write the key qualified when you mean one of them.

• allowed_system_catalogs replaces the built-in list, it does not extend it. A three-entry list here disables most of the server's own tooling.

• A require_predicate_on column must be both readable and unmasked. Hiding it with columns, or masking it, would make the table unqueryable - the predicate is mandatory and referencing the column is forbidden - so both combinations are rejected at load time rather than surfacing as a per-query error. So is a per-table rule written under the "*" key.

• require_predicate_on is not tenant isolation, whatever the column is called. It checks that a predicate exists, not what it says, so WHERE tenant_id = <any tenant> satisfies it. Use RLS. This is the mistake most likely to turn a policy file into a false sense of safety.

• required_filters is not a valid key. It was the pre-release name of require_predicate_on and is refused at startup with a message naming the replacement. There is no deprecated alias, and a config carrying both keys is still an error.

• --config implies read-only. A policy can only be enforced by parsing the SQL, so supplying a config file selects the parsing driver even under --access-mode=unrestricted. execute_sql will then reject DDL and DML - and the policy engine independently refuses any statement kind it does not model, so DML, COPY, PREPARE and DDL are rejected twice over. This is deliberate; it is also a surprise if you expected unrestricted to still write.

• Every renamed environment variable keeps its old name working, including POSTGRES_MCP_MASK_SALT. PGWARDEN_MASK_SALT, PGWARDEN_CONFIG, PGWARDEN_DATABASE_URI and PGWARDEN_INCLUDE_LANGFUSE_TRACE all fall back to their pre-rename spelling and log a warning naming the replacement. The fallback is a migration aid, not a supported configuration: a future release drops it, so rename the variables rather than relying on it. Note the fallback applies to the variables the server reads directly - a ${POSTGRES_MCP_MASK_SALT} written inside a policy file is an ordinary ${VAR} interpolation and is read literally, with no aliasing.

• A SIGHUP reload that changes a database's DSN can surface a transient connection error to a call still queued for that database's old pool. Only a query that has already been handed a connection is guaranteed to finish under the policy it started with; one still waiting for a free slot when the old pool closes should be retried against the now-current one.

Inherited capabilities

Everything in this section comes from crystaldba/postgres-mcp and works unchanged under a policy - subject to the checks in What is enforced, and where, and never row-limited or masked, because these tools run server-authored SQL.

MCP tool reference

The server exposes functionality through MCP tools only, not resources, because tool support is far more widespread across MCP clients.

Every tool that touches a database also accepts an optional database parameter naming the configured alias to use. It can be omitted when the server has a single database; with several configured it is required, and list_databases reports the valid values.

Access modes

--access-mode controls what kind of statement may run:

• unrestricted (the default) allows full read/write access to data and schema. Suitable for development.

• restricted limits operations to read-only transactions and caps query execution time at 30 seconds. Suitable for production.

Read-only enforcement is not just a flag: because Postgres has no session-level read-only mode, the statement is parsed with pglast before execution and anything that could escape the read-only transaction - notably COMMIT and ROLLBACK, as in ROLLBACK; DROP TABLE users - is rejected. (If you have enabled unsafe stored-procedure languages on the database, those protections can be circumvented from inside a function; PL/pgSQL and PL/Python cannot issue COMMIT or ROLLBACK.)

Access modes address integrity: what an agent may change. The policy layer addresses confidentiality and availability on top of them - which tables and columns an agent may read, how many rows it may pull back, and whether PII is de-identified on the way out. Supplying --config selects the same parsing driver that restricted mode uses, so a policed server is read-only in either access mode.

This flag is a server-wide default, not a per-database setting - a policy file can tighten it further for individual databases. See Per-database access mode.

Postgres extension setup

Index tuning and the full performance analysis need two extensions:

• pg_stat_statements records the runtime and resource consumption of each query, which is how the server finds tuning targets.

• hypopg simulates the planner's behaviour after adding an index, without building it.

On AWS RDS, Azure Database for PostgreSQL and Google Cloud SQL both are usually available already:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;
CREATE EXTENSION IF NOT EXISTS hypopg;

On self-managed Postgres, pg_stat_statements must additionally be listed in shared_preload_libraries, and hypopg may need installing at the system level because it does not always ship with Postgres.

Both catalogs are in the built-in allowed_system_catalogs list, so a deny policy does not get in the way.

Index tuning

analyze_workload_indexes and analyze_query_indexes implement an adaptation of Microsoft's Anytime Algorithm for index selection. It runs in four stages:

1. Identify queries worth tuning. Either you supply them, or the workload is analyzed through pg_stat_statements using mean execution time with thresholds on call count and mean time. Queries are normalized so that everything from one template counts once - a limited form of workload compression - and then weighted equally.

2. Generate candidate indexes. The SQL is parsed and every column used in a filter, join, grouping or sort becomes a candidate, including multicolumn combinations. Only one permutation of each multicolumn candidate is considered (chosen at random), because permutations usually perform equivalently and the search space is otherwise unmanageable.

3. Search for the best configuration. hypopg provides "what if?" cost estimates from the real Postgres cost model. A greedy search finds the best one-index solution, then the best index to add to it, and so on, terminating when the time budget is exhausted or a round produces no gain above a 10% minimum improvement threshold. Because normalization strips out parameter constants, realistic values are sampled from table statistics to produce plannable queries (Postgres 16's generic plans have limitations here, for example around LIKE).

4. Cost-benefit analysis. Rather than optimizing purely against a storage budget, the search selects a point on the Pareto front using relative changes: by default the log10 performance improvement must be at least 2x the log10 space cost, which works out to allowing a 10x space increase for a 100x speedup.

Compared to Dexter, this searches a larger space with different heuristics - better solutions, longer runtime. The output shows the work done in each round, including before/after query plans, which gives the calling LLM context for its recommendations.

Experimental: index tuning by LLM. Passing method="llm" swaps the heuristic search for Optimization by LLM: the schema and query plans are given to an LLM, which proposes index configurations that are then scored with hypopg and fed back for another round, until no further improvement appears. It can help when the search space is large or many-column indexes matter. It requires OPENAI_API_KEY to be set.

Database health checks

analyze_db_health adapts the checks from PgHero:

• Index health. Unused, duplicate, invalid and bloated indexes. Autovacuum marks index entries for dead tuples reusable but does not compact index pages, so pages accumulate few live references over time.

• Buffer cache hit rate. The proportion of reads served from the buffer cache rather than disk, for both tables and indexes.

• Connection health. Connection count and utilization - running out of connections is the acute risk, but a high idle or blocked count is a signal too. Reads pg_stat_activity, which is denied by default; see above.

• Vacuum health. Tables approaching transaction ID wraparound. Postgres uses 32-bit transaction IDs and must "freeze" old rows before those IDs are reused; a database that falls behind stops accepting writes.

• Replication health. Lag between primary and replicas, replication status, and replication slot usage.

• Constraint health. Invalid constraints, which can appear after a bulk load or a recovery.

• Sequence health. Sequences at risk of exceeding their maximum value.

Query plans and hypothetical indexes

explain_query returns the planner's execution plan and cost estimates. With analyze=True it runs the query for real statistics; with hypothetical_indexes it uses hypopg to show what the plan would be after adding indexes that were never built (the two options cannot be combined).

[
  {"table": "users", "columns": ["email"], "using": "btree"},
  {"table": "orders", "columns": ["user_id", "created_at"]}
]

Example prompts

Check the health of my database and identify any issues.

What are the slowest queries in my database? And how can I speed them up?

Analyze my database workload and suggest indexes to improve performance.

Transports

--transport selects stdio (the default), sse or streamable-http. The HTTP transports let several MCP clients share one server, possibly a remote one, and bind to localhost:8000 by default (--sse-host / --sse-port, --streamable-http-host / --streamable-http-port).

docker run -p 8000:8000 \
  -v /etc/pgwarden:/etc/pgwarden:ro \
  -e PGUSER -e PGPASSWORD -e PGWARDEN_MASK_SALT \
  pgwarden-mcp --config /etc/pgwarden/policy.yaml --transport=sse --sse-host=0.0.0.0

Client configuration for SSE, in Cursor's mcp.json or Cline's cline_mcp_settings.json:

{
  "mcpServers": {
    "pgwarden": {
      "type": "sse",
      "url": "http://localhost:8000/sse"
    }
  }
}

Windsurf's mcp_config.json uses serverUrl instead of url.

Development

git clone https://github.com/gokiwitech/pgwarden-mcp.git pgwarden-mcp
cd pgwarden-mcp
uv sync

Run the server against a database with no policy:

uv run pgwarden-mcp "postgresql://user:password@localhost:5432/dbname"

The checks CI runs, in order:

uv run ruff format --check .
uv run ruff check .
uv run pyright
uv run pytest -v --log-cli-level=INFO

The policy layer's tests are in tests/unit/test_policy_*.py, tests/unit/test_masking*.py, tests/unit/test_agent_sql_invariant.py and tests/integration/test_policy_guardrails_integration.py. The integration suite needs Docker.

Source layout for the policy layer:

License and credits

MIT. See LICENSE.

This is a hard fork of crystaldba/postgres-mcp, copyright Crystal Corp., originally authored by Johann Schleier-Smith - upstream's copyright notice is retained in full. Upstream's database health checks are adapted from PgHero by Andrew Kane. The index advisor follows Microsoft's Anytime Algorithm of Database Tuning Advisor for Microsoft SQL Server, and the experimental LLM tuner follows Optimization by LLM.