Postfix can no longer connect to MariaDB due to TLS incompatibility

Discussion in 'Installation/Configuration' started by cbj4074, Jul 11, 2022.

  1. cbj4074

    cbj4074 Member

    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?
     
  2. Taleman

    Taleman Well-Known Member HowtoForge Supporter

    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.
     
  3. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

    Agreed with @Taleman there. ISPConfig usually only supports the default version that is shipped with your OS, which normally is not the bleeding edge version, which you may have installed (10.5) via using MariaDB repository. So to use the MariadDB 10.5, you might need to wait for ISPConfig to support Ubuntu 22.04 which is shipped with it and is using php8.1 as its default.
     
  4. till

    till Super Moderator Staff Member ISPConfig Developer

    And you don't need TLS for the connection from Postfix to MariaDB over 127.0.0.1 as that#s the lo network interface and ISPConfig does not enforce the use of TLS there, so I guess you must have enforced that manually.
     
  5. cbj4074

    cbj4074 Member

    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).
     
  6. till

    till Super Moderator Staff Member ISPConfig Developer

    By default, TLS is not set to required for all connections in MariaDB, at least the ISPConfig installer has not set it to that value. If you set your database server up like that, then this might cause issues like the one that you are having right now.
     
  7. Oazis

    Oazis Member

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

    ahrasis Well-Known Member HowtoForge Supporter

    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.
     
  9. nhybgtvfr

    nhybgtvfr Well-Known Member HowtoForge Supporter

    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.
     
    ahrasis likes this.
  10. cbj4074

    cbj4074 Member

    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.
     
    Last edited: Jul 20, 2022
  11. cbj4074

    cbj4074 Member

    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!
     
    Last edited: Jul 20, 2022
  12. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

  13. nhybgtvfr

    nhybgtvfr Well-Known Member HowtoForge Supporter

  14. cbj4074

    cbj4074 Member

    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.
     
    Last edited: Jul 21, 2022
  15. nhybgtvfr

    nhybgtvfr Well-Known Member HowtoForge Supporter

    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....
     
    ahrasis likes this.
  16. ahrasis

    ahrasis Well-Known Member HowtoForge Supporter

    In short he needs to check his mysql configs both sides. As @till said this is never a default in ISConfig which could only means he added it himself whether he remembers it or not.
     
  17. cbj4074

    cbj4074 Member

    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.
     
  18. cbj4074

    cbj4074 Member

    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.)
     
    Last edited: Jul 22, 2022
    ahrasis and Steini86 like this.
  19. cbj4074

    cbj4074 Member

    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):

     
    ahrasis, Steini86 and till like this.
  20. Th0m

    Th0m ISPConfig Developer Staff Member ISPConfig Developer

    Thanks for sharing, had the same problem here and was able to resolve it quickly because of your thread. :)
     
    ahrasis likes this.

Share This Page