# 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
1. Clone this repository:
```bash
git clone <repository-url>
cd postgres-mcp
```
2. Install dependencies using `uv`:
```bash
uv sync
```
## Configuration
### Environment Variables
Create a `.env` file in the project root (use `.env.example` as a template):
```bash
# 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:
```sql
-- 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
```bash
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`
```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`:
```json
{
"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:**
```json
{
"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:**
```json
{
"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:**
```json
{
"schema": "public"
}
```
**Parameters:**
- `schema` (optional): Filter tables by schema name
**Output:**
```json
{
"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:**
```json
{
"table_name": "users",
"schema": "public"
}
```
**Parameters:**
- `table_name` (required): Name of the table
- `schema` (optional): Schema name (default: `public`)
**Output:**
```json
{
"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:**
```json
{}
```
**Output:**
```json
{
"schemas": ["public", "auth", "analytics"]
}
```
### 5. get_table_indexes
Get all indexes for a specific table.
**Input:**
```json
{
"table_name": "users",
"schema": "public"
}
```
**Parameters:**
- `table_name` (required): Name of the table
- `schema` (optional): Schema name (default: `public`)
**Output:**
```json
{
"indexes": [
{
"name": "users_pkey",
"type": "btree",
"columns": ["id"],
"unique": true,
"primary": true
}
]
}
```
### 6. get_query_history
Retrieve recent query history with execution metadata.
**Input:**
```json
{
"limit": 20
}
```
**Parameters:**
- `limit` (optional): Maximum queries to return (default: 20, max: 100)
**Output:**
```json
{
"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:**
```json
{}
```
**Output:**
```json
{
"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:
```python
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
```bash
# 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]