Ansible: 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: https://github.com/ansible/ansible-modules-core/pull/3587#issuecomment-227364601
• @wouteroostervld added that you have to change the plugin and password in one action, to avoid exposure: https://github.com/ansible/ansible-modules-core/issues/3003#issuecomment-256328101

NOTES

• Issue migrated from https://github.com/ansible/ansible-modules-core/issues/3003
• Releated PR: https://github.com/ansible/ansible-modules-core/pull/3587

I changed a few details:

• @verwilst mentioned that you can use either a password or auth_socket not both: https://github.com/ansible/ansible-modules-core/issues/3003#issuecomment-199799891
• 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 https://github.com/geerlingguy/ansible-role-mysql/issues/60#issuecomment-144904958

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.

[bmalynovytch]

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

Yes, sadly

[BarbzYHOOL]

Ok but how do I pull the module? it's a PR for the entire ansible project @bmalynovytch

[bmalynovytch]

In the PR, click "Files changed", then click "View file" on the mysql_user.py file, then click "Raw" to get the link to the file at the right version: https://raw.githubusercontent.com/ansible/ansible/402ca8c06ab3d26b4cec8ab5fd9ddd64deffcaee/lib/ansible/modules/database/mysql/mysql_user.py

[BarbzYHOOL]

So I just put this file in a "library" directory?

[bmalynovytch]

Yes

[BarbzYHOOL]

ok so I have this

/playbook.yml
/library/mysql_user.yml

Now I run this task in a role but it still prints the same error:

- name: Create root user for local network
  mysql_user:
    login_user: root
    name: "root"
    # host_all: true
    host: "192.168.0.%"
    priv: "*.*:ALL"
    state: present
    check_implicit_admin: true

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

edit: i can move this to the PR page if u prefer

[bmalynovytch]

Not necessary as it is probably unrelated

[BarbzYHOOL]

well it doesn't seem to work for that issue

[bmalynovytch]

Got it !
Try setting become: true and become_user: root.
The connection can only work if you run the connection being root (system, not db user).

[BarbzYHOOL]

Same thing (I put the task directly in the playbook instead)

[oscherler]

Here is my post. Give it a try, and comment there if you’re still experiencing problems.

Changing the MySQL root User from auth_socket to Password Authentication Using Ansible

[bmalynovytch]

Ok, so this is weird: the error says you connect to localhost, which means that you used the socket to connect (MySQL internal transforms localhost in use the socket, and not use network on 127.0.0.1), without password, which is what you want.

@BarbzYHOOL could you run it again, with become: true and become_user: root and -vvv ?
@oscherler using shell commands as a workaround shouldn't be the way to go

[BarbzYHOOL]

The full traceback is:
WARNING: The below traceback may *not* be related to the actual failure.
  File "/tmp/ansible_mysql_user_payload_IykDl4/__main__.py", line 648, in main
    connect_timeout=connect_timeout)
  File "/tmp/ansible_mysql_user_payload_IykDl4/ansible_mysql_user_payload.zip/ansible/module_utils/mysql.py", line 76, in mysql_connect
    db_connection = mysql_driver.connect(**config)
  File "/usr/lib/python2.7/dist-packages/pymysql/__init__.py", line 90, in Connect
    return Connection(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 699, in __init__
    self.connect()
  File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 928, in connect
    self._request_authentication()
  File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1148, in _request_authentication
    auth_packet = self._read_packet()
  File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 1010, in _read_packet
    packet.check_error()
  File "/usr/lib/python2.7/dist-packages/pymysql/connections.py", line 393, in check_error
    err.raise_mysql_exception(self._data)
  File "/usr/lib/python2.7/dist-packages/pymysql/err.py", line 107, in raise_mysql_exception
    raise errorclass(errno, errval)

fatal: [HOST]: FAILED! => changed=false 
  invocation:
    module_args:
      append_privs: false
      check_implicit_admin: true
      config_file: /root/.my.cnf
      connect_timeout: 30
      encrypted: false
      host: 192.168.0.%
      host_all: false
      login_host: localhost
      login_password: null
      login_port: 3306
      login_unix_socket: null
      login_user: root
      name: root
      password: null
      priv: '*.*:ALL'
      sql_log_bin: true
      ssl_ca: null
      ssl_cert: null
      ssl_key: null
      state: present
      update_password: always
      user: root
  msg: 'unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1045, u"Access denied for user ''root''@''localhost'' (using password: NO)")'

@oscherler ok thank you, that looks very complex for just one thing, I have a similar solution with shell so if I am forced to go this way I will compare with your tutorial

[bmalynovytch]

Ok, you should then set:

      login_unix_socket: /path/to/your/mysql/socket

[BarbzYHOOL]

lol @bmalynovytch

 invocation:
    module_args:
      append_privs: false
      check_implicit_admin: true
      config_file: /root/.my.cnf
      connect_timeout: 30
      encrypted: false
      host: 192.168.0.%
      host_all: false
      login_host: localhost
      login_password: null
      login_port: 3306
      login_unix_socket: /var/run/mysqld/mysqld.sock
      login_user: root
      name: root
      password: null
      priv: '*.*:ALL'
      sql_log_bin: true
      ssl_ca: null
      ssl_cert: null
      ssl_key: null
      state: present
      update_password: always
      user: root
  msg: 'unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1045, u"Access denied for user ''root''@''localhost'' (using password: NO)")'

[bmalynovytch]

Ok, new try (a bit cumbersome):

- name: Create root user for local network
   mysql_user:
     login_user: root

     login_host: "{{ omit }}"
     login_password: "{{ omit }}"
     login_port: "{{ omit }}"
     login_unix_socket: /var/run/mysqld/mysqld.sock

     name: "root"
    # host_all: true
     host: "192.168.0.%"
     priv: "*.*:ALL"
     state: present

[BarbzYHOOL]

EDIT: WAIT I probably made a mistake while trying to solve this shit for 3 days, I retry in 3 min

[bmalynovytch]

Ok, definitely, there's something wrong.
Can you confirm that the version you used for your last tests is the one pulled from #45355 ?
I think you gave enough feedback to help fix it.
I suppose it is somewhere at the time we check the connection, we don't provide settings properly.
(assertion waiting your confirmations :) )

[BarbzYHOOL]

ok my bad, so I purged and reinstalled percona like 5 times today to test different stuff and see how it worked manually and with ansible, and when I tried your plugin I had set a password to root and forgot about it so I verified because you can't suggest me 4 things and be wrong, it must be my installation.

So yes, now it works with your plugin!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!

TASK [percona_server : Create root user for local network] *************************************************************************************
changed: [H2_CT112] => changed=true 
  msg: User added
  user: root

I'll answer on the PR if you want, I need to log out for now and will finish tomorrow but it seems like all is good!

[bmalynovytch]

Great 😄

[BarbzYHOOL]

@bmalynovytch Your PR allows to change the password (i made sure to retry) but my original issue was because I had not set login_unix_socket: /var/run/mysqld/mysqld.sock since I didn't know what it was for.

Why isn't this the default to avoid this stupid waste of time? wouldn't you mind adding it to your PR?

In the end, I think the issue here is already fixed by the "login_unix_socket" parameter. I wanted to improve the documentation on https://docs.ansible.com/ansible/latest/modules/mysql_user_module.html by editing this line The path to a Unix domain socket for local connections. but I don't find it in the file when I click on "Edit on github" (I would even say this button is pure shit)

[bmalynovytch]

@BarbzYHOOL Thank you for the update.
I suggest you create a new issue.
You're right in the fact that we should be clearer on the reason why we didn't manage to authenticate, when using localhost, as it should be using the unix socket.

[BarbzYHOOL]

(it's about this issue, so it would be kind of a duplicate)

[bmalynovytch]

Right now, this issue's title and description are misleading.
I suggest you update them so that contributors don't need to read the entire thread to understand what's going on.

[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