How to Connect PostgreSQL to AI Agents Using MCP

Connect PostgreSQL to AI Agents Using MCP

The PostgreSQL MCP server allows AI agents to inspect database schemas, visualize tables, and execute read-only SQL queries against your PostgreSQL databases. This turns your AI assistant into a powerful data analyst that can write complex JOINs and aggregations on the fly.

Prerequisites

1. ▸A PostgreSQL database running locally or remotely.
2. ▸Node.js 18+ installed on your host machine.
3. ▸An MCP-compatible client like Claude Desktop.

Security Best Practices

Before configuring your MCP server, you must establish strict access controls. Never grant your AI agent root or postgres admin access.

1. ▸Use a read-only database user: Create a dedicated user with SELECT-only permissions.
2. ▸Limit accessible schemas: Configure the user to only query the public schema.
3. ▸Use environment variables for connection strings—never hardcode credentials in your config.

-- Execute this to create a read-only user
CREATE USER mcp_reader WITH PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO mcp_reader;
GRANT USAGE ON SCHEMA public TO mcp_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_reader;

Installation & Configuration

For production setups, we recommend injecting the database URL via environment variables rather than hardcoding it in the args array.

Update your Claude Desktop config:

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

Available Tools

Once connected, the server exposes:

• ▸query — Execute read-only SQL queries.
• ▸list_tables — List all tables in the database.
• ▸describe_table — Get schema information and column types for a specific table.

Example Workflows

Once configured, you can prompt your AI agent naturally:

• ▸"What tables exist in my database?"
• ▸"Show me the schema for the users table."
• ▸"What are the top 10 customers by order value this quarter? Write a SQL query, execute it, and format the results as a markdown table."

Troubleshooting

• ▸Connection refused: Ensure PostgreSQL is running and accepting connections on port 5432.
• ▸Authentication failed: Verify your mcp_reader credentials and ensure your pg_hba.conf allows connections from your MCP server's IP.
• ▸SSL required: If connecting to a cloud database (like AWS RDS or Supabase), append ?sslmode=require to your DATABASE_URL string.

Using with OpenAI Codex

You can use this MCP server with the OpenAI Codex CLI by adding it to your configuration:

codex mcp add --name postgres --command "npx -y @modelcontextprotocol/server-postgres postgresql://mcp_reader:your_password@/mydb"

Note: Replace the connection string with your actual PostgreSQL URL.

For a full list of recommended servers, see Best MCP Servers for OpenAI Codex.