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
Greg, Just taking a cursory look.... On Mon, 2013-09-16 at 19:04 -0700, Greg Ryman wrote:> 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 ihaveBe careful declaring capabilities, personally I dont eg: protocol sieve { managesieve_max_line_length = 65536 managesieve_logout_format = bytes=%i/%o managesieve_implementation_string = Dovecot Pigeonhole managesieve_max_compile_errors = 5 mail_max_userip_connections = 10 } seems to work just fine, though 99% of our users, use pop3, not imap> protocols = " imap sieve pop3"You have defined a lmtp below but are not defining it here in protocols, by looks of postfix master.cf, your not using it so dont want it at all anywaymso if you are not intending on using it, clean it up below> protocol lmtp { > mail_plugins = " sieve autocreate quota" > }Youve already declared quota in global plugins> protocol lda { > mail_plugins = sieve quotareplace to: mail_plugins = $mail_plugins sieve> }> protocol imap { > mail_plugins = quota antispam autocreate quota imap_quota > }mail_plugins = $mail_plugins imap_quota (antispam/autocreate - check up on, I dont use them so wont comment)> 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-CRYPTnot affecting, but a word of advice, if you have a modern system change that to CRYPT (no, it if you have a modern OS it will NOT use the old exploitable, 8 char limited "crypt" but will allow you to use salted sha512 etc)> 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'Try cleaning that up, for example password_query = SELECT username, password FROM mailbox WHERE username='%u' and active='1' (it doesnt need all that other stuff, it gets it from user query) user_query looks ok (see below), but you can drop off the "and active" stuff> 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' >concat('*:storage=', quota,'M') if you dont specify a type, I might be wrong, but I think it would likely default to bytes, which might be ok if thats how you've setup your database anyway.> # 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) > >dovecot-lda -f ${sender} -e -d ${user}@${nexthop} would be better, can allow you to use recip delim as well. -------------- next part -------------- A non-text attachment was scrubbed... Name: signature.asc Type: application/pgp-signature Size: 490 bytes Desc: This is a digitally signed message part URL: <http://dovecot.org/pipermail/dovecot/attachments/20130917/c3904eeb/attachment-0001.bin>