Postgresql MCP

🚀 PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides Create, Read, Update, and Delete (CRUD) operations for PostgreSQL database tables.

🚀 Quick Start

This MCP server allows you to interact with a PostgreSQL database through a set of tools, performing CRUD operations on specified tables. The server uses the FastMCP library and runs in standard input-output mode, making it compatible with various MCP clients.

✨ Features

• Connect to a PostgreSQL database.
• Perform CRUD operations on specified tables.
• Implement table-level and column-level access control.
• Check the database table structure.
• Execute custom SQL queries.
• Configure through a YAML file.

📦 Installation

1. Clone this repository:

   git clone https://github.com/yourusername/postgresql-mcp.git
   cd postgresql-mcp
   

2. Install the required dependencies:

   pip install -r requirements.txt
   

📚 Documentation

Configuration

The server is configured through the config.yaml file. This file includes:

1. Database connection details.
2. Table configurations, including:
   • Which tables are accessible.
   • Which columns are allowed for operations.
   • Which operations (create, read, update, delete) are allowed.

Example configuration:

database:
  host: localhost
  port: 5432
  dbname: postgres
  user: postgres
  password: postgres
  
tables:
  - name: users
    allowed_columns:
      - id
      - name
      - email
      - created_at
    allowed_operations:
      - create
      - read
      - update
      - delete
  
  - name: products
    allowed_columns:
      - id
      - name
      - price
      - description
      - category
    allowed_operations:
      - create
      - read
      - update
      - delete

Usage

Run the MCP server:

python postgresql_mcp_server.py

The server will start in standard input-output mode, ready to receive commands from MCP clients.

Available MCP Tools

list_tables

List all tables in the configuration.

response = list_tables()

create_record

Create a new record in the specified table.

response = create_record(
    table_name="users",
    data={
        "name": "John Doe",
        "email": "john@example.com"
    }
)

read_records

Read records from the specified table with optional filtering.

response = read_records(
    table_name="users",
    filters={"name": "John Doe"},
    limit=10,
    offset=0
)

update_record

Update a record in the specified table.

response = update_record(
    table_name="users",
    record_id=1,
    data={"email": "newemail@example.com"},
    id_column="id"
)

delete_record

Delete a record from the specified table.

response = delete_record(
    table_name="users",
    record_id=1,
    id_column="id"
)

execute_query

Execute a custom SQL query.

response = execute_query(
    query="SELECT * FROM users WHERE age > %s",
    params=[18]
)

get_table_schema

Get the schema information of a specific table.

response = get_table_schema(table_name="users")

Response Format

All tools return a standard response format:

{
  "status": "success",
  "message": "Optional message",
  "records": [...],  // For read operations
  "record": {...},   // For create and update operations
  "count": 10        // For read operations
}

If an error occurs, the response status will be error, and it will include an error message.

Notes

⚠️ Important Note

• Database connection information needs to be configured according to the actual situation.
• Table and column access control strictly follows the definitions in the configuration file.
• All SQL queries need to be executed in a parameterized manner to prevent injection attacks.