PostgreSQL Connection Assistant

Problem: read-only MCP is not enough

LLM tooling often treats PostgreSQL as a read-only query target, leaving out safe INSERT, UPDATE, DELETE, and DDL paths. When an agent needs to backfill data, repair rows, or migrate test tables, a read-only interface does not close the loop. Running writes directly also risks missing a WHERE clause, dropping the wrong table, or treating a production database like a test environment.

How it works

The skill provides a local CLI built with Python 3 and psycopg2-binary:
- pg_query.py: runs SELECT, WITH, and EXPLAIN, adding LIMIT 100 by default.
- pg_execute.py: runs writes with dry-run and ROLLBACK by default, committing only with explicit --confirm.
- pg_schema.py / pg_list.py: inspect columns, primary keys, indexes, foreign keys, and row estimates.
- pg_init.py: creates credentials.json, requiring the password file to live in a fixed user directory.

Connection details such as host, port, and database can be overridden via command-line arguments, avoiding ad hoc connection strings. High-risk operations such as DROP TABLE, TRUNCATE, UPDATE/DELETE without WHERE, updates affecting more than a threshold, and any production write must enter a human confirmation flow: dry-run first, pause for explicit approval, then add --confirm. Program-level safeguards include --force, --confirm-prod, Ctrl+C rollback, password masking, and explicit exit codes.

Boundaries

It fits controlled database work in local or trusted agent environments, not bypassing database permissions, audit controls, or release processes. The scripts reject mismatched SQL types and point to the correct entry point. Credentials must not be written into repos, scripts, or chat context; production changes should still follow DBA review and backup policy.