Agent-First PSQL v0.5: A PostgreSQL Connector Designed from the Agent Side

by Agent-First Kit Contributors

The v0.5 afpsql line asks what a PostgreSQL connector should look like when the primary caller is an agent: structured state, explicit permissions, stable sessions, and local control over remote databases.

A human database client can assume a human is watching the terminal. It can print a table, write prose to stderr, pause for a password, and trust the person to notice that update was more dangerous than select.

An agent connector has a different job. The caller may be generating SQL, may be operating across SSH, may need to preserve session state, and still has to decide what to do next from machine-readable facts.

The v0.5 afpsql line is shaped around that question:

What should a PostgreSQL connector look like when the primary user is an autonomous coding agent?

The premise: an agent connector is a decision interface

A terminal client returns a screen. A good agent connector returns state.

For PostgreSQL, that means rows are typed data, command tags are fields, timing lives in trace, and failures preserve PostgreSQL’s own SQLSTATE. The agent should not have to parse a padded text table to find a row count. It should not have to grep stderr for “duplicate key”. It should branch on stable database facts.

{"code":"sql_error","sqlstate":"23505","message":"duplicate key value violates unique constraint \"users_email_key\"","trace":{"duration_ms":2}}

That is the difference between “the command failed” and “PostgreSQL reported a unique violation, so the agent can handle the duplicate path.” The English message is still present for humans reading logs, but the protocol value is the structured state.

This is the first afpsql design rule: stdout is for Agent-First Data events, not a terminal transcript.

The safety rule: writes need a boundary outside the SQL string

Generated SQL is useful because it is flexible. It is risky for the same reason. A model can misunderstand a request, choose update when a read was intended, or run a destructive check against the wrong database.

The new native afpsql default is read-only. CLI and pipe requests run in a PostgreSQL read-only transaction unless the caller crosses an explicit permission boundary:

afpsql --permission write \
  --sql "update jobs set checked_at = now() where id = $1" \
  --param 1=123

This does not replace database roles, backups, or human review. It adds a guard at the connector boundary: the command shape must say “this is a write” before the SQL text is allowed to write.

Remote writes have their own boundary:

afpsql --permission ssh-write --ssh user@server \
  --host 127.0.0.1 --port 5432 \
  --user app --dbname appdb \
  --password-secret-env PGPASSWORD \
  --sql "update jobs set checked_at = now() where id = $1" \
  --param 1=123

write and ssh-write are intentionally not aliases. If the session uses afpsql’s SSH transport, direct write is rejected with a hint to use the SSH permission family. If the session is direct, ssh-write is rejected with a hint to use the direct permission family. The agent cannot accidentally blur “local write” and “remote write over SSH.”

psql compatibility mode remains script-compatible and keeps psql’s writable default. Native afpsql is the agent-safe interface.

The remote rule: keep the agent local and move only the transport

A common human workflow is: SSH into the server, run psql, read the screen. That is not a clean agent boundary. Once the agent is inside the server shell, the database task is mixed with shell state, server files, sudo prompts, psql formatting, and whatever tools happen to be installed remotely.

A better connector keeps the agent on the local machine and moves only the PostgreSQL transport through SSH:

afpsql --ssh user@server \
  --host 127.0.0.1 --port 5432 \
  --user app --dbname appdb \
  --password-secret-env PGPASSWORD \
  --sql "select now()"

The server does not need afpsql installed. The agent still receives local structured events. The permission model still applies: afpsql SSH reads default to ssh-read, and SSH writes require ssh-write.

Some production PostgreSQL installations do not expose TCP at all. They rely on a Unix socket on the server. afpsql can still keep the agent local and forward that socket:

afpsql --ssh user@server \
  --host /var/run/postgresql \
  --user app --dbname appdb \
  --sql "select current_user"

For the hard cases where the only manual command is sudo -u postgres psql, afpsql has an explicit non-interactive sudo bridge. It uses sudo -n, requires an explicit socket path, and fails with a structured error instead of hanging on a password prompt.

The rule is simple: do not turn a database query into an unbounded remote terminal session unless the user explicitly asked for a remote terminal session.

The session rule: named state should mean a PostgreSQL backend session

Agents often need database state across steps. A temp table created in one query has to exist in the next. A session setting has to stay set. A workflow may need FIFO ordering inside one named context.

That is a reliability contract, not pool marketing.

In afpsql pipe mode, a named session is intended to map to a stable PostgreSQL backend session until config invalidation or shutdown. Queries in that named session run in order. Different named sessions are isolated.

{"code":"query","id":"create","session":"work","sql":"create temp table t(n int)","options":{"permission":"write"}}
{"code":"query","id":"insert","session":"work","sql":"insert into t values (7)","options":{"permission":"write"}}
{"code":"query","id":"read","session":"work","sql":"select n from t"}

That gives the agent a simple mental model: if it needs PostgreSQL session state, name the session and keep using that name. The product goal is not to make a pool look fast; the goal is to make state predictable.

The shape of this release: afpsql encodes the contract

The v0.5 line moves afpsql closer to an agent-native PostgreSQL connector:

The important change is not one flag. It is the posture: afpsql treats database access as a protocol between an agent and PostgreSQL, not as a prettier terminal session.

The next direction: more policy, more context, fewer guesses

A good agent connector is a long-term design space. Some next steps are clear:

The direction is not “make psql prettier” and not “hide PostgreSQL behind an ORM.” The direction is a connector that exposes PostgreSQL clearly enough that an agent can act responsibly: typed data, explicit permissions, stable sessions, remote boundaries, and failures that are meant to be handled.