Pam mysql - Revision history

Leaky at 15:28, 1 November 2013

2013-11-01T15:28:39Z

Leaky: Added configuration examples and step by step info

2013-11-01T05:23:51Z

Added configuration examples and step by step info

Leaky: How to configure pam_mysql for Cyrus IMAP use

2013-10-31T21:48:39Z

How to configure pam_mysql for Cyrus IMAP use

New page

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

@@ Line 7: / Line 7: @@
 CentOS 6:
-  yum install pam_mysql mysql-server
+  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
+  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.
@@ Line 25: / Line 25: @@
 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';
+  GRANT SELECT ON system.users TO 'pam_user'@'localhost' IDENTIFIED BY 'pam_password';
 Insert a test record into the database table.
@@ Line 90: / Line 90: @@
 Configure saslauthd to use pam for authentication instead of sasldb
-CentOS 6: Edit /etc/sysconfig/saslauthd and change the MECH line so it reads
+CentOS 6: Edit /etc/sysconfig/saslauthd and change the MECH and FLAGS lines so they read
   MECH=pam
-Ubuntu: Edit /etc/default/saslauthd and change the MECHANISMS line
+Ubuntu: Edit /etc/default/saslauthd and change the MECHANISMS and OPTIONS lines
   MECHANISMS="pam"
 Finally to test the connection you should be able to run the following command.
@@ Line 104: / Line 108: @@
 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

← Older revision		Revision as of 05:23, 1 November 2013
Line 2:		Line 2:

	The latest version of pam mysql at the time of writing is 0.7rc1		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