Unable to set `plugin_auth_string` to an empty unexpired password
Opened this issue · 5 comments
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
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?
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.
@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?
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 😩
@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.