{"id":"77674448-5b06-4ea0-98b8-d0a023ca30fd","shortId":"sTXVbD","kind":"skill","title":"read-only-postgres","tagline":"Execute read-only SQL queries against PostgreSQL databases. Use when: (1) querying PostgreSQL data, (2) exploring schemas/tables, (3) running SELECT queries for analysis, (4) checking database contents. Supports multiple database connections with descriptions for auto-selection. ","description":"# PostgreSQL Read-Only Query Skill\n\nExecute safe, read-only queries against configured PostgreSQL databases.\n\n## Requirements\n\n- Python 3.8+\n- psycopg2-binary: `pip install -r requirements.txt`\n\n## Setup\n\nCreate `connections.json` in the skill directory or `~/.config/claude/read-only-postgres-connections.json`.\n\n**Security**: Set file permissions to `600` since it contains credentials:\n```bash\nchmod 600 connections.json\n```\n\n```json\n{\n  \"databases\": [\n    {\n      \"name\": \"app-db-dev\",\n      \"description\": \"Primary app database (public schema: users, organizations, orders, order_items, events)\",\n      \"host\": \"localhost\",\n      \"port\": 5432,\n      \"database\": \"app_dev\",\n      \"user\": \"app_user\",\n      \"password\": \"app_password\",\n      \"sslmode\": \"disable\"\n    },\n    {\n      \"name\": \"app-db-staging\",\n      \"description\": \"Staging database (same schema as primary app)\",\n      \"host\": \"localhost\",\n      \"port\": 5432,\n      \"database\": \"app_staging\",\n      \"user\": \"app_user\",\n      \"password\": \"app_password\",\n      \"sslmode\": \"disable\"\n    }\n  ]\n}\n```\n\n### Config Fields\n\n| Field | Required | Description |\n|-------|----------|-------------|\n| name | Yes | Identifier for the database (case-insensitive) |\n| description | Yes | What data this database contains (used for auto-selection) |\n| host | Yes | Database hostname |\n| port | No | Port number (default: 5432) |\n| database | Yes | Database name |\n| user | Yes | Username |\n| password | Yes | Password |\n| sslmode | No | SSL mode: disable, allow, prefer (default), require, verify-ca, verify-full |\n| pii_masking | No | Object mapping table names to arrays of column names to mask |\n\n### PII Masking\n\nMask sensitive data in query results by adding a `pii_masking` field to any database config. Middle characters are replaced with `*`, keeping only the first and last characters visible.\n\n```json\n{\n  \"name\": \"app-db-dev\",\n  \"host\": \"localhost\",\n  \"database\": \"app_dev\",\n  \"user\": \"readonly\",\n  \"password\": \"secret\",\n  \"pii_masking\": {\n    \"users\": [\"email\", \"phone\", \"first_name\", \"last_name\"],\n    \"orders\": [\"shipping_address\"]\n  }\n}\n```\n\n**How it works:**\n- `john@email.com` → `j************m`\n- `555-1234` → `5******4`\n- `Jo` → `Jo` (2 chars or fewer are not masked)\n\nMasking is applied automatically when querying a matching table. A footer note indicates which columns were masked.\n\n## Usage\n\n### List configured databases\n```bash\npython3 scripts/query.py --list\n```\n\n### Query a database\n```bash\npython3 scripts/query.py --db app-db-dev --query \"SELECT id, email, created_at FROM users LIMIT 10\"\n```\n\n### List tables\n```bash\npython3 scripts/query.py --db app-db-dev --tables\n```\n\n### Show schema\n```bash\npython3 scripts/query.py --db app-db-dev --schema\n```\n\n### Limit results\n```bash\npython3 scripts/query.py --db app-db-dev --query \"SELECT id, status, total_amount FROM orders\" --limit 100\n```\n\n## Database Selection\n\nMatch user intent to database `description`:\n\n| User asks about | Look for description containing |\n|-----------------|--------------------------------|\n| users, accounts | users, accounts |\n| organizations, teams | organizations, teams |\n| orders, payments | orders, payments |\n| events, audit logs | events, audit, logs |\n| analytics or reporting | analytics, reporting |\n| background jobs or queues | jobs, queue, outbox |\n\nIf unclear, run `--list` and ask user which database.\n\n## Safety Features\n\n- **Read-only session**: Connection uses PostgreSQL `readonly=True` mode (primary protection)\n- **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)\n- **Single statement**: Multiple statements per query rejected\n- **SSL support**: Configurable SSL mode for encrypted connections\n- **Query timeout**: 30-second statement timeout enforced\n- **Memory protection**: Max 10,000 rows per query to prevent OOM\n- **Column width cap**: 100 char max per column for readable output\n- **Credential sanitization**: Error messages don't leak passwords\n- **PII masking**: Configurable per-table column masking to protect sensitive data in query output\n\n## Troubleshooting\n\n| Error | Solution |\n|-------|----------|\n| Config not found | Create `connections.json` in skill directory |\n| Authentication failed | Check username/password in config |\n| Connection timeout | Verify host/port, check firewall/VPN |\n| SSL error | Try `\"sslmode\": \"disable\"` for local databases |\n| Permission warning | Run `chmod 600 connections.json` |\n\n## Exit Codes\n\n- **0**: Success\n- **1**: Error (config missing, auth failed, invalid query, database error)\n\n## Workflow\n\n1. Run `--list` to show available databases\n2. Match user intent to database description\n3. Run `--tables` or `--schema` to explore structure\n4. Execute query with appropriate LIMIT","tags":["read","only","postgres","agent","skills","jawwadfirdousi","agent-skills","ai-agents","ai-tools","automation","developer-tools","prompt-engineering"],"capabilities":["skill","source-jawwadfirdousi","skill-read-only-postgres","topic-agent","topic-agent-skills","topic-ai-agents","topic-ai-tools","topic-automation","topic-developer-tools","topic-prompt-engineering","topic-prompt-template","topic-skills","topic-workflow-automation"],"categories":["agent-skills"],"synonyms":[],"warnings":[],"endpointUrl":"https://skills.sh/jawwadfirdousi/agent-skills/read-only-postgres","protocol":"skill","transport":"skills-sh","auth":{"type":"none","details":{"cli":"npx skills add jawwadfirdousi/agent-skills","source_repo":"https://github.com/jawwadfirdousi/agent-skills","install_from":"skills.sh"}},"qualityScore":"0.455","qualityRationale":"deterministic score 0.46 from registry signals: · indexed on github topic:agent-skills · 10 github stars · SKILL.md body (4,852 chars)","verified":false,"liveness":"unknown","lastLivenessCheck":null,"agentReviews":{"count":0,"score_avg":null,"cost_usd_avg":null,"success_rate":null,"latency_p50_ms":null,"narrative_summary":null,"summary_updated_at":null},"enrichmentModel":"deterministic:skill-github:v1","enrichmentVersion":1,"enrichedAt":"2026-05-18T19:08:12.805Z","embedding":null,"createdAt":"2026-05-18T13:13:40.692Z","updatedAt":"2026-05-18T19:08:12.805Z","lastSeenAt":"2026-05-18T19:08:12.805Z","tsv":"'-1234':294 '/.config/claude/read-only-postgres-connections.json':77 '0':592 '000':512 '1':16,594,605 '10':351,511 '100':393,522 '2':20,299,612 '3':23,619 '3.8':61 '30':503 '4':29,296,627 '5':295 '5432':114,142,189 '555':293 '600':83,90,588 'account':410,412 'ad':238 'address':286 'allow':205,470 'amount':389 'analysi':28 'analyt':427,430 'app':96,101,116,119,122,128,138,144,147,150,263,269,339,359,370,381 'app-db-dev':95,262,338,358,369,380 'app-db-stag':127 'appli':308 'appropri':631 'array':223 'ask':403,444 'audit':422,425 'auth':598 'authent':564 'auto':41,178 'auto-select':40,177 'automat':309 'avail':610 'background':432 'bash':88,327,334,354,365,376 'binari':64 'block':485 'ca':211 'cap':521 'case':166 'case-insensit':165 'char':300,523 'charact':248,258 'check':30,566,574 'chmod':89,587 'code':591 'column':225,320,519,526,544 'comments/literals':471 'config':154,246,556,569,596 'configur':56,325,495,540 'connect':36,454,500,570 'connections.json':71,91,560,589 'contain':86,174,408 'content':32 'creat':70,346,559 'credenti':87,530 'ctes':478 'data':19,171,233,476,549 'data-modifi':475 'databas':13,31,35,58,93,102,115,133,143,164,173,182,190,192,245,268,326,333,394,400,447,583,602,611,617 'db':97,129,264,337,340,357,360,368,371,379,382 'ddl/dml':473 'default':188,207 'descript':38,99,131,158,168,401,407,618 'dev':98,117,265,270,341,361,372,383 'directori':75,563 'disabl':125,153,204,580 'email':278,345 'encrypt':499 'enforc':507 'error':532,554,577,595,603 'event':110,421,424 'execut':5,49,628 'exit':590 'explain':467 'explor':21,625 'fail':565,599 'featur':449 'fewer':302 'field':155,156,242 'file':80 'firewall/vpn':575 'first':255,280 'footer':316 'found':558 'full':214 'function':484 'host':111,139,180,266 'host/port':573 'hostnam':183 'id':344,386 'identifi':161 'indic':318 'insensit':167 'instal':66 'intent':398,615 'invalid':600 'item':109 'j':291 'jo':297,298 'job':433,436 'john@email.com':290 'json':92,260 'keep':252 'keyword':474 'last':257,282 'leak':536 'limit':350,374,392,632 'list':324,330,352,442,607 'local':582 'localhost':112,140,267 'log':423,426 'look':405 'm':292 'map':219 'mask':216,228,230,231,241,276,305,306,322,539,545 'match':313,396,613 'max':510,524 'memori':508 'messag':533 'middl':247 'miss':597 'mode':203,459,497 'modifi':477 'multipl':34,488 'mutat':483 'name':94,126,159,193,221,226,261,281,283 'note':317 'number':187 'object':218 'oom':518 'order':107,108,284,391,417,419 'organ':106,413,415 'outbox':438 'output':529,552 'password':121,123,149,151,197,199,273,537 'payment':418,420 'per':490,514,525,542 'per-tabl':541 'permiss':81,584 'phone':279 'pii':215,229,240,275,538 'pip':65 'port':113,141,184,186 'postgr':4 'postgresql':12,18,43,57,456 'prefer':206 'prevent':517 'primari':100,137,460 'protect':461,509,547 'psycopg2':63 'psycopg2-binary':62 'public':103 'python':60 'python3':328,335,355,366,377 'queri':10,17,26,47,54,235,311,331,342,384,462,469,491,501,515,551,601,629 'queue':435,437 'r':67 'read':2,7,45,52,451 'read-on':6,44,51,450 'read-only-postgr':1 'readabl':528 'readon':272,457 'reject':492 'replac':250 'report':429,431 'requir':59,157,208 'requirements.txt':68 'result':236,375 'row':513 'run':24,441,586,606,620 'safe':50 'safeti':448 'sanit':531 'schema':104,135,364,373,623 'schemas/tables':22 'scripts/query.py':329,336,356,367,378 'second':504 'secret':274 'secur':78 'select':25,42,179,343,385,395,465,479 'sensit':232,548 'sequenc':482 'session':453 'set':79 'setup':69 'ship':285 'show':363,466,609 'sinc':84 'singl':486 'skill':48,74,562 'skill-read-only-postgres' 'solut':555 'source-jawwadfirdousi' 'sql':9 'ssl':202,493,496,576 'sslmode':124,152,200,579 'stage':130,132,145 'statement':487,489,505 'status':387 'strip':472 'structur':626 'success':593 'support':33,494 'tabl':220,314,353,362,543,621 'team':414,416 'timeout':502,506,571 'topic-agent' 'topic-agent-skills' 'topic-ai-agents' 'topic-ai-tools' 'topic-automation' 'topic-developer-tools' 'topic-prompt-engineering' 'topic-prompt-template' 'topic-skills' 'topic-workflow-automation' 'total':388 'tri':578 'troubleshoot':553 'true':458 'unclear':440 'usag':323 'use':14,175,455 'user':105,118,120,146,148,194,271,277,349,397,402,409,411,445,614 'usernam':196 'username/password':567 'valid':463 'verifi':210,213,572 'verify-ca':209 'verify-ful':212 'visibl':259 'warn':585 'width':520 'work':289 'workflow':604 'yes':160,169,181,191,195,198","prices":[{"id":"36d54bd0-1370-4431-841d-267e06d32c78","listingId":"77674448-5b06-4ea0-98b8-d0a023ca30fd","amountUsd":"0","unit":"free","nativeCurrency":null,"nativeAmount":null,"chain":null,"payTo":null,"paymentMethod":"skill-free","isPrimary":true,"details":{"org":"jawwadfirdousi","category":"agent-skills","install_from":"skills.sh"},"createdAt":"2026-05-18T13:13:40.692Z"}],"sources":[{"listingId":"77674448-5b06-4ea0-98b8-d0a023ca30fd","source":"github","sourceId":"jawwadfirdousi/agent-skills/read-only-postgres","sourceUrl":"https://github.com/jawwadfirdousi/agent-skills/tree/main/skills/read-only-postgres","isPrimary":false,"firstSeenAt":"2026-05-18T13:13:40.692Z","lastSeenAt":"2026-05-18T19:08:12.805Z"}],"details":{"listingId":"77674448-5b06-4ea0-98b8-d0a023ca30fd","quickStartSnippet":null,"exampleRequest":null,"exampleResponse":null,"schema":null,"openapiUrl":null,"agentsTxtUrl":null,"citations":[],"useCases":[],"bestFor":[],"notFor":[],"kindDetails":{"org":"jawwadfirdousi","slug":"read-only-postgres","github":{"repo":"jawwadfirdousi/agent-skills","stars":10,"topics":["agent","agent-skills","ai-agents","ai-tools","automation","developer-tools","prompt-engineering","prompt-template","skills","workflow-automation"],"license":null,"html_url":"https://github.com/jawwadfirdousi/agent-skills","pushed_at":"2026-05-05T20:09:54Z","description":"Reusable AI agent skill definitions","skill_md_sha":"e2c59f23c530ff1940d2e28cddba891541a4d4e6","skill_md_path":"skills/read-only-postgres/SKILL.md","default_branch":"main","skill_tree_url":"https://github.com/jawwadfirdousi/agent-skills/tree/main/skills/read-only-postgres"},"layout":"multi","source":"github","category":"agent-skills","frontmatter":{"name":"read-only-postgres","description":"Execute read-only SQL queries against PostgreSQL databases. Use when: (1) querying PostgreSQL data, (2) exploring schemas/tables, (3) running SELECT queries for analysis, (4) checking database contents. Supports multiple database connections with descriptions for auto-selection. Blocks all write operations (INSERT, UPDATE, DELETE, DROP, etc.) for safety."},"skills_sh_url":"https://skills.sh/jawwadfirdousi/agent-skills/read-only-postgres"},"updatedAt":"2026-05-18T19:08:12.805Z"}}