On 12/7/2014 5:04 AM, Yves Goergen wrote:

Am 07.12.2014 um 00:56 schrieb Alexander Dalloz:

...

You did fulfill the requzirements for prefetch to work documented in the wiki?

http://wiki2.dovecot.org/UserDatabase/Prefetch

Ehm, this is my SQL configuration 'dovecot-sql.conf.ext':

...

driver = mysql connect = host=**** user=**** password=**** dbname=**** default_pass_scheme = PLAIN password_query =
SELECT
local AS username, domain, clearpass AS password,
concat(maildir, '/home') AS home, maildir AS mail
FROM mailusers
WHERE local = '%n' AND domain = '%d' AND forward = '' AND NOT locked

Now that I've found the page you gave me (didn't see it before, but I must say that wiki is not easily readable, pretty confusing) I think the column names must be different.

Instead of: username, domain, password, home, mail Should I return: username, domain, password, userdb_home, userdb_mail?

I too made a similar mistake and struggled for a while to understand why my attempts were failing. If using the prefetch userdb driver you have to return values from your database using appropriate aliases to match the expected names.

Here is what I'm using for the 'password_query':

password_query =
SELECT email AS user, password,
'vmail' AS userdb_uid,
'vmail' AS userdb_gid,
'/var/vmail/%d/%n' as userdb_home
FROM virtual_users
WHERE email = '%u'
AND enabled = '1';

Depending on your db layout you'll have different source values, but as long as you end up returning the values under the right column names (or aliases) it should work. My current db design needs improvement (as the static placeholder values in the above query shows), but it works as-is for now.

And what does that comment in the example mean? "# The userdb below is used only by lda." Should I use only userdb:driver=prefetch, or should I include a separate userdb section as if I wouldn't use prefetch? Again, confusing. Why does it have to be two separate queries at all? Just use one and take what you get. If some required column is missing and the value isn't set in the configuration, you can still throw an error.

I can't speak to the design, but from what I've read the userdb sections have a "fall through" approach. If one doesn't provide the sought after information the next userdb section is used.

From the http://wiki2.dovecot.org/UserDatabase/Prefetch wiki page:

Prefetch userdb can be used to combine passdb and userdb lookups into a single lookup. It's usually used with SQL, LDAP and checkpassword passdbs.

Prefetch basically works by requiring that the passdb returns the userdb information in extra fields with userdb_ prefixes. For example if a userdb typically returns uid, gid and home fields, the passdb would have to return userdb_uid, userdb_gid and userdb_home fields.

If you're using LDA, you still need a valid userdb which can be used to locate the users. You can do this by adding a normal SQL/LDAP userdb after the userdb prefetch. The order of definitions is significant. See below for examples.

LDAP: auth_bind=yes with auth_bind_userdn-template is incompatible with prefetch, because no passdb lookup is done then. If you want zero LDAP lookups, you might want to use static userdb instead of prefetch.

Here are my values for the auth-sql.conf.ext file (comments removed):

passdb { driver = sql args = /etc/dovecot/dovecot-sql.conf.ext } userdb { driver = prefetch } userdb { driver = sql args = /etc/dovecot/dovecot-sql.conf.ext }

Here are my comments for the last userdb entry as a reminder to myself:

Based on my readings this is used for doveadm queries which returns a list of all users, LDA (which we don't use) and LMTP (which we do). I believe the prefetch entry above will be used before this one, which would leave this entry to be used only for for doveadm queries that request a list of all users

To circle back, here are the remaining two queries from my copy of dovecot-sql.conf.ext:

# NEEDED for LDA/LMTP if we don't include a static userdb entry user_query = SELECT email as user,
'/var/vmail/%d/%n' as home
FROM virtual_users
WHERE email = '%u'
AND enabled = '1';

iterate_query = SELECT email AS user
FROM virtual_users
WHERE enabled='1';

My comments for the last query:

Query to get a list of all usernames. Requires a 'userdb' entry in # auth-sql.conf.ext that refers back to this file. Normally it matches the 'passdb' stanza aside from the name.

P.S.

The substitution used ('%u' vs '%n') will depend on how you have your user information stored. The comments in dovecot-sql.conf.ext provide some sample queries to illustrate that.

As my queries suggest, my db setup uses the 'username@example.org' format for user names. Had I thought about it a little more I might have opted to instead store the user and domain values in separate fields, but then again maybe not. Something to be aware of anyway.