pg-mcp-server

A Python Model Context Protocol (MCP) server that connects to PostgreSQL, exposes read-only database tools to any MCP-compatible LLM client, and includes an Ollama natural-language → SQL → plain-English answer pipeline — complete with a browser-based Test UI.

Ask: "Which product had the highest total sales?"
  ↓ Ollama generates SQL
  ↓ PostgreSQL executes it (read-only, guarded)
  ↓ Ollama summarizes results
Answer: "Smart Watch Series 5 led total sales with ₹23,997 across 3 orders."

Features

9 MCP tools — schema inspection, raw SELECT, safe aggregates, NL→SQL via Ollama
Read-only by design — PostgreSQL role + session-level lock + QueryGuard validation
Zero ODBC — uses psycopg2-binary directly, works on macOS / Linux / Windows
Browser Test UI — 4-panel app (Chat, SQL Editor, Schema Explorer, Aggregates)
Works with any MCP client — Claude Desktop, Cursor, Continue.dev, or custom clients
Dual transport — stdio for LLM clients, SSE for HTTP/browser access

Architecture

┌──────────────────────────────────────────────────────────┐
│                       LLM Client                         │
│     Claude Desktop · Cursor · Continue.dev · Custom      │
└───────────────────────┬──────────────────────────────────┘
                        │  MCP Protocol (stdio / SSE)
┌───────────────────────▼──────────────────────────────────┐
│                  server.py  (MCP Server)                 │
│                                                          │
│   Tools registered via FastMCP:                          │
│     execute_sql_query    list_tables    get_table_schema  │
│     list_databases       aggregate_table  get_row_count   │
│     get_top_rows         ask_database   list_ollama_models│
│                                                          │
│   Services:                                              │
│     PgService ──────────────► PostgreSQL  (psycopg2)     │
│     OllamaService ──────────► Ollama HTTP API            │
│                                                          │
│   Security:                                              │
│     QueryGuard (whitelist / blacklist / identifier check) │
└───────────────────────┬──────────────────────────────────┘
                        │  REST  (optional HTTP bridge)
┌───────────────────────▼──────────────────────────────────┐
│              api_bridge.py  (FastAPI)                    │
│   Mirrors every MCP tool as a POST endpoint              │
│   Serves  ui/index.html  at  /ui                         │
└──────────────────────────────────────────────────────────┘

Prerequisites

Requirement	Version
Python	3.11+
PostgreSQL	13+
Ollama	Latest — ollama.ai
Ollama model	`llama3` (or any chat model)

No ODBC driver or system-level SQL client required.

Quick Start

1 — Clone and install

git clone https://github.com/MugilarasuCS/pg-mcp-server.git
cd pg-mcp-server

python -m venv .venv
source .venv/bin/activate        # Windows: .venv\Scripts\activate
pip install -r requirements.txt

2 — Set up PostgreSQL

Run the three setup scripts in order as a superuser (postgres):

psql -U postgres -f scripts/01_create_database.sql
psql -U postgres -d mcp_sample_db -f scripts/02_create_tables.sql
psql -U postgres -d mcp_sample_db -f scripts/03_seed_data.sql

This creates:

Object	Detail
Database	`mcp_sample_db`
Read-only role	`mcp_reader` / `McpReader@123!`
Tables	`customers`, `products`, `categories`, `orders`, `order_items`
Sample data	10 customers · 16 products · 12 orders · 18 order items

Change the default password in scripts/01_create_database.sql before running in production.

3 — Configure environment

cp .env.example .env

Edit .env and set at minimum:

PG_PASSWORD=McpReader@123!

Never commit .env to source control — it is already in .gitignore.

4 — Start Ollama

ollama serve
ollama pull llama3    # first time only — downloads ~4 GB

5a — Run the MCP server (stdio)

python server.py

5b — Run the HTTP bridge + Test UI

uvicorn api_bridge:app --host 0.0.0.0 --port 8000 --reload

Open http://localhost:8000/ui in your browser.

MCP Client Configuration

Claude Desktop

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

{
  "mcpServers": {
    "pg-mcp-server": {
      "command": "python",
      "args": ["/absolute/path/to/pg-mcp-server/server.py"],
      "env": {
        "PG_HOST": "localhost",
        "PG_DATABASE": "mcp_sample_db",
        "PG_USER": "mcp_reader",
        "PG_PASSWORD": "McpReader@123!",
        "OLLAMA_MODEL": "llama3"
      }
    }
  }
}

Cursor / Continue.dev

Use the included mcp.json — it reads ${env:PG_PASSWORD} so the password is never hard-coded:

{
  "mcpServers": {
    "pg-mcp-server": {
      "command": "python",
      "args": ["server.py"],
      "cwd": "${workspaceFolder}",
      "env": {
        "PG_PASSWORD": "${env:PG_PASSWORD}"
      }
    }
  }
}

SSE Transport (HTTP clients)

MCP_TRANSPORT=sse python server.py
# or
uvicorn api_bridge:app --port 8000

MCP Tools Reference

Schema Tools

Tool	Parameters	Description
`list_tables`	`schema_name?`	List all tables with columns, data types, PK/FK flags, and approximate row counts
`get_table_schema`	`table_name`, `schema?`	Full column metadata for one table as Markdown + JSON
`list_databases`	—	All non-template databases on the PostgreSQL instance

Query Tools

Tool	Parameters	Description
`execute_sql_query`	`query`	Run any SELECT — guarded by `QueryGuard`, row cap, statement timeout

Aggregate Tools

Tool	Parameters	Description
`aggregate_table`	`table_name`, `function`, `column?`, `group_by_column?`, `schema?`	Safe COUNT / SUM / AVG / MIN / MAX with optional GROUP BY
`get_row_count`	`table_name`, `schema?`	Quick `COUNT(*)` for a table
`get_top_rows`	`table_name`, `top_n?`, `order_by_column?`, `order_direction?`, `schema?`	Preview first N rows with optional ORDER BY

Natural Language Tools

Tool	Parameters	Description
`ask_database`	`question`	NL question → Ollama generates SQL → executes → Ollama summarizes
`list_ollama_models`	—	List locally available Ollama models

Security Model

Security is implemented in four independent layers — any one of them alone stops unauthorized access.

Layer 1 — PostgreSQL Role Permissions

-- mcp_reader can only read
GRANT CONNECT ON DATABASE mcp_sample_db TO mcp_reader;
GRANT USAGE   ON SCHEMA public           TO mcp_reader;
GRANT SELECT  ON ALL TABLES IN SCHEMA public TO mcp_reader;

-- Write operations explicitly denied (belt-and-suspenders)
REVOKE INSERT, UPDATE, DELETE, TRUNCATE
  ON ALL TABLES IN SCHEMA public FROM mcp_reader;

Even if QueryGuard were bypassed, the database role itself cannot write.

Layer 2 — Session-Level Read-Only

conn.set_session(readonly=True, autocommit=True)

psycopg2 enforces SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY at the connection level. Any write attempt raises a ReadOnlySqlTransaction error before reaching PostgreSQL.

Layer 3 — QueryGuard (Pre-execution Validation)

src/security/query_guard.py validates every query string before execution:

Rules enforced:

Check	Detail
Must start with `SELECT`	First token checked case-insensitively
Blocked DDL/DML keywords	`DROP`, `DELETE`, `UPDATE`, `INSERT`, `TRUNCATE`, `ALTER`, `CREATE`, `GRANT`, `REVOKE`, `COPY`, `EXECUTE`, `PERFORM`, `VACUUM`, `LOCK`, `LISTEN`, `NOTIFY`, and more
Blocked pg functions	`pg_read_file`, `pg_write_file`, `pg_sleep`, `lo_import`, `lo_export`, `dblink`, `pg_execute_server_program`, `pg_reload_conf`
No SQL comments	`--` and `/* */` both rejected (classic injection vectors)
No multiple statements	Semicolon (`;`) in query string rejected
Optional table allow-list	Set `ALLOWED_TABLES=orders,customers` to restrict access
Optional schema allow-list	Set `ALLOWED_SCHEMAS=public,reporting` to restrict schemas

Layer 4 — Identifier Validation

All table, column, and schema names supplied to aggregate/top-rows helpers are validated against:

re.match(r'^[A-Za-z_][A-Za-z0-9_]*$', value)

No raw user input is ever interpolated into a SQL string — identifiers are sanitized first, then quoted with "double quotes".

Statement Timeout

Every query runs with a PostgreSQL-enforced statement timeout:

cur.execute(f"SET LOCAL statement_timeout = {self._timeout_ms}")

Default: 30 seconds — configurable via MAX_QUERY_TIMEOUT.

Environment Variables

Variable	Default	Description
`PG_HOST`	`localhost`	PostgreSQL host
`PG_PORT`	`5432`	PostgreSQL port
`PG_DATABASE`	`mcp_sample_db`	Target database
`PG_USER`	`mcp_reader`	PostgreSQL role
`PG_PASSWORD`	(required)	Role password
`OLLAMA_BASE_URL`	`http://localhost:11434`	Ollama server URL
`OLLAMA_MODEL`	`llama3`	Model name (`llama3`, `mistral`, `gemma3`, etc.)
`OLLAMA_TIMEOUT`	`120`	HTTP timeout in seconds
`MCP_TRANSPORT`	`stdio`	`stdio` or `sse`
`MAX_ROWS`	`500`	Max rows returned per query
`MAX_QUERY_TIMEOUT`	`30`	Statement timeout in seconds
`ALLOWED_SCHEMAS`	`public`	Comma-separated schema allow-list
`ALLOWED_TABLES`	(empty = all)	Comma-separated table allow-list

Test UI

The browser UI (ui/index.html) is served at /ui when the HTTP bridge is running.

Panels

Panel	Description
Ask Database	Chat interface — type a question, get SQL + plain-English answer
SQL Editor	Write and run raw SELECT queries with quick-query buttons
Schema Explorer	Collapsible table cards showing all columns, types, and constraints
Aggregates	Form-based COUNT / SUM / AVG / MIN / MAX with optional GROUP BY

Sample Prompts

How many customers do we have?
Which product had the highest total sales?
List all pending orders with customer names and amounts.
What is the total revenue by product category?
Which city has the most customers?
Show me the 5 most expensive products still in stock.
What is the average order value grouped by status?

Running Tests

Unit tests cover QueryGuard and all Pydantic models — no database or network connection required.

pytest tests/ -v

Expected output:

tests/test_query_guard.py::TestAllowed::test_simple_select         PASSED
tests/test_query_guard.py::TestAllowed::test_select_with_join      PASSED
tests/test_query_guard.py::TestBlocked::test_blocked[DROP ...]     PASSED
...
tests/test_models.py::test_query_result_markdown                   PASSED
tests/test_models.py::test_table_schema_full_name                  PASSED
...

Project Structure

pg-mcp-server/
│
├── server.py              # MCP server entry point (stdio / SSE)
├── api_bridge.py          # FastAPI HTTP bridge + static UI server
├── mcp.json               # MCP client config (Cursor / Continue.dev)
├── requirements.txt       # Python dependencies
├── pyproject.toml         # Build config + pytest settings
├── .env.example           # Environment variable template
│
├── src/
│   ├── config/
│   │   └── settings.py        # Pydantic-settings — reads from .env / env vars
│   ├── models/
│   │   └── results.py         # QueryResult, TableSchema, ColumnInfo, AggregateResult
│   ├── security/
│   │   └── query_guard.py     # SQL whitelist/blacklist + identifier validation
│   ├── services/
│   │   ├── pg_service.py      # All PostgreSQL access (psycopg2, read-only)
│   │   └── ollama_service.py  # NL→SQL→Answer pipeline via Ollama HTTP API
│   └── tools/
│       ├── query_tools.py     # MCP tool: execute_sql_query
│       ├── schema_tools.py    # MCP tools: list_tables, get_table_schema, list_databases
│       ├── aggregate_tools.py # MCP tools: aggregate_table, get_row_count, get_top_rows
│       └── nl_tools.py        # MCP tools: ask_database, list_ollama_models
│
├── ui/
│   └── index.html             # Browser Test UI (Chat · SQL · Schema · Aggregates)
│
├── scripts/
│   ├── 01_create_database.sql # Creates DB + read-only mcp_reader role
│   ├── 02_create_tables.sql   # Sample e-commerce schema
│   └── 03_seed_data.sql       # Sample data (customers, products, orders)
│
└── tests/
    ├── test_query_guard.py    # 20+ unit tests for security layer
    └── test_models.py         # Unit tests for Pydantic models

Sample Database Schema

┌─────────────┐       ┌──────────────┐       ┌──────────────┐
│  categories │       │   products   │       │  customers   │
│─────────────│       │──────────────│       │──────────────│
│ category_id │◄──┐   │ product_id   │       │ customer_id  │
│ category_name│  └───│ category_id  │       │ full_name    │
└─────────────┘       │ product_name │       │ email        │
                      │ unit_price   │       │ city         │
                      │ stock        │       │ country      │
                      └──────┬───────┘       └──────┬───────┘
                             │                      │
                      ┌──────▼───────┐      ┌───────▼──────┐
                      │ order_items  │      │    orders    │
                      │──────────────│      │──────────────│
                      │ order_item_id│      │ order_id     │
                      │ order_id     │◄─────│ customer_id  │
                      │ product_id   │      │ order_date   │
                      │ quantity     │      │ status       │
                      │ unit_price   │      │ total_amount │
                      └──────────────┘      └──────────────┘

License

MIT — see LICENSE.

Contributing

Pull requests are welcome. For major changes, open an issue first to discuss what you'd like to change. Please update tests as appropriate.

Name		Name	Last commit message	Last commit date
Latest commit History 1 Commit
scripts		scripts
src		src
tests		tests
ui		ui
.env.example		.env.example
.gitignore		.gitignore
LICENSE		LICENSE
README.md		README.md
api_bridge.py		api_bridge.py
mcp.json		mcp.json
pyproject.toml		pyproject.toml
requirements.txt		requirements.txt
server.py		server.py

Folders and files

Latest commit

History

Repository files navigation

pg-mcp-server

Features

Architecture

Prerequisites

Quick Start

1 — Clone and install

2 — Set up PostgreSQL

3 — Configure environment

4 — Start Ollama

5a — Run the MCP server (stdio)

5b — Run the HTTP bridge + Test UI

MCP Client Configuration

Claude Desktop

Cursor / Continue.dev

SSE Transport (HTTP clients)

MCP Tools Reference

Schema Tools

Query Tools

Aggregate Tools

Natural Language Tools

Security Model

Layer 1 — PostgreSQL Role Permissions

Layer 2 — Session-Level Read-Only

Layer 3 — QueryGuard (Pre-execution Validation)

Layer 4 — Identifier Validation

Statement Timeout

Environment Variables

Test UI

Panels

Sample Prompts

Running Tests

Project Structure

Sample Database Schema

License

Contributing

About

Topics

Resources

License

Uh oh!

Stars

Watchers

Forks

Releases

Packages 0

Uh oh!

Contributors

Uh oh!

Languages

Packages