machines/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) gnupg;
   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     user       ${owner}         ${db}
  45   '';
  46 };
  47 security.gnupg.secrets."postgresql/pass/${owner}" = {};
  48 systemd.services.postgresql = {
  49   after = [ gnupg.secrets."postgresql/pass/${owner}".service ];
  50   wants = [ gnupg.secrets."postgresql/pass/${owner}".service ];
  51   postStart = lib.mkAfter ''
  52     sed -e 's/ \(TO\|FROM\) \+openconcerto/ \1 ${owner}/g' \
  53      ${sql}/OpenConcerto-1.6.3.sql |
  54     connection_limit=64 \
  55     encoding=UTF8 \
  56     lc_collate=fr_FR.UTF-8 \
  57     lc_type=fr_FR.UTF-8 \
  58     owner=${owner} \
  59     pass=$(cat ${gnupg.secrets."postgresql/pass/${owner}".path}) \
  60     pg_createdb ${db} >/dev/null
  61
  62     $PSQL -d "${db}" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
  63       -- Reallow this to avoid the error:
  64       -- "Couldn't refresh the graph"
  65       -- when testing the connexion to the database
  66       -- in OpenConcerto-Configuration.sh
  67       GRANT SELECT ON pg_catalog.pg_settings TO ${owner};
  68       -- Reallow this to allow pg_dump
  69       GRANT SELECT ON pg_catalog.pg_database   TO ${owner};
  70       GRANT SELECT ON pg_catalog.pg_roles      TO ${owner};
  71       GRANT SELECT ON pg_catalog.pg_tablespace TO ${owner};
  72       -- Reallow this to allow pgadmin3
  73       GRANT SELECT ON pg_catalog.pg_user       TO ${owner};
  74
  75       -- Enable PL/PGSQL
  76       CREATE OR REPLACE LANGUAGE plpgsql;
  77     EOF
  78   '';
  79 };
  80 }