[Dovecot] Quota not working with dict proxy

[Chris Richards]

Hello all,
I'm sure this has been covered somewhere before, but my googlefu is not up
to the challenge.

Basically, I'm trying to configure quota plugin to use a dictionary
service (specifically proxy with mysql) so that I can store the quota
usage in a database and use that information in a lookup for postfix to
reject mail if over quota.  I'm doing this because postfix+avamis+dovecot
setup with amavis re-injecting into postfix results in mail being
effectively accepted before dovecot lmtp knows if mailbox is full.

I'm currently using quota maildir:

quota = maildir:User quota

and this works;

doveadm -Df tab quota get -u 'user at domain'

Quota name      Type    Value   Limit   %
User quota      STORAGE 55388   204800  27
User quota      MESSAGE 4883    -       0

When I use quota dict:

quota = dict:User quota:%u:proxy::quota

I get this:

"doveadm -f tab quota get -u user at domain"

Quota name      Type    Value   Limit   %
User quota      STORAGE 0       204800  0
User quota      MESSAGE 0       -       0

So far so good.

But manually setting database entry to values retrieved above with maildir
quota results in this:

Quota name      Type    Value   Limit   %
User quota      STORAGE 54      204800  0
User quota      MESSAGE 4883    -       0

I'm guessing this is returning values in KB, so that makes sense, I guess.

Attempting to recalc quota on one account using command:

"doveadm -f tab quota recalc -u user at domain"

returns with nothing, and when I repeat 'quota get' command, it says:

doveadm(user at domain): Fatal: User doesn't exist

Err............what?!

Indeed, looking in the database shows the account in question was actually
DELETED!

Obviously, I've got something messed here, but I don't know what.

I need some guidance here.

Dovecot version is 2.1.12

Here is my config:

# 2.1.12: /etc/dovecot/dovecot.conf
# OS: Linux 3.7.5-hardened-r1 x86_64 Gentoo Base System release 2.1 ext4
auth_master_user_separator = *
auth_mechanisms = plain login
auth_username_chars =
abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ01234567890.-_@&
auth_verbose_passwords = plain
default_process_limit = 200
dict {
  quota = mysql:/etc/dovecot/dovecot-dict-sql.conf.ext
}
disable_plaintext_auth = no
login_greeting = Awaiting command...
mail_location = maildir:/home/vmail/%d/%n/Maildir
mail_plugins = " quota"
mail_privileged_group = 100
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
namespace inbox {
  inbox = yes
  location =
  mailbox Drafts {
    special_use = \Drafts
  }
  mailbox Junk {
    special_use = \Junk
  }
  mailbox Sent {
    special_use = \Sent
  }
  mailbox "Sent Messages" {
    special_use = \Sent
  }
  mailbox Trash {
    special_use = \Trash
  }
  prefix = INBOX.
  separator = .
  type = private
}
passdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
plugin {
  expire = Trash 7
  expire2 = Trash/* 7
  expire3 = Spam 7
  quota = dict:User quota:%u:proxy::quota
  quota_rule = *:storage=200M
  quota_warning = storage=99%% quota-warning 99 %n %d
  quota_warning2 = storage=95%% quota-warning 95 %n %d
  quota_warning3 = storage=80%% quota-warning 80 %n %d
  quota_warning4 = -storage=95%% quota-warning 'less than 95' %n %d
  sieve = ~/.dovecot.sieve
  sieve_default = /home/vmail/dovecot/sieve/default.sieve
  sieve_dir = ~/sieve
  sieve_global_dir = /home/vmail/dovecot/sieve
}
protocols = imap pop3 sieve lmtp
service auth-worker {
  user = $default_internal_user
}
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0666
    user = postfix
  }
  unix_listener auth-userdb {
    group = dovecot
    mode = 0666
    user = dovecot
  }
  user = $default_internal_user
}
service dict {
  unix_listener dict {
    mode = 0600
    user = vmail
  }
}
service lmtp {
  unix_listener /var/spool/postfix/private/dovecot-lmtp {
    group = postfix
    mode = 0600
    user = postfix
  }
}
service quota-warning {
  executable = script /etc/dovecot/quota-warning.sh
  unix_listener quota-warning {
    user = vmail
  }
  user = dovecot
}
ssl_cert = </etc/ssl/dovecot/server.pem
ssl_key = </etc/ssl/dovecot/server.key
userdb {
  driver = prefetch
}
userdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
protocol lmtp {
  mail_plugins = sieve quota
  postmaster_address = postmaster at domain
}
protocol lda {
  mail_plugins = " quota sieve quota"
}
protocol imap {
  mail_max_userip_connections = 20
  mail_plugins = " quota quota imap_quota"
}
protocol pop3 {
  mail_plugins = " quota quota"
}


>From dovecot-dict-sql.conf.ext:

connect = host=localhost dbname=maildb user=dbuser password=dbpass

# CREATE TABLE quota (
#   username varchar(100) not null,
#   bytes bigint not null default 0,
#   messages integer not null default 0,
#   primary key (username)
# );

map {
  pattern = priv/quota/storage
  table = email
  username_field = address
  value_field = quota_bytes
}
map {
  pattern = priv/quota/messages
  table = email
  username_field = address
  value_field = quota_messages
}

# CREATE TABLE expires (
#   username varchar(100) not null,
#   mailbox varchar(255) not null,
#   expire_stamp integer not null,
#   primary key (username, mailbox)
# );

map {
  pattern = shared/expire/$user/$mailbox
  table = email
  value_field = expire_stamp

  fields {
    address = $user
    maildir = $mailbox
  }
}

Other than some guesswork, I've not got the FOGGIEST idea what the
settings in the file actually mean; it doesn't seem to be documented
anywhere.

Also, the quota plugin settings, at least with respect to using a proxy
service, don't seem to be real explanatory either, e.g. the reason I'm
using %u for the user is because I stumbled across a post suggesting that
leaving it blank would result in just getting the username instead of the
username at domain.  This is not at all obvious from the documentation, IMO. 
The docs would benefit greatly from some example use cases (no, I'm not
volunteering; I'm TERRIBLE with documentation ;) ).

Anyway, if someone could proffer some help or at least guidance here, I'd
be very grateful.