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. 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:
-
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";
- The next problem is harder to solve and it breaks even the documented
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
|
