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 <a...@ardaemail.com> wrote:
> >
> >
> > ------ Original Message ------
> > From "Aki Tuomi via dovecot" <dovecot@dovecot.org>
> > To "Adam Miller" <a...@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" <a...@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.tu...@open-xchange.com>
> > >> >> To "Adam Miller" <a...@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" <a...@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=usern...@domain.com"
> > >> >> >> >>
> > >> >> >> >>
> > >> ------------------------------------------------------------------------------------------------------------------
> > >> >> >> >>
> > >> >> >> >> The “usern...@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:supp...@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 = postmas...@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!
> > >> >> >> >> _______________________________________________
> > >> >> >> >> dovecot mailing list -- dovecot@dovecot.org
> > >> >> >> >> To unsubscribe send an email to dovecot-le...@dovecot.org
> > >> >> >> >_______________________________________________
> > >> >> >> >dovecot mailing list -- dovecot@dovecot.org
> > >> >> >> >To unsubscribe send an email to dovecot-le...@dovecot.org
> > >> >> >> _______________________________________________
> > >> >> >> dovecot mailing list -- dovecot@dovecot.org
> > >> >> >> To unsubscribe send an email to dovecot-le...@dovecot.org
> > >> >> _______________________________________________
> > >> >> dovecot mailing list -- dovecot@dovecot.org
> > >> >> To unsubscribe send an email to dovecot-le...@dovecot.org
> > >> >_______________________________________________
> > >> >dovecot mailing list -- dovecot@dovecot.org
> > >> >To unsubscribe send an email to dovecot-le...@dovecot.org
> > >> _______________________________________________
> > >> dovecot mailing list -- dovecot@dovecot.org
> > >> To unsubscribe send an email to dovecot-le...@dovecot.org
> > >_______________________________________________
> > >dovecot mailing list -- dovecot@dovecot.org
> > >To unsubscribe send an email to dovecot-le...@dovecot.org
> _______________________________________________
> dovecot mailing list -- dovecot@dovecot.org
> To unsubscribe send an email to dovecot-le...@dovecot.org
_______________________________________________
dovecot mailing list -- dovecot@dovecot.org
To unsubscribe send an email to dovecot-le...@dovecot.org
