PostgreSQL MCP Server

A simple Model Context Protocol (MCP) Server implementation for PostgreSQL database access. This is a pet project that demonstrates how to connect AI assistants to PostgreSQL databases through the MCP specification.

This was build specifically to connect to the Claude Desktop. If you are on Mac, you can edit the .../Library/Application Support/Claude/claude-desktop.json file and add the following:

{
    "mcpServers": {
        "pg-rs": {
            "command": "/path/to/postgresql-mcp/target/release/postgresql-mcp",
            "args": [
                "postgres://postgres:postgres@localhost:5432/your_database_name"
            ],
            "env": {
                "RUST_LOG": "debug"
            }
        }
    }
}

Then you can use the query tool in Claude to execute SQL queries against your PostgreSQL database.

⚠️ Warning: This is an experimental project and should NOT be used in production environments.

🚀 Features

• Basic PostgreSQL connectivity through standard connection strings
• Simple query execution with JSON result formatting
• MCP v2024-11-05 implementation using stdin/stdout communication
• Async operations with Tokio

📋 Prerequisites

• Rust 1.70+ (uses Rust 2024 edition)
• PostgreSQL Database (local or remote)
• Access credentials for your PostgreSQL instance

🛠️ Installation

From Source

1. Clone the repository:

   git clone <repository-url>
   cd postgresql-mcp

2. Build the project:

   cargo build --release

3. The binary will be available at:

   target/release/postgresql-mcp
   

🎯 Usage

Basic Usage

Run the MCP server with a PostgreSQL connection string:

./target/release/postgresql-mcp "postgres://username:password@localhost:5432/database_name"

Connection String Examples

# Local PostgreSQL with password
./postgresql-mcp "postgres://myuser:mypassword@localhost:5432/mydatabase"

# Remote PostgreSQL
./postgresql-mcp "postgres://user:pass@remote-host:5432/production_db"

# Local PostgreSQL without password (trust authentication)
./postgresql-mcp "postgres://postgres@localhost:5432/testdb"

# PostgreSQL with SSL (if supported)
./postgresql-mcp "postgresql://user:pass@secure-host:5432/db?sslmode=require"

Integration with AI Tools

This server communicates via stdin/stdout using the Model Context Protocol. It's designed to be used with AI assistants that support MCP servers.

🔧 Available Tools

query

Execute SQL queries on the connected PostgreSQL database.

Parameters:

• sql (string): The SQL query to execute

Example Usage:

SELECT * FROM users WHERE age > 25;

Response Format:

{
  "success": true,
  "rowCount": 3,
  "data": [
    {
      "id": 1,
      "name": "John Doe",
      "age": 30,
      "email": "john@example.com"
    },
    {
      "id": 2,
      "name": "Jane Smith",
      "age": 28,
      "email": "jane@example.com"
    }
  ]
}

Error Response:

{
  "success": false,
  "error": "relation \"nonexistent_table\" does not exist"
}

📊 Supported Data Types

The server automatically converts PostgreSQL data types to appropriate JSON representations:

PostgreSQL Type	JSON Type	Notes
bool	boolean
int2, int4	number	16-bit and 32-bit integers
int8	number	64-bit integers
float4, float8	number	Single and double precision floats
numeric, decimal	string	High-precision decimals as strings
text, varchar, char	string	Text data
timestamp, timestamptz	string	ISO 8601 formatted timestamps
Other types	string	Fallback to string representation

🏗️ Architecture

┌─────────────────┐    ┌──────────────────┐    ┌─────────────────┐
│   AI Assistant  │◄──►│  PostgreSQL MCP  │◄──►│   PostgreSQL    │
│      (Client)   │    │     Server       │    │    Database     │
└─────────────────┘    └──────────────────┘    └─────────────────┘
        │                        │                        │
        │     MCP Protocol       │   tokio-postgres      │
        │   (stdin/stdout)       │                        │
        └────────────────────────┘                        │
                                                          │
                                          SQL Queries ────┘

🧩 Dependencies

• rmcp: Model Context Protocol implementation
• tokio-postgres: Async PostgreSQL client
• tokio: Async runtime
• serde: Serialization framework
• rust_decimal: Decimal number handling
• chrono: Date and time handling

🔒 Security Considerations

• Connection Security: Always use secure connection strings in production
• SQL Injection: The server executes raw SQL queries - ensure proper input validation at the client level
• Access Control: The server inherits the permissions of the database user specified in the connection string
• Network Security: Consider using SSL/TLS connections for remote databases

🚨 Error Handling

The server provides detailed error reporting for common issues:

• Connection Failures: Invalid connection strings or unreachable databases
• SQL Syntax Errors: Malformed queries with PostgreSQL error messages
• Permission Errors: Insufficient database privileges
• Type Conversion Errors: Automatic fallback to string representation

🤝 Contributing

1. Fork the repository
2. Create a feature branch (git checkout -b feature/amazing-feature)
3. Commit your changes (git commit -m 'Add some amazing feature')
4. Push to the branch (git push origin feature/amazing-feature)
5. Open a Pull Request

📝 License

This project is licensed under the MIT License - see the LICENSE file for details.

🆘 Support

If you encounter any issues or have questions:

1. Check the error messages in the console output
2. Verify your PostgreSQL connection string is correct
3. Ensure your PostgreSQL server is running and accessible
4. Open an issue with detailed error information

🔮 Future Enhancements

• Connection pooling for better performance
• SSL/TLS connection support
• Prepared statement support
• Transaction management
• Schema introspection tools
• Query result streaming for large datasets
• Connection health monitoring