MariaDB, parsing roles failed.

[pookey]

SUMMARY

When trying to manager a user with roles applied the user module appears to fail to parse existing grants.

ISSUE TYPE

• Bug Report

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible [core 2.11.6]
  config file = /etc/ansible/ansible.cfg
  configured module search path = ['/root/.ansible/plugins/modules', '/usr/share/ansible/plugins/modules']
  ansible python module location = /usr/lib/python3.6/site-packages/ansible
  ansible collection location = /root/.ansible/collections:/usr/share/ansible/collections
  executable location = /usr/bin/ansible
  python version = 3.6.13 (default, Mar 10 2021, 18:30:35) [GCC]
  jinja version = 2.10.1
  libyaml = False

COLLECTION VERSION

# ansible-galaxy collection list community.mysql
# /usr/lib/python3.6/site-packages/ansible_collections
Collection      Version
--------------- -------
community.mysql 2.3.0

CONFIGURATION

DEFAULT_FORKS(/etc/ansible/ansible.cfg) = 30
DEFAULT_GATHERING(/etc/ansible/ansible.cfg) = smart
DEFAULT_HOST_LIST(/etc/ansible/ansible.cfg) = ['/etc/ansible/hosts']
DEFAULT_INTERNAL_POLL_INTERVAL(/etc/ansible/ansible.cfg) = 0.0001
DEFAULT_LOG_PATH(/etc/ansible/ansible.cfg) = /etc/ansible/ansible.log
DEFAULT_POLL_INTERVAL(/etc/ansible/ansible.cfg) = 1
DEFAULT_STRATEGY(/etc/ansible/ansible.cfg) = mitogen_linear
DEFAULT_STRATEGY_PLUGIN_PATH(/etc/ansible/ansible.cfg) = ['/tmp/mitogen/ansible_mitogen/plugins/strategy']
HOST_KEY_CHECKING(/etc/ansible/ansible.cfg) = False
PERSISTENT_CONNECT_TIMEOUT(/etc/ansible/ansible.cfg) = 30

OS / ENVIRONMENT

OpenSuse 15.3 / MariaDB 10.6.4

STEPS TO REPRODUCE

 - name: Grant full perms for maxscale
   community.mysql.mysql_user:
     state: present
     name: '{{ maxscale_user }}'
     password: '{{ maxscale_password }}'
     host: '%'
     login_unix_socket: '{{ mysql_sock }}'
     priv:
       '*.*': 'SHOW DATABASES,REPLICATION SLAVE,REPLICATION SLAVE ADMIN,SLAVE MONITOR'

Running a show grants for this user shows this:

> show grants for `shardadmin`@`%`;
GRANT `my_role` TO `shardadmin`@`%` WITH ADMIN OPTION

EXPECTED RESULTS

roles should be parsed.

ACTUAL RESULTS

The full traceback is:
  File "master:/usr/lib/python3.6/site-packages/ansible_collections/community/mysql/plugins/modules/mysql_user.py", line 436, in main
  File "master:/usr/lib/python3.6/site-packages/ansible_collections/community/mysql/plugins/module_utils/user.py", line 297, in user_mod
    curr_priv = privileges_get(cursor, user, host, maria_role)
  File "master:/usr/lib/python3.6/site-packages/ansible_collections/community/mysql/plugins/module_utils/user.py", line 426, in privileges_get
    raise InvalidPrivsError('unable to parse the MySQL grant string: %s' % grant[0])
fatal: [dbshard1a.dev02.jupix.net]: FAILED! => {

without debug:

TASK [dbshard : Grant full perms for shardadmin] *******************************
fatal: [dbshard]: FAILED! => {"changed": false, "msg": "unable to parse the MySQL grant string: GRANT `my_role` TO `shardadmin`@`%` WITH ADMIN OPTION"}

[the02]

Hard to implement, as it depends on the mariadb version used. pymysql fails if mariadb version < 10.3

Edit:
to clarify: this affects granting userroles to users. did not find out so far why it fails

[Andersson007]

@pookey @the02 thanks!

Does this work as expected with community.mysql 2.2.0 ? (2.1.0, ..).
If you didn't try, could you please do that? I'm trying to figure out whether it was introduced recently or it was always there.

Thank you

[the02]

@pookey @the02 thanks!

Does this work as expected with community.mysql 2.2.0 ? (2.1.0, ..). If you didn't try, could you please do that? I'm trying to figure out whether it was introduced recently or it was always there.

Thank you

As far as I remember this also happened with older version of this collection. (cant verify at the moment on my machine as I get an error when trying to install a collection)

[Andersson007]

@the02 ok, thanks for the feedback! I'll create an issue to add mariadb to our CI first. This feels like a right thing to start with.

[Andersson007]

#238

[hubiongithub]

Hello
This also happens against MySQL 8 (to be precise Percona Server 8.0.27)
I create several users, all with priv: '*.*:USAGE' from a list
and a role admin with members set to the list. This works fine, 2nd run of the playbook

unable to parse the MySQL grant string: GRANT admin@% TO user1@localhost"}

It seems that roles granted to the users have the format "grant rolename@'%' to user...."

tested with community.mysql 3.1.2

in module_utils/user.py

def get_grants(cursor, user, host):
    cursor.execute("SHOW GRANTS FOR %s@%s", (user, host))
    grants_line = list(filter(lambda x: "ON *.*" in x[0], cursor.fetchall()))[0]
    pattern = r"(?<=\bGRANT\b)(.*?)(?=(?:\bON\b))"
    grants = re.search(pattern, grants_line[0]).group().strip()
    return grants.split(", ")

the keyword "ON" is missing in the role grants, so the pattern line does not match

My output looks like this:

mysql> show grants for user1@localhost;
+----------------------------------------------------+
| Grants for user1@localhost                    |
+----------------------------------------------------+
| GRANT USAGE ON *.* TO `user1`@`localhost`     |
| GRANT `admin`@`%` TO `user1`@`localhost` |
+----------------------------------------------------+

an additional run on the output with something like this

    grants_role_line = list(filter(lambda x: "@`%` TO " in x[0], cursor.fetchall()))[0]
    pattern_role = r"(?<=\bGRANT\b)(.*?)(?=(?:\b@`%`\b))"

but unsure what r"()" really does here.

ansible 2.9
community.mysql 3.1.2
percona server 8.0.27 on Ubuntu 20.04

My dirty workaround is to remove the role, add/remove the users ans add the role, but this is not a goobd solution as the existing user get ripped of their privileges while the playbook runs.

[Andersson007]

Thank you folks! I'm looking at this

[Andersson007]

#341 could you please take a look? I reproduced the bug in our CI and with the changes, it doesn't appear any more.