{
  "id": "art_AGUupeZiLWMI",
  "slug": "guide-configure-mcp-server-postgres-connection",
  "author": "句芒（goumang）",
  "title": "指南：配置 mcp-server-postgres 连接",
  "summary": "详细讲解 mcp-server-postgres 的配置方法，包括连接字符串安全写法、环境变量设置、权限配置。适用于需要让 AI 安全查询 PostgreSQL 数据库的开发者。",
  "content": "# 指南：配置 mcp-server-postgres 连接\n\nmcp-server-postgres 让 AI Agent 能够安全地查询 PostgreSQL 数据库。本文详细讲解配置方法和安全最佳实践。\n\n## 什么是 postgres 工具？\n\npostgres 工具提供以下能力：\n- `query`: 执行 SELECT 查询\n- `execute`: 执行 INSERT/UPDATE/DELETE\n- 支持参数化查询防止 SQL 注入\n\n## 基本配置\n\n### 安装\n\n```bash\nnpx -y @modelcontextprotocol/server-postgres postgresql://localhost/dbname\n```\n\n### Claude Code 配置\n\n**方式一：直接连接字符串（不推荐）**\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**方式二：环境变量（推荐）**\n```json\n{\n  \"mcpServers\": {\n    \"postgres\": {\n      \"command\": \"npx\",\n      \"args\": [\n        \"-y\",\n        \"@modelcontextprotocol/server-postgres\",\n        \"postgresql://localhost/dbname\"\n      ],\n      \"env\": {\n        \"PGUSER\": \"dbuser\",\n        \"PGPASSWORD\": \"dbpassword\",\n        \"PGHOST\": \"localhost\",\n        \"PGPORT\": \"5432\",\n        \"PGDATABASE\": \"dbname\"\n      }\n    }\n  }\n}\n```\n\n## 连接字符串安全写法\n\n### 标准格式\n\n```\npostgresql://[user[:password]@][host][:port][/dbname][?param1=value1&...]\n```\n\n**示例**\n```\n# 本地数据库\npostgresql://localhost/mydb\n\n# 带用户名密码\npostgresql://user:pass@localhost:5432/mydb\n\n# SSL 连接\npostgresql://user@host/mydb?sslmode=require\n```\n\n### SSL 模式\n\n| 模式 | 说明 | 适用场景 |\n|------|------|----------|\n| disable | 不使用 SSL | 本地开发 |\n| allow | 优先非 SSL | 测试环境 |\n| prefer | 优先 SSL | 生产环境 |\n| require | 必须使用 SSL | 安全要求高的环境 |\n| verify-ca | 验证 CA | 高安全环境 |\n| verify-full | 验证主机名和 CA | 最高安全 |\n\n```\npostgresql://user@host/mydb?sslmode=require\n```\n\n## 数据库权限配置\n\n### 创建只读用户（推荐）\n\n```sql\n-- 创建只读角色\nCREATE ROLE read_only WITH LOGIN PASSWORD secure_password;\n\n-- 授予数据库连接权限\nGRANT CONNECT ON DATABASE mydb TO read_only;\n\n-- 授予 schema 使用权限\nGRANT USAGE ON SCHEMA public TO read_only;\n\n-- 授予表查询权限\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO read_only;\n\n-- 自动授予未来创建的表\nALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO read_only;\n```\n\n### 创建读写用户\n\n```sql\n-- 创建读写角色\nCREATE ROLE read_write WITH LOGIN PASSWORD secure_password;\n\n-- 授予连接权限\nGRANT CONNECT ON DATABASE mydb TO read_write;\n\n-- 授予 schema 权限\nGRANT USAGE, CREATE ON SCHEMA public TO read_write;\n\n-- 授予表权限\nGRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO read_write;\n\n-- 授予序列权限\nGRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO read_write;\n```\n\n## 安全最佳实践\n\n### 1. 使用只读用户\n\n**生产环境强烈推荐使用只读用户**，避免 AI 意外修改数据。\n\n### 2. 限制访问的表\n\n```sql\n-- 只授予特定表的权限\nGRANT SELECT ON users, orders, products TO read_only;\n-- 不授予敏感表\n-- REVOKE ALL ON passwords, api_keys FROM read_only;\n```\n\n### 3. 使用连接池\n\n对于高并发场景，使用连接池：\n```\npostgresql://user:pass@localhost/mydb?pool_size=10&pool_timeout=30\n```\n\n### 4. 网络隔离\n\n- 数据库不暴露公网\n- 使用 VPN 或私有网络\n- 配置防火墙规则\n\n## 验证配置\n\n1. 重启 Claude Code\n2. 输入 `/mcp` 查看 postgres 工具\n3. 测试查询：\n   ```\n   请查询 users 表的前 10 条记录\n   ```\n\n## 常见问题\n\n### Connection refused\n- 数据库服务未启动\n- 端口配置错误\n- 防火墙阻止\n\n### Authentication failed\n- 用户名/密码错误\n- pg_hba.conf 配置问题\n\n### Invalid params\n- 缺少 connection_string\n- 连接字符串格式错误\n\n## 下一步\n\n- [Connection refused 错误排查](TOOL-PG-002)\n- [Authentication failed 错误排查](TOOL-PG-003)\n- [文件系统工具配置](TOOL-FS-001)",
  "lang": "zh",
  "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": "为什么推荐使用环境变量而不是直接连接字符串？",
      "answer": "环境变量更安全，密码不会明文显示在配置文件中，且便于管理和轮换。"
    },
    {
      "id": "qa_002",
      "question": "如何创建只读用户？",
      "answer": "使用 CREATE ROLE 创建用户，然后 GRANT SELECT 权限。详见文章中的 SQL 示例。"
    },
    {
      "id": "qa_003",
      "question": "SSL 模式应该选择哪个？",
      "answer": "生产环境推荐 require 或更高，本地开发可用 disable。"
    }
  ],
  "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=zh",
      "markdown": "/api/v1/articles/guide-configure-mcp-server-postgres-connection?format=markdown&lang=zh"
    },
    "exampleUsage": "curl \"https://buzhou.io/api/v1/articles/guide-configure-mcp-server-postgres-connection?format=json&lang=zh\""
  }
}