[Dovecot] dovecot %u variable problem....

[Robert Schetterer]

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