MCP server for PostgreSQL database operations. Works with Claude Code, Claude Desktop, Cursor, and any MCP-compatible client.
This repository contains both TypeScript and Python implementations:
| Version | Directory | Status | Installation |
|---|---|---|---|
| TypeScript | /typescript |
✅ Recommended (Smithery) | npm install -g postgresql-mcp |
| Python | /python |
✅ Stable | pipx install postgresql-mcp |
Note: The TypeScript version is used for Smithery deployments. Both versions provide identical functionality.
- Query Execution: Execute SQL queries with read-only protection by default
- Schema Exploration: List schemas, tables, views, and functions
- Table Analysis: Describe structure, indexes, constraints, and statistics
- Performance Tools: EXPLAIN queries and analyze table health
- Security First: SQL injection prevention, credential protection, read-only by default
- MCP Prompts: Guided workflows for exploration, query building, and documentation
- MCP Resources: Browsable database structure as markdown
# Install globally
npm install -g postgresql-mcp
# Or run directly with npx
npx postgresql-mcp# Install
pipx install postgresql-mcp
# Configure Claude Code
claude mcp add postgres -s user \
-e POSTGRES_HOST=localhost \
-e POSTGRES_USER=your_user \
-e POSTGRES_PASSWORD=your_password \
-e POSTGRES_DB=your_database \
-- postgresql-mcpFull Installation Guide - Includes database permissions setup, remote connections, and troubleshooting.
| Variable | Required | Default | Description |
|---|---|---|---|
POSTGRES_HOST |
localhost | Database host | |
POSTGRES_PORT |
5432 | Database port | |
POSTGRES_USER |
✅ | Database user | |
POSTGRES_PASSWORD |
✅ | Database password | |
POSTGRES_DB |
✅ | Database name | |
POSTGRES_SSLMODE |
prefer | SSL mode | |
ALLOW_WRITE_OPERATIONS |
false | Enable INSERT/UPDATE/DELETE | |
QUERY_TIMEOUT |
30 | Query timeout (seconds) | |
MAX_ROWS |
1000 | Maximum rows returned |
# TypeScript version
claude mcp add postgres -s user \
-e POSTGRES_HOST=localhost \
-e POSTGRES_USER=your_user \
-e POSTGRES_PASSWORD=your_password \
-e POSTGRES_DB=your_database \
-- npx postgresql-mcp
# Python version
claude mcp add postgres -s user \
-e POSTGRES_HOST=localhost \
-e POSTGRES_USER=your_user \
-e POSTGRES_PASSWORD=your_password \
-e POSTGRES_DB=your_database \
-- postgresql-mcpAdd to ~/.cursor/mcp.json:
{
"mcpServers": {
"postgres": {
"command": "npx",
"args": ["postgresql-mcp"],
"env": {
"POSTGRES_HOST": "localhost",
"POSTGRES_PORT": "5432",
"POSTGRES_USER": "your_user",
"POSTGRES_PASSWORD": "your_password",
"POSTGRES_DB": "your_database"
}
}
}
}| Tool | Description |
|---|---|
query |
Execute read-only SQL queries against the database |
execute |
Execute write operations (INSERT/UPDATE/DELETE) when enabled |
explain_query |
Get EXPLAIN plan for query optimization |
| Tool | Description |
|---|---|
list_schemas |
List all schemas in the database |
list_tables |
List tables in a specific schema |
describe_table |
Get table structure (columns, types, constraints) |
list_views |
List views in a schema |
describe_view |
Get view definition and columns |
list_functions |
List functions and procedures |
| Tool | Description |
|---|---|
table_stats |
Get table statistics (row count, size, bloat) |
list_indexes |
List indexes for a table |
list_constraints |
List constraints (PK, FK, UNIQUE, CHECK) |
| Tool | Description |
|---|---|
get_database_info |
Get database version and connection info |
search_columns |
Search for columns by name across all tables |
Guided workflows that help Claude assist you effectively:
| Prompt | Description |
|---|---|
explore_database |
Comprehensive database exploration and overview |
query_builder |
Help building efficient queries for a table |
performance_analysis |
Analyze table performance and suggest optimizations |
data_dictionary |
Generate documentation for a schema |
Browsable database structure:
| Resource URI | Description |
|---|---|
postgres://schemas |
List all schemas |
postgres://schemas/{schema}/tables |
Tables in a schema |
postgres://schemas/{schema}/tables/{table} |
Table details |
postgres://database |
Database connection info |
Once configured, ask Claude to:
Schema Exploration:
- "List all tables in the public schema"
- "Describe the users table structure"
- "What views are available?"
Querying:
- "Show me 10 rows from the orders table"
- "Find all customers who placed orders last week"
- "Count records grouped by status"
Performance Analysis:
- "What indexes exist on the orders table?"
- "Analyze the performance of the users table"
- "Explain this query: SELECT * FROM orders WHERE created_at > '2024-01-01'"
Documentation:
- "Generate a data dictionary for this database"
- "What columns contain 'email' in their name?"
This MCP server implements multiple security layers:
Write operations (INSERT, UPDATE, DELETE) are blocked unless explicitly enabled via ALLOW_WRITE_OPERATIONS=true.
- All queries are validated before execution
- Dangerous operations (DROP DATABASE, etc.) are always blocked
- Multiple statements are not allowed
- SQL comments are blocked
- Passwords stored using secure string types
- Credentials never appear in logs or error messages
- Results limited by
MAX_ROWS(default: 1000) - Query timeout configurable via
QUERY_TIMEOUT
cd typescript
npm install
npm run build
npm run dev # Watch modecd python
uv sync
uv run pytest -v --cov=postgres_mcp# Python unit tests (no database required)
cd python
uv run pytest tests/test_security.py tests/test_settings.py -v
# Integration tests (requires PostgreSQL)
docker-compose up -d
uv run pytest tests/test_integration.py -v# Verify PostgreSQL is running
pg_isready -h localhost -p 5432
# Test connection with psql
psql -h localhost -U your_user -d your_databaseEnsure your database user has SELECT permissions:
GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;# Check server status
claude mcp get postgres
# Test server directly
postgresql-mcp # Should wait for MCP messagesMIT