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.
