MySQL has some initial support for passwordless socket authentication, but based on by very brief and initial testing, it does not seem to be ready for prime time yet. If you want socket authentication, PostgreSQL does socket authentication out of the box.
This article describes some tests on MySQL socket authentication and a configuration that does not work.
This article describes a configuration that DOES NOT WORK and is also NOT SECURE.
Goal
MySQL seems to support socket authentication. You can connect without a password when database user is the same as your user.
When you leave MySQL 5.7 root password blank, socket authentication is enabled. You can use database root with just
$ sudo mysql
or maybe even your normal database user
$ mysql
Background
MySQL database management system has its own users unrelated to Linux system users. Storing additional set of passwords and then changing them when forgotten can cumbersome. On the other hand, this socket authentication configuration is quite new.
How do you authenticate your PHP applications with socket authentication? Depending on your setup, it could be easier with project users and more difficult with multiple projects on a single Linux user.
Obviously, you should always protect your database server with a firewall, ‘sudo ufw allow 22/tcp; sudo ufw enable’.
When you install mysql-server on Ubuntu 16.04 LTS, it includes /usr/share/doc/mysql-server-core-5.7/NEWS.Debian.gz. According to that
* Password behaviour when the MySQL root password is empty has
changed. Packaging now enables socket authentication when the MySQL root password is empty. This means that a non-root user can’t log in as the MySQL root user with an empty password. The new logic is as follows:
– The auth_socket plugin will be installed automatically only if it
is to be activated for the root user.
– The auth_socket plugin will be activated for the root user:
+ If you had a database before with an empty root password.
+ If you create a new database with an empty root password.
– The auth_socket plugin will NOT be activated for the root user:
+ If you had a database before with a root password set.
+ If you create a new database with a root password set.
– The auth_socket plugin will NOT be activated for any other user.
– If you do not want the new behaviour, set the MySQL root password
to be non-empty.
Installing MySQL for Socket Authentication
On Ubuntu 16.04 LTS, install mysql-server but leave the password blank.
$ sudo apt-get update $ sudo apt-get -y install mysql-server mysql-client
The installation asks for the database password multiple times. The text recommends setting a password. Ignore that and press enter to set an empty root password.
Testing Socket Authentication
Can we log in without a password?
$ sudo mysql ... Server version: 5.7.20-0ubuntu0.16.04.1 (Ubuntu) mysql> CREATE DATABASE hellotero; Query OK, 1 row affected (0.00 sec)
So yes, we can access the database without a password.
Can we log in with an empty password? (Hope not)
$ mysql -u root --password='' mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1698 (28000): Access denied for user 'root'@'localhost'
It seems that we can’t log in with an empty password.
What about user access? Are database users created magically?
$ whoami tero $ mysql ERROR 1045 (28000): Access denied for user 'tero'@'localhost' (using password: NO)
No, database users still have to be created.
$ sudo mysql mysql> CREATE DATABASE tero; Query OK, 1 row affected (0.00 sec) mysql> CREATE USER tero@localhost; Query OK, 0 rows affected (0.00 sec) mysql> GRANT ALL ON tero.* TO tero@localhost; Query OK, 0 rows affected (0.00 sec) mysql> exit
Now tero can log in and create tables in his own database
$ whoami tero $ mysql mysql> USE tero; Database changed mysql> CREATE TABLE animals (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(160)); Query OK, 0 rows affected (0.00 sec)
But, as tero is not database root, he can’t create new databases
mysql> CREATE DATABASE noicant; ERROR 1044 (42000): Access denied for user 'tero'@'localhost' to database 'noicant'
Let’s try if a different user can log in with an empty password
$ whoami vagrant $ mysql -u tero --password='' mysql: [Warning] Using a password on the command line interface can be insecure. mysql> USE tero; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> DROP TABLE animals; Query OK, 0 rows affected (0.01 sec)
That’s a different behavior from database root socket login. Database user ‘tero’ has an empty password, and it allows any user to log in and destroy data!
Maybe we should add a password for tero or create the user again.
$ sudo mysql mysql> SELECT User FROM mysql.user; +------------------+ | User | +------------------+ | debian-sys-maint | | mysql.session | | mysql.sys | | root | | tero | +------------------+ 5 rows in set (0.00 sec) mysql> DROP USER tero@localhost; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER 'tero@localhost' IDENTIFIED BY 'k3nv89qwljuihljNEAAvg/&2JF(G'; Query OK, 0 rows affected (0.00 sec)
Let’s test the user with the password and existing non-password logins.
$ sudo mysql mysql> quit $ whoami tero $ mysql -p Enter password: ERROR 1045 (28000): Access denied for user 'tero'@'localhost' (using password: YES)
It seems that password login does not work.
Conclusion: Not Ready for Prime Time
MySQL socket authentication doesn’t seem to be ready for prime time yet.
Even though someone, somewhere might be happy using socket authentication for MySQL, at least the messages from the deb installer indicate that it’s not fully supported in the configuration I tested.
This conclusion is based on very brief and initial testing. Feel free to add a comment if and how you successfully use this feature.
Adminstrivia
Tested with Ubuntu 16.04.1 LTS xenial amd64, bento/ubuntu-16.04 box on Vagrant. With mysql-client, mysql-server and other mysql* packages version 5.7.20-0ubuntu0.16.04.1, the default from standard apt repositories.
This article has been updated many times.
This article describes a configuration that DOES NOT WORK and is also NOT SECURE.