Feb 112016
 

i’m switching from courier-imap to dovecot recently. unfortunately on my centos 6.7, i only have dovecot 2.0.9 available. quota work as expected. the only thing that bother me, everytime someone outside world sent email to one of overquota user, dovecot create bounce mail to the sender. which is not good. expert said : “you will create backscattering mail!”.

long time ago when i was using courier-imap, i was creating simple perl daemon to check user quota and disk usage. dovecot have additional mysql table for storing user quota usage/bytes and messages.

so here how i’ve done.

perl daemon

#!/usr/bin/perl
use File::Find;
use strict;
use warnings;
use DBI;
use DBD::mysql;
use Sys::Syslog qw(:DEFAULT setlogsock);
use base qw(Net::Server::PreFork);

#
# Initalize and open syslog.
#
openlog('postfix::CHECK-SIZE::','pid','mail');

__PACKAGE__->run;
exit;

###

sub configure_hook {
        my $self = shift;

        $self->{server}->{port}     = '127.0.0.1:20028';
        $self->{server}->{user}     = 'vmail';
        $self->{server}->{group}    = 'mail';
        $self->{server}->{pid_file} = '/tmp/quota.pid';
        $self->{server}->{setsid}   = 1;
        $self->{basedir}            = "/data/vmail/example.com/";

}

### process the request
sub process_request {
        my $self = shift;
        while(my $line = <STDIN>) {
                chomp($line);
                if ($line=~/^get\s+(.+)/i) {
                        my $user = $1;
                        trim($user);
                        my $sqlsize = checksqlsize($user);
                        if (defined $sqlsize && $sqlsize == 0) {
                                print STDOUT "200 DUNNO\n";
                                #print STDOUT "sqlsize: $sqlsize\n";
                                next;
                        }
                        #print STDOUT "sqlsize: $sqlsize\n";

                        my $usrdirsize = $user;
                        $usrdirsize =~ s/\@example\.com$/\//;
                        my $dir = $self->{basedir} . $usrdirsize;
                        my $sqlusage =  checksqlusage($user);

                        if (defined $sqlusage && defined $sqlsize) {
                        syslog("info","Checking %s maildir size: define=%s, diskusage=%s", $user, $sqlsize, $sqlusage);
                                if ( $sqlusage > $sqlsize) {
                                        print STDOUT "200 REJECT $user is over quota! maildir size: define=$sqlsize, diskusage=$sqlusage\n
";
                                        next;
                                }
                        }
                }
                print STDOUT "200 DUNNO\n";
        }
}

sub trim{
        $_[0]=~s/^\s+//;
        $_[0]=~s/\s+$//;
        return;
}

sub checksqlsize {
        my $user = $_[0];
        my $sqlresult;
        trim($user);
        my $dbh = DBI->connect('DBI:mysql:postfix:localhost', 'postfix', 'yourpassword', { RaiseError => 1 });
        my $sth = $dbh->prepare(qq{SELECT quota FROM mailbox WHERE username='$user'});
        $sth->execute();
        while (my @row = $sth->fetchrow_array) {
                $sqlresult = $row[0];
        }
        $sth->finish();
        $dbh->disconnect;
        if ($sqlresult >= 0 ) {
                return $sqlresult;
        } else {
                return undef;
        }
}

sub checksqlusage {
        my $user = $_[0];
        my $sqlresult;
        trim($user);
        my $dbh = DBI->connect('DBI:mysql:postfix:localhost', 'postfix', 'yourpassword', { RaiseError => 1 });
        my $sth = $dbh->prepare(qq{SELECT bytes FROM quota2 WHERE username='$user'});
        $sth->execute();
        while (my @row = $sth->fetchrow_array) {
                $sqlresult = $row[0];
        }
        $sth->finish();
        $dbh->disconnect;
        if ($sqlresult >= 0 ) {
                return $sqlresult;
        } else {
                return undef;
        }
}

1;

postfix section

smtpd_recipient_restrictions =
        permit_sasl_authenticated,
        permit_mynetworks,
        reject_unauth_destination,
        reject_non_fqdn_sender,
        reject_non_fqdn_recipient,
        reject_unknown_recipient_domain,
        check_recipient_access proxy:tcp:[127.0.0.1]:20028,
        reject_rbl_client zen.spamhaus.org,
        reject_rbl_client bl.spamcop.net,
        reject_rbl_client dnsbl.sorbs.net,

test

# telnet localhost 20028
Trying ::1...
telnet: connect to address ::1: Connection refused
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
get foo@example.com
200 REJECT foo@example.com is over quota! maildir size: define=102400000, diskusage=147383243

you’re good to go 🙂

Jul 142011
 

I recently experimented with a simple bash script, inotifywait and  smtpd_recipient_restrictions (check_recipient_access) to map email users who have exceeded the quota.

Well, during testing, i’ve noticed when using hash/texthash lookup tables, it needed to be reloaded in order smtpd detect changes in table.so i’ve made quick test on mysql_tables it seem updating record on tables will immediately able to be queried

Mapping can be done as follows:
main.cf:

smtpd_recipient_restrictions =
    check_recipient_access mysql:/etc/postfix/mysql_quota_access.cf,
	...
	...

mysql_quota_access.cf

user = user
password = password
hosts = localhost
dbname = postfixdb
query = SELECT qaction FROM quota WHERE username='%s'

create mysql table called quota:

 CREATE TABLE quota (
 id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 username VARCHAR(100),
 qaction VARCHAR(100)
 ) TYPE=innodb;

Here’s the idea, inotifywait will continuously monitor the maildir directory recursively, and updates “qaction” field on “quota” mysql table whenever new mail arrived or whenever there is email deleted from the maildir.

initial map, can be produced by retrieving user information from database.for example, username information in the database “postfixdb” with the table name “mailbox” and field “username”.

# for i in `mysql -u user -ppassword -D postfixdb -e 'SELECT  username FROM mailbox' | grep -v username`;do mysql -u user -ppassword -D postfixdb -e "INSERT INTO quota (username, qaction) VALUES ('$i', 'DUNNO')";

With this script,value of qaction field on mysql quota table will change continuously as the user’s maildir contents that keeps changing.
Continue reading »