WIP upgrade

[perso/Immae/Config/Nix.git] / modules / private / databases / postgresql.nix

diff --git a/modules/private/databases/postgresql.nix b/modules/private/databases/postgresql.nix
index 911a6d1b2a22ddfac33cf6965db78d7d3b619974..85112208a92e57cf9f0b90f4857d768742e6a68c 100644 (file)
--- a/modules/private/databases/postgresql.nix
+++ b/modules/private/databases/postgresql.nix
@@ -5,7 +5,7 @@ in {
   options.myServices.databases = {
     postgresql = {
       enable = lib.mkOption {
-        default = cfg.enable;
+        default = false;
         example = true;
         description = "Whether to enable postgresql database";
         type = lib.types.bool;
@@ -91,16 +91,6 @@ in {
           '';
         readOnly = true;
       };
-      systemdRuntimeDirectory = lib.mkOption {
-        type = lib.types.str;
-        # Use ReadWritePaths= instead if socketsDir is outside of /run
-        default = assert lib.strings.hasPrefix "/run/" cfg.socketsDir;
-          lib.strings.removePrefix "/run/" cfg.socketsDir;
-        description = ''
-        Adjusted Postgresql sockets directory for systemd
-        '';
-        readOnly = true;
-      };
     };
   };
 
@@ -108,9 +98,7 @@ in {
     networking.firewall.allowedTCPPorts = [ 5432 ];
 
     security.acme.certs."postgresql" = config.myServices.databasesCerts // {
-      user = "postgres";
       group = "postgres";
-      plugins = [ "fullchain.pem" "key.pem" "account_key.json" ];
       domain = "db-1.immae.eu";
       postRun = ''
         systemctl reload postgresql.service
@@ -119,35 +107,67 @@ in {
 
     systemd.services.postgresql.serviceConfig = {
       SupplementaryGroups = "keys";
-      RuntimeDirectory = cfg.systemdRuntimeDirectory;
+      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="psql --port=5432"
+
+      ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _: ''
+        $PSQL -tAc "SELECT 1 FROM pg_roles WHERE rolname='${role}'" \
+            | grep -q 1 \
+            || $PSQL -tAc 'CREATE USER "${role}" WITH REPLICATION'
+        '') cfg.replicationHosts)}
+
+      ${builtins.concatStringsSep "\n" (lib.mapAttrsToList (role: _:
+        let
+          sname = builtins.replaceStrings ["-"] ["_"] role;
+        in
+        ''
+        $PSQL -tAc "SELECT 1 FROM pg_replication_slots WHERE slot_name='${sname}'" \
+              | grep -q 1 \
+              || $PSQL -tAc "SELECT * FROM pg_create_physical_replication_slot('${sname}')"
+        '') cfg.replicationHosts)}
+      '';
+
     services.postgresql = {
       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'
-        ssl = on
-        ssl_cert_file = '${config.security.acme.directory}/postgresql/fullchain.pem'
-        ssl_key_file = '${config.security.acme.directory}/postgresql/key.pem'
-        '';
+      checkConfig = false;
+      settings = {
+        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";
+        # 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.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
         ));
@@ -166,9 +186,8 @@ in {
       '';
     };
 
-    secrets.keys = [
-      {
-        dest = "postgresql/pam";
+    secrets.keys = {
+      "postgresql/pam" = {
         permissions = "0400";
         group = "postgres";
         user = "postgres";
@@ -180,9 +199,8 @@ in {
           pam_filter ${filter}
           ssl start_tls
         '';
-      }
-      {
-        dest = "postgresql/pam_replication";
+      };
+      "postgresql/pam_replication" = {
         permissions = "0400";
         group = "postgres";
         user = "postgres";
@@ -194,27 +212,25 @@ in {
           pam_login_attribute cn
           ssl start_tls
         '';
-      }
-    ];
+      };
+    };
 
     security.pam.services = let
       pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
-    in [
-      {
-        name = "postgresql";
+    in {
+      postgresql = {
         text = ''
-          auth    required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
-          account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam
+          auth    required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
+          account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam"}
           '';
-      }
-      {
-        name = "postgresql_replication";
+      };
+      postgresql_replication = {
         text = ''
-          auth    required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication
-          account required ${pam_ldap} config=${config.secrets.location}/postgresql/pam_replication
+          auth    required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
+          account required ${pam_ldap} config=${config.secrets.fullPaths."postgresql/pam_replication"}
           '';
-      }
-    ];
+      };
+    };
   };
 }