Skip to main content
🧪 Preview (Beta)
Suitable for production read workloads.
Most PostgreSQL driver features are supported; some PostgreSQL features may not yet be tested and could behave differently or not work in some tools.
lightdash

​
Lightdash integration with Firebolt

Lightdash is an open-source BI platform that enables teams to explore, visualize, and share insights on top of dbt projects. Lightdash relies entirely on dbt metadata and executes queries using standard database connectors. This guide explains how to connect Lightdash to Firebolt using the PostgreSQL protocol with mutual TLS (mTLS) authentication.

​
Overview

Lightdash connects to Firebolt through the PostgreSQL-compatible endpoint exposed by Firebolt. Key characteristics of this integration:
  • Lightdash uses the PostgreSQL protocol
  • Authentication is done using Firebolt service accounts
  • Connections use mutual TLS (mTLS)
  • Account and engine are provided via the username field
    • Username use a triple identifier format: <account>:<engine>:<service_account_id>
  • Lightdash relies on dbt to compile models and generate SQL
Note Because Lightdash fully relies on dbt to execute queries, the prerequisites and constraints are very similar to connecting dbt Cloud or dbt Core to Firebolt using the PostgreSQL adapter.

​
Prerequisites

Before starting, make sure you have:
  1. Lightdash
    • Cloud or self-hosted deployment
  2. Firebolt account
    • With access to a database and engine
  3. Firebolt service account
    • Client ID and client secret
    • A user associated with the service account
  4. Permissions
  5. dbt project
    • Models already built in Firebolt

​
Authentication and security

Lightdash connects to Firebolt using:
  • PostgreSQL protocol
  • Firebolt service account credentials
  • Mutual TLS (mTLS)
  • Full server certificate verification (verify-full)
Firebolt server certificates are issued by Let’s Encrypt, so the Let’s Encrypt root CA must be provided to Lightdash. From a security perspective, this setup is equivalent to dbt Core over PostgreSQL, with Lightdash acting as the client.

​
Generate mTLS certificates for Lightdash

​
Step 1: Generate certificates

Run the following script to generate all certificates required by Lightdash and Firebolt. This script:
  • Generates a client private key and certificate
  • Downloads the Let’s Encrypt root CA used by Firebolt servers
  • Derives a public key to attach to the Firebolt service account
#!/bin/bash
set -euo pipefail

CLIENT_CN="${CLIENT_CN:-firebolt-lightdash}"
DAYS_VALID="${DAYS_VALID:-730}"
OUT_DIR="${OUT_DIR:-./out}"

LE_ROOT_URL="https://letsencrypt.org/certs/isrgrootx1.pem.txt"

mkdir -p "$OUT_DIR"

echo "==> Client CN        : $CLIENT_CN"
echo "==> Validity (days)  : $DAYS_VALID"
echo "==> Output directory : $OUT_DIR"

# ---- 1) Download Firebolt server root CA (Let's Encrypt) ----
echo "==> Downloading Let's Encrypt root CA"
curl -fsSL "$LE_ROOT_URL" -o "$OUT_DIR/isrgrootx1.pem"

openssl x509 -in "$OUT_DIR/isrgrootx1.pem" -noout -subject >/dev/null

# ---- 2) Client private key ----
echo "==> Generating client private key"
openssl genrsa -out "$OUT_DIR/fb-client.key" 2048

# ---- 3) CSR ----
echo "==> Creating CSR"
openssl req -new \
  -key "$OUT_DIR/fb-client.key" \
  -out "$OUT_DIR/fb-client.csr" \
  -subj "/CN=$CLIENT_CN"

# ---- 4) Self-signed client certificate ----
cat > "$OUT_DIR/client.ext" <<'EOF'
basicConstraints=CA:FALSE
keyUsage=digitalSignature,keyEncipherment
extendedKeyUsage=clientAuth
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer
EOF

echo "==> Creating client certificate"
openssl x509 -req \
  -in "$OUT_DIR/fb-client.csr" \
  -signkey "$OUT_DIR/fb-client.key" \
  -out "$OUT_DIR/fb-client.crt" \
  -days "$DAYS_VALID" \
  -sha256 \
  -extfile "$OUT_DIR/client.ext"

rm -f "$OUT_DIR/fb-client.csr" "$OUT_DIR/client.ext"

# ---- 5) Public key for Firebolt service account ----
echo "==> Deriving public key for Firebolt service account"
openssl pkey \
  -in "$OUT_DIR/fb-client.key" \
  -pubout \
  -out "$OUT_DIR/fb-public.pem"

echo ""
echo "Done. Generated files:"
echo ""
echo "Lightdash:"
echo "  sslrootcert : $OUT_DIR/isrgrootx1.pem"
echo "  sslcert     : $OUT_DIR/fb-client.crt"
echo "  sslkey      : $OUT_DIR/fb-client.key"
echo ""
echo "Firebolt service account:"
echo "  Public key  : $OUT_DIR/fb-public.pem"

​
Step 2: Configure the Firebolt service account

Attach the generated public key to your Firebolt service account: 
ALTER SERVICE ACCOUNT "<service_account_name>"
SET PUBLIC_KEY = '-----BEGIN PUBLIC KEY-----
<contents of fb-public.pem>
-----END PUBLIC KEY-----';

​
Connect Lightdash to Firebolt

​
Step 1: Create a Lightdash project

  1. Open Lightdash
  2. Click Create project
  3. Select PostgreSQL as the warehouse
  4. Choose Manual setup

​
Step 2: Configure the PostgreSQL connection

Fill in the connection details as follows. Host: pg.<region>.app.firebolt.io
  • Replace <region> with your Firebolt region (for example: us-east-1)
User: <account_name>:<engine_name>:<service_account_client_id>
  • Where:
    • <account_name> is your Firebolt account name
    • <engine_name> is your Firebolt engine name
    • <service_account_client_id> is the client ID of your Firebolt service account
Password: <service_account_client_secret> DB name: <database_name>

​
Step 3: Advanced connection options (mTLS)

Expand Advanced connection options and configure:
  • Port: 5432
  • SSL mode: verify-full
  • SSL certificate: upload fb-client.crt
  • SSL private key: upload fb-client.key
  • SSL root certificate: upload isrgrootx1.pem

​
Integrate with your dbt project

When prompted to integrate Lightdash with dbt:
  • Select your dbt project

​
Test and deploy

  1. Click Test connection
  2. Verify the connection succeeds
  3. Deploy the project
Once deployed, Lightdash will:
  • Read dbt metadata
  • Generate SQL queries
  • Execute them against Firebolt via pg_fire

​
Additional resources

For more details on using Lightdash, see: These resources provide additional guidance on working with Lightdash and dbt-based analytics on top of Firebolt.