Bug in implementation of SSL/TLS secured MySQL connections?

[m-erhardt]

Describe the bug

I think there might be a bug with regards to SSL/TLS encrypted MySQL database connections in Icingaweb2.

I was just setting up a new Icinga2 cluster and out of habbit I created the DB users with REQUIRE SSL.

CREATE USER 'icinga2'@'%' IDENTIFIED BY '***' REQUIRE SSL;
CREATE USER 'icingaweb2'@'%' IDENTIFIED BY '***' REQUIRE SSL;

When configuring the DB connecitons in the setup wizard I was getting the following error message.

After removing the requirement for SSL/TLS encrypted connections on the DB I was able to complete the setup wizard (with Use SSL still enabled)

ALTER USER 'icingaweb2'@'%' REQUIRE NONE;
ALTER USER 'icinga2'@'%' REQUIRE NONE;
FLUSH PRIVILEGES;

Icingaweb2 seemed to work fine. Initially I though this might just be an bug within the setup wizard and reenabled the requirement for SSL encrypted DB connections.

ALTER USER 'icingaweb2'@'%' REQUIRE SSL;
ALTER USER 'icinga2'@'%' REQUIRE SSL;
FLUSH PRIVILEGES;

Instantly the errors were back:

To double check I removed the reqirement for SSL connections from the DB users again. As use_ssl = "1" was still configured in /etc/icingaweb2/resources.ini I still expected Icingaweb2 to encrypt it's DB connections even though I did not enforce it on the DB anymore.

Wireshark cleary revealed that the connections were unencrypted even though use_ssl = "1" was enabled for the DB ressource.

By the way: the same database works fine with Icinga2 and REQUIRE SSL enabled on the DB. This just seems to affect Icingaweb2.

To Reproduce

See error description above

Expected behavior

Use SSL/TLS enrypted MySQL database connection when use_ssl = "1" is set.

Your Environment

OS: CentOS 8.3 with latest updates MariaDB: 10.5.6 (from official MariaDB yum/dnf repos) PHP: 7.2.24 (from CentOS AppStream repo) Icingaweb2: icingaweb2-2.8.2-1.el8.icinga.noarch (from "icinga-stable-release" repo at packages.icinga.com)

[nilmerg]

Hi,

checking only the box does nothing. The box is only there to let the SSL options show up. You'll need to define the client's key + certificate and the CA's certificate also:

[m-erhardt]

@nilmerg

From my understanding the settings you mention configure MySQL authentication via x509 certificates (which is not what I want to use).

I still want to use user/password based authentication and only tunnel the MySQL connections through SSL/TLS.

So a client key/certificate should not be required for this and the CA certificate is imported into the ca-bundle of the OS so the mysql client should be able to verify the server cert.

Connection to the DB is possible from the mysql CLI client via mysql -u icinga2 -p -h sqlserver.hostname icinga2 and the REQUIRE SSL-requirement set for the DB user. These connections are tunneled through TLS as expected.

[nilmerg]

Very well, then leave them out but you still need the CA's certificate.

[m-erhardt]

Thanks @nilmerg ! Adding ssl_ca = "/etc/pki/tls/certs/chain.pem" to the database config in resources.ini did fix the problem.

Probably still a something that could be improved? It seems that Icingaweb2 does not use the ca-certificates from the operating system at this point (PHP usually does, there was no need to specify the CA certificates for the ldaps ressource).

From my perspective MySQL connections should fail if use_ssl = "1" is set instead of silenty falling back to unencrypted connections.

If I would not have enforced encrypted connections on the database I would'nt have noticed this and would have thought that that the database connections are secure/encrypted.

[lippserd]

From my perspective MySQL connections should fail if use_ssl = "1" is set instead of silenty falling back to unencrypted connections.

I'm not quite sure if we ever really discussed this but that makes sense. @nilmerg For Icinga DB's Redis TLS configuration we will also fail instead of defaulting to unencrypted connections.

[nilmerg]

We can take a look why the OS's ca bundle is not used for SQL connections. But I guess that it's not possible for us to influence. At least we do nothing in regards to ldap connections, so that's automatically done by the php extension. If it's not done automatically by the pdo extension, and it's not adjustable by us, we can't improve this.

I'm not quite sure if we ever really discussed this but that makes sense.

Sure, why not. Though, we can only avoid making the user think it's done with checking that box by requiring at least the CA certificate to be configured for SSL connections. We cannot verify that the connection is then really a SSL/TLS one, or can we? (Running a query to check which is running on every single connection attempt is not really an option I suppose)

As shown here, it's also possible to explicitly allow or disallow the fallback with REQUIRE None and REQUIRE SSL respectively. So maybe the fallback is not an error and we shouldn't fail then?

Oh, and failing a connection if only use_ssl is configured is bad I think, as it will surely break some environments out there which are wrong configured but work fine. I only see this as a form validation.