Getting SSL keys+certs from deployed MariaDB instances to be used with MaxScale

Question

Hi everybody,
is there a way to get for each MariaDB deployed instance the relative SSL certificate+key couple?

Looking at the MariaDB SSL documentation there's no mention of keys or certificates, but just the CA, which is specifically this https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem.

Point is that to make MaxScale enforce using SSL, you need to pass the CA, the certificate and the key.
Otherwise, if you specify just the CA, you get this:

2021-01-18 17:26:05   error  : (2) SSL operation failed, Backend DCB in state DCB::State::POLLING fd 86 return code -1. More details may follow.  
2021-01-18 17:26:05   error  : (2) error:1408F10B:SSL routines:ssl3_get_record:wrong version number  

And all the connections fail.

Can you help me find out a workaround on this?

Many thanks!

Raoul

Answer 1

@Raoul Scarazzini

We got the response from our Team:

It seems the Maxscale documentation which you referred too is a bit confusing and ambiguous so we further investigated and tested it on our side. For your reference, a good documentation to refer to for this scenario would be https://github.com/mariadb-corporation/MaxScale/blob/2.5/Documentation/Getting-Started/Configuration-Guide.md#tlsssl-encryption. I am copying the important section below

To enable TLS/SSL for a server, you must set the ssl parameter to true. If the backend database server has certificate verification enabled, the ssl_cert and ssl_key parameters must also be defined. Custom CA certificates can be defined with the ssl_ca_cert parameter.

For connecting to Azure Database for MariaDB only ssl=true needs to be enabled and only ssl_ca_cert needs to be specified point to the path to our CA certificate file ssl_ca_cert=/path/to/BaltimoreCyberTrustRoot.crt.pem. However we have tested Maxscale on our side and some components of Maxscale may not work with Azure Database for MariaDB service as the username in Azure DB for MariaDB service is in the format username@servername. You can refer to this blog for why username@servername format is required.

Following is our recommendation

• Use ProxySQL if the intent is to use Maxscale for connection pooling and read/write split. You can refer to our blogs below for reference
  o https://techcommunity.microsoft.com/t5/azure-database-for-mysql/load-balance-read-replicas-using-proxysql-in-azure-database-for/ba-p/880042
  o Connecting efficiently to Azure Database for MySQL with ProxySQL - Microsoft Tech Community
• If you have to use Maxscale
  o Use the latest version of MaxScale and config it with the official documentation on GitHub: https://github.com/mariadb-corporation/MaxScale/blob/2.5/Documentation/Tutorials/MaxScale-Tutorial.md
  o Create separate user accounts for different components (monitors, services, etc) of MaxScale so it is easy to know which part works and which part doesn’t by a “show processlist” command on the server.

Please go through the same and let us know if you have any further question.

Regards
Navtej S

Answer 2

@Raoul Scarazzini

Here is the note from our team:

"On investigation, we found that Maxscale pads many zeros in its SSL request packet. The additional padded zeros hits the limitation on our Azure DB for MariaDB service and caused the SSL login request being treated as a clear text login request. We acknowledge this is a limitation of our service but it can also be mitigated by avoiding additional padding on Maxscale as on the service side, the issue is limitation by underlying infrastructure design.

To mitigate the issue, we just need to remove the unnecessary padding logic in MaxScale’s MariaDB protocol and then install MaxScale from source. Our engineering team created this PR to demonstrate how to do this: remove padding in ssl request packet by ThomasDai · Pull Request #1 · ThomasDai/MaxScale (github.com):

If the workaround is acceptable for you, you can use the above PR to be unblocked right away and can test it. At this point, we are not confident if the service side limitation can be overcomed quickly but we will review it and see if we can resolve it. Currently we cannot provide any timelines for the service side fix."

Thanks
Navtej S

Answer 3

Hi @Navtej Singh Saini and thanks for keeping this request alive.
I can confirm that compiling from source with the patch https://github.com/ThomasDai/MaxScale/pull/1/files#diff-bef3686f29d7975258d9f9da160ec463103d245b3f391f9d3849f6df6506e9c4 make things work.
For the sake of completeness, here's what needs to be done to use MaxScale with SSL on Azure:

1. You need to use an Ubuntu VM for MaxScale, otherwise you will not be able to recompile the package because in CentOS (7 or 8) it simply won't compile. Best way is to generate a package to be installed.
   The procedure is the one described in https://github.com/mariadb-corporation/MaxScale/blob/2.5/Documentation/Getting-Started/Building-MaxScale-from-Source-Code.md, so: git clone https://github.com/mariadb-corporation/MaxScale
   <apply the above patch on the MaxScale/server/modules/protocol/MariaDB/mariadb_backend.cc file>
   mkdir build
   cd build
   ../MaxScale/BUILD/install_build_deps.sh
   cmake ../MaxScale -DPACKAGE=Y -DTARGET_COMPONENT=core
   make package
2. You need to use the ca cert https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.pem so to get a configuration for each server, like this one: [master-node-1]
   type=server
   address=<YOURDBMASTER>.mariadb.database.azure.com
   port=3306
   protocol=mariadbbackend
   ssl=required
   ssl_ca_cert=/etc/ssl/certs/BaltimoreCyberTrustRoot.crt.pem
   [slave-node-1]
   type=server
   address=<YOURDBSLAVE>.mariadb.database.azure.com
   port=3306
   protocol=mariadbbackend
   ssl=required
   ssl_ca_cert=/etc/ssl/certs/BaltimoreCyberTrustRoot.crt.pem
3. You need to create this set of users to have every component to communicate properly:
   • one user WITHOUT the domain part, i.e. maxscale, that will be used by MaxScale for monitor/authentication;
   • one user WITHOUT the domain part, i.e. maxscaleread, that will be used by MaxScale for the service you want to implement (in this case readconnroute, so it needs SELECT grants);
   • for each user that will be used to connect you'll need a couple: one WITH and the other WITHOUT the domain part (same password), because one will be used to authenticate versus maxscale and the other will be used by maxscale to present itself over the backends;

With all this in place everything in my tests worked as expected.

We implemented specific playbooks, given a coherent inventory, that can help on this by developing the Terraform files to create database instances and vm (https://github.com/mmul-it/ansible/tree/master/roles/terraform) and deploy MaxScale (https://github.com/mmul-it/ansible/tree/master/roles/maxscale).