{ pkgs, config, hostName, host, ... }:
let
  inherit (config.networking) domain;
  inherit (config.services) postgresql;
  inherit (config.users) users;
in
{
  imports =
    [ ];
  /*
    map (eta: (import postgresql/openconcerto.nix) eta) [
    {db = "openconcerto1";}
    {db = "openconcerto2";}
    {db = "lbec";}
    {db = "lbm";}
    ];
  */
  /*
    networking.nftables.ruleset = ''
    table inet filter {
    chain input-net {
      tcp dport postgresql counter accept comment "postgresql"
    }
    }
    '';
  */
  users.groups.acme.members = [ users."postgres".name ];
  security.acme.certs."${domain}" = {
    postRun = "systemctl reload postgresql";
  };
  systemd.services.postgresql = {
    wants = [ "acme-selfsigned-${domain}.service" "acme-${domain}.service" ];
    after = [ "acme-selfsigned-${domain}.service" ];
  };
  services.postgresql = {
    enable = true;
    package = pkgs.postgresql_17;
    #enableTCPIP = true;
    settings = {
      log_connections = true;
      log_disconnections = true;
      log_hostname = false;
      max_locks_per_transaction = 1024;
      password_encryption = "scram-sha-256"; # requires postfix >= 11
      ssl = false;
      ssl_cert_file = "/var/lib/acme/${domain}/fullchain.pem";
      ssl_key_file = "/var/lib/acme/${domain}/key.pem";
      unix_socket_permissions = "0770";

      # ZFS is Copy on Write (CoW). As a result, it’s not possible
      # to have a torn page because a page can’t be partially written
      # without reverting to the previous copy.
      full_page_writes = false;
      # Avoid zero-filling of new files used for Postgres’s Write Ahead Log.
      # The reasoning here is that since ZFS overwrites these anyway,
      # you might as well get yourself a new inode.
      wal_init_zero = false;
      wal_recycle = false;

      max_connections = 50;
      # The proper size for the POSTGRESQL shared buffer cache is the largest
      # useful size that does not adversely affect other activity.
      # Ideally we should limit ZFS's ARC's max size
      # to a percentage of physical memory
      # minus the shared_buffer cache for PostgreSQL
      # minus the kernel's memory overhead.
      shared_buffers = "1GB";
      # Used by the optimizer to estimate the size of the kernel's disk buffer cache.
      effective_cache_size = "3GB";
      # Setting a large value helps in tasks like
      # VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.
      maintenance_work_mem = "256MB";
      # Bigger value for wal_buffers in case of lot of concurrent connection gives better performance.
      wal_buffers = "16MB";
      min_wal_size = "1GB";
      max_wal_size = "4GB";
      default_statistics_target = 100;
      random_page_cost = "1.1";
      effective_io_concurrency = 200;
      work_mem = "20971kB";
      huge_pages = false;
      max_worker_processes = host.CPUs;
      max_parallel_workers_per_gather = host.CPUs / 2;
      max_parallel_workers = host.CPUs;
      max_parallel_maintenance_workers = host.CPUs / 2;
    };
    # If one record is chosen and the auth fails,
    # subsequent records are not considered.
    authentication = ''
      # CONNECTION  DATABASE USER      AUTH  OPTIONS
      local         all      postgres  peer  map=admin
      local         samerole all       peer  map=user
      #local         all      backup    peer
    '';
    identMap = ''
      # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
      admin      postgres         postgres
      admin      root             postgres
      # Commented to prefer explicit auth
      # to handle revocation without droping the user yet
      #user       /^(.*)$          \1
    '';
  };
  /*
    services.syncoid.commands = {
    "${hostName}/var/postgresql" = {
    sendOptions = "raw";
    target = "backup@mermet.${domain}:rpool/backup/${hostName}/var/postgresql";
    };
    };
  */
  services.sanoid.datasets = {
    "${hostName}/var/lib/postgresql" = {
      use_template = [ "snap" ];
      daily = 31;
      hourly = 0;
      recursive = true;
    };
  };
  systemd.services.postgresql = {
    # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting
    postStart = ''
      set -eux
      # DOC: https://wiki.postgresql.org/wiki/Shared_Database_Hosting#Defining_shared_hosting
      $PSQL -d template1 --set ON_ERROR_STOP=1 -f - <<EOF
        -- Disallow access to the public schema
        -- of individual users' databases by other users
        REVOKE ALL ON DATABASE template0 FROM public;
        REVOKE ALL ON DATABASE template1 FROM public;
        REVOKE ALL ON SCHEMA public FROM public;
        GRANT  USAGE,CREATE ON SCHEMA public TO public;
        GRANT  ALL ON SCHEMA public TO "${postgresql.superUser}";
        REVOKE ALL ON ALL TABLES IN SCHEMA public FROM public;
        GRANT  ALL ON ALL TABLES IN SCHEMA public TO "${postgresql.superUser}";

        -- Disallow access to database and user names for everyone
        REVOKE ALL ON pg_catalog.pg_user         FROM public;
        REVOKE ALL ON pg_catalog.pg_group        FROM public;
        REVOKE ALL ON pg_catalog.pg_authid       FROM public;
        REVOKE ALL ON pg_catalog.pg_auth_members FROM public;
        REVOKE ALL ON pg_catalog.pg_settings     FROM public;
        -- The following must have SELECT reallowed if pg_dump is allowed
        REVOKE ALL ON pg_catalog.pg_roles        FROM public;
        REVOKE ALL ON pg_catalog.pg_database     FROM public;
        REVOKE ALL ON pg_catalog.pg_tablespace   FROM public;
      EOF

      pg_createdb () {
        local db=$1
        local owner=''${owner:-$db}
        local template=''${template:-template1}
        $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_database WHERE datname = '$db'" | grep -q 1 || {
          $PSQL -d "$template" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
            CREATE ROLE "$owner" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+PASSWORD '$pass'};
            CREATE DATABASE "$db" WITH OWNER="$owner"
             ''${encoding:+ENCODING='$encoding'}
             ''${lc_collate:+LC_COLLATE='$lc_collate'}
             ''${lc_ctype:+LC_CTYPE='$lc_ctype'}
             ''${tablespace:+TABLESPACE='$tablespace'}
             ''${connection_limit:+CONNECTION LIMIT=$connection_limit}
            ;
            REVOKE ALL ON DATABASE "$db" FROM public;
      EOF
          $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
            -- Grant all rights to the public schema in the new database to the main user
            GRANT ALL ON SCHEMA public TO "$owner" WITH GRANT OPTION;
      EOF
          $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f -
        }
      }

      pg_adduser () {
        local db=$1
        local user=$2
        $PSQL -tAc "SELECT 1 FROM pg_catalog.pg_roles WHERE rolname='$user'" | grep -q 1 ||
        $PSQL -d "$db" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
          CREATE ROLE "$user" NOSUPERUSER NOCREATEDB NOCREATEROLE NOINHERIT LOGIN ''${pass:+ENCRYPTED PASSWORD '$pass'};
          GRANT USAGE ON SCHEMA public TO "$user";
          GRANT CONNECT,TEMPORARY ON DATABASE "$db" TO "$user";
      EOF
      }
    '';
  };
}