MCP Postgres Data Interface Utility

This utility serves as a Model Context Protocol (MCP) endpoint specifically designed for secure interaction with PostgreSQL databases. Its primary function is restricted to read-only operations, enabling users to execute data queries and examine database schema definitions.

Deployment Guide

Installation requires Node Package Manager:

npm install -g @hthuong09/postgres-mcp

Configuration Modalities

The operational parameters for the server are prioritized as follows:

1. Environment Variables
2. POSTGRES_URL: The complete Uniform Resource Locator for the database connection (e.g., postgres://usr:pwd@server:5432/database_name)
3. Specific connection components:
   • POSTGRES_HOST: Target host machine address
   • POSTGRES_PORT: Connection port number (defaults to 5432)
   • POSTGRES_DB: The target database identifier
   • POSTGRES_USER: Authentication username
   • POSTGRES_PASSWORD: Secret credential for authentication
   • POSTGRES_SSL: Boolean flag ('true' to activate Transport Layer Security)
   • POSTGRES_SCHEMA: The specific database schema to operate within (defaults to 'public')

4. Auxiliary settings:

   • DOTENV_PATH: Path specification for an alternative configuration file
   • DEBUG_MCP: Flag to activate detailed diagnostic output (set to 'true')

5. Invocation Argument Direct execution utilizing a connection string: bash npx @hthuong09/postgres-mcp "postgres://user:pass@host:5432/dbname"

Accessible Endpoints

• Table Schemas: Every defined table within the database is mapped to an accessible resource.
• Resource URI Structure: The required path format is postgres://user@host/dbname/table_name/schema
• Output Format: Responses consist of a JSON array detailing column attributes (name and associated data type).

Operational Demonstrations

1. Utilizing environmental configuration: bash export POSTGRES_HOST=localhost export POSTGRES_DB=mydb export POSTGRES_USER=myuser export POSTGRES_PASSWORD=mypassword npx @hthuong09/postgres-mcp

2. Employing a direct connection link: bash npx @hthuong09/postgres-mcp "postgres://myuser:mypassword@localhost:5432/mydb"

3. Enabling secure socket layer (SSL) via variables: bash 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. Specifying a non-default configuration file location: bash DOTENV_PATH=/location/to/settings npx @hthuong09/postgres-mcp

Safety Directives

• Protect database authentication secrets diligently.
• For production deployment, favor environment variables or dedicated configuration files over command-line inputs to obscure sensitive data from process listings.
• SSL encryption is strongly advised for live operational contexts.
• All database interactions are strictly enforced as read-only transactions to maintain data integrity.
• Passwords embedded in resource URIs are automatically redacted.

Local Development Setup

To compile the utility locally:

npm install
npm run build

To initiate development iteration with automatic reloading:

npm run watch

Troubleshooting

Activate verbose logging by setting DEBUG_MCP=true. Logs will be redirected to: - POSIX Systems (Unix/macOS): /tmp/postgres-mcp-debug.json - Windows Systems: %TEMP%/postgres-mcp-debug.json

Licensing

This software is distributed under the MIT License.