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

Jaime Sicam · August 22, 2013 at 4:27 pm

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

# 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%]

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

# 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!

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

# service mysql start
Starting MySQL (Percona Server)...... SUCCESS!

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.

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)

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;

+--------------------------------+----------+--------------------+--------------------+---------+

+--------------------------------+----------+--------------------+--------------------+---------+

…

snipped for brevity

…

+--------------------------------+----------+--------------------+--------------------+---------+

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:

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)

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:

auth       required     pam_warn.so
auth       required     pam_unix.so audit
account    required     pam_unix.so audit

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

#chgrp mysql /etc/shadow
#chmod g+r /etc/shadow

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

#useradd ap_user
#passwd ap_user
#useradd apc_user
#passwd apc_user

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:

# 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)

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

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

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:

# rpm -Uvh https://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm
# yum install Percona-Server-devel-55

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

# yum install rpm-build

1	# yum install rpm-build

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

# 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

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:

yum install Percona-Server-client-55 perl perl-DBI zlib-devel perl-ExtUtils-MakeMaker  make gcc openssl-devel

1	yum install Percona-Server-client-55 perl perl-DBI zlib-devel perl-ExtUtils-MakeMaker make gcc openssl-devel

5. Build the RPM file:

cd rpmbuild/SPECS/
rpmbuild -bb rpmbuild/SPECS/perl-DBD-MySQL.spec

1 2	cd rpmbuild/SPECS/ rpmbuild -bb rpmbuild/SPECS/perl-DBD-MySQL.spec

6. Install the RPM file:

rpm -Uvh rpmbuild/RPMS/x86_64/perl-DBD-MySQL-4.013-3.el6.x86_64.rpm

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

#!/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

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";

}

#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

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

#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

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:

# 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)

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:

# 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 =&gt; (0x00007fff847ff000)
libmysqlclient.so.16 =&gt; /usr/lib64/libmysqlclient.so.16 (0x00007fa2471c3000)
…
snipped for brevity
…
libselinux.so.1 =&gt; /lib64/libselinux.so.1 (0x00007fa244730000)

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:

$ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
$ mysql -h 127.0.0.1 -u apc_user

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.

$ mysql --enable-cleartext-plugin -h 127.0.0.1 -u apc_user

1	$ mysql --enable-cleartext-plugin -h 127.0.0.1 -u apc_user

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

[client]
enable-cleartext-plugin

1 2	[client] enable-cleartext-plugin

$ mysql -h 127.0.0.1 -u apc_user

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:

$ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
$ /Applications/MySQLWorkbench.app/Contents/MacOS/MySQLWorkbench

1 2	$ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1 $ /Applications/MySQLWorkbench.app/Contents/MacOS/MySQLWorkbench

and on Linux:

$ export LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN=1
$ mysql-workbench

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.

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