Okay, I will give that a shot and see if it works. I have another question also regarding sieve filters. I am running RSPAMD and have a Sieve filter set up to move emails marked as SPAM to the “Junk” folder. In my Sieve configuration, I also have the following configuration settings:

sieve_spamtest_status_type = score sieve_spamtest_status_header = X-Spam-Level: .*? \[([[:digit:]]+\.[[:digit:]]+) / [[:digit:]]+\.[[:digit:]]+\]; sieve_spamtest_max_value = 10

What exactly is this doing and is this required if I am using a Sieve script to move the emails to Junk?

Here’s the rest of my Sieve config:

dict { sieve = mysql:redacted }

plugin { sieve = dict:proxy::sieve

sieve_plugins = sieve_imapsieve sieve_extprograms sieve_extensions = +editheader +mboxmetadata +servermetadata +imapflags +notify +spamtest +spamtestplus +virustest +relational +comparator-i;ascii-numeric

sieve_global = /mnt/mail/sieve/global/ sieve_global_extensions = +vnd.dovecot.pipe +vnd.dovecot.environment

sieve_quota_max_storage = 0

sieve_max_actions = 0 sieve_max_script_size = 0

sieve_pipe_bin_dir = /mnt/mail/sieve/pipe/

#sieve_spamtest_status_type = score #sieve_spamtest_status_header = X-Spam-Level: .*? \[([[:digit:]]+\.[[:digit:]]+) / [[:digit:]]+\.[[:digit:]]+\]; #sieve_spamtest_max_value = 10

sieve_before = /mnt/mail/sieve/global/before/spam.sieve

imapsieve_mailbox1_name = Junk imapsieve_mailbox1_causes = COPY imapsieve_mailbox1_before = file:/mnt/mail/sieve/global/learn-spam.sieve imapsieve_mailbox2_name = * imapsieve_mailbox2_from = Junk imapsieve_mailbox2_causes = COPY imapsieve_mailbox2_before = file:/mnt/mail/sieve/global/learn-ham.sieve }

Thank you!

------ Original Message ------ From "Aki Tuomi" aki.tuomi@open-xchange.com To "Aki Tuomi via dovecot" dovecot@dovecot.org; "Adam Miller" adam@ardaemail.com Date 7/26/2024 12:54:28 AM Subject Re: [EXT] Re[8]: Sieve Filters with MySQL

Sure. If you want. But this prevents your users from having multiple Sieve scripts, like one for vacation, and one for not-vacation.

But sure. you can have sieveFilter table with just email and id number of 1.

Aki

...

On 26/07/2024 10:33 EEST Aki Tuomi via dovecot dovecot@dovecot.org wrote:

It's OK to hide it to the mailbox table as sieveFilterID.

Then you can do

map { pattern = priv/sieve/name/default table = mailbox username_field = email value_field = sieveFilterID }

Aki

...

On 26/07/2024 10:14 EEST Adam Miller adam@ardaemail.com wrote:

------ Original Message ------ From "Aki Tuomi via dovecot" dovecot@dovecot.org To "Adam Miller" adam@ardaemail.com; "Adam Miller via dovecot" dovecot@dovecot.org Date 7/23/2024 10:06:38 PM Subject Re: Re[6]: Sieve Filters with MySQL

...

Hi!

Usually how the "isActive" stuff is done is bit different.

fwiw i would do the schema differently, and have two tables

table 1:

CREATE TABLE sieve_name_id ( id INT, email VARCHAR(255), name VARCHAR(255), PRIMARY KEY(email, name) )

table 2

CREATE TABLE sieve_scripts ( id INT, content TEXT )

Then you can map 'default' to the active script id, or leave it missing when there is no script enabled. This is how it's supposed to work.

It's probably not dead obvious but if you look at the dict configuration you have:

map { pattern = priv/sieve/name/$script_name table = mailboxFilter username_field = email value_field = uuid fields { name = $script_name } }

You can see that the priv/sieve/name/ is not part of $script_name that is passed to sql in fields.

btw, to debug SQL, you can try

log_debug=category=sql

next time.

Aki

...

On 24/07/2024 00:42 EEST Adam Miller via dovecot dovecot@dovecot.org wrote:

Is it possible to include extra fields? For example, the “isActive” and make sure it is true / 1?

Thank you.

------ Original Message ------ From "Aki Tuomi via dovecot" dovecot@dovecot.org To "Adam Miller" adam@ardaemail.com; "Adam Miller via dovecot" dovecot@dovecot.org Date 7/23/2024 4:44:30 AM Subject Re: Re[4]: Sieve Filters with MySQL

...

You can try

doveadm dict get -u adam proxy::sieve priv/sieve/name/default

this should return id of the default script.

doveadm dict get -u adam proxy::sieve priv/sieve/data/<id>

should return the actual script.

See https://doc.dovecot.org/configuration_manual/sieve/dict/

Aki

> On 23/07/2024 14:09 EEST Adam Miller via dovecot dovecot@dovecot.org wrote: > > > Yes, I get that. What I would like to know is what are the queries that > Dovecot / Sieve is executing so that I can better determine where the > issue exists? Is there anyway to see them? Do you see an issue anywhere > in my configuration files? > > Thank you. > > > ------ Original Message ------ > From "Aki Tuomi" aki.tuomi@open-xchange.com > To "Adam Miller" adam@ardaemail.com; "Adam Miller via dovecot" > dovecot@dovecot.org > Date 7/23/2024 4:07:22 AM > Subject Re: Re[2]: Sieve Filters with MySQL > > >Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >sieve: dict storage: script default': Script default' not found at > >path priv/sieve/name/default > > > >Aki > > > >> On 23/07/2024 13:52 EEST Adam Miller via dovecot dovecot@dovecot.org wrote: > >> > >> > >> Yes, that is where I got the error from. Here’s more from the log file: > >> > >> Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >> sieve: Pigeonhole version 0.5.16 (09c29328) initializing > >> Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >> sieve: include: sieve_global is not set; it is currently not possible to > >> include :global’ scripts. > >> Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >> sieve: Sieve imapsieve plugin for Pigeonhole version 0.5.16 (09c29328) > >> loaded > >> Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >> sieve: Sieve Extprograms plugin for Pigeonhole version 0.5.16 (09c29328) > >> loaded > >> Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >> sieve: dict storage: user=redacted, uri=proxy::sieve > >> Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >> sieve: dict storage: script default': Script default' not found at > >> path priv/sieve/name/default > >> Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >> sieve: User has no active script in storage > >> dict:proxy::sieve;user=redacted’ > >> Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >> sieve: User has no personal script > >> Jul 23 10:34:40 lmtp(redacted)<228576><CM1ZFUCHn2bgfAMAwHdlHA>: Debug: > >> sieve: No scripts to execute: reverting to default delivery. > >> > >> Is there something specific I should be looking for? > >> > >> Thank you! > >> > >> ------ Original Message ------ > >> From "Aki Tuomi via dovecot" dovecot@dovecot.org > >> To "Adam Miller" adam@ardaemail.com; "Adam Miller via dovecot" > >> dovecot@dovecot.org > >> Date 7/23/2024 3:35:17 AM > >> Subject Re: Sieve Filters with MySQL > >> > >> >Did you try with > >> > > >> >mail_debug=yes > >> > > >> >and > >> > > >> >log_debug=category=sieve > >> > > >> >Aki > >> > > >> >> On 23/07/2024 13:18 EEST Adam Miller via dovecot dovecot@dovecot.org wrote: > >> >> > >> >> > >> >> 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 = >> >> 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! > >> >> _______________________________________________ > >> >> dovecot mailing list -- dovecot@dovecot.org > >> >> To unsubscribe send an email to dovecot-leave@dovecot.org > >> >_______________________________________________ > >> >dovecot mailing list -- dovecot@dovecot.org > >> >To unsubscribe send an email to dovecot-leave@dovecot.org > >> _______________________________________________ > >> dovecot mailing list -- dovecot@dovecot.org > >> To unsubscribe send an email to dovecot-leave@dovecot.org > _______________________________________________ > dovecot mailing list -- dovecot@dovecot.org > To unsubscribe send an email to dovecot-leave@dovecot.org

---

dovecot mailing list -- dovecot@dovecot.org To unsubscribe send an email to dovecot-leave@dovecot.org

---

dovecot mailing list -- dovecot@dovecot.org To unsubscribe send an email to dovecot-leave@dovecot.org

---

dovecot mailing list -- dovecot@dovecot.org To unsubscribe send an email to dovecot-leave@dovecot.org

---

dovecot mailing list -- dovecot@dovecot.org To unsubscribe send an email to dovecot-leave@dovecot.org