search

ansible-collections / community.mysql

MySQL Ansible Collection
https://galaxy.ansible.com/ui/repo/published/community/mysql/
Other
97 stars 87 forks source link

Bug revoking 'WITH GRANT OPTION' #664

Closed michaeldg closed 1 month ago

michaeldg commented 1 month ago
SUMMARY

When trying to update the permissions of a user that has WITH GRANT OPTION, there is an error in the output.

ISSUE TYPE
COMPONENT NAME

community.mysql.user

ANSIBLE VERSION
ansible [core 2.13.13]
  config file = None
  configured module search path = ['/home/myuser/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /home/myuser/.local/pipx/venvs/ansible/lib/python3.8/site-packages/ansible
  ansible collection location = /home/myuser/.ansible/collections:/usr/share/ansible/collections
  executable location = /home/myuser/.local/bin/ansible
  python version = 3.8.10 (default, Mar 25 2024, 10:42:49) [GCC 9.4.0]
  jinja version = 3.1.4
  libyaml = True
COLLECTION VERSION
# /home/myuser/.ansible/collections/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.9.0  

# /home/myuser/.local/pipx/venvs/ansible/lib/python3.8/site-packages/ansible_collections
Collection      Version
--------------- -------
community.mysql 3.5.1
CONFIGURATION
no output
OS / ENVIRONMENT

Both control host and target host Ubuntu 20.04.

STEPS TO REPRODUCE

These are the current permissions:

MariaDB [(none)]> show grants for correct_run@'%';
+------------------------------------------------------------------------------------------------------------------+
| Grants for correct_run@%                                                                                                |
+------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `correct_run`@`%` IDENTIFIED BY PASSWORD '*' REQUIRE X509 |
| GRANT ALL PRIVILEGES ON `correct_run`.* TO `correct_run`@`%`                                                                   |
+------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

MariaDB [(none)]> show grants for incorrect_run@'%';
+-----------------------------------------------------------------------------------------------------+
| Grants for incorrect_run@%                                                                                   |
+-----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `incorrect_run`@`%` IDENTIFIED BY PASSWORD '*hidden' |
| GRANT ALL PRIVILEGES ON `incorrect_run`.* TO `incorrect_run`@`%` WITH GRANT OPTION                                    |
+-----------------------------------------------------------------------------------------------------+
2 rows in set (0.000 sec)

The difference on the old permissions is 'WITH GRANT OPTION'.

  - name: "This play executes without errors"                                    
    mysql_user:
      name: "correct_run"                                   
      password: "*SECRET"
      encrypted: 1
      host: "%"
      priv: "correct_run.*:ALL"
      tls_requires: 
        X509:

  - name: "This play generates an error"                                    
    mysql_user:
      name: "incorrect_run"                                   
      password: "*SECRET"
      encrypted: 1
      host: "%"
      priv: "incorrect_run.*:ALL"
      tls_requires: 
        X509:
EXPECTED RESULTS

I expect the users 'incorrect_run' to end up with the same permissions as the user 'correct_run'.

ACTUAL RESULTS
TASK [mariadb-mysql-galera : This play executes without errors] *****************************************************************************************************************************************************************************************************************************
task path: /home/myuser/.ansible/roles/mariadb-mysql-galera/tasks/create-local-single-user.yml:12
redirecting (type: modules) ansible.builtin.mysql_user to community.mysql.mysql_user
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' target.host '/bin/sh -c '"'"'echo ~ && sleep 0'"'"''
<target.host> (0, b'/home/myuser\n', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n")
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' target.host '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /home/myuser/.ansible/tmp `"&& mkdir "` echo /home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950 `" && echo ansible-tmp-1721730937.3171074-455733-246529581150950="` echo /home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950 `" ) && sleep 0'"'"''
<target.host> (0, b'ansible-tmp-1721730937.3171074-455733-246529581150950=/home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950\n', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n")
redirecting (type: modules) ansible.builtin.mysql_user to community.mysql.mysql_user
Using module file /home/myuser/.ansible/collections/ansible_collections/community/mysql/plugins/modules/mysql_user.py
<target.host> PUT /home/myuser/.ansible/tmp/ansible-local-453106ih71e6iv/tmpmizjgmbx TO /home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950/AnsiballZ_mysql_user.py
<target.host> SSH: EXEC sftp -b - -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' '[target.host]'
<target.host> (0, b'sftp> put /home/myuser/.ansible/tmp/ansible-local-453106ih71e6iv/tmpmizjgmbx /home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950/AnsiballZ_mysql_user.py\n', b'OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: /usr/bin/ssh -vvv -W \'[%h]:%p\' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug2: Remote version: 3\r\ndebug2: Server supports extension "posix-rename@openssh.com" revision 1\r\ndebug2: Server supports extension "statvfs@openssh.com" revision 2\r\ndebug2: Server supports extension "fstatvfs@openssh.com" revision 2\r\ndebug2: Server supports extension "hardlink@openssh.com" revision 1\r\ndebug2: Server supports extension "fsync@openssh.com" revision 1\r\ndebug2: Server supports extension "lsetstat@openssh.com" revision 1\r\ndebug3: Sent message fd 3 T:16 I:1\r\ndebug3: SSH_FXP_REALPATH . -> /home/myuser size 0\r\ndebug3: Looking up /home/myuser/.ansible/tmp/ansible-local-453106ih71e6iv/tmpmizjgmbx\r\ndebug3: Sent message fd 3 T:17 I:2\r\ndebug3: Received stat reply T:101 I:2\r\ndebug1: Couldn\'t stat remote file: No such file or directory\r\ndebug3: Sent message SSH2_FXP_OPEN I:3 P:/home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950/AnsiballZ_mysql_user.py\r\ndebug3: Sent message SSH2_FXP_WRITE I:4 O:0 S:32768\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 4 32768 bytes at 0\r\ndebug3: Sent message SSH2_FXP_WRITE I:5 O:32768 S:32768\r\ndebug3: Sent message SSH2_FXP_WRITE I:6 O:65536 S:32768\r\ndebug3: Sent message SSH2_FXP_WRITE I:7 O:98304 S:32768\r\ndebug3: Sent message SSH2_FXP_WRITE I:8 O:131072 S:32768\r\ndebug3: Sent message SSH2_FXP_WRITE I:9 O:163840 S:3695\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 5 32768 bytes at 32768\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 6 32768 bytes at 65536\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 7 32768 bytes at 98304\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 8 32768 bytes at 131072\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 9 3695 bytes at 163840\r\ndebug3: Sent message SSH2_FXP_CLOSE I:4\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n')
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' target.host '/bin/sh -c '"'"'chmod u+x /home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950/ /home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950/AnsiballZ_mysql_user.py && sleep 0'"'"''
<target.host> (0, b'', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n")
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' -tt target.host '/bin/sh -c '"'"'sudo -H -S -p "[sudo via ansible, key=gwnowukrmhowsowhweivgmwholntwjof] password:" -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-gwnowukrmhowsowhweivgmwholntwjof ; /usr/bin/python3 /home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950/AnsiballZ_mysql_user.py'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded
<target.host> (0, b'\r\n\r\n{"changed": true, "user": "correct_run", "msg": "TLS requires updated", "password_changed": false, "attributes": {}, "invocation": {"module_args": {"name": "correct_run", "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "encrypted": true, "host": "%", "priv": "correct_run.*:ALL", "tls_requires": {"X509": ""}, "user": "correct_run", "login_host": "localhost", "login_port": 3306, "config_file": "/root/.my.cnf", "connect_timeout": 30, "host_all": false, "state": "present", "append_privs": false, "subtract_privs": false, "check_implicit_admin": false, "update_password": "always", "sql_log_bin": true, "force_context": false, "login_user": null, "login_password": null, "login_unix_socket": null, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "attributes": null, "plugin": null, "plugin_hash_string": null, "plugin_auth_string": null, "resource_limits": null, "session_vars": null, "column_case_sensitive": null, "password_expire": null, "password_expire_interval": null}}, "warnings": ["Option column_case_sensitive is not provided. The default is now false, so the column\'s name will be uppercased. The default will be changed to true in community.mysql 4.0.0."]}\r\n', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\nShared connection to target.host closed.\r\n")
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' target.host '/bin/sh -c '"'"'rm -f -r /home/myuser/.ansible/tmp/ansible-tmp-1721730937.3171074-455733-246529581150950/ > /dev/null 2>&1 && sleep 0'"'"''
<target.host> (0, b'', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n")
changed: [target.host] => {
    "attributes": {},
    "changed": true,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "attributes": null,
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "column_case_sensitive": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encrypted": true,
            "force_context": false,
            "host": "%",
            "host_all": false,
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": null,
            "name": "correct_run",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "password_expire": null,
            "password_expire_interval": null,
            "plugin": null,
            "plugin_auth_string": null,
            "plugin_hash_string": null,
            "priv": "correct_run.*:ALL",
            "resource_limits": null,
            "session_vars": null,
            "sql_log_bin": true,
            "state": "present",
            "subtract_privs": false,
            "tls_requires": {
                "X509": ""
            },
            "update_password": "always",
            "user": "correct_run"
        }
    },
    "msg": "TLS requires updated",
    "password_changed": false,
    "user": "correct_run"
}

TASK [mariadb-mysql-galera : this play generates an error] *****************************************************************************************************************************************************************************************************************************
task path: /home/myuser/.ansible/roles/mariadb-mysql-galera/tasks/create-local-single-user.yml:12
redirecting (type: modules) ansible.builtin.mysql_user to community.mysql.mysql_user
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' target.host '/bin/sh -c '"'"'echo ~ && sleep 0'"'"''
<target.host> (0, b'/home/myuser\n', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n")
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' target.host '/bin/sh -c '"'"'( umask 77 && mkdir -p "` echo /home/myuser/.ansible/tmp `"&& mkdir "` echo /home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550 `" && echo ansible-tmp-1721730938.0098393-455753-193797523963550="` echo /home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550 `" ) && sleep 0'"'"''
<target.host> (0, b'ansible-tmp-1721730938.0098393-455753-193797523963550=/home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550\n', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n")
redirecting (type: modules) ansible.builtin.mysql_user to community.mysql.mysql_user
Using module file /home/myuser/.ansible/collections/ansible_collections/community/mysql/plugins/modules/mysql_user.py
<target.host> PUT /home/myuser/.ansible/tmp/ansible-local-453106ih71e6iv/tmpdg1eonog TO /home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550/AnsiballZ_mysql_user.py
<target.host> SSH: EXEC sftp -b - -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' '[target.host]'
<target.host> (0, b'sftp> put /home/myuser/.ansible/tmp/ansible-local-453106ih71e6iv/tmpdg1eonog /home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550/AnsiballZ_mysql_user.py\n', b'OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: /usr/bin/ssh -vvv -W \'[%h]:%p\' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug2: Remote version: 3\r\ndebug2: Server supports extension "posix-rename@openssh.com" revision 1\r\ndebug2: Server supports extension "statvfs@openssh.com" revision 2\r\ndebug2: Server supports extension "fstatvfs@openssh.com" revision 2\r\ndebug2: Server supports extension "hardlink@openssh.com" revision 1\r\ndebug2: Server supports extension "fsync@openssh.com" revision 1\r\ndebug2: Server supports extension "lsetstat@openssh.com" revision 1\r\ndebug3: Sent message fd 3 T:16 I:1\r\ndebug3: SSH_FXP_REALPATH . -> /home/myuser size 0\r\ndebug3: Looking up /home/myuser/.ansible/tmp/ansible-local-453106ih71e6iv/tmpdg1eonog\r\ndebug3: Sent message fd 3 T:17 I:2\r\ndebug3: Received stat reply T:101 I:2\r\ndebug1: Couldn\'t stat remote file: No such file or directory\r\ndebug3: Sent message SSH2_FXP_OPEN I:3 P:/home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550/AnsiballZ_mysql_user.py\r\ndebug3: Sent message SSH2_FXP_WRITE I:4 O:0 S:32768\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 4 32768 bytes at 0\r\ndebug3: Sent message SSH2_FXP_WRITE I:5 O:32768 S:32768\r\ndebug3: Sent message SSH2_FXP_WRITE I:6 O:65536 S:32768\r\ndebug3: Sent message SSH2_FXP_WRITE I:7 O:98304 S:32768\r\ndebug3: Sent message SSH2_FXP_WRITE I:8 O:131072 S:32768\r\ndebug3: Sent message SSH2_FXP_WRITE I:9 O:163840 S:3695\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 5 32768 bytes at 32768\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 6 32768 bytes at 65536\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 7 32768 bytes at 98304\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 8 32768 bytes at 131072\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: In write loop, ack for 9 3695 bytes at 163840\r\ndebug3: Sent message SSH2_FXP_CLOSE I:4\r\ndebug3: SSH2_FXP_STATUS 0\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n')
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' target.host '/bin/sh -c '"'"'chmod u+x /home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550/ /home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550/AnsiballZ_mysql_user.py && sleep 0'"'"''
<target.host> (0, b'', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n")
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' -tt target.host '/bin/sh -c '"'"'sudo -H -S -p "[sudo via ansible, key=oxvksztycancdjxwnerwzbqyyrwcjrdf] password:" -u root /bin/sh -c '"'"'"'"'"'"'"'"'echo BECOME-SUCCESS-oxvksztycancdjxwnerwzbqyyrwcjrdf ; /usr/bin/python3 /home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550/AnsiballZ_mysql_user.py'"'"'"'"'"'"'"'"' && sleep 0'"'"''
Escalation succeeded
<target.host> (1, b'\r\n\r\n{"failed": true, "msg": "(1141, \\"There is no such grant defined for user \'incorrect_run\' on host \'%\'\\")", "exception": "  File \\"/tmp/ansible_mysql_user_payload_s3cylwrs/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py\\", line 546, in main\\n  File \\"/tmp/ansible_mysql_user_payload_s3cylwrs/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/user.py\\", line 397, in user_mod\\n    privileges_revoke(cursor, user, host, db_table, priv, grant_option, maria_role)\\n  File \\"/tmp/ansible_mysql_user_payload_s3cylwrs/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/user.py\\", line 814, in privileges_revoke\\n    cursor.execute(query, params)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/cursors.py\\", line 170, in execute\\n    result = self._query(query)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/cursors.py\\", line 328, in _query\\n    conn.query(q)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 517, in query\\n    self._affected_rows = self._read_query_result(unbuffered=unbuffered)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 732, in _read_query_result\\n    result.read()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 1075, in read\\n    first_packet = self.connection._read_packet()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/connections.py\\", line 684, in _read_packet\\n    packet.check_error()\\n  File \\"/usr/lib/python3/dist-packages/pymysql/protocol.py\\", line 220, in check_error\\n    err.raise_mysql_exception(self._data)\\n  File \\"/usr/lib/python3/dist-packages/pymysql/err.py\\", line 109, in raise_mysql_exception\\n    raise errorclass(errno, errval)\\n", "invocation": {"module_args": {"name": "incorrect_run", "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER", "encrypted": true, "host": "%", "priv": "incorrect_run.*:ALL", "tls_requires": {"X509": ""}, "user": "incorrect_run", "login_host": "localhost", "login_port": 3306, "config_file": "/root/.my.cnf", "connect_timeout": 30, "host_all": false, "state": "present", "append_privs": false, "subtract_privs": false, "check_implicit_admin": false, "update_password": "always", "sql_log_bin": true, "force_context": false, "login_user": null, "login_password": null, "login_unix_socket": null, "client_cert": null, "client_key": null, "ca_cert": null, "check_hostname": null, "attributes": null, "plugin": null, "plugin_hash_string": null, "plugin_auth_string": null, "resource_limits": null, "session_vars": null, "column_case_sensitive": null, "password_expire": null, "password_expire_interval": null}}, "warnings": ["Option column_case_sensitive is not provided. The default is now false, so the column\'s name will be uppercased. The default will be changed to true in community.mysql 4.0.0."]}\r\n', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 1\r\nShared connection to target.host closed.\r\n")
<target.host> Failed to connect to the host via ssh: OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020
debug1: Reading configuration data /home/myuser/.ssh/config
debug1: /home/myuser/.ssh/config line 1: Applying options for target.host
debug1: Reading configuration data /etc/ssh/ssh_config
debug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files
debug1: /etc/ssh/ssh_config line 21: Applying options for *
debug2: resolve_canonicalize: hostname target.host is address
debug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host
debug1: auto-mux: Trying existing master
debug2: fd 3 setting O_NONBLOCK
debug2: mux_client_hello_exchange: master version 4
debug3: mux_client_forwards: request forwardings: 0 local, 0 remote
debug3: mux_client_request_session: entering
debug3: mux_client_request_alive: entering
debug3: mux_client_request_alive: done pid = 453129
debug3: mux_client_request_session: session request sent
debug3: mux_client_read_packet: read header failed: Broken pipe
debug2: Received exit status from master 1
Shared connection to target.host closed.
<target.host> ESTABLISH SSH CONNECTION FOR USER: None
<target.host> SSH: EXEC ssh -vvv -C -o ControlMaster=auto -o ControlPersist=60s -o KbdInteractiveAuthentication=no -o PreferredAuthentications=gssapi-with-mic,gssapi-keyex,hostbased,publickey -o PasswordAuthentication=no -o ConnectTimeout=10 -o 'ControlPath="/home/myuser/.ansible/cp/9779fcaaf9"' target.host '/bin/sh -c '"'"'rm -f -r /home/myuser/.ansible/tmp/ansible-tmp-1721730938.0098393-455753-193797523963550/ > /dev/null 2>&1 && sleep 0'"'"''
<target.host> (0, b'', b"OpenSSH_8.2p1 Ubuntu-4ubuntu0.11, OpenSSL 1.1.1f  31 Mar 2020\r\ndebug1: Reading configuration data /home/myuser/.ssh/config\r\ndebug1: /home/myuser/.ssh/config line 1: Applying options for target.host\r\ndebug1: Reading configuration data /etc/ssh/ssh_config\r\ndebug1: /etc/ssh/ssh_config line 19: include /etc/ssh/ssh_config.d/*.conf matched no files\r\ndebug1: /etc/ssh/ssh_config line 21: Applying options for *\r\ndebug2: resolve_canonicalize: hostname target.host is address\r\ndebug1: Setting implicit ProxyCommand from ProxyJump: ssh -vvv -W '[%h]:%p' proxy.jump.host\r\ndebug1: auto-mux: Trying existing master\r\ndebug2: fd 3 setting O_NONBLOCK\r\ndebug2: mux_client_hello_exchange: master version 4\r\ndebug3: mux_client_forwards: request forwardings: 0 local, 0 remote\r\ndebug3: mux_client_request_session: entering\r\ndebug3: mux_client_request_alive: entering\r\ndebug3: mux_client_request_alive: done pid = 453129\r\ndebug3: mux_client_request_session: session request sent\r\ndebug3: mux_client_read_packet: read header failed: Broken pipe\r\ndebug2: Received exit status from master 0\r\n")
The full traceback is:
  File "/tmp/ansible_mysql_user_payload_s3cylwrs/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 546, in main
  File "/tmp/ansible_mysql_user_payload_s3cylwrs/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/user.py", line 397, in user_mod
    privileges_revoke(cursor, user, host, db_table, priv, grant_option, matarget.hostria_role)
  File "/tmp/ansible_mysql_user_payload_s3cylwrs/ansible_mysql_user_payload.zip/ansible_collections/community/mysql/plugins/module_utils/user.py", line 814, in privileges_revoke
    cursor.execute(query, params)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 170, in execute
    result = self._query(query)
  File "/usr/lib/python3/dist-packages/pymysql/cursors.py", line 328, in _query
    conn.query(q)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 517, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 732, in _read_query_result
    result.read()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 1075, in read
    first_packet = self.connection._read_packet()
  File "/usr/lib/python3/dist-packages/pymysql/connections.py", line 684, in _read_packet
    packet.check_error()
  File "/usr/lib/python3/dist-packages/pymysql/protocol.py", line 220, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python3/dist-packages/pymysql/err.py", line 109, in raise_mysql_exception
    raise errorclass(errno, errval)
fatal: [target.host]: FAILED! => {
    "changed": false,
    "invocation": {
        "module_args": {
            "append_privs": false,
            "attributes": null,
            "ca_cert": null,
            "check_hostname": null,
            "check_implicit_admin": false,
            "client_cert": null,
            "client_key": null,
            "column_case_sensitive": null,
            "config_file": "/root/.my.cnf",
            "connect_timeout": 30,
            "encrypted": true,
            "force_context": false,
            "host": "%",
            "host_all": false,
            "login_host": "localhost",
            "login_password": null,
            "login_port": 3306,
            "login_unix_socket": null,
            "login_user": null,
            "name": "incorrect_run",
            "password": "VALUE_SPECIFIED_IN_NO_LOG_PARAMETER",
            "password_expire": null,
            "password_expire_interval": null,
            "plugin": null,
            "plugin_auth_string": null,
            "plugin_hash_string": null,
            "priv": "incorrect_run.*:ALL",
            "resource_limits": null,
            "session_vars": null,
            "sql_log_bin": true,
            "state": "present",
            "subtract_privs": false,
            "tls_requires": {
                "X509": ""
            },
            "update_password": "always",
            "user": "incorrect_run"
        }
    },
    "msg": "(1141, \"There is no such grant defined for user 'incorrect_run' on host '%'\")"
}
laurent-indermuehle commented 1 month ago

Hi @michaeldg and thanks for opening this issue. Unfortunately, I'm unable to reproduce your error with community.mysql 3.9.0 and MariaDB 10.6. Do you have the issue with both 3.9.0 and 3.5.1? Which database engine are you using? (MySQL, MariaDB, Galera) And what version?

When running your example, the GRANT OPTION gets removed from the user incorrect_run@'%'. Is this what you're trying to do?

Is there any other user accounts with the same username incorrect_run present in your database ?

Sometimes, running flush privileges; unlock those kind of issues.

Please, tests the above command and report back any additional informations that could help us understand the issue.

Andersson007 commented 1 month ago

@michaeldg thanks for reporting the issue! @laurent-indermuehle thanks for answering! As there's no feedback, closing the issue in order to keep the tracker clean. If any new comments feedback appear, we'll re-open it, thanks