Page 1 of 1

Blacklist/Whitelist not working

Posted: 11 Oct 2018 08:59
by Mail2GoCa
My software versions are...
EFA: 3.0.2.6
MailWatch: 1.2.7-dev
Postfix: 3.1.3
MailScanner: 5.0.7
ClamAV: 0.100.1
SpamAssassin: 3.4.1
PHP: 7.2.10
MySQL: 10.1.36-MariaDB

I ran into an issue when I imported my whitelist and blacklist data. I did not use the import script provided in these forums because it did not work for me. Since that import script was created in 2014, that may be the reason why. I didn't bother trying to debug. Instead I imported the data via the MySql control panel in WebAdmin (ver.1.890)

As new emails started arriving I noticed that the imported records were not having any effect. Only the domains/addresses I added to the list via the MailWatch UI were working. So I took a look in the MySql control panel to see if I could perhaps force a re-index of the tables. I found two unique indexes called blacklist_uniq and whitelist_uniq. I opened them to see which fields they were indexing and when I saved one of them I got an error advising me that a unique index cannot be created with blob type fields. At that point I noticed that the fields were of type 'mediumtext' in the whitelist and 'longtext' in the blacklist. From what I understand about MySql unique indexes, any text fields included in the index require a set byte length due to the index key length limitation.

To fix this, I change the data type of the text fields to varchar(128). Then I was able to rebuild the unique indexes successfully.

I have no clue what the requirements were in much earlier versions of MySql, but I am suspecting that this is a legacy issue from when unique indexes could be built with blob fields or text fields without a set byte length. Or perhaps its been a long standing issue which nobody ever noticed. Either way, I'm glad the index is now working.

However, unfortunately it did not fix my original problem.

Just to recap...
Whitelist and blacklist entries which are imported have no effect.
Whitelist and blacklist entries made via the Mailwatch UI work properly.

Has anybody else experienced this issue and if so, did you ever find a solution?

Thanks.

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 04:09
by pdwalker
So, you’re fiddling with the database by hand.

It would help greatly if you could be specific as to what you did to what tables. E.g. imported this record into that table and the result looks like...

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 05:27
by Mail2GoCa
Hi pdwalker,

here is a sample of the data imported from CSV files. I determined the format by first exporting the existing databases to CSV files.

1,default,,@domain1.com
2,default,,@domain2.com
3,default,,@domain3.com
4,default,,@domain4.com

Both tables (blacklist and whitelist) only have 4 fields as follows:
id, to_address, to_domain, from_address

Plus they are in the exact same order as the data specified in the CSV files I imported.
Once imported, they populate the fields properly and look exactly the same as the records that were added via the UI.

I am now wondering if the data from the UI is being stored in the table in a non-default character set. When I imported the data, I left the character set selection at <Default>

I'm not sure what else I can tell you, but if you have any specific questions, please fire away.

Thanks for your help.

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 08:55
by pdwalker
Ok, I just did a test on my system

First, I connected to the mysql database and queried my blacklist table:

Code: Select all

SELECT * FROM mailscanner.blacklist;
then, I entered in 1 new blacklist entry:

Code: Select all

insert into mailscanner.blacklist (to_address, to_domain, from_address) values ('pdwalker@internaldomain.com', 'internaldomain.com', 'pdwalker@externaldomain.com');
Then I went to my externaldomain account and send mail to my internal domain account - my incoming message was immediately blacklisted.

Next, I removed that entry

Code: Select all

delete from mailscanner.blacklist where to_address = 'pdwalker@internaldomain.com';
and added a new entry into the whitelist table

Code: Select all

insert into mailscanner.whitelist (to_address, to_domain, from_address) values ('pdwalker@internaldomain.com', 'internaldomain.com', 'pdwalker@externaldomain.com');
and then sent myself a message again - and it was still blacklisted.

So, I restarted mailscanner and resent the message from externaldomain.com and it worked.

Conclusion:

- whitelisting, blacklisting works just fine if you've put the data into the database correctly.
- it might take a moment or two before the efa instance reads the new entries from the database -
- when in doubt, restart mailscanner.

Hope this helps.

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 09:21
by Mail2GoCa
Thanks for the time you've taken to test this out.

I did in fact reboot the appliance after I imported the lists.

In the white list I imported around 550 entries and in the blacklist I imported approx 4800 entries.
After the reboot I let it run for a good 2 hours before checking if the entries were working and still nothing.

I'm not sure what else to try so I've given up on it. Besides, I don't think I'm going to need all those entries anyway because I've already manually entered the most important whitelist entries and the greylist is already eliminating at least 95% of the spam anyway. In fact I've only had to manually enter about 10 blacklist records so far in the 4 days I've been using EFA.

Out of the approximately 2500 emails received daily, only about 500 of them are making it past the greylist. Out of those 500, only about 25 are spam and SpamAssassin is doing a pretty good job catching most of them.

I think I have found the perfect anti-spam gateway. Well, not perfect, but certainly less management work for me.

Thanks again :D

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 09:33
by pdwalker
I'd look in your mail log to see if you can see anything interesting.

For example, the following command shows me that mailwatch frequently updates the black and white list entries:

Code: Select all

grep " MailWatch: Read " /var/log/maillog| tail -100 | vim -
I assume it's pulling from the database.

Maybe you can search your log file for one of those lines - and see it it matches the numbers you expect it to. Or maybe there is an error message somewhere around this log file entry?

Oh, last stupid question. Is sqlgrey running on your system?

Code: Select all

[root@efa log]# ps -ef |grep sqlgrey
sqlgrey  18417     1  0 Sep05 ?        00:00:46 /usr/bin/perl -w /usr/sbin/sqlgrey -d
root     21585 23325  0 17:31 pts/0    00:00:00 grep sqlgrey

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 10:00
by Mail2GoCa
Thanks. I'll look into that after I set up a copy of the current production VM in a lab environment. This should prove interesting.

As for SqlGrey, yep it's definitely running. That's what's catching 95% of the spam.

Code: Select all

[root@emxg ~]# ps -ef |grep sqlgrey
sqlgrey   2727     1  0 Oct11 ?        00:00:03 /usr/bin/perl -w /usr/sbin/sqlgrey -d
root     17982 17941  0 10:02 pts/0    00:00:00 grep sqlgrey

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 10:12
by Mail2GoCa
Looks like it was finding the entries. Just not using them.

Code: Select all

Oct  9 19:46:28 emxg MailScanner[27207]: MailWatch: Whitelist refresh time reached
Oct  9 19:46:28 emxg MailScanner[27207]: MailWatch: Starting up MailWatch SQL Whitelist
Oct  9 19:46:28 emxg MailScanner[27207]: MailWatch: Read 523 whitelist entries
Oct  9 19:46:28 emxg MailScanner[27207]: MailWatch: Blacklist refresh time reached
Oct  9 19:46:28 emxg MailScanner[27207]: MailWatch: Starting up MailWatch SQL Blacklist
Oct  9 19:46:29 emxg MailScanner[27207]: MailWatch: Read 4784 blacklist entries

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 10:25
by pdwalker
How do you know it's not using them?

Try this:

Code: Select all

cd /var/spool/MailScanner
grep -r X-Greylist: *|less
I get a lot of entries like this:

Code: Select all

quarantine/20180914/nonspam/A2B9B182AA3.A9CDD:X-Greylist: whitelisted by SQLgrey-1.8.0
quarantine/20180914/nonspam/17388182A9B.ADAAB:X-Greylist: whitelisted by SQLgrey-1.8.0
quarantine/20180914/nonspam/54B39182A9B.AACE3:X-Greylist: whitelisted by SQLgrey-1.8.0

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 10:38
by Mail2GoCa
because the domains in the whitelist table that were imported are still being scanned by SA (not highlighted in green) and the ones in the blacklist table that were imported were not being blocked (Not highlighted in black).
A couple of the whitelist emails were flagged as spam by SA and never made it to the recipient mailbox. I had to release them. Also a couple of blacklisted emails were marked as clean and ended up being delivered to the recipient mailbox.

All the emails mentioned above were related to imported whitelist and blacklist records. The records I entered manually via the Mailwatch UI (lists.php) were being used and respectively being highlighted green or black in the message list and respectively delivered or blocked.

That is what is so strange about this issue.

There has to be some hidden difference between the imported records and the entered ones. Perhaps different UTF byte sizes, or character sets, or something similar. I'll have to play with that in my lab.

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 10:43
by Mail2GoCa

Code: Select all

quarantine/20180914/nonspam/A2B9B182AA3.A9CDD:X-Greylist: whitelisted by SQLgrey-1.8.0
Simply means that it was whitelisted as a valid domain/address and IP combination in SqlGrey which is the first line of defense in EFA but is a completely separate program. This has absolutely nothing to do with MailScanner. The associated whitelist tables for SqlGrey are located in the SqlGrey database.

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 10:53
by pdwalker
Looks like I am getting things mixed up.

Anyway, according to my previous test, the whitelisting/blacklisting worked just fine.

Can you add a valid external email address to your blacklist and test it yourself like I did? Then repeat it for the whitelist in the manner similar to what I did?

According to your logs, mailcanner is reading your updated black/white list entries - so it should be working.

Re: Blacklist/Whitelist not working

Posted: 12 Oct 2018 11:43
by Mail2GoCa
I'll be testing all this methodically once I set up my lab and I'll report back with documented steps and results.

Thanks for all your help so far.

Re: Blacklist/Whitelist not working

Posted: 20 Oct 2018 10:38
by Mail2GoCa
I haven,t forgotten about this. I've just abandoned it because I no longer need my original blacklist entries to be imported and the whitelist is quite short, so I've entered them manually.