Air-gapped deployment

Run the SQL Flow control plane on your own servers with PostgreSQL, TLS and Data Protection keys, and install agents and brokers offline.

In an air-gapped deployment you run the control plane yourself, next to its PostgreSQL database, and nothing needs to reach the internet. This page sets up one control-plane instance from its container image, then connects agents and log brokers to it. For several instances, see High availability: Control plane. An air-gapped deployment needs the Air-gapped deployment capability pack, which includes offline licensing; see Plans and licensing.

Before you begin

• A Linux host with a container runtime such as Docker, for the control-plane image. The image runs on linux/amd64 and linux/arm64.
• The control-plane image and the agent and broker executables, copied into your network.
• PostgreSQL 12 or later, with one database reserved for this control plane.
• A DNS name for the control plane that users, agents and brokers resolve, for example sqlflow.corp.example.
• OpenSSL and Python 3 on the machine where you prepare certificates and the password hash.
• Time synchronization (NTP) on the control plane and on every agent and broker host. Agents and brokers ignore instructions issued more than 5 minutes ahead of their own clock.

Prepare the PostgreSQL database

Create a login and a database it owns. The login creates the control plane's tables when you run the migration step.

CREATE ROLE sqlflow_app LOGIN PASSWORD '<password>';
CREATE DATABASE sqlflow OWNER sqlflow_app;

Replace <password> with a strong password. Use TLS to the database where you can, with SSL Mode=VerifyFull in the connection string.

Choose how TLS is set up

The control plane serves HTTPS only. Agents and brokers trust only the certificate authority their join code names, not the operating system's trust store. Choose one of two setups:

• Let the control plane issue its certificate. This is the default when you supply no certificate. The control plane creates its own root authority, valid for 10 years, and a server certificate that it renews automatically. Set ControlPlane__PublicHostNames to every name and address clients use, separated by ;. The names are fixed at first start; a later start with a different list is refused with control_plane_local_ca_mismatch.
• Use your own certificate. Set Kestrel__Certificates__Default__Path and Kestrel__Certificates__Default__Password to a PFX file, and ControlPlane__CertificateAuthorityPath to the PEM file of the authority that issued it. Renewing the server certificate from the same authority needs no change on agents or brokers.

Browsers and the CLI use the operating system's trust store. With a self-issued authority, add the file from https://<control_plane_host>/ca.pem to the trust store of the machines that open the UI or run the CLI.

Create the Data Protection certificate

The control plane encrypts stored connection passwords and its own certificate authority with a key ring kept in the database. You supply the certificate, with an RSA key, that protects the key ring:

umask 077
openssl req -x509 -newkey rsa:3072 -nodes -days 3650 -subj "/CN=SQL Flow Data Protection" \
  -keyout dp.key -out dp.crt
read -rs -p "PFX password: " DP_PFX_PASSWORD; echo; export DP_PFX_PASSWORD
openssl pkcs12 -export -in dp.crt -inkey dp.key -out dp-cert.pfx -passout env:DP_PFX_PASSWORD

Create the SuperAdmin password hash

The control plane creates the SuperAdmin user and the first account from its configuration. The password is given as a hash. Generate one, and choose a password of at least 12 characters with an uppercase letter, a lowercase letter and a digit:

python3 -c 'import base64,getpass,hashlib,os; p=getpass.getpass("Password: ").encode(); s=os.urandom(16); print("pbkdf2-sha256$210000$" + base64.b64encode(s).decode() + "$" + base64.b64encode(hashlib.pbkdf2_hmac("sha256", p, s, 210000, 32)).decode())'

The output starts with pbkdf2-sha256$210000$. To change the SuperAdmin's password later, set a new hash and restart.

Write the configuration

1. Put the secrets in files, in a directory that you mount at /run/secrets. A file whose name has no dot is read as the setting of that name, so secrets stay out of the environment. The container runs as user ID 1654.

sudo mkdir -p /etc/sqlflow/secrets
sudo cp dp-cert.pfx /etc/sqlflow/secrets/dp-cert.pfx
printf '%s' 'Host=<postgres_host>;Port=5432;Database=sqlflow;Username=sqlflow_app;Password=<password>;SSL Mode=VerifyFull' \
  | sudo tee /etc/sqlflow/secrets/ConnectionStrings__SqlFlow > /dev/null
printf '%s' "$DP_PFX_PASSWORD" | sudo tee /etc/sqlflow/secrets/DataProtection__CertificatePassword > /dev/null
sudo chown -R 1654:1654 /etc/sqlflow/secrets && sudo chmod 0400 /etc/sqlflow/secrets/*

For SSL Mode=VerifyFull, also copy the PostgreSQL server's CA certificate into the directory, for example as db-ca.pem, and add ;Root Certificate=/run/secrets/db-ca.pem to the connection string.

2. Write the other settings to an environment file, /etc/sqlflow/control-plane.env:

ASPNETCORE_URLS=https://+:5100
ControlPlane__PublicHostNames=<control_plane_host>
DataProtection__KeyPath=/keys
DataProtection__CertificatePath=/run/secrets/dp-cert.pfx
SuperAdmin__Email=<admin_email>
SuperAdmin__PasswordHash=<password_hash>
SuperAdmin__Account__Name=<account_name>
SuperAdmin__Account__Domain=<email_domain>

Replace the placeholders: the DNS name from Before you begin, the SuperAdmin's email, the hash from the previous section, and a name and email domain for the first account, such as Corp and corp.example. To use your own certificate, remove ControlPlane__PublicHostNames and add the three certificate settings from Choose how TLS is set up.

Run the migration

Create the control plane's tables with the image's one-shot migrate step. It exits with code 0 when the schema is ready.

docker run --rm --env-file /etc/sqlflow/control-plane.env \
  -v /etc/sqlflow/secrets:/run/secrets:ro -v sqlflow-keys:/keys \
  <control_plane_image> migrate

Run the same step with each new release before you start it. A control plane started against a database it doesn't match refuses to start, with store_schema_too_old or store_schema_too_new.

Start the control plane

docker run -d --name sqlflow-control-plane --restart unless-stopped -p 443:5100 \
  --env-file /etc/sqlflow/control-plane.env \
  -v /etc/sqlflow/secrets:/run/secrets:ro -v sqlflow-keys:/keys \
  <control_plane_image>

Agents and brokers keep replicating while the control plane is stopped.

Verify

1. Read the start-up log with docker logs sqlflow-control-plane. It names the certificate authority and its fingerprint:

Control-plane TLS: local certificate authority "...", SHA-256 <fingerprint>, served at /ca.pem and carried as the trust anchor of every join code minted here; server certificate names: ...

2. Fetch the authority, check that its fingerprint matches the one in the log (both are lowercase hex), and check health:

curl -sk https://<control_plane_host>/ca.pem -o ca.pem
openssl x509 -in ca.pem -outform DER | sha256sum
curl --cacert ca.pem https://<control_plane_host>/healthz

/healthz answers {"status":"ok"}. /readyz also checks that the database answers; use it for monitoring, not for load-balancer health checks.

3. Open https://<control_plane_host> and sign in with the SuperAdmin email and password.

Install agents and log brokers

Copy SqlFlow.Agent.exe or SqlFlow.Agent, and SqlFlow.LogBroker.exe or SqlFlow.LogBroker, to the hosts that need them, and sqlflow.exe or sqlflow to the machines that run the CLI. Then follow Agents and Log brokers.

• Without downloads configured on the control plane, the Add agent and Add broker panels still create the join code and print the install command; use your copied files. See Air-gapped deployments.
• The install command that the panel prints includes --control-plane https://<control_plane_host>, because the executables otherwise connect to the hosted control plane.

Mail and notifications without internet access

• Mail. Without the Postmark__From and Postmark__Token settings, no mail is sent. When you add a user, the confirmation says No email was sent: mail is not configured on this server. and shows the user's one-time password on screen. Pass it on securely; the user chooses a new password at first sign-in.
• Webhooks. By default, notification webhooks are delivered only to public addresses. Loopback and private addresses, such as a chat or paging service on your internal network, are refused unless the control plane's administrator allows them. See Addresses SQL Flow refuses.

Back up

Restore a database backup only to recover from loss. Don't use an older copy to roll back an upgrade.

Next steps

• Agents
• Log brokers
• Network and ports
• Upgrades and restarts
• Plans and licensing