1 (edited by Chrissicom 2021-09-04 17:33:22)

Topic: [Tutorial] Fix iRedmail 1.4.0 MariaDB "SSL is broken out of the box"

==== REQUIRED BASIC INFO OF YOUR IREDMAIL SERVER ====
- iRedMail version 1.4.0
- Deployed with downloadable installer
- Rocky Linux 8.4
- MariaDB backend
- Nginx webserver
====

Alright, so I have installed iRedMail on a test server with MariaDB backend and rebooted the server as instructed. No other commands but the ones listed below were run on the server (except for the iRedMail installation and installation of wget and nano).

After reboot I immediately try to connect to MariaDB with

reboot
...
mysql -u root -p

This does not work because with the strict SSL certificate testing of current MariaDB versions on Rocky Linux 8.4 (should apply to CentOS 8 Stream too) there seems to be a problem with the self signed iRedMail.crt and iRedMail.key created during installation. I receive the following error message:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading authorization packet', system error: 0

The problem is with the certificate, because when I turn off SSL for MariaDB it works fine. Since running a mail server with a self signed certificate isn't best practice anyway I got a Let's Encrypt certificate and ran the following commands recommended by the /etc/nginx/templates/ssl.tmpl file.

rm -f /etc/pki/tls/private/iRedMail.key
rm -f /etc/pki/tls/certs/iRedMail.crt
ln -s /etc/letsencrypt/live/<domain>/privkey.pem /etc/pki/tls/private/iRedMail.key
ln -s /etc/letsencrypt/live/<domain>/fullchain.pem /etc/pki/tls/certs/iRedMail.crt

Of course I replaced <domain> by the corrent domain name as the command would otherwise throw an error. After this step nginx works fine with the Let's Encrypt certificate, however MariaDB doesn't because the mysql user has no access to the files. You'll see the following error messages in /var/log/messages (or eventually /var/log/mariadb/error.log depending on your logging configuration):

cat /var/log/messages | grep mysqld
Sep  4 10:36:29 test mysqld[3289]: SSL error: Unable to get certificate from '/etc/pki/tls/certs/iRedMail.crt'
Sep  4 10:36:29 test mysqld[3289]: 2021-09-04 10:36:29 0 [Warning] Failed to setup SSL
Sep  4 10:36:29 test mysqld[3289]: 2021-09-04 10:36:29 0 [Warning] SSL error: Unable to get certificate
Sep  4 10:36:29 test mysqld[3289]: 2021-09-04 10:36:29 0 [Warning] SSL error: error:0200100D:system library:fopen:Permission denied
Sep  4 10:36:29 test mysqld[3289]: 2021-09-04 10:36:29 0 [Warning] SSL error: error:20074002:BIO routines:file_ctrl:system lib
Sep  4 10:36:29 test mysqld[3289]: 2021-09-04 10:36:29 0 [Warning] SSL error: error:140DC002:SSL routines:use_certificate_chain_file:system lib

To allow MariaDB or rather the mysql user to access the certificate files I have run the following commands:

setfacl -R -m "u:mysql:rx" /etc/letsencrypt/archive/ /etc/letsencrypt/live/
setfacl -R -m "u:mysql:rx" /etc/pki/tls/certs/iRedMail.crt /etc/pki/tls/private/iRedMail.key

I used setfacl since I didn't want to "replace" the original permissions with chown.
Et voila, when you run MariaDB (systemctl restart mariadb) SSL starts working.

MariaDB [(none)]> show variables like '%ssl%';
--------------
show variables like '%ssl%'
--------------

+---------------------+-----------------------------------+
| Variable_name       | Value                             |
+---------------------+-----------------------------------+
| have_openssl        | YES                               |
| have_ssl            | YES                               |
| ssl_ca              |                                   |
| ssl_capath          |                                   |
| ssl_cert            | /etc/pki/tls/certs/iRedMail.crt   |
| ssl_cipher          | EECDH+AESGCM:EDH+AESGCM           |
| ssl_crl             |                                   |
| ssl_crlpath         |                                   |
| ssl_key             | /etc/pki/tls/private/iRedMail.key |
| version_ssl_library | OpenSSL 1.1.1g FIPS  21 Apr 2020  |
+---------------------+-----------------------------------+
10 rows in set (0.002 sec)

Additional information: my.cnf was changed in accordance with https://syslink.pl/cipherlist/ recommendations.
Make sure #ssl-ca is unset in my.cnf (default of iRedMail configuration) because the fullchain.pem from the Let's Encrypt certificate contains the full chain including the CA certificate as the name says and thus ssl-ca doesn't need to be set.

#ssl_cipher = ALL
ssl_cipher = EECDH+AESGCM:EDH+AESGCM

So to sum it up, this is no question but rather a tutorial how to fix broken out of the box SSL with MariaDB when installing iRedMail 1.4 on Rocky Linux 8.4.

PS This setup also works with Master-Master MariaDB replication in my production environment where I use two iRedMail servers (vmail is synced with GlusterFS).

A little caveat I still have here is that all services using the Let's Encrypt certificate must be restarted on renewal (nginx, amavisd, postfix, dovecot, mariadb ... for GlusterFS I use self signed certificates). For nginx I think a daemon-reload would be sufficient, but dovecot e.g. only "loads" the refreshed certificate when I run "systemctl restart dovecot". I haven't found a way yet to avoid this.

----

Spider Email Archiver: On-Premises, lightweight email archiving software developed by iRedMail team. Supports Amazon S3 compatible storage and custom branding.

2

Re: [Tutorial] Fix iRedmail 1.4.0 MariaDB "SSL is broken out of the box"

Maybe we don't enable ssl in mariadb since it's accessed from only localhost.

3

Re: [Tutorial] Fix iRedmail 1.4.0 MariaDB "SSL is broken out of the box"

ZhangHuangbin wrote:

Maybe we don't enable ssl in mariadb since it's accessed from only localhost.

Even if MariaDB is only used locally a malicious service that is running locally could still be used to inspect traffic if no SSL is used. It might sound paranoid but from a security perspective SSL should never be turned off just for comfort.
But instead of "breaking it by default" I would agree that turning SSL off for MariaDB in a standard single server iRedMail setup is favorable.
The same holds true for using plain text passwords when using STARTTLS. I know it's the default but once you're inside the tunnel plain text passwords are easily readable as the name says, so even when using STARTTLS encrypted passwords are to be recommended.

To give some additional info on my production setup and why SSL is mandatory for me:
I am running iRedMail on two cloud VPS in different datacenters. The servers are connected by a VPN on a private IP in addition to their public IPs. I replicate the /var/vmail directory with GlusterFS and also replicate MariaDB with Master-Master replication through the VPN. Since the VPN is managed by the hosting company I "don't trust it" and thus use SSL as a mandatory option.

4

Re: [Tutorial] Fix iRedmail 1.4.0 MariaDB "SSL is broken out of the box"

Beeing paranoid doesnt make it more secure, if you dont trust a hosting company, dont use it, same with VPN.

What is the point to make a mysql connection secure, when all stored data itself isn't?
Makes no sense to not trust a company and then store unencrypted data on their drives.

Even if you make the connection secure, secure the tunnel, and then secure by encrypted passwords, the stored data is still unencrypted on the drive.

5

Re: [Tutorial] Fix iRedmail 1.4.0 MariaDB "SSL is broken out of the box"

Not argue over "paranoid" or "best security" here, just my opinion:

We all know better security or more secure restrictions usually means less user-friendly (the "user" might be sysadmin in some cases), so IMO the key point is balance.

For end user side, we should always force secure connections with TLS/SSL for sure, but for traffic happened locally (i mean just localhost/127.0.0.1, not LAN. And both sender/receiver are local applications), plaintext is acceptable.

In our case, MariaDB is accessed by local applications like Postfix/Dovecot/Webmail, no external (SQL) access at all, so disabling SSL support in MariaDB is acceptable. It avoids a purchased ssl cert (saving some money), or a free cert which requires restarting daemon every X months (short service outage, etc). Same to OpenLDAP and PostgreSQL services.