[Dovecot] SQLite dovecot query caching

[Dominic Malolepszy]

On 24/06/12 04:57, Timo Sirainen wrote:
> On Sun, 2012-06-24 at 04:20 +1000, Dominic Malolepszy wrote:
>> On 24/06/12 3:39 AM, Timo Sirainen wrote:
>>> On Sun, 2012-06-24 at 03:36 +1000, Dominic Malolepszy wrote:
>>>> Hi,
>>>>
>>>> I am wondering if Dovecot caches SQLite queries, and how well it works
>>>> in high performance setups. I am particularly interested because in the
>>>> below thread SQLite has been suggested as a means of Dovecot proxying
>>>> connections to different ports.
>>> You can enable auth cache:
>>> http://wiki2.dovecot.org/Authentication/Caching
>>>
>>>
>>
>> This is a per user caching though, it will still have to perform a sql
>> look up each time a unique user authenticates to determine what port the
>> proxy should forward each connection. Is that accurate?
>
> It caches the passdb lookup. The cache key consists of the given %
> variables in the SQL query. So if your SQL query doesn't contain %n/%u
> then the cache doesn't add per-user entries.
>
>

Hi,

I had a chance to play around with this in the lab. The cache hit 
appears to be returning values that should actually be ignored 
particularly the username for the previous cached entry. This is causing 
it to rewrite the username using what is in the cache. The passdb config 
is as follows:

passdb {
   driver = sql
   args = /etc/dovecot-director/dovecot-sql.conf
}


The dovecot-sql.conf config contains:

driver = sqlite
connect = /tmp/dovecot-sqlite.db

password_query = select 'y' as proxy, \
   NULL AS password, 'y' as nopassword, \
   NULL AS user, NULL AS username, \
  case '%a' \
   when '110' then '9110' \
   when '995' then '9995' \
   when '143' then '9143' \
   when '993' then '9993' end \
  as port;

To demonstrate I log into POP3 first as user 'dmmailtest250' which 
populates the cache with 
(#011proxy#011port=9110#011user=dmmailtest250 at mailtest.com.au#011nopassword=y). 
Than I log into POP3 as non existent user 'blahtest01229', which causes 
the passdb to return user=dmmailtest250. Below is what is captured in 
the logs:


Jul 13 14:16:59 mail01 dovecot: auth: Debug: client in: 
AUTH#0111#011PLAIN#011service=pop3#011secured#011lip=127.0.0.1#011rip=127.0.0.1#011lport=110#011rport=52873#011resp=AGJsYWh0ZXN0MDEyMjkAcGFzc3dvcmQ=
Jul 13 14:16:59 mail01 dovecot: auth: Debug: 
cache(blahtest01229 at mailtest.com.au,127.0.0.1): hit: 
#011proxy#011port=9110#011user=dmmailtest250 at mailtest.com.au#011nopassword=y
Jul 13 14:16:59 mail01 dovecot: auth: 
cache(blahtest01229 at mailtest.com.au,127.0.0.1): NULL password access
Jul 13 14:16:59 mail01 dovecot: auth: Debug: 
auth(blahtest01229 at mailtest.com.au,127.0.0.1): username changed 
blahtest01229 at mailtest.com.au -> dmmailtest250 at mailtest.com.au
Jul 13 14:16:59 mail01 dovecot: auth: Debug: client out: 
OK#0111#011user=dmmailtest250 at mailtest.com.au#011proxy#011port=9110#011pass=password
Jul 13 14:16:59 mail01 dovecot: auth: Debug: client in: 
AUTH#0111#011PLAIN#011service=pop3#011secured#011lip=192.168.1.151#011rip=192.168.1.151#011lport=9110#011rport=60096#011resp=AGRtb2llbWFpbHRlc3QyNTBAb3B0dXNuZXQuY29tLmF1AHBhc3N3b3Jk
Jul 13 14:16:59 mail01 dovecot: auth: Debug: 
cache(dmmailtest250 at mailtest.com.au,192.168.1.151): hit: 
{crypt}$1$eKFvOhwW$PYfPhY05SNUIRD439USMH/#011user=dmmailtest250 at mailtest.com.au#011userdb_home=/m5/dm/dmmailtest250#011userdb_quota_rule=*:backend=52428800S

I have not specified any sort of cache_key explicitly, because according 
to http://wiki2.dovecot.org/Authentication/Caching "For SQL and LDAP 
lookups Dovecot figures this out automatically by using all the used 
%variables as the cache key", which means that %a is the key ie the 
lport number which is 110 in this case. I tried specifying a NULL user 
and username, but that does not help.

Cheers,
Dominic.