Hello!
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.
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
symmetric encryption
<http://www.postgresql.org/docs/8.4/static/pgcrypto.html#AEN116049>.
This way my database dumps are useless to any "attacker" who does not
/also/ have access to my saslpasswd.conf:
sql_select: select pgp_sym_decrypt(%p, '/mykey/') from users
where name = '%u'
sql_insert: insert into users (name, %p) values ('%u',
pgp_sym_encrypt('%v', '/mykey/'))
sql_update: update users set %p = pgp_sym_encrypt('%v',
'/mykey/') where name = '%u'
Unfortunately, the PGSQL back-end could only use this cleverness for
routine login authentication. Attempts to use saslpasswd2 to add/modify
user-credentials fail on several fronts:
1.
The back-end first tries to use the sql_select-template with %p
replaced by a * -- to determine, whether the given user is already
listed or not, and, thus, whether it should use insert or update
to change anything. (This seems strange, because the -c flag of
saslpasswd2 is supposed to tell it...) Using the * for %p results
in an invalid query:
select pgp_sym_decrypt(*, '/mykey/') from users where name = 'mi'
which throws things off-track right at the beginning. This problem
I was able to solve by using userPassword (the column, that is
documented as mandatory anyway in the documentation) instead of *:
--- plugins/sql.c 2009-04-28 11:09:17.000000000 -0400
+++ plugins/sql.c 2010-11-16 23:38:41.000000000 -0500
@@ -58,5 +58,5 @@
static const char * SQL_BLANK_STRING = "";
-static const char * SQL_WILDCARD = "*";
+static const char * SQL_WILDCARD = "userPassword";
static const char * SQL_NULL_VALUE = "NULL";
2. The next problem is harder to solve and it breaks even the
documented
<http://www.sendmail.org/%7Eca/email/cyrus2/options.html>
examples, such as:
sql_insert: INSERT INTO user_table (username, realm, %p) VALUES ('%u',
'%r', '%v')
The problem is the column-names (the %p), which are comprised of
prefix "cmusaslsecret" and the name of the mechanism used:
INSERT INTO user_table (username, realm, cmusaslsecretCRAM-MD5) VALUES
('mi', 'foo', 'bar')
is /invalid/ syntax -- because *SQL-column /names/ can not have
dashes in them*. At least, in PostgreSQL that's the case...
At this point I gave up and simply inserted the necessary rows into the
table directly -- bypassing saslpasswd2. I can live with that -- the
sql_select template above works for authentication, which is enough --
but someone may wish to revisit the SQL back-end...
Yours,
-mi
