MCP Postgres Query Server

🚀 MCP PostgreSQL Query Server

An implementation of the Model Context Protocol (MCP) server for querying PostgreSQL databases in read-only mode, designed to work with Claude Desktop and other MCP clients.

🚀 Quick Start

This project implements a Model Context Protocol (MCP) server with the following capabilities:

1. Offers a secure, read-only interface to access PostgreSQL databases.
2. Integrates with Claude Desktop via the MCP protocol.
3. Validates SQL queries to ensure only SELECT queries are executed.
4. Provides query timeout protection (10 seconds).

✨ Features

• Read-only Database Access: Only allows SELECT queries, ensuring security.
• Query Validation: Prevents potentially harmful SQL operations.
• Timeout Protection: Queries running for more than 10 seconds will be automatically terminated.
• MCP Protocol Support: A complete implementation of the Model Context Protocol.
• JSON Response Formatting: Query results are returned in a structured JSON format.

📦 Installation

Prerequisites

• Node.js (v14 or higher)
• npm (provided with Node.js)
• PostgreSQL database (connection details provided via the command line)

# Clone the repository
git clone https://github.com/RathodDarshil/mcp-postgres-query-server.git
cd mcp-postgres-query-server

# Install dependencies
npm install

# Build the project
npm run build

💻 Usage Examples

Connecting with Claude Desktop

You can configure Claude Desktop to automatically start and connect to the MCP server:

1. Access Claude Desktop's configuration file:
   • Open Claude Desktop.
   • Go to Settings > Developer > Edit Configuration.
   • This will open the configuration file in the default text editor.
2. Add postgres-query-server to the mcpServers section of claude_desktop_config.json:

{
    "mcpServers": {
        "postgres-query": {
            "command": "node",
            "args": [
                "/path/to/your/mcp-postgres-query-server/dist/index.js",
                "postgresql://username:password@hostname:port/database"
            ]
        }
    }
}

3. Replace /path/to/your/ with the actual path of your project directory.
4. Replace the database credentials with the actual PostgreSQL connection string.
5. Save the file and restart Claude Desktop. The MCP server should now appear in the MCP server selection dropdown in the settings.

Configuration Example

Here is a complete example of the configuration file with postgres-query:

{
    "mcpServers": {
        "postgres-query": {
            "command": "node",
            "args": [
                "/Users/darshilrathod/mcp-servers/mcp-postgres-query-server/dist/index.js",
                "postgresql://user:password@localhost:5432/mydatabase"
            ]
        }
    }
}

Configuration Update

To update the Claude Desktop configuration:

1. Open Claude Desktop.
2. Go to Settings > Developer > Edit Configuration.
3. Make changes in the configuration file.
4. Save the file.
5. Restart Claude Desktop for the changes to take effect.
6. If you have updated the MCP server code, rebuild it using npm run build before restarting.

📚 Documentation

API

Tools

query-postgres

Execute a read-only SQL query on the configured PostgreSQL database.

Parameters:

• query (string): The SQL SELECT query to be executed.

Response:

• A JSON object containing:
  • rows: Rows of the result set.
  • columns: Column information.
  • rowCount: Number of rows.
  • type: Query type.

Example Request

{
  "procedure": "query-postgres",
  "params": {
    "query": "SELECT * FROM users LIMIT 10"
  }
}

Example Response

{
  "rows": [
    { "id": 1, "name": "Alice" },
    { "id": 2, "name": "Bob" }
  ],
  "columns": ["id", "name"],
  "rowCount": 2,
  "type": "SELECT"
}

Development

Configuring PostgreSQL Connection

In the config.js file, configure the PostgreSQL connection details:

module.exports = {
    host: 'localhost',
    port: 5432,
    user: 'yourdbuser',
    password: 'yourdbpassword',
    database: 'yourdatabasename'
};

Running Queries

In the index.js file, define the logic to handle queries:

const { Pool } = require('pg');
const pool = new Pool(require('./config'));

async function queryPostgres(query) {
    try {
        const result = await pool.query(query);
        return {
            rows: Array.from(result.rows),
            columns: result.columns,
            rowCount: result.rowCount,
            type: result.command
        };
    } catch (error) {
        throw new Error(`Query failed: ${error.message}`);
    }
}

module.exports = { queryPostgres };

🔧 Technical Details

Security Considerations

• Ensure PostgreSQL credentials are secure and avoid storing them in plain text.
• Configure appropriate firewall rules to limit database access.
• Regularly back up the database to prevent data loss.
• Enable SSL connections to encrypt communication with the database.