✨ Features

🏗️ Architecture

graph LR
    A[Claude/AI Assistant] -->|MCP Protocol| B[PostgreSQL MCP Server]
    B -->|READ ONLY Transactions| C[(PostgreSQL Database)]
    
    style B fill:#4CAF50,color:#fff
    style C fill:#336791,color:#fff

The server acts as a secure bridge between AI assistants and your PostgreSQL database, enforcing read-only access at the database transaction level.

📦 Installation

git clone https://github.com/YohannHommet/pg-lens-mcp.git
cd pg-lens-mcp
npm install
npm run build

⚙️ Configuration

Environment Variables

Configure the connection using environment variables:

MCP Configuration

Add to your Claude Desktop config (~/.claude/claude_desktop_config.json):

Option 1: Direct Node.js (Local Installation)

{
  "mcpServers": {
    "postgres": {
      "command": "node",
      "args": ["/absolute/path/to/postgres-server/dist/index.js"],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_DATABASE": "your_database",
        "DB_USERNAME": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_SCHEMA": "public"
      }
    }
  }
}

Option 2: Using npx (No Installation Required)

{
  "mcpServers": {
    "postgres": {
      "command": "npx",
      "args": [
        "-y",
        "pg-lens-mcp"
      ],
      "env": {
        "DB_HOST": "localhost",
        "DB_PORT": "5432",
        "DB_DATABASE": "your_database",
        "DB_USERNAME": "your_username",
        "DB_PASSWORD": "your_password",
        "DB_SCHEMA": "public"
      }
    }
  }
}

Option 3: Using Docker

{
  "mcpServers": {
    "postgres": {
      "command": "docker",
      "args": [
        "run",
        "--rm",
        "-i",
        "--network=host",
        "-e", "DB_HOST=localhost",
        "-e", "DB_PORT=5432",
        "-e", "DB_DATABASE=your_database",
        "-e", "DB_USERNAME=your_username",
        "-e", "DB_PASSWORD=your_password",
        "-e", "DB_SCHEMA=public",
        "pg-lens-mcp:latest"
      ]
    }
  }
}

Docker-specific notes:

• Use --network=host for connecting to localhost databases
• For remote databases, you can remove --network=host
• For databases in other Docker containers, use custom networks:

  "args": [
    "run", "--rm", "-i",
    "--network=your_docker_network",
    "-e", "DB_HOST=postgres_container_name",
    ...
  ]
  

💡 Tip: Use a read-only database user for extra security, even though all queries run in READ ONLY transactions.

🛠️ Available Tools

🗂️ Schema Discovery

"List all schemas in the database"

"Show me all tables in the public schema"

"Find all tables that have an 'email' column"

"Show me the complete structure of the users table"

📊 Data Querying

"Get the first 20 active users created after 2024-01-01, ordered by creation date"

"Execute this query: 
SELECT u.name, COUNT(o.id) as order_count 
FROM users u 
LEFT JOIN orders o ON u.id = o.user_id 
GROUP BY u.name 
ORDER BY order_count DESC 
LIMIT 10"

⚡ Performance Analysis

"Explain how PostgreSQL would execute: SELECT * FROM users WHERE email LIKE '%@example.com'"

"Analyze the actual performance of: SELECT * FROM large_table WHERE indexed_column = 'value'"

🔐 Security

Database-Enforced Read-Only Access

Unlike simple keyword filtering, this server uses PostgreSQL's transactional READ ONLY mode:

await client.query('BEGIN TRANSACTION READ ONLY');
const result = await client.query(userQuery);  // ← PostgreSQL blocks ANY writes
await client.query('COMMIT');

Why this matters:

✅ No false positives — Queries containing words like "UPDATE" or "INSERT" in strings/comments work fine
✅ No bypasses — Cannot be circumvented via stored procedures, functions, or extensions
✅ Database-level guarantee — PostgreSQL itself enforces the read-only constraint

SQL Injection Protection

Structured filters replace dangerous string concatenation:

❌ Unsafe approach:

query += ` WHERE ${userInput}`  // Direct concatenation = SQL injection risk

✅ Our approach:

filters: [{
  column: "status",
  operator: "=",
  value: "active"
}]
// Becomes: WHERE "status" = $1 
// PostgreSQL handles escaping automatically

All user inputs are properly parameterized, eliminating SQL injection vectors.

🧪 Testing the Server

Quick Test

# Set your database credentials
export DB_HOST=localhost
export DB_DATABASE=your_database
export DB_USERNAME=your_username
export DB_PASSWORD=your_password

# Start the server
node dist/index.js

Expected output:

✓ Database connection verified
✓ PostgreSQL MCP Server running on stdio

Testing with Claude Desktop

1. Add the server to your MCP configuration
2. Restart Claude Desktop
3. Try these example prompts:
   • "List all schemas in the database"
   • "Show me the structure of the users table"
   • "Find all tables with a 'created_at' column"
   • "Explain the query plan for SELECT * FROM large_table LIMIT 10"

📋 Troubleshooting

Connection Issues

"password authentication failed"

• Check DB_USERNAME and DB_PASSWORD are correct
• Verify the user has access to the specified database

"Connection timeout"

• Check DB_HOST is reachable
• Verify PostgreSQL is running on DB_PORT
• Check firewall rules if connecting remotely

"database does not exist"

• Verify DB_DATABASE name is correct
• List available databases: psql -l

Performance

If queries are slow:

1. Use explain_analyze tool to identify bottlenecks
2. Check if indexes exist on frequently queried columns
3. Consider adjusting DB_MAX_CONNECTIONS based on your workload

📄 License

MIT License

🤝 Contributing

Contributions are welcome! Please feel free to submit issues or pull requests.