hosts/nan2gua1/postgresql/enfants.nix

   1 {
   2   pkgs,
   3   lib,
   4   config,
   5   ...
   6 }:
   7 let
   8   db = "enfants";
   9   owner = "enfants";
  10   passwordFile = enfants/passwordFile.clear;
  11   inherit (config.users) users groups;
  12   inherit (config) networking;
  13   # To be used in postStart when resetting the database
  14   drop = ''
  15     $PSQL -d template1 -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
  16       DROP OWNED BY ${owner};
  17       DROP DATABASE ${db};
  18       DROP ROLE     ${owner};
  19     EOF
  20   '';
  21 in
  22 {
  23   services.postgresql = {
  24     authentication = lib.mkForce ''
  25       # CONNECTION  DATABASE USER      AUTH  OPTIONS
  26       # FIXME: using scram-sha-256 instead of md5 requires postfix >= 11
  27       #hostssl       ${db}    ${owner}  all   md5
  28       local         all      postgres  peer  map=admin
  29       local         samerole all       peer  map=user
  30     '';
  31     identMap = ''
  32       # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
  33       user       root             ${owner}
  34       user       pgadmin          ${owner}
  35       user       julm             ${owner}
  36       user       ${owner}         ${db}
  37     '';
  38   };
  39   systemd.services.postgresql = {
  40     postStart = lib.mkAfter ''
  41       connection_limit=64 \
  42       encoding=UTF8 \
  43       lc_collate=fr_FR.UTF-8 \
  44       lc_type=fr_FR.UTF-8 \
  45       owner=${owner} \
  46       pass=$(cat ${passwordFile}) \
  47       pg_createdb ${db} >/dev/null
  48
  49       $PSQL -d "${db}" -AqtX --set ON_ERROR_STOP=1 -f - <<EOF
  50         -- Reallow this to avoid the error:
  51         -- "Couldn't refresh the graph"
  52         -- when testing the connexion to the database
  53         -- in OpenConcerto-Configuration.sh
  54         GRANT SELECT ON pg_catalog.pg_settings TO ${owner};
  55         -- Reallow this to allow pg_dump
  56         GRANT SELECT ON pg_catalog.pg_database   TO ${owner};
  57         GRANT SELECT ON pg_catalog.pg_roles      TO ${owner};
  58         GRANT SELECT ON pg_catalog.pg_tablespace TO ${owner};
  59         -- Reallow this to allow pgadmin3
  60         GRANT SELECT ON pg_catalog.pg_user       TO ${owner};
  61
  62         -- Enable PL/PGSQL
  63         CREATE OR REPLACE LANGUAGE plpgsql;
  64       EOF
  65     '';
  66   };
  67 }