Enable password and unix_socket authentication for MariaDB root user?

Is there a possibility to authenticate the root user by unix_socket (by root shell) or by password (when it is connected by localhost:3306)?

The purpose of auth_socket in MySQL or unix_socket in MariaDB is to disable password authentication in favor of Unix sockets, which only allow verified local users to connect to the server.

In other words, you can't have multiple authentication plugins for the same user/host. However like others have pointed out, a common workaround is creating additional users (with the same username) that are associated with different host addresses (passwords can even be the same).

As @ColinM mentioned, localhost usually defaults to using sockets, whereas 127.0.0.1 is inherently non-socket because it is an IP address, e.g. TCP protocol.

Here's an example of this for MySQL 8.0.x:

mysql -e "CREATE USER IF NOT EXISTS 'root'@'localhost' IDENTIFIED WITH auth_socket;"
mysql -e "CREATE USER 'root'@'127.0.0.1' IDENTIFIED WITH mysql_native_password BY '@DB_PASSWORD_ROOT';"
mysql -e "CREATE USER 'root'@'::1' IDENTIFIED WITH mysql_native_password BY '@DB_PASSWORD_ROOT';"

To ensure auth_socket is persistent add lines to your my.cnf:

plugin-load-add = auth_socket.so
auth_socket = FORCE_PLUS_PERMANENT

Keep in mind that it is now recommended to create a separate non-root user to be used with database management tools such as phpMyAdmin or Adminer, e.g. [email protected] and also realize that the new password hash plugin caching_sha2_password is less compatible with many applications, esp. if they are not authenticating using either TLS/SSL, Unix sockets, or shared memory.

A reliable and straightforward way would be to create another super-user and use it when you want to connect by password.

CREATE USER admin@localhost IDENTIFIED BY 'password';
GRANT ALL ON *.* TO admin@localhost WITH GRANT OPTION;
-- etc

MariaDb/MySQL considers 'localhost' (unix socket) to be different than '127.0.0.1' (tcp socket) so you could set a password for TCP and none for Unix sockets like so:

MariaDb:

GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' IDENTIFIED BY 'xxx' WITH GRANT OPTION;
INSTALL SONAME 'auth_socket';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' IDENTIFIED VIA unix_socket WITH GRANT OPTION;

MySQL/Percona:

CREATE USER 'admin'@'127.0.0.1' IDENTIFIED BY 'xxx';
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'127.0.0.1' WITH GRANT OPTION;
INSTALL PLUGIN auth_socket SONAME 'auth_socket.so';
CREATE USER 'admin'@'localhost' IDENTIFIED WITH auth_socket;
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'localhost' WITH GRANT OPTION;

You can then login on the local host with mysql -u admin without a password over the unix socket or via tcp socket with a password using mysql -h 127.0.0.1 -u admin -p.

Yes — starting with MariaDB 10.4 (released in June 2019*), you can use the OR keyword with IDENTIFIED VIA to allow any-of several authentication methods:

ALTER USER `minecraft` 
    IDENTIFIED VIA unix_socket 
    OR mysql_native_password USING PASSWORD("Type me over TCP, or from other accts");

https://mariadb.com/kb/en/pluggable-authentication-overview/#extended-sql-syntax

---

*Even though OS vendors may be slow-on-the-draw, or even decline to provide updates altogether, MariaDB provides an official repository serving at least this version to distributions as old as Ubuntu 14.04, CentOS & RHEL 6, Debian 7, and Fedora 28 (as well as up to current releases of each), though for some of these (e.g. Fedora < 32) you may have to manually modify the version in the URL.