pgEdge Postgres MCP Server and Natural Language Agent

1# pgEdge Postgres MCP Server and Natural Language Agent
2 
3[![CI - MCP Server](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-server.yml/badge.svg?branch=main)](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-server.yml?query=branch%3Amain)
4[![CI - CLI Client](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-cli-client.yml/badge.svg?branch=main)](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-cli-client.yml?query=branch%3Amain)
5[![CI - Web Client](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-web-client.yml/badge.svg?branch=main)](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-web-client.yml?query=branch%3Amain)
6[![CI - Docker](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-docker.yml/badge.svg?branch=main)](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-docker.yml?query=branch%3Amain)
7[![CI - Documentation](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-docs.yml/badge.svg?branch=main)](https://github.com/pgEdge/pgedge-postgres-mcp/actions/workflows/ci-docs.yml?query=branch%3Amain)
8 
9- About the pgEdge Postgres MCP Server
10    - [pgEdge Postgres MCP Server](docs/index.md)
11    - [Choosing the Right Solution](docs/guide/mcp-vs-rag.md)
12    - [Best Practices - Querying the Server](docs/guide/querying.md)
13- Installing the MCP Server
14    - [Quick Start](docs/guide/quickstart.md)
15    - [Quickstart Demo with Northwind](docs/guide/quickstart_demo.md)
16    - [Deploying on Docker](docs/guide/deploy_docker.md)
17    - [Deploying from Source](docs/guide/deploy_source.md)
18    - [Testing the MCP Server Deployment](docs/guide/test_server.md)
19- Configuring the MCP Server
20    - [Specifying Configuration Preferences](docs/guide/configuration.md)
21    - [Using Environment Variables to Specify Options](docs/guide/env_variable_config.md)
22    - [Including Provider Embeddings in a Configuration File](docs/guide/provider_config.md)
23    - [Configuring the Agent for Multiple Databases](docs/guide/multiple_db_config.md)
24    - [Configuring Supporting Services; HTTP, systemd, and nginx](docs/guide/services_config.md)
25    - [Using an Encryption Secret File](docs/guide/encryption_secret.md)
26    - [Enabling or Disabling Features](docs/guide/feature_config.md)
27- Configuring and Using a Client Application
28    - [Connecting with the Web Client](docs/guide/web-client.md)
29    - [Using the Go Chat Client](docs/guide/cli-client.md)
30    - [Configuring the Server for use with Claude Desktop](docs/guide/claude_desktop.md)
31    - [Configuring the Server for use with Cursor](docs/guide/cursor.md)
32- [Reviewing Server Logs](docs/guide/server_logs.md)
33- Authentication and Security
34    - [Authentication - Overview](docs/guide/authentication.md)
35    - [Authentication - User Management](docs/guide/auth_user.md)
36    - [Authentication - Token Management](docs/guide/auth_token.md)
37    - [Security Checklist](docs/guide/security.md)
38    - [Security Management](docs/guide/security_mgmt.md)
39- Reference
40    - [Using MCP Tools](docs/reference/tools.md)
41    - [Using MCP Resources](docs/reference/resources.md)
42    - [Using MCP Prompts](docs/reference/prompts.md)
43    - [Error Reference](docs/reference/error-reference.md)
44    - [Server Configuration File](docs/reference/config-examples/server.md)
45    - [API Token Configuration File](docs/reference/config-examples/tokens.md)
46    - [CLI Client Configuration Details](docs/reference/config-examples/cli-client.md)
47    - [KB Builder Configuration Details](docs/reference/config-examples/kb-builder.md)
48- Advanced Topics
49    - [Creating Custom Definitions](docs/advanced/custom-definitions.md)
50    - [Configuring and Using Knowledgebase Search](docs/advanced/knowledgebase.md)
51    - [Building a Custom Knowledge Base](docs/advanced/custom-knowledgebase-tutorial.md)
52    - [Using the LLM Proxy](docs/advanced/llm-proxy.md)
53    - [Row-Level and Column-Level Security](docs/advanced/row-level-security.md)
54    - [Distributed Deployment](docs/advanced/distributed-deployment.md)
55- For Developers
56    - [For Developers - Overview](docs/developers/overview.md)
57    - [MCP Protocol](docs/developers/mcp-protocol.md)
58    - [API Reference](docs/developers/api-reference.md)
59    - [API Browser](docs/api/browser.md)
60    - [Client Examples](docs/developers/client-examples.md)
61    - Building Chat Clients
62        - [Overview](docs/developers/building-chat-clients.md)
63        - [Python (Stdio + Claude)](docs/developers/stdio-anthropic-chatbot.md)
64        - [Python (HTTP + Ollama)](docs/developers/http-ollama-chatbot.md)
65- Contributing
66    - [Development Setup](docs/contributing/development.md)
67    - [Architecture](docs/contributing/architecture.md)
68    - [Internal Architecture](docs/contributing/internal-architecture.md)
69    - [KB Builder](docs/contributing/internal/kb-builder-architecture.md)
70    - [Testing](docs/contributing/testing.md)
71    - [CI/CD](docs/contributing/ci-cd.md)
72- [Accessing Online Help](docs/guide/help.md)
73- [Troubleshooting](docs/guide/troubleshooting.md)
74- [Release Notes](docs/changelog.md)
75- [Licence](docs/LICENSE.md)
76 
77The pgEdge Postgres Model Context Protocol (MCP) server enables
78SQL queries against PostgreSQL databases through MCP-compatible
79clients. The Natural Language Agent provides supporting
80functionality that allows you to use natural language to form
81SQL queries.
82 
83> **Supported Versions:** PostgreSQL 14 and higher.
84 
85> **WARNING**: This code is in pre-release status and MUST NOT be
86> put into production without thorough testing!
87 
88> **NOT FOR PUBLIC-FACING APPLICATIONS**: This MCP server provides
89> LLMs with read access to your entire database schema and data.
90> It should only be used for internal tools, developer workflows,
91> or environments where all users are trusted. For public-facing
92> applications, consider the
93> [pgEdge RAG Server](https://github.com/pgedge/pgedge-rag-server)
94> instead. See the
95> [Choosing the Right Solution](docs/guide/mcp-vs-rag.md) guide
96> for details.
97 
98## Quick Start
99 
100The [Quick Start](docs/guide/quickstart.md) guide covers
101installation and setup for all supported clients:
102 
103| Client | Transport | Best For |
104|--------|-----------|----------|
105| CLI (Stdio) | Stdio | Local single-user development |
106| CLI (HTTP) | HTTP | Multi-user or remote access |
107| Web UI | HTTP | Browser-based chat interface |
108| Claude Code | Stdio | Anthropic CLI agent |
109| Claude Desktop | Stdio | Anthropic desktop app |
110| Cursor | Stdio | AI code editor |
111| Windsurf | Stdio | Codeium code editor |
112| VS Code Copilot | Stdio | GitHub Copilot agent |
113 
114For a guided demo with sample data, see the
115[Quickstart Demo with Northwind](docs/guide/quickstart_demo.md).
116 
117## Key Features
118 
119- **Read-Only Protection** - All queries run in read-only
120  transactions by default
121- **Resources** - Access PostgreSQL statistics and more
122- **Tools** - Query execution, schema analysis, advanced hybrid
123  search (BM25+MMR), embedding generation, resource reading,
124  and more
125- **Prompts** - Guided workflows for semantic search setup,
126  database exploration, query diagnostics, and more
127- **Production Chat Client** - Full-featured Go client with
128  Anthropic prompt caching (90% cost reduction)
129- **HTTP/HTTPS Mode** - Direct API access with user and token
130  authentication
131- **Web Interface** - Modern React-based UI with AI-powered chat
132  for natural language database interaction
133- **Docker Support** - Complete containerized deployment with
134  Docker Compose
135- **Secure** - TLS support, user and token auth, read-only
136  enforcement
137- **Hot Reload** - Automatic reload of authentication files
138  without server restart
139 
140## Development
141 
142### Prerequisites
143 
144- Go 1.21 or higher
145- PostgreSQL 14 or higher (for testing)
146- golangci-lint v1.x (for linting)
147 
148### Setup Linter
149 
150The project uses golangci-lint v1.x. Install it with:
151 
152```bash
153go install github.com/golangci/golangci-lint/cmd/golangci-lint@latest
154```
155 
156Note: The configuration file [`.golangci.yml`](.golangci.yml)
157is compatible with golangci-lint v1.x (not v2).
158 
159### Building
160 
161```bash
162git clone https://github.com/pgEdge/pgedge-postgres-mcp.git
163cd pgedge-postgres-mcp
164make build
165```
166 
167### Testing
168 
169```bash
170# Run all tests
171make test
172 
173# Run server tests with a database
174export TEST_PGEDGE_POSTGRES_CONNECTION_STRING=\
175  "postgres://localhost/postgres?sslmode=disable"
176go test ./...
177 
178# Run with coverage
179go test -v -cover ./...
180 
181# Run linting
182make lint
183```
184 
185#### Web UI Tests
186 
187The web UI has a comprehensive test suite. See
188[web/TEST_SUMMARY.md](web/TEST_SUMMARY.md) for details.
189 
190```bash
191cd web
192npm test                # Run all tests
193npm run test:watch      # Watch mode
194npm run test:coverage   # With coverage
195```
196 
197## Security
198 
199- Read-only transaction enforcement (configurable per database)
200- User and API token authentication with expiration
201- TLS/HTTPS support
202- SHA256 token hashing
203- File permission enforcement (0600)
204- Input validation and sanitization
205 
206See the [Security Guide](docs/guide/security.md) for
207comprehensive security documentation.
208 
209## Troubleshooting
210 
211**Tools not visible in Claude Desktop?**
212- Use absolute paths in config
213- Restart Claude Desktop completely
214- Check JSON syntax
215 
216**Database connection errors?**
217- Ensure database connection is configured before starting the
218  server (via config file, environment variables, or
219  command-line flags)
220- Verify PostgreSQL is running: `pg_isready`
221- Check connection parameters are correct
222 
223See the [Troubleshooting Guide](docs/guide/troubleshooting.md)
224for detailed solutions.
225 
226## Support
227 
228To report an issue with the software, visit:
229[GitHub Issues](https://github.com/pgEdge/pgedge-postgres-mcp/issues)
230 
231For more information, visit
232[docs.pgedge.com](https://docs.pgedge.com)
233 
234This project is licensed under the
235[PostgreSQL License](LICENSE.md).
236