From 7ebcaad53a3261d8a4aefd8a64c5c7d9d8ac2fa0 Mon Sep 17 00:00:00 2001 From: =?utf8?q?Isma=C3=ABl=20Bouya?= Date: Sat, 26 Jan 2019 14:51:19 +0100 Subject: [PATCH] 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 --- nixops/modules/databases/default.nix | 49 +++++++++++++++---- nixops/modules/websites/chloe/chloe.nix | 3 +- .../chloe/chloe_config_dev/connect.php | 11 ++++- .../chloe/chloe_config_prod/connect.php | 11 ++++- .../connexionswing/connexionswing.nix | 4 +- .../websites/ludivine/ludivinecassal.nix | 4 +- .../websites/piedsjaloux/piedsjaloux.nix | 4 +- .../websites/tellesflorian/tellesflorian.nix | 4 +- nixops/modules/websites/tools/dav/davical.nix | 2 +- .../websites/tools/diaspora/diaspora.nix | 8 +-- .../websites/tools/git/mantisbt/mantisbt.nix | 6 +-- nixops/modules/websites/tools/tools/ttrss.nix | 8 +-- .../modules/websites/tools/tools/yourls.nix | 4 +- 13 files changed, 83 insertions(+), 35 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} ''; } { diff --git a/nixops/modules/websites/chloe/chloe.nix b/nixops/modules/websites/chloe/chloe.nix index 355cca7..9752db6 100644 --- 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 2e4439f..18b0933 100644 --- 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 2e4439f..18b0933 100644 --- 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 f394574..a9ee2ba 100644 --- 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 eff0bf8..e17a64e 100644 --- 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 1b53c4a..52838c6 100644 --- 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 4237af8..41be4b0 100644 --- 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 4d0639f..3f43607 100644 --- 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" '' 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 798ebe6..765c0a5 100644 --- 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 bc2ff3a..c6c3bff 100644 --- 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" ''