Okay, I enabled RDS query logging and was able to find the queries and
get the issue fixed. The query being executed for the first map is:
SELECT <field> FROM <table> WHERE name = 'default' AND email = ‘redacted'
I was expecting the “name” value to be “priv/sieve/name/default” not
just “default”. It would be great if the documentation explained this.
Thank you for your help, I appreciate it.
------ Original Message ------
From "Adam Miller via dovecot" <dovecot@dovecot.org>
To "Aki Tuomi" <aki.tu...@open-xchange.com>; "Adam Miller via dovecot"
<dovecot@dovecot.org>
Date 7/23/2024 1:52:56 PM
Subject Re[6]: Sieve Filters with MySQL
I have read all of this documentation already and others. I have also used
Google, StackOverflow and ChatGPT to get assistance but nothing has helped. I
provided my configuration here as well but unfortunately, I still have the same
problem.
# doveadm dict get -u redacted proxy::sieve priv/sieve/name/default
Error: priv/sieve/name/default doesn't exist
Because I am using AWS RDS it is not easy to view the queries, if they are even
logged with logging enabled. Is there some other possible way to view the
queries being executed?
I love Dovecot, don’t get me wrong here but the documentation is lacking and so
is the logging. A lot of my issues I have had could have been resolved with
better documentation and better logging. I do not mind contributing, if that is
allowed. I have found some pretty good tutorials scattered across the Internet
however some of them are pretty old and do not use pieces that I have put
together. I am seriously thinking about writing my own but need to get some
other items in place first.
Any additional help is appreciated.
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
