Query PostgreSQL Safely with a CLI

---
name: query-postgres-workflow
description: "Use psql non-interactively with a read-only role, bounded queries, and machine-readable output."
---

# Query PostgreSQL

## Intended outcome

Answer a database question without exposing credentials or modifying data.

## Evidence boundary

- Tool status distinguishes `docs-verified` material from independent execution; the two are not interchangeable.
- The current registry does not attribute a tool execution to a particular agent, so compatible agents cannot be called tested agents.
- Reconfirm the target account, environment, version, and official documentation before execution.
- Never auto-execute an R2, R3, or confirmation-marked step; obtain explicit approval immediately before it runs.

## Recommended tools, installation, and authentication

- **PostgreSQL psql** (evidence: `verified`, docs checked: `2026-07-10`, independently tested version 18.1 recorded)
  - Install: `brew install libpq`
  - Authentication: connection URI, password file, service file, peer authentication
  - Minimum permissions: Use a read-only database role, TLS, and a database/schema search path limited to the task.
- **jq** (evidence: `verified`, docs checked: `2026-07-10`, independently tested version jq-1.7.1-apple recorded)
  - Install: `brew install jq`
  - Authentication: not required for the base operation
  - Minimum permissions: No service credential is required; restrict filesystem and network access to the task.

## Input contract

- Connection identifier from the environment
- read-only database role
- question and row limit
- expected columns

## Output contract

- Bounded query result
- query text
- timing or error details
- interpretation with limitations

## Safe workflow

1. **Confirm the target safely** — Use environment-backed connection settings and print database identity without printing a password.
   - Input: Connection environment and read-only role
   - Output: Confirmed host, database, and user
   - Risk: `read-only`
   - Command: `psql -X -v ON_ERROR_STOP=1 -c "select current_database(), current_user"`
2. **Run a bounded read** — Wrap SELECT work in a read-only transaction and request a parseable format.
   - Input: Reviewed SELECT query
   - Output: CSV or unaligned rows
   - Risk: `read-only`
   - Command: `psql -X --csv -v ON_ERROR_STOP=1 -c "begin read only; select * from example limit 100; commit;"`
3. **Report query and limits** — Return the query, row count, output shape, and any assumptions without echoing credentials.
   - Input: Query result and error stream
   - Output: Database answer with provenance
   - Risk: `read-only`

## Ask before proceeding

- Any INSERT, UPDATE, DELETE, DDL, migration, or function call with side effects
- Connecting with an owner or superuser role
- Exporting sensitive or high-volume rows

## Rollback

- Use a read-only transaction so the default path has nothing to roll back
- For approved writes, wrap changes in a transaction and verify before commit
- Restore from a tested backup for changes that cannot be transactionally reversed

## Official sources

- [PostgreSQL psql documentation](https://www.postgresql.org/docs/current/app-psql.html) — Official psql options, variables, output formats, and execution behavior.