Managed databases

Pushleft treats PostgreSQL and ClickHouse as environment resources, not release roles. A new application release reuses the environment’s database instance. Teardown can retain the data path independently of the database container.

The managed host image installs and patches Docker Engine, XFS tooling, the node agent, and its systemd unit. Database jobs never install operating-system packages or execute package-manager commands. “Install PostgreSQL/ClickHouse” means that the agent reconciles a reviewed, digest-pinned engine image and its durable storage on a pre-enrolled host; this keeps the remote job protocol typed and prevents it from becoming a general-purpose root shell.

Implemented lifecycle

The control plane stores database instances, logical databases, and principals under organization-scoped RLS. Creating a deployment with data.postgres or data.clickhouse creates or updates the durable instance record. The database APIs are idempotent:

• GET /v1/database-instances
• GET /v1/database-instances/{id}/catalogs
• POST /v1/database-instances/{id}/catalogs
• GET /v1/database-instances/{id}/principals
• POST /v1/database-instances/{id}/principals

Only vault:// credential references are stored. The node task is a closed DatabaseTask enum with strict identifiers; there is no raw SQL or shell field.

On a host, pushleft-agent manage-database can currently:

• pull and start a SHA-256 digest-pinned PostgreSQL or ClickHouse image;
• accept images only from the configured engine-specific repository (official repositories by default, configurable for a private mirror);
• enforce CPU, memory, PID, capability, network, and data-root boundaries, with a read-only container root filesystem;
• enforce the purchased persistent-disk size with an XFS project quota on managed Linux nodes;
• keep ClickHouse configuration and server logs inside that quota-backed tree and rotate Docker stdout/stderr logs;
• reconcile only containers carrying Pushleft ownership and specification-hash labels;
• start an existing matching instance, but reject silent image/resource drift;
• create a logical database and PostgreSQL owner;
• create or reconcile a user with connect, read, write, and/or migrate grants, revoking grants that are no longer in desired state;
• run an engine-native health check;
• retain data by default or remove only the exact validated data path from a path-bound task;
• archive PostgreSQL WAL continuously, take verified base backups, restore to a point in time into a new generation, and run restore drills (see below);
• take ClickHouse native backups and verify them by restoring into throwaway databases.

The local maintenance command implements credential rotation and confined removal for recovery/testing. The online lease loop currently rejects both operations: dropping a future is not a database-level fence, so Pushleft will not claim that a destructive or credential-changing command stopped merely because its lease expired.

The command remains an operational/testing entry point. In normal operation, pushleft-agent run receives the same typed task through the short-lived mTLS lease protocol:

pushleft-agent manage-database \
  --task-file /run/pushleft/task.json \
  --vault-address https://vault.internal \
  --vault-token-file /run/pushleft/vault-token

The Vault token file must be mode 0600 or stricter. HTTPS is mandatory except for a loopback Vault Agent/OpenBao proxy. References use organization- and environment-bound KV v2 paths such as vault://organizations/<org>/environments/<environment>/applications/invoice#password; a missing fragment defaults to the value field. Per-execution wrapped Vault credentials remain required before shared production use.

Managed database nodes dedicate an XFS filesystem mounted with project-quota accounting and enforcement to the configured data root. Every database instance receives a durable, globally unique project ID. Before starting the container, the agent assigns the directory tree, applies the plan’s GiB limit, checks every inode’s project assignment, and reads the resulting soft and hard limits back exactly. It fails closed if XFS quota accounting or enforcement is unavailable or the report differs. The local one-shot command used by integration tests may opt out explicitly; the online lease loop cannot. The scheduler selects and revalidates only nodes advertising database_disk_quota: xfs_project.

The shipped systemd unit will not start unless /var/lib/pushleft/data is a distinct mount point. The agent then verifies XFS project-quota accounting and enforcement before it accepts any lease. The environment file contains only certificate, key, token-file paths and internal service origins; it must never contain an admin password or Vault token value. See deploy/systemd/agent.env.example.

Production and integration-test runtime images are selected from deploy/images.lock.env. PostgreSQL, ClickHouse, Tailscale and Headscale are pinned by multi-architecture manifest digest. scripts/runtime-image-scan.sh, called by npm run security, fetches and scans both linux/amd64 and linux/arm64 remotely; a locally cached image cannot substitute for the other architecture. Every run gets a fresh artifact directory under output/security/runtime-images/, with per-platform JSON and CycloneDX SBOMs derived from that same inventory. The gate validates identity, platform and package inventory and fails on HIGH/CRITICAL findings, failed/incomplete scans, or invalid SBOMs. Scanner environment/configuration filters, VEX and ignore inputs are not inherited. npm run test:security-policy exercises failure and ambient-configuration regressions without network access. Updating a database version is an explicit reviewed change, not a tag moving underneath a deployment.

The official ClickHouse 26.9.19.9 Alpine variant is an unpromoted candidate. The selected Ubuntu variant has a HIGH finding in libssl3. Both Alpine architecture scans report no HIGH/CRITICAL findings in their APK inventory, but that inventory does not establish vulnerability coverage of components copied outside APK (including glibc and ClickHouse itself). Do not treat this smaller inventory as a clean replacement or bypass the release gate. A provisional lockfile change was reverted during local review; no live workloads changed. Supplemental component provenance/security evidence is required before promotion.

The real agent management suite passed against the Alpine candidate. npm run test:clickhouse-image is a separate compatibility check using a fixed original Ubuntu baseline and disposable, networkless host-bind containers. It verifies root/file-secret bootstrap, non-root server UID/GID, zero effective/permitted/ambient capabilities, NoNewPrivs, no published ports, persisted Unicode/JSON data, RBAC and 90-day TTL across Ubuntu → candidate → Ubuntu, including candidate restart and writes. Both architectures were exercised locally; native Linux execution remains required. This is same-version variant compatibility, not security approval, backup/PITR, replication or major-version upgrade proof. No vulnerability exceptions were added. Existing managed instances are not automatically replaced.

Run npm run test:clickhouse-image -- linux/amd64 or -- linux/arm64 to select a supported architecture explicitly. The default matches the Docker worker architecture. PUSHLEFT_CLICKHOUSE_CANDIDATE_IMAGE can select another local/registry test candidate; it never alters the production lockfile. The baseline remains vulnerable and is isolated only for these tests; production release remains blocked rather than claiming that the selected image is safe.

deploy/images/clickhouse-libssl.Dockerfile provides the preferred unpromoted patch candidate: retain the pinned Ubuntu image and update only its signed distro libssl3 package to exact version 3.0.2-0ubuntu1.30. Both local architecture builds retain 111 inventoried packages and have no HIGH/CRITICAL findings in their Trivy reports. Compatibility tests on both architectures additionally require byte-identical /usr/bin/clickhouse and /entrypoint.sh. The strengthened fixture authenticates the bootstrap file password, rejects wrong/empty passwords, verifies effective reader SELECT and denied INSERT/DDL, and compares records captured before restart. It attests UID/GID 101 and zero capabilities/NoNewPrivs for PID 1 and ClickHouse processes. This candidate is not published or selected by images.lock.env; registry provenance, remote per-platform scans/SBOMs and promotion review remain required.

docker build --platform linux/arm64 -f deploy/images/clickhouse-libssl.Dockerfile -t pushleft-clickhouse-libssl-candidate:arm64 .
PUSHLEFT_CLICKHOUSE_CANDIDATE_IMAGE=pushleft-clickhouse-libssl-candidate:arm64 PUSHLEFT_CLICKHOUSE_REQUIRE_IDENTICAL_BINARIES=1 npm run test:clickhouse-image -- linux/arm64

Repeat for amd64. Its expiring, path-only DS002/static-analysis root-bootstrap exception has the same bounded-capability/file-secret rationale and residual bootstrap risk as the PostgreSQL candidate below. The upstream entrypoint must drop daemon privileges; no untrusted init scripts or extra mounts may be added. This exception does not suppress package findings or authorize promotion. The release scanner now requires versioned architecture-correct dpkg records for ClickHouse server/client/common-static, libc6 and libssl3 plus the engine executable in the package inventory; APK-only scans cannot pass by omitting copied components.

For promotion evidence, also set PUSHLEFT_CLICKHOUSE_SCAN_REPORT to the matching per-architecture Trivy JSON. The fixture then requires its scanned image ID/platform to match the actual candidate container. Successful output includes a JSON evidence record with baseline/candidate immutable IDs and actual selected platform manifests, engine/entrypoint SHA-256 hashes, platform, engine version, the scan-report SHA-256 and whether strict byte-identity and scan binding were enabled. Optimized Python execution is rejected before any runtime action, so assertions cannot silently disappear. It requires a Docker API exposing container ImageManifestDescriptor; an ambiguous index-default architecture is not accepted. A compatibility log without those bindings is not sufficient promotion evidence.

deploy/images/postgres-gosu.Dockerfile is an unpromoted security candidate. It keeps the pinned PostgreSQL 17.11 base and entrypoint, rebuilding only upstream gosu 1.19 from checksum-pinned source with digest-pinned Go 1.27.2. It does not change images.lock.env, publish a registry image, or automatically upgrade existing instances. Local candidate builds have separate reports in output/security/. To reproduce a candidate build and compatibility test for an architecture:

docker build --platform linux/arm64 -f deploy/images/postgres-gosu.Dockerfile -t pushleft-postgres-gosu-candidate:arm64 .
python3 scripts/fixtures/postgres-image-probes.py "$PWD" pushleft-postgres-gosu-candidate:arm64 linux/arm64

Repeat for linux/amd64 with the corresponding tag. The fixture uses only uniquely scoped disposable host-bind data/password files and networkless containers. It verifies root-owned PGDATA and root-only file-secret bootstrap, the final TCP postmaster (not initdb’s temporary socket server), all PostgreSQL process UID/GID values, zero effective/permitted/ambient capabilities and NoNewPrivs=1 before/after restart, unchanged engine version, and Unicode/JSON/index/grant/identity dump-restore. It then removes its own resources. This is not backup/PITR, online migration or HA verification. Promotion still requires a registry-hosted immutable multi-platform digest, agent-managed XFS integration, upgrade compatibility, and all release scans. Existing instance specification fences must not be bypassed to install it.

The candidate has narrowly scoped Trivy DS002 and Opengrep last-user-is-root exceptions on its USER root bootstrap instruction, reviewed through 2026-11-15; the Trivy exception expires automatically. This preserves the existing upstream entrypoint’s ownership initialization for agent-created root-owned host binds and root-only file secrets; the daemon/workers must subsequently run non-root with the constraints above. These exceptions do not suppress package vulnerabilities. Residual risk remains during root bootstrap: compromised bootstrap code could modify its mounted data using the bounded ownership/UID capabilities. No untrusted init scripts, extra mounts or command overrides may be added to that path. The exceptions are not image-promotion approval and must be re-reviewed or removed before expiry.

Image promotion evidence (2026-10-17)

No digest was promoted; deploy/images.lock.env is unchanged. Scans used Trivy 0.72.0 with the release policy of scripts/runtime-image-scan.sh (remote, per platform, full inventory, no ignore file). Reports are in output/security/runtime-images/ and output/security/candidates/ (local, ignored by git).

The two local candidates are clean, but promotion still needs immutable registry publication with provenance, the remote per-platform gate run against the published digest, and the live agent suites, which take only registry digests. A local tag must not go into the lock file. No Trivy exception was added for any package vulnerability. The findings are accepted, time-boxed to 2026-11-16, in deploy/security/image-risk-register.json (decision log only, not read by the scanner).

The release pipeline must additionally run npm run test:linux-database-quota as root on a disposable Linux worker with XFS tooling and systemd. It creates only a temporary loopback XFS filesystem and proves exact limit readback, project inheritance, kernel EDQUOT, reconciliation replay, quota-off refusal, and unit-file verification. This gate cannot run on the macOS development host and is intentionally separate from test:all.

Security model

Bootstrap passwords are written atomically to root-only files and mounted read-only at /run/secrets/admin_password. Containers use a read-only root filesystem, bounded tmpfs mounts, a rotating local Docker log driver, and drop the Docker default capability set before regaining only CHOWN, DAC_OVERRIDE, FOWNER, SETGID, and SETUID for official-image initialization. PostgreSQL administration uses the container-local Unix socket. PostgreSQL database-owner and login-principal roles have disjoint ownership markers, and both the API and agent reject cross-catalog owner/login reuse. ClickHouse receives a no-password maintenance identity restricted to 127.0.0.1 and ::1; it is reachable only through docker exec, which is already equivalent to host root. User passwords are escaped into fixed engine-specific SQL templates and sent on stdin. Docker arguments, task JSON, logs, labels, and the control-plane database contain no secret values.

Database containers do not publish ports. They join the environment’s private container bridge; the namespaced mesh gateway is intended to route that bridge only inside the environment workload tailnet. Database placement and agent leasing enforce selected-host attachment and firewall-generation gates. The agent can apply and verify environment nftables rules, but Headscale provisioning, host route publication, host-wide input/provider policy, and recurring attachment verification still need to be connected before production admission can open.

Placement and lifecycle state

pushleft-scheduler converts an ensure_database_service intent into a validated, digest-pinned DatabaseTask::EnsureInstance only after reserving an eligible node’s CPU, memory, and persistent disk. The task carries both the disk limit and its durable project-quota ID, so placement accounting and host enforcement describe the same resource. Catalog and principal operations remain untargeted until the instance reports ready, then follow its active node. Successful or terminal agent results update the corresponding instance, catalog, or principal state in the same transaction as the append-only result.

The initial plans are deliberately bounded: dev, starter, standard, and performance. Manifests reject other names. Fleet exhaustion is retryable and does not mark the service failed.

HA requests currently fail closed at placement. Pushleft will not create two unrelated writable databases and call that HA; replication, a third-failure-domain quorum witness and writer fencing must land before this gate is opened. Backups and PITR (below) do not change that.

Backups, PITR and restore drills

Every managed instance gets a private archive at PUSHLEFT_DATABASE_BACKUP_ROOT/<organization>/<instance>/ (default /var/lib/pushleft/backups). Every level is a non-symlink 0700 directory created with the same rules as the source store. Only one subdirectory is mounted into the engine container: wal/ for PostgreSQL and base/ for ClickHouse. The agent assigns it to the engine user inside the container (chown through docker exec), so the image’s UID mapping holds. The instance specification hash now carries runtime_policy: 4. A container created before this change reports specification drift and needs the explicit upgrade workflow. Pushleft never swaps it silently.

WAL archiving. PostgreSQL runs with archive_mode=on. Its archive_command copies each segment, fsyncs it and renames it into wal/. If a segment of the same name is already there and is identical, the command succeeds. If it differs, the command fails and PostgreSQL keeps retrying, so an archived segment is never overwritten. archive_timeout=300 bounds the recovery point to five minutes on an idle server. For a lower RPO, add pg_receivewal streaming.

Base backups. The backup task inserts a marker row (public.pushleft_backup_marker in the postgres database). It then streams pg_basebackup --format tar --wal-method fetch --manifest-checksums SHA256 through the agent into base/<YYYYMMDDTHHMMSSZ>/base.tar. The container never gets write access to base backups. The agent reads the tar back and checks every data file against PostgreSQL’s own SHA-256 backup_manifest. PostgreSQL 17 pg_verifybackup cannot read tar format, so this read-back takes its place. The agent then writes manifest.json last and atomically. It holds the file size and SHA-256, the start and end LSN, the first needed WAL segment and the server version. A base directory without that manifest is an interrupted attempt. It is discarded and never restored.

Restore and PITR. The restore task is PostgreSQL-only. It always creates a new generation <instance>_g<N> (N from 2 to 9999) with its own data path, quota project, secret directory, archive and container. It never restores in place. It selects the newest base that ends before the target, or a named backup_id. It recomputes every file hash, unpacks the base (regular files and directories only, so tablespaces are unsupported), and writes recovery.signal. It then appends restore_command (reading the source wal/, mounted read-only), the target (recovery_target_time, recovery_target_lsn, or none for latest) and recovery_target_action = 'promote'. The agent waits until recovery has finished and the server has promoted. It proves an authenticated read and reports the new timeline and the stop point from the timeline history file, for example before 2026-10-16 22:33:43+00 (timeline 1 switched at 0/6000000). If PostgreSQL reports that recovery ended before the target, the agent removes the crash-looping container, keeps the data for inspection and fails. A replay never unpacks over a generation that has already promoted.

The source generation keeps running and keeps its writers. This slice does not cut over. Relocation and write fencing remain closed: moving clients to the restored generation is a later relocation task or an explicit operator action. Restores are requested by an operator: a restore task through the agent’s typed job, or pushleft-agent manage-database on the node. No public API or CLI requests a restore, and no control-plane allocator chooses the generation number or quota ID yet.

Restore drills. The verify_backup task restores the latest base into a throwaway PostgreSQL container: networkless, read-only root, the same capability set, and the source container’s exact image. It waits for consistency, runs pg_amcheck --all --heapallindexed, requires sum(checksum_failures) = 0, and requires the base’s own marker row. Data checksums are only as strong as the cluster’s initdb setting; PostgreSQL 17 leaves them off by default. The drill always removes the container and its scratch directory. For ClickHouse, the drill runs RESTORE DATABASE ... AS pushleft_verify_<nonce>_<n> for every backed-up database on the live instance. It counts the rows, requires the marker row in pushleft_backup.marker and drops the throwaway databases. pushleft_backup and pushleft_verify* are reserved catalog names.

ClickHouse backups. A backups local disk is configured on the per-instance base/ directory through a read-only config.d file. The backup task runs BACKUP DATABASE ... TO Disk('backups', '<id>/data') for every non-system database. The agent then hashes every file ClickHouse wrote into manifest.json. Retention rules are the same as for PostgreSQL, without WAL.

Retention. The agent keeps every base started within PUSHLEFT_DATABASE_BACKUP_RETENTION_DAYS (default 14). It always keeps the newest base, and all WAL from the oldest kept base onward. Timeline .history and .partial files are never pruned. The agent prunes after each backup. It deletes only after re-verifying every file hash of the oldest base that will remain. A single base is never pruned, however old.

Schedule and evidence. The scheduler enqueues one backup per ready instance per day and one verify_backup per committed base. Both are idempotency-keyed and then placed through the normal readiness and network-admission path. The agent returns a typed DatabaseBackupReport (no secrets or host paths). The full report stays in the append-only agent_job_results table. The gateway also keeps a fenced summary on database_instances (migration 0030): last_backup_id, last_backup_at, last_backup_bytes, backup_verification (passed or failed), backup_verified_id and backup_verified_at. A result from an older instance generation is ignored.

Encryption at rest. Backups are not encrypted by the agent in this slice. WAL segments and ClickHouse backups are written by the engine itself, so the agent could seal only part of the archive. The store relies on the private 0700 root and host disk encryption, and the root should be a separate filesystem. Object-store encryption arrives with the adapter: a per-organization key, using the ring AEAD already in the lockfile.

What is proven where. npm run test:database-backup (scripts/database-backup-e2e.sh) runs the real agent and the pinned engine images on Docker Desktop. The run covers these checks:

• private store modes and archived WAL;
• the manifest hash;
• PITR into generation 2: rows written before the target are kept, and a row written after it is absent while the source still has it;
• read-only source WAL and a converging replay;
• both engines’ drills, with no leftover containers or databases;
• refusal of a tampered archive;
• retention pruning of a backdated base and exactly the WAL only it needed;
• the volume lifecycle (below).

It also runs the migrated scheduler and gateway durability tests on a disposable database. Quotas are disabled in that run. XFS enforcement for restored generations and volumes uses the same code path as npm run test:linux-database-quota, which still has to run on native Linux. The object-store adapter, write-once retention, and off-host copies do not exist yet. A host loss currently loses its backups too.

Application volumes

A role may declare volumes: in .pl.yaml:

roles:
  web:
    port: 8080
    volumes: [{ name: uploads, size: 20GiB, mount: /data }]          # the one writer
  worker:
    volumes: [{ name: uploads, mount: /uploads, read_only: true }]   # same environment, read-only

pushleft-core validates this. Each volume has exactly one read-write role, and that role runs exactly one replica. Readers declare no size and refer to a writer in the same manifest. Names containing :, / or . are refused as cross-environment references. Sizes run from 1GiB to 1TiB and are given in GiB or TiB. Mounts must be normalized absolute paths outside /proc, /sys, /dev, /etc, /run and /tmp. Only web, private and worker roles may mount volumes. The older manifest-level data.volumes map is still parsed into the plan but is not executed.

The scheduler binds a volume to the host of its writer’s first placement. It reserves disk as a fleet_allocations row of kind volume with zero CPU and memory, records the volume in application_volumes with a quota ID from the shared XFS project sequence, and queues a targeted manage_volume ensure job. Readers wait for the writer and then run on the same host. An instance is targeted only once all its volumes are ready and network admission holds. The agent creates PUSHLEFT_APPLICATION_DATA_ROOT/<organization>/<environment>/<name> (default /var/lib/pushleft/data/applications, on the XFS data mount) with 0700 parents, an ownership marker and an XFS project quota. The data directory itself is 0777, because the image user is unknown, and is reachable only through those parents. The application executor mounts exactly the declared volumes: read-write for the writer, readonly for readers. It labels each container with com.pushleft.volume.<name>, and refuses to start if a volume is not provisioned for that tenant and environment.

Teardown removes containers, never volumes. A volume that no live instance declares gets a retention window (PUSHLEFT_VOLUME_RETENTION_DAYS, default 30). An operator can end the window early with SELECT release_application_volume(org, volume); this is refused while the volume is in use. When the window ends, a release job refuses if a running container still mounts the volume. Otherwise it takes a final backup, removes the directory and releases the disk reservation. A nightly backup job writes volume.tar plus its SHA-256 to the same backup store (key volume-<environment>-<name>) under the same retention rules.

Known limits:

• During a rolling release, the previous writer keeps its read-write mount until it is drained, so two writers briefly overlap. A stop-before-start strategy for volume writers is not implemented.
• The tar of a live writer is crash-consistent per file, not a filesystem snapshot.
• Volume restore is a manual untar.
• Volumes never migrate between hosts, and resizing is refused.

Required before production

The following remain fail-closed or missing, and are not advertised as working:

• automated node enrollment and certificate issuance/rotation;
• an object-store backup adapter (S3/R2) with encryption, object lock and off-host copies; today backups share the host’s fate;
• a control-plane restore request (API/CLI, generation and quota allocation) and relocation cutover with writer fencing;
• replica bootstrap, quorum/witness policy and promotion fencing (promote_replica still returns an explicit error);
• major-version upgrade plans with compatibility checks and rollback, and the upgrade workflow for pre-runtime_policy: 4 containers;
• storage capacity alerts, IOPS accounting, automatic disk expansion and volume resize/migration;
• successful execution of the quota-enabled Linux host release gate, including restored generations and volumes (live pinned-engine integration tests are already scheduled in CI).