Fediversity

Feature Specification: Resource postgresql

Created: 2026-10-07

Status: Implemented

Input: Reconstructed from the code at 093e68b6f (core/resources/postgresql/default.nix, core/resources/postgresql/postgresql-provider.nix, core/resources/postgresql/wait-password-file.sh, core/resources/postgresql/converge-test.nix, core/setups/wiring/postgresql-roster.nix, core/setups/wiring/resource-routing.nix, core/setups/hosts-common.nix, core/checks/integrations/postgresql/), pull requests #1184, #1238 and #1392, and issues #527 and #1205.

User Scenarios & Testing (mandatory)

User Story 1 - Give an application a database and a rotated credential (Priority: P1)

A component needs a PostgreSQL database. It states a request with a database, a consumerName and a username. The policy routes the request to a PostgreSQL provider. The provider creates the database and the role. OpenBao rotates the password of the role. The OpenBao agent on the consumer node renders the password and a DSN to files. The client module of the component reads the paths of these files from contracts.postgresql.results.<app>.default.

Why this priority: most applications of core store their data in PostgreSQL. Without this resource, they do not start.

Independent Test: contracts-postgresql (a NixOS test of the provider in core/checks/integrations/postgresql/postgres-behavior.nix) and contracts-postgresql-rotation.

Acceptance Scenarios:

  1. Given a routed request in rotating mode, When the deployment is evaluated, Then the consumer node gets two openbao-agent templates from postgresql/static-creds/<username>: the password at /run/vars/secret/<username>-db/password and the DSN at /run/vars/secret/<username>-db/url, where <username> is the namespaced role name.
  2. Given a routed request in dynamic mode, When the deployment is evaluated, Then the consumer node gets one DSN template from postgresql/creds/<username>.
  3. Given OpenBao rotates a password, When the agent renders the new file, Then the agent runs systemctl reset-failed and systemctl restart --no-block on the reloadUnits of the request.
  4. Given a request with urlEnvironment, When the node starts, Then the oneshot <consumerName>-db-env writes <variable>=<DSN> to /run/<consumerName>-db-env/env, and each unit in urlEnvironment.units reads it as an EnvironmentFile.

User Story 2 - Give each deployment of a group its own database (Priority: P1)

A hosting provider deploys one operator group many times, once per tenant. Each deployment must get its own database and role. The policy prepends the group with namespacePrefix and appends the deployment with namespaceSuffix. The shared provider creates these databases through the OpenTofu graph (provisioning = "tf"), so a destroy drops them again.

Why this priority: without a per-deployment name, two deployments of one group share one database.

Independent Test: nix-unit-resources-postgresql-converge checks the names and the converge commands for the netbox, windmill, mastodon and peertube requests. nix-unit-setups-postgresql-roster checks the roster that the provider and the OpenBao engine read.

Acceptance Scenarios:

  1. Given namespacePrefix = "<group>" and namespaceSuffix = "<deployment>", When a request asks for database x, Then the database and the role are <group>-x-<deployment>.
  2. Given a non-empty namespaceSuffix and provisioning = "in-nix", When the deployment is evaluated, Then the evaluation fails, because the provider node drops a database only when its own group deploys, so the database outlives the deployment.
  3. Given provisioning = "tf" and a request with credentialMode = "dynamic", When the deployment is evaluated, Then the evaluation fails, because the tf path does not create the owner role of the lease.

User Story 3 - Run the PostgreSQL daemon on a provider node (Priority: P2)

A group with its own postgresql node runs the daemon itself (provisioning = "in-nix"). The provider module reconciles the databases and the roles with OpenTofu (postgresql-provision), sets the admin password, and accepts TLS connections from the private subnets. The reconciled objects are the options services.postgresql.runtime.roles and .databases, typed from the provider schema of the declarative-runtime pairing.

Why this priority: the development groups and the hosting provider run their own PostgreSQL node.

Independent Test: contracts-postgresql, integrations-postgresql-provision.

Acceptance Scenarios:

  1. Given a node that provides postgresql, When it is built, Then it runs the daemon with enableTCPIP, opens the port in the firewall, and takes its certificate from contracts.ssl.results.fediversity.<sslDomain>.
  2. Given TLS is on, When an application connects from an address in appSubnets, Then pg_hba accepts it with hostssl and scram-sha-256.
  3. Given an empty admin password file, When postgresql-set-passwords runs, Then the unit fails and restarts after 10 seconds.
  4. Given a database or a role in the roster that exists but is not in the state of postgresql-provision, When the unit runs, Then it adopts the object with an import block and keeps its data.
  5. Given a database or a role that leaves the roster, When postgresql-provision runs, Then it drops the database with its data, then the role.
  6. Given an engine that rotated a password or an app whose bootstrapSQL granted a membership or set a role setting, When postgresql-provision runs again, Then it changes nothing.
  7. Given a state with one for_each resource per type, from before each object had its own resource, When postgresql-provision runs, Then its moved blocks move each object to its own resource, and it changes no object.

User Story 4 - Hold a unit until its credential exists (Priority: P3)

A unit that reads the password must not start before the agent renders it. The request lists such units in gateUnits.

Why this priority: a unit that starts too early fails, and can burn its start limit.

Independent Test: no check covers gateUnits alone.

Acceptance Scenarios:

  1. Given a unit in gateUnits, When it starts, Then an ExecStartPre runs wait-password-file.sh, which waits up to 60 attempts of 1 second for each credential file to be non-empty.

Edge Cases

  • No provider is routed for the request: the resource emits no consumer configuration and no provider want.
  • bootstrapSQL or groupRoles on a tf want: postgresql-roster.nix fails the evaluation, because the OpenTofu graph does not run the SQL, and the provider node does not converge on the database and the login role of the want.
  • Two requests declare one group role with different bypassRowLevelSecurity: postgresql-roster.nix fails the evaluation. Requests that agree share the role, and their memberships and privileges merge.
  • A group role has the name of a login role or of an owner role: the evaluation of the provider node fails.
  • A dynamic request has no fixed login role. The engine grants the grantToUser groups to each lease role, through dynamicRoleGrants.
  • bootstrapSQL runs on every start of postgresql-app-bootstrap-sql. It must be idempotent.
  • dynamicOwnerRole names a role that bootstrapSQL also creates: postgresql-provision creates the role first and resets the attributes that bootstrapSQL sets.
  • An object that left the roster before the first run of postgresql-provision is in neither the config nor the state, so it is not adopted and not dropped.
  • The admin role is not in the config of postgresql-provision: ensureUsers creates it and postgresql-set-passwords sets its password. The per-lease roles of dynamic requests are the engine's.
  • The resource prepends namespacePrefix to consumerName, but keeps the unprefixed name as consumer. consumer is the app node name, so the per-host grant of the SPIFFE host/<name> role keys on it.

Requirements (mandatory)

Functional Requirements

  • FR-001: The request MUST accept enable, database, consumerName, username (default consumerName), passwordFileOwner (default root), passwordFileMode (default 0640), credentialMode, reloadUnits, urlEnvironment, bootstrapSQL, groupRoles, dynamicRoleGrants, dynamicOwnerRole (default <username>_owner) and gateUnits. The request MUST contain only data, so that it can be written to JSON.
  • FR-002: credentialMode MUST be rotating or dynamic, with rotating as the default.
  • FR-003: The policy MUST accept enable, domain, host (default domain), port (default 5432), sslMode (default require), namespacePrefix, namespaceSuffix, provisioning (in-nix or tf, default in-nix) and routing.
  • FR-004: The resource MUST name the database and the role <namespacePrefix>-<name>-<namespaceSuffix>, where an empty prefix or suffix adds nothing.
  • FR-005: The evaluation MUST fail when namespaceSuffix is set without provisioning = "tf", and when provisioning = "tf" meets a dynamic request.
  • FR-006: The resource MUST emit consumer configuration only for a request that the routing gives a provider.
  • FR-007: A rotating consumer MUST read postgresql/static-creds/<username>, and a dynamic consumer MUST read postgresql/creds/<username>.
  • FR-008: The DSN MUST have the form postgres://<user>:<password>@<host>:<port>/<database>?sslmode=<sslMode>.
  • FR-009: A re-render of a credential MUST reset and restart the units that read it, without a block on the agent.
  • FR-010: The <consumerName>-db-env oneshot MUST restart on failure every 10 seconds with no start limit, and MUST run before the units in urlEnvironment.units.
  • FR-011: Under provisioning = "in-nix", the resource MUST put a want on contracts.postgresql.want.<prefixed consumerName>.default of the provider node.
  • FR-012: The resource MUST publish a provider roster entry per request with role, ownerRole, provisioning, roleStem, database, consumer, credentialMode, bootstrapSQL, groupRoles and dynamicRoleGrants.
  • FR-013: The provider node MUST generate its admin password with openssl rand -hex 32 into the postgresql-admin vars instance (owner postgres, mode 0440).
  • FR-014: The provider MUST only accept remote connections over TLS with scram-sha-256, from the subnets in appSubnets.
  • FR-015: The OpenBao engine mount MUST be postgresql, with a rotation period of 24 hours.
  • FR-016: Under provisioning = "in-nix", the provider node MUST create each database, each fixed login role, each dynamic owner role and each group role with postgresql-provision, through services.postgresql.runtime.{roles,databases,grants,grant_roles}, and a login role MUST own its database when the names match. On a dynamic database, it MUST grant CREATE and USAGE on the public schema to public, and CONNECT, CREATE and TEMPORARY to the owner role. It MUST grant the databasePrivileges of a group role on the database of each request that declares it, each memberOf group to the group role, and each grantToUser group to the login role of a rotating request.
  • FR-017: postgresql-provision MUST adopt the declared objects that exist, MUST drop the objects that leave its config, and MUST NOT manage role passwords, the memberships that it does not declare, or the attributes that the engine and bootstrapSQL set.
  • FR-018: The resource MUST give each routed request to the consumer node through the postgresql contract: the bridge of the cross-node evaluation sets contracts.postgresql.instances.<consumer>.default to the resolved request and result, so contracts.postgresql.results.<consumer>.default holds the result. The request fields that only the consumer side reads (username, credentialMode, the file owner and mode, the reload and gate units, urlEnvironment) MUST be at fediversity.postgresqlConsumers.<consumer>. The static module consumer.nix MUST render the templates, the <consumer>-db-env oneshot and the start gate from these two values only. The values MUST contain no store paths and no functions, so that they can be written to JSON.
  • FR-019: The setups MUST give the full roster, the requests of the operator groups and the engine roles only to the provider node of the central group (the one group whose roster provides varsBackend). In a group with no route for postgresql, host MUST be the FQDN of the provider node of the central group.

Key Entities

  • Request: what one consumer needs: database, role, credential mode, files and units.
  • Provider roster: the list of all routed requests, read by the provider node (dynamicRoles, dynamicOwners, bootstrapDbSQL, groupRoles, groupMemberships, groupDatabasePrivileges) and by the OpenBao engine (roles, consumerRoles). See postgresql-roster.nix.
  • Resolved request: the data that consumer.nix renders on the consumer node: consumer, username, credentialMode, passwordFileOwner, passwordFileMode, reloadUnits, gateUnits, urlEnvironment and the contract result.
  • Credential files: /run/vars/secret/<username>-db/password and /run/vars/secret/<username>-db/url (namespaced username), rendered by the openbao-agent default instance.
  • Provisioning mode: in-nix (the provider node creates the objects) or tf (the OpenTofu graph creates them on a shared node).

Success Criteria (mandatory)

Measurable Outcomes

  • SC-001: nix-unit-resources-postgresql-converge, nix-unit-setups-postgresql-roster, nix-unit-setups-resource-routing and nix-unit-resources-plain-requests pass.
  • SC-002: contracts-postgresql, contracts-postgresql-rotation and integrations-postgresql-provision pass.
  • SC-003: Two deployments of one group get two different databases on the shared provider.

Assumptions

  • The generic flow of a request through a policy is in the data model. The node selection and the routing table are in setups and wiring.
  • The OpenBao postgresql engine and its roles come from the OpenBao component. This resource only supplies the roster.
  • The credential delivery uses the openbao-agent default instance that generateFiles configures.

Known Deviations

  • A TODO in core/resources/postgresql/default.nix says that dynamic mode does not cover a consumer with a fixed user, which needs a connection pooler.
  • A FIXME in core/resources/default.nix asks which more of the resources cross-node contracts can carry.