mysql role task, "Remove all anonymous user accounts" fails

insanity54 commented 2 years ago

I just spun up a Ubuntu 22.04 server and tried to install Wordpress using this collection. I ran into a failure

TASK [code_egg.openlitespeed_wordpress.mysql : Remove all anonymous user accounts] ******************
An exception occurred during task execution. To see the full traceback, use -vvv. The error was: pymysql.err.OperationalError: (1142, "SELECT command denied to user 'root'@'localhost' for table 'user'")
fatal: [144.202.70.47]: FAILED! => {"changed": false, "module_stderr": "Shared connection to 144.202.70.47 closed.\r\n", "module_stdout": "Traceback (most recent call last):\r\n  File \"/root/.ansible/tmp/ansible-tmp-1667114318.7645643-28643-45791120719066/AnsiballZ_mysql_user.py\", line 107, in <module>\r\n    _ansiballz_main()\r\n  File \"/root/.ansible/tmp/ansible-tmp-1667114318.7645643-28643-45791120719066/AnsiballZ_mysql_user.py\", line 99, in _ansiballz_main\r\n    invoke_module(zipped_mod, temp_path, ANSIBALLZ_PARAMS)\r\n  File \"/root/.ansible/tmp/ansible-tmp-1667114318.7645643-28643-45791120719066/AnsiballZ_mysql_user.py\", line 47, in invoke_module\r\n    runpy.run_module(mod_name='ansible_collections.community.mysql.plugins.modules.mysql_user', init_globals=dict(_module_fqn='ansible_collections.community.mysql.plugins.modules.mysql_user', _modlib_path=modlib_path),\r\n  File \"/usr/lib/python3.10/runpy.py\", line 224, in run_module\r\n    return _run_module_code(code, init_globals, run_name, mod_spec)\r\n  File \"/usr/lib/python3.10/runpy.py\", line 96, in _run_module_code\r\n    _run_code(code, mod_globals, init_globals,\r\n  File \"/usr/lib/python3.10/runpy.py\", line 86, in _run_code\r\n    exec(code, run_globals)\r\n  File \"/tmp/ansible_mysql_user_payload_lvso7fyy/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py\", line 508, in <module>\r\n  File \"/tmp/ansible_mysql_user_payload_lvso7fyy/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py\", line 498, in main\r\n  File \"/tmp/ansible_mysql_user_payload_lvso7fyy/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/user.py\", line 39, in user_exists\r\n  File \"/usr/lib/python3/dist-packages/pymysql/cursors.py\", line 148, in execute\r\n    result = self._query(query)\r\n  File \"/usr/lib/python3/dist-packages/pymysql/cursors.py\", line 310, in _query\r\n    conn.query(q)\r\n  File \"/usr/lib/python3/dist-packages/pymysql/connections.py\", line 548, in query\r\n    self._affected_rows = self._read_query_result(unbuffered=unbuffered)\r\n  File \"/usr/lib/python3/dist-packages/pymysql/connections.py\", line 775, in _read_query_result\r\n    result.read()\r\n  File \"/usr/lib/python3/dist-packages/pymysql/connections.py\", line 1156, in read\r\n    first_packet = self.connection._read_packet()\r\n  File \"/usr/lib/python3/dist-packages/pymysql/connections.py\", line 725, in _read_packet\r\n    packet.raise_for_error()\r\n  File \"/usr/lib/python3/dist-packages/pymysql/protocol.py\", line 221, in raise_for_error\r\n    err.raise_mysql_exception(self._data)\r\n  File \"/usr/lib/python3/dist-packages/pymysql/err.py\", line 143, in raise_mysql_exception\r\n    raise errorclass(errno, errval)\r\npymysql.err.OperationalError: (1142, \"SELECT command denied to user 'root'@'localhost' for table 'user'\")\r\n", "msg": "MODULE FAILURE\nSee stdout/stderr for the exact error", "rc": 1}

I did some searching and I think the cause is that the root user lacks mysql privs necessary to SELECT things in the user database.

I tried modifying the mysql role to see if I could get past the error. I added, priv: '*.*:SELECT' to the task named, "Set MySql root password for 127.0.0.1, ::1"

- name: Set MySql root password for 127.0.0.1, ::1
  mysql_user:
    name: root
    password: "{{ mysql_root_password }}"
    priv: '*.*:SELECT,GRANT'
    host: "{{ item }}"
    login_user: root
    login_password: "{{ mysql_root_password }}"
    login_unix_socket: "/var/run/mysqld/mysqld.sock"
    state: present
  with_items:
    - ::1
    - 127.0.0.1
  when: root_pwd_check.rc == 0
  tags: [ mysql, mysql-root ]

This seemed to do the trick, but after that, there was another error on the task named, "Create MySQL user for WordPress"

"Error granting privileges, invalid priv string: ALL"

I wanted to know more about what is causing this error so I browsed the source of the mysql plugin. Turns out, it could be one of three types of errors and the code isn't designed tot tell us which error it is. It could be a ProgrammingError, OperationalError, or InternalError, but the error message will always suggest that there's something wrong with the priv string.

https://github.com/ansible-collections/community.mysql/blob/b9a6ec4f7d5c8e7293cb3f84e333d1f5fba20be8/plugins/module_utils/user.py#L728

I did a quick patch on that line to print whatever the error is

fatal: [144.202.70.47]: FAILED! => {"changed": false, "msg": "(1044, \"Access denied for user 'root'@'127.0.0.1' to database 'wp4394640'\")"}

We got denied access!

I'll have to dig some more

Code-Egg commented 2 years ago

I think it was caused by the python3-pymysql package, this package seems buggy. The last commit switches to use python3-mysqldb which should have no such py issue. Feel free to give it a try and let me know if it works for you (it works in my test environment).

Code-Egg commented 2 years ago

The commit hasn't been pushed to the public yet, it seems I need to include some must files in order to make the upload work.

Code-Egg commented 2 years ago

Ok, pushed success

Code-Egg / collection

mysql role task, "Remove all anonymous user accounts" fails #4