ops: schedule home pilot backup and retain verified PC copies

deploy/pilot/backup.env.example

@@ -0,0 +1,7 @@
+# The certificate contains only a public key. Keep its private key off the server.
+BACKUP_DIRECTORY=/var/backups/krona-staff
+BACKUP_RECIPIENT_CERT=/etc/krona-staff/backup-recipient.crt
+# This-PC backups are collected through scripts/pull-pilot-backups.sh.
+BACKUP_TRANSFER_MODE=external-pull
+# Needed only for BACKUP_TRANSFER_MODE=push. For this-PC storage use pull script.
+BACKUP_REMOTE=

deploy/pilot/backup.home-server.example

@@ -0,0 +1,4 @@
+# Test pilot on the owner's home server; no private key is stored here.
+BACKUP_DIRECTORY=/home/ochenstarik/krona-staff-pilot/backups
+BACKUP_RECIPIENT_CERT=/home/ochenstarik/krona-staff-pilot/deploy/pilot/secrets/backup-recipient.crt
+BACKUP_TRANSFER_MODE=external-pull

deploy/pilot/backup.sh

@@ -0,0 +1,53 @@
+#!/usr/bin/env sh
+# Encrypt before writing to disk; keep the private recipient key off this host.
+set -eu
+umask 077
+
+: "${BACKUP_DIRECTORY:?Set a dedicated absolute backup directory}"
+: "${BACKUP_RECIPIENT_CERT:?Set the public recipient certificate path}"
+BACKUP_TRANSFER_MODE="${BACKUP_TRANSFER_MODE:-push}"
+case "$BACKUP_DIRECTORY" in /*) ;; *) echo 'Backup directory must be absolute' >&2; exit 1 ;; esac
+BACKUP_DIRECTORY=$(realpath -m -- "$BACKUP_DIRECTORY")
+case "$BACKUP_DIRECTORY" in /|/home|/tmp|/var|/usr|/etc|/opt|/srv|/root|/mnt|/media) echo 'Use a dedicated backup directory' >&2; exit 1 ;; esac
+case "$BACKUP_TRANSFER_MODE" in
+  push)
+    : "${BACKUP_REMOTE:?Set an off-host scp destination, for example backup@host:/backups/staff/}"
+    case "$BACKUP_REMOTE" in -*|*\ *|*\;*|*\`*|*\$*) echo 'Invalid backup destination' >&2; exit 1 ;; esac
+    case "$BACKUP_REMOTE" in *:/*/) ;; *) echo 'Off-host destination must be host:/absolute/directory/' >&2; exit 1 ;; esac
+    ;;
+  external-pull) ;;
+  *) echo 'BACKUP_TRANSFER_MODE must be push or external-pull' >&2; exit 1 ;;
+esac
+test -r "$BACKUP_RECIPIENT_CERT"
+openssl x509 -in "$BACKUP_RECIPIENT_CERT" -noout -checkend 0 >/dev/null
+cd "$(dirname "$0")"
+mkdir -p "$BACKUP_DIRECTORY"
+stamp=$(date -u '+%Y%m%dT%H%M%SZ')
+name="krona-staff-$stamp-$$.dump.cms"
+output="$BACKUP_DIRECTORY/$name"
+scratch=$(mktemp -d "$BACKUP_DIRECTORY/.backup-XXXXXX")
+producer=''
+cleanup() {
+  if [ -n "$producer" ]; then kill "$producer" 2>/dev/null || true; fi
+  rm -f -- "$scratch/dump.pipe" "$scratch/encrypted.partial"
+  rmdir -- "$scratch"
+}
+trap cleanup EXIT HUP INT TERM
+mkfifo "$scratch/dump.pipe"
+docker compose exec -T postgres sh -eu -c 'exec pg_dump -U "$POSTGRES_USER" -d "$POSTGRES_DB" -Fc' > "$scratch/dump.pipe" &
+producer=$!
+openssl cms -encrypt -binary -aes-256-cbc -outform DER -stream \
+  -in "$scratch/dump.pipe" -out "$scratch/encrypted.partial" "$BACKUP_RECIPIENT_CERT"
+wait "$producer"
+producer=''
+test -s "$scratch/encrypted.partial"
+mv -- "$scratch/encrypted.partial" "$output"
+(cd "$BACKUP_DIRECTORY" && sha256sum "$name" > "$name.sha256")
+if [ "$BACKUP_TRANSFER_MODE" = push ]; then
+  scp -B "$output" "$output.sha256" "$BACKUP_REMOTE"
+  copy_status=completed
+else
+  copy_status=pending_pull
+fi
+printf 'backup=%s\nchecksum=%s\noff_host_copy=%s\n' "$output" "$output.sha256" "$copy_status"
+# This is an encrypted backup, not evidence of a successful restore drill.

deploy/pilot/krona-staff-backup-user.service

@@ -0,0 +1,11 @@
+[Unit]
+Description=Encrypted Krona Staff PostgreSQL backup (user service)
+After=network-online.target
+
+[Service]
+Type=oneshot
+WorkingDirectory=%h/krona-staff-pilot/deploy/pilot
+EnvironmentFile=%h/krona-staff-pilot/deploy/pilot/backup.env
+ExecStart=/bin/sh %h/krona-staff-pilot/deploy/pilot/backup.sh
+UMask=0077
+NoNewPrivileges=true

deploy/pilot/krona-staff-backup.service

@@ -0,0 +1,13 @@
+[Unit]
+Description=Encrypted Krona Staff PostgreSQL backup
+Requires=docker.service
+After=docker.service network-online.target
+
+[Service]
+Type=oneshot
+User=root
+WorkingDirectory=/opt/krona-staff/deploy/pilot
+EnvironmentFile=/etc/krona-staff/backup.env
+ExecStart=/bin/sh /opt/krona-staff/deploy/pilot/backup.sh
+UMask=0077
+NoNewPrivileges=true

deploy/pilot/krona-staff-backup.timer

@@ -0,0 +1,10 @@
+[Unit]
+Description=Daily Krona Staff encrypted backup
+
+[Timer]
+OnCalendar=*-*-* 03:30:00
+RandomizedDelaySec=600
+Persistent=true
+
+[Install]
+WantedBy=timers.target

docs/BACKUP-RESTORE.md

@@ -7,3 +7,101 @@ Restore runbook: остановить записи, проверить checksum/
 Тест восстановления обязателен по расписанию в изолированной среде. До принятия RPO/RTO и retention документ является архитектурным шаблоном, а не обещанием показателей.
 
 Tenant export/import использует versioned archive, ULID, schema version, object references и checksum. Import идемпотентен и не может перезаписать другой tenant.
+
+## Pilot implementation — K18
+
+Status: implementation in progress. A pilot restore drill succeeded on 2026-09-26;
+the remaining schedule, retention and migration gate are listed below. Do not
+use the presence of an encrypted file alone as permission to migrate tenant data.
+
+## Storage and keys
+
+`deploy/pilot/backup.sh` streams `pg_dump -Fc` through OpenSSL CMS encryption.
+Only the public recipient certificate belongs on the server. Keep the private
+recipient key in separate, access-controlled storage outside that server, with a
+recovery copy. Loss of this key makes backups unrecoverable.
+
+Configure the variables in `deploy/pilot/backup.env.example` using an operator-owned
+environment file, never a committed secrets file. For the current test setup, use
+`BACKUP_TRANSFER_MODE=external-pull` and schedule `scripts/pull-pilot-backups.sh`
+on this PC with `PILOT_SSH_TARGET`, `PILOT_REMOTE_BACKUP_DIRECTORY` and
+`BACKUP_LOCAL_DIRECTORY`. The PC initiates SSH, so it does not need an SSH server.
+Configure non-interactive SSH and verify the server's host key out of band. Do not
+disable host-key verification. A SHA-256 sidecar detects transfer corruption;
+it is not a digital signature. Other deployments may use push mode with an
+off-host SCP destination.
+
+The PC pull script keeps 90 days of local copies by default. Set
+`BACKUP_LOCAL_RETENTION_DAYS` to an integer from 1 to 36500 to change this period.
+After a successful pull and freshness check, it removes only direct child files with
+the generated `krona-staff-<UTC timestamp>-<pid>.dump.cms` name whose one-line
+SHA-256 sidecar names that same file and matches its bytes. It removes the matching
+`.sha256` sidecar with the encrypted file. Unverifiable pairs and symlinks are
+preserved, as are unrelated files and directories. This policy applies to PC copies;
+it does not prune the server's backup directory.
+
+The server script fails if dumping or encryption fails. In push mode it also fails
+if off-host transfer fails. It does not write an unencrypted dump to disk. In
+external-pull mode a successful server run reports `pending_pull`, never an
+off-host success. The PC pull script verifies every transferred encrypted file
+against its checksum and exits nonzero on transfer or integrity failure.
+
+## Scheduling
+
+The service and timer in `deploy/pilot/` are templates. Adjust their working and
+configuration paths to the actual installation before enabling them. The timer
+runs daily at 03:30 in the server's timezone, with up to ten minutes of jitter.
+Monitor both the server service and the PC pull task exit statuses. Verify that
+the PC directory contains a recent copy; the server-only encrypted file is not
+evidence of off-host protection.
+
+Do not enable the schedule until the destination, recovery-key custody, server-side
+retention policy, and a successful restore drill have been agreed and tested.
+
+## Required restore drill before tenant migration
+
+1. Produce a consistent dump, encrypt it, and retrieve the off-host copy.
+2. Verify the encrypted file's SHA-256 checksum at the destination.
+3. Decrypt with the off-host key. Restore into a new disposable PostgreSQL 17
+   instance, with no connection to the pilot network and no exposed database port.
+   Never run a test provisioning script against the pilot cluster: it may alter
+   roles or drop databases.
+4. Restore required roles and grants, schema, data, forced RLS, constraints and
+   migration history. A restore using `--no-owner --no-acl` alone is not enough
+   evidence that production privileges can be recovered.
+5. Compare per-table and per-tenant row counts and payroll ledger totals per
+   currency against a manifest from the same consistent snapshot. Check foreign
+   keys, forced RLS, and isolation using a non-superuser application role.
+6. Run the tenant migration on that restored copy and verify that only the tenant
+   changes: row counts and ledger totals are preserved, with one additional audit
+   record for the migration.
+7. Save a report containing timestamp, source identity, backup checksum, PostgreSQL
+   version, commands and exit statuses, before/after counts, totals and isolation
+   result. Do not include passwords, tokens, employee names, or biometric data.
+8. Only after a successful drill and an agreed maintenance window may the pilot
+   application be stopped and the operation repeated on a fresh backup.
+
+## Outstanding acceptance items
+
+- Off-host storage and retention policy selected and configured.
+- Restore automation and repeatable reporting for later backups.
+- A fail-closed backup gate before migrations in `setup.sh`.
+
+Until these items are complete, K18 remains open and pilot tenant migration is blocked.
+
+## Pilot restore drill — 2026-09-26
+
+An encrypted `pg_dump -Fc` from `krona-staff-pilot-postgres-1` was pulled to the
+owner's PC. Its SHA-256 was
+`2fc680ae3a6ce63a27b53209da9b506bcee86b76d8bba9a6a06de24bac1dee47`.
+It was decrypted on the PC and streamed through SSH into a new PostgreSQL 17
+container with `network=none`, no host ports, and temporary storage. The restore
+command returned 0 with ownership and ACLs restored. The reports produced by
+`scripts/restore-drill-report.sql` matched for every tenant table: 51 employees,
+3,830 ledger entries, 3,353 work items, 2,894 daily metrics and 49 audit rows;
+the other nonempty tables matched too. All 27 tenant tables had ENABLE and FORCE
+RLS. The RUB ledger sum matched at 717,829,954 minor units. A nonsuperuser,
+non-BYPASSRLS application role saw 51 employees under the source tenant and zero
+under the target. The restore report and encrypted backup are stored on the PC
+in `C:/Users/trush/Documents/KronaStaffBackups/`. This was a restore drill, not
+yet a tenant migration or a check that the backup schedule runs unattended.

scripts/pull-pilot-backups.sh

@@ -0,0 +1,119 @@
+#!/usr/bin/env bash
+# Run on the backup PC (Git Bash), after the server has made encrypted backups.
+set -euo pipefail
+umask 077
+
+: "${PILOT_SSH_TARGET:?Set the configured SSH alias for the pilot server}"
+: "${PILOT_REMOTE_BACKUP_DIRECTORY:?Set its absolute encrypted backup directory}"
+: "${BACKUP_LOCAL_DIRECTORY:?Set an absolute backup directory on this PC}"
+BACKUP_LOCAL_RETENTION_DAYS=${BACKUP_LOCAL_RETENTION_DAYS:-90}
+[[ "$BACKUP_LOCAL_RETENTION_DAYS" =~ ^[1-9][0-9]{0,4}$ ]] &&
+  (( BACKUP_LOCAL_RETENTION_DAYS <= 36500 )) || {
+  echo 'BACKUP_LOCAL_RETENTION_DAYS must be an integer from 1 to 36500' >&2
+  exit 1
+}
+
+[[ "$PILOT_SSH_TARGET" =~ ^[a-zA-Z0-9_.@-]+$ ]] || { echo 'Invalid SSH target' >&2; exit 1; }
+[[ "$PILOT_REMOTE_BACKUP_DIRECTORY" =~ ^/[a-zA-Z0-9_./-]+$ ]] || { echo 'Invalid server backup directory' >&2; exit 1; }
+[[ "$PILOT_REMOTE_BACKUP_DIRECTORY" != */../* && "$PILOT_REMOTE_BACKUP_DIRECTORY" != */.. ]] || { echo 'Parent traversal is not allowed' >&2; exit 1; }
+[[ "$PILOT_REMOTE_BACKUP_DIRECTORY" != / ]] || { echo 'Use a dedicated server backup directory' >&2; exit 1; }
+[[ "$BACKUP_LOCAL_DIRECTORY" = /* && "$BACKUP_LOCAL_DIRECTORY" != / ]] || { echo 'Use a dedicated absolute local directory' >&2; exit 1; }
+[[ "$BACKUP_LOCAL_DIRECTORY" != */../* && "$BACKUP_LOCAL_DIRECTORY" != */.. ]] || { echo 'Parent traversal is not allowed' >&2; exit 1; }
+[[ ! -L "$BACKUP_LOCAL_DIRECTORY" ]] || { echo 'Local directory must not be a symlink' >&2; exit 1; }
+
+mkdir -p -- "$BACKUP_LOCAL_DIRECTORY"
+local_dir=$(cd -- "$BACKUP_LOCAL_DIRECTORY" && pwd -P)
+[[ "$local_dir" != / && "$local_dir" != /tmp && "$local_dir" != /var && "$local_dir" != /home ]] || {
+  echo 'Local backup path resolves to a broad directory' >&2
+  exit 1
+}
+cd -- "$local_dir"
+
+remote_dir=${PILOT_REMOTE_BACKUP_DIRECTORY%/}
+listing=$(ssh -o BatchMode=yes -- "$PILOT_SSH_TARGET" \
+  "find '$remote_dir' -maxdepth 1 -type f -name 'krona-staff-*.dump.cms' -printf '%f\n'")
+[[ -n "$listing" ]] || { echo 'No encrypted backups found on server' >&2; exit 1; }
+
+copied=0
+latest_backup_epoch=0
+while IFS= read -r name; do
+  [[ "$name" =~ ^krona-staff-[0-9]{8}T[0-9]{6}Z-[0-9]+\.dump\.cms$ ]] || {
+    echo 'Unexpected backup filename from server' >&2
+    exit 1
+  }
+  timestamp=${name#krona-staff-}
+  timestamp=${timestamp%%-*}
+  backup_epoch=$(date -u -d "${timestamp:0:4}-${timestamp:4:2}-${timestamp:6:2} ${timestamp:9:2}:${timestamp:11:2}:${timestamp:13:2} UTC" +%s)
+  if (( backup_epoch > latest_backup_epoch )); then
+    latest_backup_epoch=$backup_epoch
+  fi
+
+  if [[ -e "$name" || -e "$name.sha256" ]]; then
+    [[ -f "$name" && -f "$name.sha256" ]] || { echo "Incomplete local copy: $name" >&2; exit 1; }
+    sha256sum -c -- "$name.sha256" >/dev/null || { echo "Local checksum mismatch: $name" >&2; exit 1; }
+    continue
+  fi
+
+  encrypted_partial="$name.partial"
+  checksum_partial="$name.sha256.partial"
+  [[ ! -e "$encrypted_partial" && ! -e "$checksum_partial" ]] || {
+    echo "Interrupted prior copy needs inspection: $name" >&2
+    exit 1
+  }
+  scp -B -- "$PILOT_SSH_TARGET:$remote_dir/$name" "$encrypted_partial"
+  scp -B -- "$PILOT_SSH_TARGET:$remote_dir/$name.sha256" "$checksum_partial"
+  expected=$(awk 'NR == 1 { print $1 }' "$checksum_partial")
+  [[ "$expected" =~ ^[[:xdigit:]]{64}$ ]] || { echo "Malformed checksum: $name" >&2; exit 1; }
+  actual=$(sha256sum -- "$encrypted_partial")
+  [[ "${actual%% *}" = "$expected" ]] || { echo "Transfer checksum mismatch: $name" >&2; exit 1; }
+  mv -- "$encrypted_partial" "$name"
+  mv -- "$checksum_partial" "$name.sha256"
+  copied=$((copied + 1))
+done <<< "$listing"
+
+max_age_hours=${BACKUP_MAX_AGE_HOURS:-36}
+[[ "$max_age_hours" =~ ^[1-9][0-9]*$ ]] || { echo 'BACKUP_MAX_AGE_HOURS must be a positive integer' >&2; exit 1; }
+now_epoch=$(date -u +%s)
+(( now_epoch >= latest_backup_epoch && now_epoch - latest_backup_epoch <= max_age_hours * 3600 )) || {
+  echo 'Newest encrypted backup is stale or has a future timestamp' >&2
+  exit 1
+}
+
+retention_cutoff=$((now_epoch - BACKUP_LOCAL_RETENTION_DAYS * 86400))
+pruned=0
+retention_skipped=0
+while IFS= read -r -d '' backup_path; do
+  name=${backup_path##*/}
+  [[ "$name" =~ ^krona-staff-([0-9]{8}T[0-9]{6}Z)-[0-9]+\.dump\.cms$ ]] || continue
+  timestamp=${BASH_REMATCH[1]}
+  backup_epoch=$(date -u -d "${timestamp:0:4}-${timestamp:4:2}-${timestamp:6:2} ${timestamp:9:2}:${timestamp:11:2}:${timestamp:13:2} UTC" +%s 2>/dev/null) || continue
+  [[ "$(date -u -d "@$backup_epoch" '+%Y%m%dT%H%M%SZ')" == "$timestamp" ]] || continue
+  (( backup_epoch < retention_cutoff )) || continue
+
+  checksum_path="$local_dir/$name.sha256"
+  if [[ ! -f "$backup_path" || -L "$backup_path" || ! -f "$checksum_path" || -L "$checksum_path" ]] ||
+    [[ "$(wc -l < "$checksum_path")" -ne 1 ]]; then
+    retention_skipped=$((retention_skipped + 1))
+    continue
+  fi
+  read -r expected stored_name extra < "$checksum_path" || {
+    retention_skipped=$((retention_skipped + 1))
+    continue
+  }
+  if [[ ! "$expected" =~ ^[[:xdigit:]]{64}$ || "$stored_name" != "$name" || -n "${extra:-}" ]]; then
+    retention_skipped=$((retention_skipped + 1))
+    continue
+  fi
+  actual=$(sha256sum -- "$backup_path")
+  if [[ "${actual%% *}" != "$expected" ]]; then
+    retention_skipped=$((retention_skipped + 1))
+    continue
+  fi
+
+  rm -- "$backup_path" "$checksum_path"
+  pruned=$((pruned + 1))
+done < <(find "$local_dir" -mindepth 1 -maxdepth 1 -type f -name 'krona-staff-*.dump.cms' -print0)
+
+printf 'off_host_backup_count=%s\nnewly_copied=%s\nretention_pruned=%s\nretention_skipped=%s\n' \
+  "$(find "$local_dir" -maxdepth 1 -type f -name 'krona-staff-*.dump.cms' | wc -l)" \
+  "$copied" "$pruned" "$retention_skipped"

scripts/restore-drill-isolation.sql

@@ -0,0 +1,8 @@
+\set ON_ERROR_STOP on
+BEGIN;
+SET LOCAL ROLE krona_staff_app;
+SET LOCAL app.tenant_id = '01J00000000000000000000000';
+SELECT 'source-employee-count', count(*) FROM public.staff_employees;
+SET LOCAL app.tenant_id = '01M2R6AC5652NPT3MG34DKN4ZP';
+SELECT 'target-employee-count', count(*) FROM public.staff_employees;
+ROLLBACK;

scripts/restore-drill-report.sql

@@ -0,0 +1,27 @@
+\set ON_ERROR_STOP on
+CREATE TEMP TABLE _staff_restore_counts (table_name text NOT NULL, tenant_id varchar(26) NOT NULL, row_count bigint NOT NULL);
+DO $$
+DECLARE item record;
+BEGIN
+  FOR item IN
+    SELECT c.relname
+      FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
+     WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p')
+       AND left(c.relname, 6) = 'staff_'
+     ORDER BY c.relname
+  LOOP
+    EXECUTE format(
+      'INSERT INTO _staff_restore_counts SELECT %L, tenant_id, count(*) FROM public.%I GROUP BY tenant_id',
+      item.relname, item.relname
+    );
+  END LOOP;
+END $$;
+SELECT table_name, tenant_id, row_count
+  FROM _staff_restore_counts ORDER BY table_name, tenant_id;
+SELECT 'RLS', count(*), bool_and(c.relrowsecurity AND c.relforcerowsecurity)
+  FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
+ WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p')
+   AND left(c.relname, 6) = 'staff_';
+SELECT 'LEDGER', tenant_id, currency_code, sum(amount_minor)
+  FROM public.staff_payroll_ledger
+ GROUP BY tenant_id, currency_code ORDER BY tenant_id, currency_code;

scripts/run-pilot-backup-pull.ps1

@@ -0,0 +1,23 @@
+$ErrorActionPreference = 'Stop'
+
+$env:PILOT_SSH_TARGET = 'krona-home'
+$env:PILOT_REMOTE_BACKUP_DIRECTORY = '/home/ochenstarik/krona-staff-pilot/backups'
+$env:BACKUP_LOCAL_DIRECTORY = '/c/Users/trush/Documents/KronaStaffBackups'
+$env:BACKUP_MAX_AGE_HOURS = '36'
+
+$taskBash = 'C:\Program Files\Git\bin\bash.exe'
+$taskPull = Join-Path $PSScriptRoot 'pull-pilot-backups.sh'
+$taskLog = 'C:\Users\trush\Documents\KronaStaffBackups\pull.log'
+
+try {
+  $taskResult = & $taskBash $taskPull 2>&1
+  $taskExit = $LASTEXITCODE
+  $taskResult | Out-File -LiteralPath $taskLog -Append -Encoding utf8
+  if ($taskExit -ne 0) {
+    throw "Backup pull failed with exit code $taskExit"
+  }
+} catch {
+  "$(Get-Date -Format o) $($_.Exception.Message)" |
+    Out-File -LiteralPath $taskLog -Append -Encoding utf8
+  exit 1
+}

scripts/test-backup-pull.sh

@@ -0,0 +1,214 @@
+#!/usr/bin/env bash
+set -euo pipefail
+
+repo_root=$(cd -- "$(dirname -- "$0")/.." && pwd -P)
+pull_script="$repo_root/scripts/pull-pilot-backups.sh"
+test_parent=$(cd -- /tmp && pwd -P)
+test_root=$(mktemp -d "$test_parent/krona-backup-pull-test.XXXXXX")
+test_root=$(cd -- "$test_root" && pwd -P)
+is_test_root() {
+  [ "$(dirname -- "$1")" = "$test_parent" ] || return 1
+  case "$(basename -- "$1")" in krona-backup-pull-test.*) return 0 ;; *) return 1 ;; esac
+}
+if ! is_test_root "$test_root"; then
+  printf 'unexpected temporary test directory: %s\n' "$test_root" >&2
+  exit 1
+fi
+mock_bin="$test_root/bin"
+remote_store="$test_root/mock-remote"
+mkdir -p "$mock_bin" "$remote_store"
+cleanup() {
+  resolved_test_root=$(cd -- "$test_root" && pwd -P) || return 1
+  if ! is_test_root "$resolved_test_root"; then
+    printf 'refusing to remove unexpected test directory: %s\n' "$resolved_test_root" >&2
+    return 1
+  fi
+  rm -rf -- "$resolved_test_root"
+}
+trap cleanup EXIT
+
+cat > "$mock_bin/ssh" <<'MOCK_SSH'
+#!/usr/bin/env bash
+set -euo pipefail
+[[ "$#" -eq 5 && "$1" == -o && "$2" == BatchMode=yes && "$3" == -- ]] || exit 61
+printf '%s\n' "$4" "$5" >> "$MOCK_SSH_LOG"
+printf '%s' "$MOCK_REMOTE_LISTING"
+MOCK_SSH
+
+cat > "$mock_bin/scp" <<'MOCK_SCP'
+#!/usr/bin/env bash
+set -euo pipefail
+[[ "$#" -eq 4 && "$1" == -B && "$2" == -- ]] || exit 62
+count=0
+[[ ! -f "$MOCK_SCP_STATE" ]] || read -r count < "$MOCK_SCP_STATE"
+count=$((count + 1))
+printf '%s\n' "$count" > "$MOCK_SCP_STATE"
+if [[ "${MOCK_SCP_FAIL_AT:-0}" == "$count" ]]; then exit 63; fi
+source_name=${3##*/}
+cp -- "$MOCK_REMOTE_STORE/$source_name" "$4"
+MOCK_SCP
+chmod +x "$mock_bin/ssh" "$mock_bin/scp"
+export PATH="$mock_bin:$PATH"
+
+cert="$test_root/recipient.crt"
+key="$test_root/recipient.key"
+/usr/bin/openssl req -x509 -newkey rsa:2048 -nodes -sha256 -days 1 \
+  -keyout "$key" -out "$cert" -subj '/CN=pull-test-recipient' >/dev/null 2>&1
+
+remote_directory=/srv/krona-staff/backups/
+ssh_log="$test_root/ssh.log"
+success_state="$test_root/scp-success.count"
+assertion_failures=0
+
+run_pull() {
+  local remote_dir=$1 local_dir=$2 listing=$3 state=$4
+  shift 4
+  env PATH="$mock_bin:$PATH" \
+    PILOT_SSH_TARGET=pull-test@invalid \
+    PILOT_REMOTE_BACKUP_DIRECTORY="$remote_dir" \
+    BACKUP_LOCAL_DIRECTORY="$local_dir" \
+    BACKUP_MAX_AGE_HOURS=36 \
+    BACKUP_LOCAL_RETENTION_DAYS=90 \
+    MOCK_REMOTE_STORE="$remote_store" \
+    MOCK_REMOTE_LISTING="$listing" \
+    MOCK_SCP_STATE="$state" \
+    MOCK_SSH_LOG="$ssh_log" \
+    "$@" bash "$pull_script"
+}
+
+record_failure() {
+  printf 'FAIL: %s\n' "$*" >&2
+  assertion_failures=$((assertion_failures + 1))
+}
+
+make_backup() {
+  local name=$1
+  printf 'synthetic PostgreSQL backup\n' | /usr/bin/openssl cms -encrypt -binary \
+    -aes-256-cbc -outform DER -stream -in /dev/stdin \
+    -out "$remote_store/$name" "$cert"
+  (cd -- "$remote_store" && sha256sum -- "$name" > "$name.sha256")
+}
+
+assert_rejected_before_ssh() {
+  local label=$1 remote_dir=$2 local_dir=$3 state=$4
+  shift 4
+  rm -f -- "$ssh_log"
+  if run_pull "$remote_dir" "$local_dir" "$fresh_name" "$state" "$@" > "$test_root/reject.log" 2>&1; then
+    record_failure "$label unexpectedly succeeded"
+  elif [[ -e "$ssh_log" ]]; then
+    record_failure "$label reached mocked ssh before rejection"
+  fi
+}
+
+[[ "$(command -v ssh)" == "$mock_bin/ssh" ]] || { echo 'mock ssh is not first in PATH' >&2; exit 1; }
+[[ "$(command -v scp)" == "$mock_bin/scp" ]] || { echo 'mock scp is not first in PATH' >&2; exit 1; }
+bash -n "$pull_script"
+
+fresh_stamp=$(date -u '+%Y%m%dT%H%M%SZ')
+fresh_name="krona-staff-$fresh_stamp-101.dump.cms"
+make_backup "$fresh_name"
+local_success="$test_root/local-success"
+first_output=$(run_pull "$remote_directory" "$local_success" "$fresh_name" "$success_state")
+[[ -f "$local_success/$fresh_name" && -f "$local_success/$fresh_name.sha256" ]] || {
+  record_failure 'fresh CMS and checksum were not installed locally'
+}
+cmp -s "$remote_store/$fresh_name" "$local_success/$fresh_name" || record_failure 'fresh CMS differs from remote bytes'
+(cd -- "$local_success" && sha256sum -c -- "$fresh_name.sha256") >/dev/null || {
+  record_failure 'fresh local checksum does not validate'
+}
+[[ "$first_output" == *'newly_copied=1'* ]] || record_failure 'first pull did not report one new copy'
+[[ "$(cat "$success_state")" == 2 ]] || record_failure 'first pull did not fetch exactly CMS and checksum'
+
+second_output=$(run_pull "$remote_directory" "$local_success" "$fresh_name" "$success_state")
+[[ "$second_output" == *'newly_copied=0'* ]] || record_failure 'repeat pull was not idempotent'
+[[ "$(cat "$success_state")" == 2 ]] || record_failure 'repeat pull issued additional scp calls'
+
+old_stamp=$(date -u -d '91 days ago' '+%Y%m%dT%H%M%SZ')
+old_name="krona-staff-$old_stamp-201.dump.cms"
+recent_stamp=$(date -u -d '10 days ago' '+%Y%m%dT%H%M%SZ')
+recent_name="krona-staff-$recent_stamp-202.dump.cms"
+unverified_name="krona-staff-$old_stamp-203.dump.cms"
+symlink_name="krona-staff-$old_stamp-204.dump.cms"
+make_backup "$old_name"
+make_backup "$recent_name"
+make_backup "$unverified_name"
+make_backup "$symlink_name"
+retention_local="$test_root/local-retention"
+mkdir -p "$retention_local"
+for name in "$old_name" "$recent_name" "$unverified_name"; do
+  cp -- "$remote_store/$name" "$retention_local/$name"
+  cp -- "$remote_store/$name.sha256" "$retention_local/$name.sha256"
+done
+printf 'tampered synthetic bytes\n' >> "$retention_local/$unverified_name"
+printf 'preserve unrelated file\n' > "$retention_local/operator-notes.txt"
+printf 'symlink target\n' > "$test_root/symlink-target.bin"
+ln -s "$test_root/symlink-target.bin" "$retention_local/$symlink_name"
+cp -- "$remote_store/$symlink_name.sha256" "$retention_local/$symlink_name.sha256"
+retention_output=$(run_pull "$remote_directory" "$retention_local" "$fresh_name" "$test_root/scp-retention.count" BACKUP_LOCAL_RETENTION_DAYS=90)
+[[ ! -e "$retention_local/$old_name" && ! -e "$retention_local/$old_name.sha256" ]] ||
+  record_failure 'verified backup older than 90 days was not pruned with its checksum'
+[[ -f "$retention_local/$recent_name" && -f "$retention_local/$recent_name.sha256" ]] ||
+  record_failure 'recent backup was pruned'
+[[ -f "$retention_local/$unverified_name" && -f "$retention_local/$unverified_name.sha256" ]] ||
+  record_failure 'unverified old backup was removed'
+[[ -L "$retention_local/$symlink_name" && -f "$test_root/symlink-target.bin" ]] ||
+  record_failure 'symlink backup or its target was changed'
+[[ -f "$retention_local/$symlink_name.sha256" && -f "$retention_local/operator-notes.txt" ]] ||
+  record_failure 'retention removed a symlink sidecar or unrelated file'
+[[ "$retention_output" == *'retention_pruned=1'* ]] || record_failure 'retention did not report one pruned backup'
+[[ "$retention_output" == *'retention_skipped=1'* ]] || record_failure 'retention did not report the unverified backup'
+
+assert_rejected_before_ssh 'zero retention' "$remote_directory" "$test_root/local-invalid-retention" "$test_root/scp-invalid-retention.count" BACKUP_LOCAL_RETENTION_DAYS=0
+grep -q 'BACKUP_LOCAL_RETENTION_DAYS must be an integer' "$test_root/reject.log" ||
+  record_failure 'invalid retention failed for an unexpected reason'
+assert_rejected_before_ssh 'excessive retention' "$remote_directory" "$test_root/local-excessive-retention" "$test_root/scp-excessive-retention.count" BACKUP_LOCAL_RETENTION_DAYS=36501
+grep -q 'BACKUP_LOCAL_RETENTION_DAYS must be an integer' "$test_root/reject.log" ||
+  record_failure 'excessive retention failed for an unexpected reason'
+
+stale_stamp=$(date -u -d '48 hours ago' '+%Y%m%dT%H%M%SZ')
+stale_name="krona-staff-$stale_stamp-102.dump.cms"
+make_backup "$stale_name"
+local_stale="$test_root/local-stale"
+mkdir -p "$local_stale"
+cp -- "$remote_store/$old_name" "$local_stale/$old_name"
+cp -- "$remote_store/$old_name.sha256" "$local_stale/$old_name.sha256"
+if run_pull "$remote_directory" "$local_stale" "$stale_name" "$test_root/scp-stale.count" > "$test_root/stale.log" 2>&1; then
+  record_failure 'stale backup unexpectedly succeeded'
+elif ! grep -q 'Newest encrypted backup is stale or has a future timestamp' "$test_root/stale.log"; then
+  record_failure 'stale backup failed for an unexpected reason'
+fi
+[[ -f "$local_stale/$old_name" && -f "$local_stale/$old_name.sha256" ]] ||
+  record_failure 'failed stale pull pruned an existing local backup'
+
+bad_checksum_name="krona-staff-$fresh_stamp-103.dump.cms"
+make_backup "$bad_checksum_name"
+printf '%064d  %s\n' 0 "$bad_checksum_name" > "$remote_store/$bad_checksum_name.sha256"
+if run_pull "$remote_directory" "$test_root/local-bad-checksum" "$bad_checksum_name" "$test_root/scp-bad.count" > "$test_root/bad-checksum.log" 2>&1; then
+  record_failure 'checksum mismatch unexpectedly succeeded'
+elif ! grep -q 'Transfer checksum mismatch' "$test_root/bad-checksum.log"; then
+  record_failure 'checksum mismatch failed for an unexpected reason'
+fi
+[[ ! -e "$test_root/local-bad-checksum/$bad_checksum_name" ]] || record_failure 'checksum mismatch left a completed backup'
+
+assert_rejected_before_ssh 'remote root' / "$test_root/local-remote-root" "$test_root/scp-root.count"
+assert_rejected_before_ssh 'remote traversal' /srv/krona-staff/../escape/ "$test_root/local-remote-traversal" "$test_root/scp-traversal.count"
+assert_rejected_before_ssh 'local root' "$remote_directory" / "$test_root/scp-local-root.count"
+local_traversal="$test_root/local-traversal/../local-target"
+assert_rejected_before_ssh 'local traversal' "$remote_directory" "$local_traversal" "$test_root/scp-local-traversal.count"
+local_directory_symlink="$test_root/local-directory-symlink"
+ln -s "$local_success" "$local_directory_symlink"
+assert_rejected_before_ssh 'local directory symlink' "$remote_directory" "$local_directory_symlink" "$test_root/scp-local-symlink.count"
+
+scp_failure_name="krona-staff-$fresh_stamp-104.dump.cms"
+make_backup "$scp_failure_name"
+if run_pull "$remote_directory" "$test_root/local-scp-failure" "$scp_failure_name" "$test_root/scp-failure.count" MOCK_SCP_FAIL_AT=2 > "$test_root/scp-failure.log" 2>&1; then
+  record_failure 'scp failure unexpectedly succeeded'
+fi
+[[ ! -e "$test_root/local-scp-failure/$scp_failure_name" ]] || record_failure 'scp failure left a completed local backup'
+[[ ! -e "$test_root/local-scp-failure/$scp_failure_name.sha256" ]] || record_failure 'scp failure left a completed local checksum'
+
+if (( assertion_failures > 0 )); then
+  printf 'backup pull tests had %s failure(s)\n' "$assertion_failures" >&2
+  exit 1
+fi
+printf 'backup pull tests passed: CMS+checksum, idempotence, stale/mismatch rejection, path validation, scp failure\n'

scripts/test-backup.sh

@@ -0,0 +1,137 @@
+#!/usr/bin/env bash
+set -euo pipefail
+
+repo_root=$(cd -- "$(dirname -- "$0")/.." && pwd)
+backup_script="$repo_root/deploy/pilot/backup.sh"
+test_parent=$(cd -- /tmp && pwd -P)
+test_root=$(mktemp -d "$test_parent/krona-backup-test.XXXXXX")
+test_root=$(cd -- "$test_root" && pwd -P)
+is_test_root() {
+  [ "$(dirname -- "$1")" = "$test_parent" ] || return 1
+  case "$(basename -- "$1")" in krona-backup-test.*) return 0 ;; *) return 1 ;; esac
+}
+if ! is_test_root "$test_root"; then
+  printf 'unexpected temporary test directory: %s\n' "$test_root" >&2
+  exit 1
+fi
+mock_bin="$test_root/bin"
+mkdir -p "$mock_bin"
+cleanup() {
+  resolved_test_root=$(cd -- "$test_root" && pwd -P) || return 1
+  if ! is_test_root "$resolved_test_root"; then
+    printf 'refusing to remove unexpected test directory: %s\n' "$resolved_test_root" >&2
+    return 1
+  fi
+  rm -rf -- "$resolved_test_root"
+}
+trap cleanup EXIT
+
+cat > "$mock_bin/docker" <<'MOCK_DOCKER'
+#!/bin/sh
+set -eu
+if [ "${MOCK_DOCKER_FAIL:-0}" = 1 ]; then
+  printf 'synthetic partial dump\n'
+  exit 42
+fi
+printf 'synthetic PostgreSQL dump\n'
+MOCK_DOCKER
+
+cat > "$mock_bin/scp" <<'MOCK_SCP'
+#!/bin/sh
+set -eu
+printf '%s\n' "$@" > "$MOCK_SCP_LOG"
+if [ "${MOCK_SCP_FAIL:-0}" = 1 ]; then
+  exit 43
+fi
+MOCK_SCP
+
+cat > "$mock_bin/openssl" <<'MOCK_OPENSSL'
+#!/bin/sh
+set -eu
+if [ "${MOCK_OPENSSL_CMS_FAIL:-0}" = 1 ] && [ "${1:-}" = cms ] && [ "${2:-}" = -encrypt ]; then
+  exit 44
+fi
+exec /usr/bin/openssl "$@"
+MOCK_OPENSSL
+chmod +x "$mock_bin/docker" "$mock_bin/scp" "$mock_bin/openssl"
+
+cert="$test_root/recipient.crt"
+key="$test_root/recipient.key"
+/usr/bin/openssl req -x509 -newkey rsa:2048 -nodes -sha256 -days 1 \
+  -keyout "$key" -out "$cert" -subj '/CN=backup-test-recipient' >/dev/null 2>&1
+
+export PATH="$mock_bin:$PATH"
+export BACKUP_RECIPIENT_CERT="$cert"
+export BACKUP_REMOTE='backup@example.invalid:/backup/krona-staff/'
+export MOCK_SCP_LOG="$test_root/scp-args.log"
+
+assert_no_plain_dump() {
+  backup_dir=$1
+  if find "$backup_dir" -type f ! -name '*.dump.cms' ! -name '*.dump.cms.sha256' | grep -q .; then
+    printf 'unexpected non-encrypted backup artifact in %s\n' "$backup_dir" >&2
+    exit 1
+  fi
+}
+
+run_backup() {
+  backup_dir=$1
+  shift
+  mkdir -p "$backup_dir"
+  if env BACKUP_DIRECTORY="$backup_dir" "$@" sh "$backup_script"; then
+    return 0
+  else
+    return $?
+  fi
+}
+
+sh -n "$backup_script"
+
+success_dir="$test_root/success"
+run_backup "$success_dir" env
+encrypted=$(find "$success_dir" -maxdepth 1 -type f -name '*.dump.cms' -print -quit)
+test -n "$encrypted"
+test -s "$encrypted.sha256"
+(cd "$success_dir" && sha256sum -c "$(basename -- "$encrypted").sha256") >/dev/null
+cmp <(printf 'synthetic PostgreSQL dump\n') \
+  <(/usr/bin/openssl cms -decrypt -binary -inform DER -in "$encrypted" -recip "$cert" -inkey "$key")
+assert_no_plain_dump "$success_dir"
+test "$(wc -l < "$MOCK_SCP_LOG")" -eq 4
+
+pull_dir="$test_root/external-pull"
+pull_output=$(run_backup "$pull_dir" env BACKUP_TRANSFER_MODE=external-pull BACKUP_REMOTE=)
+test "$(find "$pull_dir" -maxdepth 1 -type f | wc -l)" -eq 2
+printf '%s\n' "$pull_output" | grep -q '^off_host_copy=pending_pull$'
+assert_no_plain_dump "$pull_dir"
+test "$(wc -l < "$MOCK_SCP_LOG")" -eq 4
+
+assert_failure_cleans_scratch() {
+  backup_dir=$1
+  if find "$backup_dir" -mindepth 1 -print -quit | grep -q .; then
+    printf 'unexpected artifact after failed backup in %s\n' "$backup_dir" >&2
+    exit 1
+  fi
+}
+
+dump_fail_dir="$test_root/pg-dump-failure"
+if run_backup "$dump_fail_dir" env MOCK_DOCKER_FAIL=1; then
+  printf 'backup succeeded after mocked pg_dump failure\n' >&2
+  exit 1
+fi
+assert_failure_cleans_scratch "$dump_fail_dir"
+
+openssl_fail_dir="$test_root/openssl-failure"
+if run_backup "$openssl_fail_dir" env MOCK_OPENSSL_CMS_FAIL=1; then
+  printf 'backup succeeded after mocked CMS encryption failure\n' >&2
+  exit 1
+fi
+assert_failure_cleans_scratch "$openssl_fail_dir"
+
+scp_fail_dir="$test_root/scp-failure"
+if run_backup "$scp_fail_dir" env MOCK_SCP_FAIL=1; then
+  printf 'backup succeeded after mocked scp failure\n' >&2
+  exit 1
+fi
+assert_no_plain_dump "$scp_fail_dir"
+test "$(find "$scp_fail_dir" -maxdepth 1 -type f | wc -l)" -eq 2
+
+printf 'backup tests passed: syntax, CMS round-trip, pg_dump/OpenSSL/scp failures, no plaintext dump on disk\n'