nixos/hosts/common/optional/nixos-containers/postgres.nix

{
  inputs,
  lib,
  config,
  configVars,
  pkgs,
  ...
}: let
  postgresPasswordPath = lib.optionalString (lib.hasAttr "sops-nix" inputs) config.sops.secrets."software/postgres/postgres/password".path;
  pubKeys = lib.filesystem.listFilesRecursive ../../users/keys;
  containerName = "postgres";
  containerIp = configVars.networking.addresses.postgres.ip;
  subnetIp = configVars.networking.addresses.subnet.ip;
  gatewayIp = configVars.networking.addresses.gateway.ip;
  postgresContainerData = configVars.locations.postgresContainerData;
in {
  sops.secrets = {
    "software/postgres/postgres/password" = {
    };
  };

  environment.persistence."/persist" = {
    hideMounts = true;
    directories = [
      "/var/lib/nixos-containers/${containerName}"
    ];
  };

  networking.nat.enable = true;
  networking.nat.internalInterfaces = ["ve-+"];
  networking.nat.externalInterface = "br0";

  containers.${containerName} = {
    autoStart = true;
    privateNetwork = true;
    hostBridge = "br0";
    nixpkgs = pkgs.path;
    bindMounts = {
      "/var/lib/postgresql" = {
        hostPath = postgresContainerData;
        isReadOnly = false;
      };
    };

    config = {
      pkgs,
      lib,
      ...
    }: {
      networking = {
        defaultGateway = "${gatewayIp}";
        interfaces.eth0.ipv4.addresses = [
          {
            "address" = "${containerIp}";
            "prefixLength" = 24;
          }
        ];
        firewall = {
          enable = true;
          allowedTCPPorts = [
            5432
          ];
        };
        useHostResolvConf = lib.mkForce false;
      };

      services.resolved.enable = true;

      environment.systemPackages = with pkgs; [
        lsof
      ];

      services.postgresql = {
        enable = true;
        enableJIT = true;
        package = pkgs.postgresql_16;
        extensions = with pkgs.postgresql_16.pkgs; [postgis];
        enableTCPIP = true;
        settings = {
          # max_worker_processes = "12";
          # max_parallel_workers = "8";
          # max_parallel_workers_per_gather = "4";
          # max_connections = "100";
          # autovacuum_work_mem = "2GB";
          # shared_buffers = "32GB";
          # work_mem = "0.32GB";
          # maintenance_work_mem = "64MB";
          max_connections = "100";
          shared_buffers = "2GB";
          effective_cache_size = "6GB";
          maintenance_work_mem = "1GB";
          checkpoint_completion_target = "0.9";
          wal_buffers = "16MB";
          default_statistics_target = "500";
          random_page_cost = "1.1";
          effective_io_concurrency = "200";
          work_mem = "17476kB";
          huge_pages = "off";
          min_wal_size = "4GB";
          max_wal_size = "16GB";
          max_worker_processes = "6";
          max_parallel_workers_per_gather = "3";
          max_parallel_workers = "6";
          max_parallel_maintenance_workers = "3";
        };
        authentication = pkgs.lib.mkOverride 10 ''
          #type database DBuser origin-address auth-method
          local   all             postgres                                peer
          host    all             all             ${subnetIp}/24         scram-sha-256
          local   replication     all                                    peer
          host    replication     all             127.0.0.1/32           scram-sha-256
        '';
      };

      # systemd.services.postgresql.postStart = ''
      #   $PSQL -tA <<'EOF'
      #     DO $$
      #     DECLARE password TEXT;
      #     BEGIN
      #       password := trim(both from replace(pg_read_file('${postgresPasswordPath}'), E'\n', '''));
      #       EXECUTE format('ALTER ROLE postgres WITH PASSWORD '''%s''';', password);
      #     END $$;
      #   EOF
      # '';

      services.openssh = {
        enable = true;
        settings.PasswordAuthentication = false;
      };

      users.users.root = {
        openssh.authorizedKeys.keys = lib.lists.forEach pubKeys (key: builtins.readFile key);
      };

      system.stateVersion = "24.05";
    };
  };
}