Dovecot proxy: per user/domain 'namespace/inbox/prefix' from MySQL
Adi Pircalabu
adi at ddns.com.au
Thu Nov 15 03:59:24 EET 2018
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:
1. 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 = <connection_string>
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.
2. 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
3. The mailbox table schema reads:
CREATE TABLE `mailbox` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`email` varchar(255) NOT NULL DEFAULT '',
`password` varchar(255) NOT NULL DEFAULT '',
`clear_password` varchar(255) NOT NULL DEFAULT '',
`name` varchar(255) NOT NULL DEFAULT '',
`host` varchar(32) DEFAULT NULL,
`port` varchar(32) DEFAULT NULL,
`ns_inbox_prefix` varchar(255) NOT NULL DEFAULT '',
`lastlog_remote_ips` bigint(20) unsigned NOT NULL DEFAULT 0,
`curlog_remote_ips` bigint(20) unsigned NOT NULL DEFAULT 0,
`disabled_smtpauth` tinyint(1) NOT NULL DEFAULT 0,
`last_modified` timestamp NOT NULL DEFAULT current_timestamp() ON
UPDATE current_timestamp(),
PRIMARY KEY (`id`),
UNIQUE KEY `email` (`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 at 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 at 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 at 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 at 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 at 0aditest.local' AND disabled_smtpauth=0
Nov 15 12:43:53 proxy1 dovecot: auth: Debug: client passdb out:
OK#0111#011user=adi2 at 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 at 0aditest.local):
started proxying to 192.168.123.24:143: user=<adi2 at 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 at 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
More information about the dovecot
mailing list