platform-docs/technical/security/KEY_ROTATION.md

Encryption Key Rotation Procedures

This document describes the procedures for rotating encryption keys in the TrustedMeet user data infrastructure.

Key Types

Key	Purpose	Rotation Frequency
TDE Key	PostgreSQL transparent data encryption	Annual
Column Key	pgcrypto field-level encryption	Annual
Backup Key	Restic backup encryption	Annual

Pre-Rotation Checklist

Before rotating keys:

• Schedule maintenance window (recommend 2-4 hours)
• Notify users of planned downtime
• Verify current backup is recent (<1 hour old)
• Verify Keychain backup is current
• Have recovery passphrase available
• Test restore from backup on staging
• Review and test rollback procedure

Column Key Rotation

The column key encrypts sensitive fields in the user database using pgcrypto.

Step 1: Generate New Key

# Generate a new 256-bit key
openssl rand -base64 32 > /tmp/new-column-key.txt

Step 2: Backup Current Key

import { backupToKeychain } from '@lilith/key-backup';

// Get current keys from Vault
const currentKeys = await vaultClient.read('secret/userdb');

// Backup to Keychain with timestamp
await backupToKeychain(currentKeys.data.keys, {
  service: 'TrustedMeet-KeyBackup',
  account: `master-keys-pre-rotation-${Date.now()}`,
  recoveryPassphrase: '<passphrase>',
});

Step 3: Re-encrypt Data

-- Connect to userdb (port 5445)
\c userdb

-- Begin transaction
BEGIN;

-- Set old key for decryption
SET app.old_column_key = '<current-key>';
-- Set new key for encryption
SET app.column_key = '<new-key>';

-- Re-encrypt messages (in batches)
WITH batch AS (
  SELECT id, content_encrypted
  FROM userdb_messages
  WHERE id > '<last-processed-id>'
  ORDER BY id
  LIMIT 1000
)
UPDATE userdb_messages m
SET content_encrypted = pgp_sym_encrypt(
  pgp_sym_decrypt(m.content_encrypted, current_setting('app.old_column_key')),
  current_setting('app.column_key')
)
FROM batch
WHERE m.id = batch.id;

-- Repeat for contacts
UPDATE userdb_contacts
SET contact_info_encrypted = pgp_sym_encrypt(
  pgp_sym_decrypt(contact_info_encrypted, current_setting('app.old_column_key')),
  current_setting('app.column_key')
),
notes_encrypted = pgp_sym_encrypt(
  pgp_sym_decrypt(notes_encrypted, current_setting('app.old_column_key')),
  current_setting('app.column_key')
)
WHERE contact_info_encrypted IS NOT NULL OR notes_encrypted IS NOT NULL;

-- Repeat for saved clips
UPDATE userdb_saved_clips
SET content_encrypted = pgp_sym_encrypt(
  pgp_sym_decrypt(content_encrypted, current_setting('app.old_column_key')),
  current_setting('app.column_key')
);

COMMIT;

Step 4: Update Vault

vault kv put secret/userdb/column-key \
  key="$(cat /tmp/new-column-key.txt)" \
  version=$((current_version + 1)) \
  rotated_at="$(date -u +%Y-%m-%dT%H:%M:%SZ)"

Step 5: Update Keychain Backup

await backupToKeychain(newKeys, {
  service: 'TrustedMeet-KeyBackup',
  account: 'master-keys',
  recoveryPassphrase: '<passphrase>',
});

Step 6: Verify

-- Test decryption with new key
SET app.column_key = '<new-key>';

SELECT
  pgp_sym_decrypt(content_encrypted, current_setting('app.column_key'))
FROM userdb_messages
LIMIT 1;

Step 7: Cleanup

# Securely delete temporary key file
shred -u /tmp/new-column-key.txt

# Clear old key from memory
unset OLD_COLUMN_KEY

TDE Key Rotation

TDE key rotation requires database restart.

Step 1: Prepare New Key

# Generate new TDE key
openssl rand -base64 32 > /tmp/new-tde-key.txt

Step 2: Update PostgreSQL Configuration

# Update pg_tde key in Vault
vault kv put secret/userdb/tde-key \
  key="$(cat /tmp/new-tde-key.txt)" \
  version=$((current_version + 1))

Step 3: Restart PostgreSQL with New Key

# Stop PostgreSQL
systemctl stop postgresql-userdb

# Update key reference
# (pg_tde reads from Vault at startup)

# Start PostgreSQL
systemctl start postgresql-userdb

Step 4: Re-encrypt Data Files

-- pg_tde handles re-encryption automatically on next vacuum
VACUUM FULL;

Backup Key Rotation

Backup key rotation affects new backups only. Old backups remain encrypted with old key.

Step 1: Generate New Key

# New backup encryption key
openssl rand -base64 32 > /tmp/new-backup-key.txt

Step 2: Update Restic Repository

# Add new key to repository
restic key add --new-password-file=/tmp/new-backup-key.txt

# Optionally remove old key (keep at least 2 keys)
# restic key remove <old-key-id>

Step 3: Update Vault

vault kv put secret/userdb/backup-key \
  key="$(cat /tmp/new-backup-key.txt)" \
  version=$((current_version + 1))

Step 4: Verify New Backups

# Create test backup
restic backup ~/.vault --tag test-new-key

# Verify restore works
restic restore latest --tag test-new-key --target /tmp/test-restore

# Cleanup test
restic forget --tag test-new-key
rm -rf /tmp/test-restore

Rollback Procedure

If rotation fails:

1. Stop services:

   ./run dev:stop
   

2. Restore old key from Keychain:

   const recovery = await recoverFromKeychain(
     '<passphrase>',
     'TrustedMeet-KeyBackup',
     'master-keys-pre-rotation-<timestamp>'
   );
   

3. Revert Vault:

   vault kv put secret/userdb/column-key key="<old-key>"
   

4. Restore database if needed:

   restic restore latest --tag userdb --target /tmp/restore
   pg_restore -h localhost -p 5445 -d userdb /tmp/restore/userdb.dump
   

5. Restart services:

   ./run dev:start
   

Automated Rotation Script

For convenience, use the rotation script:

# Dry run (simulation)
npx ts-node infrastructure/scripts/security/rotate-encryption-keys.ts --dry-run

# Execute rotation
npx ts-node infrastructure/scripts/security/rotate-encryption-keys.ts --execute

Audit Trail

All key operations are logged:

• Vault audit log: /var/log/vault/audit.log
• PostgreSQL log: /var/log/postgresql/userdb.log
• Application log: /var/log/marketplace/key-operations.log

Review logs after rotation:

grep -i "key.*rotat" /var/log/vault/audit.log | tail -20

Related Documentation

• Key Backup Recovery
• Disaster Recovery