1

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 ?

mpr
  • 11
  • 1

1 Answers1

1

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.

vidarlo
  • 3,775
  • 1
  • 12
  • 25
  • enlightening, thank you for above explanation. But ... "ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (115)" persists when bootstrapping first node in galera cluster, so ... do I have to modify "galera_new_cluster" to work around it ? – mpr Apr 20 '22 at 17:16
  • Tell galera to connect over socket, or reconfigure MySQL to be consistent with how you intend to use it. – vidarlo Apr 20 '22 at 17:21
  • As a sidenote, *ask* about what you [want to achieve, not how you think you'll achieve it](https://xyproblem.info/). Your goal was to connect Galera to MySQL. But you asked why `localhost` was handled differently from `127.0.0.1` - so you got an spot on answer, that didn't really help you solve your *actual* problem. – vidarlo Apr 20 '22 at 17:30
  • Yes: agree with you, @vidarlo. At the time of question creation it was a bug or wrong configuration in my understanding. It turned out that different handling of localhost and 127.0.0.1 is the feature of maria/mysql so a work-around is needed. – mpr Apr 21 '22 at 14:49