PG MCP Live

A read-only MCP server for inspecting and querying PostgreSQL databases.

pg-mcp-live exposes PostgreSQL schema information, table metadata, sample rows, safe SELECT queries, and query plans through the Model Context Protocol.

The goal is simple: give MCP-compatible clients useful database context without giving them write access.

Features

• PostgreSQL schema introspection
• table and column metadata
• primary key and foreign key detection
• index metadata
• unique and check constraint metadata
• table size and row estimates
• safe sample row previews
• read-only SELECT query execution
• PostgreSQL EXPLAIN plans
• MCP tools and resources
• Docker-based demo database
• SQL guard tests

MCP tools

Tool	Description
ping	Checks whether the server is running
check_database_connection	Tests the PostgreSQL connection
check_feature_support	Reports which optional database features are installed
list_event_sources	Lists allowed tables and whether they emit live notifications
list_schemas	Lists exposed schemas
list_tables	Lists base tables in exposed schemas
describe_table	Returns table metadata
get_table_sample	Returns sample rows
run_select_query	Runs a guarded read-only SELECT query
explain_query	Returns a PostgreSQL EXPLAIN plan
summarize_relationships	Returns a compact foreign-key relationship map
wait_for_notification	Waits for the next PostgreSQL notification on a channel
get_recent_events	Returns recent table-change events from the event log
summarize_recent_activity	Summarizes recent table-change activity
tail_recent_events	Waits for the next matching live event and returns its event-log rows

See docs/tools.md for detailed tool behavior.

Tool responses use a consistent JSON envelope:

{
  "ok": true,
  "summary": "Listed 1 allowed schema.",
  "data": {}
}

Errors use:

{
  "ok": false,
  "summary": "Failed to list schemas.",
  "error": {
    "message": "..."
  }
}

MCP resources

Resource	Description
postgres://schemas	Lists exposed schemas
postgres://schema/{schemaName}	Lists base tables in a schema
postgres://table/{schemaName}/{tableName}	Returns table metadata, indexes, constraints, and stats

Safety model

The server is read-only by design, but it is not a complete database sandbox.

User-provided SQL is checked before execution. The query engine blocks common write, admin, and destructive operations, including:

• INSERT
• UPDATE
• DELETE
• DROP
• ALTER
• TRUNCATE
• CREATE
• GRANT
• REVOKE
• COPY
• EXECUTE
• CALL
• MERGE
• VACUUM
• REINDEX
• REFRESH
• LOCK
• ANALYZE
• SELECT INTO
• multiple SQL statements
• row-locking clauses such as FOR UPDATE

The server also uses:

• read-only transactions
• statement timeouts
• maximum row limits
• schema allowlists
• restricted PostgreSQL search_path for query tools
• identifier validation
• safe table-name quoting

This project is still early. Do not treat it as a complete production security boundary yet.

In particular, the SQL guard is based on blocking common dangerous patterns before running a query inside a read-only transaction. It does not fully constrain which SELECT-callable database functions a role may access. Use a least-privileged PostgreSQL role and treat database permissions as the primary security boundary.

Requirements

• Node.js 20+
• npm
• Docker
• Docker Compose

Quickstart

Clone the repository:

git clone https://github.com/Dem1241/pg-mcp-live.git
cd pg-mcp-live

Install dependencies:

npm install

Create a local environment file:

cp .env.example .env

Start the demo PostgreSQL database:

docker compose -f examples/docker-compose.yml up -d

Run checks:

npm test
npm run typecheck
npm run build
npm run self-check

Start the MCP server:

npm run dev

The server uses stdio transport. When started directly, it will wait for an MCP client. On startup it also prints a short diagnostics summary to stderr describing database connectivity and optional live-event feature support.

Self-check

Run:

npm run self-check

This verifies database connectivity and reports whether optional live-event features are installed. The command exits with status 0 when the core setup works, and prints warnings if optional live-event pieces are missing.

Testing with MCP Inspector

Run:

npx @modelcontextprotocol/inspector ./node_modules/.bin/tsx src/index.ts

Then open the Inspector URL and test the tools.

Useful first checks:

{
  "tool": "ping"
}

{
  "tool": "check_database_connection"
}

{
  "tool": "check_feature_support"
}

{
  "tool": "describe_table",
  "input": {
    "schemaName": "public",
    "tableName": "order_items"
  }
}

{
  "tool": "get_table_sample",
  "input": {
    "schemaName": "public",
    "tableName": "products",
    "limit": 3
  }
}

{
  "tool": "run_select_query",
  "input": {
    "sql": "SELECT id, sku, name, price_cents FROM products ORDER BY id",
    "limit": 5
  }
}

Demo database

The included Docker demo database contains:

• customers
• products
• inventory
• orders
• order_items

The schema is small on purpose. It is meant to test relationships, joins, sample rows, and query planning without needing an external database.

Configuration

Default .env:

DATABASE_URL=postgres://pgmcp:pgmcp@localhost:5433/pg_mcp_live_demo

PG_MCP_MAX_ROWS=100
PG_MCP_STATEMENT_TIMEOUT_MS=5000
PG_MCP_ALLOWED_SCHEMAS=public

DATABASE_URL

PostgreSQL connection string.

PG_MCP_MAX_ROWS

Maximum number of rows returned by guarded query tools.

PG_MCP_STATEMENT_TIMEOUT_MS

Statement timeout used for database operations.

PG_MCP_ALLOWED_SCHEMAS

Comma-separated list of schemas the server may expose.

Example:

PG_MCP_ALLOWED_SCHEMAS=public,analytics

Example queries

Allowed:

SELECT id, sku, name, price_cents
FROM products
ORDER BY id;

Allowed:

SELECT
  o.id AS order_id,
  c.full_name,
  o.status,
  o.total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

Rejected:

DROP TABLE products;

Rejected:

UPDATE inventory SET quantity = 0;

Rejected:

SELECT * FROM products; DROP TABLE customers;

Rejected:

SELECT * FROM products FOR UPDATE;

Project structure

pg-mcp-live/

├── src/
│   ├── config/
│   ├── db/
│   ├── resources/
│   ├── security/
│   ├── server/
│   ├── tools/
│   └── index.ts
│
├── docs/
├── examples/
├── tests/
├── README.md
├── package.json
├── tsconfig.json
├── tsconfig.test.json
└── .env.example

Development

Run tests:

npm test

Run typecheck:

npm run typecheck

Build:

npm run build

Start locally:

npm run dev

Start the demo database:

docker compose -f examples/docker-compose.yml up -d

Stop the demo database:

docker compose -f examples/docker-compose.yml down

Roadmap

v0.1.0

Core MCP server with PostgreSQL introspection and guarded read-only query tools.

v0.2.0

• PostgreSQL LISTEN/NOTIFY support
• optional demo table-change triggers
• persistent event log
• recent event replay
• recent activity summaries

v0.3.0

• PostgreSQL LISTEN/NOTIFY support
• recent database activity tools
• event trigger examples

v0.4.0

• Kafka bridge
• event stream summaries
• anomaly detection helpers

License

MIT