Model Context Protocol SQL Access Gateway for PostgreSQL

This implementation serves as an MCP server component, exposing PostgreSQL data retrieval capabilities exclusively through read-only SELECT operations, tailored for consumption by Claude Desktop and other compliant MCP clients.

Core Functionality

This system furnishes:

1. A protected, read-only conduit to the PostgreSQL environment.
2. Seamless protocol adherence for interfacing with Claude Desktop.
3. Mandatory validation ensuring only SELECT statements are processed.
4. A strict operational time cap (10 seconds) on all database interrogations.

Prerequisites for Deployment

• Node.js runtime environment (version 14 minimum)
• npm package manager (bundled with Node.js)
• Access credentials for the target PostgreSQL instance (supplied at runtime)

Setup Instructions

bash

Obtain the source code repository

git clone https://github.com/RathodDarshil/mcp-postgres-query-server.git cd mcp-postgres-query-server

Install necessary dependencies

npm install

Compile the source code

npm run build

Integrating with Claude Desktop

Configuration within Claude Desktop allows automatic initialization and linkage to this MCP service:

1. Navigate to the configuration interface in Claude Desktop:

   • Launch Claude Desktop
   • Access Settings -> Developer -> Configuration File Editor
   • This action opens the primary configuration file in your system's default editor.

2. Insert the service definition into 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" ] } } }

1. Substitute the placeholder path (/path/to/your/) with the actual location of your compiled project directory.
2. Replace the sample PostgreSQL connection string with your genuine database access parameters.
3. Save the configuration file and initiate a restart of Claude Desktop. The new gateway should now be selectable within the MCP service menu in Settings.

Configuration Instance Example

Illustrative content for the configuration file incorporating the postgres-query service:

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

Configuration Management

To apply modifications to the Claude Desktop linkage:

1. Open Claude Desktop.
2. Navigate to Settings -> Developer -> Configuration File Editor.
3. Implement the required adjustments to the JSON structure.
4. Persist the changes.
5. Restart Claude Desktop for the updates to become active.
6. If source code changes were made, ensure a fresh build using npm run build precedes the restart.

Features Summary

• Data Retrieval Access Only: Strictly enforces read-only permissions; mutation commands are prohibited.
• SQL Sanitization: Rigorous checks prevent unauthorized or unsafe query execution.
• Execution Limit Enforcement: Database operations exceeding 10 seconds are unilaterally halted.
• MCP Protocol Compliance: Full adherence to the Model Context Protocol specification.
• Structured Output: Database retrieval sets are systematically formatted as JSON objects.

Interface Definition (API)

Tooling

execute-db-select

Performs a non-mutating SQL query against the provisioned PostgreSQL instance.

Arguments:

• query (string): The SQL SELECT statement intended for execution.

Return Value:

• A JSON object containing:
  • rows: The dataset returned by the query.
  • rowCount: The cardinality of the returned rows.
  • fields: Metadata describing the result columns.

Invocation Example:

execute-db-select: SELECT customer_name, registration_date FROM clients WHERE active = TRUE LIMIT 10

Implementation Details (Development)

The core server logic resides within src/index.ts. Essential components include:

• Configuration of the PostgreSQL connection pool.
• The mechanism for SQL statement validation.
• MCP server bootstrap and configuration.
• Tool and resource registration definitions.

To alter system behavior, developers can:

• Modify the criteria within the isReadOnlyQuery() function.
• Introduce supplementary tools or data resources to the MCP framework.
• Adjust the configured query termination threshold (presently set at 10s).

Security Posture

• All incoming statements undergo validation to confirm read-only intent.
• SSL is mandated for all database connections.
• The timeout feature mitigates potential resource exhaustion attacks.
• Write, update, or delete operations are explicitly blocked.
• Sensitive database credentials are provided via runtime parameters, avoiding file-based storage.

Licensing

ISC License.

Collaboration

We welcome external contributions! Please feel encouraged to submit a Pull Request.