Issues with PostgreSQL-hosted user table and saslpasswd

[Mikhail T.]

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

-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.andrew.cmu.edu/pipermail/cyrus-sasl/attachments/20101117/630e54af/attachment.html