Sunday, October 27, 2013

Using the PAM authentication plugin

The procedure for using the PAM authentication plugin as documented doesn't work flawlessly on Ubuntu.

So here is how it works on Ubuntu (and probably also on other Debian based systems).

Please note that the PAM authentication plugin is an enterprise feature.

1. Make sure the plugin is loaded

This can be done by adding the following to the mysqld section of my.cnf (Don't forget to restart). You could also use INSTALL PLUGIN to load it without restart.
plugin-load=authentication_pam.so
2.  Add a user which will use the plugin
mysql> CREATE USER 'dveeden'@'localhost' IDENTIFIED WITH authentication_pam;
Query OK, 0 rows affected (0.00 sec)
3. Add a pam config file for 'mysql':
Create /etc/pam.d/mysql with the following contents:
@include common-auth
@include common-account
@include common-session-noninteractive
4. Login with the user
mysql -p --enable-cleartext-plugin
5. Verify if you're really connected as the correct user.
mysql> select user(),current_user(),@@proxy_user;
+-------------------+-------------------+--------------+
| user()            | current_user()    | @@proxy_user |
+-------------------+-------------------+--------------+
| dveeden@localhost | dveeden@localhost | NULL         |
+-------------------+-------------------+--------------+
1 row in set (0.00 sec)
If some step doesn't work then the /var/log/auth.log file can be very helpfull.
 
The plugin has many more options. It allows you to proxy users and use different PAM configurations for different users. The plugin is used on a per user basis so you could use native authentication for your application users and PAM authentication with LDAP for administrators and/or developers.
 
Please note that SHOW GRANTS does not indicate a authentication plugin and blindly copying the grant statements to another server to copy the user might result in users without password.
 
MySQL Utilities:
$ mysqluserclone  --source=usr:pwd@srv -d 'dveeden'@'localhost'
# Source on 127.0.0.1: ... connected.
# Dumping grants for user dveeden@localhost
GRANT USAGE ON *.* TO 'dveeden'@'localhost'
Percona Toolkit:
$ pt-show-grants | grep dveeden
-- Grants for 'dveeden'@'localhost'
GRANT USAGE ON *.* TO 'dveeden'@'localhost';

No comments:

Post a Comment