[Dovecot] Performing an action on mail receipt

[Jiri Bourek]

On 14.5.2014 02:15, Andrew Beverley wrote:
> On Tue, 2014-05-13 at 16:21 +0200, Stephan Bosch wrote:
>> This should provide what you need:
>>
>> http://sieve.info/
>> http://tools.ietf.org/html/rfc5435
>> http://tools.ietf.org/html/rfc5436
>>
>> http://wiki2.dovecot.org/Pigeonhole
>
> Fantastic, thanks, exactly what I need.
>
> I've got the basics working, with a static file Sieve script. However,
> I'm trying to keep as much of my config in a MySQL database, and this is
> where I'm starting to struggle.

Welcome to the club ;-)

>
> By user, I'd like to retrieve from a SQL database firstly which Sieve
> script to run (if applicable) and secondly the parameters for that
> particular user, in this case the external email address to notify.

AFAIK PigeonHole can read scripts only from file. Being able to use SQL 
database as data source would sure be nice and I recall there was some 
short discussion about it, but - again, AFAIK - it was never added as a 
feature.

>
> As I understand it, I'll need to use the Extdata plugin to retrieve data
> values per-user. I've successfully compiled and installed this plugin,
> but I'm not sure how to connect it to a dict.

Tried that too for optional spamassassin test and failed, see 
https://www.mail-archive.com/dovecot@dovecot.org/msg57539.html . IMO 
documentation for this map and dict thing is a nightmare to say the 
least (or if there is something, Google doesn't know about it.)

Anyway, this is what I came up with:

91-sieve-extdata.conf:

plugin {
   sieve_extensions = +vnd.dovecot.extdata
   sieve_extdata_dict_uri = proxy::testing
}

dict {
   testing = pgsql:/etc/dovecot/pigeonhole-extdata.dict
}

/etc/dovecot/pigeonhole-extdata.dict:

connect = host=127.0.0.1 dbname=maildatabase user=mailuser password=secret

map {
   pattern = priv/antispam_setting
   table = mailschema.antispam_view
   value_field = vtransport
   username_field = full_username
}

I use PostgreSQL but I guess there won't be any major differences. In 
sieve script it was used like this:

if extdata :is "antispam_setting" "enabled"

I think this worked in the end. Well, sort of worked, I needed to create 
view with user names in user at domain format (I use separate tables for 
domains and users), and the view looked like this:

create view antispam_view as \
   select username || '@' || domain as full_username, vtransport \
     from mailboxes \
     left join domains on mailboxes.domains_id = domains.id ;

The || means string concatenation in PostgreSQL.

This is of course stupid and ugly, because lookup by username forces 
sequential scan of the users table. Moreover I have separate UID for 
every user, which in turn forced 0777 permissions on dict socket (sieve 
interpreter runs under mailbox owner's uid), which doesn't seem very 
secure. At this point I abandoned the idea.

Stephan Bosch suggested using the extprograms plugin - execute some 
program non-conditionally, let it do all SQL lookups and take neccessary 
actions. I didn't use that approach because it'd require me to create 
some client-server application (to keep database credentials hidden from 
users and to maintain database connection pool instead of doing connect 
- lookup - close cycle for every request.) That looked a bit too 
complicated compared with generating the sieve script (with or without 
the test) according to database data. (I had the code to do that.)

But maybe some solution with extprograms will be more suitable for you.

>
> I've created a dict configuration file, with the following contents:
>
>    map {
>      pattern = priv/extdata/notify_email
>      table = virtual_users
>      username_field = email
>      value_field = notify_email
>    }
>
> And added it to dovecot.conf:
>
>    dict {
>      notify = mysql:/etc/dovecot/pigeonhole-sieve.dict
>    }
>
> I've added the following to my Sieve configuration:
>
>    sieve_plugins = sieve_extdata
>    sieve_extdata_dict_uri = extdata::notify_email
>
> However, I'm not sure of the naming syntax of the above, and I'm just
> getting "sieve extdata: failed to initialize dict extdata::notify_email"
>
> I guess I've got the naming wrong, but I couldn't find documentation to
> specify how it should be. What am I doing wrong?

IIRC you're third person in past 4 weeks saying finding stuff in Dovecot 
documention is hard, so don't feel bad ;-)

J.