[Dovecot] (no subject)

[Greg Ryman]

Hello, I've been out of the sysadmin game for some time and I've recently
setup a mail server. Everything is working except for the quota, which I
believe is likely an error in how I'm using my SQL queries. Any guidance,
pointers, or advice would be greatly appreciated.

root at mail:/etc/dovecot/conf.d# dovecot -n
# 2.0.19: /etc/dovecot/dovecot.conf
# OS: Linux 2.6.32-042stab079.6 x86_64 Ubuntu 12.04.3 LTS reiserfs
auth_mechanisms = plain login
dict {
  sqldomainquota = mysql:/etc/dovecot/dovecot-sql-domain.conf
  sqluserquota = mysql:/etc/dovecot/dovecot-dict-sql-user.conf
}
first_valid_uid = 150
last_valid_uid = 150
mail_gid = mail
mail_location = maildir:/var/vmail/%d/%n
mail_plugins = quota
mail_uid = vmail
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
passdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
plugin {
  antispam_allow_append_to_spam = YES
  antispam_backend = dspam
  antispam_dspam_args = --user;%u;--deliver=;--source=error
  antispam_dspam_binary = /usr/bin/dspam
  antispam_dspam_notspam = --class=innocent
  antispam_dspam_result_header = X-DSPAM-Result
  antispam_dspam_spam = --class=spam
  antispam_signature = X-DSPAM-Signature
  antispam_signature_missing = error
  antispam_spam = Spam;Junk
  antispam_trash = trash;Trash
  autocreate = Trash
  autocreate2 = Junk
  autocreate3 = Sent
  autocreate4 = Drafts
  autocreate5 = Archive
  quota = dict:User Quota::proxy::sqluserquota
  quota_rule2 = Trash:storage=+10%%
  sieve = ~/.dovecot.sieve
  sieve_after = /etc/sieve/conf.d/after
  sieve_before = /etc/sieve/conf.d/before
  sieve_dir = ~/sieve
}
postmaster_address = admin at gryman.com
protocols = " imap sieve pop3"
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0666
    user = postfix
  }
  unix_listener auth-userdb {
    group = mail
    mode = 0600
    user = vmail
  }
}
service dict {
  unix_listener dict {
    mode = 0600
    user = vmail
  }
}
ssl_cert = </etc/ssl/certs/dovecot.pem
ssl_key = </etc/ssl/private/dovecot.pem
userdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
protocol lmtp {
  mail_plugins = " sieve autocreate quota"
}
protocol lda {
  mail_plugins = sieve quota
}
protocol imap {
  mail_plugins = quota antispam autocreate quota imap_quota
}

root at mail:/etc/dovecot# grep -v '^ *\(#.*\)\?$' dovecot-sql.conf
driver = mysql
connect = host=localhost dbname=mail user=mail password=mailpassword
default_pass_scheme = MD5-CRYPT
password_query = \
  SELECT username as user, password, '/var/vmail/%d/%n' as userdb_home, \
  'maildir:/var/vmail/%d/%n' as userdb_mail, 150 as userdb_uid, 8 as
userdb_gid \
  FROM mailbox WHERE username = '%u' AND active = '1'
user_query = \
  SELECT '/var/vmail/%d/%n' as home, 'maildir:/var/vmail/%d/%n' as mail, \
  150 AS uid, 8 AS gid, concat('dirsize:storage=', quota) AS quota \
  FROM mailbox WHERE username = '%u' AND active = '1'

root at mail:/etc/dovecot# tail -n 8 /etc/postfix/master.cf
# DSPAM
dspam     unix  -       n       n       -       10      pipe
  flags=Ru user=dspam argv=/usr/bin/dspam --deliver=innocent,spam --user
$recipient -i -f $sender -- $recipient

# Integration with Dovecot - hand mail over to it for local delivery, and
# run the process under the vmail user and mail group.
dovecot      unix   -        n      n       -       -   pipe
  flags=DRhu user=vmail:mail argv=/usr/lib/dovecot/dovecot-lda -d
$(recipient)


-- 
Greg Ryman