PostgreSQL MCP Server

An MCP (Model Context Protocol) server for querying a PostgreSQL database. Execute SQL queries, view table details, and manage database connections through MCP-compatible clients.

Key Features

• Database Connection: Connect to PostgreSQL databases with authentication

• SQL Execution: Execute SELECT queries with security validation

• Connection Testing: Test database connection status

Installation

Claude Desktop

Follow these steps for installation:

Step 1: Download the MCP Server

Download the postgres-connector.mcpb file from this repository (located in the root directory).

Step 2: Install the MCP Server

1. Open Claude Desktop

2. Navigate to Settings > Features

3. Click "Install Extension" and select the postgres-connector.mcpb file

Screenshot placeholder: Installing the .mcpb file

Step 3: Configure Database Connection

After installation, the PostgreSQL Connector will appear in your Claude Desktop with default environment variables that you can configure with your database details.

Screenshot placeholder: Database configuration interface showing environment variables

Step 4: Test the database connection

Send a query "test the database connection"

Configuration

Required Database Settings

You'll need to provide the following information for your PostgreSQL database:

These environment variables will be automatically available for configuration in the extension settings panel after installing the extension in Claude Desktop.

Available Tools

1. Test PostgreSQL Connection

Tool: test-postgres-connection

Tests your database connection and displays the current configuration (password hidden for security).

Example Output:

2. Get All Tables

Tool: get-all-tables

Retrieves a list of all tables in the public schema of your database.

Example Output:

3. Execute SQL Query

Tool: execute-sql-query

Executes SELECT queries on your database with security validation.

Input:

• sql: The SQL query to execute (SELECT statements only)

Example Usage:

Example Output:

Security & Safety

SQL Query Validation

The PostgreSQL MCP server includes security measures:

• READ-ONLY Operations: Only SELECT statements are permitted

• Dangerous Keywords Blocked: DROP, DELETE, TRUNCATE, ALTER, CREATE, INSERT, UPDATE operations are automatically blocked

• Statement-Level Validation: Each SQL statement is validated individually

• Safe Error Handling: Database errors are caught and displayed safely

Best practice is to create a read-only database user for the postgres connector to use.

Blocked Operations

The following SQL operations are NOT ALLOWED for security:

• DROP (deleting tables/databases)

• DELETE (removing data)

• TRUNCATE (clearing tables)

• ALTER (modifying structure)

• CREATE (creating new objects)

• INSERT (adding data)

• UPDATE (modifying data)

Usage Examples

Get Database Tables

Use the get-all-tables tool to retrieve all tables in your database schema.

Execute Queries

Use the execute-sql-query tool with SELECT statements:

Troubleshooting

Connection Issues

Problem: "Database connection failed"

• Check: Verify your host, port, database name, username, and password

• Check: Ensure your PostgreSQL server is running and accessible

• Check: Verify that your database user has the necessary permissions

Problem: "Connection timeout"

• Check: Your database server might be behind a firewall

• Check: The host address and port number are correct

• Check: Network connectivity to the database server

Query Issues

Problem: "Dangerous operation detected"

• Cause: You're trying to execute a non-SELECT statement

• Solution: Only SELECT queries are allowed for security reasons

Problem: "Query execution failed"

• Check: Your SQL syntax is correct

• Check: The tables and columns you're referencing exist

• Check: Your database user has SELECT permissions on the tables

Configuration Issues

Problem: MCP server not appearing in Claude Desktop client

• Solution: Restart Claude Desktop

• Check: Verify the .mcpb file was installed correctly

• Check: Look for any error messages in Claude Desktop's console

Problem: Environment variables not showing up

• Solution: Restart Claude Desktop after installation

• Check: Ensure the extension is enabled in Settings > Features

Problem: Cannot configure database settings

• Solution: Make sure all required environment variables are filled out

• Check: Verify your database credentials are correct

Technical Details

System Requirements

• PostgreSQL database (version 9.0 or higher)

• Network access to your PostgreSQL server

Dependencies

• Node.js runtime

• PostgreSQL client libraries

• MCP (Model Context Protocol) framework

Project Structure

License

MIT License - see the LICENSE file for details.

Author

Marty Markenson

• Email: martyamark@gmail.com

• LinkedIn: Marty Markenson

Need help? Open an issue in this repository or reach out to martymarkenson