DB-유

MariaDB 10.4: Unix Socket Authentication for root

· mariadb, security, authentication

The first time I installed MariaDB 10.4, mysql -u root -p told me access was denied with the right password, and sudo mysql let me in with none. Not a bug: 10.4 changed how root@localhost authenticates. It now accepts the OS user root through the unix_socket plugin, and password login exists only if you add it. That closes off the most common way MariaDB servers got compromised (an empty or weak root password), but it breaks a lot of older scripts.

What changed

Before 10.4, root@localhost had a password (possibly empty) and authenticated with mysql_native_password. From 10.4 on, on a fresh install, the account is created like this:

SHOW CREATE USER root@localhost;
-- CREATE USER `root`@`localhost` IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'

Two things to read there. unix_socket means the server asks the kernel which OS user owns the connecting socket, and if that user is root, lets them in – no password involved, and no way to fake it from a non-root shell. OR mysql_native_password USING 'invalid' means a password fallback exists but its hash is the literal string invalid, which no password can match. So effectively root can log in only via sudo or as OS root, over the socket.

The same applies to the new mysql@localhost account created for administrative scripts, and the plugin is compiled in rather than loaded from a .so, so INSTALL PLUGIN unix_socket is no longer needed.

Logging in the intended way

sudo mysql
# or
sudo mysql -u root

Both work. Note the socket path must match: on CentOS the default is /var/lib/mysql/mysql.sock, on Ubuntu /run/mysqld/mysqld.sock; if a script passes -h 127.0.0.1 it uses TCP and unix_socket cannot apply.

Option 1: keep socket login and add a password

If you want both, sudo mysql for convenience plus a password for tools like phpMyAdmin or a GUI on the same host, keep the OR clause and set a real password:

ALTER USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING PASSWORD('Str0ng-Root-Passw0rd!');

PASSWORD('...') computes the hash; USING '*HASH' also works if you already have one. After this, mysql -u root -p from any local user works with the password, and sudo mysql still works without it.

Option 2: password only

I don't love this, but some environments (Ansible playbooks, monitoring agents that run as a non-root service user but hold a root password) expect classic behavior. To remove socket authentication entirely:

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password USING PASSWORD('Str0ng-Root-Passw0rd!');

Or the shorter form, which does the same on 10.4:

SET PASSWORD FOR root@localhost = PASSWORD('Str0ng-Root-Passw0rd!');

mysql_secure_installation on 10.4 asks "Switch to unix_socket authentication [Y/n]" and "Change the root password?" separately, so you can express either preference there as well.

Use unix_socket for other local accounts too

The plugin is not only for root. A cron job that runs as the backup OS user can have a database account with the same name and no password anywhere on disk:

CREATE USER backup@localhost IDENTIFIED VIA unix_socket;
GRANT SELECT, LOCK TABLES, SHOW VIEW, TRIGGER, EVENT, RELOAD, REPLICATION CLIENT ON *.* TO backup@localhost;

Then mysqldump --all-databases executed by that user just works, and there is no ~/.my.cnf with a password to protect. The OS user name and the MariaDB user name must match exactly.

Looking at what you have

SELECT user, host, plugin, authentication_string FROM mysql.user WHERE host = 'localhost';
SELECT CURRENT_USER(), USER();

On 10.4 mysql.user is actually a view over mysql.global_priv; the JSON in mysql.global_priv.priv is the authoritative record:

SELECT user, host, JSON_DETAILED(priv) FROM mysql.global_priv WHERE user = 'root';

You should see "plugin": "mysql_native_password" plus "auth_or": [{}, {"plugin": "unix_socket"}] or similar, depending on what you configured. Then test each path: sudo mysql -e "SELECT USER()", mysql -u root -p -e "SELECT USER()" as a regular user, and mysql -h 127.0.0.1 -u root -p over TCP.

Errors you'll hit

  • ERROR 1698 (28000): Access denied for user 'root'@'localhost' when running mysql -u root -p as a normal user – Expected on a default 10.4 install; use sudo mysql or add a password as in option 1.
  • ERROR 1698 (28000) when connecting with -h 127.0.0.1 even as OS root – TCP connections carry no OS identity; unix_socket only works on the socket. Drop the -h or add a password.
  • phpMyAdmin or Adminer shows mysqli::real_connect(): (HY000/1698): Access denied for user 'root'@'localhost' – The web server runs as www-data or apache, not root. Either give root a password (option 1) or, better, create a dedicated admin account with a password for the web tool.
  • Ansible mysql_user module fails with unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials – Point the module at the socket (login_unix_socket: /var/lib/mysql/mysql.sock) and run the task with become: true, so it authenticates via unix_socket.
  • After mysql_upgrade from 10.3, root still uses a password – The new default applies to fresh installs only; upgrades keep the existing authentication. Switch manually with option 1 if you want socket login.

I keep the new default. I add a password only where a tool needs one.