mdev-postgresql-mcp-server 0.1.0

Model Context Protocol server for PostgreSQL database inspection and querying

PostgreSQL MCP Server

A Python Model Context Protocol (MCP) server for inspecting and querying PostgreSQL databases from MCP-compatible clients. It provides schema discovery, safe read-only query execution, query explanation, table previews, index analysis, relationship inspection, and PostgreSQL resources for table metadata.

Features

• List public tables and inspect table schemas
• Execute read-only SQL in a PostgreSQL read-only transaction
• Explain query plans without executing the target query directly
• Preview table rows with a fixed limit
• Inspect foreign-key relationships and indexes
• Expose passive MCP resources for table lists and schema details

Safety Model

postgresql_execute_read_query runs with PostgreSQL read-only transaction mode, caps returned rows by POSTGRES_READ_QUERY_LIMIT, and rolls back after execution. The server also includes postgresql_execute_write_query, which only accepts a single INSERT, UPDATE, DELETE, CREATE, ALTER, DROP, or TRUNCATE statement and can modify data/schema if the connected database user has permission. Do not auto-approve write-capable tools in your MCP client. For public or shared use, run the server with a dedicated read-only PostgreSQL user.

Requirements

• Python 3.11+
• PostgreSQL database
• MCP-compatible client such as Claude Desktop, Cursor, VS Code, or another MCP host

Installation

When published to PyPI, install or run the server like a standard Python MCP package:

uvx mdev-postgresql-mcp-server

For local development from source:

git clone https://github.com/musaddiq-dev/postgresql-mcp-server.git
cd postgresql-mcp-server
python -m venv .venv
source .venv/bin/activate
pip install -e .

Configuration

Copy the example environment file and update it with your database connection details.

cp .env.example .env

Variable	Description	Required	Default
POSTGRES_HOST	PostgreSQL host	Yes	localhost
POSTGRES_PORT	PostgreSQL port	Yes	5432
POSTGRES_USER	PostgreSQL username	Yes	None
POSTGRES_PASSWORD	PostgreSQL password	No	None
POSTGRES_DB	PostgreSQL database name	Yes	None
LOG_LEVEL	Python logging level written to stderr	No	INFO
POSTGRES_READ_QUERY_LIMIT	Maximum rows returned by read queries	No	1000

Example read-only user:

CREATE USER mcp_readonly WITH PASSWORD 'change-me';
GRANT CONNECT ON DATABASE your_database TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_readonly;

Running

mdev-postgresql-mcp-server

From a local checkout before PyPI publication, run:

python -m postgresql_mcp_server.server

MCP Client Configuration

Use an absolute path to the installed console script. MCP servers using stdio must write protocol messages only to stdout; this server writes logs to stderr through Python logging.

{
  "mcpServers": {
    "postgresql": {
      "command": "/absolute/path/to/postgresql-mcp-server/.venv/bin/mdev-postgresql-mcp-server",
      "args": [],
      "env": {
        "POSTGRES_HOST": "localhost",
        "POSTGRES_PORT": "5432",
        "POSTGRES_USER": "mcp_readonly",
        "POSTGRES_PASSWORD": "change-me",
        "POSTGRES_DB": "your_database"
      }
    }
  }
}

Tools

Tool	Purpose	Safety
postgresql_list_tables	List public base tables	Read-only
postgresql_describe_table	Show columns and metadata for a table	Read-only
postgresql_execute_read_query	Run bounded SQL under read-only transaction mode	Read-only
postgresql_execute_write_query	Run a single approved modifying SQL statement and commit	Destructive
postgresql_explain_query	Return PostgreSQL EXPLAIN output for a single query	Read-only
postgresql_get_database_summary	Return database version and table count	Read-only
postgresql_get_relationships	Inspect foreign-key relationships	Read-only
postgresql_analyze_indexes	Inspect indexes and sizes	Read-only
postgresql_preview_table	Return up to 10 rows from a table	Read-only
postgresql_search_sql_definitions	Search public SQL routines/functions	Read-only

Resources

• postgres://list_tables returns public table names.
• postgres://schema/{table_name} returns a generated schema statement for a table.

Smoke Check

Without a database, verify syntax with:

python -m py_compile src/postgresql_mcp_server/server.py

With a configured database, start the server and use your MCP client to call list_tables.

Distribution

This repository is prepared for the common Python MCP distribution path: publish the package to PyPI, keep the mcp-name marker at the top of this README for MCP Registry ownership verification, and publish server.json metadata with the GitHub repository. After release, users should prefer uvx mdev-postgresql-mcp-server in local MCP client configurations.

Security Notes

• Do not commit .env or MCP client configs containing credentials.
• Use least-privilege database users.
• Treat execute_write_query as destructive and require explicit user approval in your MCP client.
• Review generated SQL before running write-capable tools.

License

MIT