> ## Documentation Index
> Fetch the complete documentation index at: https://docs.routemcp.io/llms.txt
> Use this file to discover all available pages before exploring further.

# PostgreSQL

> Connect a PostgreSQL database and ask questions in plain English. RouteMCP introspects the schema and runs read-only analytics queries on your behalf — 2 tools, connection-string auth, with automatic redaction of sensitive and encrypted data.

<img src="https://icon.horse/icon/postgresql.org" alt="PostgreSQL" style={{ width: "48px", height: "48px", marginBottom: "16px" }} />

## Overview

| Property   | Value                                               |
| ---------- | --------------------------------------------------- |
| Slug       | `postgres`                                          |
| Category   | Database                                            |
| Auth       | Connection string (database URL)                    |
| Connection | User-supplied `postgres://…` URL, encrypted at rest |
| Tool count | 2                                                   |
| Access     | **Read-only** (SELECT / WITH only)                  |

## Tool catalogue

PostgreSQL exposes two native tools. In router mode the model discovers them with `SEARCH_TOOLS` and never needs you to bind a fixed resource ahead of time.

| Slug                       | Purpose                                                                                                                                                                                                 |
| -------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| `POSTGRES_DESCRIBE_SCHEMA` | Returns the database's tables, columns (with types), primary keys, and relationships. The model calls this first so it uses exact identifiers. Optionally focuses on the tables relevant to a question. |
| `POSTGRES_RUN_QUERY`       | Executes a single read-only SQL statement (SELECT / WITH) and returns rows. DDL/DML is rejected, a row limit is applied automatically, and the query runs inside a `READ ONLY` transaction.             |

## Connect setup

Unlike OAuth providers, PostgreSQL connects with a **database connection URL** — there is no redirect or consent screen. When the agent needs the database and it isn't connected yet, it surfaces a **Connect** action; the user pastes their connection URL into a short form.

<Steps>
  <Step title="Open the connect form">
    In the playground (or wherever a connect link is surfaced), click **Connect to PostgreSQL DB**. A form asks for a single field, the connection URL:

    ```
    postgresql://user:password@host:5432/dbname
    ```

    Append `?sslmode=disable` only for databases that do not use TLS; hosted databases generally require TLS and connect with it by default.
  </Step>

  <Step title="Validation and schema introspection">
    On submit, RouteMCP validates the host, opens a test connection (`SELECT 1`), introspects the schema (tables, columns, primary keys, foreign keys), caches a schema snapshot, and marks the connection **active**. If the database is unreachable or the URL is wrong, the form shows an error and nothing is stored.
  </Step>

  <Step title="Stored encrypted">
    The connection URL is encrypted at rest (AES-256-GCM). Only read-only queries are ever issued against it. Use **Re-sync** if your schema changes, or **Disconnect** to remove the connection.
  </Step>
</Steps>

<Note>
  For least privilege, supply a connection URL for a **read-only database role**. RouteMCP also enforces read-only at query time, but a read-only role is the strongest guarantee.
</Note>

## Usage

### Through the chat endpoint (recommended)

```bash theme={null}
curl -N -X POST "https://api.routemcp.io/api/v1/mcp/<workspace_id>/chat" \
  -H "x-api-key: sk_live_your_key" \
  -H "x-session-id: user-42" \
  -H "content-type: application/json" \
  -d '{"message": "How many users signed up this week?"}'
```

The agent calls `POSTGRES_DESCRIBE_SCHEMA` to learn the shape, writes a query such as `SELECT count(*) FROM users WHERE created_at >= date_trunc('week', now())`, runs it via `POSTGRES_RUN_QUERY`, and streams back a plain-language answer. The generated SQL is visible in the tool-call trace but never shown in the chat reply.

### Direct MCP JSON-RPC (Claude Desktop / Cursor / custom MCP clients)

```json theme={null}
{
  "mcpServers": {
    "routemcp": {
      "url": "https://api.routemcp.io/api/v1/mcp/<workspace_id>",
      "headers": {
        "x-api-key": "<YOUR_API_KEY>",
        "x-session-id": "<SESSION_ID>"
      }
    }
  }
}
```

## Data handling & security

PostgreSQL is the first read-only data connector and applies several guardrails automatically — you don't configure anything.

* **Read-only only.** `POSTGRES_RUN_QUERY` accepts a single `SELECT`/`WITH` statement. `INSERT`, `UPDATE`, `DELETE`, DDL, multi-statement input, and dangerous functions are rejected, and the query runs in a `READ ONLY` transaction. The assistant will not offer or attempt writes (no "force password reset", "rotate credentials", etc.) — it can only read and analyze.
* **Sensitive columns are redacted.** Columns whose names indicate secrets — `password`, `password_hash`, `secret`, `api_key`, `token`, `private_key`, `ssn`, `cvv`, `card_number`, and similar — are returned as `[restricted]` and are marked restricted in the schema, regardless of how the query selects them.
* **Encrypted data is detected.** When a column's values look encrypted or hashed (bcrypt/argon prefixes, high-entropy base64/hex, `iv:ciphertext:tag`), they are returned as `[encrypted]` and the assistant tells you the data is encrypted at rest rather than presenting unreadable values.
* **Host protection.** Connection hosts are validated to block internal, loopback, and cloud-metadata addresses.

<Note>
  Row results are capped (default 1000 rows) and queries run under a statement timeout. Ask for a `count(*)` or an aggregate when you want totals rather than every row.
</Note>