postgres (aerialis)

This container provides PostgreSQL database services for other containers and applications on aerialis.

General

This container houses our Postgres database. Multiple services access it:

• Mastodon
• Matrix
• Matrix bridges
• WikiJs

Admin interface

The admin interface powered by Pgadmin can be accessed here. Authentication happens via Cloudflare Access.

Nix expression

{
  garuda-lib,
  pkgs,
  sources,
  config,
  lib,
  ...
}:
let
  server_config = pkgs.writeText "server-config" ''
    {
      "Servers": {
        "1": {
          "Name": "Main",
          "Group": "Garuda",
          "Username": "pgadmin",
          "Host": "/var/run/postgresql",
          "Port": 5432,
          "SSLMode": "prefer",
          "MaintenanceDB": "postgres",
          "PassFile": "/dev/null",
          "Shared": true,
          "SharedUsername": "pgadmin"
        }
      }
    }
  '';
in
{
  imports = sources.defaultModules ++ [ ../../modules ];

  garuda = garuda-lib.mkMonitoring {
    host = "aerialis";
    units = [ "postgresql.service" ];
  };

  services.postgresql = {
    enable = true;
    ensureDatabases = [
      "chaotic-aur"
      "mastodon"
      "wikijs"
    ];
    ensureUsers = [
      {
        name = "mastodon";
        ensureDBOwnership = true;
      }
      {
        name = "wikijs";
        ensureDBOwnership = true;
      }
      {
        name = "pgadmin";
        ensureClauses.superuser = true;
      }
      {
        name = "chaotic-router";
      }
      {
        name = "chaotic-aur";
        ensureDBOwnership = true;
      }
      {
        name = "netdata";
        ensureClauses.login = true;
      }
    ];
    extensions = with pkgs.postgresql_18.pkgs; [
      pg_hll # chaotic-aur uses this for the useragent/ip statistics
      pg_repack # this lets us run the vacuum online
    ];
    authentication = lib.mkForce ''
      local all all peer
      hostssl chaotic-aur chaotic-router 0.0.0.0/0 scram-sha-256
      host all netdata 10.0.5.1/32 scram-sha-256
      # Reject anything else coming from the outside world somehow someway
      host all all 10.0.5.1/32 reject
      # Allow connections from the internal network
      host all all 10.0.5.0/24 scram-sha-256
      # Allow localhost connections
      host all all 127.0.0.1/32 scram-sha-256
      # Block the rest of the internet
      host all all 0.0.0.0/0 reject
    '';
    # This is publically accessible now through port 5432, however only the chaotic-router user can access
    # the database through the internet, and only over TLS
    enableTCPIP = true;
    package = pkgs.postgresql_18;
    settings = {
      ssl = true;
      ssl_cert_file = "${config.services.postgresql.dataDir}/server.crt";
      ssl_key_file = "${config.services.postgresql.dataDir}/server.key";

      # Defaults (128MB / 4GB) are far too small for a cluster shared by multiple services
      shared_buffers = "4GB";
      effective_cache_size = "12GB";
    };
  };

  # Regular backups for our database (every 6h)
  services.postgresqlBackup = {
    compression = "zstd";
    enable = true;
    location = "/var/garuda/backups/postgres";
  };

  services.pgadmin = {
    enable = true;
    initialEmail = "[email protected]";
    initialPasswordFile = config.sops.secrets."postgres/pg_admin".path;
    openFirewall = true;
    settings = {
      DEFAULT_SERVER = "10.0.5.20";
      FIXED_BINARY_PATHS = {
        "pg" = "${config.services.postgresql.package}/bin";
      };
      SUPPORT_SSH_TUNNEL = false;
      AUTHENTICATION_SOURCES = [ "webserver" ];
      WEBSERVER_REMOTE_USER = "X-Forwarded-User";
      MASTER_PASSWORD_REQUIRED = false;
    };
  };

  systemd.services.pgadmin = {
    preStart = lib.mkAfter ''
      EMAIL=${lib.escapeShellArg config.services.pgadmin.initialEmail}
      FILE=${lib.escapeShellArg server_config}
      ${config.services.pgadmin.package}/bin/pgadmin4-cli load-servers "$FILE" --user "$EMAIL"
    '';
  };

  # Serve the live ACME wildcard cert to Postgres for external TLS
  system.activationScripts.postgresServerCert = ''
    install -o postgres -g postgres -m 0600 \
      /var/lib/acme/garudalinux.org/key.pem \
      ${config.services.postgresql.dataDir}/server.key
    install -o postgres -g postgres -m 0644 \
      /var/lib/acme/garudalinux.org/fullchain.pem \
      ${config.services.postgresql.dataDir}/server.crt
  '';

  # Open up ports for Postgres
  networking.firewall.allowedTCPPorts = [ 5432 ];

  sops.secrets."postgres/pg_admin" = { };
  sops.secrets."postgres/netdata" = {
    owner = "postgres";
    group = "postgres";
    mode = "0400";
  };

  systemd.services.postgresql.postStart = lib.mkAfter ''
    PASS=$(cat ${config.sops.secrets."postgres/netdata".path})
    ${config.services.postgresql.package}/bin/psql -U postgres -c "ALTER USER netdata WITH LOGIN PASSWORD '$PASS'"
    ${config.services.postgresql.package}/bin/psql -U postgres -c "GRANT pg_monitor TO netdata"
  '';

  system.stateVersion = "23.05";
}