PGAutoPilot

PGAutoPilot is a production-ready PostgreSQL MCP server that lets any AI assistant safely interact with PostgreSQL using natural language. It combines schema-aware query generation, enterprise safety controls, and a minimal-configuration setup into a single executable.

  Model-agnostic   PostgreSQL-optimized   Safe writes   Read-only mode
  Minimal config   Single executable      Docker        Cloud databases
  Connection pool  Production-ready       SSL           Schema inspection

Contents

• Requirements

• Supported MCP Clients

• Who is this for?

• Design Principles

• Demo

• Quick Start

• Install without npm

• Why PGAutoPilot

• Features

• Architecture

• Safety

• Security

• Tools

• Examples

• Cloud Databases

• Docker

• Configuration

• Performance

• Compatibility

• Software Signing

• FAQ

• Troubleshooting

• Development

• Roadmap

• Contributing

• License

Related MCP server: Postgres Scout MCP

Requirements

Supported MCP Clients

Who is this for?

Design Principles

Safety before convenience.
Natural language before SQL.
No vendor lock-in.
Minimal dependencies.
Deterministic behavior.
Schema-aware generation.
Fail closed, not open.
Minimal configuration.
Protocol-first architecture.

Demo

You: "Show me customers that spent more than $500."
  -> db_aggregate(table="orders", by="customer_id", _sum="total",
       orderBy={"_sum/total": "desc"}, take=5)
  <- 23 customers found

Assistant: "23 customers have spent more than $500. The top spender is..."

Quick Start

1. Install

npm install -g pgautopilot

No npm? Use the one-line installer.

2. Point it at your database

Create a .env file anywhere on your machine:

DATABASE_URL=postgresql://user:password@localhost:5432/yourdb

PGAutoPilot finds .env automatically from the current directory.

Connection string examples:

3. Connect your AI assistant

{ "mcpServers": { "postgres": { "command": "pgautopilot" } } }

Identical config for VS Code, Cursor, Windsurf, Claude Desktop, Zed, JetBrains, Neovim.

4. Start asking questions

"Show me all tables." "How many users signed up this month?" "Find orders over $500 grouped by customer." "Add a new product called 'Widget Pro' at $29.99."

Install without npm

You need only Node.js 18+ and a browser or Git.

One-line installer:

Clones into ~/.pgautopilot (Linux/macOS) or %LOCALAPPDATA%\pgautopilot (Windows) and adds pgautopilot to your PATH. Run the same command again to update.

Download & run:

node pgautopilot.bundle.cjs

Clone & run:

git clone https://github.com/cyberreinxy/pgautopilot.git
cd pgautopilot
node dist/pgautopilot.bundle.cjs

Uninstall:

Removes the launcher, deletes the installation directory, and cleans up PATH entries. Idempotent — safe to run even when PGAutoPilot isn't installed.

Why PGAutoPilot

Existing database MCP servers usually expose raw SQL directly, offer little protection against destructive operations, or require extensive manual configuration. PGAutoPilot takes a different approach:

• Schema-aware — introspects your live database and validates every identifier before it touches SQL

• Production-first — every write path is guarded by multiple configurable safety layers

• AI-friendly — tools are designed for natural-language workflows, not SQL terminals

• Model-agnostic — works identically with Claude, GPT-4o, Gemini, DeepSeek, Copilot, and open-source models

• Safety by default — sensitive columns are redacted, bulk operations warn, and deletes require confirmation

Features

Architecture

AI Assistant
     (Claude / Cursor / GPT / Gemini)
        |
        v
   MCP Protocol
        |
        v
+----------------------------------+
|          PGAutoPilot              |
|                                    |
|  Schema Discovery                 |
|  Identifier Validation            |
|  Safety Policy Engine     <----+  |
|  SQL Builder                   |  |
|  Connection Pool               |  |
|  Tool Handlers (14 tools)      |  |
+---------------------------------+ |
        |                          |
        v                          |
   PostgreSQL          Everything passes through safety

How a request flows

Natural language
  -> MCP tool request
  -> Parameter validation (Zod)
  -> Live schema lookup
  -> Identifier validation
  -> Safety policy evaluation
  -> Parameterized SQL generation
  -> Query execution (time-limited)
  -> Response formatting
  -> AI assistant presents result

Every step is deterministic. No hidden state. No side effects.

Safety

Threat model

PGAutoPilot runs on your machine and connects to your database over the PostgreSQL wire protocol. The AI assistant communicates only through the MCP protocol, and every request passes through the safety layer before reaching PostgreSQL. The DATABASE_URL credential is the sole authentication boundary.

Safety features

Operational guarantees

• Never logs DATABASE_URL (only the hostname appears in the startup banner)

• Never exposes redacted fields (passwords, tokens, keys return ***REDACTED***)

• Never executes multiple SQL statements in one call

• Never allows UPDATE without identifier validation (table + column checked against live schema)

• Never allows DELETE ALL without explicit confirmAll: true

• Never runs raw INSERT, UPDATE, or DELETE SQL (use the structured tools)

• Never bypasses schema validation

• Never opens more connections than PGPOOL_MAX

Production recommendations

• Enable --readonly for read replicas and analytics environments

• Block sensitive tables with BLOCKED_TABLES

• Configure PGSSLMODE=require for cloud databases

• Use a dedicated database user with minimal required permissions

• Add custom sensitive columns via SENSITIVE_COLUMNS

• Set PG_STATEMENT_TIMEOUT_MS to match your SLA

• Run in --mode=production to suppress per-request logging

Read-only mode

pgautopilot --readonly

Blocks every write operation. Ideal for read replicas, analytics databases, and any read-only environment.

Blocked tables

BLOCKED_TABLES=users,refresh_tokens pgautopilot

Refuses all writes to specific tables, independent of --readonly.

Sensitive columns

Columns matching password, token, secret, api_key, private_key, ssn, credit_card, cvv (and variants) are automatically redacted on read and stripped on write. Extend with SENSITIVE_COLUMNS.

Security

• Responsible disclosure — if you discover a security vulnerability, report it privately via GitHub Security Advisories. Please avoid opening a public issue until the vulnerability has been addressed.

• Threat model — see Threat model above; the database URL is your credential boundary.

• Cryptographic verification — every release is SHA-256 checksummed and GPG-signed.

• Dependency policy — zero runtime dependencies in the bundled build.

• Logging policy — connection strings are never logged; per-request logging is disabled in production mode (--mode=production).

• Authentication — handled entirely by the DATABASE_URL credential. PGAutoPilot does not manage users or tokens.

• Production recommendation — use a dedicated read-only database user with minimal permissions.

Tools

Read tools

Write tools

Maintenance tools

Examples

Find recent orders for a customer:

Prompt: "Show me the last 10 orders for customer 42"

Tool:   db_find_many(table="orders", where={"customer_id": 42},
         select=["id","total","status","created_at"],
         orderBy={"created_at":"desc"}, take=10)

SQL:    SELECT id, total, status, created_at FROM orders
        WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 10

Result: 10 rows returned

Count products by category:

Prompt: "How many products in each category?"

Tool:   db_aggregate(table="products", by="category", _count="*",
         take=5, orderBy={"_count": "desc"})

SQL:    SELECT category, COUNT(*) FROM products
        GROUP BY category ORDER BY COUNT(*) DESC LIMIT 5

Result: Electronics: 142, Clothing: 89, Books: 54, ...

Add a new user (dry run first):

Prompt: "Add Jane Doe with email jane@example.com"

Tool:   db_create(table="users", data={"email":"jane@example.com","name":"Jane Doe"},
         dryRun=true)  -- verifies first, no write

SQL:    INSERT INTO users (email, name) VALUES ($1, $2) RETURNING *

Result: Dry run: valid. Proceed? [yes] -> Row inserted with id 105

Update order status:

Prompt: "Mark order 1503 as shipped"

Tool:   db_update_many(table="orders", where={"id":1503},
         data={"status":"shipped"})

SQL:    UPDATE orders SET status = $1 WHERE id = $2 RETURNING *

Result: 1 row updated

Delete old records:

Prompt: "Delete logs from before 2025"

Tool:   db_delete_many(table="logs",
         where={"created_at":{"lt":"2025-01-01"}}, dryRun=true)

SQL:    DELETE FROM logs WHERE created_at < $1 RETURNING *

Result: 1,204 rows would be deleted. Confirm? [yes/no]

Cloud Databases

Add PGSSLMODE for cloud providers (Neon, Supabase, RDS, Render):

DATABASE_URL=postgresql://user:pass@ep-xxx.us-east-2.aws.neon.tech/neondb
PGSSLMODE=require

Options: disable, prefer, require, verify-full. Auto-detected for most providers.

Docker

Run PGAutoPilot alongside a fresh PostgreSQL instance, or point it at a database you already have:

docker compose up --build          # PostgreSQL 16 + MCP server together
docker run -e DATABASE_URL=... pgautopilot   # Connect to an existing DB

Configuration

CLI flags

pgautopilot --readonly
pgautopilot --mode=production
pgautopilot --readonly --mode=production

Performance

PGAutoPilot adds minimal overhead over a direct PostgreSQL connection — query latency depends primarily on your database and network. The bundled single-file executable has zero runtime dependencies, and connection pool size and statement timeouts are both configurable. Full performance benchmarks will be published once the project reaches a stable release.

Compatibility

Software Signing

Install scripts verify checksums.txt automatically after cloning. On mismatch, installation aborts. Bypass with --skip-verify (not recommended).

FAQ

Do I need to restart after changing my database schema? No. PGAutoPilot validates identifiers against the live schema on every request. Schema changes are reflected immediately without a restart.

Can PGAutoPilot modify my database automatically? Only through explicit tool calls. Every write requires a deliberate AI action, and dry-run before write is the default workflow.

Does it work with Supabase? Yes. Use the Supabase connection string and set PGSSLMODE=require.

Does it require npm? No. Install via npm, the one-line installer, or the single-file bundle.

Does it support SSL? Yes — auto-detected or configured via PGSSLMODE.

Can I disable writes entirely? Yes. pgautopilot --readonly blocks every write operation.

Can I expose it publicly? No. PGAutoPilot is designed for local/private network use. There is no authentication layer or HTTP server.

Is it safe for production? Yes — every write path is guarded. See the Safety and Security sections for details.

Troubleshooting

Development

Repository layout

src/
  index.ts           Entry point, MCP server initialization
  config.ts          Environment variable loading and validation
  db.ts              PostgreSQL connection pool management
  schema.ts          Live schema introspection via information_schema
  sqlBuilder.ts      Parameterized, safe SQL query builder
  safety.ts          Redaction engine, write access control, warnings
  toolDefinitions.ts Zod schemas for all 14 tools
  toolHandlers.ts    Tool implementations, one handler per tool

Commands

Roadmap

• Authentication plugins (API key, JWT)

• Streaming query results

• Schema migration tools

• Prometheus metrics / OpenTelemetry

• Multi-database support (read replicas, shards)

• Query explain and optimization suggestions

Contributing

1. Strict TypeScript — no any types

2. Every write path must pass through the safety layer

3. Add tests for new features

4. Keep dependencies minimal

Fork, branch, commit, open a PR.

License

MIT © 2026 Cyber Reinxy