Metadata-Version: 2.4
Name: pgsql-mcp
Version: 0.1.0
Summary: A Model Context Protocol server for PostgreSQL with read-only default and super-code write access.
License: MIT
License-File: LICENSE
Requires-Python: >=3.12
Requires-Dist: mcp[cli]>=1.2.0
Requires-Dist: psycopg[binary]>=3.2.0
Description-Content-Type: text/markdown

# pgsql-mcp

A Model Context Protocol (MCP) server for PostgreSQL. Provides read-only database access by default, with optional write operations gated behind a super code.

## Overview

pgsql-mcp exposes PostgreSQL databases to MCP-compatible AI clients (such as Claude, Kiro, and Cursor) through a set of structured tools. It supports schema inspection, table browsing, row fetching, arbitrary query execution, and stored function/procedure invocation.

Write and DDL operations are disabled unless a super code is provided at server startup. This ensures safe, read-only access in environments where unrestricted database modification is not desired.

## Installation

```bash
pip install pgsql-mcp
```

## Usage

Start the server with a PostgreSQL DSN:

```bash
# Read-only mode (default)
pgsql-mcp --dsn "postgresql://user:password@localhost:5432/mydb"

# With write access enabled
pgsql-mcp --dsn "postgresql://user:password@localhost:5432/mydb" --super-code "your-secret"
```

### Command-Line Arguments

| Argument | Required | Description |
|----------|----------|-------------|
| `--dsn` | Yes | PostgreSQL connection string in DSN format |
| `--super-code` | No | Secret code to unlock write and DDL operations |

## MCP Client Configuration

Add the following to your MCP client configuration file:

```json
{
  "mcpServers": {
    "postgres": {
      "command": "pgsql-mcp",
      "args": [
        "--dsn", "postgresql://user:password@localhost:5432/mydb"
      ],
      "autoApprove": [
        "list_schemas",
        "list_tables",
        "describe_table",
        "fetch_rows",
        "run_query",
        "call_function"
      ]
    }
  }
}
```

## Tools

### list_schemas

List all non-system schemas in the database.

**Parameters:** None

### list_tables

List all tables in a given schema.

| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| schema | string | "public" | Schema name |

### describe_table

Describe columns of a table, including name, data type, nullability, and default value.

| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| table | string | required | Table name |
| schema | string | "public" | Schema name |

### fetch_rows

Fetch rows from a table with optional column selection, WHERE clause, and row limit.

| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| table | string | required | Table name |
| columns | string | "*" | Comma-separated column names or "*" |
| where | string | None | SQL WHERE clause (without the WHERE keyword) |
| limit | integer | 100 | Maximum number of rows to return |
| schema | string | "public" | Schema name |

### run_query

Execute an arbitrary SQL query. Read queries are permitted without authentication. Write and DDL queries require the super code.

| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| sql | string | required | SQL statement to execute |
| code | string | None | Super code for write operations |

### call_function

Call a PostgreSQL function or procedure and return its result. Functions are invoked with SELECT; procedures are invoked with CALL.

| Parameter | Type | Default | Description |
|-----------|------|---------|-------------|
| function_name | string | required | Name of the function or procedure |
| args | list | None | List of arguments (passed as literal strings) |
| is_procedure | boolean | false | Use CALL instead of SELECT |
| schema | string | "public" | Schema name |

## Security Model

- By default, the server operates in read-only mode. All SELECT queries, schema inspection, and function calls are permitted.
- Write operations (INSERT, UPDATE, DELETE) and DDL statements (CREATE, ALTER, DROP, TRUNCATE, GRANT, REVOKE) are blocked unless the server is started with `--super-code`.
- When write access is enabled, the caller must pass the matching code in the `run_query` tool's `code` parameter.

## Requirements

- Python 3.12 or later
- PostgreSQL database accessible via the provided DSN
- Dependencies: `mcp[cli]`, `psycopg[binary]`

## License

MIT
