devsec.hardening.mysql_hardening - Get all users that have no authentication_string - Hello world

[sbourdette]

Description

Playbook fail when no users without password or authentification_string:

TASK [devsec.hardening.mysql_hardening : Create a fact for users without password or authentication_string users_wo_auth={{ mysql_users_wo_passwords_or_auth_string.query_result.0.0 | community.general.json_query('users') }}] *********************
Thursday 23 February 2023  12:42:57 +0100 (0:00:00.103)       0:00:22.887 ***** 
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: AttributeError: module 'jmespath' has no attribute 'functions'
fatal: [centreonnew.xxx.int]: FAILED! => changed=false

Reproduction steps

When debugging, the query for retrieving "users without password or authentication_string users_wo_auth" return NULL and it seems that Python or ansible interpret it as "Hello world !"

Debugging playbook :

    - name: Get all users that have no authentication_string on MySQL version >= 5.7.6 or Mariadb version >= 10.4.0
      community.mysql.mysql_query:
        query: >
          SELECT GROUP_CONCAT(QUOTE(USER), '@', QUOTE(HOST) SEPARATOR ', ') AS users
          FROM mysql.user
          WHERE (length(authentication_string)=0
             OR authentication_string="")
            AND USER NOT IN ('mysql.sys',
                         'mysqlxsys',
                         'mariadb.sys');

        login_unix_socket: "{{ login_unix_socket | default(omit) }}"
      register: mysql_users_wo_passwords_or_auth_string

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

Result :

TASK [Get all users that have no authentication_string on MySQL version >= 5.7.6 or Mariadb version >= 10.4.0 query=SELECT GROUP_CONCAT(QUOTE(USER), '@', QUOTE(HOST) SEPARATOR ', ') AS users FROM mysql.user WHERE (length(authentication_string)=0
   OR authentication_string="")
  AND USER NOT IN ('mysql.sys',
               'mysqlxsys',
               'mariadb.sys');
, login_unix_socket={{ login_unix_socket | default(omit) }}] ***
Thursday 23 February 2023  12:46:10 +0100 (0:00:00.103)       0:00:01.766 ***** 
ok: [centreonnew.xxx.int]

TASK [debug _variable_params=msg:"{{ mysql_users_wo_passwords_or_auth_string }}"] ********************************************************************************************************************************************************************
Thursday 23 February 2023  12:46:11 +0100 (0:00:00.697)       0:00:02.464 ***** 
ok: [centreonnew.xxx.int] => 
  msg: Hello world!

Direct query in mysql :

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 99
Server version: 10.5.19-MariaDB-1:10.5.19+maria~deb11 mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> SELECT GROUP_CONCAT(QUOTE(USER), '@', QUOTE(HOST) SEPARATOR ', ') AS users FROM mysql.user WHERE (length(authentication_string)=0
    ->    OR authentication_string="")
    ->   AND USER NOT IN ('mysql.sys',
    ->                'mysqlxsys',
    ->                'mariadb.sys');
+-------+
| users |
+-------+
| NULL  |
+-------+
1 row in set (0.002 sec)

MariaDB [(none)]> 

Current Behavior

Expected Behavior

No error in execution

OS / Environment

Provide all relevant information below, e.g. target OS versions, network device firmware, etc.
Ansible server : Ubuntu 22.04
node : Debian 11

Ansible Version

ansible [core 2.14.2]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/home/ansible/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3/dist-packages/ansible
  ansible collection location = /home/ansible/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.10.6 (main, Nov 14 2022, 16:10:14) [GCC 11.3.0] (/usr/bin/python3)
  jinja version = 3.0.3
  libyaml = True

Collection Version

# /home/ansible/.ansible/collections/ansible_collections
Collection        Version
----------------- -------
ansible.posix     1.4.0  
community.crypto  2.9.0  
community.general 5.5.0  
community.mongodb 1.4.1  
community.mysql   3.3.0  
devsec.hardening  8.6.0

Additional information

No response

[sbourdette]

"Hello world!" is the default value of debug.msg

I have tried to debug mysql_users_wo_passwords_or_auth_string.query_result.0.0

and it return 'users: null'

json_query don't like string null ?

[schurzi]

the "Hello world!" is actually a consequence of an error in you test script, you missed a after msg: ;)

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

leeds to an output of:

TASK [debug] ****************************************************************************************************************************************************************************************************************************
ok: [localhost] => {
    "msg": {
        "changed": false,
        "executed_queries": [
            "SELECT GROUP_CONCAT(QUOTE(USER), '@', QUOTE(HOST) SEPARATOR ', ') AS users FROM mysql.user WHERE (length(authentication_string)=0\n   OR authentication_string=\"\")\n  AND USER NOT IN ('mysql.sys',\n               'mysqlxsys',\n               'mariadb.sys');\n"
        ],
        "failed": false,
        "query_result": [
            [
                {
                    "users": null
                }
            ]
        ],
        "rowcount": [
            1
        ]
    }
}

[schurzi]

so I tried to reproduce your problem with this playbook:

- hosts: localhost
  tasks:
    - name: Get all users that have no authentication_string on MySQL version >= 5.7.6 or Mariadb version >= 10.4.0
      community.mysql.mysql_query:
        query: >
          SELECT GROUP_CONCAT(QUOTE(USER), '@', QUOTE(HOST) SEPARATOR ', ') AS users
          FROM mysql.user
          WHERE (length(authentication_string)=0

             OR authentication_string="")
                     AND USER NOT IN ('mysql.sys',
                         'mysqlxsys',
                         'mariadb.sys');
                         ;

        login_unix_socket: "/var/run/mysqld/mysqld.sock"
      register: mysql_users_wo_passwords_or_auth_string

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


    - name: Create a fact for users without password or authentication_string
      ansible.builtin.set_fact:
        users_wo_auth: "{{ mysql_users_wo_passwords_or_auth_string.query_result.0.0 | community.general.json_query('users') }}"
      when:
        - mysql_users_wo_passwords_or_auth_string.query_result is defined
        - mysql_users_wo_passwords_or_auth_string.query_result != "" # noqa empty-string-compare

results:

root@5edb15cac213:/# ansible-playbook mtest.yml

PLAY [localhost] ************************************************************************************************************************************************************************************************************************

TASK [Gathering Facts] ******************************************************************************************************************************************************************************************************************
ok: [localhost]

TASK [Get all users that have no authentication_string on MySQL version >= 5.7.6 or Mariadb version >= 10.4.0] **************************************************************************************************************************
ok: [localhost]

TASK [debug] ****************************************************************************************************************************************************************************************************************************
ok: [localhost] => {
    "msg": {
        "changed": false,
        "executed_queries": [
            "SELECT GROUP_CONCAT(QUOTE(USER), '@', QUOTE(HOST) SEPARATOR ', ') AS users FROM mysql.user WHERE (length(authentication_string)=0\n\n   OR authentication_string=\"\")\n           AND USER NOT IN ('mysql.sys',\n               'mysqlxsys',\n               'mariadb.sys');\n               ;\n"
        ],
        "failed": false,
        "query_result": [
            [
                {
                    "users": null
                }
            ]
        ],
        "rowcount": [
            1
        ]
    }
}

TASK [Create a fact for users without password or authentication_string] ****************************************************************************************************************************************************************
ok: [localhost]

PLAY RECAP ******************************************************************************************************************************************************************************************************************************
localhost                  : ok=4    changed=0    unreachable=0    failed=0    skipped=0    rescued=0    ignored=0

So it basically seems to work. Can you try to gather more information to debug this? (maybe run my script)

regardless I think you conditions are false, since the community.mysql.mysql_query will always return a query_result (see: https://github.com/ansible-collections/community.mysql/blob/9b8455c2e66aebd2e0adeb544450749876d7537d/plugins/modules/mysql_query.py#L204)

[sbourdette]

Thanks for the reply,

I have run tasks with verbose :

TASK [debug msg={{ mysql_users_wo_passwords_or_auth_string }}] ***************************************************************************************************************************************************************************************
task path: /opt/ansible-xxx/test.yml:38
Friday 24 February 2023  09:21:55 +0100 (0:00:00.682)       0:00:02.427 ******* 
ok: [centreonnew.xxx.int] => 
  msg:
    changed: false
    executed_queries:
    - |-
      SELECT GROUP_CONCAT(QUOTE(USER), '@', QUOTE(HOST) SEPARATOR ', ') AS users FROM mysql.user WHERE (length(authentication_string)=0
  
         OR authentication_string="")
                 AND USER NOT IN ('mysql.sys',
                     'mysqlxsys',
                     'mariadb.sys');
                     ;
    failed: false
    query_result:
    - - users: null
    rowcount:
    - 1

TASK [Create a fact for users without password or authentication_string users_wo_auth={{ mysql_users_wo_passwords_or_auth_string.query_result.0.0 | community.general.json_query('users') }}] ********************************************************
task path: /opt/ansible-xxx/test.yml:43
Friday 24 February 2023  09:21:55 +0100 (0:00:00.055)       0:00:02.483 ******* 
The full traceback is:
Traceback (most recent call last):
  File "/usr/lib/python3/dist-packages/ansible/executor/task_executor.py", line 505, in _execute
    self._task.post_validate(templar=templar)
  File "/usr/lib/python3/dist-packages/ansible/playbook/task.py", line 291, in post_validate
    super(Task, self).post_validate(templar)
  File "/usr/lib/python3/dist-packages/ansible/playbook/base.py", line 536, in post_validate
    value = templar.template(getattr(self, name))
  File "/usr/lib/python3/dist-packages/ansible/template/__init__.py", line 750, in template
    d[k] = self.template(
  File "/usr/lib/python3/dist-packages/ansible/template/__init__.py", line 724, in template
    result = self.do_template(
  File "/usr/lib/python3/dist-packages/ansible/template/__init__.py", line 987, in do_template
    res = self.environment.concat(rf)
  File "/usr/lib/python3/dist-packages/ansible/template/native_helpers.py", line 44, in ansible_eval_concat
    head = list(islice(nodes, 2))
  File "<template>", line 18, in root
  File "/usr/lib/python3/dist-packages/ansible/template/__init__.py", line 262, in wrapper
    ret = func(*args, **kwargs)
  File "/home/ansible/.ansible/collections/ansible_collections/community/general/plugins/filter/json_query.py", line 129, in json_query
    jmespath.functions.REVERSE_TYPES_MAP['string'] = jmespath.functions.REVERSE_TYPES_MAP['string'] + ('AnsibleUnicode', 'AnsibleUnsafeText', )
AttributeError: module 'jmespath' has no attribute 'functions'
fatal: [centreonnew.xxx.int]: FAILED! => changed=false

Maybe I'm missing some python library ?

[sbourdette]

I got it !!!

I had to install python3-jmespath on ansible server

Maybe you have to mention it as pre-requisite !

[schurzi]

I just started a PR which makes the dependency for python3-jmespath obsolete. So I think we don't need to document this :D

When the PR is merged, your issue will be auto-closed. Thank you for your report and the work you put into this!