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

[Roland Rosenfeld]

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