How to configure Dovecot/Pigeonhole using MySQL authentication

Question

I have a working Dovecot server with Spamassassin. I can receive mail with Thunderbird and Bluemail clients. I use smtp2go to send mail out.

I want to configure Pigeonhole Sieve but find the configuration to be very daunting.

All mail is delivered to virtual mailboxes defined in the MySQL database. They go to /var/mail/vhosts. I am only hosting one domain right now.

So, what is the simplest way to set this up? All I basically want to do right now is have spam sent to the spam folder for each user. Output of dovecot -n:

# 2.2.33.2 (d6601f4ec): /etc/dovecot/dovecot.conf
# Pigeonhole version 0.4.21 (92477967)
# OS: Linux 4.15.0-47-generic x86_64 Ubuntu 18.04.2 LTS ext4
auth_mechanisms = plain login
log_path = /var/log/dovecot.log
mail_location = maildir:/var/mail/vhosts/%d/%n/
mail_privileged_group = mail
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 index ihave duplicate mime foreverypart extracttext
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 = 
}
passdb {
  args = /etc/dovecot/deny-users
  deny = yes
  driver = passwd-file
}
passdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
postmaster_address = dev@vietfeir.com
protocols = imap lmtp sieve
service auth-worker {
  user = vmail
}
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0666
    user = postfix
  }
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
  user = dovecot
}
service imap-login {
  inet_listener imap {
    port = 0
  }
  inet_listener imaps {
    port = 993
    ssl = yes
  }
}
service lmtp {
  unix_listener /var/spool/postfix/private/dovecot-lmtp {
    group = postfix
    mode = 0600
    user = postfix
  }
}
service pop3-login {
  inet_listener pop3 {
    port = 0
  }
  inet_listener pop3s {
    port = 995
    ssl = yes
  }
}
ssl = required
ssl_cert = </etc/letsencrypt/live/civicrm.vietfeir.com/fullchain.pem
ssl_client_ca_dir = /etc/ssl/certs
ssl_key =  # hidden, use -P to show it
userdb {
  driver = passwd
}
userdb {
  args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
  driver = static
}

Here is the current dovecot -n output:

# 2.2.33.2 (d6601f4ec): /etc/dovecot/dovecot.conf
# Pigeonhole version 0.4.21 (92477967)
# OS: Linux 4.15.0-47-generic x86_64 Ubuntu 18.04.2 LTS ext4
auth_mechanisms = plain login
lda_mailbox_autocreate = yes
log_path = /var/log/dovecot.log
mail_location = maildir:/var/mail/vhosts/%d/%n/
mail_privileged_group = mail
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 index ihave duplicate mime foreverypart extracttext
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 Spam {
    auto = subscribe
    special_use = \Junk
  }
  mailbox Trash {
    special_use = \Trash
  }
  prefix = 
}
passdb {
  args = /etc/dovecot/deny-users
  deny = yes
  driver = passwd-file
}
passdb {
  args = /etc/dovecot/dovecot-sql.conf.ext
  driver = sql
}
plugin {
  sieve_before = /var/mail/before.svbin
  sieve_extensions = +fileinto
  sieve_trace_dir = /var/log
  sieve_trace_level = matching
}
postmaster_address = dev@vietfeir.com
protocols = imap lmtp
service auth-worker {
  user = vmail
}
service auth {
  unix_listener /var/spool/postfix/private/auth {
    group = postfix
    mode = 0666
    user = postfix
  }
  unix_listener auth-userdb {
    mode = 0600
    user = vmail
  }
  user = dovecot
}
service imap-login {
  inet_listener imap {
    port = 0
  }
  inet_listener imaps {
    port = 993
    ssl = yes
  }
}
service lmtp {
  unix_listener /var/spool/postfix/private/dovecot-lmtp {
    group = postfix
    mode = 0600
    user = postfix
  }
}
service pop3-login {
  inet_listener pop3 {
    port = 0
  }
  inet_listener pop3s {
    port = 995
    ssl = yes
  }
}
ssl = required
ssl_cert = </etc/letsencrypt/live/civicrm.vietfeir.com/fullchain.pem
ssl_client_ca_dir = /etc/ssl/certs
ssl_key =  # hidden, use -P to show it
userdb {
  driver = passwd
}
userdb {
  args = uid=vmail gid=vmail home=/var/mail/vhosts/%d/%n
  driver = static
}

Note: I have tried referring to the before.sieve and before.svbin with no difference.

Here is the log:

      ## Started executing script 'before'
   2: header test
   2:   starting `:contains' match with `i;ascii-casemap' comparator:
   2:   extracting `X-Spam-Level' headers from message
   2:   matching value `**************************************************'
   2:     with key `*****' => 1
   2:   finishing match with result: matched
   2: jump if result is false
   2:   not jumping
   3: fileinto action
   3:   store message in mailbox `Hormel'
   4: stop command; end all script execution
      ## Finished executing script 'before'

It indicates that the message was stored in mailbox 'Hormel' but it did not create the mailbox.

I put it there because I intend to use it with Roundcube. After I got my filtering to work I went back and installed Roundcube and now users can create their own filters. — Vietyank, Apr 15 '19 at 07:56

anx · Accepted Answer · 2019-04-14T01:29:35.840

1

I do not see anything in your current config that would prevent you from simply adding another sieve file in front of any user-configured ones:

protocol lmtp {
    mail_plugins = $mail_plugins sieve
}
plugin {
    sieve_before = /var/mail/before.sieve
    sieve_extensions = +fileinto +mailbox
}

This gives you a /var/mail/before.sieve file to be executed before any other sieve script. You could move mails based on some header you know your spam filtering adds for you:

 require ["fileinto", "mailbox"];
 if header :contains "X-Spam-Level" "*****" {
      fileinto :create "Junk";
      stop;
 }

This file does not need to be writable as long as you manually compile it using sievec /var/mail/before.sieve (creating /var/mail/before.svbin)

Before you implement something like this, please check whether this is really useful to all your users - maybe they want to filter their mail differently, then sieve_default might be more appropriate.

edited Apr 14 '19 at 01:29

answered Apr 13 '19 at 20:31

anx

8,963
5
24
48

Actually, I am the only user currently. If I add others, I'll move on from here. – Vietyank Apr 13 '19 at 23:49
I get an error when I try to compile your example. before: line 3: error: unknown tagged argument ':create' for the fileinto command (reported only once at first occurrence). I'm still searching but haven't found an answer – Vietyank Apr 14 '19 at 00:28
It compiled okay but spam is not being moved into the folder specified. I've tried checking for another string, if header :contains "X-Spam-Flag" "YES" , but no luck there either. – Vietyank Apr 14 '19 at 00:59
Should the "plugin" directive refer to the compiled file or just the source file? – Vietyank Apr 14 '19 at 01:00
Got the filter working if the folder already exists but it won't create one if it doesn't exist. Still working on it – Vietyank Apr 14 '19 at 03:43
See the original post for a view of the log file – Vietyank Apr 14 '19 at 04:08
1

I discovered that it actually creates a folder but does not subscribe to it. This answer got me started and, with a bit of perseverance, I got this "simple" requirement working. Many thanks. – Vietyank Apr 14 '19 at 09:18

How to configure Dovecot/Pigeonhole using MySQL authentication

1 Answers1