PostgreSQL with MCP

Prerequisites

Step 1: Get Your Database Connection String

You need a PostgreSQL connection string in this format:

postgresql://username:password@host:port/database_name

Examples:

• Local: postgresql://postgres:password@localhost:5432/myapp
• Remote: postgresql://user:pass@db.example.com:5432/production
• Supabase: postgresql://postgres:[password]@db.[project].supabase.co:5432/postgres
• Neon: postgresql://user:pass@ep-[name].us-east-2.aws.neon.tech/main

Creating a Read-Only Database User (Recommended)

To limit Claude to read-only access:

-- Connect to your database as admin
psql -U postgres -d your_database

-- Create read-only user
CREATE USER claude_readonly WITH PASSWORD 'secure_password';

-- Grant connect permission
GRANT CONNECT ON DATABASE your_database TO claude_readonly;

-- Grant schema usage
GRANT USAGE ON SCHEMA public TO claude_readonly;

-- Grant SELECT on all existing tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO claude_readonly;

-- Grant SELECT on future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
  GRANT SELECT ON TABLES TO claude_readonly;

Step 2: Install the PostgreSQL MCP Server

Add the PostgreSQL MCP server to your Claude Desktop configuration:

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://username:password@host:port/database"
      ]
    }
  }
}

Replace the connection string with your actual database URL.

Step 3: Restart Claude Desktop

Close and reopen Claude Desktop completely. The PostgreSQL server should now be loaded.

Step 4: Try It Out

Test your PostgreSQL integration with these example queries:

Real-World Use Cases

1. Business Intelligence Queries

Get instant insights without learning SQL:

"What's our monthly recurring revenue for the last 6 months? Show me a breakdown by plan type."

Claude will query your subscriptions table, aggregate the data, and present results clearly.

2. Data Quality Checks

Find data issues:

"Find all users with invalid email addresses (emails that don't contain '@')"

3. Performance Analysis

Identify slow queries or table statistics:

"Which tables are the largest in my database? Show me row counts and disk size."

4. Customer Support

Look up customer data quickly:

"Find the user with email john@example.com and show me their recent orders"

5. Cohort Analysis

Understand user behavior over time:

"Calculate retention rate for users who signed up in January 2025. How many are still active?"

6. Anomaly Detection

Spot unusual patterns:

"Show me any orders above $10,000 in the last week"

Advanced Queries

Window Functions

"For each product, show its sales rank compared to other products in its category"

CTEs (Common Table Expressions)

"Calculate the lifetime value of each customer, then show me the top 20"

JSON Queries

If you store JSON in PostgreSQL:

"Find all users whose metadata JSON contains a 'premium' subscription"

Time-Series Analysis

"Show me daily active users for the last 90 days, grouped by week"

Understanding Query Results

Claude presents query results in readable formats:

Security Best Practices

• Use read-only users: Never give Claude write access to production databases
• Limit schema access: Grant access only to necessary schemas
• Connection string security: Store connection strings securely, never commit to Git
• Use environment variables: On shared machines, load connection strings from env vars
• Network security: Use SSL connections for remote databases (?sslmode=require)
• Audit access: Monitor PostgreSQL logs for unexpected queries
• Test on staging first: Verify queries on non-production data before running on prod

SSL Connection Example

postgresql://user:pass@host:5432/db?sslmode=require

Performance Tips

1. Limit Large Queries

For tables with millions of rows, always add LIMIT:

"Show me 10 example rows from the events table" (Claude adds LIMIT 10)

2. Use Indexes

Ask Claude to check if indexes exist:

"Show me all indexes on the orders table"

3. Explain Plans

Understand query performance:

"Run EXPLAIN ANALYZE on a query to find all orders from last month"

Troubleshooting

Connection Failed

• Verify connection string format is correct
• Check that PostgreSQL is running (pg_isready)
• Ensure your IP is allowed (check pg_hba.conf)
• Verify username and password are correct
• Check firewall settings if connecting to remote database

Permission Denied

• Verify user has CONNECT privilege on database
• Check USAGE privilege on schema
• Ensure SELECT grants on tables
• Run \du in psql to check user permissions

Query Timeout

• Add LIMIT to queries on large tables
• Create indexes on frequently queried columns
• Check for missing JOINs causing Cartesian products

SSL/TLS Errors

• Add ?sslmode=require to connection string
• For self-signed certs, use ?sslmode=allow
• Check that your PostgreSQL server has SSL enabled

Multiple Database Connections

You can connect to multiple databases simultaneously:

{
  "mcpServers": {
    "postgres-production": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://readonly:pass@prod.db.com:5432/app"
      ]
    },
    "postgres-analytics": {
      "command": "npx",
      "args": [
        "-y",
        "@modelcontextprotocol/server-postgres",
        "postgresql://analyst:pass@analytics.db.com:5432/warehouse"
      ]
    }
  }
}

Then ask Claude to specify which database:

"Query the production database for active users, then compare with analytics database to see engagement metrics"

Combining with Other MCP Servers

What's Next?

Explore more database and API integrations: