Claude Skill

postgres

Execute read-only SQL queries against multiple PostgreSQL databases. Use when: (1) querying PostgreSQL databases, (2) exploring database schemas/tables, (3) running SELECT queries for data analysis, (4) checking database contents. Supports multiple database connections with descr

LLM Mart · 0 points · 2 views 128 listing impressions 0 install-command copies
Download sanjay3290-ai-skills-skills_postgres-c842558.zip · 6 KB

Install

skills CLI npx skills add https://github.com/sanjay3290/ai-skills/tree/main/skills/postgres
Claude Code claude plugin marketplace add https://llmmart.ai/marketplace.json && claude plugin install sanjay3290-ai-skills@llmmart
Git git clone https://github.com/sanjay3290/ai-skills.git

The skills CLI installs just this skill, for any of its supported agents. Claude Code installs the whole sanjay3290/ai-skills collection as a plugin from our marketplace. Git is the plain clone.

README

postgres

Read-only PostgreSQL query skill. Query multiple databases safely with write protection.

Setup

  1. Copy the example config:
cp connections.example.json connections.json
  1. Add your database credentials:
{
  "databases": [
    {
      "name": "prod",
      "description": "Production - users, orders, transactions",
      "host": "db.example.com",
      "port": 5432,
      "database": "app_prod",
      "user": "readonly",
      "password": "secret",
      "sslmode": "require"
    }
  ]
}
  1. Secure the config:
chmod 600 connections.json

Usage

# List configured databases
python3 scripts/query.py --list

# List tables
python3 scripts/query.py --db prod --tables

# Show schema
python3 scripts/query.py --db prod --schema

# Run query
python3 scripts/query.py --db prod --query "SELECT * FROM users" --limit 100

Config Fields

Field Required Default Description
name Yes - Database identifier
description Yes - What data it contains (for auto-selection)
host Yes - Hostname
port No 5432 Port
database Yes - Database name
user Yes - Username
password Yes - Password
sslmode No prefer disable, allow, prefer, require, verify-ca, verify-full

Safety Features

  • Read-only sessions: PostgreSQL readonly=True mode blocks writes at database level
  • Query validation: Only SELECT, SHOW, EXPLAIN, WITH allowed
  • Single statement: No multi-statement queries (prevents SELECT 1; DROP TABLE)
  • Timeouts: 30s query timeout, 10s connection timeout
  • Memory cap: Max 10,000 rows per query
  • Credential protection: Passwords sanitized from error messages

Requirements

pip install psycopg2-binary

Skill manifest

PostgreSQL Read-Only Query Skill

Execute safe, read-only queries against configured PostgreSQL databases.

Requirements

  • Python 3.8+
  • psycopg2-binary: pip install -r requirements.txt

Setup

Create connections.json in the skill directory or ~/.config/claude/postgres-connections.json.

Security: Set file permissions to 600 since it contains credentials:

chmod 600 connections.json
{
  "databases": [
    {
      "name": "production",
      "description": "Main app database - users, orders, transactions",
      "host": "db.example.com",
      "port": 5432,
      "database": "app_prod",
      "user": "readonly_user",
      "password": "your-password",
      "sslmode": "require"
    }
  ]
}

Config Fields

Field Required Description
name Yes Identifier for the database (case-insensitive)
description Yes What data this database contains (used for auto-selection)
host Yes Database hostname
port No Port number (default: 5432)
database Yes Database name
user Yes Username
password Yes Password
sslmode No SSL mode: disable, allow, prefer (default), require, verify-ca, verify-full

Usage

List configured databases

python3 scripts/query.py --list

Query a database

python3 scripts/query.py --db production --query "SELECT * FROM users LIMIT 10"

List tables

python3 scripts/query.py --db production --tables

Show schema

python3 scripts/query.py --db production --schema

Limit results

python3 scripts/query.py --db production --query "SELECT * FROM orders" --limit 100

Database Selection

Match user intent to database description:

User asks about Look for description containing
users, accounts users, accounts, customers
orders, sales orders, transactions, sales
analytics, metrics analytics, metrics, reports
logs, events logs, events, audit

If unclear, run --list and ask user which database.

Safety Features

  • Read-only session: Connection uses PostgreSQL readonly=True mode (primary protection)
  • Query validation: Only SELECT, SHOW, EXPLAIN, WITH queries allowed
  • Single statement: Multiple statements per query rejected
  • SSL support: Configurable SSL mode for encrypted connections
  • Query timeout: 30-second statement timeout enforced
  • Memory protection: Max 10,000 rows per query to prevent OOM
  • Column width cap: 100 char max per column for readable output
  • Credential sanitization: Error messages don't leak passwords

Troubleshooting

Error Solution
Config not found Create connections.json in skill directory
Authentication failed Check username/password in config
Connection timeout Verify host/port, check firewall/VPN
SSL error Try "sslmode": "disable" for local databases
Permission warning Run chmod 600 connections.json

Exit Codes

  • 0: Success
  • 1: Error (config missing, auth failed, invalid query, database error)

Workflow

  1. Run --list to show available databases
  2. Match user intent to database description
  3. Run --tables or --schema to explore structure
  4. Execute query with appropriate LIMIT
Files (ai-skills)
  • scripts
    • query.py 9.2 KB
      Loading…
  • .gitignore 282 B · in bundle
  • connections.example.json 931 B
    Loading…
  • README.md 1.8 KB
    # postgres
    
    Read-only PostgreSQL query skill. Query multiple databases safely with write protection.
    
    ## Setup
    
    1. Copy the example config:
    ```bash
    cp connections.example.json connections.json
    ```
    
    2. Add your database credentials:
    ```json
    {
      "databases": [
        {
          "name": "prod",
          "description": "Production - users, orders, transactions",
          "host": "db.example.com",
          "port": 5432,
          "database": "app_prod",
          "user": "readonly",
          "password": "secret",
          "sslmode": "require"
        }
      ]
    }
    ```
    
    3. Secure the config:
    ```bash
    chmod 600 connections.json
    ```
    
    ## Usage
    
    ```bash
    # List configured databases
    python3 scripts/query.py --list
    
    # List tables
    python3 scripts/query.py --db prod --tables
    
    # Show schema
    python3 scripts/query.py --db prod --schema
    
    # Run query
    python3 scripts/query.py --db prod --query "SELECT * FROM users" --limit 100
    ```
    
    ## Config Fields
    
    | Field | Required | Default | Description |
    |-------|----------|---------|-------------|
    | name | Yes | - | Database identifier |
    | description | Yes | - | What data it contains (for auto-selection) |
    | host | Yes | - | Hostname |
    | port | No | 5432 | Port |
    | database | Yes | - | Database name |
    | user | Yes | - | Username |
    | password | Yes | - | Password |
    | sslmode | No | prefer | disable, allow, prefer, require, verify-ca, verify-full |
    
    ## Safety Features
    
    - **Read-only sessions**: PostgreSQL `readonly=True` mode blocks writes at database level
    - **Query validation**: Only SELECT, SHOW, EXPLAIN, WITH allowed
    - **Single statement**: No multi-statement queries (prevents `SELECT 1; DROP TABLE`)
    - **Timeouts**: 30s query timeout, 10s connection timeout
    - **Memory cap**: Max 10,000 rows per query
    - **Credential protection**: Passwords sanitized from error messages
    
    ## Requirements
    
    ```bash
    pip install psycopg2-binary
    ```
    
  • requirements.txt 26 B
    Loading…
  • SKILL.md 3.8 KB
    ---
    name: postgres
    description: "Execute read-only SQL queries against multiple PostgreSQL databases. Use when: (1) querying PostgreSQL databases, (2) exploring database schemas/tables, (3) running SELECT queries for data analysis, (4) checking database contents. Supports multiple database connections with descriptions for intelligent auto-selection. Blocks all write operations (INSERT, UPDATE, DELETE, DROP, etc.) for safety."
    license: Apache-2.0
    metadata:
      author: sanjay3290
      version: "1.0"
    ---
    
    # PostgreSQL Read-Only Query Skill
    
    Execute safe, read-only queries against configured PostgreSQL databases.
    
    ## Requirements
    
    - Python 3.8+
    - psycopg2-binary: `pip install -r requirements.txt`
    
    ## Setup
    
    Create `connections.json` in the skill directory or `~/.config/claude/postgres-connections.json`.
    
    **Security**: Set file permissions to `600` since it contains credentials:
    ```bash
    chmod 600 connections.json
    ```
    
    ```json
    {
      "databases": [
        {
          "name": "production",
          "description": "Main app database - users, orders, transactions",
          "host": "db.example.com",
          "port": 5432,
          "database": "app_prod",
          "user": "readonly_user",
          "password": "your-password",
          "sslmode": "require"
        }
      ]
    }
    ```
    
    ### Config Fields
    
    | Field | Required | Description |
    |-------|----------|-------------|
    | name | Yes | Identifier for the database (case-insensitive) |
    | description | Yes | What data this database contains (used for auto-selection) |
    | host | Yes | Database hostname |
    | port | No | Port number (default: 5432) |
    | database | Yes | Database name |
    | user | Yes | Username |
    | password | Yes | Password |
    | sslmode | No | SSL mode: disable, allow, prefer (default), require, verify-ca, verify-full |
    
    ## Usage
    
    ### List configured databases
    ```bash
    python3 scripts/query.py --list
    ```
    
    ### Query a database
    ```bash
    python3 scripts/query.py --db production --query "SELECT * FROM users LIMIT 10"
    ```
    
    ### List tables
    ```bash
    python3 scripts/query.py --db production --tables
    ```
    
    ### Show schema
    ```bash
    python3 scripts/query.py --db production --schema
    ```
    
    ### Limit results
    ```bash
    python3 scripts/query.py --db production --query "SELECT * FROM orders" --limit 100
    ```
    
    ## Database Selection
    
    Match user intent to database `description`:
    
    | User asks about | Look for description containing |
    |-----------------|--------------------------------|
    | users, accounts | users, accounts, customers |
    | orders, sales | orders, transactions, sales |
    | analytics, metrics | analytics, metrics, reports |
    | logs, events | logs, events, audit |
    
    If unclear, run `--list` and ask user which database.
    
    ## Safety Features
    
    - **Read-only session**: Connection uses PostgreSQL `readonly=True` mode (primary protection)
    - **Query validation**: Only SELECT, SHOW, EXPLAIN, WITH queries allowed
    - **Single statement**: Multiple statements per query rejected
    - **SSL support**: Configurable SSL mode for encrypted connections
    - **Query timeout**: 30-second statement timeout enforced
    - **Memory protection**: Max 10,000 rows per query to prevent OOM
    - **Column width cap**: 100 char max per column for readable output
    - **Credential sanitization**: Error messages don't leak passwords
    
    ## Troubleshooting
    
    | Error | Solution |
    |-------|----------|
    | Config not found | Create `connections.json` in skill directory |
    | Authentication failed | Check username/password in config |
    | Connection timeout | Verify host/port, check firewall/VPN |
    | SSL error | Try `"sslmode": "disable"` for local databases |
    | Permission warning | Run `chmod 600 connections.json` |
    
    ## Exit Codes
    
    - **0**: Success
    - **1**: Error (config missing, auth failed, invalid query, database error)
    
    ## Workflow
    
    1. Run `--list` to show available databases
    2. Match user intent to database description
    3. Run `--tables` or `--schema` to explore structure
    4. Execute query with appropriate LIMIT
    

Comments (0)

Sign in to join the conversation.

No comments yet.

Reviews (0)

No reviews yet.