Fix the SSL state for databases connections

Whenever possible, we use a socket connexion (all postgresql
connections, and a few mysql ones)

When remote (only mysql), we require SSL in the users database (cannot
be enforced globally)

Also, put pam configurations in a correct state

Fixes https://git.immae.eu/mantisbt/view.php?id=89
Fixes https://git.immae.eu/mantisbt/view.php?id=90
Fixes https://git.immae.eu/mantisbt/view.php?id=88

1 files changed, 39 insertions, 10 deletions

diff --git a/nixops/modules/databases/default.nix b/nixops/modules/databases/default.nix
index 94d8d75..d86373a 100644
--- a/nixops/modules/databases/default.nix
+++ b/nixops/modules/databases/default.nix
@@ -57,9 +57,21 @@ in {
 
     networking.firewall.allowedTCPPorts = [ 3306 5432 ];
 
+    # for adminer, ssl is implemented with mysqli only, which is
+    # currently disabled because it’s not compatible with pam.
+    # Thus we need to generate two users for each 'remote': one remote
+    # with SSL, and one localhost without SSL.
+    # User identified by LDAP:
+    # CREATE USER foo@% IDENTIFIED VIA pam USING 'mysql' REQUIRE SSL;
+    # CREATE USER foo@localhost IDENTIFIED VIA pam USING 'mysql';
     services.mysql = rec {
       enable = cfg.mariadb.enable;
       package = pkgs.mariadb;
+      extraOptions = ''
+        ssl_ca = ${pkgs.cacert}/etc/ssl/certs/ca-bundle.crt
+        ssl_key = /var/lib/acme/mysql/key.pem
+        ssl_cert = /var/lib/acme/mysql/fullchain.pem
+        '';
     };
 
     security.acme.certs."postgresql" = config.services.myCertificates.certConfig // {
@@ -72,6 +84,16 @@ in {
       '';
     };
 
+    security.acme.certs."mysql" = config.services.myCertificates.certConfig // {
+      user = "mysql";
+      group = "mysql";
+      plugins = [ "fullchain.pem" "key.pem" "account_key.json" ];
+      domain = "db-1.immae.eu";
+      postRun = ''
+        systemctl restart mysql.service
+      '';
+    };
+
     system.activationScripts.postgresql = ''
       install -m 0755 -o postgres -g postgres -d ${myconfig.env.databases.postgresql.socket}
       '';
@@ -101,9 +123,6 @@ in {
       authentication = ''
         local   all     postgres                                ident
         local   all     all                                     md5
-        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
@@ -112,21 +131,31 @@ in {
 
     security.pam.services = let
       pam_ldap = "${pkgs.pam_ldap}/lib/security/pam_ldap.so";
-      pam_ldap_mysql = pkgs.writeText "mysql.conf" ''
+      pam_ldap_mysql = with myconfig.env.databases.mysql.pam;
+        pkgs.writeText "mysql.conf" ''
         host ${myconfig.env.ldap.host}
         base ${myconfig.env.ldap.base}
-        binddn cn=mysql,cn=pam,ou=services,dc=immae,dc=eu
-        bindpw ${myconfig.env.databases.mysql.pam_password}
+        binddn ${dn}
+        bindpw ${password}
+        pam_filter ${filter}
+        ssl start_tls
+        '';
+      pam_ldap_postgresql = with myconfig.env.databases.postgresql.pam;
+        pkgs.writeText "postgresql.conf" ''
+        host ${myconfig.env.ldap.host}
+        base ${myconfig.env.ldap.base}
+        binddn ${dn}
+        bindpw ${password}
+        pam_filter ${filter}
         ssl start_tls
-        pam_filter memberOf=cn=users,cn=mysql,cn=pam,ou=services,dc=immae,dc=eu
         '';
       pam_ldap_postgresql_replication = pkgs.writeText "postgresql.conf" ''
         host ${myconfig.env.ldap.host}
         base ${myconfig.env.ldap.base}
         binddn ${myconfig.env.ldap.host_dn}
         bindpw ${myconfig.env.ldap.password}
-        ssl start_tls
         pam_login_attribute cn
+        ssl start_tls
         '';
     in [
       {
@@ -140,8 +169,8 @@ in {
       {
         name = "postgresql";
         text = ''
-          auth    required ${pam_ldap} config=${pam_ldap_postgresql_replication}
-          account required ${pam_ldap} config=${pam_ldap_postgresql_replication}
+          auth    required ${pam_ldap} config=${pam_ldap_postgresql}
+          account required ${pam_ldap} config=${pam_ldap_postgresql}
           '';
       }
       {