[MDEV-32641] Master_SSL_Verify_Server_Cert doesnt work for SSL replication Created: 2023-11-01  Updated: 2023-11-07  Resolved: 2023-11-06

Status: Closed
Project: MariaDB Server
Component/s: SSL
Affects Version/s: 10.4, 10.6
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Susmeet Khaire Assignee: Susmeet Khaire
Resolution: Not a Bug Votes: 0
Labels: None

Attachments: File master_client.cnf     File master_server.cnf     File slave_client.cnf     File slave_server.cnf    

 Description   

I followed this blog to create SSL certificates and enable SSL replication.

MariaDB Master config: master_server.cnf master_client.cnf
MariaDB Slave config: slave_server.cnf slave_client.cnf

Certificate verification and common names

[root@mariadb-server2 certs]# openssl verify -CAfile ca-cert.pem server-cert.pem client-cert.pem
 
server-cert.pem: OK
 
client-cert.pem: OK
 
 
 
[root@mariadb-server2 certs]# openssl x509 -noout -subject -in ca-cert.pem
 
subject= /C=AU/ST=NSW/L=SYDNEY/O=MARIADB/OU=IT/CN=mariadb-server2/emailAddress=email@mariadb.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-server3/emailAddress=email@mariadb.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-slave2/emailAddress=email@mariadb.com

I enabled SSL replication using the following CHANGE MASTER TO command (1st without server cert verification).

MariaDB [(none)]> STOP SLAVE;CHANGE MASTER TO MASTER_HOST='mariadb-server2', MASTER_USER='repl_ssl', 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';

Replication starts as expected and without any issues

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_ssl
 
                   Master_Port: 3306
 
                 Connect_Retry: 60
 
               Master_Log_File: mariadb-server2-bin.000004
 
           Read_Master_Log_Pos: 391
 
                Relay_Log_File: mariadb_slave3-relay-bin.000002
 
                 Relay_Log_Pos: 700
 
         Relay_Master_Log_File: mariadb-server2-bin.000004
 
              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: 391
 
               Relay_Log_Space: 1018
 
               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-54
 
       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: 8
 
Slave_Non_Transactional_Groups: 1
 
    Slave_Transactional_Groups: 0
 
          Retried_transactions: 0
 
            Max_relay_log_size: 1073741824
 
          Executed_log_entries: 146
 
     Slave_received_heartbeats: 0
 
        Slave_heartbeat_period: 30.000
 
                Gtid_Slave_Pos: 0-100-54
 
1 row in set (0.000 sec)

But when I enable Master_SSL_Verify_Server_Cert, replication fails 

MariaDB [(none)]> STOP SLAVE;
 
Query OK, 0 rows affected (0.023 sec)
 
 
 
MariaDB [(none)]> CHANGE MASTER TO Master_SSL_Verify_Server_Cert=1;
 
Query OK, 0 rows affected (0.022 sec)
 
 
 
MariaDB [(none)]> START SLAVE;
 
Query OK, 0 rows affected (0.035 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: Connecting to master
 
                   Master_Host: mariadb-server2
 
                   Master_User: repl_ssl
 
                   Master_Port: 3306
 
                 Connect_Retry: 60
 
               Master_Log_File: mariadb-server2-bin.000004
 
           Read_Master_Log_Pos: 391
 
                Relay_Log_File: mariadb_slave3-relay-bin.000001
 
                 Relay_Log_Pos: 4
 
         Relay_Master_Log_File: mariadb-server2-bin.000004
 
              Slave_IO_Running: Connecting
 
             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: 391
 
               Relay_Log_Space: 256
 
               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: NULL
 
 Master_SSL_Verify_Server_Cert: Yes
 
                 Last_IO_Errno: 2026
 
                 Last_IO_Error: error connecting to master 'repl_ssl@mariadb-server2:3306' - retry-time: 60  maximum-retries: 100000  message: SSL connection error: SSL certificate validation failure
 
                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-54
 
       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: 8
 
Slave_Non_Transactional_Groups: 1
 
    Slave_Transactional_Groups: 0
 
          Retried_transactions: 0
 
            Max_relay_log_size: 1073741824
 
          Executed_log_entries: 147
 
     Slave_received_heartbeats: 0
 
        Slave_heartbeat_period: 30.000
 
                Gtid_Slave_Pos: 0-100-54
 
1 row in set (0.000 sec)

With Master_SSL_Verify_Server_Cert being enabled by default from MariaDB 11, this could be a major issue.

 Comments   
Comment by Roel Van de Paar [ 2023-11-06 ]

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

Comment by Susmeet Khaire [ 2023-11-06 ]

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)

Comment by Roel Van de Paar [ 2023-11-06 ]

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.

Comment by Roel Van de Paar [ 2023-11-06 ]

i.e. CN=MYCA (CA) vs CN=mariadb-server2 (master/slave)

Comment by Hartmut Holzgraefe [ 2023-11-06 ]

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

Comment by Roel Van de Paar [ 2023-11-06 ]

Yes, CN mismatch is another potential issue as well. There seemed to be a similar issue in the support case (comment added).

Generated at Thu Feb 08 10:32:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.