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 cyrus-sasl cyrus-imapd cyrus-imapd-perl cyrus-imapd-utils

Ubuntu Server 12.04:

apt-get install libpam-mysql mysql-server sasl2-bin cyrus-admin cyrus-imapd libcyrus-imap-perl cyrus-clients

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'@'localhost' 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 and FLAGS lines so they read

MECH=pam
FLAGS="-r"

Ubuntu: Edit /etc/default/saslauthd and change the MECHANISMS and OPTIONS lines

MECHANISMS="pam"
OPTIONS="-r -c -m /var/run/saslauthd"

(the OPTIONS line just needs -r adding to whatever else is already there)

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 testing using testsaslauthd is successful, the next step is to test the IMAP login itself.

$ imtest -m login -u testuser -a testuser localhost

When prompted for the password, enter testpassword and with any luck, it should say that you're authenticated.

S: * OK [CAPABILITY IMAP4rev1 LITERAL+ ID ENABLE AUTH=PLAIN SASL-IR] ubuntu Cyrus IMAP v2.4.12-Debian-2.4.12-2 server ready
Please enter your password: 
C: L01 LOGIN testuser {12}
S: + go ahead
C: <omitted>
S: L01 OK [CAPABILITY IMAP4rev1 LITERAL+ ID ENABLE ACL
RIGHTS=kxte QUOTA MAILBOX-REFERRALS NAMESPACE UIDPLUS
NO_ATOMIC_RENAME UNSELECT CHILDREN MULTIAPPEND BINARY
CATENATE CONDSTORE ESEARCH SORT SORT=MODSEQ SORT=DISPLAY
THREAD=ORDEREDSUBJECT THREAD=REFERENCES ANNOTATEMORE
LIST-EXTENDED WITHIN QRESYNC SCAN XLIST URLAUTH 
URLAUTH=BINARY LOGINDISABLED COMPRESS=DEFLATE IDLE]
User logged in SESSIONID=<cyrus-5584-1383318167-1>
Authenticated.
Security strength factor: 0

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