PFS / Letsencrypt for Postfix/Dovecot/PureFTPd/mariadb

Note that ssl=on will force ssl for mysql connections, which is very useful for multi-server installs to implement ssl to the master server, but it will break any multi-server installs unless you're running the latest 3.1dev, which added back support for mysql connection flags. I believe, though as yet untested, that requires adding these settings to
`/usr/local/ispconfig/server/lib/config.inc.local.php`:

Code:

<?php
# uncomment if local mysql db uses ssl (If so, use '127.0.0.1', not 'localhost')
# $conf['db_client_flags'] = MYSQL_CLIENT_SSL;
$conf['dbmaster_client_flags']  = MYSQL_CLIENT_SSL;

That should be done on all slave servers (the settings are also in config.inc.php files, though I don't know if changes persist through updates, so config.inc.local.php is safer.)

I was actually just working on a tutorial that includes this very configuration setup, and should have the above tested/verified pretty soon.

 

I'll need to test it again to refresh my memory, but I tried using letsencrypt certificates with mysql some time ago and found everything worked or could be configured to work except the php connections from slave servers. Maybe it wasn't "ssl=on" that made it required, but something did (https://dev.mysql.com/doc/refman/5.7/en/encrypted-connections.html sounds like it should be 'require_secure_transport', though I don't remember setting that). If you don't see it as required, maybe it's just a difference in mariadb versions? (I was probably on debian 8 when I set that up originally.)

 

you might include:

Code:

openssl dhparam -out /var/lib/dovecot/ssl-parameters.tmp 2048 && \
        mv /var/lib/dovecot/ssl-parameters.tmp /var/lib/dovecot/ssl-parameters.dat

 

I've been working on/testing ssl in mariadb on debian 9, and found the following as of right now:

• as relates to my previous comments, I think my memory was off, and I did not have ssl required, but rather I wanted to make it required and could not because slave server connections wouldn't work with ssl
• if you need ssl for slave server connections (a good idea if you have them), it should work in git master branch right now, but does not in 3.1.12 nor in git stable-3.1 branch; there is a merge request to correct that, so hopefully will be available soon
• ssl=on should not be needed, that's the default when you specify certificates
• you cannot use ssl-cipher=TLSv1.2 on mariadb 10.1.26 which comes with debian 9 as it's linked to YaSSL, not OpenSSL; likely when you recompiled 10.3 it linked with openssl, which is why it worked for you, @ztk.me
• 'require_secure_transport' (mentioned above) appears to be mysql-only, in mariadb you must update the mysql.users table to require ssl
  
• my above notes about the settings for config.inc.local.php are accurate, and found the settings will not be preserved in config.inc.php through an ispconfig update currently, though there's a WIP merge request that should fix that soon
• note that db_client_flags (in config.inc.local.php) is for the connection to the local server and dbmaster_client_flags is for the connection to the master (so eg. on my test setup I had mysql ssl on the master but not the slave dns servers, so I left $conf['db_client_flags'] = 0; but set $conf['dbmaster_client_flags'] = MYSQL_CLIENT_SSL; so those connections used ssl)

 

This is inaccurate, and I corrected it in my above comment - that setting works with OpenSSL, but not with YaSSL. The version difference I confused that with was in how you make ssl required for users; starting in mariadb 10.2.0 you can use create/alter/grant to make it easy (eg. ALTER USER foo REQUIRE SSL), where in 10.1.26 I had to edit mysql.users directly.

 

And just a little more info I found for ssl on mysql connections:

• on one system MYSQL_CLIENT_SSL was not defined, I had to use MYSQLI_CLIENT_SSL instead, which is probably more correct anyways
• at least on the mariadb I'm using, you can't use SSL on a unix socket connection so if you set $conf['db_client_flags'] you must change the db)host from localhost to '127.0.0.1' (and I don't recommend ssl over tcp, just use the unix socket...)
• do NOT set $conf['db_client_flags']=MYSQLI_CLIENT_SSL in /usr/local/ispconfig/interface/lib/config.inc.local.php on the master, as it's not supported in "interface" currently, only in "server" (I removed that instruction from my above comment)