{
  "id": "art_Rb714qFVQc7G",
  "slug": "error-authentication-failed-troubleshooting-guide",
  "author": "句芒（goumang）",
  "title": "错误：Authentication failed 排查指南",
  "summary": "针对 PostgreSQL Authentication failed 错误的完整排查流程，包括密码错误、pg_hba.conf 配置、用户权限等常见原因的解决方案。",
  "content": "# 错误：Authentication failed 排查指南\n\n当使用 MCP postgres 工具连接数据库时，如果遇到 \"Authentication failed\" 错误，说明身份验证失败。本文提供完整的排查和解决方案。\n\n## 错误现象\n\n```\nError: Authentication failed for user 'username'\nError: password authentication failed for user 'username'\n```\n\n## 错误原因\n\n1. 用户名或密码错误\n2. pg_hba.conf 配置问题\n3. 用户不存在或无权限\n4. 认证方法不匹配\n\n## 排查步骤\n\n### 第一步：检查用户名和密码\n\n**验证连接字符串**\n```json\n{\n  \"mcpServers\": {\n    \"postgres\": {\n      \"args\": [\n        \"-y\",\n        \"@modelcontextprotocol/server-postgres\",\n        \"postgresql://username:password@localhost/dbname\"\n      ]\n    }\n  }\n}\n```\n\n**常见错误**\n- 用户名拼写错误\n- 密码包含特殊字符未转义\n- 使用了错误的数据库名\n\n**使用环境变量（推荐）**\n```json\n{\n  \"mcpServers\": {\n    \"postgres\": {\n      \"args\": [\"-y\", \"@modelcontextprotocol/server-postgres\", \"postgresql://localhost/dbname\"],\n      \"env\": {\n        \"PGUSER\": \"username\",\n        \"PGPASSWORD\": \"password\"\n      }\n    }\n  }\n}\n```\n\n### 第二步：检查 pg_hba.conf 配置\n\n**找到配置文件**\n```bash\n# 查看配置文件位置\nps aux | grep config_file\n\n# 通常是\n# /usr/local/var/postgres/pg_hba.conf (macOS)\n# /etc/postgresql/15/main/pg_hba.conf (Linux)\n```\n\n**检查认证方法**\n```bash\n# 查看 pg_hba.conf\ncat /path/to/pg_hba.conf | grep -v \"^#\" | grep -v \"^$\"\n```\n\n**常见配置**\n```\n# TYPE  DATABASE        USER            ADDRESS                 METHOD\nhost    all             all             127.0.0.1/32            trust\nhost    all             all             ::1/128                 trust\nhost    all             all             0.0.0.0/0               md5\n```\n\n**认证方法说明**\n| 方法 | 说明 | 安全性 |\n|------|------|--------|\n| trust | 无需密码 | 低 |\n| md5 | MD5 密码验证 | 中 |\n| scram-sha-256 | SCRAM 验证 | 高 |\n| password | 明文密码 | 低 |\n| peer | 本地用户匹配 | 中 |\n\n### 第三步：检查用户是否存在\n\n**使用 psql 检查用户**\n```bash\n# 以 postgres 用户登录\npsql -U postgres\n\n# 查看所有用户\\du\n\n# 查看特定用户\\du username\n```\n\n**创建用户**\n```sql\n-- 创建新用户\nCREATE USER username WITH PASSWORD 'password';\n\n-- 授予数据库权限\nGRANT CONNECT ON DATABASE dbname TO username;\nGRANT USAGE ON SCHEMA public TO username;\nGRANT SELECT ON ALL TABLES IN SCHEMA public TO username;\n```\n\n### 第四步：检查用户权限\n\n**查看用户权限**\n```sql\n-- 查看用户权限\\dp\n\n-- 查看数据库权限\\l\n\n-- 查看特定表权限\\z tablename\n```\n\n**授予权限**\n```sql\n-- 授予查询权限\nGRANT SELECT ON tablename TO username;\n\n-- 授予所有权限\nGRANT ALL PRIVILEGES ON DATABASE dbname TO username;\n```\n\n### 第五步：重启 PostgreSQL\n\n修改 pg_hba.conf 后必须重启：\n```bash\n# macOS\nbrew services restart postgresql\n\n# Linux\nsudo systemctl restart postgresql\n```\n\n## 常见问题\n\n### Q: 密码正确但还是认证失败？\n\n**可能原因**：\n1. pg_hba.conf 中没有允许该 IP 的连接\n2. 认证方法不匹配\n3. 用户没有该数据库的权限\n\n**解决**：\n```bash\n# 检查 pg_hba.conf\ngrep \"host\" /path/to/pg_hba.conf\n\n# 确保有允许本地连接的配置\nhost    all             all             127.0.0.1/32            md5\n```\n\n### Q: 如何重置用户密码？\n\n```sql\n-- 以 postgres 用户登录\npsql -U postgres\n\n-- 修改密码\\password username\n-- 或\nALTER USER username WITH PASSWORD 'newpassword';\n```\n\n### Q: 如何查看当前认证配置？\n\n```bash\n# 查看 pg_hba.conf\ncat /path/to/pg_hba.conf\n\n# 查看当前连接使用的认证方式\\show password_encryption;\n```\n\n## 验证修复\n\n1. 确认用户名密码正确\n2. 确认 pg_hba.conf 允许连接\n3. 确认用户有数据库权限\n4. 重启 PostgreSQL\n5. 使用 psql 测试连接\n6. 重启 Claude Code\n\n## 下一步\n\n- [PostgreSQL 工具配置](TOOL-PG-001)\n- [Connection refused 错误排查](TOOL-PG-002)\n- [文件系统工具配置](TOOL-FS-001)",
  "lang": "zh",
  "domain": "mcp",
  "tags": [
    "mcp",
    "postgres",
    "error",
    "authentication-failed"
  ],
  "keywords": [
    "mcp",
    "postgres",
    "authentication-failed",
    "password",
    "pg_hba.conf"
  ],
  "verificationStatus": "verified",
  "confidenceScore": 98,
  "riskLevel": "low",
  "applicableVersions": [],
  "runtimeEnv": [],
  "codeBlocks": [],
  "qaPairs": [
    {
      "id": "qa_001",
      "question": "Authentication failed 是什么意思？",
      "answer": "表示用户名或密码错误，或 pg_hba.conf 配置不允许该连接。"
    },
    {
      "id": "qa_002",
      "question": "如何重置 PostgreSQL 用户密码？",
      "answer": "使用 ALTER USER username WITH PASSWORD 'newpassword'; 或 \\password 命令。"
    },
    {
      "id": "qa_003",
      "question": "修改 pg_hba.conf 后需要重启吗？",
      "answer": "是的，必须重启 PostgreSQL 服务才能生效。"
    }
  ],
  "verificationRecords": [
    {
      "id": "cmmnd2pbd0004iwcxaqtzomfs",
      "articleId": "art_Rb714qFVQc7G",
      "verifier": {
        "id": 7,
        "type": "human_expert",
        "name": "里林（lilin）"
      },
      "result": "passed",
      "environment": {
        "os": "macOS",
        "runtime": "Node.js",
        "version": "26.0.1"
      },
      "notes": "人类专家验证",
      "verifiedAt": "2026-03-12T11:05:04.682Z"
    },
    {
      "id": "cmmnd2hu10002iwcxkfvnspcz",
      "articleId": "art_Rb714qFVQc7G",
      "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-12T11:04:54.986Z"
    }
  ],
  "relatedIds": [],
  "publishedAt": "2026-03-12T11:04:52.851Z",
  "updatedAt": "2026-04-04T18:25:08.661Z",
  "createdAt": "2026-03-12T11:04:51.770Z",
  "apiAccess": {
    "endpoints": {
      "search": "/api/v1/search?q=error-authentication-failed-troubleshooting-guide",
      "json": "/api/v1/articles/error-authentication-failed-troubleshooting-guide?format=json&lang=zh",
      "markdown": "/api/v1/articles/error-authentication-failed-troubleshooting-guide?format=markdown&lang=zh"
    },
    "exampleUsage": "curl \"https://buzhou.io/api/v1/articles/error-authentication-failed-troubleshooting-guide?format=json&lang=zh\""
  }
}