Hi guys n gals, im on an ubuntu 11 VPS on linode (dovecot version 2.013). ive spent a lot of time and googling trying to get postfix, dovecot, ssl, saslauthd and mysql to all play nice together and im quite close now but theres something going on i cant quite explain and id really appreciate some help. I have been going for ten hours on this today and it seems in direct conflict with what dovecot themselves say... the problem is a variable in dovecot (%u) which is normally used as a full username in sql query strings (i.e. user at domain) and is simply dropping the domain for some reason... From dovecot docs (http://wiki2.dovecot.org/Variables): The variables that work everywhere are: %u user full username (e.g. user at domain) so i have postfix and stuff set up ok to send at least and i know this because ive tested it by hard-coding the string in the sql query myself with a successful send... but, anyway, the problem with the '%u' variable is it is doing stuff like this: (from mail.log and mysql.log) localhost dovecot: auth: Debug: sql(admin at microhard.com,xx.xx.xx.xx): SELECT home,uid,gid FROM users WHERE id = 'admin at microhard.com' this was derived from the following query in /etc/dovecot/dovecot-sql.conf.ext user_query = SELECT home,uid,gid FROM users WHERE id = '%u' so no problem there.... but then this happens when i attempt to send mail and this means the user gets rejected because dovecot cant find the user id in the db without the correct string (i.e. user at domain): localhost dovecot: auth: Debug: sql(admin,xx.xx.xx.xx): query: SELECT id as user, crypt as password FROM users WHERE id= 'admin' Query SELECT id as user, crypt as password FROM users WHERE id= 'admin' which is derived from the following query also in /etc/dovecot/dovecot-sql.conf.ext password_query = SELECT id as user, crypt as password FROM users WHERE id= '%u' and to further confuse me, this 'unchangeable' variable sometimes decides to right itself without any obvious reason... localhost dovecot: auth: Debug: sql(admin at microhard.com,xx.xx.xx.xx): query: SELECT id as user, crypt as password FROM users WHERE id= 'admin at microhard.com' im wondering if its something to do with my client dropping the domain, or whether dovecot has an error, or some other thing ive overlooked... my dovecot.conf looks a lot like this: # 2.0.13: dovecot.conf # OS: Linux 3.0.18-x86_64-linode24 x86_64 Ubuntu 11.10 ext3 first_valid_uid = 5000 last_valid_uid = 5000 login_greeting = I likes my ducks n geese I do. mail_location = maildir:/var/spool/mail/virtual/%u #enables logging all failed authentication attempts. auth_verbose=yes #enables all authentication debug logging (also enables auth_verbose). Passwords are logged as <hidden>. auth_debug=yes #does everything that auth_debug=yes does, but it also removes password hiding. auth_debug_passwords=yes #enables all kinds of mail related debug logging, such as showing where Dovecot is looking for mails. mail_debug=yes #enables logging SSL errors and warnings. Even without this setting if connection is closed because of an SSL error, the error is logged as the disconnection reason (v1.1+). verbose_ssl=yes passdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } plugin { acl = vfile:/etc/dovecot/acls #quota = maildir:storage=10240:messages=1000 trash = /etc/dovecot/trash.conf } protocols = " imap" service auth { unix_listener /var/spool/postfix/private/auth { group = postfix mode = 0660 user = postfix } unix_listener auth-master { group = dovecot mode = 0660 user = dovecot } user = dovecot } ssl_ca = /etc/pki/dovecot/certs/ca-bundle.crt ssl_cert = </etc/ssl/certs/dovecot.pem ssl_key = </etc/ssl/private/dovecot.pem userdb { args = /etc/dovecot/dovecot-sql.conf.ext driver = sql } userdb { driver = prefetch } userdb { driver = passwd } protocol imap { mail_plugins = quota imap_quota } protocol pop3 { mail_plugins = quota pop3_client_workarounds = outlook-no-nuls oe-ns-eoh } protocol lda { info_log_path = /var/log/dovecot-deliver.log log_path = /var/log/dovecot-deliver.log mail_plugins = quota postmaster_address = admin at microhard.com } any help *much* appreciated! cheerz Kris
Am 25.04.2012 08:17, schrieb Kris:> this was derived from the following query in > /etc/dovecot/dovecot-sql.conf.ext > user_query = SELECT home,uid,gid FROM users WHERE id = '%u'i use like this user_query = SELECT concat('/usr/local/virtual/', maildir) AS home, \ concat('*:bytes=', mailbox.quota) AS quota_rule, \ #when saving to Trash mailbox the user gets additional 50MB "Trash:storage=+50240" AS quota_rule2, \ #when saving to Sent mailbox the user gets additional 50MB "Sent:storage=+50240" AS quota_rule3, \ #when saving to Drafts mailbox the user gets additional 50MB "Drafts:storage=+50240" AS quota_rule4, \ #when saving to Templates mailbox the user gets additional 50MB "Templates:storage=+50240" AS quota_rule5, \ #when saving to Junk mailbox the user gets additional 50MB "Junk:storage=+50240" AS quota_rule6, \ #when saving to Archives mailbox the user gets additional 50MB "Archives:storage=+50240" AS quota_rule7, \ ########### concat('maildir:/usr/local/virtual/', maildir) AS mail, \ CASE '%s' WHEN 'pop3' THEN NULL ELSE 'yes' END AS 'namespace/inbox/inbox', \ CASE '%s' WHEN 'pop3' THEN 'yes' ELSE NULL END AS 'namespace/virtual/inbox', \ 1001 AS uid, 1001 AS gid FROM mailbox WHERE username = '%u' AND active = '1'> > so no problem there.... > > but then this happens when i attempt to send mail and this means the > user gets rejected because dovecot cant find the user id in the db > without the correct string (i.e. user at domain): > localhost dovecot: auth: Debug: sql(admin,xx.xx.xx.xx): query: SELECT id > as user, crypt as password FROM users WHERE id= 'admin' > Query SELECT id as user, crypt as password FROM users WHERE id= 'admin' > which is derived from the following query also in > /etc/dovecot/dovecot-sql.conf.ext > password_query = SELECT id as user, crypt as password FROM users WHERE > id= '%u' > > and to further confuse me, this 'unchangeable' variable sometimes > decides to right itself without any obvious reason... > localhost dovecot: auth: Debug: sql(admin at microhard.com,xx.xx.xx.xx): > query: SELECT id as user, crypt as password FROM users WHERE id> 'admin at microhard.com' > > im wondering if its something to do with my client dropping the domain, > or whether dovecot has an error, or some other thing ive overlooked...password_query = SELECT username as user, password, \ 1001 as userdb_uid, \ 1001 as userdb_gid, \ "/usr/local/virtual/%d/%u/" AS userdb_home, \ "maildir:/usr/local/virtual/%d/%u/" AS userdb_mail \ FROM mailbox WHERE username = '%u' AND active = '1' AND (imap_allowed '1' or '%Ls' = 'pop3') this should not fit to you ( postfixadmin full domain layout with virtual plugin and quota additions ,clear text passwords etc), but perhaps gives you ideas by compare, what to fix at your side, however upgrade to dove latest 2.0.20 -- Best Regards MfG Robert Schetterer Germany/Munich/Bavaria
On Wed, Apr 25, 2012 at 07:17:18AM +0100, Kris wrote: [..]> the problem is a variable in dovecot (%u) which is normally used as > a full username in sql query strings (i.e. user at domain) and is > simply dropping the domain for some reason...[..]> and to further confuse me, this 'unchangeable' variable sometimes > decides to right itself without any obvious reason... > localhost dovecot: auth: Debug: > sql(admin at microhard.com,xx.xx.xx.xx): query: SELECT id as user, > crypt as password FROM users WHERE id= 'admin at microhard.com' > > im wondering if its something to do with my client dropping the > domain, or whether dovecot has an error, or some other thing ive > overlooked...Indeed, it seems some of your users try to authenticate with a username without the '@domain' part. You can either try to make your users comply or you can use auth_default_realm in your config: # Default realm/domain to use if none was specified. This is # used for both SASL realms and appending @domain to username # in plaintext logins. # #auth_default_realm Dennis [..]
On 25.4.2012, at 9.17, Kris wrote:> but then this happens when i attempt to send mail and this means the user gets rejected because dovecot cant find the user id in the db without the correct string (i.e. user at domain): > localhost dovecot: auth: Debug: sql(admin,xx.xx.xx.xx): query: SELECT id as user, crypt as password FROM users WHERE id= 'admin'What exactly is doing this lookup? SMTP AUTH? Maybe the client's SMTP authentication is configured without the @domain part in username?