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.
Hex

​
Hex integration with Firebolt

Hex is a modern analytics and BI platform that combines SQL, notebooks, dashboards, and AI-powered data exploration. Hex allows teams to explore data, build dashboards, and ask natural-language questions that are automatically translated into SQL queries. This guide explains how to connect Hex to Firebolt using the PostgreSQL protocol with mutual TLS (mTLS) authentication.

​
Overview

Hex connects to Firebolt through the PostgreSQL-compatible endpoint exposed by Firebolt. Key characteristics of this integration:
  • Hex 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>
  • Hex executes SQL directly against Firebolt
  • Dashboards, SQL exploration, and Hex AI are supported
  • Tables should be located in the public schema

​
Prerequisites

Before starting, make sure you have:
  1. Hex
    • Hex Cloud workspace
    • Admin access to configure data connections
  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

​
Authentication and security

Hex connects to Firebolt using:
  • PostgreSQL protocol
  • Firebolt service account credentials
  • Mutual TLS (mTLS)
This setup ensures:
  • Encrypted connections
  • Strong client authentication
  • Compatibility with standard PostgreSQL drivers used by Hex

​
Generate mTLS certificates for Hex

​
Step 1: Generate certificates

Run the following script to generate the certificates required by Hex and Firebolt. This script:
  • Generates a client certificate and private key
  • 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-hex}"
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) Server root (verify Firebolt server cert) ----
echo "==> Downloading Let's Encrypt root"
curl -fsSL "$LE_ROOT_URL" -o "$OUT_DIR/isrgrootx1.pem"

# Sanity check
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-sign client cert with extensions ----
cat > "$OUT_DIR/client.ext" <<'EOF'
basicConstraints=CA:FALSE
keyUsage=digitalSignature,keyEncipherment
extendedKeyUsage=clientAuth
subjectKeyIdentifier=hash
authorityKeyIdentifier=keyid,issuer
EOF

echo "==> Creating self-signed client certificate (clientAuth)"
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 "Postgres/libpq:"
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 Hex to Firebolt

​
Step 1: Create a data connection in Hex

  1. Open Hex
  2. Go to Settings → Data sources
  3. Click + Connection
  4. Select PostgreSQL

​
Step 2: Configure the PostgreSQL connection

Fill in the connection form as follows. Name A friendly name, for example: Firebolt Host & port
  • Host: pg.<region>.app.firebolt.io
    • Replace <region> with your Firebolt region (for example: us-east-1)
  • Port: 5432
Database: <database_name>

​
Step 3: Authentication (mTLS)

​
Authentication settings

Type: Select Certificate Username: <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>

​
TLS configuration

Paste the values generated by the certificate script:
  • SSL certificate Paste the contents of fb-client.crt
  • SSL root certificate Paste the contents of isrgrootx1.pem
  • SSL key Paste the contents of fb-client.key
  • SSL password Leave empty (the private key is not encrypted)

​
Test and use the connection

  1. Click Create connection
  2. Hex will automatically test the connection
  3. Once the test succeeds, the connection is ready to use
After connecting, you can:
  • Run SQL queries directly against Firebolt
  • Build SQL-based charts and dashboards
  • Create UI dashboards on top of saved queries
  • Use Hex AI to ask natural-language questions such as:
    • “What is the city with the most orders?”
    • “What is the average number of order items per order?”
Hex translates these questions into SQL, executes them on Firebolt, and evaluates the results.

​
Known limitations and workarounds

Hex provides a rich UI for building calculated fields, measures, and formulas on top of connected data sources. However, not all Hex-generated calculation formulas are currently supported by Firebolt’s PostgreSQL-compatible SQL dialect. Because Hex dynamically generates SQL for these calculations, some functions may fail at query execution time even though the connection itself is working correctly.

​
Calculation functions that may not work

Based on current testing, the following categories of Hex calculation formulas are not fully supported:

​
Date & time calculations

  • Diff* functions (for example: DiffHours, DiffMinutes, DiffSeconds)
  • Second() and Millisecond() extraction
  • Some Trunc* functions on older Firebolt versions (for example, Firebolt 4.29)
    • These work correctly on newer versions (for example, 4.31)

​
String functions

  • Left()
  • Right()
  • StartsWith()
  • EndsWith() (may be generated using RIGHT() internally)
⚠️ This list is not guaranteed to be exhaustive. Hex may generate additional SQL expressions depending on the UI feature used.
If a calculation or formula does not work in Hex UI: 👉 Write plain SQL instead of using the Hex formula builder Hex allows you to:
  • Write raw SQL queries
  • Build charts and dashboards on top of SQL results
  • Use Firebolt-supported SQL functions directly
This approach avoids unsupported generated SQL and provides full control over query logic.

​
Ongoing improvements

Firebolt continues to expand PostgreSQL SQL compatibility. Additional functions may become supported over time, reducing the need for manual SQL workarounds.

​
Additional resources

For more details on using Hex and configuring PostgreSQL connections, refer to the official Hex documentation: These resources provide up-to-date guidance on connecting Hex to databases, managing connections, and working with SQL on top of Firebolt.