pgwarden

Governed Postgres access for AI assistants. pgwarden is a drop-in MCP gateway that lets Claude, ChatGPT, Cursor or any MCP client query your Postgres database as the person asking — no shared password. Identity comes from your OIDC provider through OAuth 2.1; the database enforces access through each person's own login role, the DBA's grants, row-level security and masking views. Reads are read-only by construction, writes wait for a named human approver, and every tool call, sign-in, approval decision and admin change lands in an append-only, hash-chained audit log.

One shared database credential gives every user the union of everyone's access, and a prompt injection in a support ticket can make an assistant holding that credential read a secrets table (General Analysis on Supabase MCP, 2025). Guarantees bolted on outside the database get bypassed: a reference read-only Postgres MCP server was escaped with COMMIT; <statement> (Datadog Security Labs, 2025). pgwarden's answer is to let Postgres decide, per person.

• No SQL parsing. pgwarden never inspects, rewrites or allowlists your SQL (ADR-0001). It connects as the person's Postgres role and lets grants, RLS and a read-only transaction decide.

• One login role per person, with credentials derived from a secret and sent only as SCRAM verifiers (ADR-0002). RLS keyed on session_user cannot be spoofed from SQL.

• PII masking by generated security_barrier views, enforced by the database, not by post-processing (ADR-0005).

• Writes only through a human-approved queue, validated by EXPLAIN, bound to the reviewed statement, executed at most once (ADR-0006).

• Tamper-evident audit log, verified independently of the SQL that wrote it (ADR-0007).

• Backed by measurements: a red-team suite with per-category, oracle-verified results; a comparison against statement-filter baselines; latency overhead and a load test — all reproduced by the commands below.

Quickstart (under 5 minutes)

You need Docker (Compose v2). The MCP Inspector example below also needs Node (npx), and the CLI example needs the uv CLI.

git clone https://github.com/B0yko/pgwarden && cd pgwarden
cp .env.example .env            # optional: change host ports (all bind to 127.0.0.1)
devtools/check-ports.sh         # fail early if a port is taken
docker compose up -d --wait     # Postgres, a mock IdP, Mailpit, and the gateway

Open http://localhost:8080 for the landing page and the exact commands to connect MCP Inspector, Claude Code and Cursor. For example:

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

Connect, and the browser walks you through consent, a mock sign-in (pick bob), and a confirmation showing the Postgres role you will use. Then call whoami, list_tables, describe_table and query. Sign in as alice to see PII masked; as bob to see raw data but only EU rows; propose a write and approve it in Mailpit at http://localhost:8025. The admin UI is at /admin.

A prebuilt multi-arch image of the gateway is published with each release: docker pull ghcr.io/b0yko/pgwarden:0.1.0 (the compose file builds the same image from source; pgwarden serve is its default command).

The CLI runs without a clone, too:

uvx --from git+https://github.com/B0yko/pgwarden pgwarden --help

Related MCP server: PostgreSQL MCP Server

How it works

flowchart LR
    C["MCP client<br/>(Claude, ChatGPT, Cursor, Inspector)"] -- HTTPS --> GW
    subgraph GW["pgwarden (FastAPI, one process)"]
      OA["/oauth/*, /.well-known/*<br/>OAuth 2.1 AS facade"]
      MCP["/mcp<br/>stateless streamable HTTP"]
      WEB["/approve, /admin<br/>Jinja2 pages"]
      MW["auth &rarr; rate limit &rarr; tool &rarr; per-person pool &rarr; audit"]
    end
    OA -- consent, then --> IDP["upstream OIDC IdP"]
    MCP --> MW
    MW -- "connects AS pw_u_&lt;person&gt;" --> T[("target DB<br/>grants + RLS + pw_masked views")]
    MW -- "hash-chained audit, tokens, proposals" --> S[("state DB")]

Every query call runs the same fixed sequence, with no SQL parsing anywhere: acquire the person's connection, BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY, set the timeouts with bound values, prepare the statement through the extended protocol (which rejects a second statement), fetch up to the row cap through a portal, serialize up to the byte cap, and always ROLLBACK. See docs/adr for the decisions and docs/threat-model.md for the threat model.

The OAuth flow

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

A real session

The four screenshots below come from one scripted session against the demo stack: MCP Inspector 2.8.0 connects through its own OAuth client (dynamic client registration, PKCE, resource indicator), first as alice, then as bob, and the admin page is opened as carol.

devtools/screenshots/run.py produces them (headless Chromium, fixed 1440x900 viewport, page content only, PNG metadata stripped) and fails if the Inspector's OAuth flow does not end connected with the tool list visible. It is development tooling and is not part of the wheel or the production image.

The approval flow

sequenceDiagram
    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

All numbers below come from the commands shown, on a MacBook Air M5, 24 GB, Docker via colima with 4 CPUs / 6 GB, against the demo stack. Regenerate the tables with pgwarden report.

Red-team suite

133 must-block attacks in nine categories (at least 8 per category, each a distinct technique) and 32 benign controls, run by pgwarden redteam run (deterministic, no LLM; runs in CI on every push). Each attack has an oracle that decides from database what happened (rows returned, table checksums, locks, HTTP status, proposal state, timing), not from matching an error message, whether the objective was achieved, and the runner also checks that the layer that stopped it is the one the case expected. The run needs the admin DSN only for the oracles, which read the tables directly:

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

--allow-load also runs the request-flood case, the only one where a rate_limited outcome counts as blocked. uv run pytest tests/integration/test_redteam_positive_control.py is the positive control: over a deliberately unsafe superuser connection the same oracles flag at least 90% of the cases in categories C, D, E and G as achieved (47 of 49, 96%, in the last run; it needs the test Postgres from devtools/testpg.sh up).

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: they are run and recorded (the call must succeed and return data, as documented) and never counted as blocked. Row estimates through plain EXPLAIN (D09) come from table-wide statistics that row security does not filter, and relation names in pg_class (G09) are readable by every role. Reading pg_stats is not one of them: Postgres withholds those rows for tables with row security active and for columns the role cannot read, so D05 and E05 are blocked.

Statement-filter baselines

pgwarden bench baselines runs the SQL-bearing part of the corpus (the query attacks of categories A to G and the benign controls that send SQL; the denominators are below) through two filters that live only in bench/, never in the product — the evidence for ADR-0001. The full blocklist and the pinned sqlglot version (30.20.0) are published in docs/baselines.md; both filters were written for this comparison.

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 and load

Measured on 2026-09-28 on a MacBook Air M5, 24 GB, Docker via colima with 4 CPUs / 6 GB. The laptop was shared, not idle: the bench client, the gateway and Postgres all ran in the same 4-CPU colima VM, two other containers (the test suite's Postgres and PgBouncer) were running, and so were other programs. Absolute numbers therefore move from run to run (an earlier 60 s load run that evening gave 621 requests/s against the 565 recorded here; only the recorded run is committed); read them as an orientation for one gateway process on a laptop, not as a capacity claim. Nothing here was run on a server.

The client runs in its own bench container of the compose stack: the same image as the gateway, on the compose network (so every request crosses a network hop), with no Docker socket and no admin credentials. It mounts the bench machines' secrets and, for the direct-Postgres baselines, the gateway's role secret (the login password of the bench role is derived from it), all read-only.

# 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

Those commands print the numbers. The committed files in docs/results/ come from a host wrapper that runs the same commands and adds what the container cannot see: the commit, colima's CPUs and memory, the number of other running containers, the Postgres version and the config hash, the gateway's CPU, RSS and Postgres connections sampled from the host during the load (docker stats, VmRSS and pg_stat_activity, several samples a second), and the result of pgwarden audit verify after each run:

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 below
devtools/bench/run.sh restore                                              # default demo config again

devtools/bench/run.sh latency|cold|load runs one part (cold adds to the latency file). The three query shapes are committed in src/pgwarden/bench/queries.yaml. In the latency table, Direct is plain asyncpg from the bench container as the machine role pw_m_bench_01 on a warm connection, Direct + wrapper is the read path's own BEGIN READ ONLY / set_config / prepare / fetch / ROLLBACK sequence on a pooled connection, and Via pgwarden is an MCP tools/call query over HTTP with a warm token. Overhead is the last minus the first. Every timed call must return the row count the database returns for the same statement, or the run aborts.

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.

Server-Timing span medians inside the gateway (ms):

Cold first query after the pooled connection was evicted (connect + SCRAM; primary-key lookup, gateway with pool.idle_timeout_s: 1, 2.5 s pause): median 43.1 ms (min 19.2, max 62.3) against 3.74 ms warm, a cold cost of 39.4 ms, of which 29.8 ms in the db span. 30 of 30 samples were confirmed cold by a new backend appearing in pg_stat_activity.

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.

Reading the latency table: on a primary-key lookup the gateway adds about 2 ms at p50 and under 3 ms at p95. The read-path wrapper accounts for about 0.4 ms of that (0.46 against 0.06 ms), and the Server-Timing spans add up to about 1.2 ms of the 2 ms (auth, rate limit and audit are each a few tenths of a millisecond); the rest is HTTP and MCP framing and the client itself. On the monthly aggregate the query dominates (13.5 ms in db) and the gateway adds about 3 ms. The cold first query is dominated by opening the database connection (TCP, startup, SCRAM): about 30 ms of the extra 39 ms sit in the db span, and the other few milliseconds are auth and ratelimit also being slower after a 2.5 s pause, so the db figure is the better estimate of connect + SCRAM. It is paid once per person or machine after a pooled connection was idle for pool.idle_timeout_s (60 s by default). The cold run uses machine bench-01; a person's pool goes through the same connection code.

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.

Reading the load table: the design targets were met on this run. The gateway is a single process; it averaged 81% of one core and peaked at 102% while the load client shared the VM, which suggests one gateway process was close to saturated. Postgres held one connection per active identity (peak 20). The design targets are goals, not claims: the numbers above are whatever the run measured.

LLM indirect-injection run

pgwarden redteam llm drives two inexpensive tool-calling models through the demo stack, whose data carries planted prompt injections. Rows returned beyond the identity's privileges and writes executed without approval must both be zero; exfiltration through the model's final answer is a residual risk the gateway cannot block, reported honestly. This run costs money and is never in default CI. The stack runs demo/pgwarden.llm.yaml for it, the demo config with the query, proposal and registration limits raised to 600 so the run is not throttled.

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

Each of the 10 tasks runs 3 times per model, at most 12 turns per episode, at temperature 0, with the provider pinned and fallbacks off; the provider that actually served each call is recorded, and costs come from OpenRouter's reported cost per call. Read the table carefully: only the DeepSeek model was ever shown a planted injection (6 exposures in 3 episodes) and it acted on all 6, and the gateway blocked all 6; the Qwen model's tasks never surfaced a planted marker, so its zero says nothing about how it would have behaved. The gateway container in that run was built from an earlier commit that differs from the recorded one only in comments and the describe_table tool description.

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

The models used and their prices are verified at run time; the recorded run's full JSON is in docs/results/.

Setup, versions and infrastructure checks

• Setup. Measured on 2026-09-28 with time: on a fresh clone (a distinct compose project, non-default ports, new secrets generated) docker compose up -d --wait took 12 s, and pgwarden redteam run on that stack passed (131 / 131 attacks blocked, 32 / 32 benign controls; the corpus has grown since, see the table above). From git clone to the first whoami through the OAuth flow took about 16 s: 1 s clone, 12 s compose, 2 s for the uv environment, 1 s for login and the call. That was with the Postgres, Python and Mailpit images, the build layers and the uv cache already on the machine. Building the gateway and mock-IdP images without the layer cache took 11 s and 5 s more (docker build --no-cache); pulling the three public images depends on your network and was not measured.

• Image. The production image runs as a non-root user (uid 10001) and contains no mock IdP, screenshot script or test suite (it does carry the redteam and bench subcommands). docker image inspect reports 73.8 MB of image content; docker image ls shows 344 MB unpacked on disk.

• Versions. The full test suite and every recorded run above used Postgres 16.15 and Python 3.12. CI runs the database tests on Postgres 16 and 18 (18 is the newest stable major on 2026-09-28; 19 is still in beta).

• Terraform. deploy/terraform/check.sh runs terraform fmt -check, terraform validate (module and example), terraform test (4 runs with mock providers), tflint and trivy config through pinned Docker images and never authenticates to a cloud: everything passes with 0 findings, after one accepted exception (AVD-GCP-0017, a public Cloud SQL address with no authorized networks, reasoned in deploy/terraform/.trivyignore). The module is validated and scanned, not applied in v0.1.

Verified clients and identity providers

Use it on your own database

pgwarden works on the bundled demo data and on your own Postgres 16+ through configuration. See docs/own-database.md for creating bundle roles and RLS (keyed on session_user), writing pgwarden.yaml, the minimal admin privileges, and running db init, roles sync, masking apply, doctor and serve. docs/identity-providers.md covers generic OIDC, Google, Entra and GitHub. docs/configuration.md is the full configuration reference, generated from the code.

Deploy to Google Cloud Run + Cloud SQL with the Terraform module in deploy/terraform/gcp-cloud-run/ (ADR-0008).

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.

• One target database per deployment, and single-tenant Entra only.

• Rate limits apply to query and to write proposals; whoami, list_tables and describe_table are not rate limited.

• 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 (the offboarding item above).

• 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 a person has to propose it 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. Candidate next steps: 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/example.net emails, reserved fictional phone ranges, and canary tokens that look like CANARY-PM-0001 (no real card data). See demo/sql/.

Development

uv sync, then uv run pytest (unit tests need nothing; integration tests need a Postgres 16 via devtools/testpg.sh up; stack tests need docker compose up, and the MCP Inspector OAuth check also needs npx and uv run playwright install chromium). To regenerate the README screenshots against a running stack: uv run python devtools/screenshots/run.py. uv run ruff check, uv run ruff format --check and uv run mypy --strict src/ must pass. See CONTRIBUTING.md.

License

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