PostgreSQL MCP Server

A production-ready Model Context Protocol (MCP) server that enables Claude to execute read-only SQL queries against PostgreSQL databases safely and securely.

Features

• Read-Only Queries: Only SELECT statements allowed - no data modification

• Security First: Comprehensive SQL injection prevention and query validation

• Connection Pooling: Efficient database connection management

• Row Limits: Automatic enforcement of query result limits

• Production Ready: Structured logging, error handling, and graceful shutdown

• Type Safe: Built with TypeScript for reliability

• VPS Ready: Includes deployment guides for remote VPS setups

Read the Deployment Guide for instructions on setting up the server on a VPS.

Installation

npm install

Configuration

Create a .env file based on .env.example:

# Required
DATABASE_URL=postgresql://user:password@localhost:5432/dbname

# Optional (with defaults)
NODE_ENV=development
LOG_LEVEL=info
DEFAULT_QUERY_LIMIT=100
MAX_QUERY_LIMIT=10000
QUERY_TIMEOUT_MS=30000

Environment Variables

• DATABASE_URL (required): PostgreSQL connection string

• NODE_ENV: Environment mode (development, production, test)

• LOG_LEVEL: Logging level (trace, debug, info, warn, error, fatal)

• DEFAULT_QUERY_LIMIT: Default row limit for queries (default: 100)

• MAX_QUERY_LIMIT: Maximum allowed row limit (default: 10000)

• QUERY_TIMEOUT_MS: Query execution timeout in milliseconds (default: 30000)

Development

# Run in development mode with auto-reload (no build needed)
npm run dev

# Build the project (compile TypeScript to JavaScript)
npm run build

# Run the production build (after npm run build)
npm start

# Run tests
npm test

# Run tests in watch mode
npm run test:watch

# Run tests with coverage
npm run test:coverage

# Type check
npm run type-check

# Lint
npm run lint

# Format code
npm run format

Local Testing

Test your MCP server locally before integrating with Claude Desktop:

Option 1: Automated Test Script (Recommended)

# Ensure .env file is configured with DATABASE_URL
npm run test:local

This runs automated tests that verify:

• Server starts correctly

• Tools are registered

• Queries execute successfully

• Security validation works

• Row limits are enforced

Option 2: MCP Inspector (Interactive)

# Build first
npm run build

# Start the inspector
npm run inspect

This opens a web UI where you can:

• Browse available tools

• Execute queries interactively

• View responses and debug messages

• Test different parameters

Option 3: Manual Testing with PostgreSQL Client

# Run the dev server
npm run dev

# In another terminal, test your database connection
psql $DATABASE_URL -c "SELECT 1"

Usage with Claude Desktop

1. Build the Project

npm run build

2. Configure Claude Desktop

Add to your Claude Desktop config file:

macOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%\Claude\claude_desktop_config.json

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-server/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://user:password@localhost:5432/dbname"
      }
    }
  }
}

3. Restart Claude Desktop

Restart Claude Desktop to load the MCP server.

4. Test the Connection

Try asking Claude:

• "List all tables in the database"

• "Show me the first 5 rows from the users table"

• "Count the total number of records in the orders table"

Available Tools

execute_query

Execute a read-only SQL SELECT query against the PostgreSQL database.

Parameters:

• query (string, required): The SQL SELECT query to execute

• limit (number, optional): Row limit for results (default: from config, max: 10000)

Example:

{
  "query": "SELECT id, name, email FROM users WHERE active = true",
  "limit": 50
}

Response:

{
  "success": true,
  "data": {
    "rows": [...],
    "rowCount": 42,
    "fields": [
      { "name": "id", "dataType": "integer" },
      { "name": "name", "dataType": "text" },
      { "name": "email", "dataType": "varchar" }
    ],
    "executionTimeMs": 15
  }
}

Security

Query Validation

The server implements multiple layers of security:

1. SQL Parsing: Queries are parsed using node-sql-parser to ensure valid syntax

2. Statement Type Check: Only SELECT statements are allowed

3. Dangerous Function Detection: Blocks PostgreSQL functions like pg_read_file, COPY, etc.

4. Comment Removal: SQL comments are stripped to prevent comment-based injection

5. Row Limit Enforcement: Automatic LIMIT clauses prevent excessive data retrieval

6. Read-Only Validation: Additional layer ensures no data modification

Blocked Operations

• INSERT, UPDATE, DELETE, TRUNCATE

• CREATE, DROP, ALTER (DDL operations)

• GRANT, REVOKE (permission changes)

• SELECT INTO (data copying)

• FOR UPDATE/FOR SHARE (row locking)

• Dangerous functions (pg_read_file, pg_ls_dir, COPY, etc.)

Best Practices

1. Use Read-Only Database Users: Create a database user with SELECT-only permissions

2. Limit Database Access: Only grant access to necessary schemas/tables

3. Use SSL/TLS: Enable SSL for database connections in production

4. Monitor Queries: Review logs regularly for suspicious activity

5. Set Resource Limits: Configure appropriate query timeouts and row limits

Testing

Unit Tests

npm test

Unit tests cover:

• Query validation and sanitization

• Security checks (SQL injection, dangerous functions)

• Row limit enforcement

Integration Tests

Integration tests require a running PostgreSQL instance:

# Start PostgreSQL (example with Docker)
docker run --name test-postgres \
  -e POSTGRES_PASSWORD=testpass \
  -p 5432:5432 \
  -d postgres:16

# Set DATABASE_URL and run tests
export DATABASE_URL=postgresql://postgres:testpass@localhost:5432/postgres
npm test

Architecture

src/
├── index.ts              # Entry point
├── server.ts             # MCP server setup
├── config/
│   └── index.ts         # Configuration loader
├── database/
│   └── connection.ts    # PostgreSQL connection with pooling
├── security/
│   ├── query-validator.ts      # SQL validation
│   └── read-only-validator.ts  # Read-only enforcement
├── tools/
│   └── query-tool.ts    # execute_query tool
└── utils/
    ├── logger.ts        # Structured logging
    └── types.ts         # TypeScript types

Error Handling

The server provides detailed error messages:

• ValidationError: Invalid query syntax or parameters

• SecurityError: SQL injection attempts or unauthorized operations

• DatabaseError: Connection failures or query execution errors

• ConfigurationError: Missing or invalid configuration

Logging

Structured JSON logging in production, pretty-printed in development:

{
  "level": "info",
  "time": "2024-01-11T10:30:00.000Z",
  "msg": "Query executed successfully",
  "rowCount": 42,
  "executionTimeMs": 15
}

Troubleshooting

Connection Issues

• Verify DATABASE_URL is correct

• Check database is running and accessible

• Ensure firewall allows PostgreSQL connections

• Test connection with psql command

Permission Errors

• Ensure database user has SELECT permissions

• Check schema access permissions

• Verify connection string includes correct database name

Query Timeouts

• Increase QUERY_TIMEOUT_MS for long-running queries

• Optimize slow queries with indexes

• Reduce row limits if fetching too much data

Future Enhancements (v2)

• Multi-database support

• Additional tools (list_tables, describe_table, get_schema)

• HTTP transport for remote access

• Schema caching

• Query history logging

• Prometheus metrics export

License

MIT

Contributing

Contributions are welcome! Please ensure:

• Tests pass (npm test)

• Code is formatted (npm run format)

• Types are valid (npm run type-check)

• Security best practices are followed