hosts/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 }