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
