Complete Virtual Mail Server/GreyListing

From Gentoo Linux Wiki

[edit] GreyListing

Greylisting is a simple approach to significantly reducing your spam. When an email is received from an unknown contact/ip, the mail is rejected with a “try again later” message. Since most spammers use MTA’s that misbehave, they do not bother trying again and so the mail is never delivered. Legitimate mail servers will try again at which time the message will be accepted. For more information on greylisting, have a look at http://www.greylisting.org.

I decided to use the SQLGrey application even though it is not currently stable in the portage tree. It was forked from Postgrey (which is supported) but includes some additional features that I think make it worth going with.

Since this package is masked by the ~x86 keyword, you will need to override that first by putting it into your keywords file. Then we can emerge it like we normally would.

 
# mkdir -p /etc/portage
# echo "mail-filter/sqlgrey ~x86" >> /etc/portage/package.keywords
# emerge sqlgrey 
  

I would suggest reading the HOWTO that is part of sqlgrey before you jump in. There is a lot of good information explaining the installation process as well as ongoing maintenance issues.

 
# man /usr/share/doc/sqlgrey-1.x.y/HOWTO.gz // replace 1.x.y with the correct version
  

Finished? Then let’s walk through the specific configuration settings we need to make for our installation. We will start with a bit of housekeeping. On startup, the sqlgrey script is going to be looking for a home directory at /var/spool/sqlgrey. Although we don’t specifically need it for our implementation, we will create the directory just to get rid of the error message on startup.

 
# mkdir /var/spool/sqlgrey
# chown sqlgrey:sqlgrey /var/spool/sqlgrey
  

The second bit of housekeeping is that the sqlgrey startup script that came in my version had an error in it. The problem was up in the copyright notice so I recreated these lines, deleted the originals and the problem was cleared (I didn’t bother taking the time to find the root cause).

With that out of the way, we can now get into configuring this. The instructions provided with sqlgrey tell you to make a new database for sqlgrey. I’ve decided the last thing I need is another database, so I am going to point sqlgrey to our amavis database, however I will still create a separate database user for sqlgrey.

 
createuser -U postgres sqlgrey
Shall the new user be allowed to create databases? (y/n) y
Shall the new user be allowed to create more new users? (y/n) n
CREATE USER
  

Fortunately we do not need to create the database tables as the sqlgrey script will do this automatically when it starts up. All we need to do is tell the .conf script about our database and how to connect.

 
#nano /etc/sqlgrey/sqlgrey.conf

## Database settings
# instead of Pg below use "mysql" for MySQL, "SQLite" for SQLite
# any DBD driver is allowed, but only the previous 3 have been tested
db_type = Pg
db_name = amavis
# Note: the following are not used with SQLite
db_host = dbServerhostname
db_user = sqlgrey
db_pass = $password
# db_cleandelay = 1800 # in seconds, how much time between database cleanups
# clean_method = sync
                      # sync : cleanup is done in the main process,
                      #        delaying other operations
                      # async: cleanup is done in a forked process,
                      #        it won't delay mail processing
                      #        BEWARE: lockups have been reported
                      #        and are still investigated
  

The last step is to make postfix aware of sqlgrey by including it as one of the smtpd_recipient_restrictions. In our case, we will configure it based on sqlgrey running on the same server as postfix and that it will be listening on the default port 2501. If you want/need to use a different port, then you can change that value in the /etc/sqlgrey/sqlgrey.conf file.

 
# nano /etc/postfix/main.cf

smtpd_recipient_restrictions =
        permit_sasl_authenticated,
        permit_mynetworks,
        reject_unauth_destination,
        check_policy_service inet:127.0.0.1:2501
  

[edit] Testing

Testing this is going to be a little less direct then our previous tests. Everything in grey listing happens at the MTA level and so the sender and receiver are never aware that anything has even happened. If your ISP has mail servers that do not follow the rules, you may find that the request to try again later is never adhered to and your message get bounced as undeliverable, or even worse, just lost.

To start with, we will need to start sqlgrey and restart postfix.

 
# /etc/init.d/sqlgrey start
 * Starting SQLgrey...                                          [ ok ]

# /etc/init.d/postfix restart
 * Stopping postfix...                                          [ ok ]
 * Starting postfix...                                          [ ok ]
  

Go back to your email account that you have been using to send mails from and send a non spam-message to your test account in postfix. Then have a look at the /var/log/messages file to see if you have a sqlgrey log entry indicating that the received message was identified as a new greylist entry.

 
#nano /var/log/messages

Nov 19 21:52:49 sqlgrey: grey: new: 63.247.81(63.247.81.238), myeamil@ispdomain.com -> user@example.com
Nov 19 21:52:49 postfix/smtpd[31261]: NOQUEUE: reject: RCPT from tss6.serverconfig.com[63.247.81.238]: 4$
Nov 19 21:52:49 postfix/smtpd[31261]: disconnect from tss6.serverconfig.com[63.247.81.238]
  

You should also go the amavis database and check the sqlgrey table contact. In it you should find a single entry which is the email address you sent from, the IP address, time of receipt, etc.

After waiting about 20 minutes (spent the time in the middle writing up the last bits of this section) I sent another test message a verified that it did get through. Again, check the messages log. You should entries that indicate the message has been passed and if you check the from_awl table in your database, you will find that your sending address has been added and you are now whitelisted.

As for the original message, it is just a matter of waiting for your ISPs mail server to re-send and confirm that it gets through. In my case it took about 90 minutes before a resend was attempted.

Edit by: Alphacube 12:20, 10 May 2008 (UTC)

If you find something like

ERROR sqlgrey: dbaccess: warning: couldn't do query: CREATE TABLE yadayada... in your log.

Dont't worry it can be fixed.

It's due to incompability with SqlGrey and newer MySQL with UTF8 encoding.

MySql can't create PRIMARY KEY when varchar is (255).

Simply edit /usr/sbin/sqlgrey and change all lines that look like this.

  
PRIMARY KEY (email));
to
PRIMARY KEY (email(100)));

or for the lazy use my patch. make a backup of /usr/sbin/sqlgrey put the patch in the same directory and issue

patch sqlgrey patch-file.patch

  
--- sqlgrey-orig        2008-05-10 01:28:14.000000000 +0200
+++ sqlgrey     2008-05-10 01:31:43.000000000 +0200
@@ -820,7 +820,7 @@
              'first_seen timestamp NOT NULL, ' .
              'last_seen timestamp NOT NULL, ' .
              'PRIMARY KEY ' .
-             '(src, sender_domain, sender_name))')
+             '(src, sender_domain(100), sender_name))')
        or $self->mydie('create_from_awl_table error',
                        'Couldn\'t create table $tablename: $DBI::errstr');
 }
@@ -842,7 +842,7 @@
              'src varchar(39) NOT NULL, ' .
              'first_seen timestamp NOT NULL, ' .
              'last_seen timestamp NOT NULL, ' .
-             'PRIMARY KEY (src, sender_domain))')
+             'PRIMARY KEY (src, sender_domain(100)))')
        or $self->mydie('create_domain_awl_table error',
                        "Couldn't create table $tablename: $DBI::errstr");
 }
@@ -887,7 +887,7 @@
     $self->do("CREATE TABLE $config " .
              '(parameter varchar(255) NOT NULL, ' .
              'value varchar(255), ' .
-             'PRIMARY KEY (parameter));')
+             'PRIMARY KEY (parameter(100)));')
         or $self->mydie('create_config_table',
                        "Couldn't create table $config: $DBI::errstr");
     # we just created the table: this is the current version
@@ -907,7 +907,7 @@
     my $self = shift;
     $self->do("CREATE TABLE $optin_email " .
              '(email varchar(255) NOT NULL, ' .
-             'PRIMARY KEY (email));')
+             'PRIMARY KEY (email(100)));')
        or $self->mydie('create_optin_email_table',
                        "Couldn't create table $optin_email: $DBI::errstr");
 }
@@ -916,7 +916,7 @@
     my $self = shift;
     $self->do("CREATE TABLE $optout_domain " .
              '(domain varchar(255) NOT NULL, ' .
-             'PRIMARY KEY (domain));')
+             'PRIMARY KEY (domain(100)));')
        or $self->mydie('create_optout_domain_table',
                        "Couldn't create table $optout_domain: $DBI::errstr");
 }
@@ -925,7 +925,7 @@
     my $self = shift;
     $self->do("CREATE TABLE $optout_email " .
              '(email varchar(255) NOT NULL, ' .
-             'PRIMARY KEY (email));')
+             'PRIMARY KEY (email(100)));')
        or $self->mydie('create_optout_email_table',
                        "Couldn't create table $optout_email: $DBI::errstr");
 }

Hope this helps. cheers

[edit] UI Interface

Of course, if a UI is available, we might as well make use of it. Over at www.vanheusden.com there is an extensive library of open source applications, one which is a very basic PHP based UI for SQLgrey. It is very straight forward to put in place so let’s make use of it.

First go download the application to your local /homedir on the web server, untar it and move it into our web directory. As with the other applications, I will put this in a subdirectory under my squirrelmail installation.

 
#cd /homedir
#wget http://www.vanheusden.com/sgwi/sqlgreywebinterface-0.6.tgz

#tar –xvzf sqlreywebinterface-0.6.tgz

#cd /var/www/example.com/htdocs
#cp –R /homedire/sqlgreywebinterface-0.6 ./sqlgrey
  

Because we do not want any outside people to adjust our greylist settings, we will protect this with an .htaccess file in the sqlgrey subdirectory.

 
#cd sqlgrey
#nano .htaccess

AuthUserFile /var/www/example.com/htdocs/sqlgrey/.htpasswd
AuthGroupFile /dev/null
AuthName "SQLGrey"
AuthType Basic

<limit GET POST>
require valid-user
</limit>
  

Don’t forget to create the .htpasswd file.

 
#htpasswd2 -c .htpasswd admin
New password:
Re-type new password:
Adding password for user admin
  

At that is left is to set the parameters in the config file so that the UI can connect to our database. As you can see below, it is very simple.

 
#nano /var/www/example.com/htdocs/sqlgrey/config.inc.php
<?
$db_db   = "amavis";
$db_hostname = "dbServerhostname";
$db_user = "sqlgrey";
$db_pass = "$password";

$db_type = "pg"; # mysql or pg (pg=postgress)
?>
  

That’s it, all done. Now all you should have to do is point your browser at the installed directory (www.example.com/sqlgrey), you will be prompted for you userid and password that you created above and then you are in. There is not much to this one, but it could be an interesting addition to some of the existing UIs (again for somebody with some ambition to take on a more “complete” solution).