A data service pattern

A data service pattern

Goal: Run one data service—PostgreSQL by default—declaratively on NixOS: module options, state directories, app role/auth via secrets paths, and a written backup story (even if automation is minimal).

Important

DB passwords go through sops-nix paths, not Nix strings. Examples use placeholders and path references only.

Why this chapter exists

Application servers without state are easy. Real systems need durable data:

Concern	NixOS angle
Package + unit	services.postgresql module
State location	services.postgresql.dataDir / ensure on persistent disk
Auth	peer/scram; password files from secrets
Network exposure	localhost first (firewall discipline)
Backups	Not automatic just because Nix is declarative

Nix rebuilds the software and config; it does not magically version your rows.

---

Theory 1 — One DB only (pattern depth)

Depth > zoo. We use PostgreSQL. If you already run another engine at work, the pattern transfers: module → state dir → credentials → firewall → backups.

---

Theory 2 — Minimal PostgreSQL module

# modules/services/postgres.nix
{ config, pkgs, ... }:
{
  services.postgresql = {
    enable = true;
    package = pkgs.postgresql_16; # pick a version available on 26.05 pin
    ensureDatabases = [ "labapp" ];
    ensureUsers = [
      {
        name = "labapp";
        ensureDBOwnership = true;
      }
    ];
    settings = {
      # listen_addresses = "localhost"; # confirm option name on your version
    };
  };

  # Do NOT open 5432 on the host firewall for world access in the default lab.
}

Verify option names against search.nixos.org for 26.05.

---

Theory 3 — State directories and disk

PostgreSQL data lives outside the Nix store (mutable).

Question	Answer you must know
Where is dataDir?	Document path
Is it on the disk that survives reinstall?	Ops/impermanence later
What happens on rollback of system generation?	Software rolls back; data may not

Impermanence hosts need explicit persistence for DB dirs—note the footgun now.

sudo -u postgres psql -c 'SHOW data_directory;'

---

Theory 4 — Credentials

Patterns:

1. Peer auth for local system users (good for admin)
2. SCRAM password for app users—password from sops at runtime
3. Socket-only access for local apps

Sketch with sops (illustrative—align with module capabilities):

sops.secrets."postgres/labapp_password" = {
  owner = "postgres";
  mode = "0400";
};

# Prefer module-supported passwordFile style options when available.
# If you script role passwords, run a oneshot that reads the secret path
# and does not print it.

Never:

# BAD
# services.postgresql.ensureUsers = [{ name = "labapp"; password = "secret"; }];

---

Theory 5 — App connection story

App config should receive:

• Host: 127.0.0.1 or socket
• DB name: labapp
• User: labapp
• Password: from env file populated at activation from sops

Your TLS front door proxy does not replace DB auth. Backend talks to DB on localhost; users talk to proxy on 443.

---

Theory 6 — Backup thought (required cognitive work)

Even a one-node lab needs a plan:

Element	Example
What	pg_dump of labapp
When	nightly timer (system or user)
Where	/var/backups/postgres/ on another disk or remote
Encryption	restic/age to remote (secrets via sops)
Restore test	once per quarter / before capstone

sudo -u postgres pg_dump labapp > /tmp/labapp-dump.sql
# store off-box in real life; scrub /tmp later

---

Theory 7 — Upgrades

Postgres major upgrades are not “change package attr and forget.” They need dump/restore or pg_upgrade procedures. Pin a major version intentionally; schedule upgrades.

---

Theory 8 — ensureDatabases vs migrations

ensureDatabases / ensureUsers create empty roles/DBs. Application schema is not Nix’s job by default.

Layer	Owner
Package + unit + listen policy	NixOS module
Roles/DBs existence	ensure* options / oneshots
Schema / migrations	App tooling (flyway, django, …)
Data	Backups

---

Theory 9 — Socket vs TCP local connections

Postgres often exposes a Unix socket under /run/postgresql. Local admin via sudo -u postgres psql uses peer auth. Apps may use:

host=127.0.0.1 port=5432
# or host=/run/postgresql

Pick one, document in docs/data-service.md, match app secret env.

---

Worked example — Service + docs

modules/services/postgres.nix
docs/data-service.md
  - version
  - dataDir
  - DB/user names
  - auth method
  - backup sketch
  - restore sketch

---

Worked example — Timer sketch for dumps (optional)

{ pkgs, ... }:
{
  systemd.services.labapp-pg-dump = {
    serviceConfig = {
      Type = "oneshot";
      User = "postgres";
      ExecStart = "${pkgs.bash}/bin/bash -c '${pkgs.postgresql}/bin/pg_dump labapp | ${pkgs.gzip}/bin/gzip -c > /var/backups/labapp.sql.gz'";
    };
  };
  # + timer + StateDirectory/tmpfiles for /var/backups
}

Wire carefully (permissions, disk space). Even a commented sketch in the flake beats “we’ll back up later.”

---

Exercises

Exercise 1 — Enable PostgreSQL

Add module; rebuild; verify unit:

sudo systemctl status postgresql
sudo -u postgres psql -c '\l'

Exercise 2 — Database and role

Confirm labapp DB and user via ensure* options. Connect:

sudo -u postgres psql -d labapp -c 'SELECT 1;'

Exercise 3 — Wire a secret password path

Create sops secret placeholder for app password. Configure the safest supported method for your module version. Prove app user can authenticate without password living in the flake as plaintext.

Exercise 4 — Network posture

ss -tulpn | grep 5432 || true

Confirm listening on localhost (or document intentional LAN bind). Ensure firewall does not expose 5432 publicly.

Update docs/ports.md.

Exercise 5 — Manual dump

sudo -u postgres pg_dump labapp | head

Write restore commands in docs/data-service.md.

Exercise 6 — Failure narrative

Answer:

1. System generation rollback after a bad app deploy—does data revert?
2. Disk failure—what is lost without backups?
3. Secret rotation steps for DB password

Exercise 7 — Schema independence proof

sudo -u postgres psql -d labapp -c "CREATE TABLE lab_demo(id int);"
sudo -u postgres psql -d labapp -c '\dt'
sudo nixos-rebuild switch --flake .#lab
sudo -u postgres psql -d labapp -c '\dt'

Table should still exist—state ≠ generation.

Exercise 8 — Document restore dry-run

# restore sketch
# sudo -u postgres psql -d labapp < labapp-dump.sql

Exercise 9 — dataDir documentation

sudo -u postgres psql -c 'SHOW data_directory;'

Record in docs.

Exercise 10 — Peer auth admin path

Document how you admin without password in git (sudo -u postgres psql).

Exercise 11 — Package pin note

Write which postgresql_* package attr you chose and why you will not casual-bump majors.

Exercise 12 — Backup location permissions

sudo mkdir -p /var/backups/postgres
# ownership suitable for dump user

Exercise 13 — App connection string template

Write a template with path reference to password file—not the password.

Exercise 14 — ports.md final for DB

Confirm 5432 is localhost-only in docs and reality.

---

Common gotchas

Symptom / mistake	What to do
Data in store myth	Data is on mutable disk
Open 5432 to world	Bind localhost; close firewall
Password in nix	sops path / peer auth
Major version bump casual	Plan dump/restore
No backup thought	Exercises 5–6 required
Proxying Postgres over HTTP	Wrong layer; don’t
ensure* expected to migrate schema	Use app migrations
Rollback “restored” DB	Generations ≠ data
Dump committed with secrets	Scrub; don’t commit dumps

---

Checkpoint

• PostgreSQL enabled declaratively
• App DB + user exist
• Auth without plaintext secret in git
• Localhost exposure default
• Manual dump performed once
• Backup/restore story written
• ports.md updated
• Schema-vs-generation understood

Journal (optional)

Write three personal gotchas before continuing.

---