Mailing List Archive

Sourcing Exim Filter from MySQL/MariaDB table?
I'm building an Exim/Dovecot/Nginx/Roundcube system to replace our
ancient public mailserver (Redhat 9, Exim 4.14, Courier-IMAP).

The new system OS is Devuan 3.0 "Beowulf" with MariaDB 10.3 (no systemd
entanglement ;-) and I've built a database to host users, passwords,
domains, aliases, vacation messages etc. based on Alex's recopies over here:

http://alex.mamchenkov.net/2010/06/24/exim-dovecot-and-mysql/

and have Exim 4.92 compiled from source, clients connecting and
authenticating, sending and receiving email and Dovecot serving it, etc.
but have discovered that some of my users have custom Exim filters set
up in their .forward files, for example:

# Exim filter  <= do not remove this line!!!

if $header_to contains "asterisk-users" or
   $header_cc contains "asterisk-users" or
   $header_to contains "asterisk-dev" or
   $header_cc contains "asterisk-dev" or
then
   save "$home/Maildir/.Asterisk/"
   finish
endif

On the old server everyone with a mailbox has a unix account and hence
had access to their home directory where they could place a ".forward"
file with the magic "# Exim filter" first line.

With the new system there are no user logins and all users have virtual
mailboxes stored in:

/var/spool/mail/<domain>/<local_part>/{cur|new|tmp}

is there a way to implement per-user filtering by having Exim read it
from a MySQL/MariaDB table at delivery/processing time?


Mike



--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/
Re: Sourcing Exim Filter from MySQL/MariaDB table? [ In reply to ]
On 07/05/2019 22:52, Mike Tubby via Exim-users wrote:
> is there a way to implement per-user filtering by having Exim read it
> from a MySQL/MariaDB table at delivery/processing time?

Reading the doc chapter on the redirect router, it'd be quite
happy for you to populate the "data" option using a database-read.

I've never tried anything so whacky, and you're opening yourself
up to a rather large memory usage... It might "just work".

--
Cheers,
Jeremy

--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/
Re: Sourcing Exim Filter from MySQL/MariaDB table? [ In reply to ]
On 07/05/2019 23:09, Jeremy Harris via Exim-users wrote:
> On 07/05/2019 22:52, Mike Tubby via Exim-users wrote:
>> is there a way to implement per-user filtering by having Exim read it
>> from a MySQL/MariaDB table at delivery/processing time?
> Reading the doc chapter on the redirect router, it'd be quite
> happy for you to populate the "data" option using a database-read.
>
> I've never tried anything so whacky, and you're opening yourself
> up to a rather large memory usage... It might "just work".

I sort-of-wondered if this might work but was unsure ...

Most users have 2-3 rules, some 5-6, one has 12.  Each rule is circa
200-500 bytes.

So, I've added a 'filters' table to the recipe, thus:

MariaDB [mail]> describe filters;
+---------+---------------------+------+-----+---------+----------------+
| Field   | Type                | Null | Key | Default | Extra          |
+---------+---------------------+------+-----+---------+----------------+
| id      | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| user_id | int(10) unsigned    | NO   |     | 0 |                |
| active  | tinyint(1) unsigned | NO   |     | 0 |                |
| rule    | varchar(2000)       | YES  |     | NULL |                |
+---------+---------------------+------+-----+---------+----------------+
4 rows in set (0.001 sec)

With the idea that you put one rule in one row - you can have multiple
rows and turn them on and off with the active flag.

I've loaded four rules in the database, one of which is disable (active=0).

A bit of manually entered SQL:

MariaDB [mail]> SELECT rule FROM users LEFT JOIN domains ON
domains.id=users.domain_id LEFT JOIN filters ON users.id=filters.user_id
WHERE domains.domain='test.thorcom.com' AND users.username='fred' AND
domains.active=1 AND users.active=1 AND filters.active=1 LIMIT 10\G

yields something sane:

*************************** 1. row ***************************
rule: if $header_to contains "vhf" or
   $header_cc contains "vhf"
then
   save "$home/Maildir/.Ham Radio.VHF Contests/"
   finish
endif

*************************** 2. row ***************************
rule: if $header_to contains "vhf-contests" or
   $header_cc contains "vhf-contests"
then
   save "$home/Maildir/.Ham Radio.VHF Contests/"
   finish
endif

*************************** 3. row ***************************
rule: if $header_to contains "exim-users" or
   $header_cc contains "exim-users"
then
   save "$home/Maildir/.Computing.Exim/"
   finish
endif

3 rows in set (0.000 sec)

Which suggests I need something like:

user_filter:
    driver = forwardfile
    data = ${lookup mysql{SELECT rule FROM users LEFT JOIN domains \
        ON domains.id=users.domain_id LEFT JOIN filters \
        ON users.id=filters.user_id WHERE
domains.domain='${quote_mysql:$domain}' \
        AND users.username='${quote_mysql:$local_part}' \
        AND domains.active=1 \
        AND users.active=1 \
        AND filters.active=1}}
    no_verify
    no_expn
    check_ancestor

Am I going to need to arrange for the database result set to start with
'# Exim Filter' each time or is their a boolean to force this?

If there isn't a boolean to force it, then it would be good to have a
'is_filter' boolean ;-)

As far as memory utilisation goes, won't Exim discard any memory it
allocated for the result set at the end of the delivery?


Mike




--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/
Re: Sourcing Exim Filter from MySQL/MariaDB table? [ In reply to ]
On 08/05/2019 00:39, Mike Tubby via Exim-users wrote:

> user_filter:
>     driver = forwardfile
>     data = ${lookup mysql{SELECT rule FROM users LEFT JOIN domains \
>         ON domains.id=users.domain_id LEFT JOIN filters \
>         ON users.id=filters.user_id WHERE
> domains.domain='${quote_mysql:$domain}' \
>         AND users.username='${quote_mysql:$local_part}' \
>         AND domains.active=1 \
>         AND users.active=1 \
>         AND filters.active=1}}
>     no_verify
>     no_expn
>     check_ancestor

Where's your rule ordering? And what on earth is "forwardfile" ?

> Am I going to need to arrange for the database result set to start with
> '# Exim Filter' each time or is their a boolean to force this?
>
> If there isn't a boolean to force it, then it would be good to have a
> 'is_filter' boolean ;-)

No, there's not enough call for that; it's your problem :)
It's only a string; just glom it on before the result
of the DB lookup expansion.

> As far as memory utilisation goes, won't Exim discard any memory it
> allocated for the result set at the end of the delivery?

Yes. I wasn't meaning permanent.
--
Cheers,
Jeremy

--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/
Re: Sourcing Exim Filter from MySQL/MariaDB table? [ In reply to ]
On 2019-05-08 at 00:39 +0100, Mike Tubby via Exim-users wrote:
> Which suggests I need something like:
>
> user_filter:
>     driver = forwardfile
>     data = ${lookup mysql{SELECT rule FROM users LEFT JOIN domains \
>         ON domains.id=users.domain_id LEFT JOIN filters \
>         ON users.id=filters.user_id WHERE
> domains.domain='${quote_mysql:$domain}' \
>         AND users.username='${quote_mysql:$local_part}' \
>         AND domains.active=1 \
>         AND users.active=1 \
>         AND filters.active=1}}
>     no_verify
>     no_expn
>     check_ancestor

This is a central mailhub where users no longer have accounts. Please
read the Exim Spec security section on "Running Local Commands":

https://www.exim.org/exim-html-current/doc/html/spec_html/ch-security_considerations.html#SECTsecconslocalcmds

You're going to need to add a bunch of forbid_* options to that Router,
and keep an eye on release notes when upgrading, to watch for shiny new
forbid_* options.

See:

https://www.exim.org/exim-html-current/doc/html/spec_html/ch-the_redirect_router.html#SECID130

for the list of options, search for "forbid_" (and "allow_").

> Am I going to need to arrange for the database result set to start with '#
> Exim Filter' each time or is their a boolean to force this?

If it worked before, it'll work now; if I were choosing, I would leave
it to the users to choose an Exim filter, a Sieve filter, or just a
simple forward rule.

-Phil

--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/
Re: Sourcing Exim Filter from MySQL/MariaDB table? [ In reply to ]
On 08/05/2019 00:57, Jeremy Harris via Exim-users wrote:
> On 08/05/2019 00:39, Mike Tubby via Exim-users wrote:
>
>> user_filter:
>>     driver = forwardfile
>>     data = ${lookup mysql{SELECT rule FROM users LEFT JOIN domains \
>>         ON domains.id=users.domain_id LEFT JOIN filters \
>>         ON users.id=filters.user_id WHERE
>> domains.domain='${quote_mysql:$domain}' \
>>         AND users.username='${quote_mysql:$local_part}' \
>>         AND domains.active=1 \
>>         AND users.active=1 \
>>         AND filters.active=1}}
>>     no_verify
>>     no_expn
>>     check_ancestor
> Where's your rule ordering? And what on earth is "forwardfile" ?

erm, that's something left over from an Exim 3.x config file which I
used to create the above example - my bad ;-(


>> Am I going to need to arrange for the database result set to start with
>> '# Exim Filter' each time or is their a boolean to force this?
>>
>> If there isn't a boolean to force it, then it would be good to have a
>> 'is_filter' boolean ;-)
> No, there's not enough call for that; it's your problem :)
> It's only a string; just glom it on before the result
> of the DB lookup expansion.

I can do but I cannot obviously see how to do string concatenation in to
'data ='  (have you tried lloking up how to do this string concat in the
Exim documentation?)

    data = "# Exim Filter \n\n" ${lookup mysql{SELECT rule FROM users LEFT JOIN domains \
        ON domains.id=users.domain_id LEFT JOIN filters \
        ON users.id=filters.user_id WHERE
domains.domain='${quote_mysql:$domain}' \
        AND users.username='${quote_mysql:$local_part}' \
        AND domains.active=1 \
        AND users.active=1 \
        AND filters.active=1 ORDER BY filters.id ASC LIMIT 100}}


Fixing the order of the filters and a sensible maximum is trivial with a
bit of SQL.

>> As far as memory utilisation goes, won't Exim discard any memory it
>> allocated for the result set at the end of the delivery?
> Yes. I wasn't meaning permanent.

Good ;-)




--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/
Re: Sourcing Exim Filter from MySQL/MariaDB table? [ In reply to ]
On 09/05/2019 10:58, Mike Tubby via Exim-users wrote:
> I can do but I cannot obviously see how to do string concatenation in to
> 'data ='  (have you tried lloking up how to do this string concat in the
> Exim documentation?)

Right near the top of the "string expansions" chapter.

"When a string is being expanded it is copied verbatim from left to
right except when a dollar or backslash character is encountered".

So all you need to do is embed your ${lookup } in a literal string.

--
Cheers,
Jeremy

--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/
Re: Sourcing Exim Filter from MySQL/MariaDB table? [ In reply to ]
On Wed, 8 May 2019 at 01:01, Mike Tubby via Exim-users <exim-users@exim.org>
wrote:

> I'm building an Exim/Dovecot/Nginx/Roundcube system to replace our
> ancient public mailserver (Redhat 9, Exim 4.14, Courier-IMAP).
>
> The new system OS is Devuan 3.0 "Beowulf" with MariaDB 10.3 (no systemd
> entanglement ;-) and I've built a database to host users, passwords,
> domains, aliases, vacation messages etc. based on Alex's recopies over
> here:
>
> http://alex.mamchenkov.net/2010/06/24/exim-dovecot-and-mysql/
>
> and have Exim 4.92 compiled from source, clients connecting and
> authenticating, sending and receiving email and Dovecot serving it, etc.
> but have discovered that some of my users have custom Exim filters set
> up in their .forward files, for example:
>
> # Exim filter <= do not remove this line!!!
>
> if $header_to contains "asterisk-users" or
> $header_cc contains "asterisk-users" or
> $header_to contains "asterisk-dev" or
> $header_cc contains "asterisk-dev" or
> then
> save "$home/Maildir/.Asterisk/"
> finish
> endif
>
> On the old server everyone with a mailbox has a unix account and hence
> had access to their home directory where they could place a ".forward"
> file with the magic "# Exim filter" first line.
>
> With the new system there are no user logins and all users have virtual
> mailboxes stored in:
>
> /var/spool/mail/<domain>/<local_part>/{cur|new|tmp}
>
> is there a way to implement per-user filtering by having Exim read it
> from a MySQL/MariaDB table at delivery/processing time?
>
>
You can use sieve by adding dovecot-pigeonhole to your setup.
If you give people access to webmail using Roundcube (or SOGo), they can
manage their sieve rules
from the webmail interface. You can start from
https://forum.directadmin.com/showthread.php?t=43332
or just search for "Exim Dovecot sieve" and choose your HOWTO.


--
Best regards,
Odhiambo WASHINGTON,
Nairobi,KE
+254 7 3200 0004/+254 7 2274 3223
"Oh, the cruft.", grep ^[^#] :-)
--
## List details at https://lists.exim.org/mailman/listinfo/exim-users
## Exim details at http://www.exim.org/
## Please use the Wiki with this list - http://wiki.exim.org/