<!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">
Hello!<br>
<br>
<div align="justify">Setting up a (shiny) new IMAP server, I decided
to use PostgreSQL for all (or most) of the e-mail related things
-- including even the Bayesian anti-spam data -- so as to see,
what stats I may later be able to gather with creative joins.<br>
<br>
However, storing the passwords in clear-text bothered me... There
being no obvious way to store just the hashes of them (like BSD's
master.passwd or Linux' shadow do), I decided to, at least, use <a
href="http://www.postgresql.org/docs/8.4/static/pgcrypto.html#AEN116049">symmetric
encryption</a>. This way my database dumps are useless to any
"attacker" who does not <i>also</i> have access to my <tt>saslpasswd.conf</tt>:<br>
</div>
<blockquote><tt>sql_select: select pgp_sym_decrypt(%p, '<i>mykey</i>')
from users where name = '%u'<br>
sql_insert: insert into users (name, %p) values ('%u',
pgp_sym_encrypt('%v', '<i>mykey</i>'))<br>
sql_update: update users set %p = pgp_sym_encrypt('%v', '<i>mykey</i>')
where name = '%u'</tt><br>
</blockquote>
<div align="justify">Unfortunately, the PGSQL back-end could only
use this cleverness for routine <tt>login</tt> authentication.
Attempts to use <tt>saslpasswd2</tt> to add/modify
user-credentials fail on several fronts:<br>
</div>
<ol>
<li>
<div align="justify">The back-end first tries to use the <tt>sql_select</tt>-template
with <tt>%p</tt> replaced by a <tt>*</tt> -- to determine,
whether the given user is already listed or not, and, thus,
whether it should use <tt>insert</tt> or <tt>update</tt> to
change anything. (This seems strange, because the <tt>-c</tt>
flag of <tt>saslpasswd2</tt> is supposed to tell it...) Using
the <tt>*</tt> for <tt>%p</tt> results in an invalid query:<br>
</div>
<blockquote><tt>select pgp_sym_decrypt(*, '<i>mykey</i>') from
users where name = 'mi'</tt><br>
</blockquote>
<div align="justify">which throws things off-track right at the
beginning. This problem I was able to solve by using <tt>userPassword</tt>
(the column, that is documented as mandatory anyway in the
documentation) instead of <tt>*</tt>:<br>
</div>
<blockquote><tt>--- plugins/sql.c 2009-04-28
11:09:17.000000000 -0400<br>
+++ plugins/sql.c 2010-11-16 23:38:41.000000000 -0500<br>
@@ -58,5 +58,5 @@<br>
<br>
static const char * SQL_BLANK_STRING = "";<br>
<font color="#ff0000">-static const char * SQL_WILDCARD =
"*";</font><br>
<font color="#33cc00">+static const char * SQL_WILDCARD =
"userPassword";</font><br>
static const char * SQL_NULL_VALUE = "NULL";<br>
</tt><br>
</blockquote>
</li>
<li>The next problem is harder to solve and it breaks even the <a
href="http://www.sendmail.org/%7Eca/email/cyrus2/options.html">documented</a>
examples, such as:<br>
<blockquote>
<pre><tt>sql_insert: INSERT INTO user_table (username, realm, %p) VALUES ('%u', '%r', '%v')</tt>
</pre>
</blockquote>
<div align="justify">The problem is the column-names (the <tt>%p</tt>),
which are comprised of prefix "cmusaslsecret" and the name of
the mechanism used:<br>
</div>
<blockquote>
<pre><tt>INSERT INTO user_table (username, realm, cmusaslsecretCRAM-MD5) VALUES ('mi', 'foo', 'bar')
</tt></pre>
</blockquote>
<div align="justify">is <i>invalid</i> syntax -- because <b>SQL-column
<i>names</i> can not have dashes in them</b>. At least, in
PostgreSQL that's the case...<br>
</div>
</li>
</ol>
<div align="justify">At this point I gave up and simply inserted the
necessary rows into the table directly -- bypassing <tt>saslpasswd2</tt>.
I can live with that -- the <tt>sql_select</tt> template above
works for authentication, which is enough -- but someone may wish
to revisit the SQL back-end...<br>
</div>
<br>
Yours,<br>
<blockquote>-mi</blockquote>
</body>
</html>