#527 Harden packaging and deployment: Windows folders, database roles, provenance, services

[Anonymous]

Originally created by: athanclark

Closes [#499], closes [#502], closes [#511], closes [#513], closes [#515], closes [#516], closes [#517], closes [#518], closes [#519], closes [#520]. Refs [#501] (left as a follow-up, see there).

Windows server:

• The data folder (%ProgramData%\OpenChronology):
• Ownership and access: each run, setup makes the folder the administrators' and replaces its access list in one protected step, with nothing inherited or left over.
• What setup trusts: only items owned by administrators, SYSTEM or the installing account. Anything else, or any link or junction, stops setup with a message. -TrustExistingData takes everything over once an administrator has checked it.
• Each file: every managed file has its access list replaced on each run. initdb's password file moves out of %TEMP%.
• An install folder outside Program Files: it gets administrators/SYSTEM full control and read/execute for the rest. A drive root is refused.

Database roles:

• Docker Compose: a one-shot database-setup service, run as the superuser before each migration, creates pgmp and a non-superuser openchronology_app role that owns the database. Migrations, the app, seeding and the mail worker connect as that role; existing databases are handed over to it.
• ⚠ Upgrading Compose installs: add APP_DATABASE_PASSWORD to .env. Compose refuses to start without it.
• Homebrew: setup creates a non-superuser role that owns the database for new installs.

NixOS: the module's virtual host sets its own proxy headers with recommendedProxySettings off for its location, so one X-Forwarded-For reaches the server. The NixOS test turns the global setting on.

Releases:

• Provenance: a new attest job attests every release asset, SHA256SUMS.txt included. It uses actions/attest-build-provenance, pinned to v3.0.0's commit, with id-token/attestations: write only on that job.
• Verifying a download: docs/licensing.md explains gh attestation verify.
• AUR: its pgmp checksum is the pinned one.

Hardening:

• Docker:
• the app image's files are root-owned, writable only in .next/cache;
• persist-credentials: false on CI checkouts.
• systemd: the units get capability, device, kernel, namespace, address-family and system-call restrictions, with UMask=0077.
• journald: the follower takes JOURNALD_KNOWN_HOSTS for strict host keys.
• FreeBSD: the pid directory is root-owned.
• Docs:
• a note to blank ADMIN_INITIAL_PASSWORD after first sign-in;
• a firewall note for the syslog receiver on NixOS.

Not changed: separate Unix accounts for the log receivers. That needs a new user in every package, and upgrade scripts that find and regroup the operator's chosen files, so it's left as a follow-up.

Verified:

• Database setup: database-setup.sh ran against PostgreSQL 16 with pgmp. The role isn't a superuser; it owns everything but pgmp's objects; schema.sql, trigger toggles and pgmp arithmetic work as it; COPY … TO PROGRAM is denied.
• Configuration: docker compose config resolves.
• Nix: the module evaluates with the expected settings.
• systemd: units verified, and the services' operations tried under the new restrictions.
• Unit tests: journald, CI workflows, releases and help pass.
• Not run here: PowerShell, the Docker image builds, the NixOS VM test, and Windows and Homebrew CI. CI runs them.

🤖 Generated with Claude Code

https://claude.ai/code/session_011fFY8eruEi1xAkkMBni13B

[Anonymous]

Ticket changed by: athanclark

• status: open --> closed