question

RaoulScarazzini-6328 avatar image
0 Votes"
RaoulScarazzini-6328 asked NavtejSaini-MSFT commented

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

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

azure-database-mariadb
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RaoulScarazzini-6328
We are checking this and will get back to you.

Regards
Navtej S

0 Votes 0 ·

Thanks, @NavtejSaini-MSFT,
we really need a way to at least work around the problem, but of course, it would be optimal to get from somewhere the keys.

0 Votes 0 ·

Hi, any news on this?

0 Votes 0 ·

@RaoulScarazzini-6328

Please let us know if you need any further help with this.

Thanks
Navtej S

0 Votes 0 ·
Show more comments
NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered NavtejSaini-MSFT commented

@RaoulScarazzini-6328

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


· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Navtej,
thanks for your answer, and yes we must use MaxScale. We don't think it's a matter of permissions, at least from what we see from the logs.
We've tested everything with the latest Maxscale 2.5:

 2021-01-25 13:45:18   info   : (2) Found matching user '<MYUSER>@<MYAZUREMARIADBMASTERDNSNAME>'@'%' for client '<MYUSER>@<MYAZUREMARIADBMASTERDNSNAME>'@'<MYCLIENTIP>' with sufficient privileges.
 2021-01-25 13:45:18   info   : (2) [readconnroute] (read-service) New session for server master-node-1. Connections : 1
 2021-01-25 13:45:18   info   : (2) Started read-service client session [2] for '<MYUSER>@<MYAZUREMARIADBMASTERDNSNAME>' from <MYCLIENTIP>
 2021-01-25 13:45:18   info   : (2) Connected to 'master-node-1' with thread id 64306

Since we get "with sufficient privileges", it seems that Maxscale is able to verify the user existence and its permissions.

0 Votes 0 ·

The problem is still over the SSL part, because after the above messages we get:

 2021-01-25 13:45:18   error  : (2) SSL operation failed, Backend DCB in state DCB::State::POLLING fd 63 return code -1. More details may follow.
 2021-01-25 13:45:18   error  : (2) error:140770FC:SSL routines:SSL23_GET_SERVER_HELLO:unknown protocol
 2021-01-25 13:45:18   info   : (2) [readconnroute] (read-service) Server 'master-node-1' failed
 2021-01-25 13:45:18   info   : (2) [readconnroute] (read-service) Server 'master-node-1' failed
 2021-01-25 13:45:18   info   : Stopped read-service client session [2]

While on the client side:

 ERROR 2013 (HY000): Lost connection to MySQL server during query

And that's it.

Do you have any suggestions or tests for us?

0 Votes 0 ·

One last addition: without using SSL everything is fine, to underline that the problem is essentially on the SSL side of things.

0 Votes 0 ·
Show more comments
NavtejSaini-MSFT avatar image
0 Votes"
NavtejSaini-MSFT answered

@RaoulScarazzini-6328

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):
61139-1.jpg


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



1.jpg (71.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

RaoulScarazzini-6328 avatar image
0 Votes"
RaoulScarazzini-6328 answered NavtejSaini-MSFT commented

Hi @NavtejSaini-MSFT 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).

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@RaoulScarazzini-6328

We were happy to help and we thank you for your help to complete the thread.

Regards
Navtej S

0 Votes 0 ·