> ## Documentation Index > Fetch the complete documentation index at: https://warden.buildlab.in/llms.txt > Use this file to discover all available pages before exploring further. # PostgreSQL > The reference connector: certified against a real server on every commit. 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 ```sql theme={null} -- 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: ```sql theme={null} 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. ```yaml theme={null} 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 ``` > [!NOTE] > > 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. 1. Make a `NOLOGIN` base role and grant it what agents may touch. This is the one place agent privileges are decided. 2. Install the helper functions that create, bar and drop lease roles, and let the gateway call them. The gateway itself never gets `CREATEROLE`. 3. Name it in the connection's options. ```sql theme={null} 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. ```yaml theme={null} options: lease_role: warden_agent ``` What happens then: * **Issue.** Each lease gets a role named `warden_lease_`: `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. > [!WARNING] > > 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. This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.