Re: [exim] Sourcing Exim Filter from MySQL/MariaDB table?

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