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

Thu Nov 15 06:02:27 EET 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 = </etc/pki/dovecot/certs/dovecot.pem
ssl_key =  # hidden, use -P to show it
userdb {
   args = /etc/dovecot/dovecot-sql.conf.ext
   driver = sql
   name = sql
}
protocol sieve {
   mail_max_userip_connections = 500
   managesieve_implementation_string = Dovecot Pigeonhole
   managesieve_logout_format = rcvd=%i, sent=%o
   managesieve_max_compile_errors = 5
}

-- 
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:
> 
> 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?