#!/usr/bin/env python3 """Real PostgreSQL dump/restore plus synthetic local attachment files. Python 3.10+. SPDX-License-Identifier: MIT Only a new, isolated Docker container is touched. No remote target is accepted. """ from datetime import datetime, timezone import hashlib import json from pathlib import Path import platform import secrets import shutil import subprocess import sys import tempfile import time IMAGE = 'postgres:18.6-trixie@sha256:86c951e05bf56c93d95d397747fb8820ac76cc3bedb78f43abd83eedbe3666ae' def docker(*args, data=None, check=True): result = subprocess.run(['docker', *args], input=data, text=True, capture_output=True, timeout=90) if check and result.returncode: raise RuntimeError(f'Docker {args[0]} failed: {result.stderr.strip()}') return result def main(): if len(sys.argv) != 1: raise SystemExit('Usage: python3 run.py > results.json; isolated local fixture only') if docker('image', 'inspect', IMAGE, check=False).returncode: raise SystemExit('Pull the pinned image in README.md first; no automatic downloads') name = 'obhut-restore-example-' + secrets.token_hex(6) container = None checks = [] def check(label, condition, **evidence): if not condition: raise RuntimeError(label) checks.append({'case': label, 'passed': True, **evidence}) try: container = docker('run', '--detach', '--rm', '--network', 'none', '--name', name, '--tmpfs', '/var/lib/postgresql', '--env', 'POSTGRES_PASSWORD=' + secrets.token_hex(24), IMAGE).stdout.strip() deadline = time.monotonic() + 60 while docker('exec', container, 'pg_isready', '-h', '127.0.0.1', '-U', 'postgres', check=False).returncode: if time.monotonic() >= deadline: raise RuntimeError('PostgreSQL startup guard expired') time.sleep(0.1) # Setup readiness only; no assertion is retried. def sql(database, query): return docker('exec', '-i', container, 'psql', '-X', '-qAt', '-v', 'ON_ERROR_STOP=1', '-U', 'postgres', '-d', database, data=query).stdout.strip() docker('exec', container, 'createdb', '-U', 'postgres', 'source_crm') # Cluster roles are explicit prerequisites; a database dump is not a role backup. sql('postgres', 'CREATE ROLE fixture_owner NOLOGIN; CREATE ROLE fixture_reader NOLOGIN;') with tempfile.TemporaryDirectory(prefix='obhut-restore-') as directory: root = Path(directory) live, saved, restored = root / 'live', root / 'backup', root / 'restored' live.mkdir(); restored.mkdir() objects = {'alpha-note.txt': b'Alpha: synthetische Notiz\n', 'beta-note.txt': b'Beta: synthetische Notiz\n'} manifest = {key: hashlib.sha256(value).hexdigest() for key, value in objects.items()} for key, value in objects.items(): (live / key).write_bytes(value) sql('source_crm', ''' CREATE SCHEMA crm AUTHORIZATION fixture_owner; SET ROLE fixture_owner; CREATE TABLE crm.contacts (id text PRIMARY KEY, org text NOT NULL); CREATE TABLE crm.attachments (object_key text PRIMARY KEY, contact_id text NOT NULL REFERENCES crm.contacts(id), sha256 text NOT NULL); ALTER TABLE crm.contacts ENABLE ROW LEVEL SECURITY; ALTER TABLE crm.contacts FORCE ROW LEVEL SECURITY; CREATE POLICY tenant_read ON crm.contacts FOR SELECT TO fixture_reader USING (org = current_setting('fixture.org', true)); GRANT USAGE ON SCHEMA crm TO fixture_reader; GRANT SELECT ON crm.contacts TO fixture_reader; RESET ROLE; INSERT INTO crm.contacts VALUES ('a1', 'alpha'), ('b1', 'beta'); ''') # These values are fixed synthetic fixture values, not user input. for key, contact in [('alpha-note.txt', 'a1'), ('beta-note.txt', 'b1')]: sql('source_crm', f"INSERT INTO crm.attachments VALUES ('{key}', '{contact}', '{manifest[key]}');") # Fixture is quiescent: no application writer can change DB/files between captures. docker('exec', container, 'pg_dump', '-U', 'postgres', '-Fc', '-f', '/tmp/crm.dump', 'source_crm') shutil.copytree(live, saved) (root / 'manifest.json').write_text(json.dumps(manifest, sort_keys=True)) # Change source after the recovery point; restore must not silently acquire this row. sql('source_crm', "INSERT INTO crm.contacts VALUES ('a2-after-backup', 'alpha'); DELETE FROM crm.attachments WHERE contact_id = 'a1'; DELETE FROM crm.contacts WHERE id = 'a1';") (live / 'alpha-note.txt').unlink() check('source has changed after the backup', sql('source_crm', "SELECT count(*) FROM crm.contacts WHERE id = 'a1';") == '0') started = time.perf_counter() docker('exec', container, 'createdb', '-U', 'postgres', 'restored_crm') docker('exec', container, 'pg_restore', '-U', 'postgres', '--exit-on-error', '-d', 'restored_crm', '/tmp/crm.dump') check('two contacts restored', sql('restored_crm', 'SELECT count(*) FROM crm.contacts;') == '2') rows = sql('restored_crm', 'SELECT object_key, sha256 FROM crm.attachments ORDER BY object_key;') expected = dict(row.split('|') for row in rows.splitlines()) check('attachment metadata restored', expected == manifest) def errors(): return [key for key, digest in expected.items() if not (restored / key).is_file() or hashlib.sha256((restored / key).read_bytes()).hexdigest() != digest] missing = errors() check('database-only recovery is incomplete', missing == sorted(objects), missing_objects=missing) for key in expected: shutil.copy2(saved / key, restored / key) check('all attachment hashes match after file restore', errors() == [], object_sha256=expected) elapsed = round(time.perf_counter() - started, 4) check('post-backup row is absent', sql('restored_crm', "SELECT count(*) FROM crm.contacts WHERE id = 'a2-after-backup';") == '0') check('file relationships remain intact', sql('restored_crm', 'SELECT count(*) FROM crm.attachments a JOIN crm.contacts c ON c.id = a.contact_id;') == '2') check('RLS and FORCE RLS restored', sql('restored_crm', "SELECT relrowsecurity AND relforcerowsecurity FROM pg_class WHERE oid = 'crm.contacts'::regclass;") == 't') check('alpha reader sees only alpha', sql('restored_crm', "SET ROLE fixture_reader; SET fixture.org = 'alpha'; SELECT string_agg(id, ',') FROM crm.contacts;") == 'a1') check('beta reader sees only beta', sql('restored_crm', "SET ROLE fixture_reader; SET fixture.org = 'beta'; SELECT string_agg(id, ',') FROM crm.contacts;") == 'b1') check('reader without organization sees no rows', sql('restored_crm', "SET ROLE fixture_reader; SELECT count(*) FROM crm.contacts;") == '0') check('reader has no write grant', sql('restored_crm', "SELECT has_table_privilege('fixture_reader', 'crm.contacts', 'INSERT');") == 'f') (restored / 'alpha-note.txt').write_bytes(b'Changed after restore') check('corrupted attachment is detected', errors() == ['alpha-note.txt']) shutil.copy2(saved / 'alpha-note.txt', restored / 'alpha-note.txt') check('repaired attachment passes again', errors() == []) print(json.dumps({ 'recorded_at': datetime.now(timezone.utc).isoformat(), 'python': platform.python_version(), 'postgres': sql('postgres', 'SHOW server_version;'), 'image': IMAGE, 'scope': 'Local PostgreSQL, source and fresh target database in one isolated container; local files stand in for object storage; static fixture.org setting is not authentication.', 'technical_restore_seconds': elapsed, 'timing_scope': 'From target database creation through database restore, missing-file check, file copy and hash validation; excludes incident response, final permission tests and application restart. Not an RTO promise.', 'source_sha256': hashlib.sha256(Path(__file__).read_bytes()).hexdigest(), 'passed': len(checks), 'checks': checks, }, indent=2)) finally: if container: docker('rm', '--force', container, check=False) if __name__ == '__main__': main()