pgsql-mcp 0.1.0

A Model Context Protocol server for PostgreSQL with read-only default and super-code write access.

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

pip install pgsql-mcp

Usage

Start the server with a PostgreSQL DSN:

# 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:

{
  "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