# PostgreSQL MCP Server

A production-ready Model Context Protocol (MCP) server that enables Claude to execute read-only SQL queries against PostgreSQL databases safely and securely.

## Features

- **Read-Only Queries**: Only SELECT statements allowed - no data modification
- **Security First**: Comprehensive SQL injection prevention and query validation
- **Connection Pooling**: Efficient database connection management
- **Row Limits**: Automatic enforcement of query result limits
- **Production Ready**: Structured logging, error handling, and graceful shutdown
- **Type Safe**: Built with TypeScript for reliability
- **VPS Ready**: Includes deployment guides for remote VPS setups

[Read the Deployment Guide](./DEPLOYMENT.md) for instructions on setting up the server on a VPS.

## Installation

```bash
npm install
```

## Configuration

Create a `.env` file based on `.env.example`:

```bash
# Required
DATABASE_URL=postgresql://user:password@localhost:5432/dbname

# Optional (with defaults)
NODE_ENV=development
LOG_LEVEL=info
DEFAULT_QUERY_LIMIT=100
MAX_QUERY_LIMIT=10000
QUERY_TIMEOUT_MS=30000
```

### Environment Variables

- `DATABASE_URL` (required): PostgreSQL connection string
- `NODE_ENV`: Environment mode (`development`, `production`, `test`)
- `LOG_LEVEL`: Logging level (`trace`, `debug`, `info`, `warn`, `error`, `fatal`)
- `DEFAULT_QUERY_LIMIT`: Default row limit for queries (default: 100)
- `MAX_QUERY_LIMIT`: Maximum allowed row limit (default: 10000)
- `QUERY_TIMEOUT_MS`: Query execution timeout in milliseconds (default: 30000)

## Development

```bash
# Run in development mode with auto-reload (no build needed)
npm run dev

# Build the project (compile TypeScript to JavaScript)
npm run build

# Run the production build (after npm run build)
npm start

# Run tests
npm test

# Run tests in watch mode
npm run test:watch

# Run tests with coverage
npm run test:coverage

# Type check
npm run type-check

# Lint
npm run lint

# Format code
npm run format
```

## Local Testing

Test your MCP server locally before integrating with Claude Desktop:

### Option 1: Automated Test Script (Recommended)

```bash
# Ensure .env file is configured with DATABASE_URL
npm run test:local
```

This runs automated tests that verify:
- Server starts correctly
- Tools are registered
- Queries execute successfully
- Security validation works
- Row limits are enforced

### Option 2: MCP Inspector (Interactive)

```bash
# Build first
npm run build

# Start the inspector
npm run inspect
```

This opens a web UI where you can:
- Browse available tools
- Execute queries interactively
- View responses and debug messages
- Test different parameters

### Option 3: Manual Testing with PostgreSQL Client

```bash
# Run the dev server
npm run dev

# In another terminal, test your database connection
psql $DATABASE_URL -c "SELECT 1"
```

## Usage with Claude Desktop

### 1. Build the Project

```bash
npm run build
```

### 2. Configure Claude Desktop

Add to your Claude Desktop config file:

**macOS**: `~/Library/Application Support/Claude/claude_desktop_config.json`
**Windows**: `%APPDATA%\Claude\claude_desktop_config.json`

```json
{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/absolute/path/to/mcp-server/dist/index.js"],
      "env": {
        "DATABASE_URL": "postgresql://user:password@localhost:5432/dbname"
      }
    }
  }
}
```

### 3. Restart Claude Desktop

Restart Claude Desktop to load the MCP server.

### 4. Test the Connection

Try asking Claude:
- "List all tables in the database"
- "Show me the first 5 rows from the users table"
- "Count the total number of records in the orders table"

## Available Tools

### execute_query

Execute a read-only SQL SELECT query against the PostgreSQL database.

**Parameters:**
- `query` (string, required): The SQL SELECT query to execute
- `limit` (number, optional): Row limit for results (default: from config, max: 10000)

**Example:**

```typescript
{
  "query": "SELECT id, name, email FROM users WHERE active = true",
  "limit": 50
}
```

**Response:**

```json
{
  "success": true,
  "data": {
    "rows": [...],
    "rowCount": 42,
    "fields": [
      { "name": "id", "dataType": "integer" },
      { "name": "name", "dataType": "text" },
      { "name": "email", "dataType": "varchar" }
    ],
    "executionTimeMs": 15
  }
}
```

## Security

### Query Validation

The server implements multiple layers of security:

1. **SQL Parsing**: Queries are parsed using `node-sql-parser` to ensure valid syntax
2. **Statement Type Check**: Only SELECT statements are allowed
3. **Dangerous Function Detection**: Blocks PostgreSQL functions like `pg_read_file`, `COPY`, etc.
4. **Comment Removal**: SQL comments are stripped to prevent comment-based injection
5. **Row Limit Enforcement**: Automatic LIMIT clauses prevent excessive data retrieval
6. **Read-Only Validation**: Additional layer ensures no data modification

### Blocked Operations

- INSERT, UPDATE, DELETE, TRUNCATE
- CREATE, DROP, ALTER (DDL operations)
- GRANT, REVOKE (permission changes)
- SELECT INTO (data copying)
- FOR UPDATE/FOR SHARE (row locking)
- Dangerous functions (pg_read_file, pg_ls_dir, COPY, etc.)

### Best Practices

1. **Use Read-Only Database Users**: Create a database user with SELECT-only permissions
2. **Limit Database Access**: Only grant access to necessary schemas/tables
3. **Use SSL/TLS**: Enable SSL for database connections in production
4. **Monitor Queries**: Review logs regularly for suspicious activity
5. **Set Resource Limits**: Configure appropriate query timeouts and row limits

## Testing

### Unit Tests

```bash
npm test
```

Unit tests cover:
- Query validation and sanitization
- Security checks (SQL injection, dangerous functions)
- Row limit enforcement

### Integration Tests

Integration tests require a running PostgreSQL instance:

```bash
# Start PostgreSQL (example with Docker)
docker run --name test-postgres \
  -e POSTGRES_PASSWORD=testpass \
  -p 5432:5432 \
  -d postgres:16

# Set DATABASE_URL and run tests
export DATABASE_URL=postgresql://postgres:testpass@localhost:5432/postgres
npm test
```

## Architecture

```
src/
├── index.ts              # Entry point
├── server.ts             # MCP server setup
├── config/
│   └── index.ts         # Configuration loader
├── database/
│   └── connection.ts    # PostgreSQL connection with pooling
├── security/
│   ├── query-validator.ts      # SQL validation
│   └── read-only-validator.ts  # Read-only enforcement
├── tools/
│   └── query-tool.ts    # execute_query tool
└── utils/
    ├── logger.ts        # Structured logging
    └── types.ts         # TypeScript types
```

## Error Handling

The server provides detailed error messages:

- **ValidationError**: Invalid query syntax or parameters
- **SecurityError**: SQL injection attempts or unauthorized operations
- **DatabaseError**: Connection failures or query execution errors
- **ConfigurationError**: Missing or invalid configuration

## Logging

Structured JSON logging in production, pretty-printed in development:

```json
{
  "level": "info",
  "time": "2024-01-11T10:30:00.000Z",
  "msg": "Query executed successfully",
  "rowCount": 42,
  "executionTimeMs": 15
}
```

## Troubleshooting

### Connection Issues

- Verify `DATABASE_URL` is correct
- Check database is running and accessible
- Ensure firewall allows PostgreSQL connections
- Test connection with `psql` command

### Permission Errors

- Ensure database user has SELECT permissions
- Check schema access permissions
- Verify connection string includes correct database name

### Query Timeouts

- Increase `QUERY_TIMEOUT_MS` for long-running queries
- Optimize slow queries with indexes
- Reduce row limits if fetching too much data

## Future Enhancements (v2)

- Multi-database support
- Additional tools (list_tables, describe_table, get_schema)
- HTTP transport for remote access
- Schema caching
- Query history logging
- Prometheus metrics export

## License

MIT

## Contributing

Contributions are welcome! Please ensure:
- Tests pass (`npm test`)
- Code is formatted (`npm run format`)
- Types are valid (`npm run type-check`)
- Security best practices are followed