Postgres

PostgreSQL

Generic PostgreSQL JSON CLI. Read commands run in read-only transactions; exec runs one statement allowed by the configured PostgreSQL role.

Local .env

The CLI automatically loads a private .env beside this SKILL.md through pass-cli. Copy .env.example to .env and replace placeholders with environment values or secret-manager pointers.

A single .env may hold namespaced profiles selected with --profile:

uv run --python 3.13 --with asyncpg python scripts/pg.py --profile analytics query --sql "SELECT current_database()"

Without --profile, ordinary DATABASE_URL or PG* variables are used.

Never pass a password as a CLI argument or commit a real .env.

Read

uv run --python 3.13 --with asyncpg python scripts/pg.py query --sql "SELECT current_database(), now()"
uv run --python 3.13 --with asyncpg python scripts/pg.py query-many --hosts db-a.example.net,db-b.example.net --sql "SELECT 1"
uv run --python 3.13 --with asyncpg python scripts/pg.py list-schemas
uv run --python 3.13 --with asyncpg python scripts/pg.py list-tables --schema public
uv run --python 3.13 --with asyncpg python scripts/pg.py describe-table --schema public --table events

query and query-many reject obvious mutations and run inside transaction(readonly=True). Fan-out is bounded to 20 parallel hosts.

Execute

Use exec only after the user approves the exact operation and target:

uv run --python 3.13 --with asyncpg python scripts/pg.py \
  exec --sql "CREATE INDEX events_created_idx ON public.events(created_at)"

exec accepts one DDL, DML, or administrative statement in a normal transaction. It rejects empty and multi-statement SQL. There is no local write authorization gate: PostgreSQL role grants are the authorization boundary.

--sql accepts literal SQL, @file.sql, or -. --params is a JSON array for $1, $2, … placeholders.

Safety contract

• Obtain fresh user approval before each mutation scope.
• Read commands retain local SQL guards, read-only transactions, timeout, result limits, and bounded fan-out.
• exec uses a normal transaction and rejects empty or multi-statement SQL.
• The configured PostgreSQL role decides which operations are allowed.
• TLS is required by default; disable it only for a trusted endpoint that does not support SSL.
• Do not print environment variables, DSNs, passwords, or .env contents.
• Ask before touching sensitive data.

Output

{
  "rows": [{"current_database": "app"}],
  "row_count": 1
}

Execution returns {"ok": true, "result": "..."}.

Tests

uv run --python 3.13 --with asyncpg python tests/test_pg.py