Roel Van de Paar added a comment - 2023-11-06 02:19 - edited In terms of self-certification, note that the CA's CN needs to be different. Certs creation script: #!/bin/bash   # Cleanup old certificates # TAKE CARE BEFORE RUNNING THIS SCRIPT AS IT WILL DELETE ALL *.pem files rm -f *.pem   # The CN for the server and client certs/keys must differ from the CN used for the CA. If not done correctly, the certs will not work for servers compiled using OpenSSL. Thus, notice the 'MYCA' in CA_CONFIG's CN. CA_CONFIG= "/C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=MYCA/emailAddress=address@email.com"   SERVER_CONFIG= "/C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=$(cat /etc/hostname | tr -d '\n')/emailAddress=address@email.com" CLIENT_CONFIG= "/C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=$(cat /etc/hostname | tr -d '\n')/emailAddress=address@email.com" # Other options #SERVER_CONFIG="/C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=127.0.0.1/emailAddress=address@email.com" #CLIENT_CONFIG="/C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=127.0.0.1/emailAddress=address@email.com" #SERVER_CONFIG="/C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=localhost/emailAddress=address@email.com" #CLIENT_CONFIG="/C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=localhost/emailAddress=address@email.com"   # new ca-key openssl genrsa 2048 > ca-key.pem openssl req -new -x509 -nodes -days 9999 -key ca-key.pem -subj "${CA_CONFIG}" > ca-cert.pem   # server certs openssl req -new -newkey rsa:2048 -nodes -keyout server-key.pem -subj "${SERVER_CONFIG}" -out server-req.pem openssl x509 -req - in server-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 01 -out server-cert.pem   # client certs openssl req -newkey rsa:2048 -nodes -keyout client-key.pem -subj "${CLIENT_CONFIG}" -out client-req.pem openssl x509 -req - in client-req.pem -days 3600 -CA ca-cert.pem -CAkey ca-key.pem -set_serial 02 -out client-cert.pem   # verify certs openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem   # Check files ls -l *.pem   # Check certs openssl x509 -noout -subject - in ca-cert.pem openssl x509 -noout -subject - in server-cert.pem openssl x509 -noout -subject - in client-cert.pem

Susmeet Khaire added a comment - 2023-11-06 03:55 - edited Hi Roel I ran the script the script that you provided. And it generated the following certs for me. Note that since the script was run on the same server and CN is the hostname, so the CN would be same for client & server certs. [root @mariadb -server2 certs]# openssl x509 -noout -subject -in ca-cert.pem subject= /C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=MYCA/emailAddress=address @email .com   [root @mariadb -server2 certs]# openssl x509 -noout -subject -in client-cert.pem subject= /C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=mariadb-server2/emailAddress=address @email .com   [root @mariadb -server2 certs]# openssl x509 -noout -subject -in server-cert.pem subject= /C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=mariadb-server2/emailAddress=address @email .com   [root @mariadb -server2 certs]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem server-cert.pem: OK client-cert.pem: OK Despite the CN being the same, the verification passes. Not sure how. As per the blog here And suprisingly, replication also works. MariaDB [(none)]> STOP SLAVE;CHANGE MASTER TO MASTER_HOST= 'mariadb-server2' , MASTER_USER= 'repl' , MASTER_PASSWORD= 'Test@123' , MASTER_USE_GTID=slave_pos, MASTER_SSL= 1 , MASTER_SSL_CA= '/etc/my.cnf.d/certs/ca-cert.pem' , MASTER_SSL_CERT= '/etc/my.cnf.d/certs/client-cert.pem' , MASTER_SSL_KEY= '/etc/my.cnf.d/certs/client-key.pem' ;START SLAVE; Query OK, 0 rows affected, 1 warning ( 0.000 sec)   Query OK, 0 rows affected ( 0.039 sec)   Query OK, 0 rows affected ( 0.015 sec)   MariaDB [(none)]> SHOW ALL SLAVES STATUS\G *************************** 1 . row *************************** Connection_name: Slave_SQL_State: Slave has read all relay log; waiting for more updates Slave_IO_State: Waiting for master to send event Master_Host: mariadb-server2 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-server2-bin. 000002 Read_Master_Log_Pos: 837 Relay_Log_File: mariadb_slave3-relay-bin. 000002 Relay_Log_Pos: 704 Relay_Master_Log_File: mariadb-server2-bin. 000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 837 Relay_Log_Space: 1022 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/my.cnf.d/certs/ca-cert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/my.cnf.d/certs/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/my.cnf.d/certs/client-key.pem Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100 Master_SSL_Crl: /etc/my.cnf.d/certs/ca-cert.pem Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0 - 100 - 62 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 36 Slave_Non_Transactional_Groups: 9 Slave_Transactional_Groups: 0 Retried_transactions: 0 Max_relay_log_size: 1073741824 Executed_log_entries: 131 Slave_received_heartbeats: 0 Slave_heartbeat_period: 30.000 Gtid_Slave_Pos: 0 - 100 - 62 1 row in set ( 0.001 sec)   MariaDB [(none)]> STOP SLAVE;CHANGE MASTER TO Master_SSL_Verify_Server_Cert= 1 ; Query OK, 0 rows affected ( 0.028 sec)   Query OK, 0 rows affected ( 0.015 sec)   MariaDB [(none)]> START SLAVE; Query OK, 0 rows affected ( 0.027 sec)   MariaDB [(none)]> SHOW ALL SLAVES STATUS\G *************************** 1 . row *************************** Connection_name: Slave_SQL_State: Slave has read all relay log; waiting for more updates Slave_IO_State: Waiting for master to send event Master_Host: mariadb-server2 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mariadb-server2-bin. 000002 Read_Master_Log_Pos: 837 Relay_Log_File: mariadb_slave3-relay-bin. 000002 Relay_Log_Pos: 704 Relay_Master_Log_File: mariadb-server2-bin. 000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 837 Relay_Log_Space: 1022 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: Yes Master_SSL_CA_File: /etc/my.cnf.d/certs/ca-cert.pem Master_SSL_CA_Path: Master_SSL_Cert: /etc/my.cnf.d/certs/client-cert.pem Master_SSL_Cipher: Master_SSL_Key: /etc/my.cnf.d/certs/client-key.pem Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: Yes Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 100 Master_SSL_Crl: /etc/my.cnf.d/certs/ca-cert.pem Master_SSL_Crlpath: Using_Gtid: Slave_Pos Gtid_IO_Pos: 0 - 100 - 62 Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: optimistic SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Slave_DDL_Groups: 36 Slave_Non_Transactional_Groups: 9 Slave_Transactional_Groups: 0 Retried_transactions: 0 Max_relay_log_size: 1073741824 Executed_log_entries: 139 Slave_received_heartbeats: 0 Slave_heartbeat_period: 30.000 Gtid_Slave_Pos: 0 - 100 - 62 1 row in set ( 0.000 sec)

Roel Van de Paar added a comment - 2023-11-06 04:11 Thank you for confirming. The CN for the CA (certificate authority) should be different from the CN's for the master/client certs, which is also what the issue was.

Roel Van de Paar added a comment - 2023-11-06 04:12 i.e. CN=MYCA (CA) vs CN=mariadb-server2 (master/slave)

Hartmut Holzgraefe added a comment - 2023-11-06 08:13 The actual verification problem seems to be that the server certificate has CN=mariadb-slave2 but the slave connects to mariadb-server2 and so gets a mismatch when having host name verification enabled

Roel Van de Paar added a comment - 2023-11-06 08:26 - edited Yes, CN mismatch is another potential issue as well. There seemed to be a similar issue in the support case (comment added).