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.mkAfter ''
  25       # CONNECTION  DATABASE USER      AUTH  OPTIONS
  26       #hostssl       ${db}    ${owner}  all   scram-sha-256
  27     '';
  28     identMap = lib.mkAfter ''
  29       # MAPNAME  SYSTEM-USERNAME  PG-USERNAME
  30       user       root             ${owner}
  31       user       pgadmin          ${owner}
  32       user       julm             ${owner}
  33       user       ${owner}         ${db}
  34     '';
  35   };
  36   systemd.services.postgresql = {
  37     postStart = lib.mkAfter ''
  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 ${passwordFile}) \
  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         -- Reallow this to allow pg_dump
  53         GRANT SELECT ON pg_catalog.pg_database   TO ${owner};
  54         GRANT SELECT ON pg_catalog.pg_roles      TO ${owner};
  55         GRANT SELECT ON pg_catalog.pg_tablespace TO ${owner};
  56         -- Reallow this to allow pgadmin3
  57         GRANT SELECT ON pg_catalog.pg_user       TO ${owner};
  58
  59         -- Enable PL/PGSQL
  60         CREATE OR REPLACE LANGUAGE plpgsql;
  61       EOF
  62     '';
  63   };
  64 }