Improve postgresql: allow replication from backup-1, change root to

/run/postgres

diff --git a/virtual/eldiron.nix b/virtual/eldiron.nix
index fa5cb5123c95b9b5609fddb3ffc03e77993c3562..2e4ae1272cfb46265b5b09d7d1ff1635e9f4cee5 100644 (file)
--- a/virtual/eldiron.nix
+++ b/virtual/eldiron.nix
@@ -4,6 +4,9 @@
     enableRollback = true;
   };
 
+  # Full backup:
+  # The star after /var/lib/* avoids deleting all folders in case of problem
+  # rsync -e "ssh -i /root/.ssh/id_charon_vpn" -aAXvz --delete --numeric-ids --super --rsync-path="sudo rsync" /var/lib/* immae@immae.eu:
   eldiron = { config, pkgs, mylibs, myconfig, ... }:
     with mylibs;
     let

diff --git a/virtual/modules/certificates.nix b/virtual/modules/certificates.nix
index a9d6d99e93ccf36d3696d57e0ad8b8dd37ed1632..1a630350b0d7b34b301ecbd3c18b3b7fc03f9cc3 100644 (file)
--- a/virtual/modules/certificates.nix
+++ b/virtual/modules/certificates.nix
@@ -23,7 +23,6 @@
       # it in httpd
       "eldiron" = config.services.myCertificates.certConfig // {
         domain = "eldiron.immae.eu";
-        allowKeysForGroup = true;
       };
     };
   };

diff --git a/virtual/modules/databases.nix b/virtual/modules/databases.nix
index 9f8e70da0b2958a17eb88873d73374f195df7976..de4ace64f228b22439b178cb8a7669c58f82956c 100644 (file)
--- a/virtual/modules/databases.nix
+++ b/virtual/modules/databases.nix
@@ -42,6 +42,11 @@ in {
           url = "mirror://postgresql/source/v11.1/${name}.tar.bz2";
           sha256 = "026v0sicsh7avzi45waf8shcbhivyxmi7qgn9fd1x0vl520mx0ch";
         };
+        configureFlags = old.configureFlags ++ [ "--with-pam" ];
+        buildInputs = (old.buildInputs or []) ++ [ pkgs.pam ];
+        patches = old.patches ++ [
+          ./postgresql_run_socket_path.patch
+        ];
       });
       mariadb = mariadbPAM;
       mariadbPAM = oldpkgs.mariadb.overrideAttrs(old: rec {
@@ -62,9 +67,23 @@ in {
       package = pkgs.mariadb;
     };
 
+    # Cannot use eldiron: psql complains too much rights on the key, and
+    # setfacl cannot work properly because of acme prestart script
+    security.acme.certs."postgresql" = config.services.myCertificates.certConfig // {
+      user = "postgres";
+      group = "postgres";
+      plugins = [ "fullchain.pem" "key.pem" "account_key.json" ];
+      domain = "db-1.immae.eu";
+      postRun = ''
+        systemctl reload postgresql.service
+      '';
+    };
+
+    system.activationScripts.postgresql = ''
+      install -m 0755 -o postgres -g postgres -d /run/postgresql
+      '';
+
     # FIXME: initial sync
-    # FIXME: backup
-    # FIXME: ssl
     services.postgresql = rec {
       enable = cfg.postgresql.enable;
       package = pkgs.postgresql;
@@ -83,17 +102,19 @@ in {
         lc_numeric = 'en_US.UTF-8'
         lc_time = 'en_US.UTF-8'
         default_text_search_config = 'pg_catalog.english'
-        # ssl = on
-        # ssl_cert_file = '/var/lib/acme/eldiron/fullchain.pem'
-        # ssl_key_file = '/var/lib/acme/eldiron/key.pem'
+        ssl = on
+        ssl_cert_file = '/var/lib/acme/postgresql/fullchain.pem'
+        ssl_key_file = '/var/lib/acme/postgresql/key.pem'
         '';
       authentication = ''
         local  all     postgres                                ident
         local  all     all                                     md5
-        host   all     all             samehost                md5
-        host   all     all             178.33.252.96/32        md5
-        host   all     all             188.165.209.148/32      md5
-        #host  all     all             all                     pam
+        hostssl        all     all             samehost                md5
+        hostssl        all     all             178.33.252.96/32        md5
+        hostssl        all     all             188.165.209.148/32      md5
+        hostssl        all     all             all                     pam
+        hostssl        replication     backup-1        2001:41d0:302:1100::9:e5a9/128  pam pamservice=postgresql_replication
+        hostssl        replication     backup-1        54.37.151.137/32                pam pamservice=postgresql_replication
       '';
     };
 
@@ -107,6 +128,14 @@ in {
         bindpw ${builtins.getEnv "NIXOPS_MYSQL_PAM_PASSWORD"}
         pam_filter memberOf=cn=users,cn=mysql,cn=pam,ou=services,dc=immae,dc=eu
         '';
+      pam_ldap_postgresql_replication = assert mylibs.checkEnv "NIXOPS_ELDIRON_LDAP_PASSWORD";
+              pkgs.writeText "postgresql.conf" ''
+        host ldap.immae.eu
+        base dc=immae,dc=eu
+        binddn cn=eldiron,ou=hosts,dc=immae,dc=eu
+        bindpw ${builtins.getEnv "NIXOPS_ELDIRON_LDAP_PASSWORD"}
+        pam_login_attribute cn
+        '';
     in [
       {
         name = "mysql";
@@ -116,6 +145,20 @@ in {
           account required ${pam_ldap}/lib/security/pam_ldap.so config=${pam_ldap_mysql}
           '';
       }
+      {
+        name = "postgresql";
+        text = ''
+          auth    required ${pam_ldap}/lib/security/pam_ldap.so config=${pam_ldap_postgresql_replication}
+          account required ${pam_ldap}/lib/security/pam_ldap.so config=${pam_ldap_postgresql_replication}
+          '';
+      }
+      {
+        name = "postgresql_replication";
+        text = ''
+          auth    required ${pam_ldap}/lib/security/pam_ldap.so config=${pam_ldap_postgresql_replication}
+          account required ${pam_ldap}/lib/security/pam_ldap.so config=${pam_ldap_postgresql_replication}
+          '';
+      }
     ];
 
     # FIXME: backup

diff --git a/virtual/modules/postgresql_run_socket_path.patch b/virtual/modules/postgresql_run_socket_path.patch
new file mode 100644 (file)
index 0000000..b558c7b
--- /dev/null
+++ b/virtual/modules/postgresql_run_socket_path.patch
@@ -0,0 +1,12 @@
+diff -Naur postgresql-9.2.0.sockets/src/include/pg_config_manual.h postgresql-9.2.0/src/include/pg_config_manual.h
+--- postgresql-9.2.0.sockets/src/include/pg_config_manual.h    2012-09-06 17:26:17.000000000 -0400
++++ postgresql-9.2.0/src/include/pg_config_manual.h    2012-09-06 18:13:18.183092471 -0400
+@@ -144,7 +144,7 @@
+  * here's where to twiddle it.  You can also override this at runtime
+  * with the postmaster's -k switch.
+  */
+-#define DEFAULT_PGSOCKET_DIR  "/tmp"
++#define DEFAULT_PGSOCKET_DIR  "/run/postgresql"
+ 
+ /*
+  * The random() function is expected to yield values between 0 and

diff --git a/virtual/packages/nextcloud.nix b/virtual/packages/nextcloud.nix
index 5e9a927214b163de3ced56a2b2c249fc19f4f0ef..3ac71e09e5e880ada39e4f1f34c6fd2dd4b38571 100644 (file)
--- a/virtual/packages/nextcloud.nix
+++ b/virtual/packages/nextcloud.nix
@@ -115,7 +115,7 @@ let
         'dbtype' => 'pgsql',
         'version' => '15.0.0.10',
         'dbname' => 'webapps',
-        'dbhost' => '/tmp',
+        'dbhost' => '/run/postgresql',
         'dbtableprefix' => 'oc_',
         'dbuser' => '${builtins.getEnv "NIXOPS_NEXTCLOUD_DB_USER"}',
         'dbpassword' => '${builtins.getEnv "NIXOPS_NEXTCLOUD_DB_PASSWORD"}',