Arch Linux

onslow77

Member

Registered: 2014-09-21

Posts: 283

MariaDB confused about passwordless connection

Hi,

According to Arch wiki about MariaDB, by default both root user and the user running the server can administer the database. However, when I run

 mariadb 

I get the error ERROR 1698 (28000): Access denied for user 'my-username'@'localhost'

Is this due to that I during

 # mariadb-secure-installation 

I answered yes to remove anonymous users?

Or, is it because I also answered yes to switch to unix socket for authentication, during the above secure installation?

Also, after I ran the "mariadb-secure-installation" I learned that "unix socket authentication" is used as a default in MariaDB 10.4 and up, but the question during the secure installation imply that it is not since it is asking if you want to switch to unix socket authentication. So, what does answering yes or no actually refer to, do they for example mean for new MariaDB users?

To summer up my main question, the error I get when I try to administer the database as my normal user that runs the server, is that expected with the changes I did during the secure installation script, or have I messed something up somewhere else? I can administer the database as root or by elevate with sudo with no problem.

Regards
onslow77

Last edited by onslow77 (2025-05-18 00:12:31)

V1del

Forum Moderator

Registered: 2012-10-16

Posts: 24,795

Re: MariaDB confused about passwordless connection

The "user running the server" is usually a dedicated DB user. In the case of the Arch mariadb package and assuming the DB started via systemd the mysql user. e.g. you should be able to do

sudo -u mysql mariadb

Your "normal" user should generally have no bearing on that unless you set that up for the relevant access.

Last edited by V1del (2025-05-18 00:59:56)

onslow77

Member

Registered: 2014-09-21

Posts: 283

Re: MariaDB confused about passwordless connection

The "user running the server" is usually a dedicated DB user.

I interpreted that as a unix login name, as referred to here: https://mariadb.com/kb/en/connecting-to-mariadb/

But I see now that the Arch Wiki actually states:

To administer the server, run mariadb as the user running the server:

[mysql]$ mariadb

or as root

# mariadb

So, if I if I try to administer the MariaDB with:

 sudo  -u mysql mariadb 

I get access.

I have not set my user that administer the server (e.g., my unix login name) to interact with MariaDB, so I guess it is logical I get access denied. But I got confused since under the above link about connecting to MariaDB, it states: "...The MariaDB user name to use when connecting to the server. The default is either your Unix login name, or ODBC on Windows... " See under the --user helptext. And in the beginning of the article, it says:

If a connection parameter is not provided, it will revert to a default value.

For example, to connect to MariaDB using only default values with the mariadb client, enter the following from the command line:

mariadb

I assumed that such a user (e.g., the unix login name) could get access because anonymous user initially is allowed? Meaning allowing anyone to log into MariaDB without having to have a user account created for them.

Also, if unix socket is implemented as default when installing MariaDB, and during the

 # mariadb-secure-installation 

The first prompt is about root password

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):

Since you just installed MariaDB you press "Enter" then the script states:

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n]

What purpose does this have, and what changes (or are not) made by a yes or no choice?

Answering "no" keep unix socket, and answering "yes" also keep it by once again implementing unix socket. Or am I missing something?

Regards
onslow77

Last edited by onslow77 (2025-05-18 09:17:51)

onslow77

Member

Registered: 2014-09-21

Posts: 283

Re: MariaDB confused about passwordless connection

Hi again,

To get unix_socket authentication in MariaDB is confusing to get right, or I am missing something obvious.

First, I installed MariaDB and ran the

# mariadb-install-db --user=mysql --basedir=/usr --datadir=/var/lib/mysql 
and 
# mariadb-secure-installtion

Answered YES to the question if I want to switch to unix socket authentication.

Then I check what authentication method is used by administer MaraDB:

MariaDB [(none)]> USE mysql
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> SELECT User, Host, plugin FROM mysql.user;
+-------------+-----------+-----------------------+
| User        | Host      | plugin                |
+-------------+-----------+-----------------------+
| mariadb.sys | localhost | mysql_native_password |
| root        | localhost | mysql_native_password |
| mysql       | localhost | mysql_native_password |
| PUBLIC      |           |                       |
+-------------+-----------+-----------------------+
4 rows in set (0.001 sec)

Under plugin, I expected it to say: unix_socket. But it is not.

I figure, well maybe I messed up, and I could not remember everything I done, so I just purged MariaDB, its configuration, and reinstalled. However, this time during "mariadb-secure-installation" I answered NO to the question if I wanted to switch to unix_socket, since it is suppose to be implemented by default in MariaDB from version 10.4 und upwards. I then check again as explained above, but it still said "native_password" for every user. So answering no or yes, does not seem to make any difference.

Is my expectation wrong, or, what am I missing in order to use unix_socket as the authentication method?

Regards
onslow77

Last edited by onslow77 (2025-05-18 12:37:58)

onslow77

Member

Registered: 2014-09-21

Posts: 283

Re: MariaDB confused about passwordless connection

Hi,

After some digging I learn that the mysql.user is deprecated in MariaDB 10.4 and upwards, instead authentication information is stored in mysql.global_priv.

"...the old mysql.user table still exists. You can select from it as before, although you cannot update it anymore. It doesn’t show alternative authentication plugins and this was one of the reasons for switching to the mysql.global_priv table — complex authentication rules did not fit into rigid structure of a relational table..." 

Also, during MariaDB, two powerful user are created with:

CREATE USER root@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'
CREATE USER mysql@localhost IDENTIFIED VIA unix_socket OR mysql_native_password USING 'invalid'

Checking mysql.glovbal_priv with:

SELECT CONCAT(user, '@', host, ' => ', JSON_DETAILED(priv)) FROM mysql.global_priv;

First shows plugin as mysql_native_password, but the authentication_string shows "invalid" and then it also shows plugin as unix_socket, which represents how the two users root and mysql are created in MariaDB during the initial setup (if I understood it correctly). In other words, passwords are not used as authentication method, unix_socket is.

I am still wondering why during the

# mariadb-secure-installation

The user is asked:

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
 ... skipping.

The user select 'NO' because unix_socket is implemented by default, or 'YES' to implement unix_socket again; In other words, both answers leads to the same result.

One could argue that someone run the security installation further down the line and have switch to use the password method for authentication. However most users will likely run the security installation as part of their initial setup, (when unix_socket is already setup as default). A suggestion to make the question easier to understand would therefore be to state what method is used (not just that it is secure) and then ask if the user want to keep current implementation or switch to X or Y method depending on what the user was using.

Regards
onslow77

Last edited by onslow77 (2025-05-19 05:17:25)