pgEdge Postgres MCP Server and Natural Language Agent

Introduction
- Choosing the Right Solution
Installing the MCP Server
Configuring the MCP Server
Configuring and Using Client Applications
Authentication and Security
Reference
- Tools
- Resources
- Prompts
- Examples
Advanced Topics
For Developers
Building Chat Clients
Contributing
Troubleshooting
pgEdge Postgres MCP Server and Natural Language Agent Release Notes
Licence

The pgEdge Postgres Model Context Protocol (MCP) server enables SQL queries against PostgreSQL databases through MCP-compatible clients like Claude Desktop. The Natural Language Agent provides supporting functionality that allows you to use natural language to form SQL queries.

🚧 WARNING: This code is in pre-release status and MUST NOT be put into production without thorough testing!

⚠️ NOT FOR PUBLIC-FACING APPLICATIONS: This MCP server provides LLMs with read access to your entire database schema and data. It should only be used for internal tools, developer workflows, or environments where all users are trusted. For public-facing applications, consider the pgEdge RAG Server instead. See the Choosing the Right Solution guide for details.

Key Features

🔒 Read-Only Protection - All queries run in read-only transactions
📊 Resources - Access PostgreSQL statistics and more
🛠️ Tools - Query execution, schema analysis, advanced hybrid search (BM25+MMR), embedding generation, resource reading, and more
🧠 Prompts - Guided workflows for semantic search setup, database exploration, query diagnostics, and more
💬 Production Chat Client - Full-featured Go client with Anthropic prompt caching (90% cost reduction)
🌐 HTTP/HTTPS Mode - Direct API access with token authentication
🖥️ Web Interface - Modern React-based UI with AI-powered chat for natural language database interaction
🐳 Docker Support - Complete containerized deployment with Docker Compose
🔐 Secure - TLS support, token auth, read-only enforcement
🔄 Hot Reload - Automatic reload of authentication files without server restart

Quick Start

1. Installation

git clone <repository-url>
cd pgedge-postgres-mcp
make build

2. Configure for Claude Code and/or Claude Desktop

Claude Code: .mcp.json in each of your project directories
Claude Desktop on macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
Claude Desktop on Windows: %APPDATA%\\Claude\\claude_desktop_config.json

{
  "mcpServers": {
    "pgedge": {
      "command": "/absolute/path/to/bin/pgedge-postgres-mcp"
    }
  }
}

3. Connect to Your Database

Update your Claude Code and/or Claude Desktop configuration to include database connection parameters:

{
  "mcpServers": {
    "pgedge": {
      "command": "/absolute/path/to/bin/pgedge-postgres-mcp",
      "env": {
        "PGHOST": "localhost",
        "PGPORT": "5432",
        "PGDATABASE": "mydb",
        "PGUSER": "myuser",
        "PGPASSWORD": "mypass"
      }
    }
  }
}

Alternatively, use a .pgpass file for password management (recommended for security):

# ~/.pgpass
localhost:5432:mydb:myuser:mypass

Then, provide connection details (except PGPASSWORD) in the configuration file:

{
  "mcpServers": {
    "pgedge": {
      "command": "/absolute/path/to/bin/pgedge-postgres-mcp",
      "env": {
        "PGHOST": "localhost",
        "PGPORT": "5432",
        "PGDATABASE": "mydb",
        "PGUSER": "myuser"
      }
    }
  }
}

Note: The server connects to the database at startup using standard PostgreSQL environment variables (PG*) or PGEDGE_DB_* variables. You can store passwords securely in the .pgpass file.

Example Queries

The MCP client (like Claude Desktop) can translate natural language to SQL, which is then executed by this server.

Schema Discovery:

Request schema information using the get_schema_info tool
Execute SQL: SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';

Data Analysis:

Execute SQL: SELECT customer_id, SUM(order_total) FROM orders GROUP BY customer_id ORDER BY SUM(order_total) DESC LIMIT 10;
Execute SQL: SELECT * FROM orders WHERE shipping_time > INTERVAL '7 days';

System Monitoring:

Use the pg://stat/activity resource for current connections
Execute SQL: SELECT schemaname, tablename, n_dead_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC;
Execute SQL: SELECT sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as cache_hit_ratio FROM pg_statio_user_tables;

HTTP/HTTPS Mode

Run as a standalone HTTP server for direct API access:

# HTTP without authentication (development only)
./bin/pgedge-postgres-mcp -http -no-auth

# HTTP with token authentication (recommended)
./bin/pgedge-postgres-mcp -http -auth-token-file tokens.json

# HTTPS with TLS and authentication
./bin/pgedge-postgres-mcp -http -tls \
  -cert server.crt \
  -key server.key \
  -auth-token-file tokens.json

Note: Authentication is enabled by default in HTTP mode. Use -no-auth to disable it for local development, or provide an authentication token file with -auth-token-file. See the Authentication Guide for token setup.

API Endpoint: POST http://localhost:8080/mcp/v1

Example request (with authentication):

curl -X POST http://localhost:8080/mcp/v1 \
  -H "Authorization: Bearer your-token" \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/call",
    "params": {
      "name": "query_database",
      "arguments": {
        "natural_language_query": "Show all users"
      }
    }
  }'

Example request (without authentication):

curl -X POST http://localhost:8080/mcp/v1 \
  -H "Content-Type: application/json" \
  -d '{
    "jsonrpc": "2.0",
    "id": 1,
    "method": "tools/call",
    "params": {
      "name": "query_database",
      "arguments": {
        "natural_language_query": "Show all users"
      }
    }
  }'

CLI Client

A production-ready, full-featured command-line chat interface is available for interacting with your PostgreSQL database using natural language:

# Stdio mode setup (MCP server as subprocess)
cp examples/pgedge-postgres-mcp-stdio.yaml.example bin/pgedge-postgres-mcp-stdio.yaml
cp examples/pgedge-nla-cli-stdio.yaml.example bin/pgedge-nla-cli-stdio.yaml
# Edit config files with your database settings, then:
./start_cli_stdio.sh

# HTTP mode setup (MCP server via HTTP with auth)
# First set up web client config (see Web Client section), then:
cp examples/pgedge-nla-cli-http.yaml.example bin/pgedge-nla-cli-http.yaml
./start_cli_http.sh

Features:

💬 Natural language database queries powered by Claude, GPT, or Ollama
🔧 Dual mode support (stdio subprocess or HTTP API)
💰 Anthropic prompt caching (90% cost reduction on repeated queries)
⚡ Runtime configuration with slash commands
📝 Persistent command history with readline support
🎨 PostgreSQL-themed UI with animations

Example queries:

What tables are in my database?
Show me the 10 most recent orders
Which customers have placed more than 5 orders?
Find documents similar to 'PostgreSQL performance tuning'

API Key Configuration:

The CLI client supports three ways to provide LLM API keys (in priority order):

Environment variables (recommended for development):

export ANTHROPIC_API_KEY="sk-ant-..."
export OPENAI_API_KEY="sk-proj-..."

API key files (recommended for production):

echo "sk-ant-..." > ~/.anthropic-api-key
chmod 600 ~/.anthropic-api-key

Configuration file values (not recommended - use env vars or files instead)

See Using the CLI Client for detailed documentation.

Web Client

A web-based management interface is available for monitoring and interacting with the MCP server:

# 1. Copy example config files
cp examples/pgedge-postgres-mcp-http.yaml.example bin/pgedge-postgres-mcp-http.yaml
cp examples/pgedge-postgres-mcp-users.yaml.example bin/pgedge-postgres-mcp-users.yaml
cp examples/pgedge-postgres-mcp-tokens.yaml.example bin/pgedge-postgres-mcp-tokens.yaml

# 2. Edit config with your database and LLM settings
nano bin/pgedge-postgres-mcp-http.yaml

# 3. Create a user for web login
./bin/pgedge-postgres-mcp user add --username myuser --annotation "My User"

# 4. Start the web client (starts both MCP server and web interface)
./start_web_client.sh

Features:

🔐 Secure authentication using MCP server credentials
📊 Real-time PostgreSQL system information
🌓 Light/dark theme support
📱 Responsive design for desktop and mobile

Access:

Web Interface: http://localhost:5173
MCP Server API: http://localhost:8080

See web/README.md for detailed documentation.

Docker Deployment

Deploy the entire stack with Docker Compose for production or development:

# 1. Copy the example environment file
cp .env.example .env

# 2. Edit .env with your configuration
nano .env  # Add your database connection, API keys, etc.

# 3. Build and start all services
docker-compose up -d

What gets deployed:

🐘 MCP Server - Backend service on port 8080
🌐 Web Client - Browser interface on port 8081
🔐 Authentication - Token or user-based auth from config
💾 Persistent Storage - User and token data in Docker volumes

Quick Access:

Web Interface: http://localhost:8081
MCP API: http://localhost:8080

See Deployment Guide for complete documentation including:

Individual container builds
Production deployment with reverse proxy
Security hardening
Resource limits and monitoring
Troubleshooting

How It Works

Configure - Set database connection parameters via environment variables, config file, or command-line flags
Start - Server starts and connects to PostgreSQL, extracting schema metadata
Query - You provide SQL queries via Claude Desktop or API
Execute - SQL runs in a read-only transaction
Return - Results formatted and returned to the client

Read-Only Protection: All queries run in read-only mode - no INSERT, UPDATE, DELETE, or DDL operations allowed.

Natural Language Support: The MCP client (like Claude Desktop with an LLM) can translate your natural language questions into SQL queries that are then executed by this server.

Development

Prerequisites

Go 1.21 or higher
PostgreSQL (for testing)
golangci-lint v1.x (for linting)

Setup Linter

The project uses golangci-lint v1.x. Install it with:

go install github.com/golangci/golangci-lint/cmd/golangci-lint@latest

Note: The configuration file .golangci.yml is compatible with golangci-lint v1.x (not v2).

Testing

# Run tests (uses TEST_PGEDGE_POSTGRES_CONNECTION_STRING)
export TEST_PGEDGE_POSTGRES_CONNECTION_STRING=\
  "postgres://localhost/postgres?sslmode=disable"
go test ./...

# Run with coverage
go test -v -cover ./...

# Run linting
make lint
# or directly:
golangci-lint run

# Run locally (configure database connection via environment variables or
# config file)
./bin/pgedge-postgres-mcp

Web UI Tests

The web UI has a comprehensive test suite. See web/TEST_SUMMARY.md for details.

cd web
npm test                # Run all tests
npm run test:watch      # Watch mode
npm run test:coverage   # With coverage

Security

✅ Read-only transaction enforcement
✅ API token authentication with expiration
✅ TLS/HTTPS support
✅ SHA256 token hashing
✅ File permission enforcement (0600)
✅ Input validation and sanitization

See Security Guide for comprehensive security documentation.

Troubleshooting

Tools not visible in Claude Desktop?

Use absolute paths in config
Restart Claude Desktop completely
Check JSON syntax

Database connection errors?

Ensure database connection is configured before starting the server (via environment variables, config file, or command-line flags)
Verify PostgreSQL is running: pg_isready
Check connection parameters are correct (host, port, database, user, password)

See Troubleshooting Guide for detailed solutions.

License

This software is released under the PostgreSQL License.

Support

📖 Documentation: docs/index.md
🐛 Issues: GitHub Issues
💡 Examples: Query Examples

Related Projects

Model Context Protocol - MCP specification
Claude Desktop - Anthropic's Claude AI assistant
PostgreSQL - The world's most advanced open source database

postgres-mcp

Documentation