Why does it seem Ssl_ca / CA authority is not being set for MariaDB server?
So I have my MariaDB server version: 10.6.12-MariaDB-0ubuntu0.22.04.1 Ubuntu 22.04.
What I would like to accomplish is that I use one-way TLS. If i understand correctly this means my MariaDB server identifies itself with a certificate issued by a trusted party. The client must of course ID itself with username and passwd, however not with any certificate. I would like this one-way TLS connection flawless, where the server CA-certificate is also positively verified.
The problem I'm running into is that while the connection works and seems to be TLS encrypted, the CA certificate seems to be missing. What I observe is that (1) when I connect using my DBeaver Client from home and I get Ssl_ca status, an empty response is given, (2) when I connect using phpMyAdmin, it says "SSL is used without certificate authority". I just cannot find why this is the case. Can you see what may be going wrong here, or what I am doing wrong here? Please let me know if you need more information.
SHOW STATUS LIKE 'Ssl_ca'; #Returns empty
(1) I have set the SSL-settings in "/etc/mysql/mariadb.conf.d/50-server.cnf" to point to a certificate with key and root certificate chain from a trusted certificate provider.
[mysqld]
ssl_ca = /etc/mysql/cacert.pem #Two root certificates that I got from provider
ssl_cert = /etc/mysql/server-cert.pem #Certificate issued by trusted provider
ssl_key = /etc/mysql/server-key.pem #Key for the certificate
require-secure-transport = on
(2) permissions and ownership are OK, (3) file paths are OK, (4) MariaDB starts and restarts correctly, (5) The root certificates belong to the certificate
openssl verify -CAfile /etc/mysql/cacert.pem /etc/mysql/server-cert.pem #Returns OK
(6) the issued certificate is in X509 format, (7) REQUIRE SSL is enabled for my user that I connect remotely from,
openssl x509 -text -noout -in /etc/mysql/server-cert.pem #Returns description of certificate
(8) Remote user connects with: Use SSL: Y, Require SSL: Y, Verify server certificate: Y, Any specific client-side certificates set: N. (9) Connecting remotely with all this works fine, (10) Ssl_cipher has value: TLS_AES_256_GCM_SHA384
SHOW STATUS LIKE 'Ssl_cipher';
(11) Checked with OpenSSL from client server, it shows my certificate with correct certificate chain.
openssl s_client -connect my.server.randomtld:3306 -starttls mysql
(12) Firewall is all fine, I'm way past that point.
iptables -D INPUT -p tcp --dport 3306 -j DROP
sudo iptables -A INPUT -p tcp --dport 3306 -s 111.111.111.111 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 3306 -s 222.222.222.222 -j ACCEPT
sudo iptables -A INPUT -p tcp --dport 3306 -j DROP
