Adi Pircalabu
2018-Nov-15 01:59 UTC
Dovecot proxy: per user/domain 'namespace/inbox/prefix' from MySQL
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
Adi Pircalabu
2018-Nov-15 04:02 UTC
Dovecot proxy: per user/domain 'namespace/inbox/prefix' from MySQL
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?