My setup does the same as zicguy and I agree, excellent work. I've also tried everything he has done. As well as going back through all the steps. Nothing seems to work. I got tired of messing with it and just re-install Ubuntu with a back-up and everything works now. But for how long and is this going to happen again..?
So, it seems i'm not the only one dealing with this problem... In my case, i have this issue on a production server, so I can't easily reinstall my server (FYI, I'm on Debian Wheezy). This issue really makes me crazy. I tried a lot of things as change pemfile to monitrc, put none content on it, etc... Still an old certificate in the browser... I searching with this cert on the filesystem without any success. I really don't understand. It seems there is no issue with Monit because Pure-FTP has the same behavior and returns the same wrong cert. So, this issue seems to happen only on services using pemfile. Is there a possibility that Let's encrypt returns a wrong old certificates? Is certificate revocation could solve that? What if I try to revoke, delete and re create cert files on my main domain? If so, what is the right process to achieve that? Thanks for you help ;-)
Remember that ispserver.pem is a created file and not the same as the other ispserver.key or ispserver.crt (symlinked) files. If you did not recreate ispserver.pem (and chmod it to 600) after LE SSL certs for the hostname -f fqdn is renewed, then I think it will still use the old file. This is the earlier step, so do check that out too. Basically simply run "ls -la /usr/local/ispconfig/interface/ssl/ispserver.pem" will show you what is the date of the file and whether it is just an empty file. Reinstalling is never necessary but if after doing so it works, it only shows that you failed to check properly for the original failure. By the way, did you restart the services (monit and pure-ftpd-mysql)?
Of course I did. I restart both services many times... A new strange thing: I tried to change monit configuration to put a self-signed generated cert: Code: set httpd port 2812 and SSL ENABLE PEMFILE /etc/monit/monit.pem allow monitAdmin:XXXXXXXX Then, I restart monit service by "service monit restart" command line. Restart was OK, but in browsers, still the old let's encrypt used... It is so strange...
Finally, after hours and hours, I found the issue, and the solution For an unknow reason, I had multiple stucked processes for monit and pure-ftp even if services was stopped. One process in particular, for each of them, was launched few month ago, and I think these processes had the old SSL cert. So, first I stopped both, then I ran Code: ps aux | grep monit Code: ps aux | grep pure-ftpd-mysql and I kill all relevant PID. After that, I restarted both services and everything works well now! Monit and Pure-FTP hav the right SSL LE certificate. For some reason, and in particular cases, restart services is not sufficient and we also had to check processes and kill them if necessary. I don't know if this could be added in the le_ispc_pem.sh script. @ahrasis, if you have an idea to enhance the script in this way I suspect Monit to manage not very well processes when it restart them... but this is another story... Hope my post could help!
so what about mysql? This works for mariadb 10.3 using debian 9 assuming the ispcerts have been symlinked according to your script. Code: cp /usr/local/ispconfig/interface/ssl/ispserver.pem /etc/mysql/mysql.pem openssl rsa -in /usr/local/ispconfig/interface/ssl/ispserver.key -out /etc/mysql/mysql.crt chown mysql:mysql /etc/mysql/mysql.crt chown mysql:mysql /etc/mysql/mysql.pem chmod 400 /etc/mysql/mysql.crt chmod 400 /etc/mysql/mysql.pem and no, you can't simply symlink those, it won't read. change your /etc/mysql/mariadb.conf.d/50-server.cnf
Milllion thanks and credits to @ztk.me for his awesome tip above. [Using Let's Encrypt In ISPConfig for MySQL Server] 7B. I'll suggest a less complicated approach to extend the use of ISPConfig LE ssl certs to mysql server. Step A - Enabling SSL With Cipher In /etc/mysql/mariadb.conf.d/50-server.cnf (under the [mysqld] section), simply add these following lines: Code: ssl-cert=/etc/mysql/ssl/server-cert.pem ssl-key=/etc/mysql/ssl/server-key.pem ssl-cipher=TLSv1.2,TLSv1.3 You can use this online command to insert the above code: Code: sed -i '/\[mysqld\]/a ssl-cert=/etc/mysql/ssl/server-cert.pem\nssl-key=/etc/mysql/ssl/server-key.pem\nssl-cipher=TLSv1.2,TLSv1.3' /etc/mysql/mariadb.conf.d/50-server.cnf Step B - Copying SSL Files The LE ssl certs are usages are explain in here. Use scp to copy these files from your Server LE ssl folder to mysql folder: Code: mkdir -p /etc/mysql/ssl cd /usr/local/ispconfig/interface/ssl scp ispserver.crt /etc/mysql/ssl/server-cert.pem scp ispserver.key /etc/mysql/ssl/server-key.pem chown mysql:mysql /etc/mysql/ssl/server*.pem chmod 400 /etc/mysql/ssl/server*.pem Step C - Restart MySQL Service & Check The ssl for mysql should be automatically enabled with the above ssl settings after restarting mysql and check your database for whether the above config is working as follows: Code: service mysql restart mysql --ssl -uroot -p In your mysql, you can enter these command: Code: SHOW VARIABLES LIKE '%ssl%'; satus; The first one should display something like: Code: +---------------------+---------------------------------------+ | Variable_name | Value | +---------------------+---------------------------------------+ | have_openssl | YES | | have_ssl | YES | | ssl_ca | | | ssl_capath | | | ssl_cert | /etc/mysql/ssl/server-cert.pem | | ssl_cipher | TLSv1.2,TLSv1.3 | | ssl_crl | | | ssl_crlpath | | | ssl_key | /etc/mysql/ssl/server-key.pem | | version_ssl_library | OpenSSL 1.1.1-pre8 (beta) 20 Jun 2018 | +---------------------+---------------------------------------+ While the second should display something like: Code: [...] SSL: Cipher in use is TLS_AES_256_GCM_SHA384 [...] Step D - Copying SSL Files Between Servers The same scp command can also be used to transfer files between servers provided you already created the required access keys as suggested in post #203 for multiserver setup by adding the mysql server name or its ip number. If it has the required access keys, you can use the following command follows to remotely create the ssl folder, scp the files and restart mysql: Code: ssh [email protected] 'mkdir /etc/mysql/ssl' cd /usr/local/ispconfig/interface/ssl scp ispserver.crt [email protected]:/etc/mysql/ssl/server-cert.pem scp ispserver.key [email protected]:/etc/mysql/ssl/server-key.pem ssh [email protected] 'service mysql restart' Step E - Automate Future Copying To automate this scp in the future (when the LE ssl certs are renewed), you should add the scp lines in /etc/init.d/le_ispc_pem.sh like this: Code: #!/bin/sh ### BEGIN INIT INFO # Provides: LE ISPSERVER.PEM AUTO UPDATER # Required-Start: $local_fs $network # Required-Stop: $local_fs # Default-Start: 2 3 4 5 # Default-Stop: 0 1 6 # Short-Description: LE ISPSERVER.PEM AUTO UPDATER # Description: Update ispserver.pem automatically after ISPC LE SSL certs are renewed. ### END INIT INFO cd /usr/local/ispconfig/interface/ssl/ mv ispserver.pem ispserver.pem-$(date +"%y%m%d%H%M%S").bak cat ispserver.{key,crt} > ispserver.pem chmod 600 ispserver.pem chmod 600 /etc/ssl/private/pure-ftpd.pem scp ispserver.crt /etc/mysql/ssl/server-cert.pem scp ispserver.key /etc/mysql/ssl/server-key.pem service mysql restart service pure-ftpd-mysql restart service monit restart service postfix restart service dovecot restart service nginx restart Step F - Future Copying Between Servers Add server name or its ip address to the scp lines and service mysql restart if you are in the multi server setup, like in the sample codes in Step D above. Notes: 1. I already referred this post in the OT as part of the extended guide for this thread. 2. This has been added in LE4ISPC script so mysql service will now be covered, secured and updated together with other services. 3. You may need to create CA and client's key and cert files and add them (to this database server and client server)to enable mysql ssl remote access from another server. Do read good articles about it here and here. 4. The best way for securing mysql server may be best served by using openssl created ssl certs rather than LE ssl certs.
good point using default filenames however what you suggested with scp didn't work for me, why I had to use the ssl in out command could be my mistake ... dunno
@ahrasis https://www.howtoforge.com/communit...cot-pureftpd-mariadb.77499/page-2#post-376744 @Jesse Norell if you are working on this, too - you should exchange so the cron / isp behaviour can match better
I have this script to monitor what ssl cert is in use by mysql and rebuild/restart it if it differs from the latest letsencrypt certificate, which might be useful, and I think also implies I had to use openssl to generate, not a simple cp. Save as /usr/local/sbin/letsencrypt-for-mysql.sh: Code: #!/bin/bash # letsencrypt-for-mysql.sh: compares the ssl certficate/key used by mysql # with the current certificate/key issued by letsencrypt and copy the latter # to the former if they differ. # this can be run as a cronjob to propogate letsencrypt certificate changes # to mysql MYSQL_SSLDIR=/etc/mysql/ssl MYSQL_CA=${MYSQL_SSLDIR}/le-ca.pem MYSQL_CERT=${MYSQL_SSLDIR}/le-cert.pem MYSQL_KEY=${MYSQL_SSLDIR}/le-key.pem SYS_CAFILE=/etc/ssl/certs/ca-certificates.crt LE_DIR=/etc/letsencrypt/live/`hostname -f` LE_CA=${LE_DIR}/chain.pem LE_CERT=${LE_DIR}/cert.pem LE_KEY=${LE_DIR}/privkey.pem OPENSSL=`which openssl 2>/dev/null | head -1` # Check if letsencrypt has been setup if [ ! -f ${LE_CA} -o ! -f ${LE_CERT} -o ! -f ${LE_KEY} ] then echo "Letsencrypt files not found. You must setup letsencrypt and issue a certificate first." 1>&2 exit 0 fi # Check openssl binary exists if [ ! -f ${OPENSSL} ] then echo "Cannot find openssl. Exiting." 1>&2 exit 1 fi # setup_certs() copies/formats the letsencrypt files for mysql # (workarounds for file ownership/permissions and built-in YaSSL library limitations) function setup_certs() { if [ ! -d ${MYSQL_SSLDIR} ] then mkdir ${MYSQL_SSLDIR} chown 750 ${MYSQL_SSLDIR} fi # format private key as PKCS1 ${OPENSSL} rsa -in ${LE_KEY} -out ${MYSQL_KEY} 2>&1 | grep -v 'writing RSA key' 1>&2 # copy the certificate ${OPENSSL} x509 -in ${LE_CERT} -out ${MYSQL_CERT} # YaSSL won't follow a certificate chain, it only verifies the certificate # against the signing certificate, which is the intermediate certificate # letsencrypt used. If mysqld is linked with openssl, ${SYS_CAFILE} # should be used instead, to handle changes of the intermediate. cat ${LE_CA} ${SYS_CAFILE} > ${MYSQL_CA} chgrp -R mysql ${MYSQL_SSLDIR} chmod -R o-rwx ${MYSQL_SSLDIR} } # restart mysqld if it is running function restart_mysqld_if_running() { /etc/init.d/mysql status 2>/dev/null >/dev/null if [ $? -eq 0 ] then /etc/init.d/mysql restart >/dev/null fi } if [ ! -f ${MYSQL_CA} -o ! -f ${MYSQL_CERT} -o ! -f ${MYSQL_KEY} ] then setup_certs && restart_mysqld_if_running else # check if keys/certificates changed le_modulus=`${OPENSSL} rsa -noout -modulus -in ${LE_KEY} | md5sum` my_modulus=`${OPENSSL} rsa -noout -modulus -in ${MYSQL_KEY} | md5sum` le_serial=`${OPENSSL} x509 -noout -serial -in ${LE_CERT}` my_serial=`${OPENSSL} x509 -noout -serial -in ${MYSQL_CERT}` le_ca_serial=`${OPENSSL} x509 -noout -serial -in ${LE_CA}` my_ca_serial=`${OPENSSL} x509 -noout -serial -in ${MYSQL_CA}` if [ "${le_modulus}" != "${my_modulus}" -o "${le_serial}" != "${my_serial}" -o "${le_ca_serial}" != "${my_ca_serial}" ] then setup_certs && restart_mysqld_if_running fi fi exit 0 Then just run that from a cronjob, eg. /etc/cron.d/letsencrypt-restarts: Code: SHELL=/bin/bash PATH=/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin 10 3 * * * root /usr/local/sbin/letsencrypt-for-mysql.sh
using this threads plugin it could also be done using Code: echo "/etc/letsencrypt/archive/$(hostname -f)/ IN_MODIFY /usr/local/sbin/letsencrypt-for-mysql.sh" >> /var/spool/incron/root I guess
+1. That is if you prefer using incron like me. You can also add the same in the created /etc/init.d/le_ispc_pem.sh as the post #247 above now reflects from this thread opening post. I also adjusted it to use /etc/mysql/my.cnf file instead of mariadb since it will cover both mysql and mariadb safely. To add and enable the certs settings and links in that file, I added this one line command in the LE4ISPC script for my.cnf file: Code: sed -i '/\[mysqld\]/a ssl-cert=/etc/mysql/ssl/server-cert.pem\nssl-key=/etc/mysql/ssl/server-key.pem\nssl-cipher=TLSv1.2,TLSv1.3' /etc/mysql/my.cnf For mariadb 50-server.cnf, you can use this one line command instead: Code: sed -i '/\[mysqld\]/a ssl-cert=/etc/mysql/ssl/server-cert.pem\nssl-key=/etc/mysql/ssl/server-key.pem\nssl-cipher=TLSv1.2,TLSv1.3' /etc/mysql/mariadb.conf.d/50-server.cnf About copying from ISPConfig SSL folder, I tested scp (not cp) again in another server of mine (Ubuntu 18.04), it works just fine and the original files are rightly copied. The reason cp command does not work for this is because it only copied the symlink files instead of the needed original files.
well yeah, somehow I need to do the conversion, have you tested with mysql or mariadb? I used maria 10.3
I am using Mariadb 10.3 and the test was done on it. Theoretically it should work on Mysql as well since it is using the same file too.
well there is an issue for me and incron... add a file to watch and define a script to run, restart incron. touch / modify the watched file, incron does execute the script like expected. now touch / modify the file again and... nothing happens -.- need to add an service restart of incron after finish touching the file
Not sure about that. Last time I tested, mine works as many time as the changes are made. What did you have / add in the /var/spool/incron/root? Did you give proper permission to root?
precisely what I did: https://www.howtoforge.com/communit...sjunk2-antispam-with-sieve.79545/#post-376781 well ... I'll try again, I think I may have made changes too quickly... could live with that / would like to know where to adjust that....lets see
Checking my incron, I use IN_ATTRIB to watch for specific file changes and IN_MODIFY for directory content (file) changes (ends with a slash "/"). Since you are using it for monitoring file changes, I'd suggest to use IN_ATTRIB instead. Examples: Code: /etc/nginx/sites-available/domain.tld.vhost IN_ATTRIB /bin/bash /etc/init.d/script1.sh /etc/letsencrypt/archive/server1.domain.tld/ IN_MODIFY /bin/bash /etc/init.d/script2.sh There are some instances in the FAQ where it explains why IN_MODIFY doesn't work. Check this out: Do note that while incron is monitoring specified file / directory changes, I also monitor incron itself via Monit where if it never finished causing the use of too much resources etc, Monit will kill it and restart. It did happen to me once.
oh crap not only thank you very much for your time explaining incron but also for giving the hints ... I do use monit and hell yes it can check filechanges ... and it is embedded in ispconfig and one could add a button "fixconfigs" ... man good point