MCP PostgreSQL Server

This is a Model Context Protocol server for interacting with PostgreSQL databases. It provides a read-only interface to query PostgreSQL databases and inspect their schema.

Installation

Configuration

The server can be configured in multiple ways, listed in order of priority:

1. Environment Variables

   • POSTGRES_URL: Full database URL (e.g., postgres://user:pass@host:5432/dbname)

   • Individual connection parameters:

     • POSTGRES_HOST: Database host

     • POSTGRES_PORT: Database port (default: 5432)

     • POSTGRES_DB: Database name

     • POSTGRES_USER: Database user

     • POSTGRES_PASSWORD: Database password

     • POSTGRES_SSL: Enable SSL mode (set to 'true' to enable)

     • POSTGRES_SCHEMA: Database schema (default: 'public')

   • Additional configuration:

     • DOTENV_PATH: Custom path to .env file

     • DEBUG_MCP: Enable debug logging (set to 'true' to enable)

2. Command Line

   npx @hthuong09/postgres-mcp "postgres://user:pass@host:5432/dbname"

Resources

• Table schemas: Each table in the database is exposed as a resource

• Resource URI format: postgres://user@host/dbname/table_name/schema

• Response format: JSON array of column definitions (name and data type)

Usage Examples

1. Using environment variables:

   export POSTGRES_HOST=localhost export POSTGRES_DB=mydb export POSTGRES_USER=myuser export POSTGRES_PASSWORD=mypassword npx @hthuong09/postgres-mcp

2. Using a connection URL:

   npx @hthuong09/postgres-mcp "postgres://myuser:mypassword@localhost:5432/mydb"

3. Using environment variables with SSL:

   export POSTGRES_HOST=db.example.com export POSTGRES_DB=mydb export POSTGRES_USER=myuser export POSTGRES_PASSWORD=mypassword export POSTGRES_SSL=true npx @hthuong09/postgres-mcp

4. Using a custom .env file location:

   DOTENV_PATH=/path/to/.env npx @hthuong09/postgres-mcp

Security Considerations

• Database credentials should be kept secure

• Use environment variables or .env files instead of command line arguments in production to avoid exposing credentials in process lists

• Consider using SSL in production environments

• The server only allows read-only transactions for safety

• Passwords are automatically stripped from resource URIs

Development

To build the server locally:

To run in watch mode during development:

Debugging

Set DEBUG_MCP=true to enable debug logging. Logs will be written to:

• Unix/macOS: /tmp/postgres-mcp-debug.json

• Windows: %TEMP%/postgres-mcp-debug.json

License

MIT