mysql_user overwrite the user password to the same thing #543

camaer · 2020-06-19T16:33:18Z

SUMMARY

When using the module mysql_user to create a user the first run results in the user being created. The username and credential are working correctly. The issue is if we run the same step another time, the password seems to be updated but to the same value which return a 'Changed' in the playbook

ISSUE TYPE

Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.9.10
  config file = /etc/ansible/ansible.cfg
  configured module search path = [u'/var/lib/rundeck/.ansible/plugins/modules', u'/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python2.7/dist-packages/ansible
  executable location = /usr/bin/ansible
  python version = 2.7.12 (default, Nov 12 2018, 14:36:49) [GCC 5.4.0 20160609]

CONFIGURATION

DEFAULT_FORKS(/etc/ansible/ansible.cfg) = 25
DEFAULT_HOST_LIST(/etc/ansible/ansible.cfg) = [u'/etc/ansible/inventories']
DEFAULT_REMOTE_USER(/etc/ansible/ansible.cfg) = rundeck
DEFAULT_VAULT_PASSWORD_FILE(/etc/ansible/ansible.cfg) = /var/lib/rundeck/vault_pass.txt
INTERPRETER_PYTHON(/etc/ansible/ansible.cfg) = /usr/bin/python3

OS / ENVIRONMENT

Ubuntu 20.04

STEPS TO REPRODUCE

Use mysql_user to create a new user. Run the step 1 time to create said user. Run it a second time. It will result in a changed even if nothing really changed

- name: "Create john mysql user"
  mysql_user:
    config_file: /etc/mysql/debian.cnf
    name: "john"
    password: "changeme"
    host: "{{ item }}"
    state: present
  with_items:
    - "%"
    - "localhost"

EXPECTED RESULTS

The second run should return 'OK'. This is confusing because we might think the password has been changed to something else if we don't have verbose on.

ACTUAL RESULTS

First run:

changed: [192.168.1.181] => (item=%) => {"ansible_loop_var": "item", "changed": true, "item": "%", "msg": "User added", "user": "john", "warnings": ["Module did not set no_log for update_password"]}
changed: [192.168.1.181] => (item=localhost) => {"ansible_loop_var": "item", "changed": true, "item": "localhost", "msg": "User added", "user": "john", "warnings": ["Module did not set no_log for update_password"]}

Credentials are working correctly.

Second run:

changed: [192.168.1.181] => (item=%) => {"ansible_loop_var": "item", "changed": true, "item": "%", "msg": "Password updated (new style)", "user": "john", "warnings": ["Module did not set no_log for update_password"]}
changed: [192.168.1.181] => (item=localhost) => {"ansible_loop_var": "item", "changed": true, "item": "localhost", "msg": "Password updated (new style)", "user": "john", "warnings": ["Module did not set no_log for update_password"]}

The second run seems to be updating the password but to the same value as in the first run. You can confirm this since the previous credentials should still work. Shouldn't it return OK instead ?

Subsequent runs are fine and return OK

The text was updated successfully, but these errors were encountered:

ansibullbot · 2020-06-19T16:37:50Z

Files identified in the description:

plugins/modules/database/mysql/mysql_user.py

If these files are inaccurate, please update the component name section of the description or use the !component bot command.

click here for bot help

ansibullbot · 2020-06-19T16:37:50Z

cc @Alexander198961 @Andersson007 @Jmainguy @Xyon @bmalynovytch @bmildren @kurtdavis @michaelcoburn @Oneiroi @tolland
click here for bot help

Andersson007 · 2020-06-22T08:08:10Z

@camaer thanks for reporting this.
I can't reproduce this behavior. I took a look at the related part of the code - looks like it should work correctly.

Three questions:

Which MySQL version do you use there?
Have you tried to run that twice without the loop? e.g., just host: localhost
Have you tried to run that w/o config_file? If not, would be cool if you try.
If it's impossible in your environment, what do you have in /etc/mysql/debian.cnf?

I'm trying to figure out maybe it's not because of passing passwords

camaer · 2020-06-22T14:18:40Z

Hey @Andersson007,

8.0.20-0ubuntu0.20.04.1
The problem still persist even without the loop
I've tried using login_unix_socket instead but got the same result. Here's what I have in /etc/mysql/debian.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = hVIUideXgzkAGtyp
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint
password = hVIUideXgzkAGtyp
socket   = /var/run/mysqld/mysqld.sock

Let me know if there is any other test I can run.

Andersson007 · 2020-06-22T14:28:34Z

@camaer thanks for the info!

Andersson007 · 2020-07-03T07:59:26Z

1. First run:

[WARNING]: Module did not set no_log for update_password
changed: [spblnx125] => {
    "changed": true,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "ca_cert": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encrypted": false,
            "host": "localhost",
            "host_all": false,
            "login_host": "localhost",
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": "root",
            "name": "test3",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "priv": null,
            "sql_log_bin": true,
            "state": "present",
            "update_password": "always",
            "user": "test3"
        }
    },
    "msg": "User added",
    "user": "test3"
}

mysql> SELECT COALESCE(CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END, CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END) FROM mysql.user WHERE user = 'test3' AND host = 'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| COALESCE(CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END, CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| $A$005$f+}1K^9NK3Gx%Gbk.wg.9ipv47dt5z1dpWiPQzj8D9/4cg9uOXcGXazjX3F3                                                                                                 |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

2. Second run:

changed: [spblnx125] => {
    "changed": true,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "ca_cert": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encrypted": false,
            "host": "localhost",
            "host_all": false,
            "login_host": "localhost",
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": "root",
            "name": "test3",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "priv": null,
            "sql_log_bin": true,
            "state": "present",
            "update_password": "always",
            "user": "test3"
        }
    },
    "msg": "Password updated (new style)",
    "user": "test3"
}

mysql> SELECT COALESCE(CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END, CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END) FROM mysql.user WHERE user = 'test3' AND host = 'localhost';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| COALESCE(CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END, CASE WHEN authentication_string = '' THEN NULL ELSE authentication_string END) |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| *A957B65E303A5846CC00FF4416E1FDE0D9BAB76D                                                                                                                              |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

After subsequent runs changed False as expected.
So the password representation after the second run is different from the first one.

Needs to be fixed

Andersson007 · 2020-07-03T08:40:05Z

@camaer thanks for reporting this!
resolved_by #609

camaer · 2020-07-06T12:13:52Z

Glad you were able to reproduce it ! Thanks for your work ! Closing the issue.

Andersson007 · 2020-07-06T12:17:25Z

@camaer , merged thanks!

geerlingguy · 2020-07-22T15:30:48Z

Just wanted to note that I've found the same issue on both Ubuntu 20.04 and Debian 10's built-in versions of MySQL with Python 3.x: geerlingguy/ansible-role-drupal#77

I'm assuming this updated version of the mysql_user module might make it into Ansible 2.10?

geerlingguy · 2020-07-22T15:36:06Z

Alternatively, could the PR that fixes this issue be backported into Ansible 2.9.x? It's an LTS release and this bug will start hitting more and more people as they upgrade servers to Debian 10/Ubuntu 20.04.

Andersson007 · 2020-07-23T06:53:36Z

@geerlingguy ansible/ansible#70833 done, thanks for the suggestion!

v2.9.12 ======= Minor Changes ------------- - ansible-test - the ACME test container was updated, it now supports external account creation and has a basic OCSP responder (ansible/ansible#71097, https://github.com/ansible/acme-test-container/releases/tag/2.0.0). - debconf - add a note about no_log=True since module might expose sensitive information to logs (ansible/ansible#32386). Security Fixes -------------- - **security issue** - copy - Redact the value of the no_log 'content' parameter in the result's invocation.module_args in check mode. Previously when used with check mode and with '-vvv', the module would not censor the content if a change would be made to the destination path. (CVE-2020-14332) - **security issue** atomic_move - change default permissions when creating temporary files so they are not world readable (ansible/ansible#67794) (CVE-2020-1736) - Fix warning for default permission change when no mode is specified. Follow up to ansible/ansible#67794. (CVE-2020-1736) - Sanitize no_log values from any response keys that might be returned from the uri module (CVE-2020-14330). - reset logging level to INFO due to CVE-2019-14846. Bugfixes -------- - Address compat with rpmfluff-0.6 for integration tests - Ensure password passed in by -k is used on delegated hosts that do not have ansible_password set - Template connection variables before using them (ansible/ansible#70598). - Terminal plugins - add "\e[m" to the list of ANSI sequences stripped from device output - add magic/connection vars updates from delegated host info. - ansible-galaxy collection install - fix fallback mechanism if the AH server did not have the collection requested - ansible/ansible#70940 - ansible-test - Add ``pytest < 6.0.0`` constraint for managed installations on Python 3.x to avoid issues with relative imports. - ansible-test - Change detection now properly resolves relative imports instead of treating them as absolute imports. - api - time.clock is removed in Python 3.8, add backward compatible code (ansible/ansible#70649). - avoid clobbering existing facts inside loop when task also returns ansible_facts. - basic - use PollSelector implementation when DefaultSelector fails (ansible/ansible#70238). - cron - encode and decode crontab files in UTF-8 explicitly to allow non-ascii chars in cron filepath and job (ansible/ansible#69492) - ensure delegated vars can resolve hostvars object and access vars from hostvars[inventory_hostname]. - facts - account for Slackware OS with ``+`` in the name (ansible/ansible#38760) - facts - fix incorrect UTC timestamp in ``iso8601_micro`` and ``iso8601`` - fix issue with inventory_hostname and delegated host vars mixing on connection settings. - hashi_vault - Handle equal sign in key=value (ansible/ansible#55658). - ipa_hostgroup - fix an issue with load-balanced ipa and cookie handling with Python 3 - (ansible/ansible#71110). - lineinfile - fix not subscriptable error in exception handling around file creation - linux network facts - get the correct value for broadcast address (ansible/ansible#64384) - mysql_user - fix overriding password to the same (ansible-collections/community.general#543). - net_put - Fixed UnboundLocalError when there is no change This is a backport from U(ansible-collections/ansible.netcommon#6) - nxos_user - do not fail when a custom role is used (ansible-collections/cisco.nxos#130) - ovirt_vm - fix cd_iso search - playbooks - detect and propagate failures in ``always`` blocks after ``rescue`` (ansible/ansible#70000) - profile_tasks - typecast result before slicing it (ansible/ansible#59059). - reboot - Add support for the runit init system, used on Void Linux, that does not support the normal Linux syntax. - redfish_info, redfish_config, redfish_command - Fix Redfish response payload decode on Python 3.5 (ansible/ansible#65889) - shell - fix quoting of mkdir command in creation of remote_tmp in order to allow spaces and other special characters (ansible/ansible#69577). - templating - fix error message for ``x in y`` when y is undefined (ansible/ansible#70984) - unarchive - check ``fut_gid`` against ``run_gid`` in addition to supplemental groups (ansible/ansible#49284) - user - don't create home directory and missing parents when create_home == false (ansible/ansible#70600). - yum - fix yum list crashing if repoquery (used internally) prints errors in stdout (ansible/ansible#56800)

aceqbaceq · 2021-02-09T20:02:57Z

i have the same issue with
ubuntu 16
ansible 2.9.17

Andersson007 · 2021-02-10T07:15:55Z

@aceqbaceq hi, thanks for the feedback!
Which version of MySQL do you use?

pospelov-v · 2021-12-02T10:44:29Z

i have the same issue with:

mysql Ver 8.0.26-16 for Linux on x86_64 (Percona Server (GPL), Release '16', Revision '3d64165')
Debian GNU/Linux 10 (buster)
ansible 2.9.16

Andersson007 · 2021-12-02T11:23:19Z

@pospelov-v thanks for the feedback!
Do you have a chance to try your playbook with Ansible 4 or 5?
If the problem is present with it too, could you please create a new issue in https://github.com/ansible-collections/community.mysql ?

ansibullbot added affects_2.10 bug This issue/PR relates to a bug database module module mysql needs_triage plugins plugin (any type) python3 labels Jun 19, 2020

ansibullbot removed the needs_triage label Jun 22, 2020

Andersson007 added the needs_info This issue requires further information. Please answer any outstanding questions label Jun 22, 2020

ansibullbot removed the needs_info This issue requires further information. Please answer any outstanding questions label Jun 22, 2020

Andersson007 mentioned this issue Jul 3, 2020

mysql_user: fix overriding user passowrd to the same #609

Merged

ansibullbot added the has_pr label Jul 3, 2020

camaer closed this as completed Jul 6, 2020

geerlingguy mentioned this issue Jul 22, 2020

Debian 10 CI tests failing due to mysql_user module bug geerlingguy/ansible-role-drupal#77

Closed

geerlingguy mentioned this issue Jul 22, 2020

Remove duplicate mysql_user tasks once Ansible 2.10 is stable geerlingguy/ansible-for-devops#297

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

mysql_user overwrite the user password to the same thing #543

mysql_user overwrite the user password to the same thing #543

camaer commented Jun 19, 2020 •

edited

Loading

ansibullbot commented Jun 19, 2020

ansibullbot commented Jun 19, 2020

Andersson007 commented Jun 22, 2020

camaer commented Jun 22, 2020

Andersson007 commented Jun 22, 2020

Andersson007 commented Jul 3, 2020

Andersson007 commented Jul 3, 2020

camaer commented Jul 6, 2020

Andersson007 commented Jul 6, 2020

geerlingguy commented Jul 22, 2020

geerlingguy commented Jul 22, 2020 •

edited

Loading

Andersson007 commented Jul 23, 2020

aceqbaceq commented Feb 9, 2021

Andersson007 commented Feb 10, 2021

pospelov-v commented Dec 2, 2021

Andersson007 commented Dec 2, 2021

mysql_user overwrite the user password to the same thing #543

mysql_user overwrite the user password to the same thing #543

Comments

camaer commented Jun 19, 2020 • edited Loading

SUMMARY

ISSUE TYPE

COMPONENT NAME

ANSIBLE VERSION

CONFIGURATION

OS / ENVIRONMENT

STEPS TO REPRODUCE

EXPECTED RESULTS

ACTUAL RESULTS

ansibullbot commented Jun 19, 2020

ansibullbot commented Jun 19, 2020

Andersson007 commented Jun 22, 2020

camaer commented Jun 22, 2020

Andersson007 commented Jun 22, 2020

Andersson007 commented Jul 3, 2020

Andersson007 commented Jul 3, 2020

camaer commented Jul 6, 2020

Andersson007 commented Jul 6, 2020

geerlingguy commented Jul 22, 2020

geerlingguy commented Jul 22, 2020 • edited Loading

Andersson007 commented Jul 23, 2020

aceqbaceq commented Feb 9, 2021

Andersson007 commented Feb 10, 2021

pospelov-v commented Dec 2, 2021

Andersson007 commented Dec 2, 2021

camaer commented Jun 19, 2020 •

edited

Loading

geerlingguy commented Jul 22, 2020 •

edited

Loading