mysql_role fails to parse show grant response from mysql

[sascha-andres]

Summary

I have a role applied to another user role along with privileges. Now there comes a parsing error:

"unable to parse the MySQL grant string: GRANT app_role_aa_bbbbb_cccc_dddd TO 'role_cccccc_dddddd'"

The role was assigned using a member in another role definition

Issue Type

Bug Report

Component Name

mysql

Ansible Version

$ ansible --version

ansible [core 2.16.4]
  config file = /home/eeeeee/dev/ffff/ansible.cfg
  configured module search path = ['/home/eeeeee/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.11/site-packages/ansible
  ansible collection location = /home/eeeeee/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.11.8 (main, Feb 12 2024, 14:50:05) [GCC 13.2.1 20230801] (/usr/bin/python)
  jinja version = 3.1.3
  libyaml = True

Configuration

# if using a version older than ansible-core 2.12 you should omit the '-t all'
$ ansible-config dump --only-changed -t all

CONFIG_FILE() = /home/eeeeee/dev/ffff/ansible.cfg
DEFAULT_LOG_PATH(/home/eeeeee/dev/ffff/ansible.cfg) = /home/eeeeee/dev/ffff/ansible.log
DEFAULT_REMOTE_USER(/home/eeeeee/dev/ffff/ansible.cfg) = automation
DEFAULT_ROLES_PATH(/home/eeeeee/dev/ffff/ansible.cfg) = ['/home/eeeeee/dev/platform/ffff/galaxy_roles', '/home/eeeeee/dev/ffff/roles']
DEFAULT_STDOUT_CALLBACK(/home/andres/dev/platform/iq-baremetal/ansible/ansible.cfg) = community.general.unixy
DEPRECATION_WARNINGS(/home/eeeeee/dev/ffff/ansible.cfg) = False
EDITOR(env: EDITOR) = code
HOST_KEY_CHECKING(/home/eeeeee/dev/ffff/ansible.cfg) = False
INVENTORY_ENABLED(/home/eeeeee/dev/ffff/ansible.cfg) = ['yaml']

CONNECTION:
==========

paramiko_ssh:
____________
host_key_checking(/home/eeeeee/dev/ffff/ansible.cfg) = False
remote_user(/home/eeeeee/dev/ffff/ansible.cfg) = automation
ssh_args(/home/eeeeee/dev/ffff/ansible.cfg) = -o ForwardAgent=yes

ssh:
___
host_key_checking(/home/eeeeee/dev/ffff/ansible.cfg) = False
remote_user(/home/eeeeee/dev/ffff/ansible.cfg) = ansibleuser
ssh_args(/home/eeeeee/dev/ffff/ansible.cfg) = -o ForwardAgent=yes

OS / Environment

1 │ DISTRIB_ID="ManjaroLinux"␊
2 │ DISTRIB_RELEASE="23.1.4"␊
3 │ DISTRIB_CODENAME="Vulcan"␊
4 │ DISTRIB_DESCRIPTION="Manjaro·Linux"␊

Steps to Reproduce

- name: "[APP ROLE] aa_bbbbb cccc r"
  community.mysql.mysql_role:
    name: app_role_aa_bbbbb_cccc_read
    state: present
    members:
      - role_cccccc_dddddd
    priv:
      'schema.table': 'SELECT'

- name: "sascha.andres"
  community.mysql.mysql_user:
    name: "dddddd.eeeeee"
    ...
    state: present
    ...

# this is the default role and serves as a container to be used
# for collecting all roles
- name: "cccccc.dddddd user role"
  community.mysql.mysql_role:
    name: role_cccccc_dddddd
    state: present
    members:
      - 'cccccc.dddddd@%'
    priv:
      'schema.*': 'SELECT'
      'other.gggggg': 'UPDATE'
      .....

Expected Results

Adjusting or recognizing the role definition as ok

Actual Results

"unable to parse the MySQL grant string: GRANT app_role_aa_bbbbb_cccc_dddd TO 'role_cccccc_dddddd'"

[laurent-indermuehle]

Hello @sascha-andres and thank you for taking the time to report the issue.
Could you please update your post to add:

• community.mysql collection version used (ansible-galaxy collections list)
• The version of the database (mysql 8.? or mariadb 10.?)
• The name of the task that is producing the error in "Steps to Reproduce"

If you could try to re-run your playbook with community.mysql 3.7.2 it would be great. Because we introduced changes on the mysql_role module in 3.8.0 and I wonder if this is a regression.

[sascha-andres]

Hello @laurent-indermuehle,

here are the informations you asked for:

version of role: community-mysql-3.9.0
MariaDB: 10.4.11-MariaDB
this is the name: "cccccc.dddddd user role" or the task producing the error

version 3.7.2 produces the same error

[laurent-indermuehle]

Mmh, I bet this works with MySQL. I recall writing something to help us in those cases in #584

[sascha-andres]

Migrating to mysql is a huge task and probably not feasible. Any way to move on?

[laurent-indermuehle]

@sascha-andres The best I can offer is helping you to contribute a PR. I don't use roles but I can help with the CI, integrations and unit tests.

[sascha-andres]

@laurent-indermuehle basically if i check out the code at $HOME/ansible_collections/community/mysql I can start debugging with my playbook?

I'd assume it's privileges_get in user.py to look for

[laurent-indermuehle]

@sascha-andres : You could. You may have to edit the variable collection_path from your ansible.cfg file for this.

But to work on a PR, it's best to use our own CI. To do so and if you have 'make' and 'podman' available, you can enter the folder $HOME/ansible_collections/community/mysql and run the command:
make ansible="stable-2.17" db_engine_name="mariadb" db_engine_version="10.6.11" python="3.10" connector_name="pymysql" connector_version="1.0.2" target="test_mysql_role"

Alternatively, you can create a PR as draft. This will trigger the GitHub CI. But you won't be able to filter which target to run and it will test all combination of MySQL, MariaDB, Python, etc... (which can be a good thing over the local tests because if you break something for MySQL while working on MariaDB, you are informed earlier).

Then you can try to reproduce the issue with an integration test in tests/integration/targets/test_mysql_role/tasks/test_show_grants.yml and import it from tests/integration/targets/test_mysql_role/tasks/main.yml.