New subject: Re[2]: Sieve Filters with MySQL

23 Jul 2024


      Me again! I am trying to get sieve filters working with a MySQL backend
but same to be coming up short. Right now, the error I am getting
received is:

"User has no active script in storage
dict:proxy::sieve;user=username@domain.com"

The “username@domain.com” is obviously the email address of the
receiving mailbox.
Here’s my latest config:
# 2.3.16 (7e2e900c1a): /etc/dovecot/dovecot.conf
# Pigeonhole version 0.5.16 (09c29328)
# OS: Linux 6.5.0-1020-aws x86_64 Ubuntu 22.04.4 LTS nfs4
# Hostname: ec2-us-east-1a-arda-mail-001.ardaemail.com
auth_debug = yes
auth_mechanisms = plain login
auth_verbose = yes
auth_worker_max_count = 5
base_dir = /var/run/dovecot
debug_log_path = /var/log/dovecot/debug.log
default_internal_group = ardaemail
default_internal_user = ardaemail
dict {
quota = mysql:/etc/dovecot/inc.d/inc.quota.user.sql.conf
sieve = mysql:/etc/dovecot/inc.d/inc.sieve.sql.conf
sql = mysql:/etc/dovecot/inc.d/inc.lastlogin.sql.conf
}
hostname = = mail.ardaemail.com
info_log_path = /var/log/dovecot/info.log
instance_name = ec2-us-east-1a-arda-mail-001.ardaemail.com
listen = 172.16.0.200
log_path = /var/log/dovecot/general.log
login_greeting = Welcome to ArdaEmail
mail_debug = yes
mail_gid = ardaemail
mail_location = maildir:/mnt/mail/%d/%n
mail_max_userip_connections = 1000
mail_plugins = " quota mailbox_alias"
mail_server_admin = mailto:support@ardaemail.com
mail_uid = ardaemail
maildir_stat_dirs = yes
namespace inbox {
inbox = yes
location =
mailbox Archive {
auto = subscribe
special_use = \Archive
}
mailbox Drafts {
auto = subscribe
special_use = \Drafts
}
mailbox Junk {
auto = subscribe
autoexpunge = 90 days
special_use = \Junk
}
mailbox Sent {
auto = subscribe
special_use = \Sent
}
mailbox Trash {
auto = subscribe
autoexpunge = 30 days
special_use = \Trash
}
prefix =
}
passdb {
args = /etc/dovecot/inc.d/inc.sql.conf
driver = sql
}
plugin {
last_login_dict = proxy::sql
last_login_key = # hidden, use -P to show it
last_login_precision = ms
mailbox_alias_new = Junk Email
mailbox_alias_new2 = Junk E-mail
mailbox_alias_new3 = Sent Messages
mailbox_alias_new4 = Sent Items
mailbox_alias_new5 = Deleted Items
mailbox_alias_new6 = Junk Mail
mailbox_alias_old = Junk
mailbox_alias_old2 = Junk
mailbox_alias_old3 = Sent
mailbox_alias_old4 = Sent
mailbox_alias_old5 = Trash
mailbox_alias_old6 = Junk
quota = dict:User Quota::proxy::quota
quota_exceeded_message = Mailbox is full and cannot receive any more
emails
quota_status_nouser = DUNNO
quota_status_success = DUNNO
quota_warning = bytes=95%% quota-warning 95 %u
quota_warning2 = bytes=85%% quota-warning 85 %u
quota_warning3 = bytes=75%% quota-warning 75 %u
sieve = dict:proxy::sieve
sieve_plugins = sieve_imapsieve sieve_extprograms
}
postmaster_address = postmaster@ardaemail.com
protocols = imap lmtp
service auth-worker {
group = ardaemail
user = ardaemail
}
service auth {
group = ardaemail
unix_listener /var/spool/postfix/private/auth {
group = postfix
mode = 0666
user = postfix
}
unix_listener auth-userdb {
group = ardaemail
mode = 0666
user = ardaemail
}
user = ardaemail
}
service imap-login {
inet_listener imap {
port = 0
}
inet_listener imaps {
port = 993
ssl = yes
}
process_min_avail = 8
service_count = 0
}
service imap {
vsz_limit = 4 G
}
service lmtp {
unix_listener /var/spool/postfix/private/dovecot-lmtp {
group = postfix
mode = 0666
user = postfix
}
}
service pop3-login {
inet_listener pop3 {
port = 0
}
inet_listener pop3s {
port = 0
}
}
service quota-status {
executable = /usr/lib/dovecot/quota-status -p postfix
unix_listener /var/spool/postfix/private/quota-status {
user = postfix
}
}
service quota-warning {
executable = script /etc/dovecot/bin/quota-warning.sh
unix_listener quota-warning {
group = ardaemail
mode = 0666
user = ardaemail
}
}
service submission-login {
inet_listener submission {
port = 0
}
}
ssl = required
ssl_cert = </etc/letsencrypt/live/mail.ardaemail.com/fullchain.pem
ssl_client_ca_dir = /etc/ssl/certs
ssl_dh = # hidden, use -P to show it
ssl_key = # hidden, use -P to show it
ssl_prefer_server_ciphers = yes
userdb {
args = /etc/dovecot/inc.d/inc.sql.conf
driver = sql
}
protocol imap {
imap_metadata = yes
mail_plugins = " quota mailbox_alias imap_sieve imap_quota last_login"
}
protocol lmtp {
mail_plugins = " quota mailbox_alias sieve”
}
This output clearly does not include my Sieve configuration maps so
here’s those:
connect = host=redacted dbname=redacted user=redacted password=redacted
map {
pattern = priv/sieve/name/$script_name
table = mailboxFilter
username_field = email
value_field = uuid
fields {
name = $script_name
}
}
map {
pattern = priv/sieve/data/$uuid
table = mailboxFilter
username_field = email
value_field = filter
fields {
uuid = $uuid
}
}
And here’s the table schema:
CREATE TABLE mailboxFilter (
id int(11) unsigned NOT NULL AUTO_INCREMENT,
uuid varchar(36) NOT NULL,
email varchar(192) NOT NULL,
name varchar(255) NOT NULL,
filter longtext NOT NULL,
isActive bit(1) NOT NULL DEFAULT b’1’,
createdAt datetime NOT NULL DEFAULT current_timestamp(),
updatedAt datetime NOT NULL DEFAULT current_timestamp() ON UPDATE
current_timestamp(),
PRIMARY KEY (id),
UNIQUE KEY UUID (uuid),
UNIQUE KEY EMAIL (email)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4
COLLATE=utf8mb4_general_ci;
When I run manually queries, everything works fine though I am not
entirely sure what the queries are that Dovecot is executing.
Any help is appreciated! Also, if I could use the “isActive” field, that
would be great as well. My plan to start here is to only have one record
in the table per mailbox which would contain all Sieve filters.
Eventually, it would be nice if I could adjust the configuration to
allow for more than one that returns concatenated so any help there
would be great as well!
Thank you!

Sieve Filters with MySQL

Adam Miller

Aki Tuomi

Adam Miller

Aki Tuomi

Adam Miller

Aki Tuomi

Adam Miller

Adam Miller

Adam Miller

Aki Tuomi

Adam Miller

tags

participants (2)