cannot set user password with --strict-password-validation enabled since v2.9.x

[luto]

SUMMARY

Somewhere between ansible 2.8 and 2.9 the way mysql_user sets the mysql user password changed from

cursor.execute("CREATE USER %s@%s IDENTIFIED BY %s", (user, host, password))

to

cursor.execute("SELECT CONCAT('*', UCASE(SHA1(UNHEX(SHA1(%s)))))", (password,))
encrypted_password = cursor.fetchone()[0]
cursor.execute(
  *mogrify(
    "CREATE USER %s@%s IDENTIFIED WITH mysql_native_password AS %s",
      (user, host, encrypted_password),
      tls_requires,
    )
)

(see mysql_user.py#L466-L477)

Setups using strict_password_validation = on to enforce certain password requirements (e.g. minimum length) break after this change. To check the password, the mysql server needs to see it in clear text before hashing it. If ansible hashes the password on behalf of the server, the server bails out with "The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement" as can be seen below.

Since the password is sent to the server in clear text as part of the first statement anyway, I guess there is no added security by encrypting it beforehand.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.9.13
  config file = /etc/ansible/ansible.cfg
  configured module search path = [u'/opt/uberspace/ansible-public/library']
  ansible python module location = /opt/uberspace/python-venv-2.7/lib/python2.7/site-packages/ansible
  executable location = /opt/uberspace/python-venv/bin/ansible
  python version = 2.7.5 (default, Apr  2 2020, 13:16:51) [GCC 4.8.5 20150623 (Red Hat 4.8.5-39)]

CONFIGURATION

CONDITIONAL_BARE_VARS(/etc/ansible/ansible.cfg) = False
DEFAULT_FILTER_PLUGIN_PATH(/etc/ansible/ansible.cfg) = [u'/opt/uberspace/ansible-public/filters']
DEFAULT_GATHER_SUBSET(/etc/ansible/ansible.cfg) = [u'min']
DEFAULT_MODULE_PATH(/etc/ansible/ansible.cfg) = [u'/opt/uberspace/ansible-public/library']
DEFAULT_ROLES_PATH(/etc/ansible/ansible.cfg) = [u'/opt/uberspace/ansible/roles']
DEFAULT_STRATEGY(/etc/ansible/ansible.cfg) = mitogen_linear
DEFAULT_STRATEGY_PLUGIN_PATH(/etc/ansible/ansible.cfg) = [u'/opt/uberspace/ansible-public/mitogen/ansible_mitogen/plugins/strategy']
DEPRECATION_WARNINGS(/etc/ansible/ansible.cfg) = False

OS / ENVIRONMENT

CentOS 7, MariaDB 10.3.24 from MariaDB repos

STEPS TO REPRODUCE

1. install MaraiaDB
2. enable password check, e.g. /etc/my.cnf.d/passwords.cnf:

[mariadb]
plugin_load_add = simple_password_check
strict_password_validation = on
simple_password_check_minimal_length = 16

3. try to set a password using mysql_user

- hosts: localhost
  tasks:
    - name: grant MySQL permissions
      mysql_user:
        name: "dbcheck"
        host: "%"
        password: "Mool1cheedohch7ohRoonechoosh0i"
        state: present

EXPECTED RESULTS

task succeeds, password is set. Works in ansible <2.9.

ACTUAL RESULTS

As of ansible >=2.9:

TASK [grant MySQL permissions] ********************************************************************************************************************************************************************
[WARNING]: Module did not set no_log for update_password
fatal: [localhost]: FAILED! => {"changed": false, "msg": "(1290, u'The MariaDB server is running with the --strict-password-validation option so it cannot execute this statement')"}

[Jorge-Rodriguez]

Ping @bmildren The change to pass already hashed passwords seems to apply to "newer' servers. What was the rationale behind it?

[Andersson007]

@bmalynovytch , seems that it can be related to https://github.com/ansible/ansible/pull/45355 , could you please take a look

[bmalynovytch]

https://github.com/ansible/ansible/pull/45355 is way older than 2.9. I checked git blame and it seems to be related to https://github.com/ansible-collections/community.mysql/pull/9.

[Jorge-Rodriguez]

@bmalynovytch #9 is my change. Can you elaborate on how it's related? Mind that while blame shows changes on that line in #9, the actual hashing code is older than that

[bmalynovytch]

Ok, fair, I didn't dig deep enough. The issue is many years old (seem to be present since many years) and seem to concern updates which all provide hashed passwords to MySQL/MariaDB. https://github.com/ansible/ansible/pull/45355 reproduces the same behavior as before but handles all existing variants of queries depending on versions of MySQL/MariaDB at that time.

To sum up, I'd say : --strict-password-validation would only work with users created once with the cleartext password and won't work anymore if you update the password (using Ansible's module) because of the way updates are handled (providing hashed password).