Skip to content
BoilStream

Connect with PostgreSQL clients

tutorial
TL;DR

Use a PostgreSQL wire-protocol client to run DuckDB SQL against an authorized catalog. Obtain temporary credentials from the dashboard and keep TLS verification enabled. PGWire supports streaming inserts and recent-data queries, but PostgreSQL server features are not generally available. COPY FROM STDIN ingestion is unavailable.

Message sequenceA PostgreSQL client opens a catalog-scoped SQL session
A PostgreSQL client opens a catalog-scoped SQL sessionThe client establishes TLS and authenticates to the selected catalog with temporary credentials. SQL requests and result rows travel through the same PGWire connection.psql / SQL toolBoilStreamTLS connection and credentialsAuthentication resultSQL for selected catalogRows, status or SQL errorTime moves downward · dashed arrows are responsesA PostgreSQL client opens a catalog-scoped SQL sessionThe client establishes TLS and authenticates to the selected catalog with temporary credentials. SQL requests and result rows travel through the same PGWire connection.psql / SQLtoolBoilStreamTLS connection andcredentialsAuthentication resultSQL for selected catalogRows, status or SQL errorTime moves downward · dashed arrows are responses
Use the catalog display name as the database name. Check expiry and permissions separately when a connection fails.

Connect

Section titled “Connect”

Get temporary PGWire credentials from the Web Auth GUI, then use the TLS endpoint supplied by your deployment:

Shell
psql "host=HOST port=PORT user=USER dbname=STREAMING_CATALOG sslmode=verify-full sslrootcert=/path/to/ca.pem"

Replace the connection values and CA path with your deployment settings; enter the temporary password when prompted. For the ingestion examples below, replace STREAMING_CATALOG with an authorized catalog whose name ends in __stream (for example, boil__stream if it has been provisioned). Credentials expire, so obtain fresh credentials when needed.

Select a catalog

Section titled “Select a catalog”

The database name is the catalog display name visible to the authenticated user. The default warehouse catalog is duckie when automatic provisioning is enabled. Each tenant has its own catalog with that display name. Use a __stream catalog for streaming ingestion.

SQL
SHOW ALL TABLES;


-- After creating and populating events as shown below:
SELECT *
FROM main.events
WHERE event_time >= current_timestamp - INTERVAL 1 HOUR;

Catalog sharing reuses the owner’s physical catalog after BoilStream authorizes the requester. Display names do not determine storage placement or authorization.

Create and ingest

Section titled “Create and ingest”

DDL is authorized against the selected catalog:

SQL
CREATE TABLE main.events (
    event_time TIMESTAMP,
    event_type VARCHAR,
    value DOUBLE
);

Low-latency streaming ingestion is available only for a streaming DuckLake, whose display name ends in __stream:

SQL
INSERT INTO main.events VALUES (current_timestamp, 'click', 1.0);

For high-volume producers, use Kafka, HTTP/2 Arrow, or FlightRPC.

Expiry and connection failures

Section titled “Expiry and connection failures”

Get new credentials when the current ones expire or access changes. After a server failure, reconnect using a healthy endpoint supplied by your deployment. A lost connection may leave the outcome of a write unknown; reconcile before retrying it.

See Web Auth GUI, PGWire API and Interface compatibility for the complete workflow and limits.

Protocol reference

Section titled “Protocol reference”

See operations and limits before choosing a driver.