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@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.

On 14.5.2014 13:29, Axel Luttgens wrote:

Le 14 mai 2014 à 11:41, Jiri Bourek a écrit :

...

[...] 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.

Hello Jiri,

Just in case, since I've never tried myself...

This file:

http://hg.rename-it.nl/dovecot-2.2-pigeonhole/file/689db87e26f2/doc/script-l...

is systematically installed here as:

share/doc/dovecot/sieve/script-location-dict.txt

May perhaps be of interest?

Axel

Guess I'm not used to looking into that "doc" directory anymore, Google usually knows everything. So thanks, but it's too late for me, already have the generator in place.

Btw. gave it a try anyway on test system and found out a bit unexpected behaviour: sieve script described with everything on one line (below) works

require ["fileinto"]; fileinto "testfolder";

However when you split it to multiple lines (so it looks nice in the DB), the interpreter does nothing (I guess it only uses first line and ignores everything else)

I.e. this:

require ["fileinto"]; fileinto "testfolder";

doesn't work

Mentioning just in case someone as confused as I was stumbles into this thread.