SASL w/ Encrypted SQL Password Security (Comment, Suggestion and Possible Solution)

"Raymond T. Sundland" <raymond@xxxxxxxxxxxx> · Tue, 25 Jan 2011 12:48:38 -0500



    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).

    
    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.

    
    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.

    
    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.

    
    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" ??

    
    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.

    
    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).

    CREATE OR REPLACE FUNCTION userAuthenticate(TEXT,
        TEXT, TEXT)

      RETURNS TEXT AS $$

      DECLARE passed TEXT;

      BEGIN

              SELECT  $3 INTO passed

              FROM    passwd

              WHERE   username = $1 AND domain = $2 AND password =
        your_hashing_function_here($3)

              RETURN passed;

      END;

      $$  LANGUAGE plpgsql

      
    ---- in your SASL configuration file for the service ----

    sql_query: select userAuthenticate('%u','%r','%p')

      
    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.'

    
----
Cyrus Home Page: http://www.cyrusimap.org/
List Archives/Info: http://lists.andrew.cmu.edu/pipermail/info-cyrus/