{ pkgs, config, host, ... }:
let
  inherit (config.services) postgresql;
in
{
  /*
    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" ];
    };
  */
  environment.systemPackages = [
    pkgs.pg_top
  ];
  services.postgresql = {
    enable = true;
    package = pkgs.postgresql_13;
    enableTCPIP = false;
    # zfs set recordsize=16k
    # zfs set sync=disabled
    # zfs set logbias=latency
    # zfs set primarycache=all
    # zfs set redundant_metadata=most
    # DOC: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server
    settings = {
      log_connections = true;
      log_disconnections = true;
      log_hostname = false;
      max_locks_per_transaction = 1024;
      password_encryption = "scram-sha-256"; # requires postfix >= 11
      # Synchronous commit doesn't introduce the risk of corruption, which is really bad,
      # just some risk of data loss.
      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.
      # https://vadosware.io/post/everything-ive-seen-on-optimizing-postgres-on-zfs-on-linux/#setting-full_page_writesoff
      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 = "512MB";
      # Used by the optimizer to estimate the size of the kernel's disk buffer cache.
      effective_cache_size = "1536MB";
      # Setting a large value helps in tasks like
      # VACUUM, RESTORE, CREATE INDEX, ADD FOREIGN KEY and ALTER TABLE.
      maintenance_work_mem = "128MB";
      # 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 = "5242kB";
      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.sanoid.datasets = {
    "rpool/var/lib/postgresql" = {
      use_template = [ "snap" ];
      monthly = 1;
      daily = 31;
      hourly = 0;
      recursive = true;
    };
  };
  systemd.services.postgresql = {
    serviceConfig = {
      CPUAccounting = true;
      #CPUWeight = "idle";
      CPUQuota = "75%";
      MemoryAccounting = true;
      MemoryHigh = "500M";
      MemoryMax = "600M";
    };
    # 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
      }
    '';
  };
}