Database security

The four layers

Layer 1 · LUKS filesystem encryption

Run separately via scripts/setup_luks.sh. See LUKS setup.

Layer 2 · Field encryption

Sensitive values are stored as AES-256-GCM ciphertext with per-row nonces. Columns currently protected:

• secrets.ciphertext — the vault payload
• users.mfa_secret_enc — TOTP seeds
• AD / LDAP bind passwords, API credentials, ACME DNS-provider tokens (via secrets FKs)

The 32-byte master key lives at /etc/meridian/secrets/master.key (0400 meridian:meridian). The app derives per-domain subkeys (HMAC-based) so leakage of one context doesn't compromise the others.

Rotating the master key

sudo meridian-nip secrets rotate-master
# App re-encrypts all vault rows under a new key_version.
# Old key marked retired; usable for decrypt-only reads during migration.

Layer 3 · Row-hash tamper evidence

Eight tables carry a row_hash BYTEA column: audit_events · license · license_activations · license_verifications · cert_events · approvals · impersonations · update_history.

On every insert, the app computes

row_hash = HMAC-SHA-256(
  key     = load('/etc/meridian/secrets/row_hmac.key'),
  message = canonical_row_fields || previous_row_hash
)

The previous_row_hash term chains each row to the one before it. Any offline modification breaks the chain at the modified row and every subsequent row.

How tampering is detected

• Live path. Whenever the app reads an audit record, it re-computes the HMAC and compares. Mismatch raises a CRITICAL alert immediately.
• Scheduled path. The db-integrity-scan job runs daily at 05:00 UTC, walks every tamper-evident table, and emails all admins + SIEM on any mismatch.
• Ad-hoc. sudo meridian-nip integrity scan runs it on demand.

What it catches

• Someone deleting their own audit events
• An attacker granting themselves a permission without a matching audit trail
• A silently-altered certificate record

Any of these break the chain at the modified row. The scan reports the exact ctid and table; combined with PG's WAL (if archive_mode is on, which it is by default), forensic replay is possible.

Layer 4 · Direct-SQL access restriction

• PostgreSQL binds localhost only (listen_addresses = 'localhost').
• pg_hba.conf allows only the meridian-app role via local socket with SCRAM-SHA-256. Everything else is explicitly rejected.
• Port 5432 is explicitly denied by UFW as a belt-and-braces measure.
• A separate meridian-readonly role exists (disabled by default) for read-only reporting integrations.
• There is no postgres superuser login allowed from the app host; emergency recovery requires physical access plus the LUKS passphrase (if configured) plus the master key file.

A shell admin with sudo -u postgres psql access can technically run arbitrary SQL, but any modification to a tamper-evident table is detected within 24 hours — and logged with before/after hashes so you know exactly what changed.

Master key lifecycle

Initial generation

Done automatically by install.sh via openssl rand -out ... 32. Permissions are locked to 0400 meridian:meridian.

Backup

The key files are not included in routine backups by default. Include them only when creating a DR-grade bundle:

sudo /opt/meridian/scripts/backup.sh --include-keys --output /secure/offsite/

Without the keys, any backup tarball is useless against a future restore. With the keys, the bundle is full DR-grade and must be stored with the same care as the original host.

Loss recovery

There is no recovery path from master key loss. The database is permanently unreadable. Your only recourse is to restore from a --include-keys backup made before the loss. This is why the installer prints a bold warning after key generation.

What customers should NOT do

• Copy the master key file to unencrypted storage
• Share the key file across hosts (each install must generate its own)
• Check the key file into git, Ansible vault, or similar
• Attempt to "export" audit events via direct SQL to skip row-hash validation

Incident response: integrity alert received

1. Do not restart or redeploy. Preserve the host as-is.
2. Pull an audit snapshot: sudo meridian-nip audit export --since "2h ago" --output /tmp/audit.json
3. Run the integrity scanner with verbose mode: sudo meridian-nip integrity scan --verbose
4. Cross-reference the mismatched row with PG WAL (/var/lib/meridian/backups/wal/) to identify the exact modification.
5. Escalate per your internal IR playbook.