内容
# PostgreSQL MCP Server with OAuth
[](https://smithery.ai/server/@gldc/mcp-postgres)
<a href="https://glama.ai/mcp/servers/@gldc/mcp-postgres">
<img width="380" height="200" src="https://glama.ai/mcp/servers/@gldc/mcp-postgres/badge" />
</a>
一个PostgreSQL MCP server实现，支持使用[Model Context Protocol (MCP)](https://github.com/modelcontextprotocol) Python SDK的 **OAuth authentication**。该服务器使AI代理能够通过标准化接口与PostgreSQL数据库进行交互，具有安全的多用户身份验证和云部署功能。
## ✨ 新功能
- **🔐 OAuth Authentication**: 安全的Google OAuth 2.0集成
- **👥 Multi-tenant**: 每个用户都有自己的数据库连接
- **☁️ Railway Ready**: 针对Railway云部署进行了优化
- **🛡️ Production Safe**: 会话管理、安全控制和健康监控
## Features
### Core Database Operations
- 列出数据库模式，具有高级过滤和分页功能
- 列出模式中的表，具有模式匹配功能
- 描述表结构和约束
- 发现表关系（显式外键+隐式关系）
- 执行具有安全控制的SQL查询
- 具有JSON/markdown输出的类型化工具
- 可选的表资源和指导提示
### Authentication & Security
- **Google OAuth 2.0**: 安全的用户身份验证
- **Session Management**: 基于令牌的会话，具有过期时间
- **User Isolation**: 每个用户的数据库连接都是独立的
- **Read-only Mode**: 可选的查询限制
- **Query Timeouts**: 防止失控查询
- **Health Monitoring**: 内置的健康检查和指标
### Deployment Options
- **Local Development**: 直接数据库连接
- **OAuth Mode**: 具有个人数据库连接的多用户身份验证
- **Railway Cloud**: 通过托管PostgreSQL一键式云部署
- **Docker**: 容器化部署
## 🚀 快速开始
### Option 1: Traditional Mode (Direct Connection)
```bash
# Run with direct database connection (original behavior)
export POSTGRES_CONNECTION_STRING="postgresql://user:pass@host:5432/db"
python postgres_server.py
# Or pass connection string as argument
python postgres_server.py --conn "postgresql://user:pass@host:5432/db"
```
### Option 2: OAuth Mode (Multi-user)
```bash
# Set up OAuth credentials (see OAUTH_SETUP.md for details)
export GOOGLE_CLIENT_ID="your_client_id.apps.googleusercontent.com"
export GOOGLE_CLIENT_SECRET="your_client_secret"
export SECRET_KEY="your_32_character_secret_key"
# Run in OAuth mode
python postgres_server.py --oauth-only --transport streamable-http --port 8000
```
### Option 3: Railway Cloud Deployment
```bash
# One-click deployment to Railway (see RAILWAY_DEPLOYMENT.md)
# 1. Push code to GitHub
# 2. Connect Railway to your repository
# 3. Create TWO services from this repo (same project):
# - Service A (MCP server): default settings (SERVICE_ROLE defaults to mcp)
# - Service B (OAuth companion): set SERVICE_ROLE=oauth; optionally set Environment=oauth
# 4. Add PostgreSQL to the project (Railway sets DATABASE_URL)
# 5. Set GOOGLE_CLIENT_ID / GOOGLE_CLIENT_SECRET / SECRET_KEY at project level
# 6. Deploy (railway.toml uses unified launcher: `python start.py`)
```
## 📚 文档
- **[OAUTH_SETUP.md](./OAUTH_SETUP.md)** - 完整的OAuth设置指南
- **[RAILWAY_DEPLOYMENT.md](./RAILWAY_DEPLOYMENT.md)** - Railway云部署指南
## Installation
### Installing via Smithery
要通过[Smithery](https://smithery.ai/server/@gldc/mcp-postgres)自动为Claude Desktop安装PostgreSQL MCP Server：
```bash
npx -y @smithery/cli install @gldc/mcp-postgres --client claude
```
### Manual Installation
1. 克隆此存储库：
```bash
git clone <repository-url>
cd mcp-postgres
```
2. 创建并激活虚拟环境（推荐）：
```bash
python -m venv .venv
source .venv/bin/activate # On Windows, use: .venv\Scripts\activate
```
3. 安装依赖项：
```bash
pip install -r requirements.txt
```
## Usage
### Authentication Flow (OAuth Mode)
1. **Start the server in OAuth mode**:
```bash
python postgres_server.py --oauth-only --transport streamable-http --port 8000
```
2. **Get authentication info** (via MCP client like Claude):
```
User: "Show me information about the database server"
Claude: [Calls auth_info tool, provides Google OAuth login URL]
```
3. **Complete OAuth flow**:
- Visit the provided login URL
- Authenticate with Google
- Receive session token
4. **Configure database connection**:
- Browser: visit `http://localhost:8000/connection` (uses your session cookie)
- API:
```bash
curl -X POST http://localhost:8000/connection/set \
-H "Authorization: Bearer YOUR_SESSION_TOKEN" \
-H "Content-Type: application/json" \
-d '{"connection_string": "postgresql://user:pass@host:port/db"}'
```
5. **Use database tools** - all operations now work with your authenticated connection
### Direct Mode (Traditional)
```bash
# Without a connection string (server starts, DB‑backed tools will return a friendly error)
python postgres_server.py
# Or set the connection string via environment variable:
export POSTGRES_CONNECTION_STRING="postgresql://username:password@host:port/database"
python postgres_server.py
# Or pass it using the --conn flag:
python postgres_server.py --conn "postgresql://username:password@host:port/database"
# Optional: Run over HTTP transports
# Streamable HTTP (recommended for streaming tool outputs)
python postgres_server.py --transport streamable-http --host 0.0.0.0 --port 8000
# SSE transport (server-sent events) mounted at /sse and /messages/
python postgres_server.py --transport sse --host 0.0.0.0 --port 8000 --mount /mcp
```
## Available Tools
### Core Database Tools
- `query`: 执行SQL查询数据库
- `list_schemas`: 列出所有可用的模式
- `list_tables`: 列出特定模式中的所有表
- `describe_table`: 获取有关表结构的详细信息
- `get_foreign_keys`: 获取表的外键关系
- `find_relationships`: 发现表的显式和隐式关系
- `db_identity`: 显示当前db/user/host/port、search_path和版本
### Authentication Tools (OAuth Mode)
- `auth_info`: 获取身份验证状态和登录说明
- `server_info`: 服务器配置和功能
### Typed Tools (Preferred)
- `run_query(input)`: 使用类型化输入执行（`sql`、`parameters`、`row_limit`、`format: 'markdown'|'json'`）
- `run_query_json(input)`: 执行并返回JSON可序列化的行
- `list_schemas_json(input)`: 使用过滤器列出模式（`include_system`、`include_temp`、`require_usage`、`row_limit`）
- `list_schemas_json_page(input)`: 使用过滤器和`name_like`模式进行分页列表
- `list_tables_json(input)`: 列出模式中的表，带有过滤器（名称模式、区分大小写、table_types、row_limit）
- `list_tables_json_page(input)`: 带有过滤器的分页表列表
### Example Tool Usage
```json
// run_query (markdown)
{
"sql": "SELECT * FROM information_schema.tables WHERE table_schema = %s",
"parameters": ["public"],
"row_limit": 50,
"format": "markdown"
}
// run_query_json
{
"sql": "SELECT now() as ts",
"row_limit": 1
}
// List schemas with filters
{
"include_system": false,
"include_temp": false,
"require_usage": true,
"row_limit": 10000
}
// Paginated list with pattern filter
{
"include_system": false,
"include_temp": false,
"require_usage": true,
"page_size": 200,
"cursor": null,
"name_like": "sales_*",
"case_sensitive": false
}
```
### Resources & Prompts
Resources (if supported by client):
- `table://{schema}/{table}` for reading table rows. Fallback tools are available:
- `list_table_resources(schema)` → `table://...` URIs
- `read_table_resource(schema, table, row_limit)` → rows JSON
Prompts (registered when supported; also exposed as tools):
- `write_safe_select` / `prompt_write_safe_select_tool`
- `explain_plan_tips` / `prompt_explain_plan_tips_tool`
## Configuration
### Claude Desktop Configuration
#### Traditional Mode
```json
{
"mcpServers": {
"postgres": {
"command": "/path/to/.venv/bin/python",
"args": ["/path/to/postgres_server.py"],
"env": {
"POSTGRES_CONNECTION_STRING": "postgresql://username:password@host:5432/database?ssl=true"
}
}
}
}
```
#### OAuth Mode (Local Development)
```json
{
"mcpServers": {
"postgres-oauth": {
"command": "/path/to/.venv/bin/python",
"args": [
"/path/to/postgres_server.py",
"--oauth-only",
"--transport", "streamable-http",
"--port", "8000"
],
"env": {
"GOOGLE_CLIENT_ID": "your_client_id.apps.googleusercontent.com",
"GOOGLE_CLIENT_SECRET": "your_client_secret",
"SECRET_KEY": "your_32_character_secret_key"
}
}
}
}
```
#### Railway Cloud Deployment
```json
{
"mcpServers": {
"postgres-railway": {
"transport": {
"type": "http",
"url": "https://your-app-name.railway.app"
}
}
}
}
```
### Environment Variables
#### Core Configuration
- `POSTGRES_CONNECTION_STRING`: 直接数据库连接（传统模式）
- `MCP_TRANSPORT`: `stdio|sse|streamable-http` (default: `stdio`)
- `MCP_HOST`: HTTP传输的主机（默认值：`127.0.0.1`）
- `MCP_PORT`: HTTP传输的端口（默认值：`8000`）
#### OAuth Configuration
- `GOOGLE_CLIENT_ID`: Google OAuth客户端ID（OAuth模式必需）
- `GOOGLE_CLIENT_SECRET`: Google OAuth客户端密钥（OAuth模式必需）
- `SECRET_KEY`: 会话加密密钥（32+字符，OAuth模式必需）
- `REDIRECT_URI`: OAuth重定向URI（为Railway自动配置）
#### Security & Performance
- `POSTGRES_READONLY`: `true` 仅允许SELECT/CTE/EXPLAIN/SHOW/VALUES查询
- `POSTGRES_STATEMENT_TIMEOUT_MS`: 查询超时时间（毫秒）（例如，`30000`）
#### Railway Deployment (Auto-configured)
- `DATABASE_URL`: 托管的PostgreSQL连接字符串
- `PORT`: 服务器端口（Railway提供）
- `RAILWAY_PUBLIC_DOMAIN`: OAuth重定向的公共域
- `RAILWAY_ENVIRONMENT`: 部署环境
### Running with Docker
Build the image:
```bash
docker build -t mcp-postgres .
```
Traditional mode:
```bash
docker run \
-e POSTGRES_CONNECTION_STRING="postgresql://username:password@host:5432/database" \
-p 8000:8000 \
mcp-postgres
```
OAuth mode:
```bash
docker run \
-e GOOGLE_CLIENT_ID="your_client_id" \
-e GOOGLE_CLIENT_SECRET="your_client_secret" \
-e SECRET_KEY="your_32_char_secret" \
-p 8000:8000 \
mcp-postgres
Unified launcher (optional, same image):
```bash
# MCP
docker run -e SERVICE_ROLE=mcp -p 8000:8000 mcp-postgres python start.py
# OAuth companion
docker run -e SERVICE_ROLE=oauth -e GOOGLE_CLIENT_ID=... -e GOOGLE_CLIENT_SECRET=... -e SECRET_KEY=... -p 8000:8000 mcp-postgres python start.py
```
## HTTP Client Integration
Run the server with Streamable HTTP:
```bash
python postgres_server.py --transport streamable-http --host 0.0.0.0 --port 8000
```
### Health Check (OAuth service)
```bash
curl http://localhost:8000/health
# Expected response:
{
"status": "healthy",
"service": "PostgreSQL MCP OAuth Companion",
"oauth_enabled": true,
"environment": "development"
}
```
### OAuth Endpoints (OAuth Mode)
- `GET /auth/login` - 获取Google OAuth授权URL
- `GET /auth/callback` - OAuth回调端点
- `GET /auth/status` - 检查身份验证状态
- `POST /connection/set` - 设置用户的数据库连接字符串
### Python MCP Client Example
```python
import asyncio
from mcp.client import streamable_http
from mcp.client.session import ClientSession
async def main():
url = "http://localhost:8000/mcp"
async with streamable_http.streamablehttp_client(url) as (read, write, _get_session_id):
session = ClientSession(read, write)
init = await session.initialize()
print("protocol:", init.protocolVersion)
# List tools
tools = await session.list_tools()
print("tools:", [t.name for t in tools.tools])
# Call typed tool: run_query_json
result = await session.call_tool(
"run_query_json",
{"input": {"sql": "SELECT 1 AS n", "row_limit": 1}},
)
print("result:", result.content[0].text if result.content else result.structuredContent)
if __name__ == "__main__":
asyncio.run(main())
```
## Security Best Practices
### Authentication & Authorization
- **OAuth Secrets**: 保持Google OAuth凭据安全，切勿提交到git
- **Session Keys**: 使用强SECRET_KEY（至少32+个字符）
- **Token Rotation**: 定期轮换OAuth凭据
- **User Isolation**: 每个经过身份验证的用户都有单独的数据库连接
### Database Security
- **Connection Strings**: 使用最小权限数据库连接
- **Read-only Mode**: 适当时启用`POSTGRES_READONLY=true`
- **Query Timeouts**: 设置`POSTGRES_STATEMENT_TIMEOUT_MS`以防止失控查询
- **SSL Connections**: 始终对生产数据库连接使用SSL
### Production Deployment
- **Environment Variables**: 切勿在代码或日志中公开密钥
- **HTTPS**: 对所有OAuth流程使用HTTPS（Railway自动提供）
- **Access Controls**: 实施适当的数据库用户权限
- **Connection Pooling**: 使用连接池以获得更好的资源管理
## Deployment Options
### 1. Local Development
- 直接数据库连接
- 使用localhost进行OAuth测试
- 开发环境变量
### 2. Railway Cloud (Recommended)
- 托管的PostgreSQL数据库
- 自动HTTPS和域管理
- 环境变量管理
- 从GitHub自动部署
- 内置监控和日志记录
- **Cost**: 大多数用例为每月5-20美元
### 3. Custom Cloud Deployment
- 任何支持Python应用程序的云提供商
- 使用Docker的基于容器的部署
- 手动OAuth配置
- 自定义域和SSL设置
## Migration Guide
### From Traditional to OAuth Mode
1. **Backup Current Setup**: 保存现有配置文件
2. **Set Up OAuth**: 按照[OAUTH_SETUP.md](./OAUTH_SETUP.md)指南
3. **Update Server**: 替换为启用OAuth的版本
4. **Test Locally**: 验证OAuth流程是否有效
5. **Deploy**: 使用Railway或自定义部署
6. **Update Clients**: 使用新的服务器URL配置Claude Desktop
### Backwards Compatibility
- 传统模式仍然可以使用`--conn`参数
- 现有的MCP客户端仍然兼容
- 保留所有原始工具和功能
## Contributing
欢迎贡献！请随时提交Pull Request。
### Development Setup
1. 创建一个`.venv`并安装运行时依赖项：`pip install -r requirements.txt`
2. (可选) 安装测试依赖项：`pip install -r dev-requirements.txt`
3. 设置OAuth凭据以进行测试（请参阅[OAUTH_SETUP.md](./OAUTH_SETUP.md)）
4. 运行测试：`pytest -q`
### Development & Testing
- **Local Testing**: 测试传统模式和OAuth模式
- **Integration Tests**: 使用实际的PostgreSQL数据库进行测试
- **OAuth Testing**: 测试完整的身份验证流程
- **Railway Testing**: 测试云部署
## Troubleshooting
### Common Issues
**OAuth not configured**:
- 验证是否已设置`GOOGLE_CLIENT_ID`和`GOOGLE_CLIENT_SECRET`
- 检查Google Cloud Console OAuth设置
**Authentication required**:
- 通过`/auth/login`端点完成OAuth流程
- 验证请求中是否包含会话令牌
**Database connection errors**:
- 验证连接字符串格式
- 独立测试数据库连接
- 检查防火墙和网络访问
**Railway deployment issues**:
- 检查环境变量是否设置正确
- 验证OAuth重定向URI是否与Railway域匹配
- 监控Railway仪表板中的部署日志
有关详细的故障排除，请参阅[OAUTH_SETUP.md](./OAUTH_SETUP.md)和[RAILWAY_DEPLOYMENT.md](./RAILWAY_DEPLOYMENT.md)。
## Related Projects
- [MCP Specification](https://github.com/modelcontextprotocol/specification)
- [MCP Python SDK](https://github.com/modelcontextprotocol/python-sdk)
- [MCP Servers](https://github.com/modelcontextprotocol/servers)
- [Railway](https://railway.app) - 云部署平台
- [Google OAuth 2.0](https://developers.google.com/identity/protocols/oauth2) - 身份验证提供程序
## License
MIT License
Copyright (c) 2025 gldc
Permission is hereby granted, free of charge, to any person obtaining a copy
of this software and associated documentation files (the "Software"), to deal
in the Software without restriction, including without limitation the rights
to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
copies of the Software, and to permit persons to whom the Software is
furnished to do so, subject to the following conditions:
The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.
THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
SOFTWARE.
