Fediversity

Feature Specification: Component postgresql

Created: 2026-10-07

Status: Implemented

Input: Reconstructed from the code at 093e68b6f (core/components/hosting-provider/postgresql/default.nix, core/components/hosting-provider/pgweb.nix, core/setups/hosts-common.nix, core/resources/oidc/forward-auth-domain.nix, core/checks/nix-unit-tests.nix, core/checks/default.nix).

User Scenarios & Testing (mandatory)

User Story 1 - Run the shared database node (Priority: P1)

The apps of the hosting provider and of the operator deployments need PostgreSQL databases. The postgresql component marks the node that runs the shared PostgreSQL server. It declares provides = [ "postgresql" ]. The setups find the node by this capability. They add the server config, the databases and roles of the consumers and the roster to it (see postgresql and 005).

Why this priority: every app with a postgresql request needs this node.

Independent Test: nix-unit-setups-postgresql-roster holds the two halves of one roster: what the node creates and what the consumers expect. nix-unit-setups-postgresql-render-gate and contracts-postgresql cover the provider. No test boots this component as a whole.

Acceptance Scenarios:

  1. Given a node whose app provides postgresql, When the setups evaluate, Then the node imports postgresql.mainConfig and the postgresql supporting requests of each node in the group.
  2. Given that node in the hosting-provider group, When the setups evaluate, Then the node also gets the operator requests, the roster from postgresqlRoster.nodeConfig and appSubnets with samenet added.
  3. Given a deployment that serves PKI, When the ssl resource renders the leaf of postgresql.<domain>, Then the key is postgres:postgres with mode 0600, and a new leaf restarts postgresql.service and pgweb.service.

User Story 2 - Read the databases in a web UI (Priority: P2)

An administrator wants to look at the tables without a shell. The component requests a forward-auth vhost pgweb.<apex> in front of pgweb on 127.0.0.1:8082. Authelia checks the session first.

Why this priority: it is a convenience for operations. The databases work without it.

Independent Test: nix-unit-setups-served-domains and nix-unit-setups-apex-cohesion include pgweb in the served names. No test opens the UI.

Acceptance Scenarios:

  1. Given the policy sets deployAdminUIs, When the oidc resource applies the pgweb-ui request, Then the vhost pgweb.<apex> asks for one_factor and proxies to 127.0.0.1:8082.
  2. Given a running UI, When an administrator runs a write statement, Then pgweb refuses it, because it runs with --readonly.
  3. Given the UI, When an administrator picks an app database in the sidebar, Then pgweb switches to it, because it runs without --lock-session.

Edge Cases

  • pgweb connects as postgres over 127.0.0.1 with sslmode=disable. It needs the pg_hba rule host 127.0.0.1/32 trust from the postgresql resource. Without that rule, the UI cannot connect.
  • pgweb starts after postgresql.service with Wants=. A boot where PostgreSQL waits for its leaf can exhaust the start limit of pgweb. The leaf restart of pgweb.service recovers it.
  • With deployAdminUIs unset, the resource emits no vhost and no pgweb unit.
  • The node has no backup of the full cluster. Each consumer backs up its own database with a streamingBackup to restic, over its own credential. A database whose consumer adds no backup has no offsite copy.

Requirements (mandatory)

Functional Requirements

  • FR-001: The component MUST declare components.postgresql with package = pkgs.postgresql, the icon from sources.postgresql-pgweb and provides = [ "postgresql" ].
  • FR-002: The implementation MUST request spiffe.enable under the name spiffe, so that the node can get an OpenBao token.
  • FR-003: The implementation MUST request oidc.forwardAuth under the name pgweb-ui with domain = "pgweb.<apex>", upstream = "127.0.0.1:8082", authorizationPolicy = "one_factor" and allowedGroups = [ ], and import pgweb.nix through its nixos-module request. pgweb.nix MUST set the pgweb unit only when fediversity.forwardAuth.domains has an entry for 127.0.0.1:8082.
  • FR-004: pgweb.nix MUST run systemd.services.pgweb as a DynamicUser with --bind=127.0.0.1 --listen=8082 --readonly and Restart=on-failure.
  • FR-005: The component MUST NOT carry the server config, the databases or the roles. The setups MUST add them to the node whose app provides postgresql.
  • FR-006: On a node that serves PKI, the setups MUST request the leaf key as postgres:postgres 0600, and restart postgresql.service and pgweb.service when the leaf renders again.
  • FR-007: On the node of the central group (the one group whose roster provides varsBackend), the setups MUST add the requests of the operator groups, the full roster and samenet in appSubnets.

Key Entities

  • pgweb-ui request: the forward-auth vhost of the read-only UI.
  • PostgreSQL roster: the databases and roles that the node creates for its consumers.

Success Criteria (mandatory)

Measurable Outcomes

  • SC-001: nix-unit-setups-postgresql-roster, nix-unit-setups-postgresql-render-gate and contracts-postgresql pass.
  • SC-002: nix-unit-setups-served-domains and nix-unit-setups-apex-cohesion pass.
  • SC-003: On a live node, https://pgweb.<apex>/ sends a browser without a session to the Authelia portal.

Assumptions

  • Authelia runs in the same deployment (see authelia).
  • The entry point passes the apex as the apex module argument. core/apex.nix reads it from DNS_DOMAIN.

Known Deviations

None.