X-Git-Url: https://git.immae.eu/?p=perso%2FImmae%2FConfig%2FNix.git;a=blobdiff_plain;f=systems%2Feldiron%2Fdatabases%2Fpostgresql.nix;fp=modules%2Fprivate%2Fdatabases%2Fpostgresql.nix;h=721059a702097e70913b373810e36067cd5887d8;hp=a6c4cc998fdb138ccf53671992a916794b6eff95;hb=1a64deeb894dc95e2645a75771732c6cc53a79ad;hpb=fa25ffd4583cc362075cd5e1b4130f33306103f0

diff --git a/modules/private/databases/postgresql.nix b/systems/eldiron/databases/postgresql.nix
similarity index 81%
rename from modules/private/databases/postgresql.nix
rename to systems/eldiron/databases/postgresql.nix
index a6c4cc9..721059a 100644
--- a/modules/private/databases/postgresql.nix
+++ b/systems/eldiron/databases/postgresql.nix
@@ -97,8 +97,7 @@ in {
   config = lib.mkIf cfg.enable {
     networking.firewall.allowedTCPPorts = [ 5432 ];
 
-    security.acme.certs."postgresql" = config.myServices.databasesCerts // {
-      user = "postgres";
+    security.acme.certs."postgresql" = {
       group = "postgres";
       domain = "db-1.immae.eu";
       postRun = ''
@@ -108,10 +107,17 @@ in {
 
     systemd.services.postgresql.serviceConfig = {
       SupplementaryGroups = "keys";
+      ExecStartPre = [ ("+" + (pkgs.writeShellScript "postgresql-fix-cert" ''
+          # postgresql complains:
+          # private key file "${config.security.acme.certs.postgresql.directory}/key.pem" must be owned by the database user or root
+          cp -f "${config.security.acme.certs.postgresql.directory}/key.pem" "${config.services.postgresql.dataDir}/key.pem"
+          chown postgres:postgres "${config.services.postgresql.dataDir}/key.pem"
+          chmod go-r "${config.services.postgresql.dataDir}/key.pem"
+      '')) ];
     };
     systemd.services.postgresql.postStart = lib.mkAfter ''
       # This line is already defined in 19.09
-      PSQL="${pkgs.sudo}/bin/sudo -u postgres psql --port=5432"
+      PSQL="psql --port=5432"
 
       ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
         $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
@@ -134,32 +140,34 @@ in {
       enable = true;
       package = cfg.package;
       enableTCPIP = true;
-      extraConfig = ''
-        max_connections = 100
-        wal_level = logical
-        shared_buffers = 512MB
-        work_mem = 10MB
-        max_wal_size = 1GB
-        min_wal_size = 80MB
-        log_timezone = 'Europe/Paris'
-        datestyle = 'iso, mdy'
-        timezone = 'Europe/Paris'
-        lc_messages = 'en_US.UTF-8'
-        lc_monetary = 'en_US.UTF-8'
-        lc_numeric = 'en_US.UTF-8'
-        lc_time = 'en_US.UTF-8'
-        default_text_search_config = 'pg_catalog.english'
+      checkConfig = false;
+      logLinePrefix = "%h %q%u@%d "; # Default: '%m [%p] ', already stored independently by journald. %h needed for fail2ban
+      settings = {
+        max_connections = 300;
+        wal_level = "logical";
+        shared_buffers = "512MB";
+        work_mem = "10MB";
+        max_wal_size = "1GB";
+        min_wal_size = "80MB";
+        log_timezone = "Europe/Paris";
+        datestyle = "iso, mdy";
+        timezone = "Europe/Paris";
+        lc_messages = "en_US.UTF-8";
+        lc_monetary = "en_US.UTF-8";
+        lc_numeric = "en_US.UTF-8";
+        lc_time = "en_US.UTF-8";
+        default_text_search_config = "pg_catalog.english";
         # this introduces a small delay before storing on disk, but
         # makes it order of magnitudes quicker
-        synchronous_commit = off
-        ssl = on
-        ssl_cert_file = '${config.security.acme.certs.postgresql.directory}/fullchain.pem'
-        ssl_key_file = '${config.security.acme.certs.postgresql.directory}/key.pem'
-        '';
+        synchronous_commit = "off";
+        ssl = "on";
+        ssl_cert_file = "${config.security.acme.certs.postgresql.directory}/fullchain.pem";
+        ssl_key_file = "${config.services.postgresql.dataDir}/key.pem";
+      };
       authentication = let
         hosts = builtins.concatStringsSep "\n" (
           lib.lists.flatten (lib.mapAttrsToList (k: vs: map (v:
-            map (ip6: "hostssl	${v.database}	${v.username}	${ip6}/128	${v.method}") v.ip6
+            map (ip6: "hostssl	${v.database}	${v.username}	${ip6}	${v.method}") v.ip6
             ++ map (ip4: "hostssl	${v.database}	${v.username}	${ip4}/32		${v.method}") v.ip4
           ) vs) cfg.authorizedHosts
         ));