MariaDB Socket Auth vs TCP Users

MariaDB on the fleet uses two distinct auth paths: the root/admin account authenticates by unix socket, while application accounts (like the wiki's) authenticate by password over TCP. Knowing which is which saves a lot of "access denied" confusion.

#Socket auth (admin)

The root user uses the unix_socket plugin: MariaDB trusts the OS user, so there is no password — you authenticate by being root on the box.

sudo mysql                      # works: you are root via the socket
mysql -u root -p                # fails / prompts pointlessly — root isn't a password user

This is why admin scripts run sudo mysql -e "..." and a plain mysql -u root from a non-root shell is rejected. The credential is your OS identity, not a string.

#TCP password users (apps)

Application accounts authenticate the classic way — username + password over a TCP connection. Create the user bound to a host and grant only its own database:

CREATE DATABASE wiki_thelab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'wiki_user'@'127.0.0.1' IDENTIFIED BY '••••••';
CREATE USER 'wiki_user'@'localhost' IDENTIFIED BY '••••••';
GRANT ALL PRIVILEGES ON wiki_thelab.* TO 'wiki_user'@'127.0.0.1';
GRANT ALL PRIVILEGES ON wiki_thelab.* TO 'wiki_user'@'localhost';
FLUSH PRIVILEGES;

The app then connects over TCP with a DSN:

mysql:host=127.0.0.1;dbname=wiki_thelab;charset=utf8mb4

#Why create both @127.0.0.1 and @localhost?

MariaDB treats them as different accounts, and the host you match depends on how the client connects:

• host=localhost → the client uses the unix socket → matches 'user'@'localhost'.
• host=127.0.0.1 → the client uses TCP → matches 'user'@'127.0.0.1'.

Defining both means the app works regardless of which form the DSN uses. The wiki connects via 127.0.0.1 (TCP).

#Good practice

• One DB user per app, granted only that app's schema — never reuse root.
• Keep the app password in a config file outside the web root (the wiki stores it in /var/www/wiki.thelabsource.com/config.php, above public/).
• Bind app users to 127.0.0.1/localhost, never 'user'@'%', unless a remote host genuinely needs access.

See also: Apache Name-Based Virtual Hosts on One Server, MariaDB Security Installation Steps.