MCP Servers

Postgres MCP Server — Query Databases with AI

Connect Claude to PostgreSQL databases using the Postgres MCP server. Run queries, explore schemas, analyze data, and generate reports through natural conversation.

Why Connect AI to Your Database?

Querying a database traditionally requires writing SQL, understanding the schema, and iterating on queries until you get the result you need. The Postgres MCP server lets you describe what you want in plain English and have the AI write and execute the SQL for you. This is not a toy — production data teams use this workflow for ad-hoc analysis, debugging, data exploration, and generating reports that would otherwise require a dedicated analyst.

The Postgres MCP server connects to any PostgreSQL database (including hosted services like Supabase, Neon, AWS RDS, and Railway) and exposes tools for schema inspection, query execution, and data retrieval. The AI can see your table structures, understand relationships, and write contextually appropriate queries.

Installation

The official Postgres MCP server is available as an npm package. You need a PostgreSQL connection string — the same format you use in application code or database GUIs:

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

Replace the connection string with your actual database credentials. For cloud-hosted databases, use the connection string from your provider's dashboard. Make sure the database user has appropriate permissions — for safety, a read-only user is recommended for exploratory analysis.

Using a Read-Only Connection

For production databases, always connect with a read-only user. Create one in PostgreSQL with:

-- Create a read-only role for MCP access
CREATE ROLE mcp_reader WITH LOGIN 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;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO mcp_reader;

This ensures the AI can explore and query your data but cannot modify, delete, or insert anything. Even with careful AI tools, connecting a write-capable user to a production database through an AI assistant introduces unnecessary risk.

What You Can Do

Once connected, the AI has full visibility into your database schema and can execute queries. Here are examples of natural language requests that translate directly to SQL operations:

Schema exploration: "What tables are in this database?" or "Show me the columns in the users table" or "How are the orders and products tables related?" — the AI reads the information_schema and pg_catalog to answer structural questions.

Data analysis: "How many users signed up this month?" or "What is the average order value by country?" or "Show me the top 10 customers by revenue" — the AI writes aggregate queries, joins tables as needed, and formats the results.

Debugging: "Find all orders that have a null shipping_address" or "Are there any users with duplicate email addresses?" or "Show me records where the created_at timestamp is in the future" — the AI can systematically check for data quality issues.

Report generation: "Generate a monthly revenue summary for the last 12 months" or "Compare this quarter's metrics to last quarter" — the AI can write complex queries with window functions, CTEs, and date arithmetic.

Working with Large Schemas

Real production databases often have hundreds of tables across multiple schemas. The AI handles this by first listing available schemas and tables, then inspecting only the relevant ones based on your question. You can help by providing context: "The user data is in the auth schema and the billing data is in the payments schema" — this helps the AI navigate large databases efficiently.

For very large or complex databases, consider creating a resource in your MCP server configuration that describes the schema at a high level. A text file listing the main tables, their purposes, and key relationships gives the AI a map it can reference before diving into specific queries.

Building a Custom Postgres MCP Server

The official server covers general-purpose database access. For specialized workflows, you can build a custom server that adds domain-specific tools. For example, a content authentication database might benefit from specialized tools:

from mcp.server.fastmcp import FastMCP
import asyncpg

mcp = FastMCP("content-db")
pool = None

@mcp.tool()
async def search_verified_content(url: str) -> str:
    """Search the verification database for a specific URL.

    Args:
        url: The URL to look up in the verification database
    """
    async with pool.acquire() as conn:
        rows = await conn.fetch(
            "SELECT url, verification_status, verified_at, hash_sha256 "
            "FROM content_verifications WHERE url = $1 "
            "ORDER BY verified_at DESC LIMIT 5", url
        )
        if not rows:
            return f"No verification records found for {url}"
        return json.dumps([dict(r) for r in rows], indent=2, default=str)

@mcp.tool()
async def detection_stats(days: int = 30) -> str:
    """Get AI detection statistics for the specified time period.

    Args:
        days: Number of days to look back (default 30)
    """
    async with pool.acquire() as conn:
        row = await conn.fetchrow(
            "SELECT COUNT(*) as total, "
            "COUNT(*) FILTER (WHERE result = 'ai_generated') as ai_count, "
            "COUNT(*) FILTER (WHERE result = 'human') as human_count, "
            "AVG(confidence_score) as avg_confidence "
            "FROM detection_results "
            "WHERE analyzed_at > NOW() - $1::interval",
            f"{days} days"
        )
        return json.dumps(dict(row), indent=2, default=str)

Custom servers let you embed business logic, enforce access controls, and provide tools that are tailored to your specific domain rather than generic SQL access. This is the recommended approach for production team deployments.

Performance and Safety Tips

Always set statement timeouts for your MCP database user: ALTER ROLE mcp_reader SET statement_timeout = '30s';. This prevents accidentally expensive queries from locking up your database. Use connection pooling (the asyncpg pool in Python, or pg-pool in Node.js) if your server handles multiple concurrent requests. Monitor query patterns through PostgreSQL's pg_stat_statements extension to identify optimization opportunities.

Last updated: 2026 • Browse all courses