---
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-08-04
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. **IP allow-listing is not
  supported**, and the connect form says so.
- 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.xyz/app/connections](https://dashies.xyz/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.

## Caps

| 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. |
| Data island, whole dashboard | 8,388,608 bytes | Publish is refused. |
| Compiled dashboard body | 5,242,880 bytes | Publish is refused. Usually binds first. |

Postgres supports every materialization: the additive cube, the grain lattice,
the hybrid mode, row-level datasets, and Parquet offload for a row-level dataset
that needs to grow large between publishes.

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.xyz/app/connections](https://dashies.xyz/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).