adropofliquid/postgres-mcp-server

PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides read-only access to PostgreSQL databases. This server exposes tools that allow AI clients to interact with PostgreSQL databases safely by executing SELECT queries, listing tables, and describing table structures.

Features

The server provides three main tools:

1. List Tables: Lists all tables in the public schema
2. Execute SELECT Query: Executes SELECT queries safely (read-only)
3. Describe Table: Shows table structure including columns, data types, and constraints

Prerequisites

• Java 17 or higher
• PostgreSQL database
• Gradle 8.0+

Configuration

Database Configuration

Set the following environment variables or update src/main/resources/application.yml:

export POSTGRES_URL=jdbc:postgresql://localhost:5432/your_database
export POSTGRES_USERNAME=your_username
export POSTGRES_PASSWORD=your_password

Application Configuration

The server is configured to run as an MCP SSE (Server-Sent Events) server. The configuration is in application.yml:

spring:
  ai:
    mcp:
      server:
        sse:

Building and Running

Build the Application

./gradlew build

Run the Server

./gradlew bootRun

Or run the built JAR:

java -jar build/libs/postgres-0.0.1-SNAPSHOT.jar

Using with MCP Clients

This server communicates via SSE (Server-Sent Events) over HTTP and follows the MCP protocol specification. Any MCP-compatible client can connect to it at http://localhost:8080/mcp/sse.

Available Tools

1. listTables

Lists all tables in the public schema.

Usage: "Show me all tables in the database"

2. executeSelectQuery

Executes SELECT queries safely. Only SELECT statements are allowed for security.

Parameters:

• query: The SELECT SQL query to execute

Usage: "Execute the query: SELECT * FROM users LIMIT 10"

Security Features:

• Only SELECT queries are allowed
• Blocks INSERT, UPDATE, DELETE, DROP, CREATE, ALTER statements
• Limited to 100 rows in output for performance

3. describeTable

Describes the structure of a specific table.

Parameters:

• tableName: The name of the table to describe

Usage: "Describe the structure of the users table"

Output includes:

• Column names and data types
• Nullable constraints
• Default values
• Primary keys
• Foreign key relationships

Security

This server implements several security measures:

1. Read-Only Access: Only SELECT queries are permitted
2. Query Validation: Dangerous SQL keywords are blocked
3. Schema Restriction: Only accesses the public schema
4. Result Limiting: Query results are limited to 100 rows
5. Input Sanitization: SQL injection protection via parameterized queries

Example Queries

Once connected to an MCP client like Claude Desktop, you can ask:

• "What tables are available in this database?"
• "Show me the first 10 rows from the users table"
• "Describe the structure of the products table"
• "Find all orders from the last month"

Troubleshooting

Connection Issues

1. Verify PostgreSQL is running
2. Check database credentials
3. Ensure the database is accessible from the server location
4. Verify the JDBC URL format

MCP Client Issues

1. Ensure the server starts without errors
2. Check that SSE transport is properly configured and the port is available
3. Verify the client can access http://localhost:8080/mcp/sse
4. Check firewall settings if connecting from external clients

Performance Considerations

• Large result sets are automatically limited to 100 rows
• Complex queries may timeout - consider adding WHERE clauses
• Index your tables for better query performance

Development

Adding New Tools

To add new tools, create methods in PostgresToolsService and annotate them with @Tool:

@Tool(description = "Your tool description")
fun yourNewTool(
    @ToolParam(description = "Parameter description") param: String
): String {
    // Implementation
}

Contributing

1. Fork the repository
2. Create a feature branch
3. Implement your changes 4Submit a pull request

License

This project is licensed under the MIT License.