Getting Percona PAM to work with Percona Server & its client apps

Percona Server is bundled with the PAM plugin which opens a plethora of ways to authenticate to MySQL such as restricting time when users can connect to MySQL, authenticate via a USB key, authenticate to an external authentication system such as LDAP and many, many more PAM compatible mechanisms.

If you want to use PAM authentication on the community version of MySQL, you may follow the instructions here to get it working on your system. If you want to test PAM authentication, the simplest way is to authenticate via /etc/shadow. The steps do so can be found in here or you can follow the steps below.

Here’s a primer for setting up Percona PAM on CentOS 6 to authenticate via /etc/shadow:

1. Install Percona yum repository

2. Install Percona Server 5.5

3. Start Percona Server 5.5

4. From the mysql console, enable auth_pam and auth_pam_compat plugins. These PAM plugins will be discussed in detail later.

5. From the MySQL console, create two users that will authenticate using auth_pam and auth_pam_compat respectively. You also need to delete anonymous users:

6. Configure Percona Server to authenticate via /etc/shadow by creating a PAM config file in /etc/pam.d/mysqld with the following content:

7. Ensure Percona Server can read /etc/shadow by changing the group ownership and permissions of it

8. Create system users and respective passwords. The usernames should match the users created from the MySQL console

9. Test if you can connect to Percona Server using the Unix passwords of ap_user and apc_user:

10. Done.

The Percona PAM plugin authentication plugin has 2 types of plugins. The first is auth_pam plugin which is a full-featured implementation of the PAM plugin that uses the dialog plugin. The other is auth_pam_compat which uses mysql_clear_password plugin which Oracle provides.

The drawback of this plugin is it’s only able to accept a password as an input and credentials are sent in cleartext which is not secure. Now, why would you need to choose between one or the other? It all depends if the client supports any of those plugins or worse, none at all. To create a user to authenticate via auth_pam, on the MySQL console run:
CREATE USER <auth_pam_user> IDENTIFIED WITH auth_pam;
For auth_pam_compat, run:
CREATE USER <auth_pam_compat_user> IDENTIFIED WITH auth_pam_compat;

As of now, only Percona Server’s mysql client and an older version of HeidiSQL(version 7), a GUI MySQL client for Windows, are able to authenticate over PAM via the auth_pam plugin by default.

So, if you try to connect to MySQL using Perl, PHP, Ruby, Python and the like, you will receive this error: “Client does not support authentication protocol requested by server; consider upgrading MySQL client.”

The good news is that if the client uses libmysqlclient library to connect via MySQL, you can recompile the client’s source code to use the libmysqlclient library of Percona Server to make it compatible. This involves installing Percona Server development library, compiler tools, and development libraries followed by compiling and installing the client’s source code. This maybe an easy task for a single server but if you have a large deployment of servers and clients, it would be wiser to build RPMs or DEBs instead.

To give you an idea of which clients depend on libmysqlclient, if you’re using CentOS and yum-utils is installed, you can run: repoquery -q –whatrequires mysql-libs

For Ubuntu, just run: apt-cache rdepends libmysqlclient18

Below is an example of rebuilding and installing perl’s DBD MySQL package to make it compatible with the auth_pam plugin:

1. Install Percona yum repository and Percona Server 5.5 development library:

2. Install rpm-build, the package used to build RPMs

3. Download and install perl-DBD-MySQL Source RPM.

The source will be installed under rpmbuild of the current directory.

4. Install compilers and dependencies:

5. Build the RPM file:

6. Install the RPM file:

7. Test by creating a perl script and running it:
testpam.pl

8. Troubleshooting
If you get the error above, it means you need to install the dialog plugin which is bundled in the Percona-Server-server-55 RPM. If you don’t want to install the RPM, you may opt to download and extract it from the binary tar file provided in the Percona website and place the plugin on the /usr/lib64/mysql/plugin directory. In this example, we will install the Percona-Server-server-55 RPM

9. Done.

As for the auth_pam_compat plugin, if the clients use the libmysqlclient.so.18 and above, you do not need to recompile the client’s source code such as described above.

On Ubuntu 12.04, there’s no need to recompile libdbd-mysql-perl package:

On the other hand, perl-DBD-MySQL of CentOS 6 needs to be recompiled because it depends on libmysqlclient.so.16:

Once you’re able to resolved the issue above, depending on your client, you can use the auth_pam_compat plugin by enabling the use of the cleartext plugin via environment variable, program option or placing the configuration on my.cnf:

Examples:
Using the environment variable:

Enable the plugin as an option. It’s supported on mysql, mysqladmin and mysqlslap.

As a my.cnf config:
/etc/my.cnf

For more information on the cleartext authentication, click here.

A practical example of using auth_pam_compat is being able to use MySQL Workbench 5.2 over PAM on a Mac:

and on Linux:

Do note that on MySQL Workbench 5.2, you need to store the password in the keychain for authentication to PAM to work. Also, sadly, Percona PAM does not work on MySQL Workbench 5.2 on Windows as confirmed in the bug I reported last February 2013.

As for the latest release of MySQL Workbench 6.0, there’s an advanced option to enable the cleartext authentication plugin when setting up connections. However, not all features work with auth_pam_compat. For example, SQL Editor does not connect successfully with auth_pam_compat plugin but the Reverse Engineer tool works fine with the plugin.

Conclusion
If you want to use Percona PAM, you need to test first if your MySQL clients support it and most likely you will need to recompile these clients to make it work. If your only choice is auth_pam_compat but you are weary of using it because credentials are sent in cleartext, you can add layer of security by enabling SSL encryption on MySQL. One such example is JDBC, where cleartext authentication is permissible, but only if the connection is encrypted.