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

15 Nov 2018

      Forgot to add "doveconf -n" for the proxy server:
# 2.2.36 (1f10bfa63): /etc/dovecot/dovecot.conf
# Pigeonhole version 0.4.24 (124e06aa)
# OS: Linux 4.14.81-6.el7xen.x86_64 x86_64 CentOS Linux release 7.5.1804
(Core)
# Hostname: proxy1.0aditest.local
auth_cache_negative_ttl = 5 mins
auth_cache_size = 16 M
auth_cache_ttl = 18 hours
auth_debug = yes
auth_verbose = yes
mail_debug = yes
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope
encoded-character vacation subaddress comparator-i;ascii-numeric
relational regex imap4flags copy include variables body enotify
environment mailbox date index ihave duplicate mime foreverypart
extracttext imapflags notify
mbox_write_locks = fcntl
namespace inbox {
inbox = yes
location =
mailbox Drafts {
special_use = \Drafts
}
mailbox Junk {
special_use = \Junk
}
mailbox Sent {
special_use = \Sent
}
mailbox "Sent Messages" {
special_use = \Sent
}
mailbox Trash {
special_use = \Trash
}
prefix =
}
passdb {
args = /etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
plugin {
sieve = file:~/sieve;active=~/.dovecot.sieve
sieve_extensions = +notify +imapflags
}
protocols = imap pop3 lmtp sieve
service imap-login {
inet_listener imap {
port = 1143
}
inet_listener imaps {
port = 1993
ssl = yes
}
}
service managesieve-login {
inet_listener sieve {
port = 4190
}
service_count = 0
vsz_limit = 128 M
}
service managesieve {
process_limit = 1024
}
service pop3-login {
inet_listener pop3 {
port = 110
}
inet_listener pop3s {
port = 995
ssl = yes
}
}
ssl = required
ssl_cert = 
--
Adi Pircalabu
On 2018-11-15 12:59, Adi Pircalabu wrote:
...
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?