Postgresql MCP Server Y4k

🚀 PostgreSQL MCP Server

A Model Context Protocol (MCP) server for interacting with PostgreSQL databases, enabling various SQL operations and table management.

🚀 Quick Start

This is a Model Context Protocol (MCP) server designed to interact with PostgreSQL databases. The server supports the following functions:

• Read Queries: Execute SELECT-type SQL queries.
• Write Queries: Execute INSERT, UPDATE, and DELETE-type SQL operations.
• Create Tables: Create new tables in the database.
• List Tables: Retrieve information about all tables in the database.

✨ Features

• Execute various types of SQL queries and operations.
• Create and list tables in the PostgreSQL database.
• Validate query types to ensure security.
• Sanitize input to prevent SQL injection attacks.

📦 Installation

Prerequisites

• A PostgreSQL database instance
• A Go language development environment
• The Make tool

Clone the Repository

git clone https://github.com/yourusername/pgmcp.git
cd pgmcp

Install Dependencies

go mod tidy

Build the Server

make build

📚 Documentation

Configuration

Add the following environment variables to the project's .env file:

• DB_NAME: The name of the PostgreSQL database.
• DB_USER: The username for connecting to the database.
• DB_PASSWORD: The user's password.
• DB_HOST: The database host address (default is localhost).
• DB_PORT: The database port number (default is 5432).

Example .env file:

DB_NAME=postgres
DB_USER=pguser
DB_PASSWORD=pgpassword
DB_HOST=localhost
DB_PORT=5432

Usage

Run the Server

go run main.go

MCP Configuration

Configure the MCP service parameters in config.json:

{
  "name": "PostgreSQL MCP Server",
  "version": "1.0",
  "description": "A PostgreSQL MCP server implementation.",
  "tools": {
    "read_query": {
      "description": "Execute a SELECT query and return the results."
    },
    "write_query": {
      "description": "Execute an INSERT, UPDATE, or DELETE statement."
    },
    "create_table": {
      "description": "Create a new table in the database."
    },
    "list_tables": {
      "description": "List all tables in the database."
    }
  }
}

💻 Usage Examples

Basic Usage

Read Query

{
  "tool": "read_query",
  "params": {
    "query": "SELECT * FROM users LIMIT 10;"
  }
}

Write Query

{
  "tool": "write_query",
  "params": {
    "query": "INSERT INTO users (name, email) VALUES ($1, $2);",
    "args": ["John Doe", "john@example.com"]
  }
}

Create Table

{
  "tool": "create_table",
  "params": {
    "table_name": "users",
    "schema": "CREATE TABLE users (\n    id SERIAL PRIMARY KEY,\n    name VARCHAR(255) NOT NULL,\n    email VARCHAR(255) UNIQUE NOT NULL\n);"
  }
}

List Tables

{
  "tool": "list_tables",
  "params": {}
}

Advanced Usage

The server validates query types to ensure that each tool only performs its allowed operations. It also sanitizes the input schema names to prevent SQL injection attacks.

🔧 Technical Details

• The server validates query types to ensure that each tool only performs its allowed operations.
• It sanitizes the input schema names to prevent SQL injection attacks.
• It is recommended to use a dedicated database user with restricted permissions.
• In a production environment, enable SSL by setting DB_SSLMODE to require or higher.

📄 License

[Add license information here]

🤝 Contribution Guidelines

[Add contribution guidelines here]

⚠️ Important Note

The server validates query types to ensure that each tool only performs its allowed operations. It also sanitizes the input schema names to prevent SQL injection attacks.

💡 Usage Tip

It is recommended to use a dedicated database user with restricted permissions. In a production environment, enable SSL by setting DB_SSLMODE to require or higher.