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@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@mailtest.com.au,127.0.0.1): hit: #011proxy#011port=9110#011user=dmmailtest250@mailtest.com.au#011nopassword=y Jul 13 14:16:59 mail01 dovecot: auth: cache(blahtest01229@mailtest.com.au,127.0.0.1): NULL password access Jul 13 14:16:59 mail01 dovecot: auth: Debug: auth(blahtest01229@mailtest.com.au,127.0.0.1): username changed blahtest01229@mailtest.com.au -> dmmailtest250@mailtest.com.au Jul 13 14:16:59 mail01 dovecot: auth: Debug: client out: OK#0111#011user=dmmailtest250@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@mailtest.com.au,192.168.1.151): hit: {crypt}$1$eKFvOhwW$PYfPhY05SNUIRD439USMH/#011user=dmmailtest250@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.