Guide: Configure mcp-server-postgres Connection

mcp-server-postgres enables AI Agents to safely query PostgreSQL databases.

What is postgres tool?

Capabilities:

• query: Execute SELECT queries
• execute: Execute INSERT/UPDATE/DELETE
• Parameterized queries prevent SQL injection

Basic Configuration

Installation

npx -y @modelcontextprotocol/server-postgres postgresql://localhost/dbname

Claude Code Config

Method 1: Direct connection string (not recommended)

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

Method 2: Environment variables (recommended)

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": ["-y", "@modelcontextprotocol/server-postgres", "postgresql://localhost/dbname"],
      "env": {
        "PGUSER": "dbuser",
        "PGPASSWORD": "dbpassword",
        "PGHOST": "localhost",
        "PGPORT": "5432",
        "PGDATABASE": "dbname"
      }
    }
  }
}

Connection String Security

Standard Format

postgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]

SSL Modes

Mode	Description	Use Case
disable	No SSL	Local dev
allow	Prefer non-SSL	Testing
prefer	Prefer SSL	Production
require	Must use SSL	Secure env
verify-ca	Verify CA	High security
verify-full	Verify hostname + CA	Max security

Database Permissions

Create Read-Only User

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

Create Read-Write User

CREATE ROLE read_write WITH LOGIN PASSWORD secure_password;
GRANT CONNECT ON DATABASE mydb TO read_write;
GRANT USAGE, CREATE ON SCHEMA public TO read_write;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;

Security Best Practices

1. Use read-only user for production
2. Limit table access to necessary tables only
3. Use connection pooling for high concurrency
4. Network isolation - no public exposure

Verify Configuration

1. Restart Claude Code
2. Type /mcp to see postgres tool
3. Test query

Next Steps

• Connection Refused Error
• Authentication Failed Error
• Filesystem Configuration