PostgreSQL databases for services

{
  config,
  lib,
  hostConfig,
  pkgs,
  ...
}:
let
  cfg = config.mjm.services;

  postgresServices = lib.attrValues (lib.filterAttrs (_: s: s.postgresql.enable) cfg);
in
{
  options.mjm.services =
    let
      serviceType = { name, ... }: {
        options = {
          <<options>>
        };
      };
    in
    lib.mkOption {
      type = with lib.types; attrsOf (submodule serviceType);
    };

  config = lib.mkMerge [
    <<config>>
  ];

  _class = "nixos";
}

Services that need a PostgreSQL database can easily enable access to one through the options in this module.

If the service is running in a microVM, then the PostgreSQL server will run a separate microVM that serves all microVMs on the same host that need a database. This simplifies management of the database server without introducing an additional physical point of failure: it's not possible for one host going offline to disrupt a service on another host by taking away its database.

If the service is not running in a microVM, then the PostgreSQL server will be colocated on the same machine.

Service options

These options are available within the scope of a particular entry of mjm.services.

postgresql.enable = lib.mkEnableOption "PostgreSQL for the service";

Services must explicitly enable support for PostgreSQL.

postgresql.databases = lib.mkOption {
  type = with lib.types; listOf str;
  default = [ name ];
};

By default, a service will get a single database created with a name matching the service name. This should typically be left alone, but in some cases it might be necessary to have multiple databases or customize the name.

postgresql.external = lib.mkOption {
  type = lib.types.bool;
  default = hostConfig != null;
  internal = true;
};

This internal option determines whether PostgreSQL will be colocated with the service (external = false) or will be running externally in another microVM. It is set automatically based on whether hostConfig is present, which is a quick way to determine if the service is running in a microVM.

PostgreSQL colocated with the service

(
  let
    internalServices = lib.filter (s: !s.postgresql.external) postgresServices;
  in
  lib.mkIf (internalServices != [ ]) {
    <<colocated-config>>
  }
)

The most straightforward setup for PostgreSQL is to run alongside the service, and there's not a whole lot here to configure.

mjm.postgresql.enable = true;

Most of the actual setup for PostgreSQL is done by my postgresql service module. That module handles setting up basic telemetry, backups, and support for upgrading to newer versions.

=> postgresql service module

services.postgresql.ensureDatabases = lib.concatMap (s: s.postgresql.databases) internalServices;
services.postgresql.ensureUsers = lib.concatMap (
  s:
  map (d: {
    name = d;
    ensureDBOwnership = true;
  }) s.postgresql.databases
) internalServices;

The databases configured for the service need to be created in PostgreSQL, including users to access them. This is done using the options NixOS provides. Each database is assumed to have a matching system user as peer authentication over a Unix socket will be used. Each user will own the corresponding database, which should give services sufficient access to perform migrations as needed when upgrading versions.

PostgreSQL running externally from the service

When PostgreSQL is not running alongside the service, the configuration ends up split between two locations.

On the machine running PostgreSQL

(
  let
    externalServices = lib.pipe (hostConfig.microvm.vms or { }) [
      (lib.concatMapAttrs (_: vm: vm.config.config.mjm.services))
      lib.attrValues
      (lib.filter (s: s.postgresql.enable && s.postgresql.external))
    ];
  in
  lib.mkIf (config.mjm.postgresql.spiffe.enable) {
    <<external-db-config>>
  }
)

This configuration is enabled when the machine is configured to accept external connections to PostgreSQL via the mjm.postgresql.spiffe.enable option. In this case, PostgreSQL needs to be initialized based on the configuration of other services that are running on the same VM host. In particular, the services that are marked as accessing PostgreSQL externally, though as of now that should be all services on the machine with PostgreSQL enabled.

services.postgresql.ensureDatabases = lib.concatMap (s: s.postgresql.databases) externalServices;
services.postgresql.ensureUsers = map (s: {
  name = s.name;
  ensureDBOwnership = lib.elem s.name s.postgresql.databases;
}) externalServices;

This is similar to how databases and users are set up when running PostgreSQL colocated with the service, but there's an important difference. When running locally, the PostgreSQL user is based on the system user, so it's reasonable to expect to have a system user matching the name of the database, and therefore to always create a matching user for each database and use the ensureDBOwnership option from NixOS.

When PostgreSQL is running externally, the name of the service determines the PostgreSQL user via a SPIFFE certificate. So it only makes sense to create users based on the service name, not the databases, and only use ensureDBOwnership when the service name matches one of the databases. Usually this is the case, but I do have at least one service that runs multiple sidecars in the same VM that each need a database. Note that in this situation, the ownership of the database needs to be set manually.

On the machine running the service

(
  let
    externalServices = lib.filter (s: s.postgresql.external) postgresServices;
  in
  lib.mkIf (externalServices != [ ]) {
    <<external-service-config>>
  }
)

This configuration is enabled on the machines that need to talk to an external PostgreSQL server. The only relevant services are the ones on the same machine that have PostgreSQL enabled and marked as external. This should be a microVM connecting to another microVM that runs PostgreSQL on the same host.

# whichever version is default is fine for a client
environment.systemPackages = [ pkgs.postgresql ];

Since this machine won't be using the services.postgresql options from NixOS, it's not going to get any client tools installed either. These probably aren't needed most of the time, but for testing the connection it might be nice to have them available. The default version from Nixpkgs is used, which should usually be the most recent stable major version.

mjm.spire.tunnels = lib.pipe externalServices [
  (map (s: {
    name = "${s.name}-postgresql";
    value = {
      id = s.name;
      mode = "client";
      listen.socket = "/run/postgresql/.s.PGSQL.5432";
      target.service = "postgresql";
      target.tag = hostConfig.networking.hostName;
      extraArgs = [ "--alpn=postgresql" ];
    };
  }))
  lib.listToAttrs
];

A tunnel is used to connect to the PostgreSQL server so that the service itself doesn't need to worry about making a TLS connection itself. The tunnel listens on a Unix socket at the default location used by NixOS, so many services (and the psql CLI tool) will connect to it without custom configuration. The tunnel targets the postgresql Consul service, but uses the tag to connect to the specific instance that is running on the host machine. PostgreSQL will reject TLS connections that don't use ALPN, so an extra argument is passed to make ghostunnel do so with the correct protocol.

This code will theoretically create multiple tunnels if multiple services are declared on the machine that need one, but that won't actually work in practice. They would all put their socket in the same location, which obviously wouldn't work. I haven't encountered a need to support multiple identities connecting to PostgreSQL from a single machine yet, and I'd probably try to avoid it in general, so I'm not solving this problem right now.

mjm.spire.entries = lib.pipe externalServices [
  (map (s: {
    name = "tunnel-${s.name}-${s.name}-postgresql";
    value.dns_names = [ "${s.name}.svc.home.mattmoriarity.com" ];
  }))
  lib.listToAttrs
];

Because PostgreSQL can't extract a username from the URI SANs that SPIFFE certs include by default, the certs need to include a common name of a particular pattern that PostgreSQL can parse and get a username from. This config sets the dns_names for the SPIRE registration entries, which determines the common name on the certificate.