OVERVIEW

Simple utilities to aid in the operation of a virtual mail environment based on
maildir and PostgreSQL.

move_old_boxes.rb  Scans for mail folders that are no longer accounts marked
                   to accept e-mail. The transport table is checked to see what
                   folders under /var/mail to scan. Nothing is deleted, instead
                   it is moved to /archive for removal later.

move_old_sqmail.rb Moves old WebMail preference files to an archive folder.

scanmailboxes.py   Reads new mail from $MAILDIR/new and moves it to
                   $MAILDIR/cur or to a junk folder based on the rating of an
                   external mail scanner and a per-user limit.

scanmail.sh        Provide NFS-readable lock to ensure one running copy of
                   scanmailboxes.py

parselog.rb        Reads PostgreSQL log messages and inserts SQL statements
                   into a file for each database that requires synchronization.

sync-db.sh         Reads the SQL sequence generated by parselog.rb

TUTORIALS

http://eradman.com/article/async-scanner
http://eradman.com/article/exim-vmail
http://eradman.com/article/pgsql-sync

DATABASE STRUCTURE

CREATE TABLE transport (
    "domain" character varying(64) NOT NULL,
    "local" boolean DEFAULT false NOT NULL
);

CREATE TABLE users (
    id integer DEFAULT 0 NOT NULL,
    customer_id integer DEFAULT 0 NOT NULL,
    email character varying(64),
    username character varying(64) NOT NULL,
    "password" character varying(32),
    maildir character varying(127),
    mail boolean DEFAULT false,
    active boolean DEFAULT true,
    spamlimit double precision DEFAULT 5.0
);

POSTGRESQL LOG FORMAT

redirect_stderr = on
log_directory = 'pg_log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log
log_rotation_age = 15
log_line_prefix = ':%u:%d:'
log_statement = 'mod'

CRON TASKS (vmail)

# Run asynchronous mail scan every 3 minutes
*/3  * * * * /usr/local/bin/scanmail.sh > /dev/null 2>&1

# Archive nightly
0  4  * * *  /usr/local/bin/move_old_boxes.rb

# Get rid of Junk folder mail after 7 days, and deleted items after 14
0  3  * * *  /var/mail/ -path '*Junk/cur*' -type f -mtime +7 -exec rm {} \;
0  3  * * *  /var/mail/ -path '*Junk/new*' -type f -mtime +7 -exec rm {} \;
0  3  * * *  /var/mail/ -path '*Trash/cur*' -type f -mtime +10 -exec rm {} \;
0  3  * * *  /var/mail/ -path '*Trash/new*' -type f -mtime +10 -exec rm {} \;

CRON TASKS (pgsql)

# Scan for database updates
*/6    *       *       *       *      /var/pgsql/data/pg_log/parselog.rb > /dev/null

CRON TASKS (system)

# Check for new updates
*/20    *       *       *       *      /home/system/bin/sync-db.sh > /dev/nul