Set user to require SSL in mariaDB does not force connection to use certificate-CodePudding

My localhost is running MariaDB 10.6.7 on Win64.

I am trying to force SSL when connecting to MariaDB. I test the connection using MySQL Workbench 8.0.

To enable the SSL, I did the following:

I generate self signed certificate, then in my.ini, I set the following:

[mysqld]

datadir=C:/xampp/MariaDB 10.6/data

port=3306

innodb_buffer_pool_size=1009M

ssl-ca=C:/xampp/xampp/certs/mysql/ca-cert.pem

ssl-cert=C:/xampp/xampp/certs/mysql/server-cert.pem

ssl-key=C:/xampp/xampp/certs/mysql/server-key.pem

[client]

port=3306

plugin-dir=C:/Program Files/MariaDB 10.6/lib/plugin

ssl-ca=C:/xampp/xampp/certs/mysql/ca-cert.pem

ssl-cert=C:/xampp/xampp/certs/mysql/client-cert.pem

ssl-key=C:/xampp/xampp/certs/mysql/client-key.pem
To force SSL, I set the user in MariaDB to require SSL.

Unfortunately, with this configuration, I can still connect to MySQL without using any certificate. How can I force MariaDB to force SSL or FAIL?

My second question, I have a suspicion that my configuration is actually for setting using Require X509. Is this the case? If yes, how can I set it up to just use Require SSL?

Last, having the certificate set in my.ini, prevent me from connecting to mysql via the regular way, that is: mysql -u root. I got the following error:

 ERROR 2026 (HY000): SSL connection error: An unknown error occurred while processing 
 the certificate. Error 0x80090327(SEC_E_CERT_UNKNOWN).

I have tried to use the following command, mysql --ssl-ca=[directory]\client-cert.pem -u root, without success. I got the same error as above. Any idea?

If you have a reference page or any kind of insight, that would be appreciated.

Thanks.

CodePudding user response：

When creating a user, you need to add the require parameter, which can be x509 or ssl.

take for example

create user itpuxs2@'%' identified by 'root' require x509;

create user itpuxs3@'%' identified by 'root' require ssl;

CodePudding user response：

From my test in Win64, the ssl-ca, ssl-cert, ssl-key under [client] is not required for "Require SSL". This settings were also the reason why I could not login using "mysql -u root".

I use MySQL Workbench to test my connection to MariaDB. From my testing, even though I did not provide any certificate, as long as the server have certificate for SSL, then the connection would be encrypted (Require SSL). Having said that, I did try to provide a ca-cert.pem, but it did not work. From my research in google, it seems that this may due to the SSL Library used in MariaDB. The SSL library may be different for different distribution.

As for the X509, I was able to get it working in Linux. I did this once and was not doing further testing since I am working in windows at the moment.