# PostgreSQL Read-Only MCP Server

A secure MCP (Model Context Protocol) server that provides read-only access to PostgreSQL databases through an SSH tunnel.

## Features

- **Secure SSH Tunnel**: Connects to PostgreSQL through encrypted SSH tunnel
- **Read-Only Enforcement**: All queries run in read-only transactions
- **Connection Pooling**: Efficient database connection management
- **Query Timeout**: 15-second timeout for all operations
- **Comprehensive Tools**: Query, list tables, describe schemas, analyze data

## Configuration

1. Copy the example configuration file:
   ```bash
   cp .env.example ~/.pg_mcp/.env
   ```

2. Edit `~/.pg_mcp/.env` with your credentials:
   ```bash
   # PostgreSQL Database Configuration
   POSTGRES_DB=your_database
   POSTGRES_USER=readonly_user
   POSTGRES_PASSWORD=your_password

   # SSH Tunnel Configuration
   SSH_HOST=your-ec2-instance.amazonaws.com
   SSH_USER=ec2-user
   SSH_KEY_PATH=/path/to/ssh/key

   # RDS Configuration
   RDS_HOST=your-database.rds.amazonaws.com
   ```

3. Create a read-only database user:
   ```sql
   CREATE USER readonly_mcp WITH PASSWORD 'secure_password';
   GRANT CONNECT ON DATABASE your_database TO readonly_mcp;
   GRANT USAGE ON SCHEMA public TO readonly_mcp;
   GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly_mcp;
   ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly_mcp;
   ```

## Installation

```bash
pnpm install
pnpm run build
```

## Usage

Start the MCP server:
```bash
./start_mcp.sh
```

For development mode with hot reload:
```bash
./start_mcp.sh dev
```

## Available MCP Tools

- `postgres_query`: Execute read-only SQL queries
- `postgres_list_tables`: List all tables with optional row counts
- `postgres_describe_table`: Get detailed schema information
- `postgres_analyze_table`: Analyze table for data quality issues
- `postgres_find_related`: Find foreign key relationships
- `postgres_explain_query`: Get query execution plans

## SSH Tunnel Management

The server implements a shared SSH tunnel system:
- Multiple instances share a single tunnel
- Reference counting prevents premature closure
- Automatic reconnection on failure
- Lock files in `/tmp/pg_mcp_tunnel/`

Monitor tunnel status:
```bash
./check_tunnel.sh
```

## Security

- All queries validated for read-only operations
- Enforced read-only transactions
- 15-second query timeout
- SSH encryption for all connections
- Credentials stored outside repository

## Requirements

- Node.js v24+
- pnpm package manager
- PostgreSQL database
- SSH access to database server