PostgreSQL MCP Server

A Model Context Protocol (MCP) server that provides read-only access to PostgreSQL databases. Execute SELECT queries, inspect database schema, and track query history with built-in safety features.

Features

Read-Only Query Execution: Execute SELECT queries with automatic read-only transaction enforcement
Multiple Output Formats: Results in JSON, CSV, or Markdown table format
Schema Inspection: List tables, describe table structures, view indexes, and explore schemas
Query History: Track recently executed queries with execution time and metadata
Connection Pooling: Efficient connection management with configurable pool sizes
Security: Query validation, SQL injection prevention, and read-only transaction guarantees
Database Statistics: View database size, table counts, and connection information

Installation

Clone this repository:

git clone <repository-url> cd postgres-mcp

Install dependencies using uv:

uv sync

Configuration

Environment Variables

Create a .env file in the project root (use .env.example as a template):

# Required: PostgreSQL Connection Parameters POSTGRES_HOST=localhost POSTGRES_PORT=5432 POSTGRES_DATABASE=myapp POSTGRES_USER=readonly_user POSTGRES_PASSWORD=secure_password # Optional: Connection Pool Configuration POSTGRES_POOL_MIN_SIZE=2 # Default: 2 POSTGRES_POOL_MAX_SIZE=10 # Default: 10 POSTGRES_COMMAND_TIMEOUT=60 # Default: 60 seconds POSTGRES_CONNECTION_TIMEOUT=10 # Default: 10 seconds # Optional: Query History Configuration QUERY_HISTORY_SIZE=100 # Default: 100 # Optional: Logging Configuration LOG_LEVEL=INFO # Default: INFO (DEBUG, INFO, WARNING, ERROR)

Database User Setup

For security, create a dedicated read-only PostgreSQL user:

-- Create read-only user CREATE USER readonly_user WITH PASSWORD 'secure_password'; -- Grant connect permission GRANT CONNECT ON DATABASE myapp TO readonly_user; -- Grant schema usage GRANT USAGE ON SCHEMA public TO readonly_user; -- Grant select on all tables GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_user; -- Grant select on future tables ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_user;

Usage

Running the Server

uv run python main.py

The server communicates via stdio and can be integrated with MCP clients like Claude Desktop.

Integrating with Claude Desktop

Add this configuration to your Claude Desktop config file:

MacOS: ~/Library/Application Support/Claude/claude_desktop_config.json Windows: %APPDATA%\Claude\claude_desktop_config.json

{ "mcpServers": { "postgres": { "command": "python", "args": ["/path/to/postgres-mcp/main.py"], "env": { "POSTGRES_HOST": "localhost", "POSTGRES_PORT": "5432", "POSTGRES_DATABASE": "myapp", "POSTGRES_USER": "readonly_user", "POSTGRES_PASSWORD": "secure_password" } } } }

Alternatively, if using uv:

{ "mcpServers": { "postgres": { "command": "uv", "args": [ "run", "--directory", "/path-to-postgres-mcp/postgres-mcp", "python", "main.py" ] } }

Available Tools

1. query_database

Execute SELECT queries on the database with formatted output.

Input:

{ "query": "SELECT * FROM users LIMIT 10", "format": "json", "timeout": 30 }

Parameters:

query (required): SQL SELECT query to execute
format (optional): Output format - json (default), csv, or markdown
timeout (optional): Query timeout in seconds (max 300)

Output:

{ "rows": [...], "row_count": 10, "columns": ["id", "name", "email"], "execution_time_ms": 45.32, "format": "json", "formatted_output": "..." }

2. list_tables

List all tables in the database with metadata.

Input:

{ "schema": "public" }

Parameters:

schema (optional): Filter tables by schema name

Output:

{ "tables": [ { "schema": "public", "name": "users", "row_count_estimate": 1500, "size": "128 KB" } ] }

3. describe_table

Get detailed table structure including columns, types, indexes, and constraints.

Input:

{ "table_name": "users", "schema": "public" }

Parameters:

table_name (required): Name of the table
schema (optional): Schema name (default: public)

Output:

{ "schema": "public", "table": "users", "columns": [ { "name": "id", "type": "integer", "nullable": false, "default": "nextval('users_id_seq')", "primary_key": true } ], "indexes": [...], "foreign_keys": [...] }

4. list_schemas

List all schemas in the database.

Input:

{}

Output:

{ "schemas": ["public", "auth", "analytics"] }

5. get_table_indexes

Get all indexes for a specific table.

Input:

{ "table_name": "users", "schema": "public" }

Parameters:

table_name (required): Name of the table
schema (optional): Schema name (default: public)

Output:

{ "indexes": [ { "name": "users_pkey", "type": "btree", "columns": ["id"], "unique": true, "primary": true } ] }

6. get_query_history

Retrieve recent query history with execution metadata.

Input:

{ "limit": 20 }

Parameters:

limit (optional): Maximum queries to return (default: 20, max: 100)

Output:

{ "queries": [ { "query": "SELECT * FROM users", "timestamp": "2025-12-04T10:30:00Z", "execution_time_ms": 45.32, "row_count": 10, "format": "json", "success": true, "error": null } ] }

7. get_database_stats

Get overall database statistics and metadata.

Input:

{}

Output:

{ "database_name": "myapp", "size": "45 MB", "table_count": 12, "connection_count": 5, "version": "PostgreSQL 15.3" }

Security Features

Read-Only Enforcement

All queries are executed within read-only transactions:

async with conn.transaction(readonly=True): result = await conn.fetch(query)

Query Validation

Queries are validated before execution to prevent:

INSERT, UPDATE, DELETE operations
DROP, CREATE, ALTER operations
TRUNCATE, GRANT, REVOKE operations
Other write/admin operations

SQL Injection Prevention

Input sanitization for table and schema identifiers
Parameterized queries where applicable
Regex-based validation of identifiers

Architecture

Components

config.py: Environment configuration and validation
database.py: Connection pool management and read-only query execution
validators.py: Query validation and sanitization
formatters.py: Result formatting (JSON, CSV, Markdown)
history.py: Thread-safe query history tracking
tools.py: MCP tool implementations
server.py: MCP server setup and lifecycle management
types.py: Pydantic models for type safety

Connection Pooling

Min Size: 2 warm connections
Max Size: 10 concurrent connections
Timeout: 60 seconds command timeout, 10 seconds connection timeout
Idle Lifetime: Automatic cleanup of inactive connections

Troubleshooting

Connection Errors

Error: "Database authentication failed"

Verify POSTGRES_USER and POSTGRES_PASSWORD are correct
Check if the user exists in PostgreSQL
Ensure the user has CONNECT permission

Error: "Database 'myapp' not found"

Verify POSTGRES_DATABASE matches an existing database
Check database name spelling

Error: "Connection refused"

Verify PostgreSQL is running on the specified host and port
Check firewall settings
Verify POSTGRES_HOST and POSTGRES_PORT are correct

Query Errors

Error: "Query contains forbidden keyword: INSERT"

This server only allows SELECT queries
Use a different tool for write operations

Error: "Table does not exist"

Verify table name and schema are correct
Use list_tables to see available tables
Check if user has SELECT permission on the table

Error: "Query execution timeout"

Query took longer than the specified timeout
Optimize the query or increase timeout parameter
Check for missing indexes on large tables

Permission Errors

Error: "permission denied for table X"

The database user lacks SELECT permission
Grant appropriate permissions (see Database User Setup)

Development

Running Tests

# Add your test commands here pytest

Project Structure

postgres-mcp/ --- .env # Configuration (not in git) --- .env.example # Configuration template --- .gitignore --- README.md --- pyproject.toml --- main.py # Entry point --- src/ --- postgres_mcp/ --- __init__.py --- config.py # Configuration --- database.py # Connection pool --- formatters.py # Output formatting --- history.py # Query history --- server.py # MCP server --- tools.py # MCP tools --- types.py # Pydantic models --- validators.py # Query validation

License

[Add your license here]

Contributing

[Add contribution guidelines here]

PostgreSQL MCP Server

PostgreSQL MCP Server

Features

Installation

Configuration

Environment Variables

Database User Setup

Usage

Running the Server

Integrating with Claude Desktop

Available Tools

1. query_database

2. list_tables

3. describe_table

4. list_schemas

5. get_table_indexes

6. get_query_history

7. get_database_stats

Security Features

Read-Only Enforcement

Query Validation

SQL Injection Prevention

Architecture

Components

Connection Pooling

Troubleshooting

Connection Errors

Query Errors

Permission Errors

Development

Running Tests

Project Structure

License

Contributing

Resources

New MCP Servers

Latest Blog Posts

MCP directory API