# 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