nixos/hosts/common/optional/docker/postgres.nix

{ pkgs, lib, inputs, config, ... }:
let
  admin_dbPasswordFile = lib.optionalString (lib.hasAttr "sops-nix" inputs) config.sops.secrets."software/postgres/admin_db/password".path;
  customEntryPointScript = pkgs.writeScript "custom-entrypoint.sh" ''
    #!/usr/bin/env bash
    ## copied from: https://github.com/docker-library/postgres/pull/496#issue-358838955
    set -Eeo pipefail
              
    echo "🐘 custom-entry-point"
    # Example using the functions of the postgres entrypoint to customize startup to always run files in /always-initdb.d/
              
    source "$(which docker-entrypoint.sh)"
              
    docker_setup_env
    docker_create_db_directories
    # assumption: we are already running as the owner of PGDATA
              
    # This is needed if the container is started as `root`
    #if [ "$1" = 'postgres' ] && [ "$(id -u)" = '0' ]; then
    if [ "$(id -u)" = '0' ]; then
      exec gosu postgres "$BASH_SOURCE" "$@"
    fi
              
              
    if [ -z "$DATABASE_ALREADY_EXISTS" ]; then
      echo "🐘 db is missing"
      docker_verify_minimum_env
      docker_init_database_dir
      pg_setup_hba_conf
              
      # only required for '--auth[-local]=md5' on POSTGRES_INITDB_ARGS
      export PGPASSWORD="''${PGPASSWORD:-$POSTGRES_PASSWORD}"
              
      docker_temp_server_start "$@" -c max_locks_per_transaction=256
      docker_setup_db
      docker_process_init_files /docker-entrypoint-initdb.d/*
      docker_temp_server_stop
    else
      echo "🐘 db already exists"
      docker_temp_server_start "$@"
      docker_process_init_files /always-initdb.d/*
      docker_temp_server_stop
    fi
              
    echo "🐘 .. starting!"
    exec postgres "$@"
  '';

  initScript = pkgs.writeScript "init.sh" ''
    #!/bin/bash
    function create_user_and_database() {
      local database=$1
      local user=$2
      local extensions=$3
      echo "### admin user: $POSTGRES_USER ###"
      echo "  Creating database '$database'"
      echo "  Creating user  '$user'"
      psql -v --username "$POSTGRES_USER" -d "$POSTGRES_DB" <<-EOSQL
          CREATE USER $user;
          CREATE DATABASE $database;
          GRANT ALL PRIVILEGES ON DATABASE $database TO $user;
    EOSQL
            
    # Loop through extensions and create them
    for ext in $(echo "$extensions" | tr ',' ' '); do
      echo "     - Installing extention $ext"
      psql -v --username "$POSTGRES_USER" -d "$database" -c "CREATE EXTENSION $ext;"
    done
    }
            
    if [ -n "$POSTGRES_MULTIPLE_DATABASES" ]; then
            
      # Parse the JSON string
      database_names=$(echo "$POSTGRES_MULTIPLE_DATABASES" | jq -r '.[0] | keys[]')
      echo "Multiple database creation requested: $(echo "$database_names" | tr "\n" " ")"
              
      # Loop through each database and create it
      for db_name in $database_names; do
        user=$(echo "$POSTGRES_MULTIPLE_DATABASES" | jq -r ".[0] | .''${db_name} | .user")
        extensions=$(echo "$POSTGRES_MULTIPLE_DATABASES" | jq -r ".[0] | .''${db_name} | .extensions | join(\",\")")
        create_user_and_database "$db_name" "$user" "$extensions"
      done
    fi
  '';

in
{
  sops.secrets = {
    "software/postgres/admin_db/password" = { };
  };
  virtualisation.arion = {
    backend = "docker";
    projects = {
      "db".settings.services."db".service = {
        restart = "unless-stopped";
        build.context = "/nix/store";
        build.dockerfile = builtins.baseNameOf "${pkgs.writeScript "pgDockerfile" ''
            FROM postgres:16
            # install packages
            RUN apt-get update \
                && apt-get install -y --no-install-recommends \
                    postgresql-16-postgis \
                    jq \
                && rm -rf /var/lib/apt/lists/*
            RUN mkdir -p /scripts/ /always-initdb.d/
            COPY ${builtins.baseNameOf customEntryPointScript} /scripts/custom-entrypoint.sh
            RUN chmod -R a+x /scripts
            ENTRYPOINT /scripts/custom-entrypoint.sh
          ''}";

        environment = {
          POSTGRES_PASSWORD_FILE = admin_dbPasswordFile;
          POSTGRES_USER = "admin";
          POSTGRES_DB = "admin_db";
          PGDATA = "/var/lib/postgresql/data/pgdata";
          POSTGRES_MULTIPLE_DATABASES = ''
            [
              {
                "osm": {
                  "user": "gis",
                  "extensions": [
                    "hstore",
                    "postgis"
                  ]
                },
                "bitcoin": {
                  "user": "satoshi",
                  "extensions": []
                },
                "btc_models": {
                  "user": "dbt",
                  "extensions": []
                },
                "dev_btc_models": {
                  "user": "dbt",
                  "extensions": []
                }
                "test": {
                  "user": "test",
                  "extensions": [hstore]
                }
              }
            ]
          '';

        };
        ports = [ "5432:5432" ];

        volumes = [
          "/mnt/postgres:/var/lib/postgresql/data"

          # Need to mount secret file 
          "${admin_dbPasswordFile}:${admin_dbPasswordFile}"

          # PG init script to parse json specified in POSTGRES_MULTIPLE_DATABASES
          # creates databases, users and installs extensions for each database.
          "${initScript}:/always-initdb.d/init.sh"
        ];
      };
    };
  };
}