PostgreSQL

PostgreSQL is Warden’s reference connector. Everything the documentation describes works here, and is proven against a live server in CI.


Versions	PostgreSQL 14+
Certification	89 of 89 cases, strict, no gaps, including native identity and revocation
Driver	`pgx`, in describe-exec mode

What it provides, and who provides it

Guarantee	Level	How
Record scoping	`enforced`	Governed relations: each scoped table becomes a pre-filtered subquery
Column restriction	`enforced`	The subquery projects only permitted columns
Field masking	`enforced`	Masked inside the statement, before the agent’s SQL can alias or join to it
Statement timeout	`native`	`statement_timeout`, set per transaction and enforced by the server
Attribution	`native`	`application_name` plus a sanitised comment carrying agent, task, and step
Row limits	`enforced`	The cap is injected, and one extra row is fetched to report truncation honestly
Dry run	`native`	The change runs in a transaction and is rolled back
Cost estimate	`native`	`EXPLAIN`
Fence	`native`	`xmin` per row, re-checked `FOR UPDATE` inside the commit transaction
Read-only reads	`native`	Reads execute in read-only transactions
Per-agent identity	`native` with `lease_role`, else `unavailable`	A `NOLOGIN` role per lease, assumed with `SET LOCAL ROLE` in every transaction
Lease revocation	`native` with `lease_role`, else `unavailable`	`DROP ROLE`, after terminating any backend mid-transaction under it

Parsing

Warden links PostgreSQL’s own parser (pg_query_go), so its understanding of a statement cannot drift from the server’s. A statement is parsed into the server’s parse tree, edited, and deparsed back to SQL. A protobuf reflection walk finds every table reference in that tree — including CTEs, subqueries, FROM lists, USING clauses, and set operations — so nothing reaches the database unaccounted for. Schema-qualified names are checked against the catalog, which is what caught a homoglyph table and a nonexistent one during certification.

Governed relations

-- the agent's statement
SELECT c.email AS contact FROM shop.customers c WHERE c.tier = 'gold';

-- what the server receives, in essence
SELECT c.contact FROM (
  SELECT id, tenant_id, tier, mask(email) AS email
  FROM shop.customers WHERE tenant_id = $1
) c WHERE c.tier = 'gold';

Scope values are bound as parameters, never written in as literals. Masking uses HMAC-SHA256 with pads bound as parameters, so the masking key never appears in a statement the server logs.

Execution

Reads run in read-only transactions.
Changes that need review are previewed in a transaction that is rolled back, then committed in a second transaction that re-verifies the fence with the rows locked.
Per-agent concurrency slots with fail-fast checkout, so one agent cannot starve the others.
Each transaction sets its own statement_timeout and attribution with set_config.

Setting it up

The recommended role is least-privilege — no superuser, no BYPASSRLS, access only to the schemas it governs:

CREATE ROLE warden_gateway LOGIN;
GRANT USAGE ON SCHEMA shop, ops TO warden_gateway;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA shop TO warden_gateway;

make pg-fixture creates exactly this for a development database.

connections:
  - name: shop
    engine: postgresql
    dsn: postgres://warden_gateway:secret@db.internal:5432/shop?sslmode=require
    policy: ./policies/shop.yaml
    options:
      schemas: shop,ops          # limit introspection; the catalog is a map of your database
      statement_timeout_ms: "5000"
      max_conns: "5"
      application_name: warden

Set schemas. Without it the catalog includes every system table — useless to an agent, and a map of the database for anyone who should not have one.

A role per lease

Set lease_role and every lease becomes a real PostgreSQL role. The server then checks each statement against what the lease may do, not what the gateway may do, so a statement that somehow got past every one of Warden’s checks still cannot touch a table agents were never granted.

Make a NOLOGIN base role and grant it what agents may touch. This is the one place agent privileges are decided.
Install the helper functions that create, bar and drop lease roles, and let the gateway call them. The gateway itself never gets CREATEROLE.
Name it in the connection’s options.

CREATE ROLE warden_agent NOLOGIN;
GRANT USAGE ON SCHEMA shop TO warden_agent;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA shop TO warden_agent;

-- A role only the helper functions act as. It can't log in.
CREATE ROLE warden_provisioner NOLOGIN CREATEROLE;
-- This grant is the list of base roles leases may join; nothing else is accepted.
GRANT warden_agent TO warden_provisioner WITH ADMIN OPTION;
GRANT warden_gateway TO warden_provisioner;  -- to hand a lease's objects to the gateway

Then install warden.create_lease_role, warden.bar_lease_role and warden.drop_lease_role. Copy them from the leased-roles section of certification/profiles/postgres/fixture.sql, changing only the role names if yours differ. They’re SECURITY DEFINER, owned by warden_provisioner, and executable only by the gateway.

    options:
      lease_role: warden_agent

What happens then:

Issue. Each lease gets a role named warden_lease_<lease id>: NOLOGIN, no superuser, no BYPASSRLS, no CREATEROLE, a member of the base role.
Execute. Every transaction under the lease runs after SET LOCAL ROLE to it. That includes the agent’s statement, its preview, and its fence. On this connection a transaction with no leased role is refused; it never runs as the gateway.
Revoke or expire. The gateway’s membership in the role is removed first, so no gateway replica can switch to it any more. Every transaction running under it, on any replica, is found by the advisory lock it holds and terminated. Objects the lease created are reassigned to the gateway, so revoking access never deletes data. Then the role is dropped.
Restart. Leases that were live when the gateway stopped are revoked, and their roles dropped, before it serves anything.

The gateway proves all of this at startup. It creates a role through the helper, assumes it, and rolls the whole thing back. If any step fails, including missing helper functions, the connection refuses to start and says what is missing. It never falls back to emulated leases. A base role that can log in, is a superuser, bypasses row-level security, or can create roles is also refused, because every lease would inherit it. A lease can carry grants of its own beyond the base role, such as an elevation an operator approved. The gateway can only pass on privileges it holds WITH GRANT OPTION, and a grant it cannot make is refused rather than silently dropped. Only row-level access (read, append, write, delete) is grantable, and a delete grant can’t be limited to fields, because PostgreSQL grants DELETE per table. Schema and admin access never are. Each role carries a comment naming its lease, database and connection. The helper adds the gateway login itself, so the server records who made the role rather than taking the caller’s word for it. The helpers only act on roles made on the current database by the calling login, and Warden also requires the exact lease and connection before it assumes, reuses or drops a role. The warden_lease_ prefix alone proves nothing. If a drop fails, the role stays blocked on the server. The lease is already ended, so the reaper retries the drop on every sweep and at startup, working from the lease store. It only acts on leases its own store says have ended. A role it has no lease for may belong to another gateway replica, so it’s never dropped automatically; drop it by hand if the state it belonged to is gone.

Don’t give the gateway CREATEROLE. A gateway that can create roles can, once compromised, create itself a login role that no lease governs and that survives rotating its own password. The startup ladder flags a gateway that has it.

Known gaps

Native row-level security is not used as a second layer beneath governed relations. Planned.
Relationship scoping — scoping a child table through its parent — is not supported, so a table with no scopable column of its own cannot be granted safely. warden policy lint flags that combination.

How Warden works

Engines

What it provides, and who provides it

Parsing

Governed relations

Execution

Setting it up

A role per lease

Known gaps

​What it provides, and who provides it

​Parsing

​Governed relations

​Execution

​Setting it up

​A role per lease

​Known gaps

What it provides, and who provides it

Parsing

Governed relations

Execution

Setting it up

A role per lease

Known gaps