I want to authenticate mySQL users with a password and a token on Ubuntu, so I figured out a way to do this using MariaDB, PAM and Google Authenticator.
First we need a plugin for mySQL that can authenticate users against PAM. The non-free enterprise version of mySQL has authentication_pam.so. I’m not an Enterprise customer, so I decided against this one.
A free implementation is available from Percona . To make it work with Oracle mySQL, the plugin auth_pam_compat.so must be used. The problem with this plugin is that cleartext passwords must be enabled in the mySQL client. I didn’t like that idea so I replaced Oracle mySQL with MariaDB.
sudo apt-get install mariadb-server mariadb-client
Verify that MariaDB runs fine. Then follow the installation guide for the Percona auth_pam.so plugin. Install some dependencies first:
sudo apt-get install libpam-dev libmysqlclient-dev automake autoconf libtool build-essential bzr
then checkout the plugin source from launchpad via bazaar, ./bootstrap, ./configure, make, sudo make install. Load the plugin via /etc/mysql/my.cnf:
[mysqld]
plugin-load=auth_pam.so
and restart the mySQL server. Create a mySQL user according to the Percona installation instructions. It has to be a local system user and it must use the auth_pam plugin. Now install Google Authenticator on Ubuntu and on your smartphone.
sudo apt-get install libpam-google-authenticator pamtester
google-authenticator
Say yes to all questions and take a picture of the QR code using the phone. Then create a directory for the token files:
sudo mkdir -p /var/lib/mysql-2fa/USERNAME
sudo mv /home/USERNAME/.google_authenticator /var/lib/mysql-2fa/USERNAME
sudo chown mysql. -R /var/lib/mysql-2fa
This is necessary as the Google Authenticator PAM plugin will run as the “mysql” user, which has no access to the token files in user’s home directories. Edit /etc/pam.d/mysqld :
auth required pam_google_authenticator.so forward_pass secret=/var/lib/mysql-2fa/${USER}/.google_authenticator user=mysql
auth required pam_unix.so use_first_pass
account required pam_unix.so
(the file has a total of three lines, what looks like the first two lines above needs to be in one line)
Now mySQL needs to access some files related to PAM. Edit /etc/apparmor.d/usr.sbin.mysqld and add:
/etc/pam.d/mysqld r,
/lib/x86_64-linux-gnu/security/pam_*.so m,
/lib/security/pam_google_authenticator.so m,
/etc/pam.d/* rm,
/etc/login.defs r,
/etc/shadow r,
/var/lib/mysql-2fa/** rwk,
The mysql user also needs to be part of the ‘shadow’ group:
adduser mysql shadow
Try to authenticate using pamtester:
# sudo -u mysql pamtester -v mysqld my_username authenticate
pamtester: invoking pam_start(mysqld, my_username, ...)
pamtester: performing operation - authenticate
Password & verification code:
pamtester: successfully authenticated
Check /var/log/auth.log for any errors. Everything works? Connect to the database with two factor authentication (concatenate password and token):
# mysql -u my_username -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 1603
Server version: 5.5.39-MariaDB-0ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2014, Oracle, Monty Program Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
Wohoo!
Hello,
Nice configuration, we hope that multiOTP satisfies your needs. Please note that even if multiOTP is talking about PIN code, we are supporting long passwords as the PIN.
It’s also possible to synchronize the users of multiOTP with an AD/LDAP, and i nthis case, the prefix password is the AD/LDAP password.
Best regards,
Andre