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