postgres_mcp

Purpose

This is an MCP server for PostgreSQL database operations, usable with Claude Code, Claude Desktop, Cursor, and any MCP-compatible client. The repository contains both TypeScript and Python implementations, which are fully functionally identical; the TypeScript version is used for Smithery deployment. The TypeScript package requires Node.js 20+, and the Python package requires Python 3.10+.

Main Capabilities

• 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 protection, credential protection, read-only by default.
• MCP Prompts: Provide guided workflows for exploration, query building, and documentation writing.
• MCP Resources: Provide browsable database structures in markdown form.

Usage

Environment Variables and Multiple Databases

In real work, it is often necessary to work with multiple databases. Instead of modifying the configuration and restarting the client each time, you can declare multiple connections and select one by alias on each call:

• list_databases Returns the configured aliases and their host, database name, write permission, and default connection. Credentials are not returned.
• Except for list_databases , every tool accepts an optional database parameter to specify the alias. When omitted, the default connection is used, so existing single-database configurations continue to work without any changes.
• allowWrite is set per connection, falling back to ALLOW_WRITE_OPERATIONS. Production replicas remain read-only, while local databases can allow writes.
• default: true marks the connection used when database is omitted; otherwise, the first declared connection is used. At most one connection can be marked as default.
• Each connection can accept discrete fields (host、port、user、password、database、sslmode) or a url DSN; discrete fields override the DSN, so you can reuse a URL and modify only part of it.
• One connection pool per alias, opened lazily: configured but unused databases do not open sockets.
• POSTGRES_CONNECTIONS is mutually exclusive with the regular POSTGRES_* variable. When the former is set, the latter is ignored, so there is no question about which one takes effect.
• Credentials come only from environment variables.database The parameter names an alias; deliberately, passing host, user, password, or connection string as a tool parameter is not supported, because that would bring secrets into the conversation. This is constrained by tests.

Client Configuration

Claude Code CLI and ~/.cursor/mcp.json can both be configured for use (the relevant code snippets in the materials are not fully shown, so the specific JSON content is not expanded here).

Available Tools (15 total)

Each of the following tools (except list_databases ) accepts an optional database parameter to name the configured connection.

• Query execution
• Schema exploration
• Performance and analysis

Guided workflows and browsable structure

Provides guided workflows that help Claude assist effectively, along with a browsable database structure.

When to use

Once configured, you can ask Claude to perform the following tasks:

Schema exploration:

• "List all tables in the public schema"
• "Describe the structure of the users table"
• "What views are available?"
• "Show the first 10 rows of the orders table"
• "Find all customers who placed an order last week"
• "Count records grouped by status"

Performance analysis:

• "What indexes are on the orders table?"
• "Analyze the performance of the users table"
• "Explain this query: SELECT * FROM orders WHERE created_at > '2024-01-01'"
• "Generate a data dictionary for this database"
• "Which columns have 'email' in their name?"

Limitations and security

This MCP server implements multiple layers of security:

• Read-only by default: unless explicitly enabled via ALLOW_WRITE_OPERATIONS=true , write operations (INSERT, UPDATE, DELETE) are blocked.
• SQL injection protection: all queries are validated before execution; dangerous operations (such as DROP DATABASE, etc.) are always blocked; multiple statements are not allowed; SQL comments are blocked.
• Credential protection: passwords are stored using a secure string type; credentials do not appear in logs or error messages; results are limited by MAX_ROWS (default 1000); query timeout can be configured via QUERY_TIMEOUT .

Troubleshooting

• Connection issues
• Permission denied: ensure the database user has SELECT privileges
• MCP server cannot connect

For more information, refer to the installation guide and the GitHub repository. The author is Javier Aguilar, an AI Agent architect specializing in multi-agent orchestration and MCP development.