Re: ISP Level Postgrey

[Craig Sanders <cas@taz.net.au>]

On Wed, Jul 12, 2006 at 11:36:36PM -0600, Michael Loftis wrote:
> --On July 13, 2006 8:39:56 AM +1000 Craig Sanders <cas@taz.net.au> wrote:
> 
> >there is, or was, a postgres version of this around too. i was trialling
> >it a year or two ago but decided i didn't want my mail system dependant
> >on an SQL database.
> 
> A lot of us already have to eat that dog food though in order to provide 
> virtual mail rules and such across a decent number of servers. :/

that's ONE way of having a load-balanced mail server array, but not the
only way.

another is to use make & scp or rsync & other tools to distribute files
(e.g. virtual user tables, relay recipient tables, local user tables,
blacklists, etc) to where they are needed.

e.g. make rules that detect whether a map file has changed. if
so, rsync it to the remote system.

you can even use postgres or mysql or whatever as the initial data
source, without having postfix dependant on postgres by dumping
the postgres table(s) into text files suitable for processing with
postmap. and you can have make detect whether the pgsql db has changed
(and thus avoid unneccessary copying & processing etc if nothing has
changed) if you set up a trigger to update a timestamp field on any
insert/update/deletion, and then check that...and set up a cron job
to run "make" every few minutes.

all the benefits of SQL management of your data, without the risk of
having your mail system go down if your SQL server does....it just keeps
on running, but with no updates to the shared map files until the db is
back up and running.  IMO, since mail is extremely user-visible (i.e.
you get complaints instantly when it stops working), that's worth the
fairly small effort to implement.

oh, and a query of a local hashed postfix map file is a lot faster than
doing an sql query, so there are performance benefits to doing it this
way, too.


here's a very rudimentary example of what i mean:

#! /usr/bin/make -f

.stamp-copy: virtual access relay-recip local-recip 
	for HOST in host1 host2 host3 ... hostN; do rsync virtual access relay-recip local-recip $HOST:/etc/postfix/ ; ssh $HOST "cd /etc/postfix ; make" ; done 
	touch .stamp-copy



you can make it as fancy as you want. in particular, for security
reasons you would probably want to set up an ssh key which logs in
without a password as a non-priviledged user, and use sudo to allow
that user to run "make" with no args as root only in the /etc/postfix
directory.

to use a timestamped postgres db with make, you need to set up a trigger
that automatically updates the timestamp whenever anything changes. then
you need to have a trivial script (perl DBI is easy) which queries that
field. if it is newer than the postfix map files that depend on the db,
then dump them to plain text and run postmap on them.


craig

-- 
craig sanders <cas@taz.net.au>           (part time cyborg)