Skip to main content
Glama
bluwork

Postgres Scout MCP

by bluwork
OverviewSchemaRelated ServersScoreDiscussions
TypeScript
Hybrid

Postgres Scout MCP

Model Context Protocol server for safe PostgreSQL database interaction. Enables AI assistants to explore, analyze, and maintain PostgreSQL databases with built-in safety features.

Features

  • Safety First: Read-only mode by default, explicit opt-in for write operations

  • SQL Injection Prevention: All queries use parameterization

  • Rate Limiting: Prevent accidental DoS attacks

  • Comprehensive Logging: Audit trail of all operations

  • Query Timeouts: Configurable timeout protection

  • Connection Pooling: Efficient database resource management

Installation

pnpm install pnpm build

Quick Start

Read-Only Mode (Default)

Safe for production database exploration:

node dist/index.js postgresql://localhost:5432/mydb

Read-Write Mode

Requires explicit flag:

node dist/index.js --read-write postgresql://localhost:5432/mydb

Configuration

Environment Variables

# Database Connection DATABASE_URL=postgresql://user:password@localhost:5432/dbname # Security QUERY_TIMEOUT=30000 # milliseconds (default: 30s) MAX_RESULT_ROWS=10000 # prevent memory exhaustion ENABLE_RATE_LIMIT=true RATE_LIMIT_MAX_REQUESTS=100 RATE_LIMIT_WINDOW_MS=60000 # 1 minute # Logging LOG_DIR=./logs LOG_LEVEL=info # debug, info, warn, error # Connection Pool PGMAXPOOLSIZE=10 PGMINPOOLSIZE=2 PGIDLETIMEOUT=10000

Claude Desktop Configuration

Add to your Claude Desktop config file:

{ "mcpServers": { "postgres-scout-readonly": { "command": "node", "args": [ "/absolute/path/to/postgres-scout-mcp/dist/index.js", "postgresql://localhost:5432/production" ], "type": "stdio" }, "postgres-scout-dev": { "command": "node", "args": [ "/absolute/path/to/postgres-scout-mcp/dist/index.js", "--read-write", "postgresql://localhost:5432/development" ], "type": "stdio" } } }

Available Tools

Database Operations

listDatabases

List all databases the user has access to.

{}

getDatabaseStats

Get comprehensive database statistics.

{ "database": "production" }

Note: the database parameter must match the current connection; reconnect to target a different database.

Schema Operations

listSchemas

List all schemas in the database.

{}

listTables

List tables with detailed information.

{ "schema": "public", "includeSystemTables": false }

Notes:

  • rowEstimate is based on PostgreSQL statistics; when needsAnalyze is true, run ANALYZE for a reliable estimate.

describeTable

Get comprehensive table information including columns, constraints, and indexes.

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

Query Operations

executeQuery

Execute SELECT queries with safety checks.

{ "query": "SELECT id, email FROM users WHERE status = $1 LIMIT 10", "params": ["active"], "timeout": 5000, "maxRows": 1000 }

explainQuery

Analyze query performance with EXPLAIN ANALYZE. In read-only mode, analyze is forced to false to avoid executing statements.

{ "query": "SELECT * FROM users WHERE email = $1", "params": ["user@example.com"], "analyze": true, "verbose": true, "buffers": true }

Maintenance & Diagnostics

getHealthScore

Calculate overall database health score.

{ "database": "production" }

Note: the database parameter must match the current connection; reconnect to target a different database.

getSlowQueries

Analyze slow queries using pg_stat_statements (extension required).

{ "minDurationMs": 100, "limit": 10, "orderBy": "total_time" }

Enum Parameter Reference

  • exportTable.format: csv, json, jsonl, sql

  • getSlowQueries.orderBy: total_time, mean_time, calls

  • getLiveMetrics.metrics: queries, connections, locks, transactions, cache

  • getHottestTables.orderBy: seq_scan, idx_scan, writes, size

  • detectAnomalies.type: query_performance, data_volume, connections, errors, all

  • detectAnomalies.sensitivityLevel: low, medium, high

  • analyzeTimeSeries.groupBy: hour, day, week, month

  • analyzeTimeSeries.aggregation: sum, avg, count, min, max

  • detectSeasonality.groupBy: day_of_week, day_of_month, month, quarter

Usage Examples

Exploring Database Schema

User: Show me all tables in the public schema AI: [Uses listTables tool] AI: Found 45 tables in the public schema: - users (150,000 rows, 25 MB) - orders (2M rows, 450 MB) - products (5,000 rows, 2 MB) ...

Analyzing Query Performance

User: Why is this query slow: SELECT * FROM orders WHERE customer_id = 123 AI: [Uses explainQuery tool] AI: The query is doing a sequential scan on 2M rows. Recommendation: Create an index: CREATE INDEX CONCURRENTLY idx_orders_customer_id ON orders(customer_id); This should reduce query time from ~2500ms to ~5ms.

Database Health Check

User: How healthy is my database? AI: [Uses getDatabaseStats tool] AI: Database Statistics: - Size: 1.2 GB - Tables: 45 - Cache Hit Ratio: 98% (Excellent) - Active Connections: 12/100 - Transaction Rate: 150/sec

Security

Read-Only vs Read-Write Mode

Read-Only Mode (default):

  • SELECT queries

  • EXPLAIN ANALYZE

  • Schema inspection

  • Performance monitoring

Read-Write Mode (requires --read-write flag):

  • All read-only operations

  • INSERT, UPDATE, DELETE

  • CREATE, ALTER, DROP

  • VACUUM, ANALYZE

SQL Injection Prevention

Multiple layers of protection:

  1. Parameterized Queries: All user data passed as parameters

  2. Input Validation: WHERE clauses, conditions, and ORDER BY validated for dangerous patterns

  3. Identifier Sanitization: Table/column names validated against whitelist pattern

  4. Pattern Detection: Blocks SQL comments, UNION SELECT, and other injection patterns

// Safe - parameterized executeQuery({ query: "SELECT * FROM users WHERE email = $1", params: ["user@example.com"] }) // Safe - validated WHERE clause previewUpdate({ table: "users", where: "status = 'active' AND created_at > '2024-01-01'" }) // SafeUpdate: raw SET strings are opt-in safeUpdate({ table: "users", set: "status = 'inactive'", where: "last_login < NOW() - INTERVAL '1 year'", allowRawSet: true }) // Blocked - dangerous patterns previewUpdate({ table: "users", where: "1=1; DROP TABLE users --" // Error: dangerous pattern detected })

Rate Limiting

Prevents accidental DoS:

  • Default: 100 requests per minute

  • Configurable via environment variables

  • Can be disabled for trusted environments

Query Timeouts

All queries have configurable timeouts:

  • Default: 30 seconds

  • Prevents long-running queries

  • Protects database resources

Development

Build

pnpm build

Watch Mode

pnpm watch

Project Structure

postgres-scout-mcp/ ├── src/ │ ├── index.ts # Entry point │ ├── types.ts # TypeScript types │ ├── server/ │ │ └── setup.ts # MCP server configuration │ ├── tools/ │ │ ├── index.ts # Tool registration │ │ ├── database.ts # Database operations │ │ ├── schema.ts # Schema inspection │ │ └── query.ts # Query execution │ ├── utils/ │ │ ├── logger.ts # Logging │ │ ├── sanitize.ts # SQL injection prevention │ │ ├── query-builder.ts # Query construction │ │ ├── rate-limiter.ts # Rate limiting │ │ ├── database.ts # Connection management │ │ └── result-formatter.ts │ └── config/ │ └── environment.ts # Configuration ├── dist/ # Compiled output ├── logs/ # Log files └── bin/ └── cli.js # CLI wrapper

Troubleshooting

Connection Issues

Error: Database connection failed

Solutions:

  • Verify connection string format: postgresql://user:password@host:port/database

  • Check database server is running

  • Verify network connectivity

  • Check firewall rules

  • Verify credentials

Permission Errors

Error: permission denied for table users

Solutions:

  • Verify database user has necessary permissions

  • In read-only mode, SELECT permission is required

  • In read-write mode, additional permissions needed

  • Contact database administrator

Rate Limit Exceeded

Error: Rate limit exceeded. Try again in 30 seconds.

Solutions:

  • Wait for the rate limit window to expire

  • Increase RATE_LIMIT_MAX_REQUESTS if needed

  • Disable rate limiting for trusted environments: ENABLE_RATE_LIMIT=false

Logging

All operations are logged to:

  • logs/tool-usage.log - All tool executions

  • logs/error.log - Errors only

  • Console (stderr) - Real-time output

Log format:

2025-01-17T10:30:00Z [INFO] Tool: executeQuery, Message: Query executed successfully, Data: {"rowCount": 10, "executionTimeMs": 12}

Implemented Features

Core Features ✅

  • Database operations (list databases, stats, health scoring)

  • Schema inspection (tables, columns, constraints, indexes)

  • Query execution with safety checks

  • Query performance analysis (EXPLAIN ANALYZE)

Data Quality Tools ✅

  • Find duplicates

  • Find missing values (NULL analysis)

  • Find orphaned records

  • Check constraint violations

  • Analyze type consistency

Export Tools ✅

  • Export to CSV, JSON, JSONL, SQL

  • Generate INSERT statements with batching

Temporal Tools ✅

  • Find recent records

  • Time series analysis with anomaly detection

  • Seasonality detection

Monitoring Tools ✅

  • Current activity monitoring

  • Lock analysis

  • Index usage analysis

Mutation Tools ✅ (read-write mode)

  • Preview UPDATE/DELETE operations

  • Safe UPDATE with row limits

  • Safe DELETE with row limits

Roadmap

Future Enhancements

  • AI-powered index recommendations

  • Partitioning suggestions

  • Bloat analysis and VACUUM recommendations

  • Query optimization suggestions

License

ISC

Contributing

Contributions welcome! Areas of focus:

  • Additional tools and features

  • Performance optimizations

  • Better error messages

  • Documentation improvements

  • Test coverage

Support

This server cannot be installed

-
security - not tested
A
license - permissive license
-
quality - not tested

How are these scores calculated?

Resources

New MCP Servers

Latest Blog Posts

MCP directory API

We provide all the information about MCP servers via our MCP API.

curl -X GET 'https://glama.ai/api/mcp/v1/servers/bluwork/postgres-scout-mcp'

If you have feedback or need assistance with the MCP directory API, please join our Discord server