{ pkgs, lib, config, hostName, ... }:
let
  inherit (config.networking) domain;
  inherit (config.services) postgresql;
  inherit (config.users) users;
in
{
/*
networking.nftables.ruleset = ''
  add rule inet filter net2fw tcp dport 5432 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_12;
  enableTCPIP = false;
  settings = {
    # 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;
    log_connections = true;
    log_disconnections = true;
    log_hostname = false;
    max_connections = 25;
    max_locks_per_transaction = 1024;
    password_encryption = "scram-sha-256"; # requires postfix >= 11
    unix_socket_permissions = "0770";
  };
  # 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
  '';
};
fileSystems."/var/lib/postgresql" = {
  device = "rpool/var/postgresql";
  fsType = "zfs"; # with sync=always,
  # though loading OpenConcerto's initial SQL
  # takes 1m40s instead of 40s :\
};
services.sanoid.datasets = {
  "rpool/var/postgresql" = {
    use_template = [ "snap" ];
    daily = 31;
  };
};
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
  '';
};
}