mysql_role: multiple bugs with detach_members

[betanummeric]

ISSUE TYPE

• Bug Report

AFFECTED VERSIONS

ansible 2.10.8
community.mysql 3.2.1, mysql_role module

SUMMARY

The detach_members argument of the mysql_role module is documented as follows:

Detaches members defined by the members option from the role instead of overwriting all the current members.

adding unwanted members

When the role does not exist, it is created and the users specified in members are added instead of detached, regardless of detach_members.

See the lack of detach_members here: https://github.com/ansible-collections/community.mysql/blob/main/plugins/modules/mysql_role.py#L1067-L1071

detaching too few members

Detecting the existing members of a role does not work if the SHOW GRANTS statement uses other quotes than backticks (`). I checked MariaDB 10.{1-6} and MySQL 5.7, 8.0, with and without ANSI_QUOTES SQL-mode, and found either single or double quotes ('/"), so I don't know why the module expects backticks. This causes that existing members are added again (redundant) and no unwanted members are detached.

The backticks are assumed here:
https://github.com/ansible-collections/community.mysql/blob/main/plugins/modules/mysql_role.py#L691
https://github.com/ansible-collections/community.mysql/blob/main/plugins/modules/mysql_role.py#L697

On MariaDB 10.6, notice the double quotes:

mysql ((none))> create user testuser;
Query OK, 0 rows affected (0.005 sec)

mysql ((none))> grant testrole1 to testuser;
Query OK, 0 rows affected (0.003 sec)

mysql ((none))> show grants for testuser;
+--------------------------------------+
| Grants for testuser@%                |
+--------------------------------------+
| GRANT "testrole1" TO "testuser"@"%"  |
| GRANT USAGE ON *.* TO "testuser"@"%" |
+--------------------------------------+
2 rows in set (0.001 sec)

mysql ((none))> select version();
+--------------------------------------------+
| version()                                  |
+--------------------------------------------+
| 10.6.4-MariaDB-1:10.6.4+maria~bullseye-log |
+--------------------------------------------+
1 row in set (0.001 sec)

raising too much errors

When a user which shall be detached from a role does not exist, the module fails. I would like the module to ignore this user. Other people may expect the failure, so we could add a flag (e.g. members_must_exist, default true) to toggle failing/ignoring.

steps to reproduce

- name: ensure the user does not exist
  community.mysql.mysql_user:
    login_unix_socket: /tmp/mysql-3306.sock
    name: missing_user
    host: localhost
    state: absent
- name: ensure the role does not exist
  community.mysql.mysql_role:
    login_unix_socket: /tmp/mysql-3306.sock
    state: absent
    name: testrole1
- name: create role  # this will fail
  community.mysql.mysql_role:
    login_unix_socket: /tmp/mysql-3306.sock
    state: present
    name: testrole1
    detach_members: yes
    members:
      - missing_user@localhost

[betanummeric]

Hi @bmalynovytch, @Jorge-Rodriguez, @rsicart, @Andersson007, can you please take a look at this? :)

[betanummeric]

@Andersson007 There are still multiple open pull requests for this issue, so I would recommend to reopen this issue.

[Andersson007]

@betanummeric i didn't close it:) It was closed automatically because there's Fixes ... in the PRs:) Yes, sure, i'll re-open it, thanks for letting me know!