README.md•2.34 kB
# MCP Postgres Server
A Model Context Protocol server for querying PostgreSQL databases with tools for data retrieval and schema inspection.
## Setup
**Prerequisites**: Python 3.11+ and PostgreSQL
```bash
# Install dependencies
pip install -r requirements.txt
# Configure database connection (defaults shown)
export POSTGRES_HOST=localhost
export POSTGRES_PORT=5432
export POSTGRES_USER=postgres
export POSTGRES_PASSWORD=postgres
export POSTGRES_DB=housing
```
## Usage
**Development**
Run the following command to start the server in development mode and launch the MCP Inspector:
```bash
mcp dev main.py
```
**MCP Client Integration**
You can also add the following to your MCP client configuration to use the server:
```json
{
"mcpServers": {
"postgres": {
"command": "python",
"args": ["main.py"],
"env": {
"POSTGRES_HOST": "localhost",
"POSTGRES_PORT": "5432",
"POSTGRES_USER": "postgres",
"POSTGRES_PASSWORD": "postgres",
"POSTGRES_DB": "housing"
}
}
}
}
```
See [mcp.json](.cursor/mcp.json) for an example of how to add the server in Cursor IDE.
## Available Tools
- **`query_database`** - Execute SELECT queries (with SQL injection protection)
- **`get_table_schema`** - Get table column information and constraints
- **`list_tables`** - List all database tables
- **`postgres://schema`** - Access complete database schema (resource)
## Comparison with Official MCP Postgres Server
This implementation differs from the official [@modelcontextprotocol/server-postgres](https://www.npmjs.com/package/@modelcontextprotocol/server-postgres) in several key ways:
| Feature | This Server (Python) | Official Server (TypeScript) |
|---------|---------------------|------------------------------|
| **Safety** | Guards against SQL injection | No protection |
| **Connection Pooling** | ✅ Built-in (asyncpg) | ❌ Single connection |
## Security
- Only `SELECT` statements allowed
- Parameterized queries supported
- Environment-based configuration (no hardcoded credentials)
## Next Steps
- [ ] Implement auth for postgres - currently I'm still relying on env variables which doesn't seem very secure
- [ ] Extend this to run on multiple databases and potentially multiple postgres instances as well
- [ ] Implement table / column pruning