Some say that the best password is the one you don’t have to remember. That’s possible with MySQL, thanks to the auth_socket plugin and its MariaDB version unix_socket.
Neither of these plugins is new, and some words have been written about the auth_socket on this blog before, for example: how to change passwords in MySQL 5.7 when using plugin: auth_socket. But while reviewing what’s new with MariaDB 10.4, I saw that the unix_socket now comes installed by default and is one of the authentication methods (one of them because in MariaDB 10.4 a single user can have more than one authentication plugin, as explained in the Authentication from MariaDB 10.4 document).
As already mentioned this is not news, and even when one installs MySQL using the .deb packages maintained by the Debian team, the root user is created so it uses the socket authentication. This is true for both MySQL and MariaDB:
1 2 3 | root@app:~# apt-cache show mysql-server-5.7 | grep -i maintainers Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org> Original-Maintainer: Debian MySQL Maintainers <<a href="mailto:[email protected]">pkg-mysql-maint@lists.alioth.debian.org</a>> |
Using the Debian packages of MySQL, the root is authenticated as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 | root@app:~# whoami root= root@app:~# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.27-0ubuntu0.16.04.1 (Ubuntu) Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user = 'root'; +------+-----------+-------------+-----------------------+ | user | host | plugin | authentication_string | +------+-----------+-------------+-----------------------+ | root | localhost | auth_socket | | +------+-----------+-------------+-----------------------+ 1 row in set (0.01 sec) |
Same for the MariaDB .deb package:
1 2 3 4 5 6 7 8 9 10 | 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04 MariaDB [(none)]> show grants; +------------------------------------------------------------------------------------------------+ | Grants for root@localhost | +------------------------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION | | GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION | +------------------------------------------------------------------------------------------------+ 2 rows in set (0.00 sec) |
For Percona Server, the .deb packages from the official Percona Repo are also setting the root user authentication to auth_socket. Here is an example of Percona Server for MySQL 8.0.16-7 and Ubuntu 16.04:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 | root@app:~# whoami root root@app:~# mysql Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 8.0.16-7 Percona Server (GPL), Release '7', Revision '613e312' Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user ='root'; +------+-----------+-------------+-----------------------+ | user | host | plugin | authentication_string | +------+-----------+-------------+-----------------------+ | root | localhost | auth_socket | | +------+-----------+-------------+-----------------------+ 1 row in set (0.00 sec) |
So, what’s the magic? The plugin checks that the Linux user matches the MySQL user using the SO_PEERCRED socket option to obtain information about the user running the client program. Thus, the plugin can be used only on systems that support the SO_PEERCRED option, such as Linux. The SO_PEERCRED socket option allows retrieving the uid of the process that is connected to the socket. It is then able to get the user name associated with that uid.
Here’s an example with the user “vagrant”:
1 2 3 4 | vagrant@mysql1:~$ whoami vagrant vagrant@mysql1:~$ mysql ERROR 1698 (28000): Access denied for user 'vagrant'@'localhost' |
Since no user “vagrant” exists in MySQL, the access is denied. Let’s create the user and try again:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 | MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket; Query OK, 0 rows affected (0.00 sec) vagrant@mysql1:~$ mysql Welcome to the MariaDB monitor. Commands end with ; or g. Your MariaDB connection id is 45 Server version: 10.0.38-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.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)]> show grants; +---------------------------------------------------------------------------------+ | Grants for vagrant@localhost | +---------------------------------------------------------------------------------+ | GRANT ALL PRIVILEGES ON *.* TO 'vagrant'@'localhost' IDENTIFIED VIA unix_socket | +---------------------------------------------------------------------------------+ 1 row in set (0.00 sec) |
Success!
Now, what about on a non-debian distro, where this is not the default? Let’s try it on Percona Server for MySQL 8 installed on a CentOS 7:
1 2 3 4 5 6 7 8 9 10 | mysql> show variables like '%version%comment'; +-----------------+---------------------------------------------------+ | Variable_name | Value | +-----------------+---------------------------------------------------+ | version_comment | Percona Server (GPL), Release 7, Revision 613e312 | +-----------------+---------------------------------------------------+ 1 row in set (0.01 sec) mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket; ERROR 1524 (HY000): Plugin 'auth_socket' is not loaded |
Failed. What is missing? The plugin is not loaded:
1 2 3 4 | mysql> pager grep socket PAGER set to 'grep socket' mysql> show plugins; 47 rows in set (0.00 sec) |
Let’s add the plugin in runtime:
1 2 3 4 5 6 7 8 9 | mysql> nopager PAGER set to stdout mysql> INSTALL PLUGIN auth_socket SONAME 'auth_socket.so'; Query OK, 0 rows affected (0.00 sec) mysql> pager grep socket; show plugins; PAGER set to 'grep socket' | auth_socket | ACTIVE | AUTHENTICATION | auth_socket.so | GPL | 48 rows in set (0.00 sec) |
We got all we need now. Let’s try again:
1 2 3 4 | mysql> CREATE USER 'percona'@'localhost' IDENTIFIED WITH auth_socket; Query OK, 0 rows affected (0.01 sec) mysql> GRANT ALL PRIVILEGES ON *.* TO 'percona'@'localhost'; Query OK, 0 rows affected (0.01 sec) |
And now we can log in as the OS user “percona”.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | [percona@ip-192-168-1-111 ~]$ whoami percona [percona@ip-192-168-1-111 ~]$ mysql -upercona Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 19 Server version: 8.0.16-7 Percona Server (GPL), Release 7, Revision 613e312 Copyright (c) 2009-2019 Percona LLC and/or its affiliates Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> select user, host, plugin, authentication_string from mysql.user where user ='percona'; +---------+-----------+-------------+-----------------------+ | user | host | plugin | authentication_string | +---------+-----------+-------------+-----------------------+ | percona | localhost | auth_socket | | +---------+-----------+-------------+-----------------------+ 1 row in set (0.00 sec) |
Success again!
Question: Can I try to log as the user percona from another user?
1 2 3 | [percona@ip-192-168-1-111 ~]$ logout [root@ip-192-168-1-111 ~]# mysql -upercona ERROR 1698 (28000): Access denied for user 'percona'@'localhost' |
No, you can’t.
Conclusion
MySQL is flexible enough in several aspects, one being the authentication methods. As we see in this post, one can achieve access without passwords by relying on OS users. This is helpful in several scenarios, but just to mention one: when migrating from RDS/Aurora to regular MySQL and using IAM Database Authentication to keep getting access without using passwords.
Cool!
But I’m not sure about the concept of UNIX users as SQL users.
For the most part, I think of UNIX users as real human users, whereas I think of MySQL users more as roles. I typically set up MySQL users as (for example) browsers (SELECT only) editors (SELECT, INSERT, DELETE, UPDATE), and admins (do anything).
It seems to me that user authentication happens at the application level, and role authentication takes place between the application and the database.
Or perhaps I completely miss what you were demonstrating.
There isn’t that much changing compared to what you are describing: in your application you would still be required to use normal authentication. What Daniel is showing here is that in addition to the old scheme you can also use the Unix socket authentication. This means you will have just another way of authenticating of your Unix users than to put the password in some .my.cnf or mylogin.cnf. It’s basically saying you trust your Unix logins well enough to provide them passwordless access to MySQL, so your analogy of seeing Unix users as real human users is quite spot on.
Back when MariaDB announced to enable this by default for the root user in 10.4 I wrote a lengthy reply. I have no problem with people enabling this in a safe and confined environment, however I’m not convinced that enabling this _by default_ is a good idea as the majority installations will not be performed by sysadmins, security experts or DBAs. But I think this is a bit too off topic here.
My database server is on a non-routable network (10.*.*.*). It is accessed by my web server via the same network, which also has a state IP facing the Internet.
I can access my LAN and MySQL via VPN, but otherwise, would you think this is a “safe and confined environment?”