servers/losurdo/postgresql/openconcerto.nix

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