Fediversity

Feature Specification: Resource postgresql

Created: 2026-10-07

Status: Implemented

Input: Reconstructed from the code of core/resources/postgresql/, the setup wiring and the PostgreSQL integration tests at 093e68b6f, pull requests #1184, #1238 and #1392, and issues #527 and #1205.

User Scenarios & Testing (mandatory)

The postgresql resource gives an application a PostgreSQL database and a credential that OpenBao rotates. It wraps the postgresql contract. A provider creates the database and the roles. The OpenBao postgresql engine manages the passwords, and the OpenBao agent on the consumer node renders them. The shared resource rules are in the resource family spec.

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

A component needs a PostgreSQL database. It requests a database under its consumer name, with a role name. The routing of the policy selects a provider, and the provider creates the database and the role. The agent on the consumer node renders the credential to files. The client module of the component reads the locations of these files from the contract result. Units that read the credential restart when it changes. A component that reads the connection string from its environment gets it in an environment file.

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

Independent Test: a VM test of the provider contract, and a VM test of the credential rotation.

Acceptance Scenarios:

  1. Given a routed request in rotating mode, When the deployment is evaluated, Then the consumer node renders a password file and a connection string file from the static credential of the namespaced role.
  2. Given a routed request in dynamic mode, When the deployment is evaluated, Then the consumer node renders only a connection string file, from a lease.
  3. Given OpenBao rotates a password, When the agent renders the new file, Then the reload units of the request restart, and the agent does not wait for them.
  4. Given a request that names units that read the connection string from the environment, When the node starts, Then these units start after the environment file exists, and read it.
  5. Given a unit that the request gates on the credential, When it starts, Then it waits until each credential file is non-empty, and fails after a bounded wait.

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 puts the group name before, and the deployment name after, each name. The shared provider creates these databases through the OpenTofu graph of the deployment, so a destroy drops them again.

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

Independent Test: a unit test of the names and the reconcile plan for the netbox, windmill, mastodon and peertube requests, and a unit test of the roster that the provider and the OpenBao engine read.

Acceptance Scenarios:

  1. Given a group prefix and a deployment suffix, When a request asks for a database, Then the database and the role carry the group, the name and the deployment.
  2. Given a deployment suffix and provisioning on the provider node, When the deployment is evaluated, Then the evaluation fails. The reason: the provider node drops a database only when its own group deploys, so the database outlives the deployment.
  3. Given provisioning through OpenTofu and a dynamic request, When the deployment is evaluated, Then the evaluation fails, because the OpenTofu 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. The node reconciles the databases and the roles with OpenTofu, sets the admin password, and accepts TLS connections from the private subnets.

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

Independent Test: the VM test of the provider contract, and a VM test of the reconciliation.

Acceptance Scenarios:

  1. Given a node that provides postgresql, When it is built, Then it listens on the network, opens its port in the firewall, and takes its certificate from the ssl contract.
  2. Given TLS is on, When an application connects from a private subnet, Then the provider accepts it over TLS with SCRAM authentication.
  3. Given an empty admin password file, When the node sets the admin password, Then the step fails and retries after a short delay.
  4. Given a database or a role in the roster that exists but that the reconciler does not track yet, When the reconciler runs, Then it adopts the object and keeps its data.
  5. Given a database or a role that leaves the roster, When the reconciler runs, Then it drops the database with its data, then the role.
  6. Given an engine that rotated a password, or bootstrap SQL that granted a membership or set a role setting, When the reconciler runs again, Then it changes nothing.
  7. Given a reconciler state in an older layout, When the reconciler runs, Then it moves each object to the new layout and changes no object.

Edge Cases

  • No provider is routed for the request: the resource emits no consumer configuration and no want on a provider.
  • Bootstrap SQL or group roles on a request that OpenTofu provisions: the evaluation fails, because the OpenTofu graph does not run the SQL, and the provider node does not reconcile that database and its login role.
  • Two requests declare one group role with different row-level-security bypass: the evaluation fails. 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 requested groups to each lease role.
  • Bootstrap SQL runs on every start of its step. It must be idempotent.
  • The dynamic owner role is also created by bootstrap SQL: the reconciler creates the role first and resets the attributes that the bootstrap SQL sets.
  • An object that left the roster before the first run of the reconciler is in neither its config nor its state. The reconciler does not adopt it and does not drop it.
  • The reconciler does not manage the admin role. The daemon creates it, and a separate step sets its password. The per-lease roles of dynamic requests belong to the engine.
  • The resource prefixes the consumer name with the group, but keeps the unprefixed name as the consumer. The unprefixed name is the node name of the application, so the per-host SPIFFE grant keys on it.

Requirements (mandatory)

Functional Requirements

  • FR-001: The request MUST accept the database, the consumer name, the role name (by default the consumer name), the owner and the mode of the credential files, the credential mode, the reload units, the units that read the connection string from the environment, bootstrap SQL, group roles, the groups to grant to dynamic lease roles, the dynamic owner role, and the gated units. The request MUST contain only data, so that it can be serialized to JSON.
  • FR-002: The credential mode MUST be rotating (the default) or dynamic. A consumer that puts a fixed user in its configuration MUST use rotating mode. Dynamic mode is only for a consumer that reads only the connection string, because a lease changes the username and dynamic mode renders no password file. windmill and lldap read only the connection string but use rotating mode: the agent keeps no lease across its own restarts, so in dynamic mode each start of the agent mints a new lease and restarts the consumer, in addition to a new lease at the maximum lease lifetime of one day. In rotating mode, a start of the agent renders the same password and restarts nothing. A restart of windmill stops its running jobs, and a restart of lldap stops the directory for a short time.
  • FR-003: The policy MUST accept the host, the port, the TLS mode of the client, the group prefix, the deployment suffix, the provisioning mode (on the provider node, the default, or through OpenTofu) and the routing.
  • FR-004: The resource MUST name the database and the role from the group prefix, the requested name and the deployment suffix. An empty prefix or suffix MUST add nothing.
  • FR-005: The evaluation MUST fail when a deployment suffix is set without OpenTofu provisioning, and when OpenTofu provisioning 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 the static credential of its role, and a dynamic consumer MUST read a lease of its role.
  • FR-008: The connection string MUST hold the user, the password, the host, the port, the database and the TLS mode of the policy.
  • FR-009: A new render of a credential MUST reset and restart the units that read it, without a block on the agent.
  • FR-010: The step that writes the environment file MUST retry on failure without a start limit, and MUST finish before the units that read the file start.
  • FR-011: Under provisioning on the provider node, the resource MUST put a want for the prefixed consumer on the postgresql contract of the provider node.
  • FR-012: The resource MUST publish a roster entry per request with the role, the owner role, the provisioning mode, the role stem, the database, the consumer, the credential mode, the bootstrap SQL, the group roles and the dynamic role grants.
  • FR-013: The provider node MUST generate a random admin password that only the PostgreSQL service can read.
  • FR-014: The provider MUST accept remote connections only over TLS with SCRAM authentication, and only from the private subnets of the applications.
  • FR-015: The OpenBao engine MUST rotate the static credentials once per day.
  • FR-016: Under provisioning on the provider node, the reconciler MUST create each database, each fixed login role, each dynamic owner role and each group role. A login role MUST own its database when the names match. On a dynamic database, it MUST let all roles create objects in and use the public schema, and MUST let the owner role connect, create and use temporary tables. It MUST grant the database privileges of a group role on the database of each request that declares it, each parent group to the group role, and each requested group to the login role of a rotating request.
  • FR-017: The reconciler MUST adopt the declared objects that exist, and MUST drop the objects that leave its config. It MUST NOT manage role passwords, memberships that it does not declare, or attributes that the engine and the bootstrap SQL set.
  • FR-018: The resource MUST give each routed request to the consumer node through the postgresql contract, by the cross-node evaluation, so that the contract result on the consumer node holds the result. The request fields that only the consumer side reads (role name, credential mode, file owner and mode, reload and gated units, environment units) MUST reach the consumer node next to the contract result. A static consumer module MUST render the credential templates, the environment file and the start gate from these two values only. The values MUST contain no store paths and no functions, so that they can be serialized 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 the vars backend). In a group with no route for postgresql, the host MUST be 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. The provider node reads it for its roles, owners, bootstrap SQL, group roles, memberships and privileges. The OpenBao engine reads it for its roles.
  • Resolved request: the data that the static consumer module renders on the consumer node: the consumer, the role name, the credential mode, the file owner and mode, the reload and gated units, the environment units, and the contract result.
  • Credential files: the password file and the connection string file of the namespaced role, rendered by the shared OpenBao agent of the node.
  • Provisioning mode: on the provider node (the node creates the objects) or through OpenTofu (the deployment program creates them on a shared node).

Success Criteria (mandatory)

Measurable Outcomes

  • SC-001: the unit tests of the reconcile plan, the roster, the routing and the data-only requests pass.
  • SC-002: the VM tests of the provider contract, the rotation and the reconciliation 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 shared OpenBao agent that generateFiles configures.

Known Deviations

None.