MCP PostgreSQL Server

An MCP (Model Context Protocol) server that connects to PostgreSQL databases and enables AI assistants to run read-only queries.

Features

• query - Execute read-only SQL queries (SELECT only)
• list_tables - List all tables in the database
• describe_table - Get column information for any table

Security

The query tool enforces read-only access through three layers. Only the last is a real security boundary — treat the first two as defense in depth.

1. SQL validation (application layer)

Each query must be a single statement beginning with SELECT/WITH. Multi-statement input and dangerous primitives (COPY, DO, DDL, DML, pg_read_file, dblink, …) are rejected before reaching the database. A keyword filter cannot fully constrain SQL, so this is a first line of defense, not the guarantee.

2. Read-only transaction (database layer)

The query runs via the extended protocol (which rejects multiple commands) inside a BEGIN TRANSACTION READ ONLY that is always rolled back. PostgreSQL itself rejects any write, DDL, or COPY FROM regardless of how the SQL is spelled.

3. Least-privilege database role (required)

Layers 1–2 still run with the connection's role. A superuser connection can read host files (pg_read_file) or run programs (COPY … TO PROGRAM) through plain reads that no text filter can safely block. Connect as a dedicated non-superuser role with grants on only the objects you intend to expose:

-- Create read-only user
CREATE USER mcp_readonly WITH PASSWORD 'your_secure_password';

-- Grant connection + read access, nothing else
GRANT CONNECT ON DATABASE your_database TO mcp_readonly;
GRANT USAGE ON SCHEMA public TO mcp_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_readonly;

See SECURITY.md to report a vulnerability.

Installation

npm install
npm run build

Configuration

Set your PostgreSQL connection via environment variables:

# Option 1: Connection string
export DATABASE_URL="postgresql://mcp_readonly:password@host:port/database"

# Option 2: Individual variables
export PGHOST=localhost
export PGPORT=5432
export PGUSER=mcp_readonly
export PGPASSWORD=yourpassword
export PGDATABASE=mydb

Usage with Claude Desktop

Add to your claude_desktop_config.json:

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/path/to/mcp-postgres-server/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://mcp_readonly:password@localhost:5432/mydb"
      }
    }
  }
}

Development

npm run dev

License

MIT