Postgresql MCP Server

MCP Server Content

The main content for this mcp server.

postgresql-mcp-server-config.txt

The official PostgreSQL MCP Server from Anthropic provides read-only access to PostgreSQL databases, enabling Claude to inspect schemas and execute safe queries.

Features

Key capabilities and functionality

Read-only access for safe database exploration
Automatic discovery of table structures and columns
Execute SELECT queries within read-only transactions
Access detailed database metadata and structure
Perform data analysis without altering database state
Support for SSL connections and authentication

Installation

Setup instructions and requirements

Claude Code Setup

Claude Desktop Setup

Open your Claude Desktop configuration file
Add the PostgreSQL MCP server configuration with connection string
Restart Claude Desktop
Ask Claude to list tables to verify connection

Configuration Paths

macOS

~/Library/Application Support/Claude/claude_desktop_config.json

windows

%APPDATA%\Claude\claude_desktop_config.json

Configuration

Add these configurations to your Claude Desktop or Claude Code setup

postgresql-mcp-server-claude-desktop.json

json

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

postgresql-mcp-server-claude-code.json

json

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

Use Cases

Common scenarios and applications

Understand database schema and relationships
Generate reports from existing data
Analyze data patterns and distributions
Create documentation from database structure
Write and test SELECT queries
Explore unfamiliar database schemas

Security Best Practices

Important security considerations

All queries execute in read-only transactions
INSERT, UPDATE, DELETE operations are prevented
Supports SSL/TLS encryption for connections
Respects existing database user permissions
Safe exploration without data modification risks

Troubleshooting

Common issues and solutions

Connection refused or database not accessible
Verify PostgreSQL is running with pg_isready command. Check connection string format: postgresql://user:password@host:port/database. Test with psql command first. Verify firewall allows connections on port 5432.
SSL connection error - wrong version number or verify failed
Add sslmode parameter: postgresql://host/db?sslmode=require. Verify PostgreSQL supports TLS version (check pg_config --configure). Use sslmode=disable for local dev only. Check certificate matches CA in ssl_ca_file.
Authentication failed for user or password rejected
Verify username/password in connection string exact match. Check pg_hba.conf allows connection method (md5, scram-sha-256). Ensure user exists with GRANT SELECT permissions. Test auth with psql -U username -d database.
Certificate authentication failed - CN mismatch
Verify certificate CN matches PostgreSQL username exactly. Configure pg_ident.conf for username mapping if needed. Check ssl_cert_file and ssl_key_file permissions (owned by postgres user). Ensure client cert trusted by server CA.
Permission denied on table or schema access error
Grant SELECT permissions: GRANT SELECT ON ALL TABLES IN SCHEMA public TO username. Verify read-only user has pg_read_all_data role. Check schema permissions with \dp command in psql. Ensure user connected to correct database.

Usage Examples

Practical code examples demonstrating common use cases and implementation patterns