dovecot

dovecot@dovecot.org

September 2025

61 participants
51 discussions

Dovecot 2.4.1: Domain part of user gets dropped in userdb_sql_query
by Robert Czechowski 01 Sep '25

01 Sep '25

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(a)czecho.de)<118509><hXWpLOzOtGjtzgEAp35xQg>: Debug: auth-master: userdb lookup(robert(a)czecho.de): Started userdb lookup dovecot: lmtp(robert(a)czecho.de)<118509><hXWpLOzOtGjtzgEAp35xQg>: Debug: auth-master: conn unix:/var/run/dovecot//auth-userdb: Connecting dovecot: lmtp(robert(a)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(a)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(a)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(a)czecho.de> User doesn't exist: robert(a)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}' }

2 2