mysql_user: support IDENTIFIED VIA/WITH

[Andersson007]

Copied from ansible-collections/community.general#170
Initially reported by @drzraf

SUMMARY

Support user IDENTIFIED VIA / IDENTIFIER WITH (eg unix_socket is one such options)

ISSUE TYPE

• Feature Idea

COMPONENT NAME

mysql_user

ADDITIONAL INFORMATION

• mysql_user: add resource_limits parameter community.general#142 / add mysql options to mysql_user module ansible/ansible#26581 (comment)
• https://dev.mysql.com/doc/refman/5.5/en/create-user.html
• https://wiki.debian.org/MySql
• Don't require a username or password when using a socket prometheus/mysqld_exporter#233 is one such example app' where a mysql user needs to be provisioned in a way Ansible can't currently do

[jboulen]

Hello,

Maybe this feature is already implemented, isn't it ? See documentation here and code here.

This example works for me :

- name: support IDENTIFIED VIA/WITH
  community.mysql.mysql_user:
    name: telegraf
    priv: "*.*:USAGE"
    plugin: unix_socket
    login_unix_socket: /var/run/mysqld/mysqld.sock

Result :

MariaDB [mysql]> select host, user, password, plugin from user;
+-----------+------------------+-------------------------------------------+-------------+
| host      | user             | password                                  | plugin      |
+-----------+------------------+-------------------------------------------+-------------+
| localhost | telegraf         |                                           | unix_socket |
+-----------+------------------+-------------------------------------------+-------------+

[drzraf]

IIRC a leftover was that only IDENTIFIED WITH was implemented although depending on MariaDB vs MySQL, some version only supports IDENTIFIED VIA.

[Jorge-Rodriguez]

@drzraf can you provide the versions that support each idiom?

[drzraf]

https://wiki.debian.org/MySql

MySQL 5.5+ CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED WITH auth_socket;
MariaDB 5.2.0+ CREATE USER 'YOUR_SYSTEM_USER'@'localhost' IDENTIFIED VIA unix_socket;

About supporting both: I believe MySQL still doesn't support IDENTIFIED VIA.
MariaDB introduced the support for IDENTIFIED WITH at some point but I'm sorry I fear I can't tell at which version.

[hubiongithub]

Hello
I try to connect MariaDB to pam (https://mariadb.com/kb/en/authentication-plugin-pam) .
To use a specific pam service you can create a user like:

create user user@host IDENTIFIED WITH/VIA pam USING 'pam_service_name';

In a task like:
mysql_user:
name: user
plugin: "pam"

works, but then all pam modules regarding account/auth are questioned.
"pam USING <pam_service_name>" does obviously not work as plugin name, it is checked if the plugin given exists in SHOW PLUGINS;

So I would like to have a plugin_using: option in mysql_user which allows to specify options to plugins.

Regards

[Cardy165]

I have been hitting this exact issue, there is no way I can see to specify the using service name.

[hubiongithub]

Hello
we probably need an additional parameter "plugin_auth_service_string" which if set is added to the

create user user@host IDENTIFIED WITH/VIA pam USING 'pam_service_name';

I looked in modules/mysql_user.py and module_utils/user.py, and added an option for such a string.
Are there plugins using a password and also a USING clause?
pam does not use a password in this case (as it authenticates against an external service)

I tested it against a mariadb 10.5 with audit plugin installed so I can see what ansible does:

root,localhost,218,3822,QUERY,mysql,'CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'pam' USING 'pam_mysql' REQUIRE SSL',0

So my changes seems to work.
I looked also into the integration/targets but the only test with plugins uses mysql_native_password, I really know not enough how these tests are run to build one for a specific plugin.

I will try a push request, but that was a bit of a hassle last time :-)

[hubiongithub]

Pull Request: deleted

This may need a lot more changes to the parts that check existing user ... as mariadb stores the information about the "using" part in
IDENTIFIED WITH 'pam' USING 'pam_mysql'
in the authentication_string column, where other plugins stores their password hash.

and mysql and mariadb differ in Syntax, IDENTIFIED WITH 'auth_pam' BY 'pam_mysql' at mysql

I made a new PR #445. the changes are even smaller because I use the "plugin_auth_string" and check for the plugin used to do the "using" part only for pam as the code issues IDENTIFIED WITH plugin BY plugin_auth_string if both parameter are not empty.
So it should have worked for MySQL already, one probably did not try to set plugin_auth_string for PAM as it stores the password outside of mysql. As plugin = pam is MariaDB specific (I hope or is it distribution / OS dependent?) it should work.

But from this point on (last time 2/3 of the tests failed, which I assume for this changes also) I need help :-)

[hubiongithub]

Hello again
new plugin, same problem, if I user ed25519 as plugin for mariadb I get
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY '...passwordstring...'
I tried this:
main...hubiongithub:community.mysql:feature_ed25519_for_mariadb

but I think it is better to test wether mariadb or mysql is used and follow different code path accordingly.
But the whole fork/pr/Run failed at startup: Docker Image CI mariadb-py310-mysqlclient211 - main (9439282) mails
is to much for me, I'm not a coder and the overhead of github and tests ... I don't have the will to push through it.
I just brought my fork up to the level of the original repo and got about 20+ mails of stuff failed, no chance to correct that.
I can only suggest what to change so mariadb is happy to accept the SQL, but that's it.

[hubiongithub]

Hello again

new plugin, same problem, if I user ed25519 as plugin for mariadb I get You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BY '...passwordstring...' I tried this: main...hubiongithub:community.mysql:feature_ed25519_for_mariadb

That is this code:

# Mysql and MariaDB differ in naming pam plugin and syntax to set it
                    if plugin == 'pam':
                        query_with_args = "ALTER USER %s@%s IDENTIFIED WITH %s USING %s", (user, host, plugin, plugin_auth_string)
+                   # Mysql has caching_sha2_password, mariadb uses ed25519 for stronger password hashing    
+                  elif plugin == 'ed25519':
+                     query_with_args = "ALTER USER %s@%s IDENTIFIED WITH %s USING %s", (user, host, plugin, plugin_auth_string)    
                    else:
                        query_with_args = "ALTER USER %s@%s IDENTIFIED WITH %s BY %s", (user, host, plugin, plugin_auth_string)

Any chance to build this into mysql.user?

[Andersson007]

@hubiongithub hello, would you like to submit a PR? here's a quick-start guide. Apologies for not replying here -i don't use it- but would be happy to take a look at the PR
Please let me know if you have any question or have no time to add it yourself

[Andersson007]

@hubiongithub i don't see any difference with the first query, so, instead of adding the lines, i would just change the first if condition to:

if plugin in ('pam', 'ed25519'):
    query_with_args = "ALTER USER %s@%s IDENTIFIED WITH %s USING %s", (user, host, plugin, plugin_auth_string)

[Andersson007]

@hubiongithub last chance for you to pick it up ^, otherwise i'll put the help_wanted and easy fix labels:)

[Andersson007]

added myself #619, fyi

[Andersson007]

not sure it #619 solved the issue but closing. Feel free to re-open or create another one instead. thanks everyone!

[hubiongithub]

Hello
Sorry for all the delay, hadn't time and option to look into this. My inital piece of code only mentioned the "update user part", I now tried to create a user with plugin ed25519, which breaks with mariadb syntax error.

Line 214-216 in v3.10.0 probably need a change analog to your change #619

        # Mysql and MariaDB differ in naming pam plugin and Syntax to set it
        if plugin == 'pam':  # Used by MariaDB which requires the USING keyword, not BY
            query_with_args = "CREATE USER %s@%s IDENTIFIED WITH %s USING %s", (user, host, plugin, plugin_auth_string)

As there is no reopen button for me, and its more complicated, I will open a new issue

[Andersson007]

@hubiongithub thanks for reporting! Here's the PR