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.
You can use a smartphone for two-factor authentication with Percona Server for MySQL
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):
1 | mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so'; |
2. Configure PAM for mysqld process by putting into /etc/pam.d/mysqld file:
1 2 | auth required pam_unix.so account required pam_unix.so |
3. Create a user in the server:
1 | mysql> CREATE USER 'vadim'@'%' IDENTIFIED WITH auth_pam; |
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.
1 2 3 | > google-authenticator https://www.google.com/chart?chs=200x200&chld=M|0&cht=qr&chl=otpauth://totp/[email protected]%3Fsecret%3DEDCHNEEFQ5TYQFYL Your new secret key is: EDCHNEEFQ5TYQFYL |
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
1 | auth required pam_google_authenticator.so |
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)
1 2 3 | mysql -uvadim Password: Verification code: |
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.
