# Safe SQL guardrails

Canonical URL: https://pg.edu.rich/en/docs/ai/safe-sql

Last reviewed: 2026-08-02





## The database role is the first boundary [#the-database-role-is-the-first-boundary]

```sql
CREATE ROLE agent_reader LOGIN;
GRANT CONNECT ON DATABASE commerce TO agent_reader;
GRANT USAGE ON SCHEMA app TO agent_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA app TO agent_reader;
ALTER DEFAULT PRIVILEGES FOR ROLE app_owner IN SCHEMA app
  GRANT SELECT ON TABLES TO agent_reader;

ALTER ROLE agent_reader SET default_transaction_read_only = on;
ALTER ROLE agent_reader SET statement_timeout = '5s';
ALTER ROLE agent_reader SET lock_timeout = '1s';
ALTER ROLE agent_reader SET idle_in_transaction_session_timeout = '10s';
```

Configure credentials through a secret manager or cloud identity integration, never migrations, prompts, or tool responses. Confirm the role cannot `SET ROLE` into a stronger role.

## Pre-execution policy [#pre-execution-policy]

Validate generated SQL with a parser/AST, not regex. Default rules:

* Allow one `SELECT` statement only.
* Reject `COPY ... PROGRAM`, large objects, foreign-data wrappers, and dangerous functions.
* Reject multiple statements and comment-based bypasses.
* Restrict accessible schemas, tables, and columns.
* Bind values; choose identifiers only from an allowlist.
* Add a `LIMIT` to non-aggregate results and cap returned bytes in the driver.
* A cost preflight with `EXPLAIN (FORMAT JSON)` is useful, but estimated cost is not a runtime guarantee.

## Use a read-only transaction for each read [#use-a-read-only-transaction-for-each-read]

```sql
BEGIN READ ONLY;
SET LOCAL statement_timeout = '5s';
SET LOCAL lock_timeout = '1s';
SET LOCAL search_path = app, pg_catalog;

SELECT id, status, total_cents
FROM orders
WHERE customer_id = $1
ORDER BY placed_at DESC
LIMIT 100;

COMMIT;
```

Read-only transactions can still run expensive queries and expose readable data. Privilege, cost, and output bounds are all required.

## Do not expose arbitrary write SQL [#do-not-expose-arbitrary-write-sql]

Prefer domain tools:

```json
{
  "tool": "cancel_order",
  "arguments": {
    "order_id": 8842,
    "expected_status": "pending",
    "reason": "duplicate order",
    "idempotency_key": "case-2026-184"
  }
}
```

The application validates identity and transition, runs parameterized SQL in a transaction, and returns a typed outcome. Bulk writes, DDL, `GRANT`, restore, and replication configuration should not be general-agent tools.

## Audit fields [#audit-fields]

Record requester/tenant, tool, model and prompt versions, contract version, database target, parameter-redacted SQL fingerprint, risk tier, approver, row count, duration, SQLSTATE, and truncation state. Do not copy raw sensitive results into general logs.

<Callout type="warn" title="A row-count check is not transaction design">
  Writing first and noticing “too many rows” later may already fire triggers or external effects. Preview the target set in a controlled transaction, or make the domain API constrain the modifiable set in its predicate.
</Callout>