Metadata-Version: 2.4
Name: mcp-postgres-server
Version: 0.1.2
Summary: A production-ready MCP server that exposes PostgreSQL databases via the Model Context Protocol
Project-URL: Homepage, https://github.com/madmarin/mcp-postgres-server
Project-URL: Repository, https://github.com/madmarin/mcp-postgres-server
Project-URL: Issues, https://github.com/madmarin/mcp-postgres-server/issues
Author-email: Alexander Marin <marinalexander691@gmail.com>
License: MIT License
        
        Copyright (c) 2026 Alexander Marin
        
        Permission is hereby granted, free of charge, to any person obtaining a copy
        of this software and associated documentation files (the "Software"), to deal
        in the Software without restriction, including without limitation the rights
        to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
        copies of the Software, and to permit persons to whom the Software is
        furnished to do so, subject to the following conditions:
        
        The above copyright notice and this permission notice shall be included in all
        copies or substantial portions of the Software.
        
        THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
        IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
        FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
        AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
        LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
        OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
        SOFTWARE.
License-File: LICENSE
Keywords: ai,claude,database,llm,mcp,postgres,postgresql
Classifier: Development Status :: 3 - Alpha
Classifier: Intended Audience :: Developers
Classifier: License :: OSI Approved :: MIT License
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Scientific/Engineering :: Artificial Intelligence
Requires-Python: >=3.11
Requires-Dist: loguru>=0.7.0
Requires-Dist: mcp>=1.0.0
Requires-Dist: psycopg-pool>=3.2.0
Requires-Dist: psycopg[binary]>=3.1.0
Requires-Dist: pydantic-settings>=2.1.0
Requires-Dist: pydantic>=2.5.0
Requires-Dist: python-dotenv>=1.0.0
Provides-Extra: dev
Requires-Dist: mypy>=1.8.0; extra == 'dev'
Requires-Dist: pre-commit>=3.6.0; extra == 'dev'
Requires-Dist: pytest-asyncio>=0.23.0; extra == 'dev'
Requires-Dist: pytest-cov>=4.1.0; extra == 'dev'
Requires-Dist: pytest-mock>=3.12.0; extra == 'dev'
Requires-Dist: pytest>=8.0.0; extra == 'dev'
Requires-Dist: ruff>=0.3.0; extra == 'dev'
Provides-Extra: test
Requires-Dist: pytest-asyncio>=0.23.0; extra == 'test'
Requires-Dist: pytest-cov>=4.1.0; extra == 'test'
Requires-Dist: pytest-mock>=3.12.0; extra == 'test'
Requires-Dist: pytest>=8.0.0; extra == 'test'
Requires-Dist: testcontainers[postgres]>=4.0.0; extra == 'test'
Description-Content-Type: text/markdown

# mcp-postgres-server

A production-ready [Model Context Protocol (MCP)](https://modelcontextprotocol.io) server that gives AI assistants (Claude, etc.) direct, safe access to your PostgreSQL database.

[![CI](https://github.com/madmarin/mcp-postgres-server/actions/workflows/ci.yml/badge.svg)](https://github.com/madmarin/mcp-postgres-server/actions/workflows/ci.yml)
[![PyPI](https://img.shields.io/pypi/v/mcp-postgres-server)](https://pypi.org/project/mcp-postgres-server/)
[![Python](https://img.shields.io/pypi/pyversions/mcp-postgres-server)](https://pypi.org/project/mcp-postgres-server/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](LICENSE)

---

## What it does

`mcp-postgres-server` exposes five tools to any MCP-compatible client:

| Tool | Description |
|------|-------------|
| `query` | Execute a read-only `SELECT` statement and get JSON results |
| `execute` | Run a write statement (`INSERT`, `UPDATE`, `DELETE`, DDL) — requires `ALLOW_WRITE=true` |
| `list_schemas` | List all user schemas in the database |
| `list_tables` | List tables/views in a schema with row estimates and sizes |
| `describe_table` | Get columns, primary key, foreign keys, and indexes for a table |

---

## Quickstart

### 1. Install

```bash
pip install mcp-postgres-server
```

Or install from source:

```bash
git clone https://github.com/madmarin/mcp-postgres-server
cd mcp-postgres-server
pip install -e .
```

### 2. Configure

Copy `.env.example` to `.env` and fill in your connection details:

```bash
cp .env.example .env
```

Minimum required:

```env
DATABASE_URL=postgresql+psycopg://user:password@localhost:5432/mydb
```

If your password contains special characters (for example `@`, `:`, `/`, `#`, `%`), URL-encode it in `DATABASE_URL`.

Example:

```env
# Raw password: p@ss:w0rd/with#chars%
DATABASE_URL=postgresql+psycopg://postgres:p%40ss%3Aw0rd%2Fwith%23chars%25@localhost:5432/mydb
```

You can encode safely with Python:

```bash
python3 -c "import urllib.parse; print(urllib.parse.quote('p@ss:w0rd/with#chars%', safe=''))"
```

### 3. Run

```bash
mcp-postgres-server
```

The server starts in `stdio` mode by default, ready to be used by any MCP client.

---

## Add to Claude Desktop

Edit `~/Library/Application Support/Claude/claude_desktop_config.json` (macOS) or `%APPDATA%\Claude\claude_desktop_config.json` (Windows):

```json
{
  "mcpServers": {
    "postgres": {
      "command": "mcp-postgres-server",
      "env": {
        "DATABASE_URL": "postgresql+psycopg://user:password@localhost:5432/mydb"
      }
    }
  }
}
```

> **macOS note:** Claude Desktop uses a restricted PATH and may not find the command by name. If you get a `Server disconnected` error, use the full path instead:
>
> ```bash
> which mcp-postgres-server
> # e.g. /Library/Frameworks/Python.framework/Versions/3.14/bin/mcp-postgres-server
> ```
>
> Then use that full path as the `"command"` value in the config above.

Restart Claude Desktop — you will see the PostgreSQL tools available.

---

## Add to Claude Code (CLI)

```bash
claude mcp add postgres -- mcp-postgres-server
```

Then set the environment variable:

```bash
export DATABASE_URL="postgresql+psycopg://user:password@localhost:5432/mydb"
```

---

## Configuration Reference

All settings are read from environment variables or a `.env` file in the working directory.

| Variable | Default | Description |
|----------|---------|-------------|
| `DATABASE_URL` | — | Full connection string (`postgresql+psycopg://...`). Takes priority over individual fields. |
| `POSTGRES_HOST` | `localhost` | Host (used if `DATABASE_URL` is not set) |
| `POSTGRES_PORT` | `5432` | Port |
| `POSTGRES_DB` | `postgres` | Database name |
| `POSTGRES_USER` | `postgres` | Username |
| `POSTGRES_PASSWORD` | — | Password |
| `MCP_SERVER_NAME` | `mcp-postgres-server` | Name reported to MCP clients (logical MCP server name, not the CLI command) |
| `MCP_TRANSPORT` | `stdio` | Transport: `stdio` or `sse` |
| `LOG_LEVEL` | `INFO` | `DEBUG`, `INFO`, `WARNING`, `ERROR` |
| `ALLOW_WRITE` | `false` | Set to `true` to enable the `execute` tool |
| `POOL_MIN_SIZE` | `2` | Minimum connections in pool |
| `POOL_MAX_SIZE` | `10` | Maximum connections in pool |
| `QUERY_TIMEOUT` | `30.0` | Per-statement timeout in seconds |

---

## Tool Reference

### `query(sql, params?)`

Execute a read-only SELECT statement.

```
Input:
  sql     — SELECT statement or CTE
  params  — optional list of values for parameterized queries

Output (JSON):
  {
    "columns": ["id", "name", "age"],
    "rows": [[1, "Alice", 30], [2, "Bob", 25]],
    "row_count": 2,
    "execution_time_ms": 3.14
  }
```

**Always use `params` for user-supplied values** — never interpolate them into the SQL string.

```python
# Safe
query("SELECT * FROM users WHERE name = %s", ["Alice"])

# Never do this
query(f"SELECT * FROM users WHERE name = '{user_input}'")
```

---

### `execute(sql, params?)`

Run a write statement. Requires `ALLOW_WRITE=true`.

```
Output (JSON):
  {
    "rows_affected": 1,
    "status": "INSERT 0 1",
    "execution_time_ms": 2.5
  }
```

---

### `list_schemas()`

```
Output (JSON):
  [
    {"schema_name": "public", "owner": "postgres"},
    {"schema_name": "analytics", "owner": "alice"}
  ]
```

---

### `list_tables(schema?)`

```
Output (JSON):
  [
    {
      "table_name": "users",
      "table_type": "BASE TABLE",
      "row_estimate": 12345,
      "total_size": "2048 kB"
    }
  ]
```

---

### `describe_table(table, schema?)`

```
Output (JSON):
  {
    "table": "users",
    "schema": "public",
    "columns": [
      {"name": "id", "type": "integer", "nullable": false, "default": "nextval(...)"},
      {"name": "name", "type": "text", "nullable": false, "default": null}
    ],
    "primary_key": ["id"],
    "foreign_keys": [],
    "indexes": [
      {"name": "users_pkey", "definition": "CREATE UNIQUE INDEX ..."}
    ]
  }
```

---

## Security Model

- **Read-only by default**: the `execute` tool is disabled unless you explicitly set `ALLOW_WRITE=true`. This prevents accidental mutations.
- **Parameterized queries**: all tools use psycopg's parameterized query API. SQL is never built by string concatenation.
- **Denylist**: even with `ALLOW_WRITE=true`, certain destructive patterns (`DROP DATABASE`, `ALTER SYSTEM`, etc.) are blocked.
- **Error isolation**: internal error details (stack traces, SQL) are logged to stderr and never returned to the LLM.

---

## Development

```bash
git clone https://github.com/madmarin/mcp-postgres-server
cd mcp-postgres-server
python -m venv .venv && source .venv/bin/activate
pip install -e ".[dev,test]"
pre-commit install
```

Run the tests (requires Docker for testcontainers):

```bash
pytest
```

Lint and format:

```bash
ruff check src tests
ruff format src tests
mypy src
```

See [CONTRIBUTING.md](CONTRIBUTING.md) for details on how to add new tools or submit a PR.

---

## License

[MIT](LICENSE)
