Pam mysql

Configuration of pam_mysql allows you to use virtual users for things like Cyrus IMAP, and combined with nss_mysql you can also create real system accounts that can login, run programs, etc.

The latest version of pam mysql at the time of writing is 0.7rc1

Ensure that you have installed the pam_mysql package for your system, together with a MySQL server. Securing your MySQL installation is not covered here.

CentOS 6:

yum install pam_mysql mysql-server

Ubuntu Server 12.04:

apt-get install libpam-mysql mysql-server

Create a suitable MySQL database and table, together with a MySQL user account with access to the table. For the database table, all you really need are the username and password fields, but in this example I'm adding an active column so it's easy to disable an account.

CREATE DATABASE `system`;
CREATE TABLE `users` (
  `username` char(200) NOT NULL,
  `password` char(40) NOT NULL,
  `active` tinyint(1) DEFAULT '1',
  PRIMARY KEY (`username`)
);

Next we need to give SELECT access to the database table - if you give the user UPDATE access as well, you can use the pam_mysql functionality that allows changing of passwords too. If not, you'll have to provide some alternative means for users to change their password (such as a control panel that manipulates the database record directly).

GRANT SELECT ON 'system'.'users' TO 'pam_user' IDENTIFIED BY 'pam_password';

Insert a test record into the database table.

INSERT INTO users SET username='testuser',password=ENCRYPT('testpassword','$1$Salt8Chr');

The salt used should be different for every user - a quick bit of SQL to produce a suitable, but not perfect (all lowercase) salt is as follows.

CONCAT("$1$", LOWER(SUBSTRING((CONV(SUBSTRING(RAND(), 3), 10, 36)), 2, 8)))

The output from MySQL ENCRYPT() when passed a salt beginning $1$ should be something like $1$Salt8Chr$fxC.KclNAH1ky5qvmI4l61 - if it only returns $1FFuPaAQqeIM then your MySQL build doesn't support MD5 passwords. I've found this is the case on OSX 10.9 (Mavericks) but it works fine in the default MySQL package for CentOS 6.

After setting up the database table, you will need to update the PAM configuration for the relevant service so that it knows to use the pam_mysql module.

CentOS 6 - edit /etc/pam.d/imap and add the two lines containing pam_mysql.so - the rest of the config shown below is the default from CentOS 6.4

#%PAM-1.0
auth       required     pam_nologin.so
auth       sufficient    pam_mysql.so config_file=/etc/pam-mysql.conf
auth       include      system-auth

account    sufficient    pam_mysql.so config_file=/etc/pam-mysql.conf
account    include      system-auth

password   required pam_deny.so
session    include      system-auth

For Ubuntu Server 12.04 it's the same file but the content is different.

# PAM configuration file for Cyrus IMAP service
#
# If you want to use Cyrus in a setup where users don't have
# accounts on the local machine, you'll need to make sure
# you use something like pam_permit for account checking.
#
# Remember that SASL (and therefore Cyrus) accesses PAM 
# modules through saslauthd, and that SASL can only deal with
# plaintext passwords if PAM is used.
#

auth       sufficient    pam_mysql.so config_file=/etc/pam-mysql.conf
@include common-auth

account    sufficient    pam_mysql.so config_file=/etc/pam-mysql.conf
@include common-account

In both cases, the line added is the same and simply refers pam_mysql to the configuration file /etc/pam-mysql.conf which will be created next.

verbose = 0;

users.host = localhost;
users.database = system;
users.db_user = pam_user;
users.db_passwd  = pam_password;
users.password_crypt = 1;
users.use_md5 = Y;

users.table = users;
users.user_column = username;
users.password_column = password;

users.where_clause = active = 1;

Configure saslauthd to use pam for authentication instead of sasldb

CentOS 6: Edit /etc/sysconfig/saslauthd and change the MECH line so it reads

MECH=pam

Ubuntu: Edit /etc/default/saslauthd and change the MECHANISMS line

MECHANISMS="pam"

Finally to test the connection you should be able to run the following command.

$ testsaslauthd -u testuser -p testpassword -s imap
0: OK "Success."

The -s imap tells it which service to use, change imap to whichever pam.d file you were editing and obviously use a different username/password if you didn't insert that same test record as above into the database.

If you are experiencing problems, pam_mysql should come with a readme file documenting the various options - the CentOS package locates it in /usr/share/doc/pam_mysql-0.7/README There should also be log entries logged via syslog to (probably) /var/log/secure or /var/log/messages