Re: [Exim] SQL POP before SMTP method?

--
On (2002/10/24 12:03), Eric Renfro wrote:

> Here's an added feature I would like to add to my exim setup.
>
> I would like to make an SQL-based POP before SMTP method, allowing users who
> can't SMTP AUTH by other normal means, to be able to login through POP before
> sending email out to anywhere.
>
> I haven't a clue how to start this idea, but I'm guessing it's somehow
> ACL-based in exim4?

Here's what I do:

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:

# In the main section:
#
PGSQL_POPB4 = select ip from popb4smtp where ip='${sender_host_address}'
hide pgsql_servers = localhost/popb4smtp/exim/XX_POPB4SMTP_RO_PASSWD_XX

hostlist relay_hosts = 127.0.0.1/32 : net-pgsql;PGSQL_POPB4

acl_smtp_rcpt = check_recipient

# In the acl section:

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

I do a lot of other funky stuff with the PGSQL_POPB4 lookup, e.g.

tls_advertise_hosts = net-pgsql;PGSQL_POPB4
host_lookup = !127.0.0.1/32 : !net-pgsql;PGSQL_POPB4
rfc1413_hosts = !127.0.0.1/32 : !net-pgsql;PGSQL_POPB4

But you get the idea. :-)

HTH.

Ciao,
Sheldon.
--
#!/usr/bin/perl -w

use strict;
use Date::Parse;
use DBI;
use File::Tail;
use POSIX qw(strftime);

my $DBDSN = "DBI:Pg:dbname=popb4smtp;host=localhost";
my $DBUSER = "popb4smtp";
my $DBPASS = "XX_POPB4SMTP_RW_PASSWD_XX";
my $POPLOG = '/var/log/popd.log';
my $PROGNAME = 'popb4smtp-watch';
my $PROGLOG = '/var/log/popb4smtp.log';
my $PIDFILE = '/var/run/popb4smtp-watch.pid';

my ($dbh, $rv);
my $line;
my $tail;

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

use strict;
use DBI;

my $DBDSN = "DBI:Pg:dbname=popb4smtp;host=localhost";
my $DBUSER = "popb4smtp";
my $DBPASS = "XX_POPB4SMTP_RW_PASSWD_XX";
my $PROGNAME = 'popb4smtp-clean';
my $PIDFILE = '/var/run/popb4smtp-clean.pid';
my $KEEPSECS = 60 * 60 * 2;
my $FLUSHFREQ = 60 * 5;

my ($dbh, $rv);

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")
);

REVOKE ALL on "popb4smtp" from PUBLIC;
GRANT ALL on "popb4smtp" to "root";
GRANT ALL on "popb4smtp" to "popb4smtp";
GRANT SELECT on "popb4smtp" to "exim";

--