Patching courier-authlib to enable password check in mysql database (md5, sha, crypt) with gentoo portage overlay

Intruduction

My first problem was that I had a mysql database with my users that have md5 crypted passwords, and I wanted an imap server who will authenticate with this credential.

I've choosen courier-imap. (Maybe that was my only problem)

However courier-authlib provide mysql and also MYSQL_SELECT_CLAUSE whitch can be customized for your need, it does not allow the mysql to compare the passwords, and the algorithms provided by the authlib does not match with the ones in mysql. (The {MD5} prefix for the authlib will produce the base64 encode of the md5, the database provide the md5 represented on hexadecimals. You may write a function, that convert the hex digits to binary, and than an other that will base64 encode the value, but would be more difficult than fixing the courier.)

The Patch

So, I have extend the authmysqllib to be able to substitute password to the MYSQL_SELECT_CLAUSE. So that you can write queries like: 


MYSQL_SELECT_CLAUSE \
        SELECT c.name, \
                NULL, \
                '$(password)', \ # -- this will be match by authlib: I did not want to rewrite the whole code.
                8, \
                12, \
                '/var/mail/$(domain)', \
                '$(local_part)', \
                0, \
                '', \
                '' \
        FROM \
                email e \
                JOIN credential c ON e.credential_id = c.id \
        WHERE \
                '$(local_part)@$(domain)' = e.address \
                AND c.password = md5('$(password)')

I have used courier-authlib-0.58 whitch was the latest stable in the time.
I provide the patch later.

The power of Gentoo

This is the first time I see the power of the gentoo-portage since I use Gentoo, whitch was a month already.
You are able to patch a package (portage), so it will be compiled for you with the patch.

What you need is Overlay. Portage offers you an opportunity to create custom packag handlers (called ebuilds). You have to define the overlay base directory PORTDIR_OVERLAY in the /etc/build.conf. Usualy:
PORTDIR_OVERLAY="/usr/local/portage"

In the overlay dir you can mirror packages like this:

1. Create the directories 

# mkdir -p /usr/local/portage/net-libs/courier-authlib/ (which is the mirror of /usr/portage/net-libs/courier-authlib)

2. copy 'files' folder 

# cd /usr/portage/net-libs/courier-authlib/; tar cf - files | ( cd /usr/local/portage/net-libs/courier-authlib/; tar xpf - )

3. duplicate the ebuild 

# cp /usr/portage/net-libs/courier-authlib/courier-authlib-0.58.ebuild /usr/local/portage/net-libs/courier-authlib/

4. create digest from the ebuild (A Manifest file to the directory of the given ebuild) 

# ebuild /usr/local/portage/net-libs/courier-authlib/courier-authlib-0.58.ebuild digest

Now if you emerge the portage it will work from your overlay directory. See 

# emerge --pretend --verbose courier-authlib

The next step is to apply the patch.

5. Copy the patch to the 'files' directory 

# cp ~/prampec.patch /usr/local/portage/net-libs/courier-authlib/files/

6. Edit the ebuild to apply the patch before compile. I not know mutch about ebuild but my guess to put it to the src_unpack() function was right.
So, I have added the following line to the src_unpack() function lines. (e.g. as the last line): 

epatch ${FILESDIR}/prampec.patch

7. repeat step 4 

# ebuild /usr/local/portage/net-libs/courier-authlib/courier-authlib-0.58.ebuild digest

8. rebuild the portage 

# emerge net-libs/courier-authlib

It took me two days. I hope this will spare some time for you.

Regards,
Balázs Kelemen
http://www.sdkmi.hu/dev

Here I my patch for courier-authlib: 


diff -r -U2 ./authmysql.c ../courier-authlib-0.58-prampec/authmysql.c
--- ./authmysql.c	2005-07-10 02:49:20.000000000 +0200
+++ ../courier-authlib-0.58-prampec/authmysql.c	2006-11-03 22:41:27.073865750 +0100
@@ -47,5 +47,6 @@
 	}
 
-	authinfo=auth_mysql_getuserinfo(user, service);
+	authinfo=auth_mysql_getuserinfo(user, service,
+					pass); /* prampec@gmail.com */
 
 	if (!authinfo)		/* Fatal error - such as MySQL being down */
@@ -109,5 +110,6 @@
 	struct authmysqluserinfo *authinfo;
 
-	authinfo=auth_mysql_getuserinfo(user, service);
+	authinfo=auth_mysql_getuserinfo(user, service,
+					pass); /* prampec@gmail.com */
 
 	if (!authinfo)
diff -r -U2 ./authmysql.h ../courier-authlib-0.58-prampec/authmysql.h
--- ./authmysql.h	2005-07-10 02:49:20.000000000 +0200
+++ ../courier-authlib-0.58-prampec/authmysql.h	2006-11-03 22:26:50.751099000 +0100
@@ -23,4 +23,5 @@
 
 extern struct authmysqluserinfo *auth_mysql_getuserinfo(const char *,
+							const char *, /* prampec@gmail.com */
 							const char *);
 extern void auth_mysql_cleanup();
diff -r -U2 ./authmysqllib.c ../courier-authlib-0.58-prampec/authmysqllib.c
--- ./authmysqllib.c	2005-07-13 02:54:57.000000000 +0200
+++ ../courier-authlib-0.58-prampec/authmysqllib.c	2006-11-04 00:37:45.381982750 +0100
@@ -571,5 +571,6 @@
 static char *parse_select_clause (const char *clause, const char *username,
 				  const char *defdomain,
-				  const char *service)
+				  const char *service,
+				  const char *pass) /* prampec@gmail.com */
 {
 static struct var_data vd[]={
@@ -577,4 +578,5 @@
 	    {"domain",		NULL,	sizeof("domain"),	0},
 	    {"service",		NULL,	sizeof("service"),	0},
+	    {"password",	NULL,	sizeof("password"),	0}, /* prampec@gmail.com */
 	    {NULL,		NULL,	0,			0}};
 
@@ -588,4 +590,5 @@
 		return NULL;
 	vd[2].value     = service;
+	vd[3].value     = pass; /* prampec@gmail.com */
 
 	return (parse_string (clause, vd));
@@ -621,5 +624,6 @@
 
 struct authmysqluserinfo *auth_mysql_getuserinfo(const char *username,
-						 const char *service)
+						 const char *service,
+						 const char *pass) /* pampec@gmail.com */
 {
 const char *defdomain	=NULL;
@@ -747,5 +751,6 @@
 		/* siefca@pld.org.pl */
 		querybuf=parse_select_clause (select_clause, username,
-					      defdomain, service);
+					      defdomain, service,
+					      pass); /* prampec@gmail.com */
 		if (!querybuf)
 		{
@@ -755,5 +760,15 @@
 	}
 
-	DPRINTF("SQL query: %s", querybuf);
+	/* prampec@gmail.com */
+	if((strstr(select_clause, "$password") == NULL) ||
+	   (courier_authdebug_login_level < 2))
+	{
+		DPRINTF("SQL query: %s", querybuf);
+	}
+	else
+	{
+		DPRINTF("SQL query is hide, because it contains passwords. Use DEBUG_LOGIN=2 in authdaemonrc to show passwords.");
+	}
+
 	if (mysql_query (mysql, querybuf))
 	{
@@ -1094,5 +1109,6 @@
 		/* siefca@pld.org.pl */
 		querybuf=parse_select_clause (select_clause, "*",
-					      defdomain, "enumerate");
+					      defdomain, "enumerate",
+					      ""); /* prampec@gmail.com */
 		if (!querybuf)
 		{
diff -r -U2 ./preauthmysql.c ../courier-authlib-0.58-prampec/preauthmysql.c
--- ./preauthmysql.c	2004-10-21 02:10:49.000000000 +0200
+++ ../courier-authlib-0.58-prampec/preauthmysql.c	2006-11-03 22:49:14.663088250 +0100
@@ -29,5 +29,6 @@
 struct	authinfo	aa;
 
-	authinfo=auth_mysql_getuserinfo(user, service);
+	authinfo=auth_mysql_getuserinfo(user, service,
+					""); /* prampec@gmail.com */
 
 	if (!authinfo)		/* Fatal error - such as MySQL being down */

( prmpec | 2006. 11. 05., v – 09:29 )

Note that the crypting algoriths of the mysql are not really standard. (expect for the encrypt() )
Most of the applications like to use md5-hash, and sha1-hash, but mysql produces hex coded results. Which is not very kind at all. Forthermore mysql does not support a base64 codec, so you can not create hashed passwords inside mysql.

If you could authlib has the solution. See the auth_generic manual: 


...
The account's encrypted password, if available. If the account has a cleartext password defined, this field can be set to NULL. The encrypted password can take several formats:


    

  • A traditional triple-DES crypted password, or a MD5+salt-hashed password, as used in Linux.
    • 

  • "{MD5}" followed by a base64-encoded MD5 hash of the password.
    • 

  • "{SHA}" followed by a base64-encoded SHA1 hash of the password.

( prmpec | 2006. 11. 05., v – 12:43 )

Note, that postgresql use the same method: md5() function will display the hex value of the hash. But in postgresql you can reproduce the base64 representation of the hash easily: 

select encode(decode(md5('some text'),'hex'), 'base64');

Mysql also has a decode function for hexadecimal values called unhex. But unfortunetly does not have a base64 encoder. Ian Gulliver has wrote a base64 hask for mysql, but it works only for mysql5 (what I do not have). See: http://www.planetmysql.org/entries/2326

So I you have mysql5 (with the right version) you don't need my patch. What you need is the base64 function of Ian, and you can create the base64 coded md5 like this: 

select BASE64_ENCODE(unhex(md5('some text')));

I can not say anything about performance, but stored procedure data manipulation is surely slow.