README

PostgreSQL MCP Server for Claude Code - Secure and configurable PostgreSQL integration via Model Context Protocol.

🎉 Version 1.0.0-beta: Core functionality complete! Production testing in progress.

🚀 Features

🔒 Secure by Default - Read-only operations by default, write operations require explicit permission
🎯 PostgreSQL Native - Full support for JSONB, arrays, CTEs, window functions, and more
⚡ High Performance - Connection pooling, prepared statements, query timeouts
📊 Schema Introspection - Explore databases, tables, columns, indexes
🔧 Flexible Configuration - Environment variables, .env files, multi-database support
🛡️ Built-in Protection - SQL injection prevention, dangerous keyword blocking, result limits

📦 Requirements

PHP 8.1 or higher
PostgreSQL 12 or higher
Composer
PHP extensions: ext-pdo, ext-pdo_pgsql

🔧 Installation

1. Clone the Repository

git clone https://github.com/momodemo333/php-mcp-postgresql.git
cd php-mcp-postgresql

2. Install Dependencies

composer install

3. Configure Environment

cp .env.example .env
# Edit .env with your PostgreSQL credentials

⚙️ Configuration

Environment Variables

Variable	Description	Default
`PGSQL_HOST`	PostgreSQL server host	localhost
`PGSQL_PORT`	PostgreSQL server port	5432
`PGSQL_USER`	Database username	postgres
`PGSQL_PASS`	Database password	(empty)
`PGSQL_DB`	Database name (optional for multi-db)	(empty)
`ALLOW_INSERT_OPERATION`	Enable INSERT queries	false
`ALLOW_UPDATE_OPERATION`	Enable UPDATE queries	false
`ALLOW_DELETE_OPERATION`	Enable DELETE queries	false
`QUERY_TIMEOUT`	Query timeout in seconds	30
`MAX_RESULTS`	Maximum rows returned	1000
`CONNECTION_POOL_SIZE`	Max concurrent connections	5
`LOG_LEVEL`	Logging level (DEBUG/INFO/WARN/ERROR)	INFO

Claude Code Configuration

Add to your Claude Code settings (claude-code-settings.json):

{
  "mcpServers": {
    "postgresql": {
      "command": "php",
      "args": ["/absolute/path/to/php-mcp-postgresql/bin/server.php"],
      "type": "stdio",
      "env": {
        "PGSQL_HOST": "localhost",
        "PGSQL_PORT": "5432",
        "PGSQL_USER": "your_user",
        "PGSQL_PASS": "your_password",
        "PGSQL_DB": "your_database"
      }
    }
  }
}

See examples/ for more configuration examples.

🛠️ Available Tools

Database Management

pgsql_list_databases - List all available databases
pgsql_list_tables - List tables in a database
pgsql_describe_table - Get detailed table structure
pgsql_server_status - Get server status and statistics

Query Execution

pgsql_select - Execute SELECT queries safely
pgsql_insert - Insert data (requires permission)
pgsql_update - Update data (requires permission)
pgsql_delete - Delete data (requires permission)
pgsql_execute_query - Execute custom queries (with validation)

🧪 Testing

Quick Test

# Start test PostgreSQL database
./tests/start-test-db.sh

# Run all tests
./tests/run-tests.sh

# Stop test database
./tests/stop-test-db.sh

Docker Test Environment

The project includes a complete Docker test environment:

# Start PostgreSQL container with test data
docker-compose -f docker-compose.test.yml up -d

# Run tests
php tests/test_connection.php
php tests/test_mcp_server.php

# Stop containers
docker-compose -f docker-compose.test.yml down

🔒 Security

Default Security Features

Read-only by default - All write operations disabled unless explicitly enabled
Prepared statements - Prevents SQL injection attacks
Query validation - Blocks dangerous keywords and operations
Result limits - Prevents memory exhaustion from large result sets
Connection timeouts - Prevents hanging queries
Schema restrictions - Limit access to specific schemas

Best Practices

Never enable write operations in production unless absolutely necessary
Use read-only database users when possible
Set appropriate MAX_RESULTS and QUERY_TIMEOUT values
Review logs regularly for suspicious activity
Keep the server updated with security patches

🎆 PostgreSQL-Specific Features

JSONB Support

-- Query JSONB fields
SELECT * FROM users WHERE metadata->>'role' = 'admin';
SELECT * FROM products WHERE specifications @> '{"cpu": "Intel i7"}';

Array Support

-- Query array fields
SELECT * FROM products WHERE 'electronics' = ANY(tags);
SELECT * FROM products WHERE tags && ARRAY['computers', 'portable'];

Advanced Features

Common Table Expressions (CTEs)
Window functions
RETURNING clause on INSERT/UPDATE/DELETE
Materialized views
Full-text search
Custom types and domains

📝 Documentation

🤝 Contributing

Contributions are welcome! Please:

Fork the repository
Create a feature branch
Write tests for new functionality
Ensure all tests pass
Submit a pull request

📄 License

MIT License - see LICENSE file for details.

🙏 Acknowledgments

Built with php-mcp/server framework
Based on php-mcp-mysql architecture
Thanks to the MCP community for the protocol specification

💬 Support

Issues: GitHub Issues
Discussions: GitHub Discussions

Version: 1.0.0-beta
Status: Production Testing
Last Updated: January 2025

momodemo333 / php-mcp-postgresql

Maintainers

Details