As a way to try and avoid using "prefix = INBOX." ad infinitum for the inbox namespace, I'm looking for ways to move on to "prefix =" for new mail accounts, and grandfather the existing ones. Previously running Courier-IMAP, now Dovecot, I looked at https://wiki.dovecot.org/Namespaces#Backwards_Compatibility:_Courier_IMAP and decided it's too risky to go down that path and use namespace compat, with so many IMAP clients out there the scope of testing is huge and the outcome is uncertain and not worth it. After reading https://wiki.dovecot.org/Namespaces#Per-user_Namespace_Location_From_SQL I thought I might be able to overwrite the server configuration per user returning 'namespace/inbox/prefix' value from SQL. Here's the setup I attempted, briefly:
Client connects to the Dovecot proxy, which authenticates the user and proxies to the backend using a query like this in /etc/dovecot/conf.d/dovecot-sql.conf.ext: driver = mysql connect =
password_query = SELECT NULL AS password, 'Y' as nopassword, host, 'any-cert' as 'starttls', 'Y' AS proxy FROM mailbox WHERE email = '%u' AND disabled_smtpauth=0 Works a treat. Next, I'm trying to add the prefix lookup in the picture. In the same file I've added: user_query = SELECT ns_inbox_prefix AS 'namespace/inbox/prefix' FROM mailbox WHERE email = '%u' AND disabled_smtpauth=0
The mailbox table schema reads: CREATE TABLE
mailbox(idint(11) unsigned NOT NULL AUTO_INCREMENT,emailvarchar(255) NOT NULL DEFAULT '',passwordvarchar(255) NOT NULL DEFAULT '',clear_passwordvarchar(255) NOT NULL DEFAULT '',namevarchar(255) NOT NULL DEFAULT '',hostvarchar(32) DEFAULT NULL,portvarchar(32) DEFAULT NULL,ns_inbox_prefixvarchar(255) NOT NULL DEFAULT '',lastlog_remote_ipsbigint(20) unsigned NOT NULL DEFAULT 0,curlog_remote_ipsbigint(20) unsigned NOT NULL DEFAULT 0,disabled_smtpauthtinyint(1) NOT NULL DEFAULT 0,last_modifiedtimestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(), PRIMARY KEY (id), UNIQUE KEYemail(email) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1; The 2 queries above return: MariaDB [postfix]> SELECT NULL AS password, 'Y' as nopassword, host, 'any-cert' as 'starttls', 'Y' AS proxy FROM mailbox WHERE email = 'adi2@0aditest.local' AND disabled_smtpauth=0; +----------+------------+----------------+----------+-------+ | password | nopassword | host | starttls | proxy | +----------+------------+----------------+----------+-------+ | NULL | Y | 192.168.123.24 | any-cert | Y | +----------+------------+----------------+----------+-------+ 1 row in set (0.00 sec) MariaDB [postfix]> SELECT ns_inbox_prefix AS 'namespace/inbox/prefix' FROM mailbox WHERE email = 'adi2@0aditest.local' AND disabled_smtpauth=0; +------------------------+ | namespace/inbox/prefix | +------------------------+ | | +------------------------+ 1 row in set (0.00 sec)
After reloading dovecot service with auth_debug = yes are the maillog for an IMAP session: Nov 15 12:43:48 proxy1 dovecot: auth: Debug: Loading modules from directory: /usr/lib64/dovecot/auth Nov 15 12:43:48 proxy1 dovecot: auth: Debug: Module loaded: /usr/lib64/dovecot/auth/lib20_auth_var_expand_crypt.so Nov 15 12:43:48 proxy1 dovecot: auth: Debug: Module loaded: /usr/lib64/dovecot/auth/libdriver_mysql.so Nov 15 12:43:48 proxy1 dovecot: auth: Debug: Module loaded: /usr/lib64/dovecot/auth/libdriver_sqlite.so Nov 15 12:43:48 proxy1 dovecot: auth: Debug: Read auth token secret from /var/run/dovecot/auth-token-secret.dat Nov 15 12:43:48 proxy1 dovecot: auth: Debug: auth client connected (pid=7527) Nov 15 12:43:53 proxy1 dovecot: auth: Debug: client in: AUTH#0111#011PLAIN#011service=imap#011secured#011session=AorrLqp6drgAAAAAAAAAAAAAAAAAAAAB#011lip=::1#011rip=::1#011lport=1143#011rport=47222#011resp=<hidden> Nov 15 12:43:53 proxy1 dovecot: auth: Debug: sql(adi2@0aditest.local,::1,<AorrLqp6drgAAAAAAAAAAAAAAAAAAAAB>): cache miss Nov 15 12:43:53 proxy1 dovecot: auth-worker(7533): Debug: Loading modules from directory: /usr/lib64/dovecot/auth Nov 15 12:43:53 proxy1 dovecot: auth-worker(7533): Debug: Module loaded: /usr/lib64/dovecot/auth/lib20_auth_var_expand_crypt.so Nov 15 12:43:53 proxy1 dovecot: auth-worker(7533): Debug: Module loaded: /usr/lib64/dovecot/auth/libdriver_mysql.so Nov 15 12:43:53 proxy1 dovecot: auth-worker(7533): Debug: Module loaded: /usr/lib64/dovecot/auth/libdriver_sqlite.so Nov 15 12:43:53 proxy1 dovecot: auth-worker(7533): Debug: sql(adi2@0aditest.local,::1,<AorrLqp6drgAAAAAAAAAAAAAAAAAAAAB>): query: SELECT NULL AS password, 'Y' as nopassword, host, 'any-cert' as 'starttls', 'Y' AS proxy FROM mailbox WHERE email = 'adi2@0aditest.local' AND disabled_smtpauth=0 Nov 15 12:43:53 proxy1 dovecot: auth: Debug: client passdb out: OK#0111#011user=adi2@0aditest.local#011host=192.168.123.24#011starttls=any-cert#011proxy#011pass=<hidden> Nov 15 12:43:53 proxy1 dovecot: imap-login: Invalid certificate: [...] Nov 15 12:43:53 proxy1 dovecot: imap-login: Invalid certificate: [...] Nov 15 12:43:53 proxy1 dovecot: imap-login: Invalid certificate: [...] Nov 15 12:43:53 proxy1 dovecot: imap-login: Invalid certificate: [...] Nov 15 12:43:53 proxy1 dovecot: imap-login: Invalid certificate: [...] Nov 15 12:43:53 proxy1 dovecot: imap-login: proxy(adi2@0aditest.local): started proxying to 192.168.123.24:143: user=adi2@0aditest.local, method=PLAIN, rip=::1, lip=::1, secured, session=<AorrLqp6drgAAAAAAAAAAAAAAAAAAAAB>
Looks like user_query isn't executed, why? And here's the corresponding IMAP session:
Trying ::1... Connected to localhost. Escape character is '^]'.
- OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE IDLE STARTTLS AUTH=PLAIN] Dovecot ready. . LOGIN adi2@0aditest.local <hidden> . OK [CAPABILITY IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE IDLE SORT SORT=DISPLAY THREAD=REFERENCES THREAD=REFS THREAD=ORDEREDSUBJECT MULTIAPPEND URL-PARTIAL CATENATE UNSELECT CHILDREN NAMESPACE UIDPLUS LIST-EXTENDED I18NLEVEL=1 CONDSTORE QRESYNC ESEARCH ESORT SEARCHRES WITHIN CONTEXT=SEARCH LIST-STATUS BINARY MOVE NOTIFY SPECIAL-USE QUOTA] Logged in . NAMESPACE
- NAMESPACE (("INBOX." ".")) NIL NIL . OK Namespace completed (0.000 + 0.000 secs). . LIST "" *
- LIST (\HasChildren) "." INBOX
- LIST (\HasNoChildren \Trash) "." INBOX.Trash
- LIST (\HasNoChildren) "." INBOX.Templates
- LIST (\HasNoChildren \Sent) "." INBOX.Sent
- LIST (\HasNoChildren \Drafts) "." INBOX.Drafts
- LIST (\HasNoChildren \Archive) "." INBOX.Archives
- LIST (\HasNoChildren \UnMarked \Junk) "." INBOX.Spam . OK List completed (0.000 + 0.000 secs). . LSUB "" *
- LSUB (\Archive) "." INBOX.Archives
- LSUB (\Drafts) "." INBOX.Drafts
- LSUB (\Sent) "." INBOX.Sent
- LSUB (\Junk) "." INBOX.Spam
- LSUB () "." INBOX.Templates
- LSUB (\Trash) "." INBOX.Trash . OK Lsub completed (0.000 + 0.000 secs). . LOGOUT
- BYE Logging out . OK Logout completed (0.000 + 0.000 secs). Connection closed by foreign host.
How do I overwrite 'namespace/inbox/prefix' for an user on the Dovecot proxy? Is user_query working in this context?
-- Adi Pircalabu