PostgreSQL MCP Server

MCP Server Configuration

Read-Only Mode (Recommended for Production)

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

Full Access Mode

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

Available Tools

Tool	Description
query	Execute SQL queries against the connected database
list_tables	List all tables in the current schema
describe_table	Get column definitions, types, and constraints
list_schemas	List all available schemas

Connection String Format

postgresql://[user]:[password]@[host]:[port]/[database]?[params]

Examples:

Copy
# Local development
postgresql://postgres:secret@localhost:5432/myapp_dev

# With SSL (production)
postgresql://user:[email protected]:5432/production?sslmode=require

# With specific schema
postgresql://user:pass@localhost:5432/mydb?search_path=public

Setup

1. Ensure PostgreSQL is running and accessible
2. Create a dedicated read-only user (recommended):

Copy
CREATE ROLE claude_reader WITH LOGIN PASSWORD 'secure_password';
GRANT CONNECT ON DATABASE mydb TO claude_reader;
GRANT USAGE ON SCHEMA public TO claude_reader;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO claude_reader;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO claude_reader;

3. Replace the connection string with your actual credentials

Security Notes

• Always use read-only credentials for production databases
• Never expose production connection strings in committed files
• Use environment variables for sensitive connection details
• Consider connecting through an SSH tunnel for remote databases
• The server does NOT sandbox queries in read-only mode by default - create a read-only PostgreSQL user