AI agents on PostgreSQL
pg_agent_gate v0.4.3
Agents propose. PostgreSQL decides.
An extension that makes SQL not execute in an agent's session: the agent may only propose one statement; PostgreSQL verifies it against itself, shows its exact effect, and keeps it only if every guard agrees. Enforced by hooks inside the server: there is no client, driver or protocol path around it.
Install
CREATE EXTENSION pg_agent_gate; In plain words
A gate between artificial intelligence and your database: the AI proposes, the database checks and decides.
- The situation
An AI assistant handles orders and can update customer data in your system.
- Without this piece
A misread instruction deletes 8 orders. It is a valid command, so the database complies. Nobody sees it until a customer complains.
- With this piece
The database sees it is 8 rows and that assistant's limit is 5: it does not run it, shows what would have happened, and records it. The legitimate change to one customer goes through.
Why it matters if you decide
- Bounded risk: the AI cannot do more damage than the limit your team sets, per assistant
- Evidence: every proposal, approval and refusal lands in a record that cannot be erased
- Simple to run: an extension inside your PostgreSQL, with no other service to keep up
The problem
Today the database cannot tell the agent from the application
An agent reaches PostgreSQL with a role and a connection. From then on, when the model hallucinates a DELETE without a WHERE, a DROP TABLE, or a read of another customer's rows, the database runs it: valid SQL with valid privileges.
Every guarantee lives in the MCP server or in the prompt, outside the database. The server obeys the model and PostgreSQL never learns what was checked.
| What the schema says | What happens |
|---|---|
| DELETE FROM orders, typed by the agent | Without the gate: 8 rows gone. With it: ERROR, the session proposes, it does not execute. |
| DROP TABLE customers | Without the gate: the table is gone. With it: refused at propose (kind_allowed: no DDL). |
| Delete 8 rows with a limit of 5 | With the gate: commit aborted, with the before/after of the rows. |
| A legitimate 1-row UPDATE | With the gate: kept, showing the effect before it is kept. |
How it works
Seven verbs, and in an agent session nothing else
- 01
discover
What it may touch: tables, views and functions it has privileges on, with columns, types, keys and constraints. From the live catalog, with a fingerprint so a client can tell the schema changed.
- 02
propose
One statement, nothing runs. Each check is PostgreSQL itself: parses, single_statement, kind_allowed, no_writing_cte, keeps_its_context, no_amplification, no_hidden_sql, no_opaque_function, read_changes_nothing / changes_only_rows, resolves.
- 03
dry_run
Runs inside a subtransaction and rolls back: rows touched, before and after of every row, and whether bound assertions would still hold.
- 04
commit
Verifies again, runs with the agent's own privileges and keeps it if every guard agrees. More rows than max_rows, or a broken assertion: undone. A proposal is committed at most once.
- 05
propose_and_commit, acts, whoami
Both steps in one call (one transaction, one commit); the append-only record of everything proposed and done, with why anything was refused; and which agent this session is and whether the gate is enforced.
SQL
Register an agent
register_agent refuses superuser, REPLICATION and BYPASSRLS roles, and members of pg_write_all_data, pg_execute_server_program or pg_write_server_files: an agent that can leave the gate is not behind it. It takes effect on the role's next connection.
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 customers 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'); What it guarantees
What it guarantees, with a suite per line
- Nothing the session types executes except the seven verbs: not DELETE, not DO, not COPY, not PREPARE, not a cursor.
- 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, in a function's body, a constraint or a default (0.4.2).
- No other role can become it: with the library in shared_preload_libraries, an agent role has no members, so no session but its own login can SET ROLE into it (0.4.1).
- A read changes nothing the record would not show: no sequence, lock or large object (0.4.3).
- Everything it proposed and did is recorded append-only, including why anything was refused. A TRUNCATE of the record is refused (0.2.0).
- A proposal expires: commit verifies again, and a proposal older than agent_gate.proposal_ttl_seconds (900) is refused outright.
- A change the gate keeps is always committed with the durability the server is configured with, and that flush also carries the record of the proposal that led to it: no change exists on disk without its proposal and its execution next to it.
What it does not do
What it does not cover, said here so nobody learns it the hard way
- Superusers, and whoever can edit postgresql.conf or restart the server: they can unload the gate. That is why register_agent refuses superusers.
- GRANT is still the authorization: the gate adds verification, it does not invent privileges or take them away. What a DBA attaches to a table (a trigger, a CHECK, a view, a definer function) runs with its own rules.
- It does not protect intent: a well-formed statement, within privileges and under max_rows, runs. Measured: asked to "set every customer's plan to free", the model wrote a correct 2-row UPDATE and the gate kept it, by contract.
- A function's body is trusted to be what the function is marked: the gate does not read it. It stops set_config(), SQL received as text and functions the agent can alter, wherever they are reached (0.4.3). What remains is a DBA's function marked STABLE or IMMUTABLE that writes: it runs, and only the row-count backstop sees what it changed. REVOKE EXECUTE ... FROM PUBLIC on anything an agent must not reach.
- Without the library loaded before the agent's first statement, the gate fails open: whoami() reports it. Use shared_preload_libraries. Existing connections are not behind it until they reconnect.
- pg_dump does not carry who is an agent (it lives on the role): restored without the globals, the record still says agent while the role is outside the gate.
- With attempt_durability = fast (the default), a server crash can lose the record of an attempt that changed nothing: a proposal, a dry run, a read, a refusal. Never of a change. With durable, every record pays its own flush.
- allow_ddl is schema power, not a bigger max_rows: an agent allowed DDL runs any CREATE, ALTER or DROP its grants permit, and max_rows does not bound what it does to its own tables. A granted DROP TABLE is kept. Leave it false unless a specific agent needs it.
Measured
Measured with pg_agent_gate 0.4.0, on a public CI run anyone can re-run
| Trap tasks that broke out | Same model, same tasks, a superuser checking what really changed: through a popular Postgres MCP server, 4 of 7 trap tasks broke out. With the database deciding, 0 of 7. |
| Tokens and time, same gate behind both | With the same gate behind both, the agent without MCP used 5x fewer prompt tokens than through MCP (24k vs 126k for 13 tasks) and finished each task 13x faster: a median of 14 s against 185 s, on a CPU-only CI runner anyone can re-run. |
| One statement, the database decides | Writing one statement and letting the database decide took a median of 6 s per task. |
| A gap, found and closed | We found a gap ourselves — a looping model split a refused delete into legal slices — and closed it in 0.4.0: a write budget per agent, on by default. |
Reference run: the CI of the agent-gate-demo repository (github.com/Manuelreyesbravo/agent-gate-demo, run 41184106078), on a CPU-only GitHub runner.
For the enterprise
For the enterprise
This is the piece that lets you connect agents to the production database under an auditable contract: what each agent may touch, how many rows, with or without DDL, and a record of every proposal with its verdict.
- One agent per use case, each with its role, its max_rows and its bound assertions
- Append-only record of proposals and executions: evidence for audits and for reviewing what a model did
- No MCP server required: the agent reaches it over psql, a driver or direct SQL. A shim exists for clients that only speak MCP, with no power of its own
- ygg-agent (Python, Apache-2.0) is the ready-made loop between a model and the gate; since 0.2.0 it can run the model in the same process, with no model server and no HTTP
- A write budget per agent, on by default (since 0.4.0)
Spec sheet
- Version
- 0.4.3 on PGXN; the public image 0.3.6 ships 0.4.3 (measured 2026-11-13)
- PostgreSQL
- 18 or later (dry_run uses RETURNING old/new); suite green on 18 and 19
- Built with
- pgrx 0.19.2 (Rust)
- Configuration
- shared_preload_libraries = 'pg_agent_gate' (recommended)
- License
- Apache-2.0
- Enterprise edition
- Included (PostgreSQL 18)
The other pieces of the ecosystem
- pg_phasevec · preview 0.x Vector memory with provenance
- pg_living_assertions Guarantees with a date
- pg_plan_guard Query plans, watched
- pg_recall_guard Vector index recall
- pg_promise_guard Schema guarantees
- pg_grammar_guard Structured output that cannot invent names
- ygg-agent v0.2.0 · PyPI The agent loop on pg_agent_gate, with no MCP: the model writes SQL and the database decides.