SQLgrey and whitelisting senders

I implemented whitelisting of sender addresses and domains in SQLgrey. Now you can whitelist senders that should never be greylisted, and you can let outgoing email automatically update the whitelist.

On my mail server I use postfix, amavisd-new, clamav, and SQLgrey. SQLgrey implements greylisting for postfix using MySQL as repository. There is a web interface called sgwi that allows you to view and modify the SQLgrey database tables.

I have recently been looking at ASSP, a spam filtering SMTP proxy server. One neat feature of ASSP is the ability to whitelist senders so that they will not be greylisted. The neat thing is that the whitelist is automatically updated whenever you send an email to someone: the recipient is added to the whitelist so that the recipient's reply will not be greylisted.

This "whitelisting of sender addresses" feature is missing in SQLgrey, so I decided to implement it. (SQLgrey comes with optin/optout functionality, but this is for the recipient address.)

1. First of all I modified SQLgrey 1.7.6 by adding two new database tables named "whitelist" and "whitelist_exclude":
   • "whitelist" contains sender email addresses or domains that are whitelisted, i.e. will not be greylisted.
   • "whitelist_exclude" contains sender email addresses or domains that should never be whitelisted, i.e. will always be greylisted.
   SQLgrey now creates these tables and checks them to see if greylisting should be applied to a particular email.
2. Second, I modified sgwi so that I could view and modify the new database tables.
3. Third, I wrote a postfix content filter by modifying dkimproxy, see the story about that. The proxy automatically adds email addresses to the whitelist when outgoing mail is sent.

The idea is that when I send an outgoing email, I use a special smtp port, e.g. 587:

client ---> (587) smtpd -----> (10026) dkimproxy-----> (10027) smtpd ----> next smtp server

Incoming mail will normally be received on port 25 (the standard smtp port), which doesn't do whitelisting, while I send my outgoing mail on port 587. To be sure the port won't be abused, I have configured it to require SASL authentication.

Here is how I configured the postfix master.cf file:

# ====================================================================
# DKIM signature and SQLgrey whitelisting
#
# Mail is sent to filter on localhost:10026, and received back on localhost:10027
# (advanced after-queue filter according to http://www.postfix.org/FILTER_README.html#advanced_filter)
#
587     inet  n       -       n       -       -       smtpd
    -o content_filter=scan:localhost:10026
    -o receive_override_options=no_address_mappings
    -o smtpd_enforce_tls=yes
    -o smtpd_delay_reject=yes
    -o smtpd_client_restrictions=
    -o smtpd_helo_restrictions=
    -o smtpd_sender_restrictions=
    -o smtpd_recipient_restrictions=permit_mynetworks,permit_sasl_authenticated,reject

scan      unix  -       -       n       -       4      smtp
    -o smtp_send_xforward_command=yes
    -o disable_mime_output_conversion=yes
    -o smtp_generic_maps=

localhost:10027 inet  n       -       n       -       5      smtpd
    -o content_filter=
    -o receive_override_options=no_unknown_recipient_checks,no_header_body_checks,no_milters
    -o smtpd_helo_restrictions=
    -o smtpd_client_restrictions=
    -o smtpd_sender_restrictions=
    -o smtpd_recipient_restrictions=permit_mynetworks,reject
    -o mynetworks=127.0.0.0/8
    -o smtpd_authorized_xforward_hosts=127.0.0.0/8
 

The lines above define the two smtpd listeners on ports 587 and 10027. The dkimproxy process, which is started separately, listens on 10026 and sends to 10027.

Attached are two files that contain the whitelisting patches I made to SQLgrey and sgwi.

Update 090526: The whitelist now keeps track of the last send and receive dates.
Update 100209: Updated the sgwi patch for sgwi 0.8. Note however that the patch for the copyright.inc.php file will fail until the sgwi version number has been updated correctly to 0.8
Update 100428: Added patch for SQLgrey 1.8.0-rc2. I cleaned up the patch a bit, removed some unnecessary changes.
Update 101112: Added patch for sgwi 1.1.1.
Update 110816: Added patch for sgwi 1.1.2.
Update 131223: Updated patch for sgwi 1.1.2.
Update 140220: Added patches for SQLgrey 1.8.0 for sgwi 1.1.6.
Update 151103: Added patch for sgwi 1.1.8.
Update 161110: Updated patch for sgwi 1.1.8.