Agent-First PSQL

A PostgreSQL interface for AI agents: reliable, structured, explicit, and read-only by default.

The problem: a terminal transcript is not a database contract

Classic psql is excellent for a human at a terminal. It is not a stable contract for an agent. It renders tables as text, mixes human interaction with execution, and turns many failures into prose that an agent has to guess about.

afpsql gives agents a dependable PostgreSQL contract:

• Structured results. Rows, columns, command tags, logs, and errors are emitted as machine-readable Agent-First Data events.
• Machine-readable failures. PostgreSQL execution errors carry SQLSTATE; connection-time PostgreSQL rejections preserve SQLSTATE diagnostics on connect_failed; runtime/protocol failures use stable error_code values and actionable hints.
• Safe write boundary. Native CLI and pipe mode default to PostgreSQL read-only transactions; writes must opt in with explicit permission.
• Predictable session state. Pipe named sessions map to stable PostgreSQL backend sessions with FIFO execution, so temp tables, GUCs, and other session state behave predictably.
• No SQL guesswork. Runtime behavior is derived from PostgreSQL metadata, not SQL-text heuristics.
• First-class SSH/container boundaries. Use --ssh, --container, or --ssh + --container to keep the agent local while crossing server, container, and remote-container boundaries with structured output and transport-specific permissions.
• psql compatibility where it helps. --mode psql translates common non-interactive psql flags for scripts, while preserving psql's writable default.

The goal is reliability for agents, not being a high-throughput pooler or an interactive database UI. Reusing a backend session is part of the reliability contract for session state; any latency benefit is secondary.

Where to use it: read checks, safe writes, stateful sessions, and script bridges

Use native CLI mode for one agent action:

afpsql --host 127.0.0.1 --port 5432 --user app --dbname appdb \
  --sql "select id, status from jobs where id = $1" \
  --param 1=123

Use pipe mode when an agent needs a long-running conversation with the database, especially when later statements depend on PostgreSQL session state:

afpsql --mode pipe --dsn-secret-env DATABASE_URL

Use psql-compatible mode only for non-interactive script compatibility:

afpsql --mode psql -h 127.0.0.1 -p 5432 -U app -d appdb -c "select 1"

Human terminal sessions, prompts, and psql meta-commands are intentionally out of scope. Use the original PostgreSQL psql binary for those.

Write safety: read by default, explicit by permission

Native afpsql and pipe mode are read-only by default:

• direct connection default: read
• afpsql SSH transport default: ssh-read
• afpsql container transport default: container-read

Writes are explicit:

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

SSH transport has its own write permission so agents cannot silently turn a remote/local boundary into a write path:

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

Container transport also has its own write permission:

afpsql --permission container-write --container pg-container \
  --dsn-secret-env DATABASE_URL \
  --sql "update jobs set checked_at = now() where id = $1" \
  --param 1=123

--mode psql deliberately keeps psql's writable default for script compatibility and does not expose afpsql permission flags.

First-class remote and container access: keep the agent local

Keep afpsql on the machine where the agent runs. SSH and container access are core transports, not recipes for shelling into another environment to run human psql. If PostgreSQL only listens on the server, use afpsql's SSH transport instead of installing afpsql on that server or asking the agent to run human psql over SSH:

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

If the working manual command is docker exec CONTAINER psql ... or an equivalent Podman, nerdctl, Compose, or Kubernetes exec, use container transport instead of container-local psql. The container does not need afpsql or psql; afpsql uses a no-TTY exec bridge through the selected driver:

afpsql --container pg-container \
  --dsn-secret 'postgresql://app:pw@127.0.0.1:5432/appdb' \
  --sql "select now()"

For container-local Unix sockets, pass the socket directory as --host:

afpsql --container pg-container \
  --host /var/run/postgresql --port 5432 \
  --user app --dbname appdb \
  --sql "select current_user"

For peer-authenticated sockets, add --container-user to run the bridge as the matching container OS user.

For containers on a remote SSH host, combine afpsql's existing SSH transport with container transport. Do not SSH in and then run a container-local psql; local afpsql drives both boundaries. The container exec command runs on the SSH host, and permissions stay in the container family:

afpsql --ssh root@server --container app-container \
  --container-driver docker \
  --host postgres --port 5432 \
  --user app --dbname appdb \
  --password-secret-env PGPASSWORD \
  --sql "select 1"

Use --container-driver podman|nerdctl|compose|kubectl when the target uses a non-default exec driver. --container-runtime can override the executable path, for example --container-runtime docker-compose with --container-driver compose for Compose v1. Use named scope flags such as --container-context, --container-namespace, --container-compose-file, and --container-compose-project instead of raw driver option passthrough.

Use host.docker.internal only when the Docker environment provides it (Docker Desktop, or Linux configured with host-gateway).

For socket/peer-auth and sudo bridge cases, see the Overview.

Adopt it: make afpsql the agent's PostgreSQL interface

Agent-First PSQL is most useful when the agent treats it as the default way to touch PostgreSQL, and treats human psql as the fallback for interactive admin work. Paste this to your coding agent:

Ensure afpsql is installed locally (brew install agentfirstkit/tap/afpsql or cargo install agent-first-psql if missing). Then run afpsql skill install and verify afpsql skill status. After that, follow the installed Agent-First PSQL skill for PostgreSQL work. If I ask to replace non-interactive psql, run afpsql psql install and verify afpsql psql status reports active_in_path: true.

Manual install, if you are doing it yourself:

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

For agents with local skill support, install the embedded Agent Skill from the binary:

afpsql skill status
afpsql skill install
afpsql skill status

To replace psql for non-interactive scripts:

afpsql psql status
afpsql psql install
afpsql psql status

When status reports active_in_path: true, ordinary script calls keep their psql-shaped arguments and return structured afpsql events:

psql -h 127.0.0.1 -p 5432 -U app -d appdb -c "select 1 as n"

The wrapper is only for non-interactive psql calls. Human terminal sessions, prompts, and meta-commands should use the original PostgreSQL psql binary.

Docs

• Overview — agent usage guide: modes, permissions, sessions, SSH, and container
• Protocol Reference — exact runtime fields and event schema
• Agent Skill — behavior rules for AI-assisted database access
• CLI — generated command and flag reference
• Design — reliability-first architecture and non-goals

License

MIT