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 and execute read-only SQL queries against your PostgreSQL databases.

Prerequisites

• ▸PostgreSQL database running and accessible
• ▸Node.js 18+ installed
• ▸Claude Desktop or another MCP host

Installation

The PostgreSQL server is available as an npm package:

npx -y @modelcontextprotocol/server-postgres postgresql://user:password@/mydb

Configuration

Add to your Claude Desktop config:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://user:password@/mydb"
      ]
    }
  }
}

Available Tools

Once connected, the server exposes:

• ▸
  codeCopy

  query

  — Execute read-only SQL queries
• ▸
  codeCopy

  list_tables

  — List all tables in the database
• ▸
  codeCopy

  describe_table

  — Get schema information for a specific table

Security Best Practices

1. ▸Use a read-only database user: Create a dedicated user with SELECT-only permissions
2. ▸Limit accessible schemas: Configure the server to only expose specific schemas
3. ▸Use environment variables for connection strings — never hardcode credentials

-- 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;

Example Usage

Once configured, you can ask your AI agent:

• ▸"What tables exist in my database?"
• ▸"Show me the schema for the users table"
• ▸"What are the top 10 customers by order value?"
• ▸"Analyze the distribution of orders by month"

Environment Variables

For production setups, use environment variables:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres"],
      "env": {
        "DATABASE_URL": "postgresql://user:password@host:5432/db"
      }
    }
  }
}

Troubleshooting

• ▸Connection refused: Ensure PostgreSQL is running and accepting connections
• ▸Authentication failed: Verify credentials and pg_hba.conf settings
• ▸SSL required: Add
  codeCopy

  ?sslmode=require

  to your connection string