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:

~/.config/postgresql-crud/profiles/

The default profile name is saved at:

~/.config/postgresql-crud/default_profile

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

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 <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 <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 <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 <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 <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 <skill-path>/scripts/postgresql_crud.sh list-profiles

The output is redacted.

Remove Profile

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

Inspect Schema

List tables for the selected profile:

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

List tables across all non-system schemas:

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

List columns for one table:

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 <skill-path>/scripts/postgresql_crud.sh schema \
  --profile prod \
  --schema auth \
  --table users

Query Rows

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 <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 <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 <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 <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 <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 <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 <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 <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 <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."