Agent-First PSQL

Ask your agent: “How many orders shipped late last month?”

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 error.code="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.
• PostgreSQL-enforced writes. Query shape and write enforcement come from PostgreSQL metadata, transaction mode, and permissions. The readonly binary additionally uses a narrow lexical classifier for transaction-control SQL.
• First-class SSH/container boundaries. Use --ssh, a --container-<driver>-* flag family, or both together 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 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.

Read connection secrets from application config

Each connection secret can come directly from a JSON, TOML, YAML, or dotenv file without shell command substitution or a subprocess: afpsql reads the value in-process through Agent-First Data’s document layer.

afpsql --dsn file:config.yaml#database.url \
  --sql 'select 1'
afpsql --dsn file:.env#DATABASE_URL \
  --sql 'select 1'
afpsql --conninfo file:.env#PG_CONNINFO \
  --sql 'select 1'
afpsql --ssh user@server \
  --dsn file:config.yaml#database.url \
  --sql 'select 1'
afpsql --host localhost --user app --dbname app \
  --password file:.env#PGPASSWORD \
  --sql 'select 1'

A config source is one typed argument containing both a file and dot path; malformed sources are refused rather than partially interpreted. Within one secret slot, literal, env:NAME, and file:PATH#DOT_PATH are the supported forms. A config value must exist, be a non-empty string, and is read once during process startup. Pipe mode keeps that resolved in-memory value across reconnects; it does not watch the file or accept dynamic config-file references in pipe requests.

Resolved secrets never enter a temporary environment variable, startup logs, errors, or config responses. A literal source is necessarily present in argv; prefer env: or file: when process arguments are observable. Runtime config output represents a configured dsn_secret, conninfo_secret, or password_secret as ***. Startup logging may include only the source kind, file path, and dot-path.

With SSH or container transport, afpsql parses DSN/conninfo locally and uses its host/port or Unix socket inside the final transport boundary. It preserves the remaining authentication and TLS settings; do not reveal or split a connection URL in shell code. Boundary transports currently require the source to resolve to one PostgreSQL endpoint rather than a multi-host failover list.

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 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-docker-name pg-container \
  --dsn 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.

Narrow client guard for read access

Use afpsql-readonly as a client-side guard when an agent needs database reads. It hard-rejects write permissions, read-write pipe transactions, transaction control SQL, and psql translation mode while continuing to support the same SQL files, secret env/config sources, SSH options, container runtimes, stream redirection, and skill management as afpsql. Its name promises no PostgreSQL write permission; it is not a general host capability sandbox.

This executable is not the database authorization boundary. In adversarial deployments, use a dedicated PostgreSQL reader role with only the required CONNECT, schema USAGE, relation SELECT, and audited function EXECUTE privileges. A host rule such as Bash(afpsql-readonly:*) still authorizes caller-selected local files and environment variables, arbitrary database and SSH/container targets, process-spawning transport options, network connections, and all data the reader role may see; evaluate that scope against the host’s policy before whitelisting it.

For a host rule restricted to one administrator-defined endpoint, install a profile executable name such as afpsql-readonly-production (a symlink or copy of afpsql-readonly) and a matching /etc/afpsql/readonly-profiles/production.json. The JSON has the same flat connection/SSH/container fields as a pipe session. It must be root-owned and not group/world writable. This locked executable rejects every CLI connection or transport override and every pipe session patch, and reads no connection environment variables at all; the agent may supply only query/result-shaping inputs. Whitelist the profile executable itself rather than parsing flags in a shell-prefix rule.

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 \
  --dsn file:config.yaml#database.url \
  --sql "select now()"

The DSN host/port is interpreted from user@server, while afpsql keeps the connection secret local. Discrete --host, --port, --user, --dbname, and password sources remain available when the application does not store a DSN.

For two-hop SSH, keep afpsql in charge of the transport and pass the jump host through OpenSSH options instead of creating an external temporary port forward:

afpsql --ssh user@db-server \
  --ssh-option ProxyJump=bastion \
  --host 127.0.0.1 --port 5432 \
  --user app --dbname appdb \
  --password env:PGPASSWORD \
  --sql "select now()"

If the working manual command is container exec CONTAINER psql ..., 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-apple-name pg-container \
  --dsn 'postgresql://app:pw@127.0.0.1:5432/appdb' \
  --sql "select now()"

The Docker family remains available when Docker is the selected runtime:

afpsql --container-docker-name pg-container \
  --dsn '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-docker-name pg-container \
  --host /var/run/postgresql --port 5432 \
  --user app --dbname appdb \
  --sql "select current_user"

For peer-authenticated sockets, add the family’s user flag (--container-docker-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-docker-name app-container \
  --host postgres --port 5432 \
  --user app --dbname appdb \
  --password env:PGPASSWORD \
  --sql "select 1"

The driver is the flag family, not a separate selector: use --container-apple-name, --container-podman-name, --container-nerdctl-name, --container-compose-service, or --container-kubectl-pod when the target uses another exec driver. Each family carries only the options its driver has, so --container-kubectl-namespace exists and --container-podman-context does not, and flags from two families cannot be combined.

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, use --ssh-remote-socket with --ssh-sudo-user (see the CLI reference).

Adopt it: hand afpsql to your agent

The quickest way to find out whether afpsql fits your setup is to let your agent read it and tell you. Paste this to your agent:

Read what Agent-First PSQL is at https://agentfirstkit.com/agent-first-psql, then tell me in plain terms what it would do for me and whether it fits what I’m working on. If it’s a fit, install it — the prebuilt package for the quick path, or build from source after a quick security review of the repo if you’d rather read what you run — then run afpsql skill install so you follow its behavior rules.

If it’s a fit, install it — a prebuilt package, or from source if you want to read it first:

# prebuilt binary
brew install agentfirstkit/tap/afpsql   # macOS / Linux
scoop bucket add agentfirstkit https://github.com/agentfirstkit/scoop-bucket && scoop install afpsql   # Windows

# or build from source after reviewing the repo
git clone https://github.com/agentfirstkit/agent-first-psql
cargo install --path agent-first-psql

Then install the embedded Agent Skill so the agent follows afpsql’s behavior rules. skill install targets Codex, Claude Code, opencode, and Hermes; skill status reports whether each install is present, valid, and current:

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

• CLI — generated command, flag, and concept reference
• Protocol Reference — exact runtime fields and event schema
• Agent Skill — behavior rules for AI-assisted database access
• Design — reliability-first architecture and non-goals