How to set up passwordless access to MariaDB/MySQL

29 June 2021

If you regularly access databases on the command line, or if you want to run SQL commands from a script, then you might want to set up passwordless access. This article looks at two ways to configure this; you can either store the password in a user’s ~/.my.cnf file or you can use the unix_socket plugin.

Storing the password in ~/.my.cnf

Database users need privileges on database tables, and they need to log in order to run database queries. The most common way to configure this is by using the IDENTIFIED BY clause followed by the user’s password. For instance, here I create the user example@localhost and grant it all privileges on any table in a database named testdb:

MariaDB [(none)]> CREATE USER 'example'@'localhost' IDENTIFIED BY '6FGGpT3SusKxf_v7HKfD3-ezvq6SqQf2tZVy';
MariaDB [(none)]> GRANT ALL PRIVILEGES ON testdb.* TO 'example'@'localhost' IDENTIFIED BY '6FGGpT3SusKxf_v7HKfD3-ezvq6SqQf2tZVy';
MariaDB [(none)]> FLUSH PRIVILEGES;

The user example can now connect to the testdb database. Note that the -p option causes MariaDB to prompt the user for its password.

$ mysql -u example -p testdb
Enter password: 

MariaDB [testdb]> 

If you don’t want to enter the password on every login then you can store the password in a .my.cnf file in your home directory (on Windows the file is named my.ini). The .my.cnf file is the default configuration file for MySQL and MariaDB. A .my.cnf file in a user’s home directory only sets options for the user (i.e. its scope is limited to the user).

The contents of the file should be as follows (obviously you need to set the correct password):

$ cat ~/.my.cnf
[client]
password = 6FGGpT3SusKxf_v7HKfD3-ezvq6SqQf2tZVy

You also want to make sure that the file has 600 permissions. This is particularly important on older Ubuntu systems, as the default permissions let users peek inside each others home directories (RHEL-based servers never allow this):

$ chmod 600 ~/.my.cnf

And that’s it – the user example now has passwordless access:

$ mysql testdb

MariaDB [testdb]> 

Using unix_socket authentication

When I created the user example I user the IDENTIFIED BY clause to set the user’s password. An alternative is the IDENTIFIED VIA clause. With that option the user can authenticate using a plugin rather than a password. There are quite a few different authentication plugins. A commonly used plugin is Unix Socket. This plugin authenticates a user using their operating system credentials. When the user logs in it tells MariaDB: “I am a user on this system and I own this database, so don’t you dare bother me with password prompts.” MariaDB then checks if what the user says is true, and if so authenticates the user.

The plugin is installed by default from MariaDB version 10.4.3 onwards. RHEL8-based servers ship an older version, so you may need to install the plugin. You can check if the plugin is already installed using this command:

MariaDB [(none)]> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'unix_socket';
Empty set (0.001 sec)

Here, the plugin isn’t installed. To fix that you can run the below command. Note that the name of the plugin is auth_socket rather than unix_socket:

MariaDB [(none)]> INSTALL SONAME 'auth_socket';

MariaDB [(none)]> SELECT * FROM information_schema.PLUGINS WHERE PLUGIN_NAME = 'unix_socket' \G
*************************** 1. row ***************************
           PLUGIN_NAME: unix_socket
        PLUGIN_VERSION: 1.0
         PLUGIN_STATUS: ACTIVE
           PLUGIN_TYPE: AUTHENTICATION
   PLUGIN_TYPE_VERSION: 2.1
        PLUGIN_LIBRARY: auth_socket.so
PLUGIN_LIBRARY_VERSION: 1.13
         PLUGIN_AUTHOR: Sergei Golubchik
    PLUGIN_DESCRIPTION: Unix Socket based authentication
        PLUGIN_LICENSE: GPL
           LOAD_OPTION: ON
       PLUGIN_MATURITY: Stable
   PLUGIN_AUTH_VERSION: 1.0
1 row in set (0.002 sec)

Next, you can create the user and double-check that the unix_socket plugin is enabled for the user:

MariaDB [(none)]> CREATE USER example@localhost IDENTIFIED VIA unix_socket;

MariaDB [(none)]> SELECT user, host, plugin FROM mysql.user WHERE user = 'example';
+---------+-----------+-------------+
| user    | host      | plugin      |
+---------+-----------+-------------+
| example | localhost | unix_socket |
+---------+-----------+-------------+

And after that you grant the user one or more privileges:

MariaDB [(none)]> GRANT ALL PRIVILEGES ON testdb.*
    -> TO 'example'@'localhost'
    -> IDENTIFIED VIA unix_socket;

Et voilà! Our user now has passwordless access to the testdb database:

$ mysql testdb

MariaDB [testdb]>