mariaDB 10.5.12 access denied for [email protected]

fresh installation of mariaDB 10.5.12 on debian 11 was hardened with 'mysql_secure_installation' script, question "Switch to unix_socket authentication [Y/n]" was answered with 'yes'.

Now mariaDB allows local root to log in when host equals to 'localhost':

mysql --host=localhost
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 40
Server version: 10.5.12-MariaDB-0+deb11u1-log Debian 11

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)]>

but rejects when IP address was used:

mysql --host=127.0.0.1
ERROR 1698 (28000): Access denied for user 'root'@'127.0.0.1'

after some duckducking following modifications were conducted on database:

CREATE USER 'root'@'::1' IDENTIFIED VIA unix_socket;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'::1' WITH GRANT OPTION;
CREATE USER 'root'@'127.0.0.1' IDENTIFIED VIA unix_socket;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'127.0.0.1' WITH GRANT OPTION;
flush privileges;

"skip-name-resolve" parameter is not present in server's configuration:

show variables like '%skip_name%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| skip_name_resolve | OFF   |
+-------------------+-------+

relevant root accounts are now:

MariaDB [mysql]> select user, password, host, plugin from user where user='root';
+------+----------+-----------+-------------+
| User | Password | Host      | plugin      |
+------+----------+-----------+-------------+
| root |          | localhost | unix_socket |
| root |          | ::1       | unix_socket |
| root |          | 127.0.0.1 | unix_socket |
+------+----------+-----------+-------------+

For some reason local root is allowed to access 'localhost' but not to access database neither when connecting via "127.0.0.1" nor when connecting via "::1".

Why ?

In short localhost is treated as use socket. This answer on StackOverflow explains it:

The MariaDB server (also MySQL) treats localhost in a special way. Where other software treat it like an alias of the loopback address 127.0.0.1, MariaDB will interpret it as a UNIX domain socket connection to the server. By default this socket file is located in /var/lib/mysql/mysql.sock.

As you're not connecting via the socket when you use 127.0.0.1, socket authentication doesn't work. You have to either use localhost or the --socket=/path/to/socket.