change_root_pwd reports success but does not result in desired change

[dataondeck]

Hey Eslam,

So I am working on some small changes to get the module working with MySQL 8 and in doing so I was testing with MariaDB as you have used in your samples as a point of reference.

I have always used MySQL in my work so I am not familiar with MariaDB and may be misunderstanding some of the differences but:

On Ubuntu 20.04 with installing mariadb-server version installed is:

mariadb Ver 15.1 Distrib 10.3.22-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Running module task as is in sample playbook produces:

TASK [mysql : debug] **********************************************************************************************************************************************************************************************
ok: [] => {
    "secure": {
        "changed": true, 
        "failed": false, 
        "meta": {
            "change_root_pwd": 1, 
            "disallow_root_remotely": 0, 
            "hosts_failed": [
                "127.0.0.1", 
                "::1"
            ], 
            "hosts_success": [
                "localhost"
            ], 
            "remove_anonymous_user": 0, 
            "remove_test_db": 0, 
            "stdout": "Password for user: root @ Hosts: ['localhost'] changed to the desired state"
        }
    }
}

The failing of "127.0.0.1" and "::1" is understandable as there are no entries for those hosts upon install. But still running:

$ mariadb -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

with set password.

However as root:

# mariadb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 54
Server version: 10.3.22-MariaDB-1ubuntu1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

passwordless login is still allowed

It appears at least in my case it is due to the default plugin for MariaDB installations being 'unix_socket'.

As referenced from the top answer here:

https://stackoverflow.com/questions/44298160/mysql-mariadb-10-0-29-set-root-password-but-still-can-login-without-asking-p

MariaDB [(none)]> select user, host, password from mysql.user where user='root';
+------+-----------+-------------------------------------------+
| user | host      | password                                  |
+------+-----------+-------------------------------------------+
| root | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |
+------+-----------+-------------------------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> select user, host, password, plugin from mysql.user where user='root';
+------+-----------+-------------------------------------------+-------------+
| user | host      | password                                  | plugin      |
+------+-----------+-------------------------------------------+-------------+
| root | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 | unix_socket |
+------+-----------+-------------------------------------------+-------------+
1 row in set (0.000 sec)

MariaDB [(none)]> update mysql.user set plugin='' where user='root' and host='localhost';
Query OK, 1 row affected (0.001 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [(none)]> select user, host, password, plugin from mysql.user where user='root';
+------+-----------+-------------------------------------------+--------+
| user | host      | password                                  | plugin |
+------+-----------+-------------------------------------------+--------+
| root | localhost | *94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29 |        |
+------+-----------+-------------------------------------------+--------+
1 row in set (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

# mariadb
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

$ mariadb -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 59
Server version: 10.3.22-MariaDB-1ubuntu1 Ubuntu 20.04

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

So it seems that at least on Ubuntu 20.04 in order for the module to have the desired effect (having a root password set to be used for logging in) the above plugin changing will have to be added. I can work on this in addition to the MySQL 8 changes but I wanted to check with you on thoughts as it may have to be an OS based option i.e. Debian/Ubuntu only.

Forgive me if I am missing something as this is my first time working with the underlying code of Ansible modules and also MariaDB so I could be way off.

[dataondeck]

Closing this because I realize now unix_socket in MariaDB is the same as auth_socket in MySQL which is also the default plugin however my workaround for the PASSWORD function being removed in MySQL 8 was to run ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'yourpasswd'; which caused the plugin to be changed to caching_sha2_password and MariaDB being open source it seems they have not removed the PASSWORD function and still use it in their version of mysql_secure_installation as UPDATE mysql.user SET Password=PASSWORD('$esc_pass') WHERE User='root';.

However as MySQL is not open source I cannot find the source for how they set the root password during mysql_secure_installation without causing the plugin to be changed from auth_socket.

[eslam-gomaa]

hello @dataondeck ,

I'd go with this fix for now ,, source (The source you provided)

# remove plugin
UPDATE mysql.user SET plugin = '' WHERE user = root;

# Test, unix_socket show be gone
SELECT host, user, password, plugin FROM mysql.user where User='root' LIMIT 0,1;

while I'm not sure if it's a mistake to remove the unix_socket plugin or not but I see that the unix_socket does not come by default with older mysql version (tested on CentOS 7)

---

You can add these lines to your code

cursor.execute("SELECT host, user, password, plugin FROM mysql.user where User='{}' LIMIT 0,1;".format(user))
            socket_exists = cursor.fetchall()
            if 'unix_socket' in list(chain.from_iterable(socket_exists)):
                cursor.execute("UPDATE mysql.user SET plugin = '' WHERE user = {};".format(user))

---

Test Result

(After running the Module)

Thank you for letting me know

[dataondeck]

Eslam,

From what I have read since I originally closed the issue it is not recommended to disable unix_socket/auth_socket and change to password auth by default but that workaround above was the best approach for doing so if needed. Without being able to see how MySQL does it during mysql_secure_installation (since the function used in the MariaDB version is deprecated in MySQL 8 and not being able to find source code for MySQL 8 mysql_secure_installation) I will have to do more digging to find out how it is being done without removing unix_socket/auth_socket.

The way I was approaching it in my changes was by adding a new option that can be set to use the correct function for MySQL 8 that will not result in getting an 'SQL syntax' error:

mysql_version_8:
        description:
            - Is this MySQL 8?
        default: False
        type: bool

And then the option is checked for:

if change_root_password:
                pwd = {}
                for host in hosts:
                    cursor.execute('use mysql;')
                    if mysql_version_8:
                        cursor.execute(
                        "alter user '{}'@'{}' IDENTIFIED WITH caching_sha2_password BY '{}';".format(user, host,
                                                                                                      new_password))
                    else:
                        cursor.execute('update user set password=PASSWORD("{}") where User="{}" AND Host="{}";'.format(new_password, user, 
                                                                                                                                    host))                                                                                                      
                    cursor.execute('flush privileges;')

                    if mysql_version_8:
                        cursor.execute('select user, host, authentication_string from mysql.user where user="{}";'.format(user))
                    else:
                        cursor.execute('select user, host, password from mysql.user where user="{}";'.format(user))

BUT again using that caching_sha2_password command will cause password auth to be default so I think the best thing to do for now is to revert the changes you made adding in the workarounds or make it as an option as I have done in my fork until I can figure out the way it is done by MySQL 8 mysql_secure_installation.

Sorry for the confusion! I return home from vacation on Monday and will dig into it more then.

Thanks

[eslam-gomaa]

Closing the issue