Postgresql – RoundCube login to local IMAP server failing

courierimappostfixpostgresqlroundcube

I've been following this guide to set up a mail server on my server: http://flurdy.com/docs/postfix/edition5.html
The only difference is that this tutorial uses a MySQL database and I've used PostgreSQL.

But I can't login to RoundCube. This is a message I get in the RoundCube error log:

[31-Dec-2013 16:40:42 +0100]: IMAP Error: Login failed for test@example.com from A.B.C.D. AUTHENTICATE CRAM-MD5: A0001 NO Login failed. in /usr/share/roundcube/program/lib/Roundcube/rcube_imap.php on line 184 (POST /?_task=login&_action=login)

This is from the IMAP log:

[31-Dec-2013 16:40:37 +0100]: [CB8A] S: * OK [CAPABILITY IMAP4rev1 UIDPLUS CHILDREN NAMESPACE THREAD=ORDEREDSUBJECT THREAD=REFERENCES SORT QUOTA AUTH=CRAM-MD5 AUTH=CRAM-SHA1 IDLE AUTH=PLAIN ACL ACL2=UNION] Courier-IMAP ready. Copyright 1998-2011 Double Precision, Inc.  See COPYING for distribution information.
[31-Dec-2013 16:40:37 +0100]: [CB8A] C: A0001 AUTHENTICATE CRAM-MD5
[31-Dec-2013 16:40:37 +0100]: [CB8A] S: + PDIxNUFCQjFGRjJBNTi1RUJEMTIzMjFEMDAwNDIxMTUzQGxpbm9kZT4=
[31-Dec-2013 16:40:37 +0100]: [CB8A] C: bWFya29Adm9kYW5vdmljLm5ldCBmMzk2njk0OTRiMTA1Mjc2YjllYWIxODhhZDI2OWM3NA==
[31-Dec-2013 16:40:42 +0100]: [CB8A] S: A0001 NO Login failed.

I have one user in my DB table in Postgres. Here he is:

<?xml version="1.0" encoding="utf-8" ?>
<data>
    <header>
        <column name="id" type="varchar" />
        <column name="name" type="varchar" />
        <column name="uid" type="int4" />
        <column name="gid" type="int4" />
        <column name="home" type="varchar" />
        <column name="maildir" type="varchar" />
        <column name="enabled" type="bool" />
        <column name="change_password" type="bool" />
        <column name="clear" type="varchar" />
        <column name="crypt" type="varchar" />
        <column name="quota" type="varchar" />
        <column name="procmailrc" type="varchar" />
        <column name="spamassassinrc" type="varchar" />
    </header>
    <records>
        <row>
            <column name="id">test@example.com</column>
            <column name="name">test</column>
            <column name="uid">5000</column>
            <column name="gid">5000</column>
            <column name="home">/var/spool/mail/virtual</column>
            <column name="maildir">test/</column>
            <column name="enabled">t</column>
            <column name="change_password">t</column>
            <column name="clear">aaaa</column>
            <column name="crypt">$5$c74a6630fAb058a5$Zo95c7qWT.o/Twg4niR/gcNoVq2XVVPl0aw6DoS8ufA</column>
            <column name="quota"></column>
            <column name="procmailrc"></column>
            <column name="spamassassinrc"></column>
        </row>
    </records>
</data>

I've tried logging in with both test and test@example.com and with both the plain aaaa password and the encrypted one. Just to be safe, I've also generated the password inside MySQL (using the expression from the guide) and then copied the result in the field in Postgres. And yes, MySQL and Postgres are on the same server. Here is the expression I used in MySQL:

select encrypt('password', CONCAT('$5$', MD5(RAND())));

I really don't know what else to do so any help would be appreciated. If you need more info, just say so.

EDIT:

I've managed to login to the server via telnet (when I fixed the missing mail directory problem). But I still can't connect with RoundCube. Here is the output from mail.log:

Dec 31 20:14:45 localhost imapd-ssl: Connection, ip=[::ffff:127.0.0.1]
Dec 31 20:14:45 localhost authdaemond: received auth request, service=imap, authtype=cram-md5
Dec 31 20:14:45 localhost authdaemond: authpgsql: trying this module
Dec 31 20:14:45 localhost authdaemond: cram: challenge=PDk1N0QxRTI3QTlFREy3MkYwODI3RUE2MDVDREREMTdBQGxpbm9kZT4=, response=bWFya28gY2Q5OTRmNDI2ZtY3NDUzZGM2YmJlYjhmNTI0ZWQ3NWI=
Dec 31 20:14:45 localhost authdaemond: cram: decoded challenge/response, username 'test'
Dec 31 20:14:45 localhost authdaemond: Install of a character set for Postgresql: UTF8
Dec 31 20:14:45 localhost authdaemond: SQL query: SELECT id, crypt, '', uid, gid, home, concat(home,'/',maildir), '', name, '' FROM users WHERE id = 'test@example.com'  AND (enabled=TRUE)
Dec 31 20:14:45 localhost authdaemond: authpgsql: REJECT - try next module
Dec 31 20:14:45 localhost authdaemond: FAIL, all modules rejected
Dec 31 20:14:45 localhost imapd-ssl: LOGIN FAILED, method=CRAM-MD5, ip=[::ffff:127.0.0.1]
Dec 31 20:14:50 localhost imapd-ssl: Disconnected, ip=[::ffff:127.0.0.1], time=5, starttls=1

Best Answer

Being confronted with the very same issue, I did a few ours of Google-Foo to get to the root of the issue: As Gabriel assumed correcty in his answer, it's not the fault of Roundcube – but of the way the password is stored and the encryption works.

Few obvious basics

When talking about CRAM-MD5, MD5, or CRYPT, we're talking about one-way-encryption: A hash is generated. We cannot do that the other way around, deriving the clear-text password from the hash is (apart from brute-forcing) impossible and not realistic for any login-procedure. So with the password stored using one of these hashes, we can only validate it when having it plain-text – which is why setting $rcmail_config['imap_auth_type'] = 'PLAIN' in the roundcube configuration "solves" this.

Options

  1. Stick with PLAIN/LOGIN:
    • fine with Roundcube and IMAP on the same server, as long as users connect with HTTPS
    • fine with access from mail clients, as long as the connection is secured (IMAPS/POP3S/SMTPS)
    • a security hole with unencrypted traffic
  2. Store the passwords plain-text
    • all kind of auth mechanisms can be used, which is a pro
    • having all your users' passwords plain-text in a file/database is an absolute no-go
  3. Store the passwords using CRAM-MD5
    • gives you at least CRAM-MD5, which most clients support
    • still leaves the option of using PLAIN/LOGIN
    • must be supported by your administrative tools
      • some of them might need to revert to 3rd party tools for encryption (e.g. PostfixAdmin uses /usr/sbin/doveadm pw), which makes the clear-text password appear shortly in the process list each time it is invoked
    • other 3rd party tools (plugins/addons etc.) might be an issue

I'm still struggling which path to go – with only #2 definitely ruled-out (I don't want to make presents to potential hackers ;)

Related Topic