Transparent Data Encryption Tutorial

Estimated Time: 20 minutes Difficulty: Intermediate

---

Table of Contents

1. Introduction
2. How Encryption Works
3. Setting Up Encryption
4. Key Management
5. Working with Encrypted Data
6. Security Best Practices
7. Compliance Scenarios
8. Troubleshooting

---

Introduction

Transparent Data Encryption (TDE) encrypts data at rest without changes to your SQL or application code. With TDE enabled, HeliosDB Lite encrypts row values before they are written to the data directory and decrypts them when they are read.

TDE helps with:

• Protecting database files against unauthorised file-system access.
• Protecting data on stolen or decommissioned storage media.
• Meeting encryption-at-rest controls in frameworks such as HIPAA, GDPR, PCI DSS and SOC 2 (TDE is one control among several; see Compliance Scenarios).

Zero-knowledge encryption, where keys never leave the client, is planned for Lite.

---

How Encryption Works

• Algorithm: AES-256-GCM (authenticated encryption: confidentiality plus integrity).
• Key size: 256 bits (32 bytes), supplied as 64 hexadecimal characters.
• Nonce: a random 96-bit (12-byte) nonce per encryption, stored in front of the ciphertext.
• Integrity: the GCM authentication tag makes any change to the stored bytes fail decryption instead of returning altered data.

Stored layout of one encrypted value:

┌──────────────┬──────────────────┬─────────────────┐
│   Nonce      │   Ciphertext     │   Auth Tag      │
│  (12 bytes)  │   (variable)     │   (16 bytes)    │
└──────────────┴──────────────────┴─────────────────┘

Each encrypted value therefore takes 28 bytes more than its plaintext.

---

Setting Up Encryption

Prerequisites

1. A secure place to keep the encryption key (secret manager, key file with tight permissions).
2. A backup copy of the key, stored separately from the database backups.
3. HELIOSDB_JWT_SECRET for the server’s REST API (the server does not start without it).

Step 1: Generate a Key

openssl rand -hex 32
# 64 hexadecimal characters

Step 2a: Key in an Environment Variable

export HELIOSDB_ENCRYPTION_KEY="$(openssl rand -hex 32)"

config.toml:

[encryption]
enabled = true
algorithm = "Aes256Gcm"
key_source = { Environment = "HELIOSDB_ENCRYPTION_KEY" }

Step 2b: Key in a File

openssl rand -hex 32 > /etc/heliosdb/encryption.key
chmod 400 /etc/heliosdb/encryption.key

config.toml:

[encryption]
enabled = true
algorithm = "Aes256Gcm"
key_source = { File = "/etc/heliosdb/encryption.key" }

The file may hold 64 hex characters or 32 raw bytes.

Step 3: Start the Server

export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir /var/lib/heliosdb --config config.toml

The key is loaded when the database opens. If it is missing or malformed, the server stops with an error instead of opening the database unencrypted.

Enable encryption when you create a database. Rows already stored in plaintext are not re-encrypted, and reading them fails once encryption is turned on.

Step 4: Use SQL as Usual

CREATE TABLE patients (id INT PRIMARY KEY, name TEXT, diagnosis TEXT);
INSERT INTO patients VALUES (1, 'Jane Doe', 'Hypertension');
SELECT * FROM patients WHERE id = 1;

Clients see plaintext; the data directory holds ciphertext.

---

Key Management

Key Storage Options

Method	Suitable for	Notes
Environment variable	Development, containers fed by a secret manager	Keep it out of shell history and process listings
Key file	Single-server deployments	chmod 400, owned by the database user
Cloud KMS (AWS, Azure, GCP)	Enterprise production	Planned: key_source = { Kms = ... } is parsed but not yet supported

Key Generation

• Use a cryptographically secure source (openssl rand -hex 32, or dd if=/dev/urandom bs=32 count=1 2>/dev/null | xxd -p -c 32).
• Never derive keys from predictable values (timestamps, host names, counters).
• Use a different key per database and per environment (dev, staging, production).

Key Backup

# Keep at least one offline copy, separate from the database backups
cp /etc/heliosdb/encryption.key /secure/backup/encryption.key
chmod 400 /secure/backup/encryption.key

Store a copy in your password manager or secret manager as well.

Supplying the Key to Services

# systemd unit: prefer an EnvironmentFile readable only by the service user
[Service]
EnvironmentFile=/etc/heliosdb/secrets.env

# Kubernetes: keep the key in a Secret and mount it as a file
kubectl create secret generic heliosdb-encryption \
  --from-file=key=/path/to/encryption.key

Key Rotation

Online key rotation (ALTER SYSTEM ROTATE ENCRYPTION KEY) is planned. In the current release the statement returns an error and changes nothing. Plan the key’s lifetime accordingly and protect it as a long-lived secret.

Lost Keys

Scenario	Recoverable?
Key file deleted, backup copy exists	Yes: restore the key from the backup
No copy of the key	No: the encrypted data cannot be decrypted
A different key is configured	No, until the original key is configured again

---

Working with Encrypted Data

Encryption is transparent: every SQL statement, client driver and REST call works the same way with TDE on or off. Indexes, joins, aggregates and transactions need no changes.

Each encrypted write costs one AES-GCM operation and 28 bytes of overhead per value (12-byte nonce plus 16-byte tag). The fixed overhead matters most for small values: a 1-byte value is stored in 29 bytes, a 100-byte value in 128 bytes and a 1,000-byte value in 1,028 bytes.

Encryption overhead per operation (internal tests, HeliosDB Lite with TDE vs without; reproduce on your own hardware and workload before sizing a deployment):

Operation	Unencrypted	Encrypted	Overhead
Small writes (64B)	1.2 µs	1.23 µs	+2.5%
Medium writes (1KB)	1.5 µs	1.54 µs	+2.7%
Large writes (16KB)	3.8 µs	3.91 µs	+2.9%
Point reads	0.8 µs	0.82 µs	+2.5%
Table scans (1000 rows)	850 µs	875 µs	+2.9%
Indexed lookups	1.1 µs	1.13 µs	+2.7%

The overhead stays under 3% when the CPU has hardware AES (check with lscpu | grep -i aes on Linux or sysctl -a | grep -i aes on macOS). AES-256-GCM per operation with and without AES-NI (internal tests, HeliosDB Lite):

CPU	Without AES-NI	With AES-NI	Speedup
Intel Core i7	~15 µs	~1.2 µs	12x faster
AMD Ryzen 7	~14 µs	~1.1 µs	13x faster
ARM (modern)	~10 µs	~0.9 µs	11x faster

---

Security Best Practices

File-System Permissions

# Dedicated service user
useradd -r -s /bin/false heliosdb

# Key readable by the service only
chown root:heliosdb /etc/heliosdb/encryption.key
chmod 440 /etc/heliosdb/encryption.key

# Data directory owned by the service user
chown heliosdb:heliosdb /var/lib/heliosdb
chmod 700 /var/lib/heliosdb

Encryption in Transit

TDE protects data at rest. Protect connections with TLS and password authentication:

export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir /var/lib/heliosdb --config config.toml \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD" \
  --tls-cert /etc/heliosdb/server.crt --tls-key /etc/heliosdb/server.key

The server then offers TLS to every client; require it from clients with sslmode=require (or stricter) in their connection settings.

See PostgreSQL SSL/TLS Configuration.

Audit Logging

Built-in audit logging is planned; see Audit Logging for the planned design and for recording changes with triggers in the meantime.

---

Compliance Scenarios

TDE covers the encryption-at-rest control. The other controls come from TLS, authentication, access control and your operating procedures.

Control	HeliosDB Lite setting
Encryption at rest	[encryption] enabled = true
Encryption in transit	--tls-cert / --tls-key at startup, sslmode=require on clients
Authentication	--auth scram-sha-256 with a password
Access control	SQL roles, GRANT / REVOKE, row-level security
Audit trail	Built-in audit logging is planned; use triggers to record changes today
Key management	Key file or secret manager; KMS integration is planned

Example configuration for a regulated workload:

[encryption]
enabled = true
algorithm = "Aes256Gcm"
key_source = { File = "/secure/keys/encryption.key" }

[server]
listen_addr = "127.0.0.1"

Start it with TLS and password authentication:

export HELIOSDB_JWT_SECRET="$(openssl rand -hex 32)"
heliosdb-lite start --data-dir /var/lib/heliosdb --config config.toml \
  --auth scram-sha-256 --password "$HELIOSDB_ADMIN_PASSWORD" \
  --tls-cert /etc/heliosdb/server.crt --tls-key /etc/heliosdb/server.key

Store the minimum personal data needed, and use row-level security so each user sees only the rows they are entitled to (see RLS Quick Start).

---

Troubleshooting

”Environment variable ‘HELIOSDB_ENCRYPTION_KEY’ not found”

The configured variable is not set in the server’s environment.

echo "${HELIOSDB_ENCRYPTION_KEY:?not set}"
export HELIOSDB_ENCRYPTION_KEY="<64-character-hex-key>"

For systemd, put the variable in the unit’s EnvironmentFile.

”Hex key must be 64 characters (32 bytes), got N”

The key is not 64 hex characters. Generate a new key with openssl rand -hex 32, or check for stray whitespace or quotes:

echo -n "$HELIOSDB_ENCRYPTION_KEY" | wc -c   # expect 64

“Failed to read key file …”

The server user cannot read the key file, or the path is wrong.

ls -l /etc/heliosdb/encryption.key
sudo chown root:heliosdb /etc/heliosdb/encryption.key
sudo chmod 440 /etc/heliosdb/encryption.key

Decryption errors on read

The configured key is not the key the data was written with, or the stored bytes were altered. Configure the original key; if the files are damaged, restore them from a backup.

”KMS provider ’…’ not yet supported”

KMS key sources are planned. Use an environment variable or a key file.

---

Summary

1. Generate a 256-bit key with openssl rand -hex 32 and back it up.
2. Add an [encryption] section to config.toml that points at the key.
3. Start the server with --config; SQL and clients work unchanged.
4. Combine TDE with TLS, password authentication and access control.

See also the Encryption Quick Start.