[Dovecot] (no subject)
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@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@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@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@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 ihave
Be 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 quota
replace 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@mail:/etc/dovecot# grep -v '^ *\(#.*\)\?$' dovecot-sql.conf driver = mysql connect = host=localhost dbname=mail user=mail password=mailpassword default_pass_scheme = MD5-CRYPT
not 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.
Thank you Noel. I did manage to get this working before you posted. As I thought, there was a problem with my SQL syntax. Now my issue is that the 1G limit I have hardset in my files is not being overwritten by the SQL query. I assume this is again because of my query.
On Mon, Sep 16, 2013 at 10:40 PM, Noel Butler <noel.butler@ausics.net>wrote:
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 ihave
Be 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 quota
replace 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@mail:/etc/dovecot# grep -v '^ *\(#.*\)\?$' dovecot-sql.conf driver = mysql connect = host=localhost dbname=mail user=mail password=mailpassword default_pass_scheme = MD5-CRYPT
not 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.
-- Greg Ryman
On Mon, 2013-09-16 at 23:07 -0700, Greg Ryman wrote:
Thank you Noel. I did manage to get this working before you posted. As I thought, there was a problem with my SQL syntax. Now my issue is that the 1G limit I have hardset in my files is not being overwritten by the SQL query. I assume this is again because of my query.
I'd say so, yes, once you corrected that,it should work
Noel,
What a superb reply. I wish they all were so thorough. It not only helps the OP, but also anyone who views it now or later.
steve campbell
On 9/17/2013 1:40 AM, Noel Butler wrote:
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 ihave Be 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 quota replace 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@mail:/etc/dovecot# grep -v '^ *\(#.*\)\?$' dovecot-sql.conf driver = mysql connect = host=localhost dbname=mail user=mail password=mailpassword default_pass_scheme = MD5-CRYPT
not 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.
On 16 Sep 2013, at 23:40 , Noel Butler <noel.butler@ausics.net> wrote:
mail_plugins = $mail_plugins imap_quota (antispam/autocreate - check up on, I dont use them so wont comment)
Anyone out there who does use antispam and wants to comment?
-- 'On whose authority?' demanded Wert. Trymon turned his grey eyes on him. 'Mine. I need no other.' --The Light Fantastic
On Tue, 2013-09-17 at 18:42 -0600, LuKreme wrote:
On 16 Sep 2013, at 23:40 , Noel Butler <noel.butler@ausics.net> wrote:
mail_plugins = $mail_plugins imap_quota (antispam/autocreate - check up on, I dont use them so wont comment)
Anyone out there who does use antispam and wants to comment?
Sure I'll comment :) all antispam tasks are best left to the MTA or its associated software (amavisd, mailscanner, etc), that's part of its job - not a receivers, like pop3/imap...
Hi all,
Actually the antispam plugin for dovecot provides (re)training functionality initiated by moving a message to/from Spam folder. It was originally developed for DSPAM filtering but I assume is now more general. Personally I stopped using DSPAM and other content-based filters some time ago (in favor of more thorough postfix checks + DNSBL), but when I used it, the plugin worked just fine =)
Best wishes Eugene
-----Original Message----- From: Noel Butler Sent: Wednesday, September 18, 2013 6:19 AM To: dovecot@dovecot.org Subject: Re: [Dovecot] mail_plugins = antispam (was Re: (no subject))
On Tue, 2013-09-17 at 18:42 -0600, LuKreme wrote:
On 16 Sep 2013, at 23:40 , Noel Butler <noel.butler@ausics.net> wrote:
mail_plugins = $mail_plugins imap_quota (antispam/autocreate - check up on, I dont use them so wont comment)
Anyone out there who does use antispam and wants to comment?
Sure I'll comment :) all antispam tasks are best left to the MTA or its associated software (amavisd, mailscanner, etc), that's part of its job - not a receivers, like pop3/imap...
On 17 Sep 2013, at 21:48 , Eugene <genie@geniechka.ru> wrote:
Actually the antispam plugin for dovecot provides (re)training functionality initiated by moving a message to/from Spam folder.
Hmm, that sounds interesting, retraining is a real problem. I've setup scripts to do it, but it requires users moving spam out of the spam folder into a specific "notSpam" folder, and they just won't do that.
Personally I stopped using DSPAM and other content-based filters some time ago (in favor of more thorough postfix checks + DNSBL), but when I used it, the plugin worked just fine =)
I use postfix with postscreen and RBLs, but that still lets in a considerable amount of spam, so I run SpamAssassin on the mail that does get through.
-- For more than a thousand generations the Jedi were the guardians of peace and justice in the galaxy. Before the dark times. Before the Empire.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1
LuKreme wrote:
On 17 Sep 2013, at 21:48 , Eugene <genie@geniechka.ru> wrote:
Actually the antispam plugin for dovecot provides (re)training functionality initiated by moving a message to/from Spam folder.
Hmm, that sounds interesting, retraining is a real problem. I've setup scripts to do it, but it requires users moving spam out of the spam folder into a specific "notSpam" folder, and they just won't do that.
your users keep ham-messages in the Spam folder? The antispam plugin triggers as soon as a message is copied from a SPAM folder into another folder, that does neither match spam nor trash pattern.
However, the problem is what spam folders to use, IMHO. If you use a folder, that is used by MUAs to automatically move spam into, you might learn false positives. Some systems cannot "unlearn" a message 100%, so you poison the database.
If you use another folder, your users won't move messages from the MUA's junk folder there, hence, you miss spam.
Steffen -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.9 (Darwin) Comment: Using GnuPG with undefined - http://www.enigmail.net/
iQEVAwUBUkKUfF3r2wJMiz2NAQKvQgf+M7ZKt6DKiPrKP/A6Qlp1TU9Yjs32Um5E Th1vHXPRE4p0xaRP9XQqt9hbreHfzRCNLk7ADKG3SfFEgtfzgzFhCJ5CShNkuIpI rWqt58X0fE5T0mYJii4HCG1gPaE3Pe0m8QQldxIjIpZ/pIYnIrrcgb7Dy2KV4fJ4 1UpSWsKa0iKKaKuT96ND7U5U5mO2LqSLQRRVKA4bQy6axy7zOhqoWznWTPwX5zfI M0y/ze4NHE+r7SD5kM4o2YG/1oJ0v16LCk51OBpCp+boI1TlHlHdomt4n3Pb2JRk MQzh5y5RoHcM0WB+BEidlk9nCGXvpK4Hx5zMz8pA0GCi9vDJnbhMKg== =vLSz -----END PGP SIGNATURE-----
participants (6)
-
Eugene
-
Greg Ryman
-
LuKreme
-
Noel Butler
-
Steffen
-
Steve Campbell