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
