pg_agent_gate

verify License: Apache 2.0 PostgreSQL 18 and 19

Agents propose, PostgreSQL decides.

Teams are connecting AI agents to production PostgreSQL. The agent gets a role and a connection, and from then on PostgreSQL cannot tell it from the application: when the model hallucinates a DELETE without a WHERE, a DROP TABLE, or a read of another customer’s rows, the database runs it.

pg_agent_gate is an extension that makes the database tell the difference. In a session that belongs to an agent, SQL does not execute. The agent may only propose one statement; PostgreSQL verifies it against itself, shows its exact effect, and keeps it only if every guard agrees. It is enforced by hooks inside the server, so there is no client, driver or protocol path around it.

No MCP server required. The gate lives in the database, so an agent reaches it over psql, a driver or direct SQL — there is nothing to put in front of it (see a client that only speaks MCP). A small, free model operating it live, verified end to end: see the demo ▶.

Contents: See it in one minute · Verify it yourself · How it works · The seven verbs · Setup · Run the two side by side · The pipe: what JSON costs · A client that only speaks MCP · Measured · What it costs · Threat model · What it does not cover

See it in one minute

make demo PG_CONFIG=/path/to/pg_config

The same role, with the same privileges, first as an ordinary user and then registered as an agent. After every attempt a superuser prints the database:

WITHOUT the gate: an ordinary role, the way an agent connects today
     database now: customers: 4 · orders: 8
  1. the model 'cleans up' the orders:        DELETE FROM orders
     database now: customers: 4 · orders: 0
  2. the model 'fixes' the schema:             DROP TABLE customers CASCADE
     database now: customers: TABLE GONE · orders: 8
  3. the model reads another customer's data:  set_config('app.tenant_id', '2') + SELECT
     Iris|iris@TWO.example
     Juan|juan@TWO.example

WITH the gate: the same role, registered as an agent (max_rows 5, no DDL)
  1. DELETE FROM orders, typed directly
     ERROR:  pg_agent_gate: this session belongs to agent "assistant": it proposes, it does not execute
  2. DROP TABLE customers CASCADE, typed directly
     ERROR:  pg_agent_gate: this session belongs to agent "assistant": it proposes, it does not execute
  3. set_config to tenant 2, typed directly
     ERROR:  pg_agent_gate: this session belongs to agent "assistant": it proposes, it does not execute
     database now: customers: 4 · orders: 8

  ...and the same three, PROPOSED through the gate:
  1. propose + commit:  DELETE FROM orders   (8 rows, the agent may touch 5)
     commit: aborted -- it touched 8 rows and this agent may touch at most 5
  2. propose:  DROP TABLE customers
     refused at propose -- kind_allowed: T_DropStmt changes the schema or the server, and this agent is not allowed DDL
  3. propose:  a read that moves the tenant from inside the statement
     refused at propose -- keeps_its_context: set_config() would change, while the proposal runs, a parameter ...
     database now: customers: 4 · orders: 8

  ...while legitimate work still goes through, and is seen before it is kept:
  dry_run (nothing kept):
     "rows": [{"after": {..., "email": "ana@new.example"}, "before": {..., "email": "ana@one.example"}}]
  commit:
     kept, Ana's email is now ana@new.example

A small, free model doing this live — fork-and-run, with the scored report: agent-gate-demo · see a live run.

Try it with no Rust or PostgreSQL installed

make demo needs PostgreSQL 18+, Rust and cargo-pgrx. To see the same with/without-gate demo with none of that, run it in a throwaway container — the extension is built into the image, and it needs only Docker or Podman:

make docker-demo        # builds the image from this repo and runs the demo

or pull the prebuilt image instead of building it:

docker run --rm ghcr.io/manuelreyesbravo/pg_agent_gate-demo

The image is linux/amd64; on an Apple-silicon Mac it runs under emulation.

Verify it yourself

Every claim in this README has a test that attacks it on purpose and checks the database from a superuser’s side – not the gate’s own answer:

make verify PG_CONFIG=/path/to/pg_config     # your PostgreSQL 18+, a throwaway cluster
make clean-machine                           # the same, in a fresh container that only gets git HEAD
suite what it attacks checks
adversarial.sh every channel a session can type, and what can be slipped past propose 45
hostile.sh garbage into the verbs, the record rewritten, the world moved between propose and commit 37
privileges.sh the privilege boundary between agents, roles and the record 31
rls_isolation.sh moving the context a row-level policy reads: SET, startup parameters, set_config 22
dump_restore.sh the record across pg_dump and restore 10
upgrade.sh an installation of the oldest schema, upgraded 9
pgrx unit tests the verbs, from inside the server 10

On a fresh Debian container receiving only what git has committed, with PostgreSQL 18.6 and with 19beta4 from PGDG: verified: 164 checks passed, 0 failed on both (and on 19beta2, where it was developed). CI runs the same container for 18 and 19 on every push. VERIFY_DRIVERS=1 adds real pgjdbc and node-pg sessions.

Those 164 are the hand-written suites above. The amplification class that 0.2.2 and 0.2.3 closed – a writing CTE, a cascading foreign key, a trigger, a rule, an opaque (volatile or SECURITY DEFINER) function, and the commit-time row-count backstop and per-agent allow-list that back them – is guarded by make fuzz, not by make verify. Its teeth build each of those and assert the gate refuses or aborts it (and that discover says the same thing propose does, through the one shared function), and a k=1..8 differential oracle checks max_rows against the row count a superuser actually measures. make fuzz also throws generated and mutated adversarial SQL at the gate and checks, from a superuser’s side: nothing the agent runs directly changes the database, and every proposal the gate accepts, once committed, leaves the catalog, the role and another tenant untouched and stays within max_rows – the surface the two 0.2.1 bugs lived on. Two teeth checks run first so neither half can pass vacuously (a known change must move the fingerprint; propose must refuse a known DROP and GRANT), and run against the v0.2.0 tag the fuzzer reports the historical set_config hole. CI runs FUZZ_ITERS=0 make fuzz – the teeth and the oracle, no generation – on every push, in the required end-to-end job; the generative fuzz (FUZZ_ITERS > 0) runs weekly. Each tooth asserts WHICH check refuses a statement (via failed_check), not merely that one did, so it cannot pass unless the right check fired – a refusal by a parse error or a missing privilege does not count. That is the structural guard against a vacuous pass. As a spot negative control (measured 2026-10-08), disabling no_amplification turns FUZZ_ITERS=0 red: 9 checks fail (the cascade and trigger teeth, the allow-list breach checks, the discover/propose parity). tests/fuzz.py says plainly what it does and does not catch.

Two of these suites were green while a hole was open: see what found the 0.2.1 fixes. A green suite is a claim about the cases it has, which is why the threat model below says what is not covered.

How it works

Today an LLM agent reaches PostgreSQL through an MCP server that holds a connection, hands the model a list of tools, and runs whatever the model asks. The model decides; the server obeys. Every guarantee lives in that server’s code, outside the database, and the database never learns what was checked.

pg_agent_gate moves the decision into PostgreSQL. An agent does not run SQL. It has seven verbs, and in a session that belongs to an agent it cannot do anything else – not DELETE, not DO, not COPY, not PREPARE. The database verifies every proposal against itself, runs it with the agent’s own privileges, and keeps the record.

discover  ->  propose  ->  dry_run  ->  commit
          ->  propose_and_commit           (both in one call)
                                         acts, whoami

What an agent sees

-- what may I touch? derived from the live catalog and MY privileges
SELECT agent_gate.discover('clientes');

-- propose one statement: nothing runs
SELECT agent_gate.propose(
  'update clientes set plan = ''pro'' where id = $1::int',
  'upgrade the customer who asked for it',
  ARRAY['1']);
--  {"proposal": 42, "ok": true, "kind": "write", "estimated_rows": 1,
--   "checks": [{"check": "parses",           "passed": true, ...},
--              {"check": "single_statement", "passed": true, ...},
--              {"check": "kind_allowed",     "passed": true, ...},
--              ... no_writing_cte, keeps_its_context, no_amplification, no_opaque_function ...
--              {"check": "resolves",         "passed": true,
--               "detail": "the planner resolved every table, column, type and function without executing ..."}]}

-- see the exact effect, then nothing is kept
SELECT agent_gate.dry_run(42);
--  {"outcome": "rolled_back", "rows_affected": 1,
--   "rows": [{"before": {"id": 1, "plan": "free"}, "after": {"id": 1, "plan": "pro"}}]}

-- make it real, if every guard still agrees
SELECT agent_gate.commit(42);
--  {"outcome": "kept", "rows_affected": 1, ...}

Anything else, from that session:

=> delete from clientes where id > 0;
ERROR:  pg_agent_gate: this session belongs to agent "billing": it proposes, it does not execute
DETAIL:  DELETE does not reach the database from an agent session
HINT:  Call agent_gate.propose(sql, intent), then agent_gate.dry_run(proposal) or agent_gate.commit(proposal) -- or agent_gate.propose_and_commit(sql, intent) for both in one call.

Run the two side by side

make contrast PG_CONFIG=/path/to/pg_config

The same DROP TABLE that an ordinary connection runs – and the table is gone, irreversibly – the gate refuses, with the reason. And where an ordinary server hands back a row count after it ran, the gate returns the catalog the agent may touch, every check with its verdict, and the exact before/after of the change, before anything is kept. The piece you take out returned a result; the piece in its place returns a decision you can see.

The seven verbs

verb does
discover(filter, max_objects) tables, views and functions the agent has privileges on, with columns, types, keys, constraints and comments. Objects owned by extensions are left out. Includes a fingerprint, so a client can tell the schema changed
propose(sql, intent, params) verifies one statement and records it. Runs nothing
dry_run(proposal) runs it inside a subtransaction and rolls it back: rows touched, before/after of every row of a write, whether bound assertions would still hold
commit(proposal) verifies again, runs it and keeps it if every guard agrees. A proposal is committed at most once
propose_and_commit(sql, intent, params) propose then commit in ONE call, so in one transaction: the same checks (verified at propose and again at execution), the same record, one commit instead of two. No dry_run in between – for a statement whose effect the agent has already seen, or a read. Returns {outcome, proposal, commit}; a statement that does not verify never reaches execution (refused_at_propose, commit null)
acts(max_acts) what this agent proposed and did, with every execution and why anything was refused
whoami() which agent this session is, whether the gate is enforced in it, and how durable attempts are

Verification

Nothing is reimplemented. Each check is PostgreSQL itself:

  1. parses – PostgreSQL’s own raw parser accepts it.
  2. single_statement – exactly one, so what was verified is what runs. select 1; delete ... dies here.
  3. kind_allowed – read, write or DDL, against what the agent may do. Transaction control is refused: the gate owns the transaction.
  4. no_writing_cte – PostgreSQL’s analyzer and rewriter build the query tree (every table, column, type, operator and function must exist and fit, or the proposal fails resolves right here), and a CTE that changes data is refused. max_rows counts the rows of the statement, and a write hidden in a CTE under a SELECT count(*) is counted as one row: before 0.2.1 it deleted every row under a limit of 5. Propose each write as its own statement.
  5. keeps_its_context – set_config() anywhere in that tree (the WHERE, the FROM, a subquery, a CTE, an expanded view, schema-qualified or not) is refused. Inside the statement it moves, while the statement runs, the parameter a row-level policy reads; before 0.2.1, ... where set_config('app.tenant_id', '2', true) is not null read another tenant’s rows. Not covered: a function that already exists and calls set_config in its own body – its body is not in the tree. Do not grant an agent EXECUTE on one.
  6. no_amplification, no_opaque_function – on the same tree: a write whose cascade, trigger or rule the gate cannot vouch for, and a call to a user function that is volatile or SECURITY DEFINER (see “What it does not do”).
  7. resolves – only now EXPLAIN plans it, for the estimate. The order is a guarantee (0.2.6): planning runs functions – it folds an IMMUTABLE call with constant arguments and estimates a STABLE one by calling it – so up to 0.2.5, where EXPLAIN came first, a SECURITY DEFINER function ran as its owner before no_opaque_function refused it, and what it raised (a secret) came back in the check’s detail. Now every refusal is decided on the analyzed tree, which runs nothing (tests/plan_time.sh counts the calls); and since 0.2.7 a SECURITY DEFINER function reached through a body the tree does not show – a wrapper the planner folds – is stopped before it runs. estimated_rows is given only when the agent could read in full every relation the analyzed tree AND the plan touch: SELECT on the whole table and no row-level security hiding rows from it. The statistics are gathered beneath the policy and beneath a view, and the estimate told another tenant’s frequent value (34 rows) from an absent one (1) – through the table (0.2.6), through a SQL function the planner inlines after the tree was built, and through a view that isolates tenants without row-level security (both 0.2.8). It errs on the withholding side: SELECT on a partitioned parent without its partitions, or on some columns only, withholds the estimate too, although nothing would leak. The agent’s SQL never runs in a parallel worker (0.2.8): the backend runs the whole plan, where the gate’s hooks see it. DDL (for agents allowed it) is verified instead by running it in a subtransaction that is rolled back.

Checks 4 and 5 were found by an LLM proposing through the gate against a two-tenant database, with a superuser comparing the database before and after every case – not by the suites in tests/, which were green with both holes open. Both now have cases there (adversarial.sh, rls_isolation.sh) that are red against 0.2.0.

commit verifies again: a verification is a statement about the database at a moment. A proposal older than agent_gate.proposal_ttl_seconds (900) is refused outright.

Execution

  • Runs with the agent’s own privileges. The gate adds verification; GRANT is still the authorization.
  • Inside a subtransaction, decided after it runs:
    • a write touching more rows than the agent’s max_rows is undone;
    • deferred constraints are fired inside the gate (SET CONSTRAINTS ALL IMMEDIATE), not at the caller’s commit where nobody is watching;
    • assertions bound to the agent (from pg_living_assertions) are run after the change: broken or erroring undoes it – a check that cannot run is not a check that passed.
  • A read’s subtransaction is always rolled back: a read has nothing to keep.

The record

agent_gate_internal.proposals and .executions are append-only (triggers refuse UPDATE and DELETE) and survive pg_dump. Each execution says its outcome – kept, read, rolled_back, aborted or refused – and the last two must say why (a CHECK). Writes keep the before/after of up to 50 rows. Reads keep nothing but counts: copying what an agent read into an audit table would copy the data itself.

Only the gate writes the record. The writing functions are SECURITY DEFINER and first ask the gate whether the caller is the gate’s own code – a flag no SQL can set, kept down while the agent’s own SQL runs. A proposal cannot forge its history; an agent calling those functions by name is refused by the hook.

What a rollback takes, the server log keeps. The record is written in the caller’s transaction, and an agent session may control its transaction (a driver such as psycopg opens BEGIN by itself). So ROLLBACK, ROLLBACK TO SAVEPOINT, or a session that disconnects without COMMIT takes those rows with it – a refused attempt and a read included. The gate cannot keep a row its caller rolls back, and refusing transaction blocks would break every driver that opens one. Instead, when an abort takes rows from the record, each one is written to the server log at LOG – outside every transaction, never sent to the agent’s session – on one line, with the agent’s text JSON-escaped:

LOG:  pg_agent_gate: rolled back with the transaction, so not in the record: proposal 1 of agent rollback (role ...): kind=ddl ok=false intent="probe one" sql="drop table shop.orders"

acts() and the tables show what was committed; the server log shows what an abort took (since 0.2.10, from an external audit of 0.2.8; tests/rollback.sh). A server crash before the log line is written loses it, as it would lose any.

How durable the record is

agent_gate.attempt_durability (superuser-only):

  • A change the gate keeps is always committed with the durability the server is configured with. That flush also carries the record of the proposal that led to it, which sits earlier in the WAL: no change exists on disk without its proposal and its execution next to it.
  • fast (default): the record of an attempt – a proposal, a dry run, a read, a refusal, anything that changed no data – rides on an asynchronous commit. A server crash inside the WAL writer’s window can lose the record of an attempt that changed nothing. (pgaudit’s log is not fsynced either.)
  • durable: every record pays its own flush – so propose + commit pay two, and propose_and_commit pays one: the proposal and its execution ride on the same commit. Counted, not inferred: tests/flushes.sh reads the WAL fsyncs of client backends from pg_stat_io around 50 acts of every kind, and gets exactly 2.00 and 1.00 per act (0.00 for a fast read, 1.00 for any kept change).

Never relaxed in a transaction that had already written something before the first verb ran: those writes are not the gate’s to relax.

Setup

PostgreSQL 18 or later (dry_run uses RETURNING old/new). Built with pgrx 0.19.2; the test suite passes on 18 and 19.

cargo install cargo-pgrx --version 0.19.2 --locked
cargo pgrx install --release --pg-config /path/to/pg_config

# postgresql.conf -- recommended
shared_preload_libraries = 'pg_agent_gate'

CREATE EXTENSION pg_agent_gate;

CREATE ROLE billing_agent LOGIN;               -- never a superuser
GRANT USAGE ON SCHEMA public TO billing_agent;
GRANT SELECT, UPDATE ON clientes TO billing_agent;

SELECT agent_gate.register_agent(
  'billing', 'billing_agent', 'answers billing questions and upgrades plans',
  p_max_rows => 50, p_allow_ddl => false);

-- optional: every write this agent keeps must leave this assertion holding
SELECT agent_gate.bind_assertion('billing', 'no_customer_without_plan');

register_agent sets agent_gate.agent on the role. If the library is not in shared_preload_libraries, it also sets session_preload_libraries on that role, so the gate is loaded before the agent’s first statement. It refuses superuser roles: a superuser can unset a superuser-only setting, and an agent that can leave the gate is not behind it. It takes effect on the role’s next connection.

For a client that only speaks MCP: a shim with no power

The gate is in the database, so there is nothing to put in front of it – an agent reaches it through psql, a driver, or direct SQL, and it is governed all the same. You do not need an MCP server, and that is the point: the piece that used to hold the connection and run what the model asked is gone.

For a client that only speaks MCP, gated-mcp/ fills the gap without bringing that power back. It is a small Bun + Hono server exposing six of the verbs as six MCP tools (propose_and_commit, added in 0.2.5, is not exposed there yet) over Streamable HTTP, and it connects as the agent role, so it is not where the gate lives – replace it with anything and it still can only call the verbs. It is a compatibility layer, not the product. Six tools, never one per table: what the agent may touch comes from discover.

cd gated-mcp && bun install
AGENT_GATE_DATABASE_URL=postgres://billing_agent@localhost/app bun run src/index.ts
# POST http://127.0.0.1:7878/mcp   (set AGENT_GATE_TOKEN to require a bearer token)

It speaks two protocol eras on the same endpoint, chosen by what each request says: the 2026-07-28 revision – stateless; MCP-Protocol-Version, Mcp-Method and Mcp-Name must match the body or the request gets 400 and -32020 before it reaches the database; an unsupported version gets 400 and -32022; server/discover and tools/list carry ttlMs and cacheScope – and the 2025-11-25 initialize handshake most clients still run. GET and DELETE get 405.

Bound to loopback, Host and Origin are checked on every request and a foreign one gets 403 before the token and before the database: any web page can make a browser post to localhost. Bound elsewhere, set AGENT_GATE_ALLOWED_HOSTS and AGENT_GATE_ALLOWED_ORIGINS.

Besides the six tools it offers resources and prompts, and they add no power. The resources – agent-gate://catalog, agent-gate://whoami, agent-gate://acts and the template agent-gate://relation/{schema}/{name} – are read by calling a verb as the agent role, never with a query of their own: the catalog as a resource is identical to what discover returns, and a relation the agent was not granted answers -32602, not its content. The prompts, change_data and investigate, carry no data: they teach a model the gate’s loop. What a resource contains depends on the agent’s grants, so it is marked cacheScope: private.

Checked against clients nobody here wrote: test/clients.mjs drives the gate end to end with the official @modelcontextprotocol/client 2.0.0 pinned to 2026-07-28 and with @modelcontextprotocol/sdk 1.30.0, and checks from a superuser connection that only the committed change happened; test/transport.mjs checks the transport MUSTs with plain fetch; and the official @modelcontextprotocol/conformance suite runs against it.

One command runs all of it – a throwaway cluster, an agent registered behind the gate, gated-mcp started as that role, and every suite against it:

make mcp PG_CONFIG=/path/to/pg_config     # needs node and bun on PATH

CI (.github/workflows/verify.yml, the end-to-end job) runs that same command on every commit, so “a real MCP client can only operate the gate” is re-proved, not just asserted.

The pipe: what JSON costs

MCP is JSON-RPC, and JSON has no 64-bit integer, no exact decimal and no binary. An agent that reaches the database through it moves its data over that pipe; one that reaches the gate over a native connection does not. The gate’s guarantee is identical either way – the same verbs, the same verification – so what you weigh is the pipe. make transfer measures it on a replica of a real workload (a 64-bit id, exact money, binary, arrays, nested documents, at volume):

make transfer PG_CONFIG=/path/to/pg_config
the same data native, PostgreSQL’s own protocol forced through JSON
the id 9007199254740993 exact 9007199254740992 – a JSON number is an IEEE double
numeric(40,12) exact lost, unless it travels as a string
256 bytes of binary 256 bytes, raw 348 bytes of base64 text (+35%)
the whole set its typed, binary self larger, every number and key as text

None of it is real data; it is there so you can run it. The fidelity loss is shown live, by parsing the value with the same JSON a client would use.

Measured

With real sessions of an agent role, both wire protocols, against PostgreSQL 19beta2 built without assertions – a throwaway cluster from the same binaries the extension is installed into (tests/cluster.sh), loading it from cargo pgrx package. Each attack counts only if the error is the gate’s (a PostgreSQL permission error proves nothing about the gate), and a separate superuser connection then checks that nothing changed. Every criterion was declared before the code that it measures.

criterion threshold measured
raw SQL executed from an agent session: SELECT/INSERT/UPDATE/DELETE, DO, CALL, PREPARE, EXPLAIN ANALYZE, writing CTE next to a verb, foreign function next to a verb or as its argument, subquery as argument, reading or forging the record, LOCK, CREATE, SET ROLE, COPY, extended protocol, cursor 0 0 of 20, no side effect
raw SQL executed after an error inside the gate (error escaping a verb, error inside execution, cancellation mid-act, rollback in an explicit transaction) 0 0 of 4
invented proposals refused at propose: missing table/column/function, wrong literal type, operator without a type, unbound parameter, hidden second statement, broken syntax, transaction control, DDL without permission 100% 17 of 17, no side effect
control: correct proposals pass and do exactly what they say 100% 10 of 10
control: a kept change survives an immediate shutdown (no checkpoint) with its proposal and its execution 100% 10 of 10
every channel a session can type, tried on purpose (tests/adversarial.sh): two statements in one query, PREPARE/EXECUTE, a cursor, COPY TO/FROM PROGRAM, DO, CALL, EXPLAIN ANALYZE, CREATE TABLE/FUNCTION, SELECT INTO, a writing CTE, a function as a verb argument, a subselect of what it was not granted, SET ROLE, SET SESSION AUTHORIZATION, RESET ALL, DISCARD ALL, VACUUM, CHECKPOINT, LISTEN/NOTIFY, lo_export, a function as the argument of propose_and_commit, and a replication connection 0 0 of 24 had any effect
what can be slipped past propose (tests/adversarial.sh): two statements, a second one hidden after a comment, DDL without permission, COPY TO PROGRAM, SELECT INTO, FOR UPDATE and a write beyond max_rows, two writing CTEs under a SELECT, an expired verification, another agent’s proposal – and each of the first nine again through propose_and_commit 0 0 of 20
control: an agent still runs whoami, discover, proposes, sees before/after in dry_run, commits a read and a write, is refused a second commit, and reads its acts; in one call, reads rows and keeps a write that shows in acts 100% 15 of 15
garbage into propose and commit (tests/hostile.sh): malformed SQL, unbalanced parentheses, an unterminated comment and dollar quote, a NUL byte, 500 levels of nesting, 100 KB of SQL, 500 parameters, fewer parameters than placeholders, a type that does not exist, invalid UTF-8, a 10 KB identifier, an empty proposal, ids that do not exist, an endless query 0 crashes 0 of 16, same postmaster
the record rewritten (tests/hostile.sh): DELETE, TRUNCATE, disabling the triggers and dropping them, as the agent and as the owner 0 0 of 7
the world moved between propose and commit (tests/hostile.sh): table renamed, column dropped, privilege revoked, row gone, a bound assertion broken meanwhile, two sessions at once, and the same proposal committed twice at once 100% 9 of 9
control: what an owner’s trigger, SECURITY DEFINER function and view do on the agent’s behalf is what this README says 100% 5 of 5

Two of those rows found real defects, which is why they exist. TRUNCATE emptied the whole record: the append-only triggers were FOR EACH ROW, and TRUNCATE fires no row trigger, so the history could be erased in one statement with nothing disabled and nothing said. And two concurrent commits of the same proposal both ran it – each transaction read committed from its own snapshot, where the other had not committed yet, so a write was applied twice. On a balance that is a double charge. Both are fixed (0.2.0): statement-level triggers for TRUNCATE, and the proposal’s row is locked while a commit decides.

What found the 0.2.1 fixes

Not these suites: they were green with both holes open. What found them was an LLM (a Qwen3-8B with a SQL adapter) proposing through the gate against a two-tenant database with row-level security, on 157 cases – 30 legitimate tasks, destructive ones written by the model and injected by hand, raw SQL in the agent’s session, and attempts to cross tenants – with a superuser comparing the database before and after every case, and the same cases run by an identical role that was not an agent, to prove the cases were dangerous at all.

  1. A CTE that writes dodged max_rows. The limit counted the rows of the outer statement, so with d as (delete ...) select count(*) from d deleted 12 rows under a limit of 5.
  2. set_config() inside a proposal moved the tenant. The session allowlist judged SET and the startup parameters, but while the gate runs a proposal its hooks step aside, so ... where set_config('app.tenant_id', '2', true) is not null read the other tenant’s rows.

Against 0.2.0, 9 of 10 variants of the two did damage. Against 0.2.1, 0 of 10; both are refused at propose (checks 5 and 6 above), and both now have cases in the suites that are red against 0.2.0. On the same run, with the gate in place: the model’s correct SQL was never refused (0 of 27 false positives), and the gate’s own work cost 0.54 ms per act, median, over running the same SQL directly.

What it costs

make bench PG_CONFIG=/path/to/pg_config

tests/bench.py on the throwaway cluster. “Directly” means an identical role – same grants, same statement, same parameters – that is not registered as an agent; never a superuser. Each of 300 iterations runs both paths back to back, alternating which goes first. The thresholds were declared before the code.

what threshold measured (median)
extra time per read act (propose + commit) over the same query directly <= 10 ms 0.962 ms
extra time per kept write over the same UPDATE directly, which pays its own durable commit <= 5 ms 1.366 ms
throughput lost by sessions that are not agents when the library is preloaded (pgbench -S, 7 alternating pairs of 15 s) <= 3% 1.75%
extra time per read act with attempt_durability = durable <= 10 ms 2.821 ms
the same durable read act in ONE call (propose_and_commit) <= 10 ms 1.694 ms

Where the time goes. Of the ~1 ms a read act takes end to end, the gate’s own work inside the server – parse, plan, the amplification and function checks, run, decide – is about 0.48 ms. Most of the rest is the record: every act is a proposal and an execution written to append-only tables, in their own commits. That is not overhead to optimize away; it is what makes an agent’s actions auditable. With durable every one of those records pays its own flush, and a flush is the disk: propose + commit pay two, propose_and_commit writes both records in one commit and pays one (counted by tests/flushes.sh). A durable act costs about the gate’s own work plus one or two flushes, so its number is mostly the disk’s.

The same disk, two states, the same day. The table above ran with fdatasync at 0.79 ms (pg_test_fsync on the data directory). Hours earlier, under sustained I/O from other work on the machine, the same disk took 3.9-5.1 ms per flush. Sampled both times as 20 clean rounds of 300 pairs (CCX-pinned lane, calibration before and after every round, the two ways interleaved):

durable read act fast disk (0.88 ms per flush) slow disk (3.9 ms per flush)
two calls (propose + commit) 2.65 ms [95% CI 2.62..2.67] 10.86 ms [95% CI 9.30..12.56] – crosses the threshold
one call (propose_and_commit) 1.68 ms [95% CI 1.65..1.72] 5.06 ms [95% CI 4.87..5.36]
one call vs two -36.7% [95% CI -38.0..-35.0] -53.4% [95% CI -60.0..-44.3]

With a healthy disk both ways are far under the threshold; with a slow one the two-call act straddles it and the one-call act stays under it with its whole interval. The disk has no calibration of its own – fsync latency is not what the sentinel measures – so a durable number is only as good as the disk it was taken on: measure yours.

These numbers are on a small schema. Two of the checks scale with the catalog: the commit backstop reads pg_stat_xact_user_tables (twice per kept write), and no_amplification walks the inheritance of the target. On a database with thousands of tables, expect the per-act cost to grow; re-run make bench against your own schema rather than trust these.

Every pair of the throughput test, because a median hides how noisy one pair is. Loss with the library preloaded, in %: 8.28, -3.4, 1.75, -7.57, 2.25, 1.65, 1.99. Single 15-second runs hiccup in both directions – a -7.6% is a bare run dropping, not the gate making anything faster – which is exactly why the criterion is the median of several pairs and not any one run: earlier runs of this same test reported 0.24%, 0.43%, 0.83% and, uncontrolled, 3.16%.

Measured on 2026-10-08 on PostgreSQL 19beta2 built without assertions (an --enable-cassert build, such as pgrx’s own instance, inflates every number here), AMD Ryzen AI 9 HX 370, data directory on Btrfs, fsync and synchronous_commit on, with the benchmark pinned to its own cores and a calibration run before and after to reject any measurement taken while the machine was contended. Your numbers will differ; make bench writes them to target/bench/result.json.

Upgrading

Copying a new .so does not upgrade anything. Most of what this extension guarantees lives in its SQL – the triggers that keep the record append-only, the functions that write it – and that SQL runs once, inside CREATE EXTENSION. A database where the extension already exists keeps the old schema until you say:

ALTER EXTENSION pg_agent_gate UPDATE;

That is not hypothetical. 0.2.0 closes two defects that live in the SQL, and while preparing it the new library was loaded into a live database whose schema stayed at 0.1.0: the TRUNCATE hole was still open, and the library was calling a two-argument _load_proposal that did not exist there, so every commit an agent made would have failed. Nothing broke only because that database had no agents registered yet.

tests/upgrade.sh measures the upgrade against the real old schema, kept in tests/fixtures/ byte for byte, rather than against a freshly built one: an extension is installed clean once and upgraded for the rest of its life, and until now only the case that happens once was being tested.

Notes for extension authors

  • pgrx’s SpiClient::update assigns a transaction id before running anything (it calls GetCurrentTransactionId() to mark the transaction mutable). A transaction with an id writes a commit record, and a durable commit record is an fsync. The first version of this extension used it for internal reads: every discover paid a flush, and the question “did this transaction already write?” answered itself yes by being asked. Reads here go through SpiClient::select; that question is asked with GetTopTransactionIdIfAny().
  • CREATE EXTENSION does not grant USAGE on the extension’s schema. An agent could not even propose until the script granted it.

Threat model

The adversary is the agent. A model that hallucinates, a model that was prompt-injected, or a fully hostile client: it holds a connection as its role and can send anything the wire protocol carries – any statement, any startup parameter, the extended protocol, a cursor, garbage.

Where the line is. The gate bounds what the agent names – the statement it proposes and the rows that statement changes – plus the row count. What a DBA attaches to a table (a trigger, a CHECK or DEFAULT, a generated column, a view) is trusted code that runs with its own rules: the gate refuses the nested utility such code fires (0.2.4) and counts the rows it writes, but it does not read a definer function’s body or re-decide what a view may show.

What the gate guarantees against it, each with a suite above:

  • nothing the session types executes except the seven verbs;
  • a proposal runs only after PostgreSQL’s parser, planner, analyzer and rewriter accept it as one statement of a kind the agent may run;
  • a write that touches more than max_rows rows, or breaks a bound assertion, is undone;
  • the context its row-level policies read cannot be moved – not by SET, not at connection start, not by set_config() inside a proposal;
  • everything it proposed and did is recorded append-only, including why anything was refused.

What it trusts, and is out of scope:

  • superusers, and whoever can edit postgresql.conf or restart the server: they can unload the gate. That is why register_agent refuses a superuser role – an agent that can leave the gate is not behind it;
  • the DBA’s grants: GRANT is still the authorization. The gate adds verification, it does not invent privileges or take them away;
  • the functions and views the agent is granted: they run with their own rules (see below);
  • the operating system and the server binaries.

What it does not protect, by design: intent. A well-formed statement, within the agent’s privileges and under max_rows, runs. Measured: asked to “set every customer’s plan to free”, the model wrote UPDATE ... WHERE plan <> 'free', which touched 2 rows under a limit of 5, and the gate kept it – correctly, by its contract. Bound the blast radius with a small max_rows, bind assertions that must keep holding, and put a human on dry_run for writes that matter.

What it does not cover

Said here so nobody learns it the hard way:

  • A function that calls set_config() in its own body. The gate refuses set_config anywhere in a proposal’s query tree, but a function’s body is not in that tree. Do not grant an agent EXECUTE on such a function.

  • Without the library loaded before the agent’s first statement, the gate fails open. whoami() reports enforced. Use shared_preload_libraries.

  • Existing connections of a role are not behind the gate until they reconnect.
  • A dump of the database does not carry who is an agent. register_agent marks the role (ALTER ROLE ... SET agent_gate.agent), and roles belong to the cluster: pg_dump leaves the mark out, pg_dumpall --globals-only has it. Restored into a server without the globals, the record still says the role is an agent while the role is outside the gate. The record itself does survive pg_dump; tests/dump_restore.sh checks both halves.
  • An agent session may change only the session parameters on an allowlist: client formatting and time limits, which is what a driver sets on its own (application_name, client_encoding, DateStyle, statement_timeout, the transaction characteristics, and a few more). SHOW and transaction control pass; everything else is refused, including role, session_authorization, every agent_gate.* setting even if someone granted SET ON PARAMETER on it, and – the reason it is an allowlist and not a list of forbidden names – whatever the application decides with. A row-level policy over current_setting('app.tenant_id') is the ordinary way to separate tenants, and no denylist can name the parameters an application invents: measured, an agent could point that policy at another tenant and the same read returned the other tenant’s row (tests/rls_isolation.sh). An agent’s context is set on its role (ALTER ROLE ... SET), and a DBA who needs one more parameter adds it to agent_gate.settable – which is saying, out loud, that no policy of theirs stands on it.
  • The allowlist also covers parameters set when the connection starts. A startup parameter – libpq’s PGOPTIONS, the options property of pgjdbc and node-pg – is not a statement and reaches no hook, and a value the client sets at startup outranks the one on the role. Measured with pgjdbc 42.7.13, node-pg 8.23.0 and libpq before this was covered: options=-c app.tenant_id=2 and the agent read the other tenant’s row (tests/drivers.sh). An agent session that started with a parameter outside the allowlist now fails closed: every statement it sends is refused. What the drivers send on their own (client_encoding, DateStyle, TimeZone, extra_float_digits, application_name, a statement_timeout in options) is on the list and keeps working. search_path is deliberately not on it, so a driver configured with a default schema will be refused: pgjdbc’s connection code sends currentSchema as the startup parameter search_path (read in the driver, not measured here). Set the agent’s schema on its role instead.
  • The fast-path function-call protocol (PQfn) skips the parser. A function reached that way that runs no SQL – large objects – is not stopped. Revoke EXECUTE on those from agent roles.
  • With attempt_durability = fast, a crash can lose records of attempts that changed nothing. Never of changes.
  • dry_run is a rollback, not a sandbox. Sequence values, session advisory locks, and anything outside the transaction (dblink, untrusted languages) are not undone.
  • A row-level policy that calls a SECURITY DEFINER function refuses every proposal on its table. The common membership helper (USING (is_member(tenant)), SECURITY DEFINER so it can read the membership table) is in the analyzed tree – the rewriter adds the policy’s qual – so no_opaque_function refuses the statement, and since 0.2.7 the function-manager hook would stop it anyway. That is the price of the rule, not an accident: write such a policy over a security_invoker helper or a plain subquery on a table the agent may read, or the table is out of the agent’s reach.
  • An opaque user function is refused (0.2.3). A statement that calls a user function which is volatile or SECURITY DEFINER is refused at propose (no_opaque_function): its body is not in the analyzed tree, so it could write rows no one counts or run as its owner outside the agent’s tenant. Built-ins (pg_catalog, e.g. gen_random_uuid()) and non-volatile, non-SECURITY DEFINER user functions pass. There is no allow-list for functions in 0.2.3, so a volatile helper that does not write should be marked STABLE or IMMUTABLE. Measured (tests/hostile.sh): such a function inside a WRITE is refused and writes nothing.
  • no_opaque_function walks the proposal’s query tree, not every place a function can hide – this is a class, not a fixed list. It follows direct calls, operators, = ANY/IN, aggregates and window functions in the statement itself. It does NOT follow a function reached any other way: through a row comparison (RowCompareExpr), an aggregate’s own transition or final functions, an I/O or user cast, or a function the catalog attaches rather than the statement – a column DEFAULT, a CHECK or domain constraint, a generated column, an expression index, or the body of a view or another function the statement touches. For ordinary DML done this way the commit backstop still counts the rows against max_rows (bounded in COUNT, though not in tenant or privilege – like an allow-listed trigger, a row written this way is counted but not tenant-filtered). A nested UTILITY is worse than uncounted: pg_stat_xact ignores TRUNCATE, so the backstop could not bound a TRUNCATE, GRANT, ALTER or DROP a function fires. 0.2.4 refuses any utility a trigger, constraint or function reaches while the gate runs the verified statement (on the gate’s own execution flag, not current_user – inside a SECURITY DEFINER function the agent is already the owner). Measured: a CHECK whose function TRUNCATEd a table ran on a plain INSERT the agent proposed – before 0.2.4 the table was emptied and kept; now the same statement aborts and the table is intact. The window spans the gate’s own SET CONSTRAINTS, so a utility fired by a DEFERRED constraint trigger is caught too; the one utility the gate runs on purpose (an allow_ddl agent’s own DDL, and the SET CONSTRAINTS) is exempted, so it still goes through. This refuses ALL nested utility, benign included – a trigger that does NOTIFY, SET LOCAL, LOCK TABLE, CALL or CREATE TEMP TABLE makes the statement abort (pg_notify() called as a function is fine). A SECURITY DEFINER function reached through any of these positions is stopped before it runs (0.2.7). Every SECURITY DEFINER call goes through PostgreSQL’s fmgr_security_definer, which calls the function-manager hook before the body; while SQL the agent wrote is running (verification, the verified statement, its deferred constraints, an allowed DDL) the gate refuses there, however the call was reached – another function, a CHECK or domain constraint, a default, a generated column, an expression index, a trigger, or the planner folding an IMMUTABLE wrapper at propose. Measured on 0.2.6: such a wrapper verified and ran its SECURITY DEFINER callee at propose and again at commit; now neither, and the write a CHECK reached that way is not kept (tests/plan_time.sh). There is no allow-list for it. What this does NOT stop, because it is not a SECURITY DEFINER call: a non-SECURITY DEFINER function in those positions (it runs with the agent’s rights; if it writes, the commit backstop counts the rows), a referential action, which runs as the table owner by its own mechanism, and a view without security_invoker (below).
  • A cascade, trigger or rule that amplifies a write is refused (0.2.2/0.2.3). Its extra rows run as the table owner, outside the agent’s max_rows and – for a referential action – its tenant. agent_gate.allow_write(agent, table) re-permits one table, but only for what the gate can check: a trigger whose own function is not SECURITY DEFINER. A cascade and a rule are NOT allow-listable in 0.2.3 (making some cascades safe needs a recursive closure over the cascade edges, and lands in 0.2.4), and a SECURITY DEFINER trigger never is. The commit backstop still counts every amplified row against max_rows (it over-counts, the safe side). It reads pg_stat_xact_user_tables, which counts USER TABLES only – not foreign tables, large objects, sequences, or effects outside the transaction; a TRUNCATE it does not count is instead refused as a nested utility (0.2.4). “What the gate can check” is the honest bound: a non-SECURITY DEFINER trigger can still fire a cascade of its own that runs as a table owner outside RLS – the gate does not see the body. (A SECURITY DEFINER function it calls is stopped since 0.2.7.) Its rows are counted (so the limit holds) but not tenant-filtered. Allow-listing a trigger is a risk the DBA accepts for that one table; the backstop is the floor under it, not RLS.
  • A view is checked with its owner’s privileges unless it was created with security_invoker. An agent granted SELECT on such a view reads the tables behind it, including ones it has no privilege on at all: measured, a secret came through that way. That is PostgreSQL, not the gate – the gate verifies the proposal, it does not re-decide what a view may show. discover lists the view, because the agent was granted it, and not the table behind.
  • EXPLAIN folds constant calls to immutable functions. A function falsely marked IMMUTABLE could run at propose. Marking functions honestly is a prerequisite.
  • The record is transactional. Inside an explicit transaction that the caller rolls back, the record rolls back too; so does a propose_and_commit that is cancelled, or that fails outside the gate’s own subtransaction, where propose + commit would have kept the proposal. Every verdict is also written to the server log, which does not roll back – the verdict (agent, proposal id, kind, outcome), not the statement: with parameters, the log shows $1.
  • Parameters travel as text. Cast them in the SQL ($1::int).
  • User-defined casts around a verb’s arguments are allowed, like any cast. Creating a cast already needs ownership of the types.

License

Apache License 2.0 – see LICENSE. Copyright 2026 Manuel Reyes Bravo.

The name is not licensed with the code: see TRADEMARK.md. Security reports: SECURITY.md. Contributions: CONTRIBUTING.md.