Unable to set plugin_auth_string to an empty unexpired password

[TimWolla]

SUMMARY

I'm unable to configure caching_sha2_password together with an empty password to leverage TLS client certificate authentication.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.12.10]
  config file = /*snip*/ansible/ansible.cfg
  configured module search path = ['/home/timwolla/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3/dist-packages/ansible
  ansible collection location = /home/timwolla/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.8.10 (default, May 26 2023, 14:05:08) [GCC 9.4.0]
  jinja version = 2.10.1
  libyaml = True

COLLECTION VERSION

# /usr/lib/python3/dist-packages/ansible_collections
Collection      Version
--------------- -------
community.mysql 2.3.8  

CONFIGURATION

(empty)

OS / ENVIRONMENT

Ansible running on Ubuntu 20.04 with a Debian Bullseye (11) target running MySQL 8.

STEPS TO REPRODUCE

- name: Create MySQL user.
  mysql_user:
    name: *snip*
    host: '%'
    update_password: always
    priv: '*.*:ALL,GRANT'
    plugin: 'caching_sha2_password'
    plugin_auth_string: ''
    tls_requires:
      subject: '*snip*'
      issuer: '*snip*'
      cipher: 'ECDHE-RSA-AES128-GCM-SHA256'
    login_user: root
    login_unix_socket: /run/mysqld/mysqld.sock

EXPECTED RESULTS

I expected the MySQL user to be created with an empty and unexpired password and TLS requirements.

ACTUAL RESULTS

The user was created with an expired password:

                  plugin: caching_sha2_password
   authentication_string: 
        password_expired: Y

This is due to a semantic difference between:

ALTER USER 'manager'@'%' IDENTIFIED WITH caching_sha2_password;

and

ALTER USER 'manager'@'%' IDENTIFIED WITH caching_sha2_password BY '';

The former will configure the password to be expired, whereas the latter will not. Both will clear the authentication_string.

Example:

[(none)]> SELECT plugin, authentication_string, password_expired FROM mysql.user WHERE user = 'manager';
+-----------------------+-----------------------+------------------+
| plugin                | authentication_string | password_expired |
+-----------------------+-----------------------+------------------+
| caching_sha2_password |                       | N                |
+-----------------------+-----------------------+------------------+
1 row in set (0.00 sec)

[(none)]> ALTER USER 'manager'@'%' IDENTIFIED WITH caching_sha2_password;
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT plugin, authentication_string, password_expired FROM mysql.user WHERE user = 'manager';
+-----------------------+-----------------------+------------------+
| plugin                | authentication_string | password_expired |
+-----------------------+-----------------------+------------------+
| caching_sha2_password |                       | Y                |
+-----------------------+-----------------------+------------------+
1 row in set (0.00 sec)

[(none)]> ALTER USER 'manager'@'%' IDENTIFIED WITH caching_sha2_password BY '';
Query OK, 0 rows affected (0.01 sec)

[(none)]> SELECT plugin, authentication_string, password_expired FROM mysql.user WHERE user = 'manager';
+-----------------------+-----------------------+------------------+
| plugin                | authentication_string | password_expired |
+-----------------------+-----------------------+------------------+
| caching_sha2_password |                       | N                |
+-----------------------+-----------------------+------------------+
1 row in set (0.00 sec)

I suppose the following line might need to check for is not None or something like that:

community.mysql/plugins/module_utils/user.py

Line 311 in 9439282

elif plugin_auth_string:

[laurent-indermuehle]

Hi @TimWolla,

Thank your for opening the issue.

I want to understand your use case. Why creating an empty password? Is it really necessary?
So anyone with your certificate can login without any authentication required?

[TimWolla]

@laurent-indermuehle

So anyone with your certificate can login without any authentication required?

Your wording is a little misleading, but yes: We're handing out short-lived X.509 certificates to our application and the application uses this certificate to authenticate to various services, including MySQL.

Thus the connection to MySQL is not being performed without any authentication, having the certificate (or rather: access to the certificate's private key) is the authentication. In fact I'd argue this kind of authentication is much stronger than using passwords.

[laurent-indermuehle]

@TimWolla thanks you for the clarification. Indeed what you're doing to authenticate your clients to the database seems secure and cool. Are you using a product to handle the certificate rotation or it's all in house?

I know we can improve the handling of plugin_auth_string by a lot. But as a work around, will it works if you use the mysql_native_password plugin instead?

[TimWolla]

Are you using a product to handle the certificate rotation or it's all in house?

We're using an in-house solution here.

will it works if you use the mysql_native_password plugin instead?

I'm afraid I'm unable to easily test this and completely unable to run this in production. We're running MySQL 8 as the database and it deprecated mysql_native_password in a way that logs a deprecation notice for every single connection, instead of just once per user or something similar, leading to our logs being completely swamped by that deprecation 😩

[laurent-indermuehle]

@TimWolla I see. I read that mysql_native_password is not installed at all since MySQL 8.1.

So a PR is definitely worse it. Do you have time to contribute? We can help you getting started if you wish to contribute to the collection.