Safe SQL guardrails

The database role is the first boundary

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

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

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

Prefer domain tools:

{
  "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

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.