Postfix, Dynamic OverQuota User Map Script Using Bash And Inotifywait

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.

quota.sh:

#!/bin/sh
# user domain example.com
# user maildir /var/postfix/user (just user, without domain)
# user quota limit 102400 (102.4M)
inotifywait -mr --format '%w %f %e' -e create,delete /var/postfix | while read event
do
        user=`echo $event | awk '{print $1}' | cut -f 4 -d"/"`
        udir=`echo $event | awk '{print $1}'`
        sql="mysql -u user -ppassword -D postfixdb -e"
        quota="102400"
        if [[ $event =~ "CREATE$" ]]; then
                if [[ -n "$user" ]]; then
                        uqusage=`du -sb $udir | awk '{print $1}'`
                        if [[ "$uqusage" -lt "$quota" ]]; then
                                $sql "UPDATE quota SET qaction = 'DUNNO' WHERE username='${user}@example.com'"
                        else
                                log="${user}@example.com 452 4.2.2 Mailbox full"
                                $sql "UPDATE quota SET qaction = '452 4.2.2 Mailbox full' WHERE username='${user}@example.com'"
                                echo $log | logger -p mail.info -t postfix/QUOTA[$$]
                        fi
                fi
        elif [[ $event =~ "DELETE$" ]]; then
                if [[ -n "$user" ]]; then
                        uqusage=`du -sb $udir | awk '{print $1}'`
                        if [[ "$uqusage" -lt "$quota" ]]; then
                                $sql "UPDATE quota SET qaction = 'DUNNO' WHERE username='${user}@example.com'"
                        else
                                log="${user}@example.com 452 4.2.2 Mailbox full"
                                $sql "UPDATE quota SET qaction = '452 4.2.2 Mailbox full' WHERE username='${user}@example.com'"
                                echo $log | logger -p mail.info -t postfix/QUOTA[$$]
                        fi
                fi
        fi
done

Make it executable and run the script in background

# chmod 755 quota.sh
# nohup ./quota.sh &