Force Postfix to use TLS for MySQL Connection?

Question

The Problem

I cannot figure out how to force Postfix to use a TLS connection to MySQL. I can manually connect from the Postfix server to the MySQL server with TLS as the postfix user, so there's nothing broken with MySQL authentication. The problem is clear: Postfix isn't requesting TLS for the MySQL connection.

Versions:

• OS: CentOS 7
• Postfix: 2:2.10.1-6.el7 and 2:3.2.4-1.gf.el7
• MySQL (MariaDB): 5.5.56

How I Got Here

I have been searching all over the web, including the various StackExchange sites, for an answer to this question. I have read the Postfix and MySQL documentation at great length, repeatedly. The best answer I've found is one I reject as unnecessarily complicated: "Set up an SSH tunnel between your Postfix and MySQL servers, then connect over it." There has to be a way to instruct Postfix to use TLS (SSL) encryption with the MySQL client.

Before we explore the setup, let me make this firm: My mail server setup works perfectly well without MySQL TLS. I am successfully using TLS for SMTPS and IMAPS and that has nothing to do with my question. Mail is being transported securely both in and out of my network except MySQL connections between Postfix and the MySQL server are unencrypted. Except for the exposed Postfix-MySQL link, there are no problems whatsoever with my mail infrastructure.

However, due to changing security requirements, I must update my existing infrastructure to encrypt all MySQL connections. Setting up TLS for MySQL connections was easy. Manual tests show that MySQL clients can successfully connect over a TLS-encrypted link from all permitted hosts. So, the MySQL TLS setup is also solid and working for everything except Postfix.

What I've Tried

Relevant configuration on the Postfix Server:

/etc/postfix/main.cf

Reduced to just one of several connections to keep the material as specific as possible.

virtual_alias_maps = proxy:mysql:/etc/postfix/lookup_aliases.cf
proxy_read_maps = $virtual_alias_maps

/etc/postfix/lookup_aliases.cf

Credentials and hostnames obfuscated.

hosts = mysql-server.domain.tld
user = postfix
password = *****
dbname = mail
option_file = /etc/my.cnf.d/client.cnf
option_group = client
tls_verify_cert = yes
query = SELECT goto FROM alias WHERE address = '%s' AND active = '1'

/etc/my.cnf

This file is untouched. I'm including it only to show that the next configuration file is properly included into the MySQL Client configuration.

!includedir /etc/my.cnf.d

/etc/my.cnf.d/client.cnf

[client]
ssl = true

Successful -- MANUAL -- MySQL TLS Connection Output From the Postfix Server as the postfix User

Note in particular that TLS is successfully employed:

# hostname -f
mail.domain.tld

# sudo -u postfix mysql -h mysql-server.domain.tld -u postfix -p mail
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 72
Server version: 5.5.56-MariaDB MariaDB Server

Copyright (c) 2000, 2017, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [mail]> \s
--------------
mysql  Ver 15.1 Distrib 5.5.56-MariaDB, for Linux (x86_64) using readline 5.1

Connection id:     72
Current database:  mail
Current user:      postfix@mail.domain.tld
SSL:           Cipher in use is DHE-RSA-AES256-GCM-SHA384
Current pager:     stdout
Using outfile:     ''
Using delimiter:   ;
Server:            MariaDB
Server version:        5.5.56-MariaDB MariaDB Server
Protocol version:  10
Connection:        mysql-server.domain.tld via TCP/IP
Server characterset:   latin1
Db     characterset:   utf8
Client characterset:   utf8
Conn.  characterset:   utf8
TCP port:      3306
Uptime:            1 hour 27 min 23 sec

Threads: 1  Questions: 271  Slow queries: 0  Opens: 13  Flush tables: 2  Open tables: 39  Queries per second avg: 0.051
--------------

MariaDB [mail]> \q
Bye

Relevant configuration on the MySQL Server:

MySQL Grants

Notice that TLS (SSL) is REQUIREd but otherwise the permissions are relatively lax until I sort this TLS connection problem out:

MariaDB [(none)]> SHOW GRANTS FOR 'postfix'@'10.0.101.%';
+-----------------------------------------------------------------------------------------------------+
| Grants for postfix@10.0.101.%                                                                       |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'postfix'@'10.0.101.%' IDENTIFIED BY PASSWORD '*SOMEPASSWORDHASH' REQUIRE SSL |
| GRANT SELECT ON `mail`.* TO 'postfix'@'10.0.101.%'                                                  |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Logs

Postfix Connection Fails When REQUIRE SSL

Even though the manual command-line test of the MySQL TLS connection SUCCEEDS here, Postfix still won't use MySQL TLS.

The MySQL General Log:

180217 14:45:15       16 Connect   postfix@mail.domain.tld as anonymous on mail
      16 Connect   Access denied for user 'postfix'@'mail.domain.tld' (using password: YES)

The Postfix Log:

Feb 19 19:22:55 mail postfix/cleanup[11951]: warning: proxy:mysql:/etc/postfix/lookup_aliases.cf lookup error for "root@mail.domain.tld"
Feb 19 19:22:55 mail postfix/cleanup[11951]: warning: E2CCA2069823: virtual_alias_maps map lookup problem for root@mail.domain.tld -- message not accepted, try again later

Postfix Connection SUCCEEDS When REQUIRE SSL is Removed

This is Postfix successfully connecting after changing the permission from REQUIRE SSL to REQUIRE NONE. However, the connection is not encrypted.

180217 15:17:21       26 Connect   postfix@mail.domain.tld as anonymous on mail
          26 Query show databases
          26 Query show tables
          26 Field List    admin
          26 Field List    alias
          26 Field List    alias_domain
          26 Field List    config
          26 Field List    domain
          26 Field List    domain_admins
          26 Field List    fetchmail
          26 Field List    log
          26 Field List    mailbox
          26 Field List    quota
          26 Field List    quota2
          26 Field List    vacation
          26 Field List    vacation_notification
          26 Query select @@version_comment limit 1

Conclusion

All I need is for Postfix to honor ssl = true for its MySQL client connections. Please let me know if you need any further information.

Update:

After posting this question, I discovered through a more careful read that Postfix versions older than 2.11 do not support reading MySQL configuration files, at all. As such, it is impossible to configure the vendor-supplied version of Postfix (version 2.10) to use MySQL TLS. I feel for a poor wording choice in the Postfix documentation, where the upper portion of the Postfix documentation for MySQL configuration reads:

Postfix 3.1 and earlier don't read [client] option group settings unless a non-empty option_file or option_group value are specified. To enable this, specify, for example "option_group = client".

The author failed to spell out:

These options are ignored for Postfix 2.10 and earlier.  Postfix 2.11 through 3.1 don't read [client] option group settings unless a non-empty option_file or option_group value are specified. To enable this, specify, for example "option_group = client".

So, I upgraded Postfix on CentOS 7 from the vendor-supplied version 2.10 to the GhettoForge Plus supplied version 3.2. I did install the additional postfix3-mysql package. I had high hopes but those have been dashed. Now, even with Postfix 3.2, it still won't connect to MySQL over a TLS link.

I have attempted both option_file = /etc/my.cnf (should be the default and unnecessary) and option_file = /etc/my.cnf.d/client.cnf to no avail.

I even attempted to force Postfix to consider TLS by adding tls_verify_cert = yes. This also had no effect. Note that the certificate name does match, so this verification will pass if it is ever actually attempted.

Answer 1

Synopsis

In order to establish a MySQL TLS connection from Postfix, you need:

1. Postfix version 2.11 or newer. CentOS 7 only provides Postfix version 2.10. You must acquire an upgrade for Postfix from non-CentOS means. I elected to use the postfix3 and postfix3-mysql packages from GhettoForge Plus because it seems well recommended by the CentOS community.
2. Set within your mysql_table configuration files either:
   1. At least one permissible tls_ciphers setting;
   2. A client-side TLS certificate and its matching private key, signed by a Certificate Authority that is common to both the MySQL server and your Postfix client; or
   3. both.
3. Do not rely on setting these in your /etc/my.cnf or /etc/my.cnf.d/* files! The Postfix MySQL code doesn't read those files to determine whether to establish a MySQL TLS connection; they are parsed much too late. The only way to instigate Postfix to open a MySQL TLS connection is to specify the TLS settings within your mysql_table configuration files. Period.

How I Got Here

I finally resorted to reading through the Postfix 3.2 source code. Lines 653-658 of src/global/dict_mysql.c were especially informative.

Final Solution

If you want to emulate the behavior of ssl = true, you just need to add a line similar to the following to your mysql_table configuration files:

tls_ciphers = TLSv1.2

Our configuration requires that only TLS 1.2 be used -- and had already long been enforced in the configuration of our MySQL servers, so it didn't automatically occur to me to also enforce it on the clients -- but you are free to add other ciphers if your organization tolerates older ciphers.

Note that we are perfectly happy not using client-side certificates. We use other means to guard and actively monitor our database connections; we just don't want additional complexity.

Alternative Solution

If you would rather enforce that only known, trusted clients connect to your MySQL servers -- and you cannot do so through other policy enforcement mechanisms like tight firewalls, strict password complexity, password rotation periodicity, connection auditing, and such -- then you can also use client-side certificates via these additional settings in your mysql_table configuration files:

tls_key_file = /path/to/private.key
tls_cert_file = /path/to/public.certificate
tls_CAfile = /path/to/common.CA.certificate # OR tls_CApath = /path/to/CA-and-intermediate-chain-certificates/

Again, setting these in /etc/my.cnf or /etc/my.cnf.d/* won't help. These files aren't parsed until after the connection type (TLS or non-TLS) has already been decided.

If you opt to use client-side certificates, enforce them at the server! You do not need client-side certificates when all you want is to encrypt MySQL traffic; instead, just use the tls_ciphers alternative, above. Adding client-side certificates enables MySQL to verify that a connecting client is both already known and trusted but only if you tell MySQL how to do so! It won't guess and relying only on the presence of a common CA between client and server is only a partial implementation of this powerful tool. If you are interested in this MySQL security model, I found at least one How To guide that provides good examples for how to employ client certificate validation.