Stand up multi-tenant PostgreSQL on chicken (issue #4) #6
rpickler
Loading…
Add table
Add a link
Reference in a new issue
No description provided.
Delete branch "postgres-chicken"
Deleting a branch is permanent. Although the deleted branch may continue to exist for a short time before it actually gets removed, it CANNOT be undone in most cases. Continue?
Implements issue #4: moves PostgreSQL to bare metal (chicken), multi-tenant, with credential injection into the cluster and restic backups.
What's added
modules/postgres.nix— newpickler.postgresmodule:services.postgresqlenabled withenableTCPIP, scram-sha-256 for TCP fromallowedSourceNetworks(defaults:192.168.97.0/24local +10.0.0.0/24k8s), unix peer auth locally.pickler.postgres.tenants.<name>with per-tenant role/database andkubernetesNamespacestargets.postgres-<tenant>-password);pickler-postgres-setup.serviceensures role/db + applies the password via postgres peer auth;pickler-postgres-k8s-sync.servicepushes<tenant>-postgresSecrets (password,urikeys) into each namespace withkubectl create --dry-run | apply(idempotent), per your kubectl-at-startup suggestion — plus apickler-postgres-reconcilesystemd timer (default 12h) so key rotation is automatic: change SOPS, the timer re-applies on the DB and re-pushes into the cluster.services.restic.backups.postgres:backupPrepareCommandrunspg_dumpall --globals-only+pg_dump -Fcper tenant into/var/backups/postgres, restic uploads and prunes (--keep-daily 7 --keep-weekly 5 --keep-monthly 12); nixpkgs' restic module has nativecommand/backupPrepareCommandsupport, so no custom units.flake.nix— module added to thechickensystem.hosts/chicken.nix—pickler.postgres.enable = true+ restic repository pointing at the rest: server on pi4 (repo pathchicken-postgres; passphraserestic-passphrase, already used by the restic server / home backups).Follow-ups (deliberately not guessed)
tenants.<name>entry per database you move over; the assertion in the module will nag until the repository/tenants pair is consistent.pickler.postgres.kubernetes.kubeconfigSecretto a SOPS key holding a kubeconfig scoped to Secret-management in the tenant namespaces (ServiceAccount + Role/RoleBinding bound to it is enough;kubernetes.addonManager.bootstrapAddonsdoesn't do secret injection).backup:htpasswd user; if you'd rather keep credentials out ofnixosgit, I can switch toenvironmentFile+ SOPS.sslmode=disablein the generated URIs — fine on the LAN/k8s overlay, flag it if you want certs.⚠️ Not evaluated: no
nix flake checkrun in this environment (no nix available). Needs onenix build .#nixosConfigurations.chicken.config.system.build.toplevel(or your auto-upgrade pipeline) on a machine with nix before deploying.@hermes-bot How do I make a secret for kubeconfig in this case? I've never made a service that needs to talk to the kube api before.
I've also pushed a secret for Authentik, as that will be the first thing we re-target to this database
@ -26,0 +29,4 @@# restic server on pi4 (modules/postgres.nix).pickler.postgres = {enable = true;backup.repository = "rest:http://backup:UnicycleResignedClash@pi4:8000/chicken-postgres";Please find a way to use the restic-passphrase secret in this url. It should be 'rest:http://backup:@pi4:8000/chicken-postgres'
If the url needs to become a constant in our nix implementation to do so, that's fine. This is unlikely to change any time soon across my whole fleet.
@ -26,0 +32,4 @@backup.repository = "rest:http://backup:UnicycleResignedClash@pi4:8000/chicken-postgres";# Add a SOPS key `postgres-<tenant>-password` (plaintext password) per# tenant, then declare it:# tenants.grafana.kubernetesNamespaces = [ "grafana" ];Start with 'authentik'
@ -0,0 +244,4 @@sops.secrets.restic-passphrase = { };sops.secrets =(lib.listToAttrs (map (name: lib.nameValuePair (pwSecret' name) { })pwSecret' does not exist. I think this is a minor hallucination that you've repeated elsewhere.
@ -0,0 +251,4 @@];sops.templates = lib.listToAttrs (lib.mapAttrsToList (name: t:lib.nameValuePair (connTpl' name) {Undefined variable connTpl'
Check https://noogle.dev/ for nix function reference documentation
- chicken: build the restic URL from the restic-passphrase SOPS secret via a placeholder rendered through an sops template at boot; the URL (which embeds the passphrase) reaches restic via repositoryFile and never lands in the Nix store. - chicken: declare the authentik tenant (kubernetesNamespaces). - postgres module: pwSecret/connTpl interpolated as literal strings (''${...}) instead of evaluating; call them correctly (they were referenced as pwSecret'/connTpl' in several places). - postgres module: tenant passwords resolved from the nested SOPS key postgresql-tenants/<tenant> (sops-nix addresses nested keys with /). - postgres module: KUBECONFIG env referenced a literal secret path. - postgres module: compose sops.secrets/tenants as sets (listToAttrs ++ optionals was a type error).Implemented all four review findings in
c0bc771:hosts/chicken.nix): the passphrase is no longer hardcoded. The URL is nowrest:http://backup:<placeholder>@pi4:8000/chicken-postgresbuilt fromconfig.sops.placeholder.restic-passphrase, rendered at boot via an sops template and handed to restic throughrepositoryFile, so the final URL never lands in the Nix store. pi4 host +chicken-postgresrepo unchanged, as agreed.hosts/chicken.nix):tenants.authentik.kubernetesNamespaces = [ "authentik" ].pwSecret'/connTpl'(modules/postgres.nix): root cause was the helpers being defined with''${...}escaping — they returned literal${cfg.passwordSecretPrefix}...strings instead of evaluating, and several call sites then referenced the non-existent primed names. Helpers now interpolate properly, all call sites use them, and the primed names are gone.Related fixes found while implementing the above:
postgresql-tenants:→authentik:) viakey = "postgresql-tenants/<tenant>"— sops-nix addresses nested keys with/(verified against the pinned sops-nixsops-install-secrets,recurseSecretKey), so the key you pushed works as-is.environment.KUBECONFIGhad the same literal-''${...}bug — fixed.sops.secrets/sops.templatescomposition usedlistToAttrs ... ++ optionals(type error —listToAttrsreturns a set and++wants lists) — fixed with proper set composition.Both changed files verified with the real tree-sitter Nix grammar (0 parse errors; a control file with an injected error confirms the checker detects them). A full
nix evalof the chicken system is still pending — no Nix toolchain in my sandbox — so anixos-rebuild dry-build(ornix eval .#chicken.config.systemd.services) on chicken is worth doing before merge.Also: still open from the discussion above — the Kubernetes credential sync only activates once
pickler.postgres.kubernetes.kubeconfigSecretpoints at a SOPS key holding a kubeconfig. Happy to walk through creating a least-privilege ServiceAccount for that when you're ready.Replace the string-fragment header soup (''..'' + "\\n" + '') with a single indented-string bash preamble defining apply_password and ensure_tenant once, plus a per-tenant one-line invocation generated by concatMapStrings. Fixes: - od -An -N12 was invalid coreutils syntax; od exited non-zero and pipefail killed the whole setup run. Tag is now head -c16 /dev/urandom | od -An -tx1 | tr -d '[:space:]' (pipefail safe, verified against real coreutils). - Trailing whitespace/newlines in the secret file are no longer baked into the role password: cat $2 strips them via command substitution. - Bash vars are bare $name so they can never collide with ''''''''''''''''''''''${...} Nix interpolation inside the indented string.sops-nix defines config.sops.placeholder behind 'mkIf (config.sops.templates != {})', so evaluating any placeholder forces the templates attribute set. The chicken host config defines pickler.postgres.backup.repository by interpolating config.sops.placeholder.restic-passphrase; the template guard 'cfg.backup.enable && cfg.backup.repository != ""' sat on the templates spine, forcing the repository string while the templates set was being computed — placeholder -> templates -> repository -> placeholder, forever. The guard is now placeholder-free (cfg.backup.enable only, a plain bool). The element content stays lazily evaluated: listToAttrs forces only the name part of each pair, and the URL reaches the store only at activation via the sops template. Unset repository is still caught by the existing backup assertion for the tenants case.systemd.services.*.script and services.restic.backups.*.backupPrepareCommand are typed as strings, not paths; pass the writeShellScript derivations via interpolation (\${setupScript}\) so eval accepts them.lib.concatMapStrings with a single argument is a partial application ('concatMapStrings sep f') and evaluates to a function — hence 'cannot coerce a function to a string'. mapAttrsToList already yields a list of strings, so concatStrings is the right combinator.textClosureList resolves every deps name strictly via predefined.${dep}; a name no module registers aborts eval with 'attribute missing'. 'networking' is a legacy dep name that no longer exists in this nixpkgs (checked config/networking.nix at the pinned revision; 'users' and 'groups' still exist but order nothing we care about). The snippet only restarts units guarded on postgresql.service being active, so it needs no ordering against other activation snippets at all.Post-assessment (full write-up in issue #4):
nix eval .#nixosConfigurations.chicken.config.system.build.toplevel.drvPathnow passes clean on this branch — full chicken system, exit 0, module assertions included.One bug fixed today (
d31c05c):systemctlExe = lib.getExe pkgs.systemdresolved tobin/systemd(the init binary — nometa.mainProgram, so getExe falls back to the package name). The activation guard and the reconcileExecStartwould have exec'd the wrong program, so credential reconcile would silently never run. Nowlib.getExe' pkgs.systemd "systemctl"; re-eval clean and the activation script verified to execbin/systemctl.All 4 review findings were already fixed (see the Oct 16 comment). This review still formally sits at REQUEST_CHANGES — ready for re-review. Remaining checklist (kubeconfig ServiceAccount recipe, first switch on chicken, authentik re-target) is in my issue #4 comment.
Instead of trying to inject the secrets into k8s, why not directly support sops secrets in k8s using something like this?
https://github.com/isindir/sops-secrets-operator
Adopted this direction and implemented the NixOS side in
d55f2b8.What changed on this branch
pickler.postgres.kubernetes.kubeconfigSecret, thepickler-postgres-k8s-syncunit, thekubectl create secret | applyscript, and the per-tenantkubernetesNamespaces/secretNameoptions.hosts/chicken.nixnow just declarestenants.authentik = { }.kubeconfigSecretwas never set on chicken, so the injection service was dormant (anyK8s= false) — nothing existing depended on it. The kubeconfig/ServiceAccount recipe from my earlier checklist is now obsolete, one less credential to create.postgresql-tenants.authentikinmodules/secrets.yaml(the key you pushed), restic backups untouched.main(theflake.nixconflict was just jellyfin vs postgres modules in the chicken block — kept both).nix eval .#nixosConfigurations.chicken.config.system.build.toplevel.drvPathpasses clean; generated config no longer contains the sync unit, and the activation path references onlypickler-postgres-setup.service.Cluster side (helm repo — the remaining half)
sops-secrets-operator (isindir) fits without Flux — a
SopsSecretCR reconciles into native Secrets however it's applied, so helmfile deploys it like anything else:age-keygena dedicated cluster keypair. Don't reuse the sops-nix host keys — the operator's private key lives as a k8s Secret in etcd, so keep it able to decrypt only the files addressed to it.helm repo add sops https://isindir.github.io/sops-secrets-operator/(chart 0.28.1 / operator 0.21.2), key wired viasecretsAsFiles+SOPS_AGE_KEY_FILEenv.encrypted in place with
sops --age <cluster-pubkey>— and per the operator's docs, useunencrypted_regex: '^(apiVersion|kind|metadata|status|name)$'so the k8s-injected plain fields keep decrypting. Apply with helmfile; the operator materialisespostgresql-connin theauthentiknamespace, and authentik's chart values point at that Secret pluschicken.lan:5432.Two caveats worth knowing before we commit to it:
modules/secrets.yamlhere, the SopsSecret there). Same value — re-encrypt on rotation, never regenerate one side. Worth a comment in both files stating that invariant.Ready for re-review: the four findings from your review are fixed (
d31c05ccomment above), this change is the fifth.Checked your
Merge branch 'main' into postgres-chicken(af97bc4) — merge is sound, and re-validated against the bumped lockfile:git diff d55f2b8..af97bc4carries only main-side changes (flake.lock,modules/llama-swap.nix, neovim plugin files).modules/postgres.nix,hosts/chicken.nixandmodules/secrets.yamlare byte-identical to my head — no conflict resolution touched our paths this time (main didn't touchflake.nix).af97bc4—nixosConfigurations.chicken.config.system.build.toplevel.drvPathevaluates clean against the new lock (nixos-system-chicken-26.05.20261105.c56a87d.drv, exit 0). Spot assertions on the merged config:pickler-postgres-setuppresent,pickler-postgres-k8s-syncabsent,tenants = [ "authentik" ].PR stays mergeable=true. Nothing further from me — all five review findings are addressed (Oct 16, Oct 21, Nov 2 comments); the formal REQUEST_CHANGES just needs your re-review when you get a chance.
View command line instructions
Checkout
From your project repository, check out a new branch and test the changes.Merge
Merge the changes and update on Forgejo.Warning: The "Autodetect manual merge" setting is not enabled for this repository, you will have to mark this pull request as manually merged afterwards.