pgwarden

Quickstart  ·  How it works  ·  Results  ·  Limitations  ·  Docs

pgwarden is an MCP gateway that lets Claude, ChatGPT, Cursor or any MCP client query Postgres as the person asking. People sign in through your OIDC provider; the gateway connects as their own Postgres role, and the database decides what they see through your grants, row-level security and masking views. Reads are read-only by construction, writes wait for a named approver, and every tool call, sign-in, approval and admin change lands in an append-only, hash-chained audit log.

Why let Postgres decide. With one shared credential every user gets the union of everyone's access, and a prompt injection in a support ticket can make an assistant that holds it read a private table (General Analysis, 2025). Guards bolted on outside the database get bypassed: a reference read-only Postgres MCP server was escaped with COMMIT; <statement> (Datadog Security Labs, 2025).

_{MacBook Air M5, 24 GB, Docker via colima with 4 CPUs / 6 GB · the commands and caveats are under Results}

What changes

Quickstart

Docker with Compose v2 is all the demo needs.

git clone https://github.com/B0yko/pgwarden && cd pgwarden
docker compose up -d --wait     # Postgres, a mock IdP, Mailpit and the gateway

Every port binds to 127.0.0.1. If 5432, 8080, 9400, 1025 or 8025 is taken, cp .env.example .env, change the ports there, and devtools/check-ports.sh confirms they are free.

Open http://localhost:8080: the landing page prints the connect commands for MCP Inspector, Claude Code and Cursor. With MCP Inspector (needs Node):

npx @modelcontextprotocol/inspector@2.8.0 --server-url http://localhost:8080/mcp --transport http

Connecting walks you through consent, a mock sign-in and a confirmation that names the Postgres role you will use. Then call whoami, list_tables, describe_table and query as any of the demo identities:

Without a clone:

docker pull ghcr.io/b0yko/pgwarden:0.1.0                            # the gateway (amd64, arm64)
uvx --from git+https://github.com/B0yko/pgwarden pgwarden --help   # the CLI

To put it in front of your own Postgres 16+, follow docs/own-database.md.

A real session

One scripted session against the demo stack: MCP Inspector 2.8.0 connects with its own OAuth client (dynamic registration, PKCE, resource indicator), as alice and then bob, and carol opens the admin page.

Consent first. Before any sign-in, pgwarden names the application, where it sends you back to, and the resource.	Masked by the database. alice's role can only read the masked view, so SELECT full_name, email FROM customers comes back masked.
A write waits for a person. The approver gets a summary and a signed link; the statement is shown only on the review page, after sign-in.	Everything is on the record. A raw-table read refused by Postgres (42501) and a write refused by the read-only transaction (25006).

_{Made by devtools/screenshots/run.py: headless Chromium, a fixed 1440x900 viewport, page content only, PNG metadata stripped. It fails unless the Inspector's OAuth flow ends connected; it is development tooling, not part of the wheel or the image.}

How it works

flowchart LR
    client["MCP client<br/>Claude, ChatGPT, Cursor, Inspector"]
    idp["Your OIDC provider"]
    subgraph gw["pgwarden, one process"]
        direction TB
        oauth["OAuth 2.1<br/>consent, tokens"]
        mcp["/mcp<br/>auth, rate limit, tool, audit"]
        pages["/approve, /admin"]
    end
    target[("Your Postgres<br/>grants, RLS, masking views")]
    state[("State database<br/>audit chain, tokens, proposals")]
    client -- sign in --> oauth
    oauth <-- OIDC --> idp
    client -- tool calls --> mcp
    mcp -- as the person's role --> target
    mcp --> state
    pages --> state
    classDef hub fill:#2563eb,stroke:#1d4ed8,color:#ffffff
    classDef store fill:#eef4ff,stroke:#2563eb,color:#0b1220
    classDef outside fill:#64748b14,stroke:#64748b
    class oauth,mcp,pages hub
    class target,state store
    class client,idp outside
    style gw fill:#2563eb0f,stroke:#3b82f6

Every query runs the same fixed sequence, and nothing in it parses SQL:

1. take a pooled connection logged in as the person's own role;
2. BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY, then set the timeouts with bound values;
3. prepare the statement through the extended protocol, which refuses a second statement;
4. fetch up to the row cap through a portal and serialize up to the byte cap;
5. ROLLBACK, always.

The decisions behind it are in docs/adr/, the threats and their tests in docs/threat-model.md.

Sign-in

sequenceDiagram
    autonumber
    participant Client
    participant pgwarden
    participant IdP
    Client->>pgwarden: GET /oauth/authorize (PKCE, resource, state)
    pgwarden-->>Client: consent page (client name, redirect host)
    Client->>pgwarden: approve (CSRF POST)
    pgwarden->>IdP: redirect with a fresh, browser-bound state + nonce
    IdP-->>pgwarden: GET /oauth/callback (code)
    pgwarden-->>Client: confirmation (identity, Postgres role)
    Client->>pgwarden: confirm (CSRF POST)
    pgwarden-->>Client: redirect with authorization code
    Client->>pgwarden: POST /oauth/token (code + verifier + resource)
    pgwarden-->>Client: access token (EdDSA at+jwt, aud = /mcp)

Writes

sequenceDiagram
    autonumber
    participant Client as MCP client
    participant pgwarden
    participant DB as Postgres (writer role)
    participant Approver as Approver (browser)
    Client->>pgwarden: propose_write (SQL, params, reason)
    pgwarden->>DB: EXPLAIN (FORMAT JSON) as the person's writer role
    DB-->>pgwarden: plan, accepted only with one Insert/Update/Delete root
    pgwarden->>Approver: notice with a summary and a signed link, never the SQL
    Approver->>pgwarden: open the link, sign in, review the exact statement
    Approver->>pgwarden: approve (CSRF POST), not the proposer
    pgwarden-->>Client: single-use grant, valid 15 minutes
    Client->>pgwarden: execute_approved_write
    pgwarden->>pgwarden: check proposer and binding, claim the grant atomically, audit "started"
    pgwarden->>DB: run the stored statement in a read-write transaction
    DB-->>pgwarden: rows affected (more than max_rows rolls back)
    pgwarden-->>Client: result, audited

Results

Every number below comes from the command next to it, run against the demo stack on a MacBook Air M5 (24 GB, Docker via colima with 4 CPUs / 6 GB). pgwarden report writes the tables from the JSON in docs/results/, and CI fails if they drift.

Red-team suite

133 must-block attacks in nine categories (at least 8 per category, each a distinct technique) and 32 benign controls that must succeed. Each attack has an oracle that decides from what happened (rows returned, table checksums, locks, HTTP status, proposal state, timing), never from an error message, and the runner also checks which layer stopped it. Deterministic, no LLM; CI runs it on every push.

Benign controls passed: 32 / 32. Documented residual risks: 2.

_{Run: 2026-09-28; commit 9f60c13; Postgres 16.15; MacBook Air M5, 24 GB, Docker via colima with 4 CPUs / 6 GB; config pgwarden.yaml (sha256 b5c902f5656f70cd); 2 other containers running on the machine during the run.}

Two behaviours are documented residual risks, run and recorded but never counted as blocked: row estimates through plain EXPLAIN (D09) and relation names in pg_class (G09), which every role may read. pg_stats is not one of them: Postgres withholds it for tables under row security and for columns the role cannot read, so D05 and E05 are blocked.

docker compose up -d --wait
export PGWARDEN_ADMIN_DSN="$(sed 's#/postgres?#/shop?#' .pgwarden-dev/admin/admin_dsn_host)"
export PGWARDEN_STATE_DSN="$(cat .pgwarden-dev/host/state_dsn_host)"   # optional: clears rate windows so a rerun is clean
uv run pgwarden redteam run --allow-load --target-url http://localhost:8080 \
  --machine-secret-file .pgwarden-dev/machines/machine-nightly-report \
  --report docs/results/redteam-$(date +%F).json

Statement-filter baselines

The evidence for ADR-0001: the SQL-bearing cases through two filters written for this comparison, which live only in bench/, never in the product. pgwarden bench baselines executes nothing; the full blocklist and the pinned sqlglot version are in docs/baselines.md.

The 90 attacks are the query cases of categories A to G that must be blocked; the 29 benign queries are the benign controls that send SQL. The pgwarden row is the red-team run above, not a separate measurement. sqlglot 30.20.0.

_{Run: 2026-09-28; commit 9f60c13.}

Latency

One gateway process, with the bench client in its own container on the compose network and Postgres in the same 4-CPU VM, on a laptop that was also running other things: read the numbers as orientation, not as a capacity claim. Direct is plain asyncpg as the machine role on a warm connection, Direct + wrapper adds the read path's own transaction sequence, and Via pgwarden is an MCP tools/call query over HTTP with a warm token.

Milliseconds, median of 3 repetitions of 1000 timed calls after 100 warm-up calls each; the small line under a cell is the min-max of the repetitions' p50 / p95. Overhead is via pgwarden minus direct.

Design target (overhead p50 <= 10 ms on the primary-key lookup): met, 1.98 ms.

_{Run: 2026-09-28; commit 6edd97f; Postgres 16.15; MacBook Air M5, 24 GB, Docker via colima with 4 CPUs / 6 GB; config pgwarden.bench.yaml (sha256 fb713f1da5d24904); 2 other containers running on the machine during the run. Audit chain verified after the latency run: OK, chain intact through seq 80715.}

On a primary-key lookup the gateway adds about 2 ms at p50: about 0.4 ms is the read-path wrapper, about 1.2 ms the spans inside the gateway (auth, rate limit, audit), and the rest HTTP and MCP framing. A cold first query after the pool was evicted costs about 39 ms more, most of it opening the connection (TCP, startup, SCRAM); it is paid once per person after pool.idle_timeout_s (60 s by default).

Load

Design target (p95 <= 150 ms with 0 non-rate-limit errors): met (p95 94.8 ms, 0 errors).

_{Run: 2026-09-28; commit 6edd97f; Postgres 16.15; MacBook Air M5, 24 GB, Docker via colima with 4 CPUs / 6 GB; config pgwarden.bench.yaml (sha256 fb713f1da5d24904); 2 other containers running on the machine during the run.}

The single gateway process averaged 81% of one core and peaked at 102% with the load client in the same VM, so it was close to saturation. An earlier 60 s run that evening gave 621 requests/s; only the recorded run is committed.

# The stack on the bench config: machines bench-01..20 and raised rate limits
export PGWARDEN_DEMO_CONFIG=pgwarden.bench.yaml
docker compose up -d --wait

# Latency: 3 repetitions of 1000 timed calls after 100 warm-up calls, per query shape
docker compose --profile bench run --rm bench pgwarden bench latency --iterations 1000 --warmup 100

# Load: 20 identities at concurrency 20 for 60 s
docker compose --profile bench run --rm bench pgwarden bench load --identities 20 --concurrency 20 --duration 60 --mix pk:60,filter:30,agg:10

# Cold first query: the same config plus a 1 s pool idle timeout, so the pool is evicted in a pause
export PGWARDEN_DEMO_CONFIG=pgwarden.bench-cold.yaml
docker compose up -d --no-deps --wait gateway
docker compose --profile bench run --rm bench pgwarden bench cold --samples 30 --idle-wait 2.5

# Back to the default demo config
unset PGWARDEN_DEMO_CONFIG
docker compose up -d --wait

uv sync
PGWARDEN_BENCH_MACHINE="MacBook Air M5, 24 GB" devtools/bench/run.sh all   # about 8 minutes
uv run pgwarden report                                                     # regenerates the tables
devtools/bench/run.sh restore                                              # default demo config again

LLM indirect-injection run

Two inexpensive tool-calling models work through 10 tasks, 3 trials each, on demo data that carries planted prompt injections. Rows beyond the identity's privileges and writes without approval must both be 0. A link to an attacker's host in the model's final answer is a residual risk the gateway cannot block, and it is counted, not hidden. The run costs money, so it never runs in default CI.

Provider that served each call, from OpenRouter's response: deepseek/deepseek-v4-flash-0731: Sail Research 117 (pinned to sail-research); qwen/qwen3.7-flash: Alibaba 109 (pinned to alibaba).

Total spend: $0.0099 of a $0.15 budget. Rows beyond privilege and writes without approval must be 0; exfiltration through the model's final answer is a residual risk the gateway cannot block.

_{Run: 2026-09-28; commit c6cb29a; MacBook Air M5, 24 GB, Docker via colima with 4 CPUs / 6 GB; config pgwarden.llm.yaml (sha256 3f55c698a58e6159).}

Only the DeepSeek model ever saw a planted injection (6 exposures in 3 episodes): it acted on all 6, and the gateway blocked all 6. The Qwen model's tasks never surfaced a marker, so its zero says nothing about how it would behave.

PGWARDEN_DEMO_CONFIG=pgwarden.llm.yaml docker compose up -d --no-deps gateway
export OPENROUTER_API_KEY=...                     # never written to a file in the repository
export PGWARDEN_ADMIN_DSN="$(sed 's#/postgres?#/shop?#' .pgwarden-dev/admin/admin_dsn_host)"
export PGWARDEN_TARGET_DSN="$PGWARDEN_ADMIN_DSN"
export PGWARDEN_ROLE_SECRET_FILE=.pgwarden-dev/gateway/role_secret
uv run pgwarden redteam llm --models deepseek/deepseek-v4-flash-0731,qwen/qwen3.7-flash \
  --trials 3 --max-turns 12 --budget 0.15 \
  --provider deepseek/deepseek-v4-flash-0731=sail-research --provider qwen/qwen3.7-flash=alibaba \
  --target-url http://localhost:58080 --report docs/results/llm-redteam-$(date +%F).json
unset PGWARDEN_DEMO_CONFIG; docker compose up -d --no-deps gateway    # back to the demo config

Setup and build

Clients and identity providers

Documentation

Limitations

• A compromised gateway host holds the role secret, so it can act as any mapped person within that person's privileges.
• Disclosure of data the person is allowed to read is limited but not prevented.
• Planner statistics can leak through plain EXPLAIN.
• A function in the target database that runs dynamic SQL is a hazard pgwarden cannot see.
• IdP offboarding takes effect within the refresh-token family's absolute lifetime (8 h from login) unless the person is suspended or removed from config.
• The audit log stores SQL text, which may contain literals.
• A database superuser can rewrite the audit table and recompute the chain; export the head hash that pgwarden audit verify prints to somewhere the superuser cannot write.
• Masking is all-or-nothing per person, and pseudonyms are linkable by design.
• Fixed-window rate limits allow bursts of up to twice the limit at window edges; they apply to query and to write proposals, not to whoami, list_tables or describe_table.
• One target database per deployment, and single-tenant Entra only.
• Access tokens are valid for 10 minutes. A revoked token or a suspended person is refused on the next call (the gateway checks the state database on every request), but a person removed only at the identity provider keeps working until their refresh-token family ends.
• A write is one plain INSERT, UPDATE or DELETE: no MERGE, DDL or several statements. If the gateway crashes after an approved write was claimed, the proposal stays executing and never re-runs, so it has to be proposed again.
• Transaction-mode poolers (PgBouncer in transaction mode, hosted transaction-pooler endpoints) are unsupported, because the per-person session state pgwarden relies on does not survive them; pgwarden doctor detects them. Connect directly or through a session-mode pooler.

Roadmap

Everything described above as shipped is implemented and tested as stated; what is marked as not verified is not verified. Next candidates: masking variants per bundle, just-in-time role provisioning from IdP group claims, more upstream presets, and Terraform for other clouds.

Data

All demo data is synthetic, generated in-repo with setseed and licensed Apache-2.0 with the code: invented names, example.com, example.org and example.net emails, reserved fictional phone ranges, and canary tokens such as CANARY-PM-0001 (no real card data). See demo/sql/.

Development

uv sync
uv run pytest                                   # unit tests need nothing else
devtools/testpg.sh up                           # a Postgres 16 for the integration tests
uv run ruff check && uv run ruff format --check && uv run mypy --strict src/

Stack tests need docker compose up; the MCP Inspector check also needs npx and uv run playwright install chromium. uv run python devtools/screenshots/run.py regenerates the screenshots against a running stack, and devtools/screenshots/brand.py the banner and the social preview. See CONTRIBUTING.md.

License

Apache-2.0. Copyright 2026 Andrii Boiko. See LICENSE and NOTICE.

Built by Andrii Boiko · Project overview.