PostgreSQL MCP Server Setup and Security Guide

Connect PostgreSQL to AI Agents Using MCP

Connecting a production database to an autonomous AI agent or Large Language Model (LLM) assistant provides powerful operational advantages: agents can inspect schemas, troubleshoot data anomalies, compose SQL queries, and synthesize reporting tables on demand.

However, connecting AI to databases introduces significant operational risks—including unintentional data corruption, table locking, and credential leaks. This guide outlines the end-to-end production setup for the official PostgreSQL Model Context Protocol (MCP) server, featuring strict read-only permission sandboxing, connection timeout limits, and verified client configuration across Claude Desktop, Cursor, and the OpenAI Codex CLI.

Architectural Overview

The PostgreSQL MCP server (@modelcontextprotocol/server-postgres) operates as a lightweight local adapter that connects to your PostgreSQL instance via the Node.js pg driver and translates standard JSON-RPC protocol requests from your AI client into parameterized SQL statements.

By default, the server operates over standard input/output (stdio), meaning the database credentials and network socket exist strictly within your local machine's user process environment.

Step 1: Database Security Sandboxing & User Setup

Never connect an MCP server using an administrative postgres superuser role or your application's primary read-write credentials. Always provision an isolated, unprivileged role configured with explicit table-level restrictions.

Connect to your database via psql as a superuser:

psql -U postgres -d production_db

Execute the following SQL commands to create a dedicated, hardened read-only role:

-- 1. Create dedicated MCP role with strong password
CREATE USER mcp_reader WITH PASSWORD 'Replace_With_Secure_Random_Token_2026!';

-- 2. Grant connection rights to target database
GRANT CONNECT ON DATABASE production_db TO mcp_reader;

-- 3. Grant schema usage strictly to public (or your specific schema)
GRANT USAGE ON SCHEMA public TO mcp_reader;

-- 4. Grant SELECT-only privileges on current and future tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_reader;

-- 5. Revoke dangerous execution permissions
REVOKE EXECUTE ON ALL FUNCTIONS IN SCHEMA public FROM mcp_reader;
ALTER ROLE mcp_reader SET default_transaction_read_only = on;

-- 6. Enforce statement timeouts to prevent rogue unindexed query lockups
ALTER ROLE mcp_reader SET statement_timeout = '8000'; -- 8 second maximum query duration
ALTER ROLE mcp_reader SET idle_in_transaction_session_timeout = '5000'; -- 5s idle timeout

Why statement_timeout is non-negotiable: Autonomous LLMs occasionally write unindexed sequential scans or Cartesian cross-joins on multi-million row tables. Setting statement_timeout = '8000' guarantees that any runaway query is terminated by the PostgreSQL engine before it consumes all database IOPS.

Step 2: Multi-Client Installation & Configuration

Option A: Claude Desktop Setup

Locate your Claude Desktop configuration file:

• ▸macOS: ~/Library/Application Support/Claude/claude_desktop_config.json
• ▸Windows: %APPDATA%\Claude\claude_desktop_config.json
• ▸Linux: ~/.config/Claude/claude_desktop_config.json

Add the postgres server entry under mcpServers. We recommend passing the database URI as an environment variable rather than hardcoding credentials inside command arguments:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres"],
      "env": {
        "DATABASE_URL": "postgresql://mcp_reader:Replace_With_Secure_Random_Token_2026!@/production_db?sslmode=prefer"
      }
    }
  }
}

Option B: Cursor IDE Setup

In Cursor (version 0.44+), open your workspace or global settings and locate .cursor/mcp.json:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres"],
      "env": {
        "DATABASE_URL": "postgresql://mcp_reader:Replace_With_Secure_Random_Token_2026!@/production_db"
      }
    }
  }
}

Option C: OpenAI Codex CLI Setup

For OpenAI Codex CLI, you can add it via config.toml (~/.codex/config.toml):

[mcp_servers.postgres]
command = "npx"
args = [
  "-y",
  "@modelcontextprotocol/server-postgres",
  "postgresql://mcp_reader:Replace_With_Secure_Random_Token_2026!@/production_db"
]

Or register the server dynamically via the codex mcp add utility:

codex mcp add --name postgres \
  --command "npx -y @modelcontextprotocol/server-postgres postgresql://mcp_reader:Replace_With_Secure_Random_Token_2026!@/production_db"

Verify the registration with:

codex mcp list

Step 3: Available Tools & Schema Discovery

Once the server initializes, it exposes three primary tools to the host LLM:

Tool Execution Flow Example

When you ask your AI: "What are our 5 highest-spending customer accounts this month?", the host model coordinates with the MCP server:

1. ▸The model calls list_tables to discover database structure.
2. ▸The model calls describe_table with table_name: "orders" and table_name: "customers" to understand foreign key relationships.
3. ▸The model composes an optimized SQL query:
   sqlCopy

   SELECT c.name, c.email, SUM(o.total_amount) AS total_spent
   FROM customers c
   JOIN orders o ON c.id = o.customer_id
   WHERE o.created_at >= '2026-08-01'
   GROUP BY c.id, c.name, c.email
   ORDER BY total_spent DESC
   LIMIT 5;

4. ▸The model calls query with the generated SQL.
5. ▸The MCP server returns row data, which the model presents as a formatted markdown table.

Production Troubleshooting & Common Traps

1. Error: spawn npx ENOENT

• ▸Root Cause: The graphical desktop application (Claude Desktop) launched in an environment where Node.js / npx is not available in the system GUI $PATH.
• ▸Fix: Replace "command": "npx" with the absolute binary path:
  • ▸macOS / Linux: /usr/local/bin/npx or /opt/homebrew/bin/npx (find with which npx).
  • ▸Windows: C:\\Program Files\\nodejs\\npx.cmd (find with where.exe npx.cmd).

2. error: password authentication failed for user "mcp_reader"

• ▸Root Cause: Special characters inside the database password (such as @, /, #, or ?) broke URI parsing in the connection string.
• ▸Fix: URL-encode special characters in your password (e.g., replace @ with %40, # with %23), or enclose the connection string in strict quotes.

3. FATAL: no pg_hba.conf entry for host

• ▸Root Cause: If PostgreSQL is hosted remotely (AWS RDS, Supabase, Neon) or inside Docker, the server rejected the incoming connection IP.
• ▸Fix: Ensure pg_hba.conf allows host connections with md5 or scram-sha-256, and verify that ?sslmode=require is appended to the connection string for cloud databases.

4. Docker Container Networking (host.docker.internal)

• ▸If PostgreSQL is running inside a local Docker container while Claude Desktop runs on the host OS, connect to .
• ▸If your MCP server is containerized while PostgreSQL runs on the host machine, use host.docker.internal:5432.

Verification & Health Check

After saving your configuration, restart your AI client and test with the following verification prompt:

"Query PostgreSQL using MCP: run SELECT version(); and list the first 3 tables in the database."

If configured properly, the model will output the PostgreSQL engine version and table names within 1–2 seconds.

Related Guides & Tooling

• ▸What Is Model Context Protocol?
• ▸Visual MCP Config Generator
• ▸MCP Security Best Practices