userdb lookup not possible with only userdb prefetch

[deoren]

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 at 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.