Dovecot 2.4.1: Domain part of user gets dropped in userdb_sql_query

1 Sep 2025


      Hi,
I stumbled upon a problem where documentation and logs no longer help
me.
After upgrading to Debian Trixie / Dovecot 2.4.1-4 I have run into the
following problem after fixing up most of my configuration.
I'm using a vmail setup with user information in a mysql database and
mailboxes in maildir format. Apparently %{user | domain} will not get
set in my SQL query when looking up users for lmtp transport:
With the setting
userdb_sql_query = SELECT 150 AS uid, 8 AS gid FROM users WHERE
username = '%{user | username}' AND domain = '%{user | domain}'
I get the following debug log:
dovecot: lmtp(118509): Connect from local
dovecot: lmtp(robert@czecho.de)<118509><hXWpLOzOtGjtzgEAp35xQg>: Debug:
auth-master: userdb lookup(robert@czecho.de): Started userdb lookup
dovecot: lmtp(robert@czecho.de)<118509><hXWpLOzOtGjtzgEAp35xQg>: Debug:
auth-master: conn unix:/var/run/dovecot//auth-userdb: Connecting
dovecot: lmtp(robert@czecho.de)<118509><hXWpLOzOtGjtzgEAp35xQg>: Debug:
auth-master: conn unix:/var/run/dovecot//auth-userdb (pid=115443,uid=0):
Client connected (fd=14)
dovecot: auth: Debug: conn unix:/var/run/dovecot/auth-userdb
(pid=118509,uid=150): Server accepted connection (fd=23)
dovecot: auth: Debug: master in:
USER#0111#011robert@czecho.de#011protocol=lmtp
dovecot: auth(robert): Debug: sql: Performing userdb lookup
dovecot: auth-worker(115838): Debug: conn unix:auth-worker
(pid=115833,uid=109): auth-worker<7>: Handling USER request
dovecot: auth-worker(robert)<115838>: request [7]: Debug: sql:
Performing userdb lookup
dovecot: auth-worker(robert)<115838>: request [7]: Debug: sql: SELECT
150 AS uid, 8 AS gid FROM users WHERE username =  'robert' AND domain =
''
dovecot: auth-worker(115838): Debug: mysql(127.0.0.1): Finished query
'SELECT 150 AS uid, 8 AS gid FROM users WHERE username =  'robert' AND
domain = ''' in 0 msecs
dovecot: auth-worker(robert)<115838>: request [7]: sql: unknown user
dovecot: auth-worker(robert)<115838>: request [7]: Debug: sql: Finished
userdb lookup
dovecot: auth-worker(115838): Debug: conn unix:auth-worker
(pid=115833,uid=109): auth-worker<7>: Finished: user_unknown
dovecot: auth(robert): Debug: sql: Finished userdb lookup
dovecot: auth: Debug: userdb out: NOTFOUND#0111
Note the
SELECT 150 AS uid, 8 AS gid FROM users WHERE username =  'robert' AND
domain = ''
Resulting in
poostfix/lmtp[118495]: A7D6F5A0350: to=<robert@czecho.de>,
relay=alyx.czecho.de[private/dovecot-lmtp], delay=0.11,
delays=0.03/0.04/0.02/0.02, dsn=5.1.1, status=bounced (host
alyx.czecho.de[private/dovecot-lmtp] said: 550 5.1.1 <robert@czecho.de>
User doesn't exist: robert@czecho.de (in reply to RCPT TO command))
Apparently, somewhere, the domain part of the users gets lost.
My current workaround is to use the query without the domain part:
userdb_sql_query = SELECT 150 AS uid, 8 AS gid FROM users WHERE
username = '%{user | username}'
However this places the mail in the wrong maildir directory. I've
worked around that by creating symlinks for all users, but it seems a
bit more effort than necessary.
Also this will break as soon as two domains have the same username. :(
For completeness:
dovecot -n
2.4.1-4 (7d8c0e5759): /etc/dovecot/dovecot.conf
Pigeonhole version 2.4.1-4 (0a86619f)
OS: Linux 6.12.41+deb13-amd64 x86_64 Debian 13.0
Hostname: alyx.czecho.de
4 default setting changes since version 2.4.0
dovecot_config_version = 2.4.0
auth_debug = yes
auth_mechanisms = plain login
auth_verbose = yes
base_dir = /var/run/dovecot/
dovecot_storage_version = 2.4.0
first_valid_uid = 150
fts_autoindex = yes
fts_autoindex_max_recent_msgs = 999
fts_search_add_missing = yes
language_filters = normalizer-icu snowball stopwords
language_tokenizers = generic email-address
listen = * ::
mail_debug = yes
mail_driver = maildir
mail_gid = mail
mail_home = /var/vmail/%{user | domain}/%{user | username}
mail_path = ~/mail
mail_plugins {
fts = yes
fts_flatcurve = yes
}
mail_privileged_group = mail
mail_uid = vmail
protocols = imap lmtp
namespace inbox {
inbox = yes
mailbox Drafts {
special_use = "\\Drafts"
}
mailbox Junk {
special_use = "\\Junk"
}
mailbox Trash {
special_use = "\\Trash"
}
mailbox Sent {
special_use = "\\Sent"
}
mailbox "Sent Messages" {
special_use = "\\Sent"
}
}
service imap-login {
inet_listener imap {
port = 143
}
inet_listener imaps {
port = 993
ssl = yes
}
}
service lmtp {
user = vmail
unix_listener /var/spool/postfix/private/dovecot-lmtp {
group = postfix
mode = 0660
user = postfix
}
}
service imap {
}
service pop3 {
}
service auth {
unix_listener auth-userdb {
group = mail
mode = 0600
user = vmail
}
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0660
user = postfix
}
}
service auth-worker {
}
service dict {
unix_listener dict {
}
}
ssl_server {
cert_file = /etc/ssl/certs/letsencrypt/mail.scrt
key_file = /etc/ssl/private/letsencrypt/mail.priv
}
protocol lmtp {
auth_username_format = %{user | username}
}
language en {
default = yes
filters = lowercase snowball english-possessive stopwords
}
fts flatcurve {
substring_search = yes
}
mysql 127.0.0.1 {
dbname = vmail
password = # hidden, use -P to show it
user = vmail
}
passdb sql {
default_password_scheme = SHA512-CRYPT
query = SELECT username, domain, password FROM users WHERE username =
'%{user | username}' AND domain = '%{user | domain}'
sql_driver = mysql
}
userdb sql {
sql_driver = mysql
iterate_query = SELECT CONCAT(username, '@', domain) as user FROM
users
query = SELECT 150 AS uid, 8 AS gid FROM users WHERE username =
'%{user | username}'
}

Dovecot 2.4.1: Domain part of user gets dropped in userdb_sql_query

Robert Czechowski

dovecot -n

2.4.1-4 (7d8c0e5759): /etc/dovecot/dovecot.conf

Pigeonhole version 2.4.1-4 (0a86619f)

OS: Linux 6.12.41+deb13-amd64 x86_64 Debian 13.0

Hostname: alyx.czecho.de

4 default setting changes since version 2.4.0