[Dovecot] ACL with MySQL dict => full table scans

16 Jul 2012


      Hi!
I'm just installing ACL support on 2.1.6.
When a user changes these ACLs, this results in a full table scan on
the dictionary, that tells dovecot who shares with whom.
Here's what queries I see when I run
$ doveadm acl delete -u user1 shared/user2/folder user=user1
1: SELECT dummy,to_user,from_user FROM imap_acl_shares
2: SELECT dummy,from_user FROM imap_acl_anyone
3: BEGIN
4: DELETE FROM imap_acl_shares WHERE to_user = 'user1' AND from_user = 'user1'
5: INSERT INTO imap_acl_shares (dummy,to_user,from_user) VALUES ('1','user1','user1') ON DUPLICATE KEY UPDATE dummy='1'
6: DELETE FROM imap_acl_shares WHERE to_user = 'user2' AND from_user = 'user1'
7: INSERT INTO imap_acl_shares (dummy,to_user,from_user) VALUES ('1','user2','user1') ON DUPLICATE KEY UPDATE dummy='1'
8: COMMIT
Line 2 is okay, since imap_acl_anyone is always empty (I'm not able to
disable this query, otherwise dovecot spawns errors about a missing
map, but I can live with this).
Don't ask me what lines 4/5 are repeated in 6/7, but these use indexes
and I can live with this.
But what the hell is line 1?  Why doesn't dovecot use a WHERE clause
here with either to_user='user1', to_user='user2', from_user='user1',
or from_user='user2'?  All these WHERE clauses would use an index and
speedup the query.  Without the WHERE clause I expect 100000 or more
results on our production system, which could slow down it massively.
Here's my setup:
/etc/dovecot/dovecot-dict-sql.conf:
connect = host=foo dbname=mail user=imap password=hidden
map {
pattern = shared/shared-boxes/user/$to/$from
table = imap_acl_shares
value_field = dummy
fields {
from_user = $from
to_user = $to
}
}
map {
pattern = shared/shared-boxes/anyone/$from
table = imap_acl_anyone
value_field = dummy
fields {
from_user = $from
}
}
And dovecot -n:
# 2.1.6: /etc/dovecot/dovecot.conf
# OS: Linux 2.6.32-5-amd64 x86_64 Debian 6.0.5
auth_cache_negative_ttl = 5 mins
auth_cache_size = 100 M
auth_cache_ttl = 5 mins
auth_default_realm = example.com
auth_master_user_separator = *
auth_mechanisms = plain login
auth_verbose = yes
dict {
acl = mysql:/etc/dovecot/dovecot-dict-sql.conf
}
disable_plaintext_auth = no
listen = *
log_timestamp = "%Y-%m-%d %H:%M:%S "
mail_fsync = always
mail_gid = 999
mail_location = maildir:~/mail
mail_plugins = acl quota
mail_uid = 999
managesieve_notify_capability = mailto
managesieve_sieve_capability = fileinto reject envelope encoded-character vacation subaddress comparator-i;ascii-numeric relational regex imap4flags copy include variables body enotify environment mailbox date ihave imapflags
mmap_disable = yes
namespace {
list = children
location = maildir:%%h/mail:INDEX=~/mail/shared/%%u
prefix = shared/%%u/
separator = /
subscriptions = no
type = shared
}
namespace inbox {
inbox = yes
location =
prefix =
separator = /
type = private
}
passdb {
args = /etc/dovecot/passwd.master
driver = passwd-file
master = yes
pass = yes
}
passdb {
args = /etc/dovecot/dovecot-sql.conf
driver = sql
}
plugin {
acl = vfile
acl_shared_dict = proxy::acl
quota = maildir
sieve = ~/.dovecot.sieve
sieve_dir = ~/mail/sieve
sieve_extensions = +imapflags
}
protocols = imap pop3 sieve
service auth {
unix_listener auth-master {
group = vmail
mode = 0600
user = vmail
}
}
service dict {
unix_listener dict {
group = vmail
mode = 0600
user = vmail
}
}
service imap {
process_limit = 4000
}
service managesieve-login {
inet_listener sieve {
port = 4190
}
}
service managesieve {
process_limit = 100
}
service pop3 {
process_limit = 1000
}
shutdown_clients = no
ssl = no
syslog_facility = local2
userdb {
args = /etc/dovecot/dovecot-sql.conf
driver = sql
}
verbose_proctitle = yes
protocol imap {
mail_max_userip_connections = 10
mail_plugins = quota imap_quota acl imap_acl
}
protocol pop3 {
mail_plugins = quota
pop3_no_flag_updates = yes
pop3_uidl_format = %v.%u
}
Tschoeeee
    Roland

[Dovecot] ACL with MySQL dict => full table scans

Roland Rosenfeld