RE: [Exim] SQL POP before SMTP method?

> 1) First you create your popb4smtp database.  I use
> PostgreSQL, which is
>    overkill for something like this; MySQL would suffice, but I had
>    trouble with MySQL on SMP FreeBSD boxes about a year ago.
> The schema
>    I use is attached (schema.sql).

> 2) I watchdog the POP3 logs for authentication information and slap it
>    into the popb4smtp database.  My script popb4smtp-watch is
> attached.

>    Beware; I suspect the File::Tail module has trouble with log files
>    larger than about 40MB.  Make sure you rotate your logs regularly.

> 3) Just so the database doesn't grow indefinitely, I clean out values
>    from time to time with my script popb4smtp-clean
> (attached).  You can
>    decide for yourself how frequently you want to run it.

> 4) You can start these two scripts with something like my popb4smtp.sh
>    script, attached.

> 5) Now you just need to configure Exim to allow relaying from recently
>    authenticated POP3 client hosts.  Sending you my entire configure
>    file would be noisy, so the bits you're interested in are:

> check_recipient:
>   [...]
>   accept    hosts    = +relay_hosts
>   deny        message    = relay not permitted

> open(PID, "> $PIDFILE") or
>     die "$PROGNAME: $PIDFILE: $!\n";
> print PID "$$\n";
> close(PID);

> $dbh = DBI->connect($DBDSN, $DBUSER, $DBPASS, {
>     RaiseError => 1,
>     AutoCommit => 1
>     });
> $tail = File::Tail->new(
>     name => $POPLOG,
>     maxinterval => 1,
>     interval => 1,
>     ignore_nonexistant => 1
>     );

> while ($line = $tail->read()) {
>     my ($ip, $timestr, $unixtime, $user);

>     if ($line =~ /^(\w+\s+\d+\s+\d+:\d+:\d+) .*
> popd\[\d+\]: Login user=(\w+) host=\[([^]]+)\]/) {
>         ($timestr, $user, $ip) = ($1, $2, $3);
>         $unixtime = str2time($timestr);
>         db_store_popauth($unixtime, $user, $ip);
>     }
> }

> sub db_store_popauth($$$)
> {
>     my ($unixtime, $user, $ip) = @_;
>     my $rv;

>     eval {
>         $rv = $dbh->do(qq{
>             UPDATE popb4smtp
>                SET timestamp = TIMESTAMP($unixtime),
>                    username = '$user'
>              WHERE ip = '$ip'
>             });
>         if ($rv != 1) {
>             die "no_such_row\n";
>         }
>     };
>     if ($@ eq "no_such_row\n") {
>         eval {
>             $dbh->do(qq{
>                 INSERT INTO popb4smtp
>                        (ip, username, timestamp)
>                 VALUES ('$ip', '$user',
> TIMESTAMP($unixtime))
>                 });
>             };
>         if ($@) {
>             logwarn("warning: INSERT failed: $@");
>         } else {
>             logwarn("debug: insert $ip for $user");
>         }
>     } elsif ($@) {
>         logwarn("warning: UPDATE failed: $@");
>     } else {
>         logwarn("debug: update $ip for $user");
>     }
> }

> sub logwarn(@)
> {
>     my (@msg) = @_;

>     unshift(@msg, strftime("%F %T", localtime()));
>     open(LOGFILE, ">> $PROGLOG") or
>         die "$PROGNAME: $PROGLOG: $!\n";
>     print LOGFILE join(' ', @msg), "\n";
>     close(LOGFILE);
> }
> --
> #!/usr/bin/perl -w

> open(PID, "> $PIDFILE") or
>     die "$PROGNAME: $PIDFILE: $!\n";
> print PID "$$\n";
> close(PID);

> $dbh = DBI->connect($DBDSN, $DBUSER, $DBPASS, {
>     RaiseError => 1,
>     AutoCommit => 1
>     });

> while (1) {
>     eval {
>         $rv = $dbh->do(qq{
>             DELETE FROM popb4smtp
>              WHERE age(now(), timestamp) >
>                 interval('$KEEPSECS seconds')
>             });
>     };
>     if ($@) {
>         warn "$PROGNAME: unexpected error while flushing: $@\n";
>     }
>     sleep($FLUSHFREQ);
> }
> --
> [ Content of type application/x-sh deleted ]
> --
> -- POP-before-SMTP database schema.
> --
> -- $Id: schema.sql,v 1.1 2002/08/21 12:58:47 sheldonh Exp $
> --
> -- Everything after this comment block is created with:
> --
> --      pg_dump -R -s massmail |grep -v -- -- > schema.sql
> --

> CREATE TABLE "popb4smtp" (
>     "ip" character varying(15) NOT NULL,
>     "hostname" character varying(80),
>     "username" character varying(32) NOT NULL,
>     "timestamp" timestamp with time zone NOT NULL,
>     Constraint "popb4smtp_pkey" Primary Key ("ip")
> );