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

# Postgres

> Bind a block to a read-only SQL query against your own Postgres database.

The Postgres connector lets a block read from your own database with a SQL query you write. The platform wraps every customer query in a read-only safety envelope, defeats SSRF and DNS rebinding, and refreshes on a cron cadence.

<Note>
  Postgres data sources are a **Builder beta** feature. Free workspaces can read this page, but connector creation, tests, refreshes, and schema introspection return `PRO_REQUIRED`.
</Note>

## Add a Postgres data source

<Steps>
  <Step title="Go to the wizard">
    `/settings/data-sources/new/postgres`.
  </Step>

  <Step title="Paste a postgres:// connection string">
    A standard Postgres URI. The connection string is envelope-encrypted with your workspace's DEK before it's written to Convex.

    The SSRF check (see below) runs synchronously on the URL — bad URLs (private IPs, `localhost`, plaintext schemes) are rejected immediately.
  </Step>

  <Step title="Click Test Connection">
    Optional but recommended. The wizard runs a one-off connection probe (admin-gated) against your database with the same safety envelope it uses for every refresh. You get back `{ ok: true }` or a useful error.
  </Step>

  <Step title="Pick a cadence and save">
    `5`, `15`, `60`, `360`, or `1440` minutes. Postgres is cron-mode only in v1 — no LISTEN/NOTIFY (long-running connections don't fit Convex actions cleanly). Push-mode for Postgres is on the roadmap.
  </Step>
</Steps>

## The SQL safety envelope

Every query you write — every refresh, every preview — is wrapped server-side in this transaction:

```sql theme={null}
BEGIN TRANSACTION READ ONLY;
SET LOCAL statement_timeout = 5000;
SET LOCAL idle_in_transaction_session_timeout = 6000;
SELECT * FROM (<your SQL>) AS q LIMIT 10000;
COMMIT;
```

What this gives you:

* **Read-only.** `BEGIN TRANSACTION READ ONLY` rejects any write at the Postgres level. INSERT, UPDATE, DELETE, DDL — all fail with a Postgres error.
* **5-second statement timeout.** A runaway query kills itself instead of holding the connection.
* **Hard 10 000 row cap.** No accidental million-row pull.
* **Subquery wrapper.** Your SQL is parenthesized as a derived table. You write `SELECT ... FROM ... WHERE ...` and the platform plugs it in.

What the validator rejects before the wrap:

* Trailing `;` on your SQL — stripped automatically.
* Inner `;` — rejected as potential statement chaining.
* Empty SQL — rejected.

CTEs (`WITH ... SELECT ...`) work because they're valid inside the subquery position. Window functions, joins, subqueries — all fine. Multi-statement scripts won't fit.

## SSRF protection

Two-layer defense before the platform connects to your database:

<Tabs>
  <Tab title="Synchronous (at add time)">
    The wizard validates the connection string the moment you paste it:

    * Scheme must be `postgres:` or `postgresql:` — no `http:`, no `file:`, no `internal:`.
    * Hostname blocklist: `localhost`, anything ending in `.local`.
    * Literal IP check: IPv4 private ranges (`10/8`, `172.16/12`, `192.168/16`, `127/8`, `169.254/16`, `0.0.0.0`), IPv6 private ranges (`::1`, `fc00::/7`, `fe80::/10`).

    Pasting `postgres://postgres@localhost/db` is rejected up front.
  </Tab>

  <Tab title="Async (every connect)">
    Immediately before every refresh — not just at add time — the platform runs `dns.promises.lookup({ all: true })` on the hostname and rejects if **any** A/AAAA record falls in the same private ranges.

    This defeats DNS rebinding. A malicious DNS server could resolve a public-looking hostname to a private IP between the wizard check and the refresh; the per-refresh re-check catches it.
  </Tab>
</Tabs>

## Bind a block

```json theme={null}
{
  "binding": {
    "dataSourceId": "<postgres-data-source-id>",
    "queryConfig": {
      "type": "postgres.sql",
      "params": {
        "sql": "SELECT count(*) AS signups FROM users WHERE created_at > now() - interval '24 hours'"
      }
    },
    "projection": {
      "fields": {
        "value": { "source": "rows.0.signups", "format": "number" },
        "label": { "literal": "Signups (24h)" }
      }
    }
  },
  "widget": {
    "template": "metric",
    "data": { "value": "0", "label": "Signups (24h)" }
  }
}
```

The query result is shaped `{ rows: Row[], rowCount: number }`. The projection paths into `rows.0.signups` to pluck the count.

## Agent-driven schema introspection

Your coding agent can discover your database structure through two MCP tools without seeing the connection string. Useful when telling an agent "build me a block off my Postgres" and letting it figure out what to query:

<AccordionGroup>
  <Accordion title="dataSources.introspectSchema">
    Lists the top 50 tables sorted by activity (from `pg_stat_user_tables`). Returns table name, schema, and approximate row count.

    Postgres-only — other source types return `isError: true`.
  </Accordion>

  <Accordion title="dataSources.introspectTable">
    Lists columns + types for a given table (from `information_schema.columns`).

    Postgres-only.
  </Accordion>
</AccordionGroup>

Both run in the same safety envelope (read-only transaction, statement timeout, DNS re-check). Both decrypt the credential and write an `auditLog` row before connecting.

## Credential storage

A single-secret credential (just the connection string). Encrypted via the workspace DEK. Every decryption is audit-logged with the calling actor.

## What's next

<CardGroup cols={2}>
  <Card title="Webhook" icon="webhook" href="/connecting-data/webhook">
    Push-mode delivery from your own systems.
  </Card>

  <Card title="Outbound MCP" icon="plug" href="/connecting-data/outbound-mcp">
    Point at a third-party MCP server (Linear, GitHub, Notion).
  </Card>
</CardGroup>