PostgreSQL

{
  pkgs,
  config,
  lib,
  ...
}:
let
  cfg = config.mjm.postgresql;
in
{
  imports = [ ./spiffe.nix ];

  options.mjm.postgresql = {
    <<options>>
  };

  config = lib.mkIf cfg.enable {
    <<config>>
  };

  _class = "nixos";
}

I use PostgreSQL as the database for several different services. While it's pretty easy to set it up on NixOS, I have a little bit of extra infrastructure for it that this module enables.

Whenever I run PostgreSQL, it is within the same microVM as the service using it. This avoids any need to mess about with credentials: using peer authentication over a Unix socket is very simple.

Obviously there is overhead to running so many distinct PostgreSQL instances, particularly on one machine. It might be cool to explore a setup where a single PostgreSQL microVM runs on the host, and then its socket is exposed to each VM that needs a database via vsock. A solution that does not require any passwords would be strongly preferred. Another option could be using tunnels with mTLS via SPIFFE. I definitely prefer to have the DB instance on the same host as the service, so that bringing down a host machine only interrupts services actually running on that host.

Options

enable = lib.mkEnableOption "PostgreSQL";

PostgreSQL is not enabled by default, since not every machine needs it.

extraBackupDatabases = lib.mkOption {
  type = with lib.types; listOf str;
  default = [ ];
};

This module enables automatic backups of the databases within the PostgreSQL instance. Normally, the databases to backup are determined from the services.postgresql.ensureDatabases option. Some NixOS services don't use this though: they create their database via some bespoke mechanism, so the backup will miss them. In that case, the name of the database can be added to extraBackupDatabases to ensure it still gets backed up.

oldPackage = lib.mkOption {
  type = with lib.types; nullOr package;
  default = null;
};

The oldPackage option facilitates upgrading PostgreSQL to a new major version. The process of doing an upgrade will be covered more later, but to be able to do so, services.postgresql.package should be set to the new version to upgrade to, and mjm.postgresql.oldPackage should be set to the version that was being used previously. An upgrade script will be generated that uses both of these to perform the upgrade.

When this is null, no upgrade script will be present and only one version of PostgreSQL will be installed on the machine.

spiffe.enable = lib.mkOption {
  type = lib.types.bool;
  default = false;
};

Config

services.postgresql.enable = true;
services.postgresql.package = lib.mkOverride 900 (throw "no postgresql package set");

Enables the PostgreSQL service, and sets a default value for the package so that it must be set explicitly. The NixOS module for PostgreSQL uses system.stateVersion to determine the default PostgreSQL version: it uses whatever was current when the system was first set up. I don't really like that behavior. I think it makes much more sense to require the version to be explicitly set, given that upgrades between major versions must be done manually.

The default for services.postgresql.package is set via lib.mkDefault, so I use a custom override priority of 900 to take precedence over that while still allowing modules to set the actual package without an explicit override.

services.postgresql.settings.log_min_messages = "INFO";
services.postgresql.settings.log_connections = "all";
services.postgresql.settings.log_disconnections = true;
services.postgresql.settings.log_statement = "ddl";

PostgreSQL's logging is pretty quiet by default, so this enables a little more verbosity.

TLS authentication with SPIFFE certs

{
  lib,
  config,
  hostConfig,
  ...
}:
let
  cfg = config.mjm.postgresql;
  hostName = hostConfig.networking.hostName or config.networking.hostName;
in
{
  config = lib.mkIf (cfg.enable && cfg.spiffe.enable) {
    <<spiffe-config>>
  };
}

I'm exploring moving from running PostgreSQL instances within each microVM to having a single PostgreSQL instance per host, and using SPIFFE identities for authentication. This logic is kept in its own module to be able have one gate for everything for whether the option is enabled.

mjm.consul.services.postgresql = {
  port = 5432;
  serviceConfig.tags = [ hostName ];

  checks.up = {
    script.args = [ "${config.services.postgresql.package}/bin/pg_isready" ];
  };
};

The PostgreSQL instance will be accessed from outside the VM it's running in, so it needs to register with Consul to let clients discover it. The hostname of the host (not the VM) is used as a tag on the service instance, so that clients can target the PostgreSQL instance running on the same host using DNS. For instance, the instance running on apollo will be reachable at apollo.postgresql.service.consul:5432.

mjm.spire.certs.postgresql = {
  systemd.unit = "postgresql.service";
  systemd.action = "reload-or-restart";
  user = "postgres";
};
mjm.spire.entries.spiffe-certs-postgresql.dns_names = [ "${hostName}.postgresql.service.consul" ];

In this setup, PostgreSQL will need TLS certificates. It needs the trust bundle to be able to verify client certificates, and it needs to provide its own TLS certificate for the server it exposes. The SPIRE entry created automatically by mjm.spire.certs is updated to include the DNS name matching the tag set above in Consul, since this is the address clients will be using to reach this instance.

services.postgresql.enableTCPIP = true;
services.postgresql.settings = {
  ssl = true;
  ssl_ca_file = "/run/certs/postgresql/bundle.pem";
  ssl_cert_file = "/run/certs/postgresql/cert.pem";
  ssl_key_file = "/run/certs/postgresql/key.pem";

  <<vector-settings>>
};

networking.firewall.allowedTCPPorts = [ 5432 ];

With certificates issued, PostgreSQL is configured to use them. TCP needs to be used for SSL connections to PostgreSQL, so that's enabled and the port is allowed through the firewall so other VMs can actually reach it.

Below, some additional settings will be added to support the vector extensions used for Immich.

services.postgresql.authentication = ''
  hostssl  all  all  all  cert  map=spiffe
'';
services.postgresql.identMap = ''
  spiffe  /^([[:word:]-]+).svc.home.mattmoriarity.com$  \1
'';

These settings configure PostgreSQL to do authentication using TLS client certificates. When using the "cert" authentication method, PostgreSQL will use the CN (common name) from the subject of the certificate to determine the database username for the connection. By default, certificates from SPIRE don't have a CN: they include the SPIFFE ID as a URI SAN. PostgreSQL can't use this as far as I can tell. We can get SPIRE to produce a certificate with a CN by using dns_names in the registration entry: the first name in the list seems to be used as the CN. So for the entries for connecting to PostgreSQL, I'll be adding a dns_name of ".svc.home.mattmoriarity.com" for the service. The regular expression in the "spiffe" ident map will pull out the name and use it as the database username.

services.postgresql.extensions = ps: [
  ps.pgvector
  ps.vectorchord
];

shared_preload_libraries = [ "vchord.so" ];
search_path = "\"$user\", public, vectors";

Immich needs two vector extensions, so those are set up on any PostgreSQL instance intended for external use.

Upgrading to a new major version

environment.systemPackages = lib.mkIf (cfg.oldPackage != null) [
  (pkgs.execline.writeScriptBin "upgrade-postgres" "-P" ''
    <<upgrade-postgres>>
  '')
];

If an oldPackage is set, that means that I intend to upgrade this machine to a new PostgreSQL version. This requires taking the database server offline during the process, so it's not something that NixOS will do automatically. I've written a script to make this easier, since I run so many small PostgreSQL instances.

multisubstitute {
  define old_bin ${cfg.oldPackage}/bin
  define new_bin ${config.services.postgresql.package}/bin
  define old_data /var/lib/postgresql/${cfg.oldPackage.psqlSchema}
  define new_data /var/lib/postgresql/${config.services.postgresql.package.psqlSchema}
}

The upgrade script starts by defining names for paths to the bindir and data of both PostgreSQL versions. These will of course be used later to perform the upgrade.

cd /root

pg_upgrade will create temporary sockets in the current directory, so that should be somewhere writable that other users can't access. /root fits the bill.

if { systemctl stop postgresql }

The new PostgreSQL service will likely be running already, albeit with no data in it. It needs to be stopped to perform the upgrade.

if { run0 rm -rf $new_data }
if { run0 -u postgres initdb -D $new_data }

The database for the new version of PostgreSQL should be a clean slate, but it's possible for some of the necessary databases for services on the machine to have been created when it was first started, which can prevent the upgrade from working. In case this happened, the script deletes the new data directory and runs initdb again.

if {
  run0 -u postgres
    pg_upgrade
    -b $old_bin
    -B $new_bin
    -d $old_data
    -D $new_data
}
if { systemctl start postgresql }

Now the script performs the actual upgrade by running pg_upgrade, providing it both the old and new bindirs and data. If that succeeds, it starts the new PostgreSQL version up again, and this time it should have the data from the old version.

foreground { run0 -u postgres ''${new_bin}/vacuumdb --all --analyze-in-stages --missing-stats-only }
run0 -u postgres ''${new_bin}/vacuumdb --all --analyze-only

pg_upgrade instructs you to run these vacuumdb commands after the upgrade. They regenerate some statistics that are not copied over by the upgrade process.

And that's it. After running the script, the mjm.postgresql.oldPackage option can be unset, which will remove the script from the machine. Services may need to be restarted in some cases to reconnect to the DB server.

Automatic backups

mjm.backups.postgresql =
  let
    pg = config.services.postgresql.package;
    dbs = config.services.postgresql.ensureDatabases ++ cfg.extraBackupDatabases;
    dumpDBs = lib.pipe dbs [
      lib.unique
      (lib.concatMapStrings (dbname: ''
        if { ${pg}/bin/pg_dump --format=directory -j 4 -f ${dbname} ${dbname} }
      ''))
    ];
  in
  {
    paths = [ "/var/lib/postgresql/backup" ];
    user = "postgres";
    backupPrepareCommand = ''
      foreground { rm -rf /var/lib/postgresql/backup }
      if { mkdir -p /var/lib/postgresql/backup }
      cd /var/lib/postgresql/backup

      if { ${pg}/bin/pg_dumpall --globals-only -f globals.sql }
      ${dumpDBs}
      exit
    '';
    backupCleanupCommand = ''
      rm -rf /var/lib/postgresql/backup
    '';
  };

This sets up a backup job for PostgreSQL that will run on a daily timer. It dumps each database into its own directory in the backup, and dumps any globals into globals.sql.

Now there is a little bit of tedious work to give the backup job access to secrets. PostgreSQL itself doesn't need any secrets, but the Restic repository for the backup is protected by a password, so it needs that. Ideally, I would be able to add a snippet like this:

mjm.services.postgresql = {
  secrets.enable = true;
};

And that would set everything up. But because the mjm.services module has an option to enable PostgreSQL, that ends up causing an infinite recursion. So instead, this module has to replicate the manual steps to enable secrets.

mjm.spire.entries = {
  "postgresql-${config.networking.hostName}" = {
    spiffe_id = "svc/postgresql";
    parent_id = config.networking.hostName;
  };
  spiffe-creds-postgresql = {
    spiffe_id = "svc/postgresql";
    selectors = [
      {
        type = "systemd";
        value = "id:spiffe-creds@postgresql.service";
      }
    ];
  };
};

First, it defines a SPIRE entry for the PostgreSQL service on the current machine. It defines a second such that any machine with the PostgreSQL service grants that identity to the spiffe-creds@postgresql service.

systemd.sockets."spiffe-creds@postgresql" = {
  overrideStrategy = "asDropin";
  wantedBy = [ "sockets.target" ];
};

spiffe-creds runs as a template service, so most of the configuration for it is already set up. To get an instance running for PostgreSQL specifically, a drop-in file is needed to actually start the socket for it when the system starts.

mjm.vault.services = [ "postgresql" ];

Finally, the infrastructure config needs to know to create an entity alias in Vault for the PostgreSQL service. Otherwise, when the spiffe-creds service first accesses Vault to read a secret, a default alias and entity would be created automatically, and that entity would not have the policy that allows it to access secrets for the service.

Gathering metrics

environment.etc."alloy/postgresql.alloy".source = ./postgresql.alloy;
environment.etc."alloy/dsns.json".text = builtins.toJSON (
  map (
    db: "postgresql:///${db}?host=/run/postgresql&user=alloy"
  ) config.services.postgresql.ensureDatabases
);

Grafana Alloy is already running on the system and includes the PostgreSQL exporter for Prometheus, so all that is needed to gather database metrics is to drop in a configuration file for it. An additional file is generated here to provide the list of datasource names to Alloy, one for each database.

local.file "dsns" {
  filename = "/etc/alloy/dsns.json"
}
prometheus.exporter.postgres "default" {
  data_source_names = json_path(local.file.dsns.content, "$[*]")
}

The file with the DSNs generated above is imported via the local.file component, and then those names are passed to the PostgreSQL exporter component using json_path to parse them from JSON into Alloy data types.

prometheus.scrape "postgres" {
  targets = prometheus.exporter.postgres.default.targets
  forward_to = [prometheus.remote_write.prod.receiver]
}

With the exporter up and running, it can be scraped and forwarded to Prometheus. The remote write receiver is set up in my common server module.