mysql_query should not masking sensitive data in its query output

[NicolasGoeddel]

SUMMARY

In my Dev environment I've got a database with name icingaweb and a user and a password which is also icingaweb. If I now use mysql_query with this query:

- community.mysql.mysql_query:
    login_db: icingaweb
    login_user: icingaweb
    login_password: icingaweb
    login_host: 127.0.0.1
    loging_port: 3306
    query: "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'icingaweb;"
    named_args:
      db_name: "{{ icingaweb_db_name }}"
  register: icingaweb_db_existing_tables

- debug:
    msg: "{{ icingaweb_db_existing_tables.query_result[0][0]['TABLE_NAME'] }}"

I get something like this as the output:

TASK [r-icingaweb : debug]
ok: [icingaweb] => {}

MSG:

********_group

Usually the table name should be icingaweb_group. But because my password also is icingaweb somehow it gets totally mangled up. The interesting thing: This is the real content of the variable. It really consists of asterisks. I even tried to regex_replace the underlying string icingaweb with something different before debugging it, but it can not be replace. But I can replace the * characters with something else.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_query

ANSIBLE VERSION

ansible 2.9.25
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/home/ngoeddel/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.6/site-packages/ansible
  executable location = /usr/bin/ansible
  python version = 3.6.8 (default, Sep 10 2021, 09:13:53) [GCC 8.5.0 20210514 (Red Hat 8.5.0-3)]

COLLECTION VERSION

version: 2.3.1

CONFIGURATION

COMMAND_WARNINGS(/home/ngoeddel/git/ansible-v2/ansible.cfg) = False
DEFAULT_HOST_LIST(/home/ngoeddel/git/ansible-v2/ansible.cfg) = ['/home/ngoeddel/git/ansible-v2/environment/dev']
DEFAULT_ROLES_PATH(/home/ngoeddel/git/ansible-v2/ansible.cfg) = ['/home/ngoeddel/git/ansible-v2/roles']
DEFAULT_STDOUT_CALLBACK(/home/ngoeddel/git/ansible-v2/ansible.cfg) = debug
DEFAULT_STRATEGY(/home/ngoeddel/git/ansible-v2/ansible.cfg) = linear
DEFAULT_TIMEOUT(/home/ngoeddel/git/ansible-v2/ansible.cfg) = 30
DEFAULT_VAULT_PASSWORD_FILE(/home/ngoeddel/git/ansible-v2/ansible.cfg) = /home/ngoeddel/.my.ansible.cnf
DISPLAY_SKIPPED_HOSTS(/home/ngoeddel/git/ansible-v2/ansible.cfg) = False
INTERPRETER_PYTHON(/home/ngoeddel/git/ansible-v2/ansible.cfg) = /usr/bin/python3
RETRY_FILES_ENABLED(/home/ngoeddel/git/ansible-v2/ansible.cfg) = False

OS / ENVIRONMENT

NAME="CentOS Linux" VERSION="8" ID="centos" ID_LIKE="rhel fedora" VERSION_ID="8" PLATFORM_ID="platform:el8" PRETTY_NAME="CentOS Linux 8" ANSI_COLOR="0;31" CPE_NAME="cpe:/o:centos:centos:8" HOME_URL="https://centos.org/" BUG_REPORT_URL="https://bugs.centos.org/" CENTOS_MANTISBT_PROJECT="CentOS-8" CENTOS_MANTISBT_PROJECT_VERSION="8"

STEPS TO REPRODUCE

You need a database with name icingaweb, a user and a password with the same name and at least one table which has the prefix icingaweb_. Then you can run the playbook mentioned above.

EXPECTED RESULTS

If I do a query nothing should alter the result in any way. If I want to select a column which consists of passwords which are also in my environment I need to be able to use them without being replaced by asterisks.

ACTUAL RESULTS

TASK [r-icingaweb : Query existing tables in the database] ********************************************************************************************************************************************************
task path: /home/ngoeddel/git/ansible-v2/roles/r-icingaweb/tasks/configure_icingaweb.yml:49
ok: [icingaweb] => {
    "changed": false,
    "executed_queries": [
        "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = '********';"
    ],
    "invocation": {
        "module_args": {
            "ca_cert": null,
            "check_hostname": null,
            "client_cert": null,
            "client_key": null,
            "config_file": "/home/imonops/.my.cnf",
            "connect_timeout": 30,
            "login_db": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_host": "127.0.0.1",
            "login_password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "login_port": 3307,
            "login_unix_socket": null,
            "login_user": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "named_args": {
                "db_name": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER"
            },
            "positional_args": null,
            "query": "SELECT TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA = %(db_name)s;",
            "single_transaction": false
        }
    },
    "query_result": [
        [
            {
                "TABLE_NAME": "********_group"
            },
            {
                "TABLE_NAME": "********_group_membership"
            },
            {
                "TABLE_NAME": "********_rememberme"
            },
            {
                "TABLE_NAME": "********_user"
            },
            {
                "TABLE_NAME": "********_user_preference"
            }
        ]
    ],
    "rowcount": [
        5
    ]
}

TASK [r-icingaweb : debug] ****************************************************************************************************************************************************************************************
task path: /home/ngoeddel/git/ansible-v2/roles/r-icingaweb/tasks/configure_icingaweb.yml:64
ok: [icingaweb] => {}

MSG:

********_group

[Andersson007]

@NicolasGoeddel @MartinWeise hello, thank you for reporting the issue! sorry for the delayed response, we have only several maintainers in this collection

I've just taken a look at the code. It uses the no_log=True parameter for the login_password argument. This is the feature of ansible-core API. This module does not do anything with the output which is returned to users via ansible-core. I think ansible-core internally replaces everything in its I/O that looks like the password marked with no_log=True. I think it probably shouldn't.

I see you use pretty old versions of Ansible. Could you please:

• try the latest one, maybe it was fixed
• if it wasn't, could you please open an issue ansible-core and link it with this one?

Looking forward to your feedback, thank you

[NicolasGoeddel]

Unfortunately we have to use that version here because Red Hat seems not to be in a mood to give us any updates here on my customers side. But thanks for pointing out that this is a general issue in the ansible-core. So it may be already fixed indeed later versions.

Because this issue is so old already I need no longer any fixes here because I found a workaround which I don't remember right now. But I am may going to create a new ticket in the ansible-core issue tracker if I can not find a similar issue there.

[FactorT]

@Andersson007 hello! I have the same problem. I am using

ansible      9.1.0
ansible-core 2.16.2

And when I run next tasks

    - name: Get existing users from MySQL
      community.mysql.mysql_query:
        login_password: "admin123"
        login_user: root
        query: "select user, host from mysql.user where user='tech_cloudxadmin123456';"
      register: _ret_users

    - name: debug
      debug:
        msg:
          - "{{ _ret_users }}"

I 've got the result

TASK [Get existing users from MySQL] ********************************************
ok: [10.21.236.37]

TASK [debug] ********************************************************************
ok: [10.21.236.37] => {}

MSG:

[{'changed': False, 'executed_queries': ["select user, host from mysql.user where user='tech_cloudx********456';"], 'query_result': [[{'user': 'tech_cloudx********456', 'host': '%'}]], 'rowcount': [1], 'failed': False}]

As you can see i'm trying to select from the table exactly one user - 'tech_cloudxadmin123456' and the "query_result" section returned that user but this user was masked - tech_cloudx****456 As you can see ansible masked substring = admin123. And admin123 it's the password from parameter "login_password". It's not good. Anasible shouldn't masked query's reponse data.

Source of the problem: MySQL_Query uses AnsibleModule class from ansible.module_utils.basic Let's look into ansible.module_utils.basic There are two interesting functions heuristic_log_sanitize and _clean_args As I understand ansible is looking for arguments with name like password (login_password), remember it's value (admin123) and then parsing all arguments including query_result's argument from **kw and masking all substring.

How to prevent ansible from parsing and masking query_result argument?

[laurent-indermuehle]

@FactorT thanks for your investigation. Interesting to see how Ansible masks the returned values automatically. I've no idea how to fix that yet.

For your specific use case, you can have a look at the mysql_info module with the users_infofilter : https://docs.ansible.com/ansible/latest/collections/community/mysql/mysql_info_module.html#ansible-collections-community-mysql-mysql-info-module

[NicolasGoeddel]

It already is a really old bug. It would be really nice to have that fixed somehow. I understand that the probability is really low with a high entropy password to match an output of a query. Therefore maybe a simple switch to disable sanitizing would be helpful.

[Andersson007]

The source of the issue is ansible-core, that's why I redirected people to open an issue there. We use its API but it's a separate project we are not responsible for (maintained by core team). I don't see a way to configure ansible-core anyhow in the module, if anybody knows how, please open a PR

[FactorT]

Thanks everyone for participating! I opened issue to ansible-core team. I think we can close this issue.

[Andersson007]

@FactorT cool, thanks! Let's keep it open for a while, maybe there's a magic toggle or something that we can use on our side. If no response/solution, we'll close it in the next issue review iteration.

[Andersson007]

btw, folks, welcome to join our matrix channel and forum group, all the info in https://docs.ansible.com/ansible/devel/community/communication.html