{ pkgs, lib, config, ... }:
let
  inherit (config) networking;
  inherit (config.services) postgresql;
  inherit (config.users) users;
in
{
imports = [
  postgresql/openconcerto.nix
];
services.shorewall.configs.rules = ''
  PostgreSQL(ACCEPT) net $FW {rate=s:5/min:10}
'';
users.groups.acme.members = [ users."postgres".name ];
security.acme.certs."${networking.domain}" = {
  postRun = "systemctl reload postgresql";
};
systemd.services.postgresql = {
  wants = [ "acme-selfsigned-${networking.domain}.service" "acme-${networking.domain}.service"];
  after = [ "acme-selfsigned-${networking.domain}.service" ];
};
services.postgresql = {
  enable = true;
  package = pkgs.postgresql_9_6;
  enableTCPIP = true;
  extraConfig = ''
    max_connections = 25
    max_locks_per_transaction = 256
    password_encryption = on # FIXME: replace md5 by scram-sha-256, which requires postfix >= 11
    ssl = on
    ssl_cert_file = '/var/lib/acme/${networking.domain}/fullchain.pem'
    ssl_key_file = '/var/lib/acme/${networking.domain}/key.pem'
    unix_socket_permissions = 0770
  '';
  authentication = lib.mkForce ''
    # 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
    user       /^(.*)$          \1
  '';
};
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  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_roles        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_database     FROM public;
      REVOKE ALL ON pg_catalog.pg_tablespace   FROM public;
      REVOKE ALL ON pg_catalog.pg_settings     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 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=$1
      $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 ENCRYPTED PASSWORD '$pass';
        GRANT USAGE ON SCHEMA public TO $user;
        GRANT CONNECT,TEMPORARY ON DATABASE $db TO $user;
    EOF
    }
  '';
};
}