MariaDB - enable root login via both UNIX_SOCKET and TCP

Posted June 12, 2017 12.9k views
MariaDBUbuntu 16.04

With MariaDB by default the root user has the plugin unix_socket enabled:

SELECT Plugin FROM user WHERE User = "root";

| Plugin      |
| unix_socket |

From the documentation:

  • In this example, a user is already logged into the system and has full shell access. Because he has identified himself to the operating system, he does not need to do it again for the database — MariaDB trusts operating system credentials. But he cannot connect to the database as another user.

This is useful. However we also run PHPMyAdmin which requires the root username and password entering to connect to MariaDB, and this login is failing because the root user has the unix_socket plugin specified.

If we remove the unix_socket from the root user, PHPMyAdmin then works with a regular username/password combination, but we also have to enter the username and password each time when connecting to MariaDB from the command line.

Is there a solution where both can be used in-conjunction with each other?

For security we don’t really want to store the root password in plaintext in a ~/.my.cnf file.

These answers are provided by our Community. If you find them useful, show some love by clicking the heart. If you run into issues leave a comment, or add your own answer to help others.

Submit an Answer
1 answer

Hi @DigitalCarrot

Why don’t you create two root users? As long as the Host+User is unique, then it’s allowed.

But from a security point, I would would say /root/.my.cnf is a clear winner compared to phpMyAdmin.
If you look at how many security flaws there has been in PMA, PHP, Apache and MySQL combined versus the chmod/chown commands of the Unix-world, then you wouldn’t doubt how it works.

I’m guessing you’re running PMA over HTTPS, and storing all company passwords in a password manager - if not, then that’s a much bigger security problem.

  • Thanks for your reply.

    Yes, we’re running PHPMyAdmin over https. It’s also in a directory renamed to something obscure, and locked to our local IP address so you have to be on our network to access it via a browser. We login to this using the root username and password (which is stored locally via an encrypted password manager).

    I guess we would have to use a different username (something other than root) but with root privileges in order to have a different user (still set to localhost) without the “unix_socket” plugin set in the user table?

    • @DigitalCarrot

      It’s really not a problem using the /root/.my.cnf. If someone would be able to read that file, they would be able to read anything on the server - including the entire database - but they could also place a sniffer in the PMA login.
      Of course, given that you only set chmod to 600 and chown to $USER.

      It would probably be best to create another user for administrative access via PMA - and keep the root user only accessible via the command line.