---
title: Connect PostgreSQL
description: Create a read-only role, add the data source, and test it, so your dashboards can refresh from a Postgres or Postgres-compatible warehouse.
updated: 2026-09-22
tier: pro
engines: [postgres]
---

Postgres or a Postgres-compatible warehouse. Dashies reads it over TLS with a
read-only login you create.

## Before you start

- The host must be reachable from the public internet.
- If your database only accepts connections from known addresses, choose that when you add
  the data source: tick **This database only accepts connections from known IP addresses**.
  Dashies shows the addresses for your workspace, and every connection to your database, from
  the test to every refresh, comes from them. Allow both, on your database's port.
  [IP addresses](/reference/ip-addresses) lists them by region. Checks take longer on this
  setting, because each one starts from those addresses, so leave it off if your database
  accepts connections from anywhere.
- The port must be **5432, 5433, or 6543**. Any other port is refused with
  `The port must be a Postgres port (5432, 5433, or 6543).` Port 1433 is
  deliberately excluded, because that set belongs to SQL Server and the two never
  mix.
- Use the direct endpoint host, not a connection-pooler host.

## 1. Create a read-only role

Run this once on your warehouse. It is the same script the connect form shows
under **Create a read-only role**, with `analytics` as the database name and
`public` as the schema. Substitute your own.

```sql
-- Run once on your Postgres warehouse. Replace the password with a strong secret,
-- then enter that same password below.
create role dashies_readonly with login password 'REPLACE_WITH_A_STRONG_PASSWORD';

grant connect on database "analytics" to dashies_readonly;

grant usage on schema "public" to dashies_readonly;
grant select on all tables in schema "public" to dashies_readonly;
alter default privileges in schema "public"
  grant select on tables to dashies_readonly;
```

Repeat the last block for every schema you plan to import.

:::warning{title="The last statement is load-bearing"}
`alter default privileges` is what keeps `select` working on tables created
later. Without it, a table added next month is invisible to Dashies and the
refresh that depended on it silently breaks.
:::

## 2. Add the data source

Open [dashies.ai/app/connections](https://dashies.ai/app/connections), click to
add a data source, and pick **PostgreSQL**.

| Field | Required | Example | Notes |
|---|---|---|---|
| Host | yes | `db.example.com` | Up to 255 characters. |
| Port | yes | `5432` | Must be 5432, 5433, or 6543. |
| Database | yes | `analytics` | Up to 128 characters. |
| User | yes | `dashies_readonly` | Up to 128 characters. |
| Password | yes | | Up to 1024 characters. |
| Schemas to import | yes | `public` | Comma or newline separated. |
| Display name | no | `Analytics warehouse` | Up to 120 characters. |

Schema limits: at most **20 schemas**, each at most **63 characters**, each
matching `[A-Za-z_][A-Za-z0-9_]*`.

TLS is not configurable. Dashies always connects with `sslmode=require` and a
10-second connect timeout. That is `require` rather than `verify-full`, so a
private certificate authority still works.

If the host is refused, the message is one of `That host is not allowed.` or
`That host did not resolve to any address.` Dashies resolves the hostname and
refuses private and internal address ranges, and refuses an address written in a
non-standard form such as decimal or octal.

## 3. Test it

Postgres is a two-step connect. Creating the data source already proves Dashies
can reach the warehouse and import your schemas, and leaves the status
**pending**. Click **Test** to run a real `select 1` through the read-only
executor and move it to **active**.

If the test fails, the class tells you where to look:

| Class | Usual cause |
|---|---|
| `connection_auth` | Wrong user or password, or the role has no `login`. |
| `ssl_failed` | The server does not offer TLS, or refuses `sslmode=require`. |
| `connection_unreachable` | The host does not resolve, refuses the connection, or resets it. |
| `connection_timeout` | The server accepted the connection but the query did not finish. |

## What Dashies can see

`IMPORT FOREIGN SCHEMA` pulls exactly the schemas you named. The visible surface
is the tables and columns of those schemas and nothing else.

The import flattens the schema name, so a table you know as
`analytics.usage_metrics` is referenced in cube SQL unqualified, as
`from usage_metrics`.

Postgres is the only engine that reports approximate row counts during
introspection, taken from the planner's own statistics. Treat them as
order-of-magnitude, not exact.

Postgres is also the only engine with a **Resync** button, which re-imports the
schemas after you add or change tables upstream. Every other engine hides it.

## Where the data lives

**A Postgres dashboard is served.** It keeps its data with Dashies rather than
inside the published file: each refresh runs your SQL, writes the result to
Dashies' own storage, and the page asks for what it needs when a reader opens it.
Every warehouse a refreshing dashboard can publish against is on that path.

The in-file materializations - `cube`, `lattice`, `hybrid` and `rows`, which ship
the numbers inside the page - are not available on a warehouse you connect, so a
publish that asks for one on this connection is refused. They are available on the
data Dashies provides; [Dataset modes](/concepts/dataset-modes) is where that and its
one plan-dependent exception are set out.

One consequence to design for: **the refresh is asynchronous.** A newly published
dashboard reads "Updating" until the first refresh lands, rather than carrying
numbers from the moment you published.

## Caps

The first two bound one result read through the read-only executor, which is the
path the authoring tools take. **They are not what bounds a served dashboard's own
data**: that is streamed by the refresh rather than assembled by the executor. The
third is engine-independent.

| Limit | Value | What happens past it |
|---|---|---|
| Rows per dataset query | 100,000 | Hard error. Never a silent truncation. |
| Bytes per dataset query | 8,000,000 | Hard error, raised by the read-only executor. |
| Compiled dashboard body | 5,242,880 bytes | Publish is refused. |

How these ceilings relate to each other, and which one binds first, is in
[sizes and ceilings](/concepts/dataset-modes#sizes-and-ceilings).

## Dialect notes

Cube SQL is PostgreSQL.

- **Bucket dates in your business time zone, in the SQL.** A refresh runs with no
  session time zone, so a bare `date_trunc('month', ts)` buckets in UTC and moves
  month boundaries.
- **Mind the `AT TIME ZONE` operand trap.** On a `timestamp with time zone`, the
  single form converts:

  ```sql
  date_trunc('month', ts AT TIME ZONE 'America/Los_Angeles')::date
  ```

  On a naive `timestamp` that stores UTC, you need the double form, labelling UTC
  first and then converting:

  ```sql
  date_trunc('month', ts AT TIME ZONE 'UTC' AT TIME ZONE 'America/Los_Angeles')::date
  ```

  The single form on a naive timestamp silently mis-buckets. Check the column
  type first, which introspection reports. Publish and validation flag a single
  `AT TIME ZONE` as ambiguous, as a non-blocking warning, because the server
  cannot read the operand's type.
- Postgres folds an unquoted output alias to lower case. A case-only difference
  between your alias and your declared measure key is handled for you, so
  `sum(amount) as revenue` against a measure named `revenue` is correct. Two
  output columns differing only by case are refused, naming both.
- Relative window: `now() - interval '12 months'`. Conditional count:
  `count(*) filter (where c)`. Exact median:
  `percentile_cont(0.5) within group (order by x)`.

## Rotating the password

Edit the data source and use the **Password** field, hinted `Leave blank to keep
the current password.` A pasted private key or service-account JSON is rejected,
because it is not a Postgres password.

## Check it worked

1. The data source reads **active** on
   [dashies.ai/app/connections](https://dashies.ai/app/connections).
2. Ask your AI tool to introspect it and run one query:

   > Introspect my Postgres data source, then validate this cube SQL against it:
   > `select 1 as ok`

   Introspection should list the tables of the schemas you imported, and the
   validation should return one row.

3. Then [author a dashboard against it](/guides/author-a-dashboard).