Database connection using unix_socket auth

I recently updated to MariaDB 10.4, in which the unix_socket authentication plugin is installed by default.

In unix_socket auth mysqld gets the user name of the process that is connected to the socket from an operating system call. It then authenticates the connecting user as the MariaDB account that has the same user name.

In my Debian servers, PHP FPM runs as user www-data. So I added a user in MariaDB

CREATE USER IF NOT EXISTS 'www-data'@'localhost' IDENTIFIED VIA unix_socket;

In my Yii app I changed the username to www-data and removed the password so it looks like

    'class' => 'yii\db\Connection',
    'dsn' => 'mysql:host=localhost;dbname=myschema',
    'username' => 'www-data',

And now the app can use the database without the overhead of a password exchange on each connection.

If your PHP process connects to the database over a network then this isn’t possible but it works for me because I have an old-fashioned LAMP-like stack with nginx, PHP FPM and MariaDB all on the same host.