Two-factor authentication for mySQL users

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)

MariaDB [(none)]>

Wohoo!