Incoming emails rejected or POP3 logon rejected

Discussion in 'Installation/Configuration' started by ITFixt, Oct 9, 2012.

  1. ITFixt

    ITFixt New Member

    I had ISPConfig 3.0.4 installed and everything working except that all incoming email was rejected with:
    Code:
    Status: 5.1.1
    Diagnostic-Code: x-unix; user unknown
    
    I eventually tracked this down to authentication in Dovecot delivery. The message was being accepted by Postfix, passed through Amavis, back into Postfix and thence to Dovecot.

    I then discovered that there had been a problem with the "user_query = " command in /etc/dovecot/dovecot-sql.conf, so I changed it in accordance with

    http://bugtracker.ispconfig.org/index.php?do=details&task_id=1634&opened=599&status[0]=

    And incoming mails worked! But now email client logging in (which was working) doesn't :(

    It appears that the same SQL statement is used to retrieve the email address during delivery and the account login username at POP3/IMAP login time. If I change the WHERE clause from:
    Code:
    WHERE login = '%u' AND disable%Ls = 'n'
    to
    Code:
    WHERE email = '%u' AND disable%Ls = 'n'
    then the delivery works but login doesn't, and vice-versa.

    I should add that ISPConfig is set to allow custom login names.

    I *think* a solution is to user the password query to get both username and password (by prepending "userdb_" to the appropriate fields, as per Dovecot docs) and then the user query is only used for delivery. Or, I can change the dovecot config file to use two separate sql config files.

    Comments, please?
     
  2. ITFixt

    ITFixt New Member

    I solved the problem like this:

    In dovecot.conf, change
    Code:
    passdb {
      args = /etc/dovecot/dovecot-sql.conf
      driver = sql
    }
    
    userdb {
      args = /etc/dovecot/dovecot-sql.conf
      driver = sql
    }
    to

    Code:
    passdb {
      args = /etc/dovecot/dovecot-sql.conf
      driver = sql
    }
    userdb {
      driver = prefetch
    }
    #Following userdb only used for delivery, so lookup on email
    userdb {
      args = /etc/dovecot/dovecot-sql.conf
      driver = sql
    }
    And in dovecot-sql.conf, I used these two queries:

    Code:
    password_query = SELECT password, /
    maildir AS userdb_home, /
    uid AS userdb_uid, /
    gid as userdb_gid /
    FROM mail_user /
    WHERE login = '%u' AND disable%Ls = 'n'
    
    user_query = SELECT email as user, /
    maildir as home, /
    CONCAT('maildir:', maildir, '/Maildir') as mail, /
    uid, gid, /
    CONCAT('*:storage=', quota, 'B') AS quota_rule /
    FROM mail_user /
    WHERE email = '%u' AND disable%Ls = 'n'
    This is more efficient than the original as only one query is executed to do user/PW lookup for mail client logon. As I've got ISPConfig set to use fixed UID & GID, it may well be possible to optimise this so that only the maildir & quota needs to be fetched from the DB, the rest of the fields being static.

    As before, I'd appreciate comments. Although this now works, I still have the niggling feeling it should have been fixed some other way :}
     

Share This Page