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

diff --git a/nixops/modules/databases/default.nix b/nixops/modules/databases/default.nix
index 94d8d75ededd5c134c58fd75c56a2d2a585b56f6..d86373ade13f9fe71d02db895856ca2125ba8842 100644 (file)
--- 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}
           '';
       }
       {

diff --git a/nixops/modules/websites/chloe/chloe.nix b/nixops/modules/websites/chloe/chloe.nix
index 355cca7cb79fcf67a6771c374e1d5829e49ad88b..9752db63b7f81c059100e2875a4ae3a2c97ca25a 100644 (file)
--- a/nixops/modules/websites/chloe/chloe.nix
+++ b/nixops/modules/websites/chloe/chloe.nix
@@ -23,7 +23,8 @@ let
         env[SPIP_LDAP_SEARCH_DN] = "${config.ldap.dn}"
         env[SPIP_LDAP_SEARCH_PW] = "${config.ldap.password}"
         env[SPIP_LDAP_SEARCH] = "${config.ldap.search}"
-        env[SPIP_MYSQL_HOST] = "db-1.immae.eu"
+        env[SPIP_MYSQL_HOST] = "${config.mysql.host}"
+        env[SPIP_MYSQL_PORT] = "${config.mysql.port}"
         env[SPIP_MYSQL_DB] = "${config.mysql.name}"
         env[SPIP_MYSQL_USER] = "${config.mysql.user}"
         env[SPIP_MYSQL_PASSWORD] = "${config.mysql.password}"

diff --git a/nixops/modules/websites/chloe/chloe_config_dev/connect.php b/nixops/modules/websites/chloe/chloe_config_dev/connect.php
index 2e4439f934762f0eac96309c661ed7d710cfd73e..18b09330042a6945fb0f1eef04d5a554b021a56f 100644 (file)
--- a/nixops/modules/websites/chloe/chloe_config_dev/connect.php
+++ b/nixops/modules/websites/chloe/chloe_config_dev/connect.php
@@ -2,5 +2,14 @@
 if (!defined("_ECRIRE_INC_VERSION")) return;
 define('_MYSQL_SET_SQL_MODE',true);
 $GLOBALS['spip_connect_version'] = 0.7;
-spip_connect_db(getenv("SPIP_MYSQL_HOST"),'',getenv("SPIP_MYSQL_USER"),getenv("SPIP_MYSQL_PASSWORD"),getenv("SPIP_MYSQL_DB"),'mysql', 'spip','ldap.php');
+spip_connect_db(
+  getenv("SPIP_MYSQL_HOST"),
+  getenv("SPIP_MYSQL_PORT"),
+  getenv("SPIP_MYSQL_USER"),
+  getenv("SPIP_MYSQL_PASSWORD"),
+  getenv("SPIP_MYSQL_DB"),
+  'mysql',
+  'spip',
+  'ldap.php'
+);
 ?>

diff --git a/nixops/modules/websites/chloe/chloe_config_prod/connect.php b/nixops/modules/websites/chloe/chloe_config_prod/connect.php
index 2e4439f934762f0eac96309c661ed7d710cfd73e..18b09330042a6945fb0f1eef04d5a554b021a56f 100644 (file)
--- a/nixops/modules/websites/chloe/chloe_config_prod/connect.php
+++ b/nixops/modules/websites/chloe/chloe_config_prod/connect.php
@@ -2,5 +2,14 @@
 if (!defined("_ECRIRE_INC_VERSION")) return;
 define('_MYSQL_SET_SQL_MODE',true);
 $GLOBALS['spip_connect_version'] = 0.7;
-spip_connect_db(getenv("SPIP_MYSQL_HOST"),'',getenv("SPIP_MYSQL_USER"),getenv("SPIP_MYSQL_PASSWORD"),getenv("SPIP_MYSQL_DB"),'mysql', 'spip','ldap.php');
+spip_connect_db(
+  getenv("SPIP_MYSQL_HOST"),
+  getenv("SPIP_MYSQL_PORT"),
+  getenv("SPIP_MYSQL_USER"),
+  getenv("SPIP_MYSQL_PASSWORD"),
+  getenv("SPIP_MYSQL_DB"),
+  'mysql',
+  'spip',
+  'ldap.php'
+);
 ?>

diff --git a/nixops/modules/websites/connexionswing/connexionswing.nix b/nixops/modules/websites/connexionswing/connexionswing.nix
index f3945748764e98c1eef1036053913b38609da780..a9ee2bab0e7b857e6094c6d506b525593d0e5b70 100644 (file)
--- a/nixops/modules/websites/connexionswing/connexionswing.nix
+++ b/nixops/modules/websites/connexionswing/connexionswing.nix
@@ -7,8 +7,8 @@ let
       writeText "parameters.yml" ''
         # This file is auto-generated during the composer install
         parameters:
-            database_host: db-1.immae.eu
-            database_port: null
+            database_host: ${config.mysql.host}
+            database_port: ${config.mysql.port}
             database_name: ${config.mysql.name}
             database_user: ${config.mysql.user}
             database_password: ${config.mysql.password}

diff --git a/nixops/modules/websites/ludivine/ludivinecassal.nix b/nixops/modules/websites/ludivine/ludivinecassal.nix
index eff0bf8f762ed34104fb825e56239df578e6b5d6..e17a64eed0dbdfa09f936e2f04f1a3831155a090 100644 (file)
--- a/nixops/modules/websites/ludivine/ludivinecassal.nix
+++ b/nixops/modules/websites/ludivine/ludivinecassal.nix
@@ -7,8 +7,8 @@ let
       writeText "parameters.yml" ''
         # This file is auto-generated during the composer install
         parameters:
-            database_host: db-1.immae.eu
-            database_port: null
+            database_host: ${config.mysql.host}
+            database_port: ${config.mysql.port}
             database_name: ${config.mysql.name}
             database_user: ${config.mysql.user}
             database_password: ${config.mysql.password}

diff --git a/nixops/modules/websites/piedsjaloux/piedsjaloux.nix b/nixops/modules/websites/piedsjaloux/piedsjaloux.nix
index 1b53c4a5022e0531ca3cf2591512aa869d2b53c7..52838c69f93ccae2126b069caf38432d79b0fceb 100644 (file)
--- a/nixops/modules/websites/piedsjaloux/piedsjaloux.nix
+++ b/nixops/modules/websites/piedsjaloux/piedsjaloux.nix
@@ -7,8 +7,8 @@ let
       writeText "parameters.yml" ''
         # This file is auto-generated during the composer install
         parameters:
-            database_host: db-1.immae.eu
-            database_port: null
+            database_host: ${config.mysql.host}
+            database_port: ${config.mysql.port}
             database_name: ${config.mysql.name}
             database_user: ${config.mysql.user}
             database_password: ${config.mysql.password}

diff --git a/nixops/modules/websites/tellesflorian/tellesflorian.nix b/nixops/modules/websites/tellesflorian/tellesflorian.nix
index 4237af859532d874c5823576e73cd61786e85101..41be4b04e38c134703f10a73936a77e2a9e8919f 100644 (file)
--- a/nixops/modules/websites/tellesflorian/tellesflorian.nix
+++ b/nixops/modules/websites/tellesflorian/tellesflorian.nix
@@ -7,8 +7,8 @@ let
       writeText "parameters.yml" ''
         # This file is auto-generated during the composer install
         parameters:
-            database_host: db-1.immae.eu
-            database_port: null
+            database_host: ${config.mysql.host}
+            database_port: ${config.mysql.port}
             database_name: ${config.mysql.name}
             database_user: ${config.mysql.user}
             database_password: ${config.mysql.password}

diff --git a/nixops/modules/websites/tools/dav/davical.nix b/nixops/modules/websites/tools/dav/davical.nix
index 4d0639f3ed6346384bccc1cc9d906655277bfecc..3f43607312b740008ccde40da0540a5991e75360 100644 (file)
--- a/nixops/modules/websites/tools/dav/davical.nix
+++ b/nixops/modules/websites/tools/dav/davical.nix
@@ -18,7 +18,7 @@ let
   davical = rec {
     config = writeText "davical_config.php" ''
         <?php
-        $c->pg_connect[] = "dbname=davical user=davical_app host=db-1.immae.eu password=${env.postgresql.password}";
+        $c->pg_connect[] = "dbname=${env.postgresql.database} user=${env.postgresql.user} host=${env.postgresql.socket} password=${env.postgresql.password}";
 
         $c->readonly_webdav_collections = false;
 

diff --git a/nixops/modules/websites/tools/diaspora/diaspora.nix b/nixops/modules/websites/tools/diaspora/diaspora.nix
index 798ebe6707d22a4b6245a8b3e0815710d3e7f77e..765c0a51ad2a8c5f09a92d5d1b9b36f1c9df0ca3 100644 (file)
--- a/nixops/modules/websites/tools/diaspora/diaspora.nix
+++ b/nixops/modules/websites/tools/diaspora/diaspora.nix
@@ -99,9 +99,9 @@ let
   database_config = writeText "database.yml" ''
       postgresql: &postgresql
         adapter: postgresql
-        host: db-1.immae.eu
-        port: 5432
-        username: "diaspora"
+        host: "${env.postgresql.socket}"
+        port: "${env.postgresql.port}"
+        username: "${env.postgresql.user}"
         password: "${env.postgresql.password}"
         encoding: unicode
       common: &common
@@ -113,7 +113,7 @@ let
         database: diaspora_development
       production:
         <<: *combined
-        database: diaspora
+        database: ${env.postgresql.database}
       test:
         <<: *combined
         database: "diaspora_test"

diff --git a/nixops/modules/websites/tools/git/mantisbt/mantisbt.nix b/nixops/modules/websites/tools/git/mantisbt/mantisbt.nix
index bc2ff3a1127f1cc0f37a6b02794bbb246383688e..c6c3bff60ea5a31c8bb553753046210f6907a8bb 100644 (file)
--- a/nixops/modules/websites/tools/git/mantisbt/mantisbt.nix
+++ b/nixops/modules/websites/tools/git/mantisbt/mantisbt.nix
@@ -20,10 +20,10 @@ let
     config = 
       writeText "config_inc.php" ''
       <?php
-      $g_hostname              = 'db-1.immae.eu';
-      $g_db_username           = 'mantisbt';
+      $g_hostname              = '${env.postgresql.socket}';
+      $g_db_username           = '${env.postgresql.user}';
       $g_db_password           = '${env.postgresql.password}';
-      $g_database_name         = 'mantisbt';
+      $g_database_name         = '${env.postgresql.database}';
       $g_db_type               = 'pgsql';
       $g_crypto_master_salt    = '${env.master_salt}';
       $g_allow_signup          = OFF;

diff --git a/nixops/modules/websites/tools/tools/ttrss.nix b/nixops/modules/websites/tools/tools/ttrss.nix
index 76105be615c35f67aff5602700d6c46db16d80fb..95cca9d68f819eb20860874e0f102304abf2081d 100644 (file)
--- a/nixops/modules/websites/tools/tools/ttrss.nix
+++ b/nixops/modules/websites/tools/tools/ttrss.nix
@@ -66,11 +66,11 @@ let
         define('MYSQL_CHARSET', 'UTF8');
 
         define('DB_TYPE', 'pgsql');
-        define('DB_HOST', 'db-1.immae.eu');
-        define('DB_USER', 'ttrss');
-        define('DB_NAME', 'ttrss');
+        define('DB_HOST', '${env.postgresql.socket}');
+        define('DB_USER', '${env.postgresql.user}');
+        define('DB_NAME', '${env.postgresql.database}');
         define('DB_PASS', '${env.postgresql.password}');
-        define('DB_PORT', '5432');
+        define('DB_PORT', '${env.postgresql.port}');
 
         define('AUTH_AUTO_CREATE', true);
         define('AUTH_AUTO_LOGIN', true);

diff --git a/nixops/modules/websites/tools/tools/yourls.nix b/nixops/modules/websites/tools/tools/yourls.nix
index b97dac997ac2c207da301a3992248945f572a6d4..66dd2fd22c976a1626226976692f5acb8135bba5 100644 (file)
--- a/nixops/modules/websites/tools/tools/yourls.nix
+++ b/nixops/modules/websites/tools/tools/yourls.nix
@@ -18,9 +18,9 @@ let
         define( 'YOURLS_DB_USER', '${env.mysql.user}' );
         define( 'YOURLS_DB_PASS', '${env.mysql.password}' );
         define( 'YOURLS_DB_NAME', '${env.mysql.database}' );
-        define( 'YOURLS_DB_HOST', 'db-1.immae.eu' );
+        define( 'YOURLS_DB_HOST', '${env.mysql.host}' );
         define( 'YOURLS_DB_PREFIX', 'yourls_' );
-        define( 'YOURLS_SITE', 'http://tools.immae.eu/url' );
+        define( 'YOURLS_SITE', 'https://tools.immae.eu/url' );
         define( 'YOURLS_HOURS_OFFSET', 0 ); 
         define( 'YOURLS_LANG', ''' ); 
         define( 'YOURLS_UNIQUE_URLS', true );