PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides read-only access to PostgreSQL databases.

Features

• Read-only operations: Query data safely without risk of modifications
• Schema inspection: Explore database structure, tables, and columns
• Query execution: Run SELECT statements and view results
• Connection management: Secure connection handling with proper cleanup

Installation

1. Clone this repository

2. Install dependencies:

   npm install
   

3. Build the project:

   npm run build
   

4. Configure your database connection (see Configuration section below)

Configuration

Create a .env file in the root directory with your PostgreSQL connection details:

POSTGRES_HOST=localhost
POSTGRES_PORT=5432
POSTGRES_DATABASE=your_database
POSTGRES_USER=your_username
POSTGRES_PASSWORD=your_password
POSTGRES_SSL=false

Usage

Running the Server

npm start

Available Tools

The MCP server provides the following tools:

1. postgres_query - Execute SELECT queries
2. postgres_describe_table - Get table structure and column information
3. postgres_list_tables - List all tables in the database
4. postgres_list_schemas - List all schemas in the database

Example MCP Client Configuration

Add this to your MCP client configuration (e.g., ~/.config/claude-desktop/mcp.json):

{
  "servers": {
    "postgresql": {
      "command": "node",
      "args": ["path/to/mcp/server/start-server.cjs"],
      "type": "stdio"
    }
  }
}

This configuration automatically loads environment variables from your project's .env file, keeping sensitive credentials out of the MCP configuration.

Security

This server operates in read-only mode only. It:

• Only allows SELECT statements
• Blocks INSERT, UPDATE, DELETE, DROP, CREATE, ALTER operations
• Uses parameterized queries to prevent SQL injection
• Validates all queries before execution

Testing

Quick Test

# Run complete test suite (includes security testing)
npm run test:complete
# or
./test.sh

Test Environment

The project includes a comprehensive test environment with:

• PostgreSQL 15.13 in Docker
• Realistic e-commerce test data (74 records across 6 tables)
• Security validation tests
• Automated setup and cleanup

See test/README.md for detailed testing documentation.

Development

# Development mode (rebuild and run)
npm run dev

# Build only (required after code changes)
npm run build

# Test database connection
npm run test-connection

# Test security features
npm run test-mcp-security

Note: You only need to run npm run build when you modify the TypeScript source code. For normal usage with different database credentials, just update your .env file.

License

MIT