pgEdge Postgres MCP Server and Natural Language Agent

The pgEdge Natural Language Agent provides access to the pgEdge Postgres Model Context Protocol (MCP) Server, enabling users to interact with a PostgreSQL database (version 14 or higher) using natural language queries.

Core Workflow

1. Schema extraction: The server loads table and column metadata from PostgreSQL.
2. Query generation: An LLM converts natural language into SQL.
3. Safe execution: Queries are executed within read-only transactions to prevent accidental data modification.
4. Result formatting: The LLM processes the results and returns a human-readable answer.

Key Features

• Natural Language to SQL: Query databases using plain English.
• Read-Only Protection: All queries are enforced as read-only transactions.
• Multiple LLM Support: Compatible with Anthropic Claude, OpenAI, and Ollama (for local/free usage).
• Interfaces: Includes a React-based Web Interface and a production-ready CLI Client with Anthropic prompt caching.
• Security: Supports TLS, token/user authentication, and read-only enforcement.

To prevent DNS rebinding attacks—where a malicious website attempts to make requests to your local server via the user's browser—the server validates the Origin header of incoming requests.

How it works:

• The server checks the Origin header (set by the browser) against a list of allowed origins. If the origin is not recognized, the server returns a 403 Forbidden error.
• The server validates the Origin header only, not the Host header. This is critical because, during a DNS rebinding attack, the Host and Origin headers will match the attacker's domain, making a Host-based check ineffective.
• No Origin header: Requests without an Origin header are allowed. This ensures compatibility with non-browser MCP clients (like CLI tools) and prevents breaking standard command-line usage.

Configuration Requirements:

• Default behavior: The server accepts loopback origins on any port by default, which supports local use and the bundled web client.
• Custom Hostnames: If you deploy the web client under a specific hostname, you must add that hostname to the http.allowed_origins configuration setting, otherwise the browser will block requests.
• Network Binding: To further limit exposure, if the server is only intended for local use, restrict its listen address by setting http.address to 127.0.0.1:8080 instead of binding to all interfaces.

The MCP server resolves configuration values using a specific hierarchy. When multiple sources define the same setting, the following priority order applies (from highest to lowest):

1. Command Line Flags: Explicitly passed flags (e.g., -addr ":3000") always take precedence.
2. Environment Variables: Variables like PGEDGE_HTTP_ADDRESS override settings in the configuration file.
3. Configuration File: Settings defined in the YAML file (specified via -config).
4. Hard-coded Defaults: Used when no other source provides a value.

Priority Examples

Command Line Override If the config file specifies :8080 but you run with -addr ":3000", the server uses :3000.

Environment Override If the config file specifies http.address: ":8080" but PGEDGE_HTTP_ADDRESS is set to ":9090", the server uses :9090.

# Example: Command line flag overriding config file and environment
./bin/pgedge-postgres-mcp \
  -http \
  -addr ":3000"

The server implements a best-effort redaction mechanism to prevent provider credentials (like OpenAI API keys) from leaking in error messages. Redaction occurs in three specific areas:

1. /api/llm/ endpoint responses: The library's proxy filters the response body.
2. Trace entries: Logs written to the file specified by the -trace-file flag.
3. CLI error reporting: Errors reported to the operator or redirected to logs.

Mechanism: The server replaces both configured credentials and any text matching known key formats with [REDACTED]. The surrounding error context (provider name, status code, and failure reason) is preserved.

Security Limitations:

• Streaming vulnerability: Because filtering happens on the response stream, a credential split across two separate writes might evade redaction. (Note: JSON error bodies and Server-Sent Events are written as single units and are not subject to this specific risk).
• Pattern matching limits: Redaction relies on recognizing known formats. If a provider uses a new format or quotes a key in an unexpected way, it may not be caught.
• Safety net only: Redaction is a secondary safety measure. The primary security practice is to ensure provider keys are never included in any published output.

The server employs multiple layers of defense to enforce read-only mode:

1. Extended Query Protocol: Every statement runs through the PostgreSQL extended query protocol, which enforces exactly one statement per message, preventing statement smuggling.
2. Transaction-level Enforcement: Read-only mode is requested as part of the BEGIN command, ensuring the transaction is never briefly writable.
3. Session-level Settings: The default_transaction_read_only setting is applied to every connection in the pool.
4. Statement Guard: The server intercepts and rejects constructs used to escape read-only mode, such as SET SESSION CHARACTERISTICS, RESET ALL, DISCARD, SET ROLE, ALTER ROLE, ALTER USER, ALTER DATABASE, DO blocks, COPY ... TO PROGRAM, and dblink.

Note on Security Guarantees: While these layers protect against accidents, the only absolute guarantee against an adversary is to use a database role with INSERT, UPDATE, and DELETE privileges revoked at the database level.