MySQL Bugs: #112170: ssl_mode=VERIFY_IDENTITY fails for driver=native

Bug #112170	ssl_mode=VERIFY_IDENTITY fails for driver=native_sshtun
Submitted:	23 Aug 2023 16:19	Modified:	27 Apr 2024 12:29
Reporter:	Simon Schneider	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Workbench	Severity:	S3 (Non-critical)
Version:	8.0.34	OS:	Windows (Windows 10 Enterprise 21H2)
Assigned to:	MySQL Verification Team	CPU Architecture:	x86 (Intel Celeron G5905)

Description:
Workbench fails to verify an instance's certificate if the connection uses a ssh tunnel and identity verification is configured. 

Instead of the instance's hostname, Workbench seems to try and check the ssh jump host's name against the certificate. Verification succeeds if either:
  - a different ssl_mode is configured, or
  - the instance's certificate covers the jump host's name.

How to repeat:
1. Set up certificates and keys
  - ca.pem is the certificate chain for a certificate authority
  - db_server.key is a new private key
  - db_server.csr is a new signing request with the following attributes:
      Subject: CN = db_server.local
      Subject Alternative Name:
        DNS: db_server, DNS: db_server.local
     db_server.csr must be signed by db_server.key
  - db_server.cert is the signed certificate produced by the certificate authority from db_server.csr

2. Set up three nodes:
  - db_client.local has Workbench installed, knows ca.pem
  - jump_host.local has sshd installed
  - db_server.local has MySQL Server installed, knows db_server.cert, db_server.key and ca.pem

3. Configure Workbench with ssl_mode=VERIFY_IDENTITY
  Connection Method: Standard TCP/IP over SSH
  SSH Hostname: jump_host.local 
  MySQL Hostname: db_server.local
  (Usernames and Password/Key files left out for brevity)
  Use SSL: Require and Verify Identity
  SSL CA File: ca.pem

4. Test Connection
  The connection test will fail, because the certificate could not be validated.

Suggested fix:
I supect that ssl_mode=VERIFY_IDENTITY leads Workbench to use the wrong hostname for validation. Two experiments support this hypothesis:

1. Configure Workbench with ssl_mode=VERIFY_CA
  - Apply Configuration as above, but with:
    Use SSL: Require and Verify CA (or any other setting)
  - Test Connection
    The connection test will succeed

2. Extend db_server.cert to cover the jump host's name
  - Set up certificates and keys as above, but add to the attributes for db_server.csr:
    Subject alternative Name:
      DNS: db_server, DNS: db_server.local, DNS: jump_host, DNS: jump_host.local
  - Test Connection
    The connection test will succeed, even with ssl_mode=VERIFY_IDENTITY

I suggest finding out how Workbench confuses the two hostnames and to ensure that only the database server's hostname is used in certificate validation.

HI Mr. Schneider,

8.0.22 is an old version.

Please, let us know if the bug persists in 8.0.34.

We are waiting on your feedback.

Hi,

the symptoms have changed, or rather: I cannot reproduce the exact same problem with 8.0.34. Do you want me to open another bug or can we stay with this one?

The problem is now: ssl_mode=VERIFY_IDENTITY fails for driver=native_sshtun, no matter the hostname or certificate details. 

If i use the same certificates but with "Standard (TCP(IP)" as the connection method, the connection succeeds and the verification works.

Kind regards
Simon Schneider

Hello Simon,

Thank you for the feedback.
Do you see any issues when tested with 8.0.34? If yes, please provide exact steps to reproduce this issue at our end.

Regards,
Ashwini Patil

Hello,

here are detailed instructions to reproduce the issue with MySQL Community Server 8.0.34 and MySQL Workbench 8.0.34.
For your convenience, I have provided a bash script for step 1 and the minimal config file from step 2.3 as attachments.
My apologies for the wall of text, but I don't have very many formatting options here.

1. Set up certificates and keys with openssl
  The command lines in this section, when run on a linux machine, will create the following files:
    - selfsigned-ca.pem is a self-signed certificate chain for a certificate authority
    - db_server.key is a new private key
    - db_server.csr is a new signing request with the following attributes:
        Subject: CN = db_server.local
        Subject Alternative Name:
          DNS: db_server, DNS: db_server.local
       db_server.csr must be signed by db_server.key
    - db_server.pem is the signed certificate produced by the certificate authority from db_server.csr

  user@host:/home> openssl req -verbose -new -x509 -newkey rsa:2048 -days 10 -nodes \
  > -subj '/CN=Self-signed CA' \
  > -keyout selfsigned-ca.key -out selfsigned-ca.pem
  user@host:/home> openssl req -verbose -newkey rsa:2048 -nodes \
  > -subj '/CN=db_server.local' \
  > -keyout db_server.key -out db_server.csr
  user@host:/home> echo 'subjectAltName=DNS:db_server,DNS:db_server.local' > db_server.ext
  user@host:/home> openssl x509 -req -days 10 -set_serial 01 \
  > -CA selfsigned-ca.pem -CAkey selfsigned-ca.key \
  > -in db_server.csr -extfile db_server.ext \
  > -out db_server.pem

  You can check the created certificates like this:
  user@host:/home> openssl verify -verbose -CAfile selfsigned-ca.pem -verify_hostname db_server.local db_server.pem

2. Set up three nodes

2.1 db_client.local, a windows machine
  - let's say you log in as: DOMAIN\UserName
  - install MySQL Workbench from mysql-workbench-community-8.0.34-winx64.msi
  - put selfsigned-ca.pem in the user's home, e.g. as C:\Users\UserName\selfsigned-ca.pem

2.2 jump_host.local, a linux machine
  - let's say you log in as: jump@jump_host
  - install sshd from the os package manager

2.3 db_server.local, a linux machine
  - let's say you log in as: mysql@db_server:/home/mysql
    execute all command lines in your $HOME, e.g. /home/mysql
  - install MySQL from mysql-8.0.34-linux-glibc2.28-x86_64.tar.gz:
    - unpack the archive to your home:
      mysql@db_server:/home/mysql> tar xf mysql-8.0.34-linux-glibc2.28-x86_64.tar.gz
      mysql@db_server:/home/mysql> mv mysql-8.0.34-linux-glibc2.28-x86_64/ mysql
    - put selfsigned-ca.pem, db_server.pem and db_server.key in your home
    - put the following minimal my.cnf into your home:
      mysql@db_server:/home/mysql> cat > my.cnf <<EOF
      > [mysqld]
      > ssl_ca   = /home/mysql/selfsigned-ca.pem
      > ssl_cert = /home/mysql/db_server.pem
      > ssl_key  = /home/mysql/db_server.key
      > EOF
    - initialize the data directory
      mysql@db_server:/home/mysql> mysql/bin/mysqld --defaults-file=/home/mysql/my.cnf --initialize
      look out for the line that says "[Note] [MY-010454] [Server] A temporary password is generated for root@localhost" and remember the initial password
  - start the database server
    mysql@db_server:/home/mysql> mysql/bin/mysqld --defaults-file=/home/mysql/my.cnf
    this command will not return to the command line until the server is shut down, so any further command need to be given in a separate shell.
    observe these two lines in the log output:
      "[Warning] [MY-010068] [Server] CA certificate /home/mysql/selfsigned-ca.pem is self signed."
      "[System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel."
  - change the password for the database root user and create a test user
    mysql@db_server:/home/mysql> mysql/bin/mysql --user root --password
    mysql> alter user current_user identified by '<new-root-password>';
    mysql> create user db_client identified by '<new-client-password>';

    You can check the connection parameters like this:
    mysql@db_server:/home/mysql> mysql/bin/mysql --protocol tcp --host localhost --port 3306 \
    > --ssl-mode=VERIFY_IDENTITY --ssl-ca=/home/mysql/selfsigned-ca.pem \
    > --user db_client --password

3. Configure Workbench
Workbench is installed on db_client.local and run as DOMAIN\UserName (see above).
If the Workbench complains about invalid padding in selfsigned-ca.pem, you'll need to re-create the certificates as in step 1.

3.1 add a new connection
  Connection Name: mysql@db_server
  Connection:
    Connection Method: Standard (TCP/IP)
    Parameters:
      Hostname: db_server.local
      Port:     3306
      Username: db_client
    SSL:
      Use SSL: Require and Verify Identity
      SSL CA File: C:\Users\UserName\selfsigned-ca.pem

3.2 add a new connection via an ssh tunnel
  Connection Name: mysql@db_server
  Connection:
    Connection Method: Standard (TCP/IP) over SSH
    Parameters:
      SSH Hostname: jump_host.local
      SSH Username: jump
      MySQL Hostname:    db_server.local
      MySQl Server Port: 3306
      MySQL Username:    db_client
    SSL:
      Use SSL: Require and Verify Identity
      SSL CA File: C:\Users\UserName\selfsigned-ca.pem

4. Test the connections and observe the problem
  - The connection from step 3.1 works.
    Execute the statement "SHOW STATUS LIKE 'Ssl_cipher';" and observe a non-null value.
  - The connection from step 3.2 doesn't work.
    You will receive an error message saying: "SSL connection error: error:0A000086:SSL routines::certificate verify failed"
  - Edit the connection from step 3.2 to only "Require and Verify CA".
    The connection now works.
    Execute the statement "SHOW STATUS LIKE 'Ssl_cipher';" and observe a non-null value.

Thank you for your time, kind regards
Simon Schneider

Creates a new tls certificate and a self-signed ca certificate to validate against

Attachment: makecert-selfsigned.sh (application/x-shellscript, text), 1.13 KiB.

I seem unable to attach my minimal my.cnf. My browser reports:

Access Denied
You don't have permission to access "http://splash.oracle.com/bug.php?" on this server.

Reference #18.9f7b1302.1694795948.192a49fc

Thank you for the details. Please try with latest version. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".