servers/losurdo/postgresql/openconcerto.nix

   1 { db, owner ? db, ... }:
   2 { pkgs, lib, config, ... }:
   3 let
   4   sql = pkgs.fetchzip {
   5     url = "https://www.openconcerto.org/fr/telechargement/1.6/OpenConcerto-1.6.3.sql.zip";
   6     sha256 = "02h35ni9xknzrjsra56c3zhlhs0ji9qc61kcgi7vgcpylqjw0s6n";
   7   };
   8   inherit (config) networking;
   9   # Example of ~/.config/OpenConcerto/main.properties
  10   # DOC: https://code.openconcerto.org/filedetails.php?repname=OpenConcerto&path=%2Ftrunk%2FOpenConcerto%2Fsrc%2Forg%2Fopenconcerto%2Fsql%2FPropsConfiguration.java
  11   # DOC: https://jdbc.postgresql.org/documentation/head/connect.html
  12   "main.properties" = ''
  13     base.root=Common
  14     customer=Gestion_Default
  15     jdbc.connection.ssl=true
  16     jdbc.connection.sslmode=require
  17     server.driver=postgresql
  18     server.ip=openconcerto.${networking.domain}\:5432
  19     server.login=${owner}
  20     server.password=TheSecretPassword
  21     systemRoot=${db}
  22   '';
  23   # To be used in postStart when resetting the database
  24   drop = ''
  25     $PSQL -d template1 -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
  26       DROP OWNED BY ${owner};
  27       DROP DATABASE ${db};
  28       DROP ROLE     ${owner};
  29     EOF
  30   '';
  31 in
  32 {
  33 systemd.services.postgresql = {
  34   postStart = lib.mkAfter ''
  35     sed -e 's/ \(TO\|FROM\) \+openconcerto/ \1 ${owner}/g' \
  36      ${sql}/OpenConcerto-1.6.3.sql |
  37     connection_limit=64 \
  38     encoding=UTF8 \
  39     lc_collate=fr_FR.UTF-8 \
  40     lc_type=fr_FR.UTF-8 \
  41     owner=${owner} \
  42     pass=$(cat /run/keys/postgresql_pass_${owner}) \
  43     pg_createdb ${db} >/dev/null
  44
  45     $PSQL -d "${db}" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
  46       -- Reallow this to avoid the error:
  47       -- "Couldn't refresh the graph"
  48       -- when testing the connexion to the database
  49       -- in OpenConcerto-Configuration.sh
  50       GRANT SELECT ON pg_catalog.pg_settings TO ${owner};
  51
  52       -- Enable PL/PGSQL
  53       CREATE OR REPLACE LANGUAGE plpgsql;
  54     EOF
  55   '';
  56 };
  57 services.postgresql = {
  58   authentication = lib.mkForce ''
  59     # CONNECTION  DATABASE USER      AUTH  OPTIONS
  60     # FIXME: using scram-sha-256 instead of md5 requires postfix >= 11
  61     hostssl       ${db}    ${owner}  all   md5
  62   '';
  63   identMap = ''
  64     # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
  65     user       root             ${owner}
  66   '';
  67 };
  68 security.install.shellHook = ''
  69   pass "servers/losurdo/postgresql/pass/${owner}" |
  70   ssh "$target" install -D -m 0400 -o root -g root /dev/stdin \
  71    /run/keys/postgresql_pass_${owner}
  72 '';
  73 }