# Postgres

> Query and execute PostgreSQL SQL through a JSON CLI, including bounded read-only fan-out across hosts. Use when the user explicitly asks to inspect PostgreSQL/Postgres or run a confirmed DDL, DML, or administrative statement. Access is limited by the configured PostgreSQL role grants.

- Skill: `bgevorkian/postgres` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx skillmds@latest add bgevorkian/postgres`
- Raw SKILL.md: https://api.skillmd.com/api/skills/bgevorkian/postgres/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- License: MIT
- Author: bgevorkian (https://skillmd.com/u/bgevorkian)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/bgevorkian/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`:

```bash
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.

| Variable | Flag | Default |
|---|---|---|
| `DATABASE_URL` | `--dsn` | none |
| `PGHOST` | `--host` | none |
| `PGPORT` | `--port` | `5432` |
| `PGDATABASE` | `--database` | none |
| `PGUSER` | `--user` | operating-system user |
| `PGPASSWORD` | none | empty |
| `PGSSLMODE` | `--ssl-mode` | `require` |

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

## Read

```bash
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:

```bash
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

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

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

## Tests

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