Skip to content
Back to search

pgmcp

An MCP server to query any Postgres database in natural language.

Safety scan

A static text scan, not a guarantee. Select the badge to see what was scanned and every finding.

Checked today
Badge for your README
findsafeskills safety rating

Markdown

[![findsafeskills safety rating](/api/listings/subnetmarco--pgmcp/badge.svg)](/listings/subnetmarco--pgmcp)

HTML

<a href="/listings/subnetmarco--pgmcp"><img src="/api/listings/subnetmarco--pgmcp/badge.svg" alt="findsafeskills safety rating" /></a>

Install

i

Install instructions from the README

As written by the repo's author. findsafeskills has not verified these steps.

PGMCP connects to **your existing PostgreSQL database** and makes it accessible to AI assistants through natural language queries.

Prerequisites

- PostgreSQL database (existing database with your schema) - OpenAI API key (optional, for AI-powered SQL generation)

Basic Usage

# Set up environment variables
export DATABASE_URL="postgres://user:password@localhost:5432/your-existing-db"
export OPENAI_API_KEY="your-api-key"  # Optional

# Run server (using pre-compiled binary)
./pgmcp-server

# Test with client in another terminal
./pgmcp-client -ask "What tables do I have?" -format table
./pgmcp-client -ask "Who is the customer that has placed the most orders?" -format table
./pgmcp-client -search "john" -format table

Here is how it works:

πŸ‘€ User / AI Assistant
         β”‚
         β”‚ "Who are the top customers?"
         β–Ό
β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚                    Any MCP Client                           β”‚
β”‚                                                             β”‚
β”‚  PGMCP CLI  β”‚  Cursor  β”‚  Claude Desktop  β”‚  VS Code  β”‚ ... β”‚
β”‚  JSON/CSV   β”‚  Chat    β”‚  AI Assistant    β”‚  Editor   β”‚     β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
         β”‚
         β”‚ Streamable HTTP / MCP Protocol
         β–Ό
β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚                    PGMCP Server                             β”‚
β”‚                                                             β”‚
β”‚  πŸ”’ Security    🧠 AI Engine      🌊 Streaming              β”‚
β”‚  β€’ Input Valid  β€’ Schema Cache    β€’ Auto-Pagination         β”‚
β”‚  β€’ Audit Log    β€’ OpenAI API      β€’ Memory Management       β”‚
β”‚  β€’ SQL Guard    β€’ Error Recovery  β€’ Connection Pool         β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
         β”‚
         β”‚ Read-Only SQL Queries
         β–Ό
β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚                Your PostgreSQL Database                     β”‚
β”‚                                                             β”‚
β”‚  Any Schema: E-commerce, Analytics, CRM, etc.               β”‚
β”‚  Tables β€’ Views β€’ Indexes β€’ Functions                       β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜

External AI Services:
OpenAI API β€’ Anthropic β€’ Local LLMs (Ollama, etc.)

Key Benefits:
βœ… Works with ANY PostgreSQL database (no assumptions about schema)
βœ… No schema modifications required  
βœ… Read-only access (100% safe)
βœ… Automatic streaming for large results
βœ… Intelligent query understanding (singular vs plural)
βœ… Robust error handling (graceful AI failure recovery)
βœ… PostgreSQL case sensitivity support (mixed-case tables)
βœ… Production-ready security and performance
βœ… Universal database compatibility
βœ… Multiple output formats (table, JSON, CSV)
βœ… Free-text search across all columns
βœ… Authentication support
βœ… Comprehensive testing suite

What was scanned

The files the safety scan read, as written by the repository's author and shown as plain text. Lines with a finding are marked.

README.md Β· README Β· 307 lines

View this file on GitHub (read at the repository's current version: the scan did not record which commit it read)

  1. [![ci](https://github.com/subnetmarco/pgmcp/actions/workflows/ci.yml/badge.svg)](https://github.com/subnetmarco/pgmcp/actions/workflows/ci.yml)
  2. [![Go Report Card](https://goreportcard.com/badge/github.com/subnetmarco/pgmcp)](https://goreportcard.com/report/github.com/subnetmarco/pgmcp)
  3. [![License](https://img.shields.io/badge/License-Apache%202.0-blue.svg)](https://opensource.org/licenses/Apache-2.0)
  4. # PGMCP - PostgreSQL Model Context Protocol Server
  5. PGMCP connects AI assistants to **any PostgreSQL database** through natural language queries. Ask questions in plain English and get structured SQL results with automatic streaming and robust error handling.
  6. **Works with**: Cursor, Claude Desktop, VS Code extensions, and any [MCP-compatible client](https://modelcontextprotocol.io/)
  7. ## Quick Start
  8. PGMCP connects to **your existing PostgreSQL database** and makes it accessible to AI assistants through natural language queries.
  9. ### Prerequisites
  10. - PostgreSQL database (existing database with your schema)
  11. - OpenAI API key (optional, for AI-powered SQL generation)
  12. ### Basic Usage
  13. ```bash
  14. # Set up environment variables
  15. export DATABASE_URL="postgres://user:password@localhost:5432/your-existing-db"
  16. export OPENAI_API_KEY="your-api-key" # Optional
  17. # Run server (using pre-compiled binary)
  18. ./pgmcp-server
  19. # Test with client in another terminal
  20. ./pgmcp-client -ask "What tables do I have?" -format table
  21. ./pgmcp-client -ask "Who is the customer that has placed the most orders?" -format table
  22. ./pgmcp-client -search "john" -format table
  23. ```
  24. Here is how it works:
  25. ```
  26. πŸ‘€ User / AI Assistant
  27. β”‚
  28. β”‚ "Who are the top customers?"
  29. β–Ό
  30. β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  31. β”‚ Any MCP Client β”‚
  32. β”‚ β”‚
  33. β”‚ PGMCP CLI β”‚ Cursor β”‚ Claude Desktop β”‚ VS Code β”‚ ... β”‚
  34. β”‚ JSON/CSV β”‚ Chat β”‚ AI Assistant β”‚ Editor β”‚ β”‚
  35. β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
  36. β”‚
  37. β”‚ Streamable HTTP / MCP Protocol
  38. β–Ό
  39. β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  40. β”‚ PGMCP Server β”‚
  41. β”‚ β”‚
  42. β”‚ πŸ”’ Security 🧠 AI Engine 🌊 Streaming β”‚
  43. β”‚ β€’ Input Valid β€’ Schema Cache β€’ Auto-Pagination β”‚
  44. β”‚ β€’ Audit Log β€’ OpenAI API β€’ Memory Management β”‚
  45. β”‚ β€’ SQL Guard β€’ Error Recovery β€’ Connection Pool β”‚
  46. β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
  47. β”‚
  48. β”‚ Read-Only SQL Queries
  49. β–Ό
  50. β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”
  51. β”‚ Your PostgreSQL Database β”‚
  52. β”‚ β”‚
  53. β”‚ Any Schema: E-commerce, Analytics, CRM, etc. β”‚
  54. β”‚ Tables β€’ Views β€’ Indexes β€’ Functions β”‚
  55. β””β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”€β”˜
  56. External AI Services:
  57. OpenAI API β€’ Anthropic β€’ Local LLMs (Ollama, etc.)
  58. Key Benefits:
  59. βœ… Works with ANY PostgreSQL database (no assumptions about schema)
  60. βœ… No schema modifications required
  61. βœ… Read-only access (100% safe)
  62. βœ… Automatic streaming for large results
  63. βœ… Intelligent query understanding (singular vs plural)
  64. βœ… Robust error handling (graceful AI failure recovery)
  65. βœ… PostgreSQL case sensitivity support (mixed-case tables)
  66. βœ… Production-ready security and performance
  67. βœ… Universal database compatibility
  68. βœ… Multiple output formats (table, JSON, CSV)
  69. βœ… Free-text search across all columns
  70. βœ… Authentication support
  71. βœ… Comprehensive testing suite
  72. ```
  73. ## Features
  74. - **Natural Language to SQL**: Ask questions in plain English
  75. - **Automatic Streaming**: Handles large result sets automatically
  76. - **Safe Read-Only Access**: Prevents any write operations
  77. - **Text Search**: Search across all text columns
  78. - **Multiple Output Formats**: Table, JSON, and CSV
  79. - **PostgreSQL Case Sensitivity**: Handles mixed-case table names correctly
  80. - **Universal Compatibility**: Works with any PostgreSQL database
  81. ### Environment Variables
  82. **Required:**
  83. - `DATABASE_URL`: PostgreSQL connection string to your existing database
  84. **Optional:**
  85. - `OPENAI_API_KEY`: OpenAI API key for AI-powered SQL generation
  86. - `OPENAI_MODEL`: Model to use (default: "gpt-4o-mini")
  87. - `HTTP_ADDR`: Server address (default: ":8080")
  88. - `HTTP_PATH`: MCP endpoint path (default: "/mcp")
  89. - `AUTH_BEARER`: Bearer token for authentication
  90. ## Installation
  91. ### Download Pre-compiled Binaries
  92. 1. Go to [GitHub Releases](https://github.com/subnetmarco/pgmcp/releases)
  93. 2. Download the binary for your platform (Linux, macOS, Windows)
  94. 3. Extract and run:
  95. ```bash
  96. # Example for macOS/Linux
  97. tar xzf pgmcp_*.tar.gz
  98. cd pgmcp_*
  99. ./pgmcp-server
  100. ```
  101. ### Alternative Options
  102. ```bash
  103. # Homebrew (macOS/Linux) - Available after first release
  104. brew tap subnetmarco/homebrew-tap
  105. brew install pgmcp
  106. # Build from source
  107. go build -o pgmcp-server ./server
  108. go build -o pgmcp-client ./client
  109. ```
  110. Add `-ldflags="-s -w -extldflags=-static" -trimpath` if you want to get stripped executables (no debug info):
  111. ```console
  112. go build -ldflags="-s -w -extldflags=-static" -trimpath -o pgmcp-server ./server
  113. go build -ldflags="-s -w -extldflags=-static" -trimpath -o pgmcp-client ./client
  114. ```
  115. ### Docker/Kubernetes
  116. ```bash
  117. # Docker
  118. docker run -e DATABASE_URL="postgres://user:pass@host:5432/db" \
  119. -p 8080:8080 ghcr.io/subnetmarco/pgmcp:latest
  120. # Kubernetes (see examples/ directory for full manifests)
  121. kubectl create secret generic pgmcp-secret \
  122. --from-literal=database-url="postgres://user:pass@host:5432/db"
  123. kubectl apply -f examples/k8s/
  124. ```
  125. #### Quick Start
  126. ```bash
  127. # Set up database (optional - works with any existing PostgreSQL database)
  128. export DATABASE_URL="postgres://user:password@localhost:5432/mydb"
  129. psql $DATABASE_URL < schema.sql
  130. # Run server
  131. export OPENAI_API_KEY="your-api-key"
  132. ./pgmcp-server
  133. # Test with client
  134. ./pgmcp-client -ask "Who is the user that places the most orders?" -format table
  135. ./pgmcp-client -ask "Show me the top 40 most reviewed items in the marketplace" -format table
  136. ```
  137. ### Environment Variables
  138. **Required:**
  139. - `DATABASE_URL`: PostgreSQL connection string
  140. **Optional:**
  141. - `OPENAI_API_KEY`: OpenAI API key for SQL generation
  142. - `OPENAI_MODEL`: Model to use (default: "gpt-4o-mini")
  143. - `HTTP_ADDR`: Server address (default: ":8080")
  144. - `HTTP_PATH`: MCP endpoint path (default: "/mcp")
  145. - `AUTH_BEARER`: Bearer token for authentication
  146. ## Usage Examples
  147. ```bash
  148. # Ask questions in natural language
  149. ./pgmcp-client -ask "What are the top 5 customers?" -format table
  150. ./pgmcp-client -ask "How many orders were placed today?" -format json
  151. # Search across all text fields
  152. ./pgmcp-client -search "john" -format table
  153. # Multiple questions at once
  154. ./pgmcp-client -ask "Show tables" -ask "Count users" -format table
  155. # Different output formats
  156. ./pgmcp-client -ask "Export all data" -format csv -max-rows 1000
  157. ```
  158. ## Example Database
  159. The project includes two schemas:
  160. - **`schema.sql`**: Full Amazon-like marketplace with 5,000+ records
  161. - **`schema_minimal.sql`**: Minimal test schema with mixed-case `"Categories"` table
  162. **Key features:**
  163. - **Mixed-case table names** (`"Categories"`) for testing case sensitivity
  164. - **Composite primary keys** (`order_items`) for testing AI assumptions
  165. - **Realistic relationships** and data types
  166. Use your own database:
  167. ```bash
  168. export DATABASE_URL="postgres://user:pass@host:5432/your_db"
  169. ./pgmcp-server
  170. ./pgmcp-client -ask "What tables do I have?"
  171. ```
  172. ## AI Error Handling
  173. When AI generates incorrect SQL, PGMCP handles it gracefully:
  174. ```json
  175. {
  176. "error": "Column not found in generated query",
  177. "suggestion": "Try rephrasing your question or ask about specific tables",
  178. "original_sql": "SELECT non_existent_column FROM table..."
  179. }
  180. ```
  181. Instead of crashing, the system provides helpful feedback and continues operating.
  182. ## MCP Integration
  183. ### Cursor Integration
  184. ```bash
  185. # Start server
  186. export DATABASE_URL="postgres://user:pass@localhost:5432/your_db"
  187. ./pgmcp-server
  188. ```
  189. Add to Cursor settings:
  190. ```json
  191. {
  192. "mcp.servers": {
  193. "pgmcp": {
  194. "transport": {
  195. "type": "http",
  196. "url": "http://localhost:8080/mcp"
  197. }
  198. }
  199. }
  200. }
  201. ```
  202. ### Claude Desktop Integration
  203. Edit `~/.config/claude-desktop/claude_desktop_config.json`:
  204. ```json
  205. {
  206. "mcpServers": {
  207. "pgmcp": {
  208. "transport": {
  209. "type": "http",
  210. "url": "http://localhost:8080/mcp"
  211. }
  212. }
  213. }
  214. }
  215. ```
  216. ## API Tools
  217. - **`ask`**: Natural language questions β†’ SQL queries with automatic streaming
  218. - **`search`**: Free-text search across all database text columns
  219. - **`stream`**: Advanced streaming for very large result sets with pagination
  220. ## Safety Features
  221. - **Read-Only Enforcement**: Blocks write operations (INSERT, UPDATE, DELETE, etc.)
  222. - **Query Timeouts**: Prevents long-running queries
  223. - **Input Validation**: Sanitizes and validates all user input
  224. - **Transaction Isolation**: All queries run in read-only transactions
  225. ## Testing
  226. ```bash
  227. # Unit tests
  228. go test ./server -v
  229. # Integration tests (requires PostgreSQL)
  230. go test ./server -tags=integration -v
  231. ```
  232. ## License
  233. Apache 2.0 - See LICENSE file for details.
  234. ## Related Projects
  235. - [Model Context Protocol](https://modelcontextprotocol.io/) - The underlying protocol specification
  236. - [MCP Go SDK](https://github.com/modelcontextprotocol/go-sdk) - Go implementation of MCP
  237. ---
  238. PGMCP makes your PostgreSQL database accessible to AI assistants through natural language while maintaining security through read-only access controls.

Ratings and comments

Sign in to rate this skill.

Sign in

Free to use. If it’s helped, pass it on.