Agent-First PSQL v0.6: SSH Transport and Explicit Write Permissions

Agent-First PSQL v0.6 lands the two features that were missing for agents working with remote PostgreSQL: a local SSH transport, and a permission model that does not blur “write” and “remote write”.

SSH transport: the agent stays local

Most production PostgreSQL only listens on the server’s loopback. The usual workaround — SSH into the server and run human psql — mixes the database task with shell state, server files, and prose output the agent cannot reliably parse.

v0.6 keeps the agent on the local machine and tunnels only the PostgreSQL transport:

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 reads structured Agent-First Data events locally. TLS behavior is aligned with libpq’s sslmode semantics, so server certificates verify the same way across both transports.

For PostgreSQL that listens only on a Unix socket, afpsql forwards the socket. For the harder sudo -u postgres psql case, an explicit non-interactive sudo bridge uses sudo -n and requires the socket path to be passed explicitly — no ambient-path surprises.

Permissions: read by default, write across the right boundary

afpsql native CLI and pipe mode now default to PostgreSQL read-only transactions. Writes are explicit and split by transport:

--permission read       direct read   (default)
--permission write      direct write
--permission ssh-read   SSH read      (default with --ssh)
--permission ssh-write  SSH write

The split is the point. A request that uses afpsql’s SSH transport with --permission write is rejected before execution with a hint to use ssh-write. A direct session with --permission ssh-write is rejected the same way. The agent cannot accidentally generalize “I have write permission” into “I can write to whatever side of the SSH tunnel I am on.”

--mode psql keeps psql’s writable default for non-interactive script compatibility and intentionally does not expose afpsql permission flags or SSH transport extensions. The agent-safety contract lives in native afpsql.

Pipe-mode hardening

Several pipe-mode failure modes were tightened so an agent can rely on the protocol shape:

• Pipe config validation is stricter.
• SSH tunnel readiness is checked before queries are dispatched.
• Wrapped result streams are drained before savepoint release, preventing partial result leaks across statements.
• Duplicate result column names are rejected at the protocol layer instead of silently overwriting in object-shaped row output.
• Named sessions are tested to map to stable PostgreSQL backend sessions with FIFO execution — the named-session contract is now backed by integration tests, not just intent.

CLI input limits, result limits, and startup logging were also tightened so argv and connection secrets no longer reach log output. Redaction now goes through the same Agent-First Data policy other kit tools use, so secret handling looks the same across afhttp, afpsql, afpay, and afmail.

Agent Skill

v0.6 ships skills/agent-first-psql.md so coding agents that load skill files automatically pick up afpsql’s operating guidance: read-by-default, structured rows, SQLSTATE-based error handling, named sessions for state, and when to use SSH transport vs. a local connection.

psql wrapper, documented

afpsql psql install and afpsql psql status were documented as a first-class workflow: non-interactive psql calls in existing scripts can be routed through afpsql while interactive sessions and meta-commands are explicitly rejected with structured errors pointing back at the real psql binary.

Adoption

brew install agentfirstkit/tap/afpsql   # macOS / Linux
cargo install agent-first-psql          # any platform

For the agent-side framing behind these choices, see the v0.5 design post.