[Build] caching_sha2_password authentication module does not work on Ubuntu 22.04

[pvannierop]

What OS and which version do you use?

Ubuntu 22.04

How did you installed mysql client library?

On top of a Ubuntu:22.04 (jammy) image I ran (in Dockerfile):

FROM ubuntu:jammy
RUN apt update
RUN apt install -y python3 python3-setuptools python3-dev python3-pip build-essential pkg-config default-libmysqlclient-dev
RUN pip3 install --upgrade pip
RUN pip3 install mysqlclient=2.2.0

This installs mysqlclient v2.2.0 inside the Ubuntu container.

The problem is that on Ubuntu the caching_sha2_password module does not work. It results in an error:

>>> MySQLdb.connect(host='mysql', port=3306, db='my_database', user='my_user', password='my_password', ssl_mode='DISABLED')
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/venv/lib/python3.10/site-packages/MySQLdb/__init__.py", line 121, in Connect
    return Connection(*args, **kwargs)
  File "/venv/lib/python3.10/site-packages/MySQLdb/connections.py", line 193, in __init__
    super().__init__(*args, **kwargs2)
MySQLdb.OperationalError: (2061, "Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.")

This connection works fine when I install mysqlclient in another Debian base image in exactly the same way (e.g. python:3.10-bullseye):

FROM python:3.10-bullseye
RUN apt update
RUN apt install -y python3 python3-setuptools python3-dev python3-pip build-essential pkg-config default-libmysqlclient-dev
RUN pip3 install --upgrade pip
RUN pip3 install mysqlclient=2.2.0

gives response:

>>> MySQLdb.connect(host='mysql', port=3306, db='my_database', user='my_user', password='my_password', ssl_mode='DISABLED')
<_mysql.connection open to 'mysql' at 0x55dad3427ba0>

Against the same database instance.

So, I conclude that:

1. Building of the Ubuntu image is OK.
2. The Ubuntu image is build against MySQL connector/C library (see below).

Somehow I think that the image does not use the correct MySQL Connector/C library during builds? Could someone give me some pointers.

Output from pkg-config --cflags --libs mysqlclient

-I/usr/include/mysql -lmysqlclient

How did you tried to install mysqlclient?

pip install mysqlclient

Output of building mysqlclient

pip3 install mysqlclient==2.2.0
Collecting mysqlclient==2.2.0
  Downloading mysqlclient-2.2.0.tar.gz (89 kB)
     ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 89.5/89.5 kB 2.6 MB/s eta 0:00:00
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
  Installing backend dependencies ... done
  Preparing metadata (pyproject.toml) ... done
Building wheels for collected packages: mysqlclient
  Building wheel for mysqlclient (pyproject.toml) ... done
  Created wheel for mysqlclient: filename=mysqlclient-2.2.0-cp310-cp310-linux_x86_64.whl size=123670 sha256=2e1be22003ea023c1fd25224c91129e34f3d6d96d6a3ed73a65b2ac1b4bfc80c
  Stored in directory: /root/.cache/pip/wheels/a4/f8/fd/0399687c0abd03c10c975ed56c692fcd3d0fb80440b5a661f1
Successfully built mysqlclient
Installing collected packages: mysqlclient
Successfully installed mysqlclient-2.2.0
WARNING: Running pip as the 'root' user can result in broken permissions and conflicting behaviour with the system package manager. It is recommended to use a virtual environment instead: https://pip.pypa.io/warnings/venv
root@4bab55383899:/#

[pvannierop]

Some followup. It turns out that on the python:3.10-bullseye image, that is able to run with the caching_sha2_password module, the output of the pkg-config -cflags --libs mysqlclient points to the mariadb library (!):

root@37b65fec646c:/# pkg-config --cflags --libs mysqlclient
-I/usr/include/mariadb/ -L/usr/lib/x86_64-linux-gnu/ -lmariadb

This is quite unexpected to me, since all the other posts here state that mariadb connection libs do not support caching_sha2_password, and MySQL connector/C libs do.

Should I install mariadb libraries?

[methane]

Read the error message. The error don't say the library doesn't support caching_sha2_password.

[methane]

Both library does support caching_sha2_password.
But only MySQL Connector disable insecure behavior by default.

If you want to use caching_sha2_password, I recommend to use secure connection (e.g. TLS or Unix socket).
Another way is download caching sha2 password public RSA key from the server in secure way and use it in your my.cnf file:

[client]
server-public-key-path=/path/to/caching_sha2_public_key.pem

There is an insecure option that beaves same to MariaDB connector, but I don't want to write it here because it is insecure option.

See the reference manual for caching sha2 password auth plugin for detail.

[pvannierop]

Both library does support caching_sha2_password.
But only MySQL Connector disable insecure behavior by default.

Ah, that is good to know. Thank you for this.

If you want to use caching_sha2_password, I recommend to use secure connection (e.g. TLS or Unix socket).

In certain cases this is not needed at all. For instance on on K8C clusters it is quite common to run over non-encrypted connection between services. In these cases configuring TLS is overengineering.

There is an insecure option that behaves same to MariaDB connector, but I don't want to write it here because it is insecure option.

Could we perhaps discuss this matter somewhere else? I have been experimenting with some MySQL connector/C options (see here) that I passed with the ssl={...} parameter for your mysqlclient lib like so:

MySQLdb.connect(host='mysql', port=3306, db='my_database', user='my_user', password='my_password', ssl_mode='DISABLED', ssl={'OPT': True})

The option would allow retrieval of the server public key. However, this params did not get this to work. Are the parameters under the ssl parameter passed to MySQL at all when ssl_mode is DISABLED?

Alternatively, could point me to instructions on how to build mysqlclient against mariadb libs?

[methane]

The option would allow retrieval of the server public key.

No. You are wrong at all. Why did you think so? What reference did you read?

Are the parameters under the ssl parameter passed to MySQL at all when ssl_mode is DISABLED?

Its totally unrelated. SSL options are SSL options. Not relating to caching_sha2_password behavior.

I already give you a link to the reference of caching_sha2_password. Read it carefully.

Good luck.

[pvannierop]

@methane From the docs:

Since you appeared to not willing to discuss the precise mechanism of getting caching_sha2_password to work, I did not name the option (hence OPT) and describe the goal of the option (retrieve the server public key).

And to the point of this discussion, these are the relevant docs for caching_sha2_password:

For connections by accounts that authenticate with caching_sha2_password and RSA key pair-based password exchange, the server does not send the RSA public key to clients by default. Clients can use a client-side copy of the required public key, or request the public key from the server.
...
- For programs that use the C API, call [mysql_options()](https://dev.mysql.com/doc/c-api/8.0/en/mysql-options.html) to specify the RSA public key file by passing the MYSQL_SERVER_PUBLIC_KEY option and the name of the file, or request the public key from the server by passing the MYSQL_OPT_GET_SERVER_PUBLIC_KEY option.
...

Its totally unrelated. SSL options are SSL options. Not relating to caching_sha2_password behavior.

Yeah, that is my bad. I think I misread this line from the docs:

[mysql_ssl_set()](https://dev.mysql.com/doc/c-api/5.7/en/mysql-ssl-set.html) is a convenience function that is essentially equivalent to this set of [mysql_options()](https://dev.mysql.com/doc/c-api/5.7/en/mysql-options.html) calls:

I mistook this to mean that I would be able top pass any mysql_option() to the C connector.

I already give you a link to the reference of caching_sha2_password. Read it carefully.

Yeah, I do not think my lack of understanding of the C connector is the problem here. I basically do not understand how to configure the C connector via the Python API.

I have raised a related discussion to this point here.

[pvannierop]

@methane Did you decide to not further provide assistance here?

[methane]

Yes. I am not free tech support nor teacher.
I gave you enough hints and link to the reference.