How to use a smartphone for two-factor authentication with Percona Server for MySQL

In this post I will describe a non-trivial way to authenticate users in Percona Sever for MySQL. Percona Server comes with PAM authentication plugin, which allows you to do a lot of cool things, such as: OS authentication, LDAP authentication, even RSA Secure Server authentication (which is useful if you are required a PCI-compliance), and use Google Authenticator, which is the topic of this post.

With two-factor authentication a user is required to enter not only password, but to have an additional security token, which in the case with Google Authenticator can be your cell-phone (clients are available for Android, iPhone, Windows Mobile or BlackBerry with the full list here). This way an attacker will need not only steal or guess password, but also to gain an access to cell phone, which is not impossible, but makes things more complicated.

The setup actually is quite easy if you follow steps:

1. Enable PAM plugin (more in our documentation):

2. Configure PAM for mysqld process by putting into /etc/pam.d/mysqld file:

3. Create a user in the server:

After this we already able to authenticate using an OS account (assuming there is an account for user ‘vadim’)

4. Now, to install pam-google-authenticator, there are possible ways:

• compile from a source code, the project is hosted here https://code.google.com/p/google-authenticator/
• if you are Ubuntu user – install package sudo apt-get install libpam-google-authenticator

5. From the user account, we setup authentication for, run google-authenticator for an initial setup.

It outputs URL, when you use it will produce an QRCode, like:

which you scan from smartphone Authentificator application to connect accounts, or
just manually enter secret key.

6. Instruct PAM to use google authentificator, add to /etc/pam.d/mysqld

7. All set, now when I try to login with user ‘vadim’, the server asks me both password and Verification Code (generated on my smartphone in Authentificator application)

Authentificating against LDAP server is not much more complicated, once you have LDAP running, just replace
pam_unix to pam_ldap in /etc/pam.d/mysqld

This was just an example, but to show a Percona Server’s feature: a complex authentication which satisfies Enterprise-grade security requirements.