read-only-postgres

Compare original and translation side by side

🇺🇸

Original

English

🇨🇳

Translation

Chinese

PostgreSQL Read-Only Query Skill

PostgreSQL只读查询Skill

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

针对已配置的PostgreSQL数据库执行安全的只读查询。

Requirements

环境要求

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

Python 3.8+
psycopg2-binary：执行
```
pip install -r requirements.txt
```
安装

Setup

配置步骤

Create

connections.json

in the skill directory or

~/.config/claude/read-only-postgres-connections.json

Security: Set file permissions to

since it contains credentials:

bash

chmod 600 connections.json

json

{
  "databases": [
    {
      "name": "app-db-dev",
      "description": "Primary app database (public schema: users, organizations, orders, order_items, events)",
      "host": "localhost",
      "port": 5432,
      "database": "app_dev",
      "user": "app_user",
      "password": "app_password",
      "sslmode": "disable"
    },
    {
      "name": "app-db-staging",
      "description": "Staging database (same schema as primary app)",
      "host": "localhost",
      "port": 5432,
      "database": "app_staging",
      "user": "app_user",
      "password": "app_password",
      "sslmode": "disable"
    }
  ]
}

在Skill目录或

~/.config/claude/read-only-postgres-connections.json

路径下创建

connections.json

文件。

安全提示：由于文件包含凭证信息，请将文件权限设置为

：

bash

chmod 600 connections.json

json

{
  "databases": [
    {
      "name": "app-db-dev",
      "description": "Primary app database (public schema: users, organizations, orders, order_items, events)",
      "host": "localhost",
      "port": 5432,
      "database": "app_dev",
      "user": "app_user",
      "password": "app_password",
      "sslmode": "disable"
    },
    {
      "name": "app-db-staging",
      "description": "Staging database (same schema as primary app)",
      "host": "localhost",
      "port": 5432,
      "database": "app_staging",
      "user": "app_user",
      "password": "app_password",
      "sslmode": "disable"
    }
  ]
}

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

字段	是否必填	描述
name	是	数据库标识（大小写不敏感）
description	是	数据库包含的数据内容（用于自动选择数据库）
host	是	数据库主机地址
port	否	端口号（默认值：5432）
database	是	数据库名称
user	是	用户名
password	是	密码
sslmode	否	SSL模式：disable、allow、prefer（默认）、require、verify-ca、verify-full

Usage

使用方法

List configured databases

列出已配置的数据库

bash

python3 scripts/query.py --list

bash

python3 scripts/query.py --list

Query a database

查询数据库

bash

python3 scripts/query.py --db app-db-dev --query "SELECT id, email, created_at FROM users LIMIT 10"

bash

python3 scripts/query.py --db app-db-dev --query "SELECT id, email, created_at FROM users LIMIT 10"

List tables

列出所有表

bash

python3 scripts/query.py --db app-db-dev --tables

bash

python3 scripts/query.py --db app-db-dev --tables

Show schema

查看数据库模式

bash

python3 scripts/query.py --db app-db-dev --schema

bash

python3 scripts/query.py --db app-db-dev --schema

Limit results

限制查询结果行数

bash

python3 scripts/query.py --db app-db-dev --query "SELECT id, status, total_amount FROM orders" --limit 100

bash

python3 scripts/query.py --db app-db-dev --query "SELECT id, status, total_amount FROM orders" --limit 100

Database Selection

数据库选择规则

Match user intent to database

description

User asks about	Look for description containing
users, accounts	users, accounts
organizations, teams	organizations, teams
orders, payments	orders, payments
events, audit logs	events, audit, logs
analytics or reporting	analytics, reporting
background jobs or queues	jobs, queue, outbox

If unclear, run

--list

and ask user which database.

根据用户需求匹配数据库的

description

字段：

用户查询内容	匹配包含以下关键词的数据库描述
用户、账户	users, accounts
组织、团队	organizations, teams
订单、支付	orders, payments
事件、审计日志	events, audit, logs
分析或报表	analytics, reporting
后台任务或队列	jobs, queue, outbox

如果无法明确匹配，请执行

--list

命令并询问用户选择哪个数据库。

Safety Features

安全特性

Read-only session: Connection uses PostgreSQL
```
readonly=True
```
mode (primary protection)
Query validation: Only SELECT, SHOW, EXPLAIN, WITH queries allowed (comments/literals stripped; DDL/DML keywords, data-modifying CTEs, SELECT INTO, and sequence mutation functions blocked)
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

只读会话：连接使用PostgreSQL的
```
readonly=True
```
模式（核心保护措施）
查询验证：仅允许SELECT、SHOW、EXPLAIN、WITH类型的查询（会去除注释/字面量；禁止DDL/DML关键字、修改数据的CTE、SELECT INTO以及序列突变函数）
单语句限制：拒绝包含多个语句的查询
SSL支持：可配置SSL模式以实现加密连接
查询超时：强制设置30秒的语句超时时间
内存保护：每个查询最多返回10000行结果，防止内存溢出
列宽度限制：每列内容最多显示100个字符，保证输出可读性
凭证脱敏：错误信息不会泄露密码

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`

错误	解决方案
未找到配置文件	在Skill目录下创建 `connections.json` 文件
认证失败	检查配置文件中的用户名/密码
连接超时	验证主机/端口是否正确，检查防火墙/VPN设置
SSL错误	针对本地数据库，尝试设置 `"sslmode": "disable"`
权限警告	执行 `chmod 600 connections.json` 设置正确权限

Exit Codes

退出码说明

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

0：执行成功
1：执行错误（配置缺失、认证失败、查询无效、数据库错误）

Workflow

操作流程

Run
```
--list
```
to show available databases
Match user intent to database description
Run
```
--tables
```
or
```
--schema
```
to explore structure
Execute query with appropriate LIMIT

执行
```
--list
```
命令查看可用数据库
根据用户需求匹配数据库描述
执行
```
--tables
```
或
```
--schema
```
命令探索数据库结构
执行查询并设置合适的LIMIT参数