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
1 2 3 4 | # rpm -Uvh https://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm Retrieving https://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm Preparing... ########################################### [100%] 1:percona-release ########################################### [100%] |
2. Install Percona Server 5.5
1 2 3 4 5 6 7 8 9 10 11 12 | # yum install Percona-Server-server-55 Percona-Server-client-55 … snipped for brevity … Installed: Percona-Server-client-55.x86_64 0:5.5.32-rel31.0.549.rhel6 Percona-Server-server-55.x86_64 0:5.5.32-rel31.0.549.rhel6 Dependency Installed: Percona-Server-shared-55.x86_64 0:5.5.32-rel31.0.549.rhel6 perl.x86_64 4:5.10.1-131.el6_4 perl-Module-Pluggable.x86_64 1:3.90-131.el6_4 perl-Pod-Escapes.x86_64 1:1.04-131.el6_4 perl-Pod-Simple.x86_64 1:3.13-131.el6_4 perl-libs.x86_64 4:5.10.1-131.el6_4 perl-version.x86_64 3:0.77-131.el6_4 Complete! |
3. Start Percona Server 5.5
1 2 | # service mysql start Starting MySQL (Percona Server)...... SUCCESS! |
4. From the mysql console, enable auth_pam and auth_pam_compat plugins. These PAM plugins will be discussed in detail later.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | mysql> INSTALL PLUGIN auth_pam SONAME 'auth_pam.so'; Query OK, 0 rows affected (0.00 sec) mysql> INSTALL PLUGIN auth_pam_compat SONAME 'auth_pam_compat.so'; Query OK, 0 rows affected (0.00 sec) mysql> SHOW PLUGINS; +--------------------------------+----------+--------------------+--------------------+---------+ | Name | Status | Type | Library | License | +--------------------------------+----------+--------------------+--------------------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | … snipped for brevity … | auth_pam | ACTIVE | AUTHENTICATION | auth_pam.so | GPL | | auth_pam_compat | ACTIVE | AUTHENTICATION | auth_pam_compat.so | GPL | +--------------------------------+----------+--------------------+--------------------+---------+ 42 rows in set (0.01 sec) |
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:
1 2 3 4 5 6 7 8 9 10 11 | mysql> CREATE USER ap_user IDENTIFIED WITH auth_pam; Query OK, 0 rows affected (0.00 sec) mysql> CREATE USER apc_user IDENTIFIED WITH auth_pam_compat; Query OK, 0 rows affected (0.00 sec) mysql> DELETE FROM mysql.user WHERE USER=''; Query OK, 2 rows affected (0.00 sec) mysql> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) |
6. Configure Percona Server to authenticate via /etc/shadow by creating a PAM config file in /etc/pam.d/mysqld with the following content:
1 2 3 | auth required pam_warn.so auth required pam_unix.so audit account required pam_unix.so audit |
7. Ensure Percona Server can read /etc/shadow by changing the group ownership and permissions of it
1 2 | #chgrp mysql /etc/shadow #chmod g+r /etc/shadow |
8. Create system users and respective passwords. The usernames should match the users created from the MySQL console
1 2 3 4 | #useradd ap_user #passwd ap_user #useradd apc_user #passwd apc_user |
9. Test if you can connect to Percona Server using the Unix passwords of ap_user and apc_user:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 | # mysql -u ap_user -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 9 Server version: 5.5.32-31.0 Percona Server (GPL), Release rel31.0, Revision 549 Copyright (c) 2009-2013 Percona Ireland Ltd. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> SELECT USER(), CURRENT_USER(); +-------------------+----------------+ | USER() | CURRENT_USER() | +-------------------+----------------+ | ap_user@localhost | ap_user@% | +-------------------+----------------+ 1 row in set (0.00 sec) # mysql --enable-cleartext-plugin -u apc_user -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 11 Server version: 5.5.32-31.0 Percona Server (GPL), Release rel31.0, Revision 549 Copyright (c) 2009-2013 Percona Ireland Ltd. Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> SELECT USER(), CURRENT_USER(); +--------------------+----------------+ | USER() | CURRENT_USER() | +--------------------+----------------+ | apc_user@localhost | apc_user@% | +--------------------+----------------+ 1 row in set (0.00 sec) |
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:
1 2 | # rpm -Uvh https://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm # yum install Percona-Server-devel-55 |
2. Install rpm-build, the package used to build RPMs
1 | # yum install rpm-build |
3. Download and install perl-DBD-MySQL Source RPM.
1 2 | # wget http://vault.centos.org/6.4/os/Source/SPackages/perl-DBD-MySQL-4.013-3.el6.src.rpm # rpm -Uvh perl-DBD-MySQL-4.013-3.el6.src.rpm |
The source will be installed under rpmbuild of the current directory.
4. Install compilers and dependencies:
1 | yum install Percona-Server-client-55 perl perl-DBI zlib-devel perl-ExtUtils-MakeMaker make gcc openssl-devel |
5. Build the RPM file:
1 2 | cd rpmbuild/SPECS/ rpmbuild -bb rpmbuild/SPECS/perl-DBD-MySQL.spec |
6. Install the RPM file:
1 | rpm -Uvh rpmbuild/RPMS/x86_64/perl-DBD-MySQL-4.013-3.el6.x86_64.rpm |
7. Test by creating a perl script and running it:
testpam.pl
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | #!/usr/bin/perl use DBI; $database="mysql"; $username="ap_user"; $password="ap_user_password"; $host="127.0.0.1"; $sql="SHOW TABLES"; $table=""; $dbh = DBI->connect("DBI:mysql:$database:$host", $username, $password) or die "$DBI::errstrn"; $query = $dbh->prepare($sql) or die "$dbh->errstrn"; $query->execute or die "$query->errstr"; while (@row = $query->fetchrow_array()) { $table = $row[0]; print "$tablen"; } |
1 2 3 4 5 | #chmod +x testpam.pl #perl testpam.pl DBI connect('mysql:127.0.0.1','ap_user',...) failed: Authentication plugin 'dialog' cannot be loaded: /usr/lib64/mysql/plugin/dialog.so: cannot open shared object file: No such file or directory at test.pl line 10 Authentication plugin 'dialog' cannot be loaded: /usr/lib64/mysql/plugin/dialog.so: cannot open shared object file: No such file or directory |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 | #yum install Percona-Server-server-55 #perl testpam.pl columns_priv db event func general_log help_category help_keyword help_relation help_topic host ndb_binlog_index plugin proc procs_priv proxies_priv servers slow_log tables_priv time_zone time_zone_leap_second time_zone_name time_zone_transition time_zone_transition_type user |
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:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 | # dpkg -L libdbd-mysql-perl /. … snipped for brevity … /usr/lib/perl5/auto/DBD/mysql/mysql.so #ldd /usr/lib/perl5/auto/DBD/mysql/mysql.so linux-vdso.so.1 => (0x00007fff943ff000) libmysqlclient.so.18 => /usr/lib/libmysqlclient.so.18 (0x00007f20517bf000) … snipped for brevity … libz.so.1 => /lib/x86_64-linux-gnu/libz.so.1 (0x00007f20504aa000) |
On the other hand, perl-DBD-MySQL of CentOS 6 needs to be recompiled because it depends on libmysqlclient.so.16:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 | # rpm -ql perl-DBD-MySQL /usr/lib64/perl5/Bundle … snipped for brevity … /usr/lib64/perl5/auto/DBD/mysql/mysql.so … snipped for brevity … /usr/share/man/man3/DBD::mysql::INSTALL.3pm.gz # ldd /usr/lib64/perl5/auto/DBD/mysql/mysql.so linux-vdso.so.1 => (0x00007fff847ff000) libmysqlclient.so.16 => /usr/lib64/libmysqlclient.so.16 (0x00007fa2471c3000) … snipped for brevity … libselinux.so.1 => /lib64/libselinux.so.1 (0x00007fa244730000) |
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:
1 2 | $ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1 $ mysql -h 127.0.0.1 -u apc_user |
Enable the plugin as an option. It’s supported on mysql, mysqladmin and mysqlslap.
1 | $ mysql --enable-cleartext-plugin -h 127.0.0.1 -u apc_user |
As a my.cnf config:
/etc/my.cnf
1 2 | [client] enable-cleartext-plugin |
1 | $ mysql -h 127.0.0.1 -u apc_user |
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:
1 2 | $ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1 $ /Applications/MySQLWorkbench.app/Contents/MacOS/MySQLWorkbench |
and on Linux:
1 2 | $ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1 $ mysql-workbench |
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.
Great post, very informative, I have tested Active Directory Authentication using MIT Kerberos (Ubuntu 12.04 LTS) with Percona PAM Plugin. I would love to post it here also.
http://www.routingswitching.com/ubuntu-12-04-3-lts-precise-mysql-5-5-active-directory-authentication/
Thanks for the info, very clear.
I see these instructions were for Centos 6
Should the Percona PAM authentication work on Centos 5 also?
Hi I am using LDAP, There is no password is stored in mysql , I am getting the below error — Error No:2059 , Authentication plugin ‘mysql_clear_password’ cannot be loaded: plugin not enabled , how to solve the problem ? , Through .net connector i need connect the mysql server, How to solve the problem.
If you want to know how to recompile LDAP with PHP, there are instructions at http://www.sheeri.org/ldap-with-auth_pam-and-php/
To recompile LDAP with Python’s MySQLdb connector, there are instructions at http://www.sheeri.org/ldap-with-auth_pam-and-python-to-authenticate-against-mysql/
There is also an explanation of why the MySQL connector provided by Oracle cannot be easily recompiled to add this functionality – because it does not rely on external libraries.