# PostgreSQL MCP Server
A comprehensive Model Context Protocol (MCP) server for PostgreSQL database access. Provides 36 tools for querying, managing, and interacting with PostgreSQL databases through the MCP interface.
## Features
- **36 Database Tools**: Complete set of read-only and write operations
- **PostgreSQL-Specific Features**: Schema support, JSONB operations, extensions, functions, triggers, views, sequences
- **Full SSL/TLS Support**: CA certificates, client certificates, configurable TLS versions
- **Security First**: Query validation, rate limiting, blocked dangerous operations
- **Connection Pooling**: Efficient connection management with configurable limits
- **Audit Logging**: Track all database operations
## Installation
```bash
# Clone or copy to your tools directory
cd /path/to/tools/mav-postgresql-mcp-server
# Install dependencies
npm install
# Build the server
npm run build
```
## Configuration
Copy `.env.example` to `.env` and configure your PostgreSQL connection:
```bash
cp .env.example .env
```
### Required Settings
| Variable | Description | Default |
|----------|-------------|---------|
| `PG_HOST` | PostgreSQL server hostname | `localhost` |
| `PG_PORT` | PostgreSQL server port | `5432` |
| `PG_USER` | Database username | `postgres` |
| `PG_PASSWORD` | Database password | - |
| `PG_DATABASE` | Target database name | - |
| `PG_SCHEMA` | Default schema | `public` |
### SSL Configuration
| Variable | Description | Options |
|----------|-------------|---------|
| `PG_SSL_MODE` | SSL connection mode | `disable`, `require`, `verify-ca`, `verify-full` |
| `PG_SSL_REJECT_UNAUTHORIZED` | Reject self-signed certs | `true`, `false` |
| `PG_SSL_CA_PATH` | Path to CA certificate | - |
| `PG_SSL_CERT_PATH` | Path to client certificate | - |
| `PG_SSL_KEY_PATH` | Path to client key | - |
| `PG_SSL_MIN_VERSION` | Minimum TLS version | `TLSv1.2`, `TLSv1.3` |
### Security Settings
| Variable | Description | Default |
|----------|-------------|---------|
| `ALLOW_WRITE_OPERATIONS` | Enable INSERT/UPDATE/DELETE | `false` |
| `CONNECTION_LIMIT` | Max pool connections | `10` |
| `QUERY_TIMEOUT` | Query timeout (ms) | `30000` |
| `MAX_RESULTS` | Maximum rows returned | `1000` |
### Rate Limiting
| Variable | Description | Default |
|----------|-------------|---------|
| `RATE_LIMIT_PER_MINUTE` | Queries per minute | `60` |
| `RATE_LIMIT_PER_HOUR` | Queries per hour | `1000` |
| `RATE_LIMIT_CONCURRENT` | Concurrent queries | `10` |
## Usage
### With Claude Desktop
Add to your Claude Desktop configuration (`~/Library/Application Support/Claude/claude_desktop_config.json` on macOS):
```json
{
"mcpServers": {
"postgresql": {
"command": "node",
"args": ["/path/to/mav-postgresql-mcp-server/build/index.js"],
"env": {
"PG_HOST": "localhost",
"PG_PORT": "5432",
"PG_USER": "your_user",
"PG_PASSWORD": "your_password",
"PG_DATABASE": "your_database",
"PG_SCHEMA": "public",
"ALLOW_WRITE_OPERATIONS": "false"
}
}
}
}
```
### With MCP Inspector
```bash
npx @anthropic/mcp-inspector node build/index.js
```
## Available Tools
### Core Read-Only Tools (7)
| Tool | Description |
|------|-------------|
| `query` | Execute SELECT queries |
| `list_tables` | List all tables in schema |
| `describe_table` | Get table structure and columns |
| `database_info` | Get database version and settings |
| `show_indexes` | List indexes on a table |
| `explain_query` | Get query execution plan |
| `show_constraints` | List table constraints |
### PostgreSQL-Specific Read-Only Tools (14)
| Tool | Description |
|------|-------------|
| `list_schemas` | List all schemas in database |
| `get_current_schema` | Get current search path |
| `list_extensions` | List installed extensions |
| `extension_info` | Get detailed extension information |
| `list_functions` | List user-defined functions |
| `list_triggers` | List triggers on a table |
| `list_views` | List views in schema |
| `list_sequences` | List sequences in schema |
| `table_stats` | Get table statistics |
| `connection_info` | Get current connection details |
| `database_size` | Get database/table sizes |
| `jsonb_query` | Query JSONB columns |
| `jsonb_path_query` | Execute JSON path queries |
### Write Operation Tools (15)
*Requires `ALLOW_WRITE_OPERATIONS=true`*
| Tool | Description |
|------|-------------|
| `insert` | Insert a single row |
| `update` | Update rows with conditions |
| `delete` | Delete rows with conditions |
| `create_table` | Create a new table |
| `alter_table` | Modify table structure |
| `drop_table` | Drop a table |
| `bulk_insert` | Insert multiple rows |
| `execute_procedure` | Call stored procedures |
| `add_index` | Create an index |
| `drop_index` | Remove an index |
| `rename_table` | Rename a table |
| `set_search_path` | Change schema search path |
| `create_schema` | Create a new schema |
| `drop_schema` | Drop a schema |
| `jsonb_update` | Update JSONB fields |
| `vacuum_analyze` | Optimize table statistics |
## MCP Resources
The server exposes database schema as MCP resources:
- `pg://database/schema` - List all tables and columns
- `pg://database/info` - Database information
- `pg://table/{schema}.{table}` - Individual table schema
## Security Features
### Blocked Operations
The server blocks dangerous operations by default:
- File system operations (`COPY FROM/TO`, `pg_read_file`, etc.)
- Permission modifications (`GRANT`, `REVOKE`, `ALTER ROLE`)
- Administrative commands (`CREATE ROLE`, `DROP DATABASE`, etc.)
- System catalog modifications
### Protected Tables
Access to sensitive system tables is blocked:
- `pg_catalog.pg_authid`
- `pg_catalog.pg_shadow`
- `pg_catalog.pg_auth_members`
### Query Validation
- All identifiers are validated (max 63 characters, safe characters only)
- Query timeouts prevent long-running operations
- Rate limiting prevents abuse
## Setting Up a Read-Only User
For production use, create a dedicated read-only PostgreSQL user:
```bash
# Run as PostgreSQL superuser
psql -U postgres -f setup-readonly-user.sql
```
Or manually:
```sql
-- Create user
CREATE USER mcp_readonly WITH PASSWORD 'secure_password';
-- Grant connect
GRANT CONNECT ON DATABASE your_database TO mcp_readonly;
-- Grant schema usage
GRANT USAGE ON SCHEMA public TO mcp_readonly;
-- Grant read access to all tables
GRANT SELECT ON ALL TABLES IN SCHEMA public TO mcp_readonly;
-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO mcp_readonly;
```
## Development
```bash
# Run in development mode
npm run dev
# Build for production
npm run build
# Type checking
npm run typecheck
```
## Troubleshooting
### Connection Issues
1. Verify PostgreSQL is running: `pg_isready -h localhost -p 5432`
2. Check credentials: `psql -h localhost -U your_user -d your_database`
3. Enable debug mode: `MCP_DEBUG=true`
### SSL Issues
1. Verify certificate paths are correct
2. Check certificate permissions (readable by the user running the server)
3. Try `PG_SSL_MODE=require` first, then upgrade to `verify-ca` or `verify-full`
### Rate Limiting
If you're hitting rate limits:
1. Increase `RATE_LIMIT_PER_MINUTE` and `RATE_LIMIT_PER_HOUR`
2. Batch operations where possible
3. Use more specific queries to reduce call volume
## License
MIT
