Postfix can no longer connect to MariaDB due to TLS incompatibility

Hello,

It seems that a recent update to MariaDB has caused Postfix not to be able to connect to MariaDB, and a TLS version incompatibility message results in the Postfix mail log (I don't have the message in front of me at the moment, but I will update the post with it shortly).

I have not researched the specific nature of the change in MariaDB yet, but I suspect that the minimum required TLS version was increase to v1.3 or similar.

Has anybody else encountered this problem? I'm running ISPConfig on a vanilla Ubuntu 20.04 LTS machine. The MariaDB version is as follows:

Code:

$ mysql --version
mysql  Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

And the Postfix version as follows:

Code:

$ postconf mail_version
mail_version = 3.4.13

The MariaDB and Postfix instances are both on the same server, so I was able to "quick-fix" this by forcing Postfix to connect over a local socket instead of a TCP connection. To do this, I simply changed the database host value in all of the relevant Postfix configuration files, e.g.:

Code:

sed -i 's/hosts = 127\.0\.0\.1/hosts = localhost/g' /etc/postfix/*.cf

But this doesn't feel like a permanent fix (does ISPConfig ever overwrite these configuration files?), and it doesn't help anybody whose MySQL/MariaDB server is on a different host.

Is the "proper fix" to upgrade Postfix to a newer version?

 

If you are running ISPConfig, you should not upgrade database. At least not upgrade unless before make sure the new database version is supported by ISPConfig. Same goes for other applications under ISPConfig control, it may well be ISPConfig can not work with the different version.
This forum has previous threads about upgraded database breaking ISPConfig.

 

Thanks for the input here, everyone.

@Taleman and @ahrasis MariaDB 10.3, which is the version I'm using, is available in stock Ubuntu 20.04 LTS. I'm not using 10.5, and any upgrade I've applied would have been a simple "apt-get upgrade" (as opposed to "apt-get dist-upgrade" or something).

As a bit of background, I did indeed enable TLS in the MariaDB config, so that users are able to connect securely from remote hosts on which an SSH tunnel is not an option (due to MFA requirements for SSH sessions). However, it didn't occur to me that connection attempts between Postfix and MariaDB would be subject to the TLS requirement, so I didn't think through the TLS versions that are supported both by Postfix and MariaDB, and so didn't anticipate that any change thereto could be problematic. That said, enabling TLS in MariaDB by itself doesn't seem like an edge-case or an outrageous requirement, does it?

@till You say that ISPConfig does not enforce the use of TLS on local connections between Postfix and MariaDB. Can you elaborate on that at all? It looks to me that when TLS is enabled and set to REQUIRED in the MySQL/MariaDB configuration, the requirement applies to any TCP/IP connection, even if the connection originates from localhost. Is that inconsistent with your expectation? In studying the MySQL and MariaDB documentation regarding TLS, I cannot find anything to the contrary.

The only method I can find to forego TLS for local connections is to use a socket (instead of TCP via localhost).

 

Can you provide a link describing how to set up a connection in MariaDB via ТLS?

 

Basically as explain by @till, you don't need to secure the connection if they are in one same server. It is mentioned in https://mariadb.com/kb/en/secure-connections-overview/

That said, we did test that when securing ISPConfig manually was instroduced long time ago: https://forum.howtoforge.com/thread...ts-encrypt-free-ssl.75554/page-13#post-376720 but we do not use this anymore as everything were already merged in ISPConfig 3.2 installer / updater (but not that mysql part though).

So far if I am not mistaken securing mysql was not implemented at all, even between ISPConfig servers. Do correct me if I am wrong on this.

 

as far as i'm aware, TLS is only enforced if the user is created with the 'require ssl' option.
so if the member ispconfig / postfix server mysql account is created without this option, it should still be able to connect to mysql with an unencrypted connection.

 

Thanks for all the insight, everyone.

@ahrasis Thanks for posting the link to that thread; that's essentially the same process I followed to enable TLS in MariaDB. And yes, I believe you are correct in that securing MySQL/MariaDB has never been within the scope of ISPConfig's intentions or capabilities.

As you noted, enforcing TLS is not strictly "necessary" when the database service runs on the same server as all of the other ISPConfig components, but even so, it is absolutely necessary to enforce TLS on any remote connections to the MySQL/MariaDB instance in the aforementioned configuration.

One might argue, "Oh, that's not necessary. Just tunnel-in via SSH first, then you don't need to enforce TLS on the database server." Sure, that is a simple solution, but it isn't always possible. In my case, my organization requires multi-factor authentication for SSH connections, so I can't easily use an SSH tunnel in all scenarios in which I need to connect to the database remotely (especially in an automated capacity).

@nhybgtvfr According to the documentation at https://mariadb.com/kb/en/securing-connections-for-client-and-server/#requiring-tls , this does indeed seem to be the case:

Further, it seems that in versions prior to 10.5.2, the only way to enforce TLS was on a per-account basis, which has me scratching my head, because I don't believe that the REQUIRE_SSL option is enabled for the user that Postfix uses to connect to MariaDB.

In other words, I'm surprised that Postfix is even trying to use TLS when connecting to MariaDB when using TCP at 127.0.0.1. It seems like I need to determine why this is happening before troubleshooting this any further.

 

Indeed, everything I've tried indicates that TLS is not in fact required for MariaDB connections:

Code:

MariaDB [mysql]> select User, ssl_type from user;
+------------------+----------+
| User             | ssl_type |
+------------------+----------+
| root             |          |
| ispconfig        |          |
| debian-sys-maint |          |
+------------------+----------+

MariaDB [(none)]> show grants for 'ispconfig';
ERROR 1141 (42000): There is no such grant defined for user 'ispconfig' on host '%'

Furthermore, I can connect to MariaDB as the "ispconfig" user (which is the same user that the Postfix configuration uses) and TLS is not enforced:

Code:

MariaDB [(none)]> status
--------------
mysql  Ver 15.1 Distrib 10.3.34-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Connection id:          75608
Current database:
Current user:           ispconfig@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server:                 MariaDB
Server version:         10.3.34-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04
Protocol version:       10
Connection:             127.0.0.1 via TCP/IP
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
TCP port:               3306
Uptime:                 8 days 21 hours 25 min 21 sec

So, it really seems to me like Postfix is insisting that it use TLS when connecting to MariaDB, despite not specifically configuring it to do so.

I captured the detailed error messages that are written to /var/log/mail.log when I attempt to send mail through Postfix (via SMTP) locally:

Code:

Jul 20 13:54:34 vm postfix/smtpd[3363676]: connect from localhost[::1]
Jul 20 13:54:34 vm postfix/proxymap[3363677]: warning: connect to mysql server 127.0.0.1: SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol
Jul 20 13:54:34 vm postfix/trivial-rewrite[3363728]: warning: proxy:mysql:/etc/postfix/mysql-virtual_transports.cf lookup error for "*"
Jul 20 13:54:34 vm postfix/trivial-rewrite[3363728]: warning: proxy:mysql:/etc/postfix/mysql-virtual_transports.cf lookup error for "*"
Jul 20 13:54:34 vm postfix/proxymap[3363677]: warning: connect to mysql server 127.0.0.1: SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol
Jul 20 13:54:34 vm postfix/trivial-rewrite[3363728]: warning: virtual_alias_domains: proxy:mysql:/etc/postfix/mysql-virtual_alias_domains.cf: table lookup problem
Jul 20 13:54:34 vm postfix/trivial-rewrite[3363728]: warning: virtual_alias_domains lookup failure
Jul 20 13:54:34 vm postfix/trivial-rewrite[3363728]: warning: virtual_alias_domains: proxy:mysql:/etc/postfix/mysql-virtual_alias_domains.cf: table lookup problem
Jul 20 13:54:34 vm postfix/trivial-rewrite[3363728]: warning: virtual_alias_domains lookup failure
Jul 20 13:54:34 vm postfix/proxymap[3363677]: warning: connect to mysql server 127.0.0.1: SSL connection error: error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol
Jul 20 13:54:34 vm postfix/smtpd[3363676]: warning: proxy:mysql:/etc/postfix/mysql-virtual_client.cf lookup error for "localhost"
Jul 20 13:54:34 vm postfix/smtpd[3363676]: NOQUEUE: reject: RCPT from localhost[::1]: 451 4.3.5 <localhost[::1]>: Client host rejected: Server configuration error; from=<[email protected]> to=<[email protected]> proto=ESMTP helo=<[127.0.0.1]>
Jul 20 13:54:34 vm postfix/smtpd[3363676]: disconnect from localhost[::1] ehlo=1 mail=1 rcpt=0/1 data=0/1 quit=1 commands=3/5

The Postfix docs at https://www.postfix.org/mysql_table.5.html don't seem to contain anything that seems relevant in terms of forcing Postfix to use TLS (nor preventing it from doing so).

This person appears to have the same problem (not an ISPConfig user, specifically), and nobody has been able to post an Answer:

https://askubuntu.com/questions/1370590/postfix-mariadb-ssl-connection-error

Any ideas from the group? Thanks in advance!

 

It means if you set require_secure_transport on, it will not connect unless the connection is secured. It happens when you set something like this:

Code:

[mysqld]
ssl_ca=ca.pem
ssl_cert=server-cert.pem
ssl_key=server-key.pem
require_secure_transport=ON

Source: https://dev.mysql.com/doc/mysql-security-excerpt/8.0/en/using-encrypted-connections.html

Disabling that will allow normal connection without TLS.

 

perhaps the mysql credentials were configured via PAM?
https://groveld.com/articles/postfix-with-mysql-backend-and-tls

 

Thank you for the continued assistance!

@ahrasis I am running MariaDB 10.3, which doesn't look to have the "require_secure_transport" variable (it was introduced in 10.4 ES, according to https://mariadb.com/docs/reference/mdb/system-variables/require_secure_transport/ ).

Also, if I had configured MariaDB to require TLS, I would expect "mysql --help --verbose" not to output the following:

Code:

ssl                               FALSE
ssl-ca                            (No default value)
ssl-capath                        (No default value)
ssl-cert                          (No default value)
ssl-cipher                        (No default value)
ssl-key                           (No default value)
ssl-crl                           (No default value)
ssl-crlpath                       (No default value)
ssl-verify-server-cert            FALSE

Also, we know that MariaDB isn't requiring TLS because I can log in without TLS as demonstrated in a previous post.

@nhybgtvfr That's a great thought, but I can't find any evidence of this. I built this server using one of the Perfect Server tutorials, and didn't stray too far off the path, if at all, and I don't believe any of those tutorials use the MySQL PAM module for Postfix. And this returns nothing (and /etc/pam/smtp does not exist):

Code:

# grep -ir "pam_mysql\.so" /etc

I put a post out to the Postfix mailing list, and a knowledgeable regular (with whom I've interacted before, so I know he's basically always correct), said that Postfix does not implement nor handle any of the details regarding the connection between Postfix and MySQL/MariaDB. Rather, all of that, including TLS negotiation, is handled through the MySQL/MariaDB client library, and so he insists that I must have configured the MariaDB client to use TLS. I'm sure he's right, but I just can't find any evidence of that, nor determine why this problem began suddenly and without changing the configuration intentionally.

Here is my OP, if anyone would like to see what I asked:

https://marc.info/?l=postfix-users&m=165834801010570&w=2

And his response:

https://marc.info/?l=postfix-users&m=165834940811140&w=2

I'm really not sure what else to check here! Cranking-up the Postfix SMTP daemon's logging verbosity to -vvv doesn't seem to reveal any additional useful information, either (just a bunch of seemingly irrelevant noise). Same thing on the MariaDB side; debug-level logging doesn't reveal anything actionable about the failed connection attempts.

 

it's a bit old (from mysql5.7):

perhaps it's set in /etc/myql/mariadb.conf.d/50-client.cnf or /etc/mysql/mariadb.conf.d/50-mysql-clients.cnf?

my copy of 50-clients.cnf (ubuntu 20.04 mariadb 10.3.34 ) contains:

Code:

[client]
# Example of client certificate usage
#ssl-cert = /etc/mysql/client-cert.pem
#ssl-key  = /etc/mysql/client-key.pem
#
# Allow only TLS encrypted connections
#ssl-verify-server-cert = on

perhaps you've got that last line uncommented....

 

Thanks @nhybgtvfr , I did of course check all of those config files.

/etc/mysql/mariadb.conf.d/50-client.cnf:

Code:

#
# This group is read by the client library
# Use it for options that affect all clients, but not the server
#

[client]
# Default is Latin1, if you need UTF-8 set this (also in server section)
default-character-set = utf8mb4

# socket location
socket = /var/run/mysqld/mysqld.sock

# Example of client certificate usage
# ssl-cert=/etc/mysql/client-cert.pem
# ssl-key=/etc/mysql/client-key.pem
#
# Allow only TLS encrypted connections
# ssl-verify-server-cert=on

# This group is *never* read by mysql client library, though this
# /etc/mysql/mariadb.cnf.d/client.cnf file is not read by Oracle MySQL
# client anyway.
# If you use the same .cnf file for MySQL and MariaDB,
# use it for MariaDB-only client options
[client-mariadb]

/etc/mysql/mariadb.conf.d/50-mysql-clients.cnf:

Code:

#
# These groups are read by MariaDB command-line tools
# Use it for options that affect only one utility
#

[mysql]
# Default is Latin1, if you need UTF-8 set this (also in server section)
default-character-set = utf8mb4

[mysql_upgrade]

[mysqladmin]

[mysqlbinlog]

[mysqlcheck]

[mysqldump]

[mysqlimport]

[mysqlshow]

[mysqlslap]

As I noted earlier, this server was built years ago and has worked fine up until very recently, and the only changes I've made to it are installing routine upgrades via "apt upgrade ...".

After digging a bit further, I've discovered that MariaDB 10.3 uses the statically linked yaSSL library, which supports only up to TLS 1.1.

From the 10.2 section of https://mariadb.com/kb/en/tls-and-c...riadb/#mariadb-clients-and-utilities-on-linux :

The libmysqlclient link points to https://dev.mysql.com/doc/refman/5.5/en/c-api.html , which doesn't even exist anymore (it redirects to the 8.0 version), so I can't easily get more information regarding the specific implementation. But libmysqlclient versions 5.6 and later indicate that

While I can't yet find documentation to this effect, it seems that the default in libmysqlclient 5.5 is to try using TLS 1.0 and fail outright if it doesn't work out. In other words, there may be a distinction between "See if the server advertises TLS, and if not, fall back to unencrypted connection" versus "Try to establish a TLS connection and throw an exception if there's a handshake failure and do not try to reconnect at all". We shall see.

I'll keep digging and let you know if I find the root cause. Thanks again for the assistance here.

 

I finally figured it out.

It was the last line in this snippet:

/etc/mysql/mariadb.conf.d/50-server.cnf

Code:

#
# * Security Features
#
# Read the manual, too, if you want chroot!
#chroot = /var/lib/mysql/
#
# For generating SSL certificates you can use for example the GUI tool "tinyca".
#
#ssl-ca = /etc/mysql/cacert.pem
ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem
#
# Accept only connections using the latest and most secure TLS protocol version.
# ..when MariaDB is compiled with OpenSSL:
#ssl-cipher = TLSv1.2
# ..when MariaDB is compiled with YaSSL (default in Debian):
# ssl = on ##### THIS DEFAULT VALUE OF "on" IS THE PROBLEM! #####

So, apparently, "ssl = on" is now the default behavior "when MariaDB is compiled with YaSSL (default in Debian)". Uncommenting the line and changing it to "ssl = off" fixes the problem with Postfix and the TLS connection failures.

I went back and looked to see if dpkg had created a backup of this file at some point in the past, and sure enough, it had, and here is the same section from that file:

/etc/mysql/mariadb.conf.d/50-server.cnf.dpkg-old:

Code:

#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

Note that there is no such line, nor the associated commentary.

It seems clear that my initial assumption was correct; at some point, a MariaDB upgrade caused the default behavior to change. Whether that change was made by MariaDB or the Debian/Ubuntu package manager is another matter, but I would certainly call that a breaking change. (Actually, the line is commented-out, by default, so its addition alone is not significant; it's the underlying default MariaDB behavior that changed.)

 

Okay, so now I think I discovered the "why", too.

In looking at https://mariadb.com/kb/en/mysql-command-line-client/#options , I noticed an important and as yet unmentioned (if I'm not mistaken) detail, which is that several other options imply the "--ssl" option.

Namely, defining any of the following options automatically implies the "--ssl" option:

Code:

--ssl-ca=name
--ssl-capath=name
--ssl-cert=name
--ssl-cipher=name
--ssl-key=name

Of course, I have defined two of those by uncommenting them, because I need to support TLS connections from remote hosts that cannot leverage an SSH tunnel:

/etc/mysql/mariadb.conf.d/50-server.cnf:

Code:

ssl-cert = /etc/mysql/server-cert.pem
ssl-key = /etc/mysql/server-key.pem

It was never obvious to me that merely enabling TLS would automatically require it for all TCP connections without explicitly making it required. It seems stupid that the default is not to be opportunistic, but what do I know.

In any case, the problem is solved for me, but I don't know how this isn't affecting a lot of other people. Maybe nobody using ISPConfig is using TLS for remote connections to MySQL/MariaDB...

So, this is not actually true in MariaDB 10.4 CS, 10.3 ES, 10.3 CS, 10.2 ES, 10.2 CS; it's the first three lines that require TLS, not the last one (which doesn't exist in these versions of MariaDB):

 

So did you use this @Th0m? Or did you disable "--ssl" options?

Re-looking this thread, it would be nice to re-work database secure connection especially from remote hosts.