Postgres wire protocol

The Postgres wire protocol endpoint is an experimental, enterprise-only feature.

On Lightdash Cloud, availability is instance-dependent — contact Lightdash to enable it for your organization.
On self-hosted deployments, admins enable it via the PGWIRE_PORT environment variable and a valid enterprise license (see Enable the endpoint).

What it is

The Postgres wire protocol endpoint exposes your Lightdash semantic layer as a read-only Postgres database. Any standard Postgres client — psql, node-postgres, psycopg, JDBC drivers, SQL notebooks, and most BI tools — can connect, authenticate, and query your explores as if they were Postgres tables. Behind the scenes, Lightdash parses the incoming SQL, compiles it into a MetricQuery, and runs it through the same query path as the explorer. That means:

Project access controls, joins, metric definitions, and user attribute row-level rules apply exactly as they would in the Lightdash UI. The endpoint is not a warehouse passthrough — it’s a semantic-layer query interface.
Metrics, dimensions, and joins are honored automatically. You reference field IDs (for example, orders_total_order_amount), and Lightdash generates the correct warehouse SQL, including joins between explores.
Queries are read-only. Only SELECT is supported — the endpoint cannot modify warehouse data.

Typical use cases:

Query your semantic layer from a Python or Node script without going through the HTTP API.
Connect BI tools or notebooks that speak Postgres but don’t have a native Lightdash integration.
Use psql for quick ad-hoc analysis against governed, semantically consistent data.

Enable the endpoint

Enabling the endpoint is a two-step process: the instance must expose a Postgres wire port, and each organization then opts in from its project settings.

1. Enable the endpoint at the instance level

The Postgres wire protocol server lives in the enterprise codebase and only starts when both an enterprise license and the PGWIRE_PORT environment variable are configured.

Self-hosted
Lightdash Cloud

Set the following environment variables on the Lightdash backend:

PGWIRE_PORT=5433
PGWIRE_HOST=analytics.example.com   # optional: the hostname clients should connect to

On startup Lightdash logs Postgres wire protocol server listening on port 5433. Without a valid enterprise license the server logs a warning and does not listen, even if PGWIRE_PORT is set.

PGWIRE_PORT — the TCP port the server binds to. Required.
PGWIRE_HOST — optional. The hostname advertised to users in the project settings UI. If unset, the settings page shows an empty host and admins will need to fill it in themselves.

Expose the port to the clients that need it (for example, through your ingress or load balancer). TLS is not yet supported — clients must connect with sslmode=disable, so we recommend terminating TLS in front of the endpoint or restricting it to a private network.

2. Enable the endpoint for your organization

Once the instance exposes a pgwire port, each organization has to explicitly opt in. The setting is off by default.

Go to Project settings → Semantic layer (the tab only appears when the instance has pgwire enabled).
As an organization admin, toggle Semantic layer Postgres connection on.

Once enabled, the same page shows the connection details, an inline Generate token button for creating a scoped service account token, and ready-to-copy client snippets (connection URL, psql, and JDBC). Non-admins see the tab but not the connection details.

Connection details (host and port) are only returned to organization admins. The enabled flag is visible to all org members so the settings tab can render correctly for everyone.

Connect

Use any Postgres client. The Project settings → Semantic layer page shows the exact host, port, database (project UUID), and ready-to-copy psql, connection URL, and JDBC snippets for your instance — copy the snippet that matches your client and paste in a token as the password. The connection parameters are:

Parameter	Value
Host	Your Lightdash instance host (shown on the settings page)
Port	The value of `PGWIRE_PORT` (self-hosted) or the port provided by Lightdash (Cloud)
User (`-U`)	Any string — the value is ignored
Database (`-d`)	The project UUID or the slugified project name (for example, `ecom-store`)
Password	A service account token (`ldsvc_…`) or personal access token (`ldpat_…`)
SSL mode	`disable` (TLS is not yet supported)

Example: `psql`

PGPASSWORD=ldsvc_... psql \
  -h analytics.example.com \
  -p 5433 \
  -U lightdash \
  -d ecom-store \
  "sslmode=disable"

Example: connection URL

postgresql://lightdash:ldsvc_...@analytics.example.com:5433/ecom-store?sslmode=disable

Example: JDBC (DBeaver / DataGrip)

jdbc:postgresql://analytics.example.com:5433/ecom-store?sslmode=disable

Authentication

The password must be one of:

Service account token (ldsvc_ prefix) — recommended for automation and production integrations. Create one from Settings → Service accounts. See Service accounts. Service accounts scoped only to SCIM are rejected.
Personal access token (ldpat_ prefix) — useful for interactive use. Create one from Settings → Personal access tokens. See Personal access tokens.

The -U value is not used for authentication — the user identity is derived entirely from the token, so any placeholder works.

Choosing a database

The -d value can be either the project UUID (always unambiguous) or a slugified project name derived from the display name in Lightdash (for example, the project “Ecom Store” becomes ecom-store). If two projects share the same slugified name, the endpoint returns error code 3D000 at connect time and lists the candidate UUIDs — use one of those UUIDs to connect.

What maps to what

Lightdash concept	Postgres concept
Explore	Table
Field ID (for example, `orders_total_order_amount`)	Column
Joined-table field (for example, `customers_region` when joined into `orders`)	Column on the base explore
Dimensions and metrics	Columns (distinguished by `field_type` in `information_schema.columns`)

Because explores already flatten joins in the semantic layer, joined-table fields appear as columns on the base explore — you do not (and cannot) write JOIN clauses yourself.

Discovering tables and columns

The endpoint serves a virtual information_schema so clients and users can list explores and fields:

-- List all explores in the project
SELECT table_name
FROM information_schema.tables;

-- List the columns of an explore, showing whether each is a dimension or metric
SELECT column_name, data_type, field_type
FROM information_schema.columns
WHERE table_name = 'orders';

The field_type column is a Lightdash extension that returns dimension or metric. Standard columns like column_name, data_type, and is_nullable behave the same as in real Postgres.

pg_catalog is not implemented. GUI schema browsers that rely on pg_catalog (for example, DBeaver’s schema tree) will show an empty catalog. Query information_schema instead, or use a client that respects it (for example, psql \dt and \d work).

Example queries

The examples below assume an explore called orders in an ecom-store project.

Basic query

SELECT
  orders_status,
  orders_total_order_amount
FROM orders
WHERE orders_order_date >= '2026-01-01'
  AND orders_status IN ('completed', 'shipped')
ORDER BY orders_total_order_amount DESC
LIMIT 100;

Because orders_total_order_amount is a metric and orders_status is a dimension, Lightdash automatically:

Adds orders_status to the group-by.
Runs the query through the metric definition (so aggregation logic lives in YAML, not the SQL you write).
Applies the same permissions and user attributes as the Lightdash explorer.

Filtering on metrics (routed to `HAVING`)

Metric conditions in WHERE are automatically routed to metric filters, so this works:

SELECT
  customers_region,
  orders_total_order_amount
FROM orders
WHERE orders_total_order_amount > 10000
ORDER BY orders_total_order_amount DESC;

You can also use HAVING explicitly — it maps to the same metric filters.

Table calculations

Expressions in the SELECT list become table calculations:

SELECT
  orders_order_month,
  orders_total_order_amount,
  orders_total_order_amount
    - LAG(orders_total_order_amount) OVER (ORDER BY orders_order_month) AS mom_change,
  CASE
    WHEN orders_total_order_amount > 10000 THEN 'high'
    ELSE 'normal'
  END AS revenue_tier
FROM orders
ORDER BY orders_order_month;

Table calculations support arithmetic, functions, CASE expressions, window functions, and references to other calculations in the same query.

Supported SQL

The parser accepts a practical subset of Postgres SQL, focused on what maps cleanly onto a semantic-layer query:

SELECT — including *, column aliases, and table-qualified names (orders.orders_status).
WHERE — =, !=, <, <=, >, >=, IN / NOT IN, LIKE / ILIKE, BETWEEN, IS NULL / IS NOT NULL, boolean columns, and nested AND / OR. Metric conditions are automatically routed to metric filters.
HAVING — routed to metric filters.
GROUP BY — optional (Lightdash groups by the selected dimensions automatically), but accepted.
ORDER BY — on field IDs, ordinal positions, column aliases, and table calculations. NULLS FIRST / NULLS LAST are honored.
LIMIT.
Table calculations — arithmetic, functions, CASE, window functions, and references to other calculations in the same query.
Catalog discovery — virtual information_schema.tables and information_schema.columns (with an extra field_type column).
Session shims — BEGIN, COMMIT, SET, SHOW, SELECT version(), and FROM-less selects (for example, SELECT 1) are accepted so that clients and drivers connect cleanly.

Not supported

The following are rejected by design, so that queries stay within the semantic-layer contract:

Extended query protocol / bind parameters — clients that force prepared statements will receive error code 0A000. Configure your driver to use the simple query protocol.
pg_catalog — GUI schema browsers (for example, DBeaver’s tree) will not populate. Use information_schema instead.
Explicit JOINs — joins are defined in your explores; you cannot join tables in ad-hoc SQL.
Subqueries and CTEs.
DML (INSERT, UPDATE, DELETE, MERGE) and DDL — the endpoint is read-only.
Ad-hoc custom metrics — only pre-defined metrics from your semantic layer can be selected. Aggregate expressions like count(*) or sum(...) in the SELECT list are rejected.
Period-over-period and other Lightdash features that require additional query-time metadata beyond what SQL can express.
SELECT DISTINCT and OFFSET.

Errors

Errors are returned as standard Postgres ErrorResponse messages, so your client’s error handling behaves normally.

SQLSTATE	Meaning
`28P01`	Authentication failed — the password is not a valid `ldsvc_` or `ldpat_` token, or the token belongs to a SCIM-only service account.
`3D000`	The database name is unknown or ambiguous. Use a project UUID, or one of the slugs listed in the error message.
`0A000`	The client tried to use an unsupported feature — for example, the extended query protocol, `pg_catalog`, or an operator that isn’t allowed on `information_schema`.

Security and permissions

All queries flow through the same services as the Lightdash UI, so:

Every query is authorized against the caller’s project and space permissions.
User attributes and row-level rules are applied to metric queries exactly as they are in the explorer.
The endpoint is read-only — it cannot mutate warehouse data or Lightdash content.

Because service accounts and personal access tokens carry the same permissions as the user or scopes they belong to, we recommend using a service account with only the scopes needed for the queries the integration will run, rather than a personal token, for any long-lived integration.

Introduction

Explore and analyze

Build with AI

Build your semantic layer

Workspace and user management

Integrations

Lightdash SDK

Embedding

Self-hosting and deployment

Contact

Postgres wire protocol

What it is

Enable the endpoint

1. Enable the endpoint at the instance level

2. Enable the endpoint for your organization

Connect

Example: `psql`

Example: connection URL

Example: JDBC (DBeaver / DataGrip)

Authentication

Choosing a database

What maps to what

Discovering tables and columns

Example queries

Basic query

Filtering on metrics (routed to `HAVING`)

Table calculations

Supported SQL

Not supported

Errors

Security and permissions

​What it is

​Enable the endpoint

​1. Enable the endpoint at the instance level

​2. Enable the endpoint for your organization

​Connect

​Example: psql

​Example: connection URL

​Example: JDBC (DBeaver / DataGrip)

​Authentication

​Choosing a database

​What maps to what

​Discovering tables and columns

​Example queries

​Basic query

​Filtering on metrics (routed to HAVING)

​Table calculations

​Supported SQL

​Not supported

​Errors

​Security and permissions

What it is

Enable the endpoint

1. Enable the endpoint at the instance level

2. Enable the endpoint for your organization

Connect

Example: `psql`

Example: connection URL

Example: JDBC (DBeaver / DataGrip)

Authentication

Choosing a database

What maps to what

Discovering tables and columns

Example queries

Basic query

Filtering on metrics (routed to `HAVING`)

Table calculations

Supported SQL

Not supported

Errors

Security and permissions