Pg MCP Server

🚀 PG_MCP Server

A custom implementation supported by PostgreSQL + PostGIS

🚀 Quick Start

This is a general-purpose PostgreSQL MCP server. The MCP part is implemented using go-mcp, supporting Stdio and SSE transmission.

✨ Features

When deploying large language models locally, efficient context management is required. Reading the database schema during each call consumes time and valuable context tokens. This project adopts a preprocessing approach. It natively supports retrieving table structures from the database and providing descriptive information for LLMs:

• Implicitly or explicitly convey schema information using tool descriptions and input patterns.
• Utilize MCP resources during initialization to read table names, columns, constraints, foreign keys, indexes, geometric types, and EPSG codes, establishing a basic understanding for large models.

📦 Installation

In main.go:

// You can set the connection string for database interaction here
schemaLoadConnID, err := dbService.RegisterConnection(tempCtx, "postgres://mcp_user:mcp123456@192.168.2.19:5432/postgres")

The format is postgres://user:pass@host:port/db. Alternatively, you can configure it through the.env file. Simply uncomment and set the SCHEMA_LOAD_DB_URL variable. Here, RegisterConnection provides the server with an initial connection string to cache database table information. 🏁 It is recommended to create a dedicated role for the server. Here are the SQL statements:

-- Create a new role and set the password
CREATE ROLE mcp_user WITH LOGIN PASSWORD 'mcp123456';

-- Set the basic permissions for mcp_user
GRANT CONNECT ON DATABASE postgres TO mcp_user;
GRANT USAGE ON SCHEMA public TO mcp_user;

-- Grant SELECT permissions on all existing tables in the public schema
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_user;

-- Automatically grant SELECT permissions on new tables in the public schema to mcp_user
ALTER DEFAULT PRIVILEGES IN SCHEMA public
   GRANT SELECT ON TABLES TO mcp_user;

-- Create the temp schema
CREATE SCHEMA temp;

-- Grant all permissions on the temp schema to mcp_user
GRANT USAGE, CREATE ON SCHEMA temp TO mcp_user;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA temp TO mcp_user;

-- Automatically grant SELECT, INSERT, UPDATE, and DELETE permissions on new tables in the temp schema to mcp_user
ALTER DEFAULT PRIVILEGES IN SCHEMA temp
   GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO mcp_user;

💻 Usage Examples

Running with Docker

1. Ensure you have installed Docker.
2. Execute the following command in the project directory:

docker-compose up --build

This will build and start the service.

Running Directly

To run directly, make sure you have installed all the dependencies. Navigate to the project root directory in the terminal and run:

go run main.go

📚 Documentation

Extended Support

• PostGIS: Implemented
• PgVector: Implemented
• PgRouting: In progress

To-Do List

• Warm Table Management Mechanism
  • Implement table structure caching
  • Add table structure change detection
  • Develop an invalidation strategy
• Unit Testing
  • Unit tests for table structure reading and parsing
  • Tests for SQL injection and XSS protection

📄 License

The descriptions related to PostGIS and PgVector are quoted from another open-source project: https://github.com/stuzero/pg-mcp-server 🙏🙏paginator
This approach of providing context is refreshing.

⚠️ Important Note

The database needs to define roles to prevent SQL injection. Grant SELECT permissions on the public schema to avoid sensitive data leakage. ⚠️ Grant the new role SELECT, INSERT, UPDATE, and DELETE permissions on all tables in the temp schema to ensure data isolation.

Chinese Documentation