Namespace configuration over SQL

[Ain]

Hello!

I had working setup, where namespace settings (including prefix) was
queried over SQL. Now, after installing new Dovecot to new server (using
same configuration), this is not working anymore.

Log shows following error:

"""
Initialization failed: namespace configuration error: list=yes requires
prefix=yes to end with separator
"""

relevant part from dovecot-sql.conf.ext:
"""
user_query = \
SELECT \
1002 AS uid \
, 1003 AS gid \
, CONCAT('/www/home/vmail/domains/', SUBSTRING(d.name, 1,
1), '/', d.name, '/', u.username) AS home \
, namespace_prefix AS 'namespace/default/prefix' \
, 'yes' AS 'namespace/default/inbox' \
, '%s' AS 'mail_service' \
FROM vm_domain d \
INNER JOIN vm_user u ON u.vm_domain_id = d.id \
WHERE d.name = '%d' AND u.username = '%n' AND u.active = 1 AND
d.active = 1
"""

namespace_prefix can be empty or INBOX.

doveconf -n output:
"""
# 2.2.13: /usr/local/etc/dovecot/dovecot.conf
# OS: FreeBSD 9.3-RELEASE amd64
auth_debug = yes
auth_verbose = yes
disable_plaintext_auth = no
listen = *
mail_debug = yes
mail_location = maildir:~/Maildir
mmap_disable = yes
namespace default {
inbox = yes
list = yes
location =
prefix =
separator = .
type = private
}
passdb {
args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
plugin {
mail_log_events = delete undelete expunge copy mailbox_delete
mailbox_rename flag_change append
mail_log_fields = uid box msgid size
mail_log_group_events = no
}
service imap {
process_limit = 1480
}
ssl_cert = </usr/local/etc/ssl/server.crt
ssl_key = </usr/local/etc/ssl/server.key.unsecure
userdb {
args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
verbose_proctitle = yes
protocol imap {
imap_capability = IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE
IDLE SORT SORT=DISPLAY THREAD=REFERENCES THREAD=REFS
THREAD=ORDEREDSUBJECT MULTIAPPEND UNSELECT CHILDREN NAMESPACE UIDPLUS
LIST-EXTENDED I18NLEVEL=1 ESEARCH ESORT SEARCHRES WITHIN CONTEXT=SEARCH
LIST-STATUS SPECIAL-USE
mail_max_userip_connections = 900
}
protocol pop3 {
mail_max_userip_connections = 300
}
"""

Seems like "prefix = " is interpreted as "prefix = yes".

How I can solve this?

[Ain]

I tried few different dovecot versions. 2.1.16 is working, 2.2.2 is
broken. Because I use FreeBSD, in-between versions aren't available (I can
use source of course, but ports tree don't include them).
Probably some bigger change from 2.1 to 2.2 is affecting SQL output
handling, any fix or workaround?

Thanks in advance!

Post by Ain
Hello!
I had working setup, where namespace settings (including prefix) was
queried over SQL. Now, after installing new Dovecot to new server (using
same configuration), this is not working anymore.
"""
Initialization failed: namespace configuration error: list=yes requires
prefix=yes to end with separator
"""
"""
user_query = \
SELECT \
1002 AS uid \
, 1003 AS gid \
, CONCAT('/www/home/vmail/domains/', SUBSTRING(d.name, 1,
1), '/', d.name, '/', u.username) AS home \
, namespace_prefix AS 'namespace/default/prefix' \
, 'yes' AS 'namespace/default/inbox' \
, '%s' AS 'mail_service' \
FROM vm_domain d \
INNER JOIN vm_user u ON u.vm_domain_id = d.id \
WHERE d.name = '%d' AND u.username = '%n' AND u.active = 1 AND
d.active = 1
"""
namespace_prefix can be empty or INBOX.
"""
# 2.2.13: /usr/local/etc/dovecot/dovecot.conf
# OS: FreeBSD 9.3-RELEASE amd64
auth_debug = yes
auth_verbose = yes
disable_plaintext_auth = no
listen = *
mail_debug = yes
mail_location = maildir:~/Maildir
mmap_disable = yes
namespace default {
inbox = yes
list = yes
location =
prefix =
separator = .
type = private
}
passdb {
args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
plugin {
mail_log_events = delete undelete expunge copy mailbox_delete
mailbox_rename flag_change append
mail_log_fields = uid box msgid size
mail_log_group_events = no
}
service imap {
process_limit = 1480
}
ssl_cert = </usr/local/etc/ssl/server.crt
ssl_key = </usr/local/etc/ssl/server.key.unsecure
userdb {
args = /usr/local/etc/dovecot/dovecot-sql.conf.ext
driver = sql
}
verbose_proctitle = yes
protocol imap {
imap_capability = IMAP4rev1 LITERAL+ SASL-IR LOGIN-REFERRALS ID ENABLE
IDLE SORT SORT=DISPLAY THREAD=REFERENCES THREAD=REFS
THREAD=ORDEREDSUBJECT MULTIAPPEND UNSELECT CHILDREN NAMESPACE UIDPLUS
LIST-EXTENDED I18NLEVEL=1 ESEARCH ESORT SEARCHRES WITHIN CONTEXT=SEARCH
LIST-STATUS SPECIAL-USE
mail_max_userip_connections = 900
}
protocol pop3 {
mail_max_userip_connections = 300
}
"""
Seems like "prefix = " is interpreted as "prefix = yes".
How I can solve this?

[Steffen Kaiser]

Post by Ain
I had working setup, where namespace settings (including prefix) was
queried over SQL. Now, after installing new Dovecot to new server (using
same configuration), this is not working anymore.
"""
Initialization failed: namespace configuration error: list=yes requires
prefix=yes to end with separator
"""
"""
user_query = \
SELECT \
1002 AS uid \
, 1003 AS gid \
, CONCAT('/www/home/vmail/domains/', SUBSTRING(d.name, 1,
1), '/', d.name, '/', u.username) AS home \
, namespace_prefix AS 'namespace/default/prefix' \
, 'yes' AS 'namespace/default/inbox' \
, '%s' AS 'mail_service' \
FROM vm_domain d \
INNER JOIN vm_user u ON u.vm_domain_id = d.id \
WHERE d.name = '%d' AND u.username = '%n' AND u.active = 1 AND
d.active = 1
"""

what do you return as namespace_prefix, NULL vs. '' ? Could you try the
other value?

Post by Ain
namespace default {
inbox = yes
list = yes
prefix =
separator = .
type = private
}

- --
Steffen Kaiser

[Ain]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Ain
I had working setup, where namespace settings (including prefix) was
queried over SQL. Now, after installing new Dovecot to new server (using
same configuration), this is not working anymore.
"""
Initialization failed: namespace configuration error: list=yes requires
prefix=yes to end with separator
"""
"""
user_query = \
SELECT \
1002 AS uid \
, 1003 AS gid \
, CONCAT('/www/home/vmail/domains/', SUBSTRING(d.name, 1,
1), '/', d.name, '/', u.username) AS home \
, namespace_prefix AS 'namespace/default/prefix' \
, 'yes' AS 'namespace/default/inbox' \
, '%s' AS 'mail_service' \
FROM vm_domain d \
INNER JOIN vm_user u ON u.vm_domain_id = d.id \
WHERE d.name = '%d' AND u.username = '%n' AND u.active = 1 AND
d.active = 1
"""

what do you return as namespace_prefix, NULL vs. '' ? Could you try the
other value?

Empty string, so "". I have even changed this experimentally to this:

"""
user_query = \
SELECT \
1002 AS uid \
, 1002 AS gid \
, CONCAT('/www/home/vmail/domains/', SUBSTRING(d.name, 1,
1), '/', d.name, '/', u.username) AS home \
, IF(namespace_prefix="", "", namespace_prefix ) AS
'namespace/default/prefix' \
, 'yes' AS 'namespace/default/inbox' \
, '%s' AS 'mail_service' \
FROM vm_domain d \
INNER JOIN vm_user u ON u.vm_domain_id = d.id \
WHERE d.name = '%d' AND u.username = '%n' AND u.active = 1 AND
d.active = 1


"""

Result is same, error from log:
"""
Sep 26 15:12:25 server dovecot: imap(test at foo.local): Error: user
test at foo.local: Initialization failed: namespace configuration error:
list=yes requires prefix=yes to end with separator
Sep 26 15:12:25 server dovecot: imap(test at foo.local): Error: Invalid user
settings. Refer to server log for more information.

"""

With NULL in DB (and using first query version of course) error is same,
like above.

Thanks!

Ain

[Ain]

Sorry, using NULL indeed works correctly. My previous test with NULL was
little bit flawed.

Thanks for help!


Ain

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Ain
I had working setup, where namespace settings (including prefix) was
queried over SQL. Now, after installing new Dovecot to new server (using
same configuration), this is not working anymore.
"""
Initialization failed: namespace configuration error: list=yes requires
prefix=yes to end with separator
"""
"""
user_query = \
SELECT \
1002 AS uid \
, 1003 AS gid \
, CONCAT('/www/home/vmail/domains/', SUBSTRING(d.name, 1,
1), '/', d.name, '/', u.username) AS home \
, namespace_prefix AS 'namespace/default/prefix' \
, 'yes' AS 'namespace/default/inbox' \
, '%s' AS 'mail_service' \
FROM vm_domain d \
INNER JOIN vm_user u ON u.vm_domain_id = d.id \
WHERE d.name = '%d' AND u.username = '%n' AND u.active = 1 AND
d.active = 1
"""

what do you return as namespace_prefix, NULL vs. '' ? Could you try the
other value?

Post by Ain
namespace default {
inbox = yes
list = yes
prefix =
separator = .
type = private
}

- --
Steffen Kaiser
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
iQEVAwUBVCVjsXz1H7kL/d9rAQL9GggAuK7ad919SVY/7YV0EDEtiZW6psgHclqs
P/oI5o6ycX2C/MkREo9QeDuo68S12jt7H4fvewJ+MgVkUS7FtO8KUeKj5QD6FzKZ
bi5mDwnamO8+4fgmO74iGPv2u1pp2E+SS8wPreiVxyZWU/zcM4BrlMJf0JFVk+f7
f5CGrM91hCjWAFr8WSpYlb1m9gY4RG3crogs9c2kntmDY2kwEHcsDvElwf6rwGHk
VW1mjxdLUY3wgXXSr8OBEvmWYBIIpTu8XHf3nOh5STbZXRMkdSRWPkftKTzPTmH4
13/xsKzmG6zWuwYHdROinXfCg/hmFg1l+6zsqU/jMAlDiAV6S3B71A==
=6ewJ
-----END PGP SIGNATURE-----

[Steffen Kaiser]

On Fri, 26 Sep 2014, Ain wrote:

There seems to be no way, in order to override a non-empty
namespace/default/prefix with an empty value. My test system is Dovecot
v2.2.13 (35c3194900b7), the current hg version.

See below for an example with SQL. Returning an empty string '' using SQL
results into namespace/default/prefix=yes, the boolean string "yes" does
replace the empty string returned by SQL. Using a NULL value, you can work
around this, but in this the default value must be empty.

The applies to LDAP as well, I assume.

It definitly applies to userdb_import returned by LDAP, too:
http://wiki2.dovecot.org/UserDatabase/ExtraFields?highlight=%28userdb_import%29

The string:

namespace/default/prefix=<TAB>namespace/inbox/prefix

sets both namespace/default/prefix and namespace/inbox/prefix to 'yes'.
The '=' does not matter.

====

Post by Ain
Sorry, using NULL indeed works correctly. My previous test with NULL was
little bit flawed.

Post by Steffen Kaiser

Post by Ain
I had working setup, where namespace settings (including prefix) was
queried over SQL. Now, after installing new Dovecot to new server (using
same configuration), this is not working anymore.
"""
Initialization failed: namespace configuration error: list=yes requires
prefix=yes to end with separator
"""
"""
user_query = \
SELECT \
1002 AS uid \
, 1003 AS gid \
, CONCAT('/www/home/vmail/domains/', SUBSTRING(d.name, 1,
1), '/', d.name, '/', u.username) AS home \
, namespace_prefix AS 'namespace/default/prefix' \
, 'yes' AS 'namespace/default/inbox' \
, '%s' AS 'mail_service' \
FROM vm_domain d \
INNER JOIN vm_user u ON u.vm_domain_id = d.id \
WHERE d.name = '%d' AND u.username = '%n' AND u.active = 1 AND
d.active = 1
"""

what do you return as namespace_prefix, NULL vs. '' ? Could you try the
other value?

- --
Steffen Kaiser