<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
</head>
<body bgcolor="#ffffff" text="#000000">
Maybe this isn't the correct list for this question as it has to do
more with SASL, but I am setting up a new mail server on a new box.
This is my 4th iteration of "starting fresh" using Cyrus Imap with
some sort of 3rd party database backend, using both LDAP and MySQL
in the past. This time I'm using Postgres for the hell of it (no
rhyme or reason... this is for my own system, not my employer).<br>
<br>
That being said, I am also by profession an IT security guy by
profession and of course follow the news regarding various web site
break-ins and what not.<br>
<br>
So when I look at the configuration for configuring Cyrus IMAP w/
SASL to query against my new PostgreSQL backend, I cringe. Why?
Because all of the examples I find require me to write an SQL query
using sql_query (or sasl_sql_query) that basically asks for the
plaintext password from the database.<br>
<br>
We recently went through a round of websites (e.g. gawker/gizmodo)
getting cracked and passwords being stolen because they stored their
passwords in cleartext. I would really like some sort of hashing,
but of course prefer something as strong as SHA256. PostgresSQL
supports this using the pgcrypto module, that's not the problem. It
seems that SASL (the thing with security in it's name) is actually
the limitation here.<br>
<br>
So given that it's been at least 6 years since it's been common
security practice to not store cleartext passwords in a database,
why does SASL still require it? Can't SASL be modified to accept
some token from the SQL query that basically says, "yes the password
you gave me matches" ??<br>
<br>
In the meantime, I have to write a PostgreSQL function that takes
the username and password, does the comparison to the hashed
password in the database, and if it matches, return the same
password that was passed to it in the first place. Honestly, I
shouldn't have to do this.<br>
<br>
Here's a sample of one I created based on some help from the
Postgres manual. This assumes you have separate columns for the
USER and REALM and a custom hashing function that will compare the
supplied password to that in the database, but using the md5()
function will work. It will return the password SASL supplied to it
if the SELECT was successful, otherwise returns blank (just as
sql_query would expect).<br>
<blockquote><tt>CREATE OR REPLACE FUNCTION userAuthenticate(TEXT,
TEXT, TEXT)</tt><br>
<tt>RETURNS TEXT AS $$</tt><br>
<tt>DECLARE passed TEXT;</tt><br>
<tt>BEGIN</tt><br>
<tt> SELECT $3 INTO passed</tt><br>
<tt> FROM passwd</tt><br>
<tt> WHERE username = $1 AND domain = $2 AND password =
your_hashing_function_here($3)</tt><br>
<tt> RETURN passed;</tt><br>
<tt>END;</tt><br>
<tt>$$ LANGUAGE plpgsql</tt><br>
<br>
</blockquote>
---- in your SASL configuration file for the service ----<br>
<blockquote><tt>sql_query: select userAuthenticate('%u','%r','%p')<br>
</tt></blockquote>
<br>
This is best classified as a "trick" but is more of a "hack", IMO,
but it works. Please do not contact me for support of this code.
It works for me, you may need to do some research and figure out
what works for you.'<br>
<br>
<br>
</body>
</html>