# Postgresql Crud

> Use when the user wants to configure saved PostgreSQL database connections, connect directly or through SSH, inspect schemas, query rows, insert records, update records, delete records, or run safe PostgreSQL SQL with a Bash script, dry-run protections, readonly profiles, local profile storage, SSH tunnel access, or remote-server PostgreSQL access through a saved .env DATABASE_URL.

- Skill: `finpeakinc/postgresql-crud` (Agent Skill, multi-file: 3 files)
- Install (CLI): `npx skillmds@latest add finpeakinc/postgresql-crud`
- Raw SKILL.md: https://api.skillmd.com/api/skills/finpeakinc/postgresql-crud/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Data & Analytics
- Author: finpeakinc (https://skillmd.com/u/finpeakinc)
- Updated: 2026-09-17
- Page: https://skillmd.com/skills/finpeakinc/postgresql-crud

---


# PostgreSQL CRUD

Safely inspect, query, and change PostgreSQL data through saved local profiles. The skill uses a Bash script and the `psql` CLI; it does not depend on Python, `psycopg`, or `jq`.

## Purpose

Use this skill for:

- saving reusable PostgreSQL connection profiles
- direct PostgreSQL CLI connections
- SSH tunnel connections to private PostgreSQL hosts
- SSH remote execution where the database URL exists on a server, such as a remote `.env` file
- schema-aware table operations with `public` as the default schema
- schema inspection
- `select`, `insert`, `update`, `delete`, and raw SQL

Output:

- JSON printed to stdout
- the same JSON saved to `./out/postgresql-crud-<UTC timestamp>-<pid>.json` by default

## Safety Rules

- Never save database credentials in this repository.
- Use `~/.config/postgresql-crud/profiles/<profile>.conf` for saved profiles.
- Do not print passwords or full database URLs; use `list-profiles` for redacted output.
- Prefer read-only queries unless the user clearly asks to mutate data.
- For `insert`, `update`, and `delete`, run dry-run first.
- Run writes with `--execute` only after explicit user confirmation.
- `readonly` profiles must not be used for `insert`, `update`, `delete`, or raw write SQL.
- `update` and `delete` require `--where` unless the user explicitly confirms a full-table operation and `--allow-full-table` is passed.
- Raw write SQL requires both `--execute` and `--allow-raw-write`.
- Use parameter placeholders in filters, such as `--where "email = :email" --param email=a@example.com`.
- Do not invent schema, table, or column names. Run `schema` first when unsure.
- Default to schema `public` when the user gives only a table name.

## What This Skill Needs

- `bash`
- local `psql` CLI for `direct` and `ssh-tunnel` profiles
- `ssh` for `ssh-tunnel` and `ssh-remote` profiles
- remote `bash` and `psql` CLI for `ssh-remote` profiles

## Profile Storage

Profiles are saved under:

```text
~/.config/postgresql-crud/profiles/
```

The default profile name is saved at:

```text
~/.config/postgresql-crud/default_profile
```

Each profile is a `0600` shell-style config file. Example:

```bash
MODE=ssh-remote
READONLY=true
SSH_ALIAS=fr
REMOTE_CWD=/server/app
ENV_FILE=.env
ENV_KEY=DATABASE_URL
```

Do not edit this file by hand unless needed; prefer `configure`.

## Configure Profiles

### Direct PostgreSQL

Prefer explicit fields:

```bash
bash <skill-path>/scripts/postgresql_crud.sh configure \
  --profile local \
  --mode direct \
  --pg-host 127.0.0.1 \
  --pg-port 5432 \
  --pg-database app_db \
  --pg-user app_user \
  --prompt-pg-password \
  --default
```

Add `--test-connection` to verify `SELECT 1` before the profile is saved:

```bash
bash <skill-path>/scripts/postgresql_crud.sh configure \
  --profile local \
  --mode direct \
  --url "postgresql://user:password@127.0.0.1:5432/app_db?sslmode=require" \
  --test-connection
```

A simple PostgreSQL URL is also supported:

```bash
bash <skill-path>/scripts/postgresql_crud.sh configure \
  --profile local \
  --mode direct \
  --url "postgresql://user:password@127.0.0.1:5432/app_db" \
  --default
```

### SSH Remote

Use this when the agent should SSH to a server and run the remote `psql` client there. This is best when the server has access to a private database and the database URL is already present in a remote `.env` file.

```bash
bash <skill-path>/scripts/postgresql_crud.sh configure \
  --profile prod \
  --mode ssh-remote \
  --ssh-alias app-prod \
  --remote-cwd /server/app \
  --env-file .env \
  --env-key DATABASE_URL \
  --readonly \
  --default
```

The script SSHes to the server, optionally runs `cd <remote_cwd>`, reads `DATABASE_URL` from the remote `.env`, parses it on the remote host with Bash, runs `psql -A -F <tab>`, and converts the tabular output to JSON locally. It must not display the full `DATABASE_URL`.

Use `--remote-cwd` when the `.env` file exists only inside an application directory after SSH login. `--env-file` may be either relative to `--remote-cwd`, such as `.env`, or an absolute path.

### SSH Tunnel

Use this when the local script should open an SSH tunnel to a private PostgreSQL host, then connect locally with the `psql` CLI.

```bash
bash <skill-path>/scripts/postgresql_crud.sh configure \
  --profile staging \
  --mode ssh-tunnel \
  --ssh-host staging.example.com \
  --ssh-user ubuntu \
  --ssh-key ~/.ssh/staging.pem \
  --pg-host 10.0.1.20 \
  --pg-port 5432 \
  --pg-database app_staging \
  --pg-user app_user \
  --prompt-pg-password
```

### List Profiles

```bash
bash <skill-path>/scripts/postgresql_crud.sh list-profiles
```

The output is redacted.

### Remove Profile

```bash
bash <skill-path>/scripts/postgresql_crud.sh remove-profile --profile staging
```

## Inspect Schema

List tables for the selected profile:

```bash
bash <skill-path>/scripts/postgresql_crud.sh schema --profile prod
```

List tables across all non-system schemas:

```bash
bash <skill-path>/scripts/postgresql_crud.sh schema \
  --profile prod \
  --all-schemas
```

List columns for one table:

```bash
bash <skill-path>/scripts/postgresql_crud.sh schema \
  --profile prod \
  --table users
```

If no schema is included in `--table`, the script assumes `public`.

Use another schema explicitly:

```bash
bash <skill-path>/scripts/postgresql_crud.sh schema \
  --profile prod \
  --schema auth \
  --table users
```

## Query Rows

```bash
bash <skill-path>/scripts/postgresql_crud.sh select \
  --profile prod \
  --table users \
  --where "email = :email" \
  --param email=test@example.com \
  --limit 20
```

This queries `public.users` by default. To query another schema, pass `--schema`:

```bash
bash <skill-path>/scripts/postgresql_crud.sh select \
  --profile prod \
  --schema auth \
  --table users \
  --where "email = :email" \
  --param email=test@example.com
```

If `--profile` is omitted, the script uses the saved default profile.

Optional fields:

- `--columns "id,email,created_at"`
- `--order-by created_at`
- `--desc`
- `--limit 50`

## Insert Rows

Dry-run first:

```bash
bash <skill-path>/scripts/postgresql_crud.sh insert \
  --profile staging \
  --table users \
  --value email=test@example.com \
  --value name=Test
```

Execute only after explicit confirmation:

```bash
bash <skill-path>/scripts/postgresql_crud.sh insert \
  --profile staging \
  --table users \
  --value email=test@example.com \
  --value name=Test \
  --execute
```

## Update Rows

Dry-run first:

```bash
bash <skill-path>/scripts/postgresql_crud.sh update \
  --profile staging \
  --table orders \
  --set status=paid \
  --where "id = :id" \
  --param id=123
```

The dry-run previews matching rows and returns the SQL that would run.

Execute only after explicit confirmation:

```bash
bash <skill-path>/scripts/postgresql_crud.sh update \
  --profile staging \
  --table orders \
  --set status=paid \
  --where "id = :id" \
  --param id=123 \
  --execute
```

## Delete Rows

Dry-run first:

```bash
bash <skill-path>/scripts/postgresql_crud.sh delete \
  --profile staging \
  --table sessions \
  --where "expires_at < :cutoff" \
  --param cutoff=2026-01-01
```

Execute only after explicit confirmation:

```bash
bash <skill-path>/scripts/postgresql_crud.sh delete \
  --profile staging \
  --table sessions \
  --where "expires_at < :cutoff" \
  --param cutoff=2026-01-01 \
  --execute
```

## Raw SQL

Use raw SQL for read-only statements when the structured commands are too limited:

```bash
bash <skill-path>/scripts/postgresql_crud.sh raw-sql \
  --profile prod \
  --sql "SELECT COUNT(*) AS count FROM public.users"
```

Raw write SQL requires both `--execute` and `--allow-raw-write`:

```bash
bash <skill-path>/scripts/postgresql_crud.sh raw-sql \
  --profile staging \
  --sql "UPDATE public.orders SET status = 'paid' WHERE id = 123" \
  --execute \
  --allow-raw-write
```

Prefer structured `insert`, `update`, and `delete` over raw write SQL.

## Response Shape

Success responses include:

- `profile`
- `mode`
- `operation`
- `rows` and `row_count` for queries
- `dry_run` for write previews
- `sql` for transparency

Structured queries and previews use PostgreSQL JSON generation internally, so text or JSON columns containing tabs or newlines remain valid JSON in the script output.

## Notes

- Use `ssh-remote` for production-style access where the server already knows `DATABASE_URL`.
- For `ssh-remote`, set `--remote-cwd` when the `.env` lives inside a project directory on the remote server.
- Use `ssh-tunnel` when the database is private but CRUD should run through the local `psql` CLI.
- Use `direct` for local or directly reachable PostgreSQL.
- CRUD commands use `--schema public` by default when `--table` is not schema-qualified.
- Pass `--schema <name>` to target a non-public schema, or pass a schema-qualified table such as `auth.users`.
- Use `schema --all-schemas` when the user wants database-wide table discovery.
- Use `configure --test-connection` when the user wants to confirm saved connection details before relying on a profile.
- For `--url`, the script passes the full URL to `psql`, so query parameters such as `sslmode=require` are preserved.
- `ssh-remote` parses the remote `.env` with remote Bash; if remote `bash`, `psql`, or `remote_cwd` is missing/invalid, stop and report that dependency or path issue.
- `ssh-tunnel` launches `ssh -N -L` and terminates the tunnel when the command finishes.
- Identifier names are restricted to simple PostgreSQL identifiers and optional `schema.table` form.
- The script is intentionally not a migration tool. Do not use it for schema changes unless the user explicitly asks for raw SQL and approves the risk.

## Example Prompts

### Chinese

- "配置一个 PostgreSQL profile，名字叫 prod，通过 ssh alias app-prod 到服务器，进入 /server/app 后读取 .env 的 DATABASE_URL，只读。"
- "查一下 prod 的 users 表结构，默认 public schema。"
- "用默认 PostgreSQL 数据库查 public.users 表里 email 是 test@example.com 的记录。"
- "查 auth schema 下 users 表里 email 是 test@example.com 的记录。"
- "把 staging 的 orders 表 id=123 的 status 改成 paid，先 dry-run。"

### English

- "Configure a readonly PostgreSQL profile through SSH using the remote DATABASE_URL."
- "Inspect the schema for the public.users table."
- "Query users by email using the default profile."
- "Preview an update to an order status before executing it."