mysql.user tries to set password on second run with update_password = "on_create"

[hubiongithub]

Hello

I did not find an issue describing my problem, tested witch community_mysql 3.1.2

SUMMARY

create user in mysql (8) with password validation configured:
password_history = 5
second run tries to update password (with the same value) which results in
"msg": "(3638, "Cannot use these credentials for 'bobby@localhost' because they contradict the password history policy")"}
see below for ansible code used

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql.user

ANSIBLE VERSION

2.9.27

COLLECTION VERSION

3.1.2

CONFIGURATION

OS / ENVIRONMENT

Ubuntu 20.04 LTS on both sides

STEPS TO REPRODUCE

---
- hosts: all
  tasks:
    - name: Create MySQL users
      become: yes
      community.mysql.mysql_user:
        plugin: caching_sha2_password
        name: "{{ item.name }}"
        plugin_auth_string: "{{ item.password }}"
        priv: "{{ item.priv }}"
        host: "localhost"
        update_password: "on_create"
        state: present
      with_items:
        - name: bobby
          password: ...
          priv: "sakila.*:SELECT"

and a smal .ini file describing which hosts to use

EXPECTED RESULTS

If update_password is set to "on_create" I would assume no alter user ... is issued at all, as long the user exists.

I'm not sure if it is possible to analyse authentication_string from mysql.user with the current password in the yaml
code in plugins/module_utils/user.py says (line 267)
if plugin_auth_string and current_plugin[1] != plugin_auth_string:
# this case can cause more updates than expected,
# as plugin can hash auth_string in any way it wants
# and there's no way to figure it out for
# a check, so I prefer to update more often than never
update = True
that it isn't, which will cause the same problem if update_password is set to always.
But at least if update_password is set to on_create this code should not run at all.

ACTUAL RESULTS

msg": "(3638, \"Cannot use these credentials for 'bobby@localhost' because they contradict the password history policy\")"}

Probably this also will fail on every other plugin also, but I only tested caching_sha2_password.

Beside the actual error if I don't use plugin_auth_string but password, I get the user created but with an empty authentication_string,
the documentation could be a bit more clear when to use which variable. (Or the code could could handle "password" differently depending on the plugin given, so one who makes the plugin variable don't need to code around this depending on which plugin is used)

Regards

[hubiongithub]

Hello

I looked a bit deeper into the code,

plugins/modules/mysql_user.py

if update_password == "always":
                    changed, msg = user_mod(cursor, user, host, host_all, password, encrypted,
                                            plugin, plugin_hash_string, plugin_auth_string,
                                            priv, append_privs, tls_requires, module)
                else:
                    changed, msg = user_mod(cursor, user, host, host_all, None, encrypted,
                                            plugin, plugin_hash_string, plugin_auth_string,
                                            priv, append_privs, tls_requires, module)

in if != always case: password is replaced by "None" in the user_mod() call but "plugin, plugin_hash_string, plugin_auth_string"
is not replaced. I tried this (not sure if this breaks other stuff, is changing the plugin while using "on_create" a possibility?):

diff mysql_user.py mysql_user.py.3.1.2
436c436
<                                             None, None, None,
---
>                                             plugin, plugin_hash_string, plugin_auth_string,

I don't get errors anymore.

[Andersson007]

@hubiongithub hello and welcome to the project!
Thanks for the issue and investigation!

not sure if this breaks other stuff, is changing the plugin while using "on_create" a possibility?

Good question, i don't know:)

@hubiongithub anyway you could continue the investigation.
If you figure out that changing the plugin while using "on_create" does not make sense,
you could try to submit a pull request which will trigger CI tests and we can see if anything fails.
Would you like to do it? We have a Quick-start guide. There's also a way to run the tests locally described in the guide, which makes sense to do before pushing

[Andersson007]

@rsicart @betanummeric @Jorge-Rodriguez @bmalynovytch do you have any ideas on #334 (comment) ?

[hubiongithub]

@hubiongithub anyway you could continue the investigation. If you figure out that changing the plugin while using "on_create" does not make sense, you could try to submit a pull request which will trigger CI tests and we can see if anything fails. Would you like to do it? We have a Quick-start guide. There's also a way to run the tests locally described in the guide, which makes sense to do before pushing

I've done the git clone and create a branch so far, I probably won't be able to install all the testing stuff on my work pc here,
but if I read your statement correct I done need to because the pull request will trigger tests anyways?

From my point of view "on_create" should never try to set a password again for an existing user.
Should we open a discussion on this topic or is it ok to discuss this here in the issue?

I will try to investigate if changing the plugin in ansible results in passwords rewrites but as the plugin determines how a password is stored (or otherwise checked e.g. PAM) changing the plugin alone results in an empty authentication_string in mysql.user:

coming from plugin mysql_native_password
alter user 'user'@'host' identified with caching_sha2_password = '...'; -> new values in authentication_string
alter user 'user'@'host' identified with caching_sha2_password; -> empty value in authentication_string (not OK from my point of view)
alter user 'user'@'host' identified with auth_pam; -> empty value in authentication_string (OK as this plugin asks pamd for authentication

So I would say "on_create" is a one_way, if you need to change the plugin you either drop/create the user or use always,
trying to know how all possible password plugins behave and to react differently on "on_create" seems odd.