Dovecot proxy: per user/domain 'namespace/inbox/prefix' from MySQL

15 Nov 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:

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 (
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@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

Adi Pircalabu

Adi Pircalabu

tags

participants (1)