mysql_user expires password on second run

[dotneutron]

SUMMARY

I want to create non-root users with mysql_user. Everything goes fine when I run the playbook once. The second time I run it, the task is again marked as changed despite having msg: User unchanged in the result. The issue is that password_expired is changed to true inside the mysql.user table after the second run, which makes it impossible for users to log in without a workaround. All subsequent runs mark the task as ok.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.12.2]
  config file = /path/to/project/ansible.cfg
  configured module search path = ['/Users/neutron/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /opt/homebrew/Cellar/ansible/5.3.0/libexec/lib/python3.10/site-packages/ansible
  ansible collection location = /Users/neutron/.ansible/collections:/usr/share/ansible/collections
  executable location = /opt/homebrew/bin/ansible
  python version = 3.10.2 (main, Feb  2 2022, 05:51:25) [Clang 13.0.0 (clang-1300.0.29.3)]
  jinja version = 3.0.3
  libyaml = True

COLLECTION VERSION

# /Users/neutron/.ansible/collections/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.1.1

# /opt/homebrew/Cellar/ansible/5.3.0/libexec/lib/python3.10/site-packages/ansible_collections
Collection      Version
--------------- -------
community.mysql 2.3.3

CONFIGURATION

ANSIBLE_PIPELINING(/path/to/project/ansible.cfg) = True
DEFAULT_HOST_LIST(/path/to/project/ansible.cfg) = ['/path/to/project/inventory']
DEFAULT_STDOUT_CALLBACK(/path/to/project/ansible.cfg) = yaml
RETRY_FILES_ENABLED(/path/to/project/ansible.cfg) = False

OS / ENVIRONMENT

Host OS: Ubuntu 20.04 LTS
Docker MySQL OS: See Dockerfile for mysql:8
MySQL client: PyMySQL
MySQL settings:

• default_password_lifetime: 0

STEPS TO REPRODUCE

- name: Create MySQL users
      community.mysql.mysql_user:
        login_user: root
        login_password: "{{ mysql_root_password }}"
        login_port: "{{ mysql_host_port }}"
        plugin: caching_sha2_password
        name: "{{ item.name }}"
        password: "{{ item.password }}"
        priv: "{{ item.priv }}"
        host: "%"
        update_password: on_create
        state: present
      with_items:
        - name: bobby
          password: blablabla
          priv: "{{ mysql_database }}.*:SELECT,INSERT,UPDATE,DELETE"
      tags: mysql-users
      register: mysql_user_create

EXPECTED RESULTS

The task should not expire the users' passwords on the second run or cause any other changes.

ACTUAL RESULTS

Despite the modification(s) caused by the second run, the task result has msg: User unchanged.

[dotneutron]

I just managed to get it to stop expiring passwords. I substituted password: "{{ item.password }}" with plugin_auth_string: "{{ item.password }}". It makes sense since I specified the plugin variable, but perhaps this could be mentioned somewhere in the docs so that people know these options are kinda mutually exclusive, or some similar wording.

The task is now marked as changed no matter how many times I run the playbook, but that's not such a big deal, I guess. 👍

EDIT: I just noticed the first version ignores plugin and creates the user with mysql_native_password.

[Andersson007]

@dotneutron hello, welcome to the project and thanks for reporting the issue!
I'm not a MySQL specialist and not a user, so user's judgment is much appreciated here. We need more contributors from users side.

1. In your opinion, the module works logically now or we could make it better?
2. Would the doc improvements be enough? If yes, would you like to submit a PR? (the easiest way is to use "edit" button against https://github.com/ansible-collections/community.mysql/blob/main/plugins/modules/mysql_user.py or a classic approach described in this guide).

Thanks!

[hubiongithub]

Hello

I just managed to get it to stop expiring passwords. I substituted password: "{{ item.password }}" with plugin_auth_string: "{{ item.password }}". It makes sense since I specified the plugin variable, but perhaps this could be mentioned somewhere in the docs so that people know these options are kinda mutually exclusive, or some similar wording.

A little more description about "password / encrypted" is only for "mysql_native_password" and otherwise use
plugin + plugin_auth_string/plugin_hash_string in the documentation would be nice.
Or an error message if plugin is used and password is set.

The task is now marked as changed no matter how many times I run the playbook, but that's not such a big deal, I guess. 👍

This relates to issue 334 as the logic on on_create only prevents updates on "password" but not on plugin,plugin_auth_string,...

[Andersson007]

#334 (comment)

A little more description about "password / encrypted" is only for "mysql_native_password" and otherwise use
plugin + plugin_auth_string/plugin_hash_string in the documentation would be nice.
Or an error message if plugin is used and password is set.

Does anyone wants to raise a PR? We need:

• Update the doc, just add "- Mutually exclusive with .." to the options (the doc is stored in module's file)
• Add mutually_exclusive=(..., ...) key to the module = AnsibleModule( dict in the main function UPDATE: it's a breaking change, so we should postpone it to the next major release. Just a warning would be enough for now (use module.warn("..."))

Quick start dev guide

[hubiongithub]

Is the information we added in PR342 for issue 334 enough to clarify?

[Andersson007]

@dotneutron could you please take a look at #342. If it clarifies things, could you please close the issue?

[Andersson007]

I'll close this for now. If any questions, we can re-open it at any time later