Add plugin as parameter for mysql_user, to allow use of auth_socket plugin

[pgrenaud]

ISSUE TYPE

Feature Idea

COMPONENT NAME

mysql_user

ANSIBLE VERSION

ansible 2.3.0.0
  config file =
  configured module search path = Default w/o overrides

CONFIGURATION

Default configuration

OS / ENVIRONMENT

N/A

SUMMARY

From the MySQL 5.7 what's new page:

The server now requires account rows in the mysql.user table to have a nonempty plugin column value and disables accounts with an empty value.

On a fresh installation of MySQL 5.7 the root user for host localhost is now configured with a plugin value of auth_socket. This prevents users other then root to connect as root using the mysqlclient utility:

user@server$ mysql -u root -pP4ssw0rd
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

While as root this works:

root@server# mysql

Currently, this cannot be done with Ansible.

References:

• https://dev.mysql.com/doc/refman/5.7/en/socket-pluggable-authentication.html
• https://www.slideshare.net/ottokekalainen/passwordless-login-with-unix-authsocket

STEPS TO REPRODUCE

It would be nice to introduce a new parameter to this module which accepts all possible plugin values (defaulting to mysql_native_password) , e.g.:

mysql_user: name=root password=abc123 plugin=mysql_native_password
mysql_user: name=root plugin=auth_socket

EXPECTED RESULTS

N/A

ACTUAL RESULTS

N/A

CHALLENGES

• @oscherler noted that the syntax varies between MySQL and MariaDB: add support for auth_socket ansible-modules-core#3587 (comment)
• @wouteroostervld added that you have to change the plugin and password in one action, to avoid exposure: Add plugin as parameter for mysql_user  ansible-modules-core#3003 (comment)

NOTES

• Issue migrated from Add plugin as parameter for mysql_user  ansible-modules-core#3003
• Releated PR: add support for auth_socket ansible-modules-core#3587

I changed a few details:

• @verwilst mentioned that you can use either a password or auth_socket not both: Add plugin as parameter for mysql_user  ansible-modules-core#3003 (comment)
• I updated the format to follow the latest one
• I updated the ansible version output, even if not very usefull in this case
• I also added some references and a challenges section

[ansibot]

cc @Jmainguy
click here for bot help

[guidodobboletta]

Would be nice to get this feature as this would also be useful for people trying to add IAM authentication to RDS databases on AWS.

[BarbzYHOOL]

Is there a workaround to this? I don't know how to change root password because of this o_0

[ansibot]

cc @Xyon @bmalynovytch @dagwieers @michaelcoburn @Oneiroi @tolland
click here for bot help

[oscherler]

@BarbzYHOOL There is. First, do you really need to set a root password? You can use MySQL as root without a password using sudo mysql, which is what’s nice about the auth_socket plugin. If you’re used to using the root user with projects you are developing (e.g. in Vagrant), you can create another user instead.

If you really want to set up a root user with a password using Ansible, you can:

• Create a MySQL user with sufficient privileges for use with Ansible
• Check the root plugin by selecting from mysql.user
• If it’s not mysql_native_password, delete the root user and recreate it.

[BarbzYHOOL]

@oscherler I can't use the "mysql_user" module if I don't set any password (on a clean install, I didn't run a security script afterwards)

- name: Create user for databases "auth"
  mysql_user:
    name: "testou"
    password: "testou"
    priv: "*.*:ALL"
    state: "present"
    append_privs: no

  msg: 'unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, u"Access denied for user ''root''@''localhost''")'

Also I wonder if the auth_socket thing will allow me to connect to mysql from another local machine (virtual machine), as root.

[BarbzYHOOL]

In fact, I can't even create any user with ansible because of this on mysql 5.7.

I just can't connect to mysql because it can only connects through root and I don't connect to my server with the root account (who does that?). I also tried to add "become: yes" to the role but it didn't do anything

[oscherler]

@BarbzYHOOL I’m writing a dev.to post about how to do it. Should be ready tomorrow.

[bmalynovytch]

@BarbzYHOOL you need to set login_user and login_password or check_implicit_admin
See https://docs.ansible.com/ansible/latest/modules/mysql_user_module.html

[BarbzYHOOL]

you need to set login_user and login_password or check_implicit_admin

I install mysql, then i run a task with mysql_user module and it can't connect:

msg: 'unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1698, u"Access denied for user ''root''@''localhost''")

If i don't set any user, it uses by default root. I can't set any password since root has no password

+---------------+-----------+------------------------------------------------------------------------------------+-----------------------+
| User          | Host      | HEX(authentication_string)                                                         | plugin                |
+---------------+-----------+------------------------------------------------------------------------------------+-----------------------+
| root          | localhost |                                                                                    | auth_socket   

So basically I'm stuck there. However if I run the shell module I can run mysql as root (but not with mysql modules... makes no sense to me) and execute queries

There is a solution, use debconf to set the root password during install though but I'm surprised to have to do this annoying thing

[bmalynovytch]

Ok, thank you for the update, it's clearer now.
Could you give a try with #45355 ?
This PR is still not merged (needing more reviews and shipit commands) but adds authentication_string column management, which is not the case to the current module (not able to use recent versions of MySQL and MariaDB)

[BarbzYHOOL]

This seems related geerlingguy/ansible-role-mysql#60 (comment)

Btw your PR speaks about MariaDB, note that I use percona-server-server-5.7

I have never built ansible from scratch, never tried any PR here, not sure how to proceed (the docs look huge about this).

You mean the actual plugin uses the "password" column instead of "authentication_string" ??

[bmalynovytch]

The PR is related to MySQL and MariaDB, and probably also to Percona-server.

To try the module, just pull it in a library folder next to your playbook, it'll override the one in your current version of Ansible.

[ansibot]

cc @bmildren
click here for bot help

[wouteroostervld]

The client trying unix-socket if you specify 'localhost' instead of '::1' or '127.0.0.1' as host is a peculiarity of MySQL not an issue with ansible itself. But maybe because this isn't a well known fact a hint in documentation (eg. an example) would be nice to have.

[ansibot]

cc @Alexander198961
click here for bot help

[ansibot]

cc @Andersson007
click here for bot help

[oscherler]

@wouteroostervld The problem is that you cannot set a password for root%localhost using Ansible on recent version of MySQL, because the mysql_user module doesn’t support auth plugins, and nowadays root%localhost uses the auth_socket plugin by default.

[wouteroostervld]

@wouteroostervld The problem is that you cannot set a password for root%localhost using Ansible on recent version of MySQL, because the mysql_user module doesn’t support auth plugins, and nowadays root%localhost uses the auth_socket plugin by default.

Well it does support auth_plugins in the way that you could change from auth_socket to auth_native_password by setting a password for a user. (It did )

[wouteroostervld]

ah-sh*t-here-we-go-again.gif: updating ansible checkout, firing up spacemacs and installing MySQL5.7 (in a docker possibly).

(If true this is a regression. It worked. Maybe it still works but misses an example in documentation. It's tricky first you need to connect without password and the next time with. So after you set rootpw you should create ~/root/my.cnf immediately.

Something like:

- name: install /root/.my.cnf
  template:
    dest: "/root/.my.cnf"
    src: "{{ role_path }}/templates/my-root.cnf.j2"

With as template:

[mysql]
host=localhost
password="{{ mysqld_root_password }}"
user=root

)

[abohne]

It seems like the root password use case has been solved, but it doesn't address the original issue reported. It would certainly be helpful to be able to specify an auth plugin for a user as a parameter.

For example, creating a user in AWS RDS w/ IAM authentication the query is:
CREATE USER jane_doe IDENTIFIED WITH AWSAuthenticationPlugin AS 'RDS';

[kepi]

Well it does support auth_plugins in the way that you could change from auth_socket to auth_native_password by setting a password for a user. (It did )

I don't believe this is the case, at least not with stable version of ansible. Yes, password changes but plugin field remains as unix_socket and you can't login with that password.

[ansibot]

cc @kurtdavis
click here for bot help

[ansibot]

cc @tomaszkiewicz
click here for bot help

[pgrenaud]

It seems that this feature was implemented in #65789. Not able to test, since it's in 2.10, which is not released yet.

[Andersson007]

@pgrenaud we'll be waiting for your feedback right after release, thanks!

[oscherler]

@pgrenaud It doesn’t look like #65789 is supporting all the mess of different syntaxes between different versions of MariaDB and MySQL (this mess being the reason I stopped working on my pull request: testing on 13 different database versions was getting tiresome).

[pgrenaud]

@oscherler Yeah, I get that. In my case, I'm working with MySQL 5.7 and now trying to update to MySQL 8.0. I do not work with MariaDB, so I can't tell. To be frank, it's been so long since I've created this issue that I don't remember why I needed the plugin parameter. 😅

[oscherler]

For me it was because I wanted to set a password for root, to reproduce a set-up we had on our previous internal development server, and the default on Ubuntu had become to have root using the auth_socket plugin.

In retrospect, it would have been so much easier to just use another user and change the name in the config files of the 200+ web sites we were developing as needed. 🤣

[pgrenaud]

Thinking about it, I think it was to do the opposite of your use case. I wanted a way to set the auth_socket plugin for other users, in order to completely eliminate the need to manage database password. Man, I already eager to try that! 🤩

[ansibot]

Thank you very much for your interest in Ansible. Ansible has migrated much of the content into separate repositories to allow for more rapid, independent development. We are closing this issue/PR because this content has been moved to one or more collection repositories.

• lib/ansible/modules/database/mysql/mysql_user.py -> https://galaxy.ansible.com/community/mysql

For further information, please see:
https://github.com/ansible/ansibullbot/blob/master/docs/collection_migration.md