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

sbourdette · 2023-02-23T11:53:17Z

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

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

sbourdette · 2023-02-23T12:47:33Z

"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 · 2023-02-23T21:28:16Z

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 · 2023-02-23T21:45:37Z

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 · 2023-02-24T08:24:47Z

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 · 2023-02-24T08:29:59Z

I got it !!!

I had to install python3-jmespath on ansible server

Maybe you have to mention it as pre-requisite !

schurzi · 2023-02-24T11:47:59Z

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!

sbourdette added the bug label Feb 23, 2023

schurzi mentioned this issue Feb 24, 2023

simplify MySQL queries for user deletion #641

Merged

schurzi linked a pull request Feb 24, 2023 that will close this issue

simplify MySQL queries for user deletion #641

Merged

rndmh3ro closed this as completed in #641 Mar 1, 2023

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

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

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

sbourdette commented Feb 23, 2023 •

edited by schurzi

Loading

sbourdette commented Feb 23, 2023

schurzi commented Feb 23, 2023 •

edited

Loading

schurzi commented Feb 23, 2023 •

edited

Loading

sbourdette commented Feb 24, 2023

sbourdette commented Feb 24, 2023

schurzi commented Feb 24, 2023

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

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

Comments

sbourdette commented Feb 23, 2023 • edited by schurzi Loading

Description

Reproduction steps

Current Behavior

Expected Behavior

OS / Environment

Ansible Version

Collection Version

Additional information

sbourdette commented Feb 23, 2023

schurzi commented Feb 23, 2023 • edited Loading

schurzi commented Feb 23, 2023 • edited Loading

sbourdette commented Feb 24, 2023

sbourdette commented Feb 24, 2023

schurzi commented Feb 24, 2023

sbourdette commented Feb 23, 2023 •

edited by schurzi

Loading

schurzi commented Feb 23, 2023 •

edited

Loading

schurzi commented Feb 23, 2023 •

edited

Loading