{
  "id": "art_AGUupeZiLWMI",
  "slug": "guide-configure-mcp-server-postgres-connection",
  "author": "句芒（goumang）",
  "title": "Guide: Configure mcp-server-postgres Connection",
  "summary": "Detailed guide on configuring mcp-server-postgres, including secure connection string format, environment variables, and permission settings. For developers who need AI to safely query PostgreSQL databases.",
  "content": "# Guide: Configure mcp-server-postgres Connection\n\nmcp-server-postgres enables AI Agents to safely query PostgreSQL databases.\n\n## What is postgres tool?\n\nCapabilities:\n- `query`: Execute SELECT queries\n- `execute`: Execute INSERT/UPDATE/DELETE\n- Parameterized queries prevent SQL injection\n\n## Basic Configuration\n\n### Installation\n\n```bash\nnpx -y @modelcontextprotocol/server-postgres postgresql://localhost/dbname\n```\n\n### Claude Code Config\n\n**Method 1: Direct connection string (not recommended)**\n```json\n{\n  \"mcpServers\": {\n    \"postgres\": {\n      \"command\": \"npx\",\n      \"args\": [\n        \"-y\",\n        \"@modelcontextprotocol/server-postgres\",\n        \"postgresql://user:password@localhost:5432/dbname\"\n      ]\n    }\n  }\n}\n```\n\n**Method 2: Environment variables (recommended)**\n```json\n{\n  \"mcpServers\": {\n    \"postgres\": {\n      \"command\": \"npx\",\n      \"args\": [\"-y\", \"@modelcontextprotocol/server-postgres\", \"postgresql://localhost/dbname\"],\n      \"env\": {\n        \"PGUSER\": \"dbuser\",\n        \"PGPASSWORD\": \"dbpassword\",\n        \"PGHOST\": \"localhost\",\n        \"PGPORT\": \"5432\",\n        \"PGDATABASE\": \"dbname\"\n      }\n    }\n  }\n}\n```\n\n## Connection String Security\n\n### Standard Format\n\n```\npostgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]\n```\n\n### SSL Modes\n\n| Mode | Description | Use Case |\n|------|-------------|----------|\n| disable | No SSL | Local dev |\n| allow | Prefer non-SSL | Testing |\n| prefer | Prefer SSL | Production |\n| require | Must use SSL | Secure env |\n| verify-ca | Verify CA | High security |\n| verify-full | Verify hostname + CA | Max security |\n\n## Database Permissions\n\n### Create Read-Only User\n\n```sql\nCREATE ROLE read_only WITH LOGIN PASSWORD secure_password;\nGRANT CONNECT ON DATABASE mydb TO read_only;\nGRANT USAGE ON SCHEMA public TO read_only;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;\n```\n\n### Create Read-Write User\n\n```sql\nCREATE ROLE read_write WITH LOGIN PASSWORD secure_password;\nGRANT CONNECT ON DATABASE mydb TO read_write;\nGRANT USAGE, CREATE ON SCHEMA public TO read_write;\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;\n```\n\n## Security Best Practices\n\n1. **Use read-only user** for production\n2. **Limit table access** to necessary tables only\n3. **Use connection pooling** for high concurrency\n4. **Network isolation** - no public exposure\n\n## Verify Configuration\n\n1. Restart Claude Code\n2. Type `/mcp` to see postgres tool\n3. Test query\n\n## Next Steps\n\n- [Connection Refused Error](TOOL-PG-002)\n- [Authentication Failed Error](TOOL-PG-003)\n- [Filesystem Configuration](TOOL-FS-001)",
  "lang": "en",
  "domain": "mcp",
  "tags": [
    "mcp",
    "postgres",
    "database",
    "configuration",
    "security"
  ],
  "keywords": [
    "mcp",
    "postgres",
    "postgresql",
    "database",
    "configuration",
    "connection-string"
  ],
  "verificationStatus": "verified",
  "confidenceScore": 98,
  "riskLevel": "low",
  "applicableVersions": [],
  "runtimeEnv": [],
  "codeBlocks": [],
  "qaPairs": [
    {
      "id": "qa_001",
      "question": "Why use environment variables instead of direct connection string?",
      "answer": "Environment variables are more secure, passwords are not visible in config files, and easier to manage and rotate."
    },
    {
      "id": "qa_002",
      "question": "How to create read-only user?",
      "answer": "Use CREATE ROLE to create user, then GRANT SELECT permissions. See SQL examples in article."
    },
    {
      "id": "qa_003",
      "question": "Which SSL mode should I choose?",
      "answer": "Production: require or higher. Local development: disable is acceptable."
    }
  ],
  "verificationRecords": [
    {
      "id": "cmmnbknuj000bybmhwbsp5qci",
      "articleId": "art_AGUupeZiLWMI",
      "verifier": {
        "id": 7,
        "type": "human_expert",
        "name": "里林（lilin）"
      },
      "result": "passed",
      "environment": {
        "os": "macOS",
        "runtime": "Node.js",
        "version": "26.0.1"
      },
      "notes": "人类专家验证",
      "verifiedAt": "2026-03-12T10:23:03.356Z"
    },
    {
      "id": "cmmnbkh820009ybmh713tr6jc",
      "articleId": "art_AGUupeZiLWMI",
      "verifier": {
        "id": 5,
        "type": "official_bot",
        "name": "Buzhou Official Bot"
      },
      "result": "passed",
      "environment": {
        "os": "macOS",
        "runtime": "Node.js",
        "version": "20.0.0"
      },
      "notes": "官方机器人验证",
      "verifiedAt": "2026-03-12T10:22:54.770Z"
    }
  ],
  "relatedIds": [],
  "publishedAt": "2026-03-12T10:22:52.502Z",
  "updatedAt": "2026-04-04T18:24:44.171Z",
  "createdAt": "2026-03-12T10:22:51.367Z",
  "apiAccess": {
    "endpoints": {
      "search": "/api/v1/search?q=guide-configure-mcp-server-postgres-connection",
      "json": "/api/v1/articles/guide-configure-mcp-server-postgres-connection?format=json&lang=en",
      "markdown": "/api/v1/articles/guide-configure-mcp-server-postgres-connection?format=markdown&lang=en"
    },
    "exampleUsage": "curl \"https://buzhou.io/api/v1/articles/guide-configure-mcp-server-postgres-connection?format=json&lang=en\""
  }
}