Mass Import WhiteList/BlackList

[rhall_mi]

I am new to EFA and currently use a product called Perfect Mail.

Perfect mail will display my lists (Black/White) to the screen in which I can copy the entire lists.

How can I take this information and dump it into EFA? I would like to import my list(s) to EFA.

I notice the EFA GUI interface will only allow me to type in one name at a time.

Thank you.

[shawniverson]

You can update whitelists and blacklists using a SQL import. This would be a great feature to add to MailWatch though!

I'll do my homework and post how to do this here.

[shawniverson]

Can you share an example of the format your whitelists and blacklists? I want to write a mass import tool to help you do this, and since you asked, I'd be happy to tailor it to your needs.

[rhall_mi]

As can see from my small sample list it is very basic. Either a full email address or you can just list a domain name. I figured I would have to massage the list to comply with EFA requirements like adding “default” on domain only entries.

White List:
skipsmith6560@sbcglobal.net
skybar@umich.edu
slynnbutch@sbcglobal.net
sm-member@ebay.com
smallmidwest@yahoo.com
smcgarry@umich.edu
smokemasternyc@aol.com
smsmaker.com
socalbadboy19@hotmail.com
soliar_99@yahoo.com
soliari_99@yahoo.com

Black List:
businessstylecards@ppymd.arjaygalvanizations.com
businessstylecards@wnnjh.pangacalenturing.com
businesssuccessgroup@mightcool.com
buycar/truck@componentconnectfast.com
bvc@bkhbpa.com
bxmvjtzbctev9nmynt@digisys.net
c.voogd@tiscali.be
cabledeals@thebigideas.net
cameoedacquittals@aamooreandson.co.uk
cancunweekend@bbulletinoutlets.com
cardealers@currentsector.com

Thank you so much for adding this feature.

[dbator]

I was just wondering if any mass import tools has been created yet?

I am migrating from an older version of MailScanner standalone, and have a ton of whitelisted/blacklisted addresses. It would probably take me 4+ hours to manually enter all of them through the mailscanner WebUI

[dbator]

Actually, what would be really helpful is finding what conf file it is writing to when adding Whitelist/Blacklist addresses. I thought it was writing to:

Code: Select all

/etc/MailScanner/rules/spam.whitelist.rules

However, I added a a dummy from and to address and added it to the Whitelist.

Code: Select all

cat /etc/MailScanner/rules/spam.whitelist.rules

I don't see it in here. Conversely I added a dummy from IP address and added it to the whitelist.rules list, restarted the EFA. Upon reboot, checked the Whitelist list on MailScanner WebUI, and it was not here. rechecked the whitelist.rules, and the dummy one I added before still isn't listed here.

[shawniverson]

They are actually being stored in MySQL, hence the need for a mass import tool. I'll make some time to make progress on this as I know it is really needed.

[rhall_mi]

Can someone tell me the path of the MySQL database and name of the database? This way I can create an ODBC connector to it through MS Access and just import them that way.

[shawniverson]

Database is mailscanner, tables are whitelist and blacklist

Password to access mysql is in /etc/EFA-Config

Note that port 3306 (mysql) is closed and firewalled on EFA and will need to be opened up for remote access.

[shawniverson]

Mass import script is now available here:

https://raw.githubusercontent.com/E-F-A ... simport.sh

To download:

Code: Select all

wget https://raw.githubusercontent.com/E-F-A/v3/master/tools/massimport.sh --no-check-certificate
chmod ugo+x massimport.sh

EFA Mass Whitelist and Blacklist Import Help

massimport.sh Copyright (C) 2014 efa-project.org
Licened GNU GPL v3. This program comes with ABSOLUTELY NO WARRANTY
This is free software, and you are welcome to redistribute it under
certain conditions. See http://www.gnu.org/licenses for more details

Usage: massimport.sh -f mylist -b|-w [-a|-o [-q]]
-f Whitelist or Blacklist File
-a append to existing list
-b File is a Blacklist
-q force overwrite database tables without prompting
-o overwrite existing list
-w File is a Whitelist

Whitelist and Blacklist is newline separated list with each
line in either of the following formats:

<From Address Domain or IP>, <To Address Domain or IP>
<From Address Domain or IP>

Examples:

Code: Select all

sudo /path/to/massimport.sh -f /path/to/mywhitelist.txt -a -w

Code: Select all

sudo /path/to/massimport.sh -f /path/to/myblacklist.txt -a -b

Use the overwrite feature with caution. Always back up prior to executing your mass import.

[cdburgess75]

error:

[root@shield ~]# sudo /home/netadmin/wl.sh -f /home/netadmin/wl.txt -o -q -w
sudo: /home/netadmin/wl.sh: command not found
[root@shield ~]# sudo sh /home/netadmin/wl.sh -f /home/netadmin/wl.txt -o -q -w
: command not foundh: line 21:
: command not foundh: line 30:
: command not foundh: line 31:
'home/netadmin/wl.sh: line 32: syntax error near unexpected token `{
'home/netadmin/wl.sh: line 32: `function help(){
[root@shield ~]#

[cdburgess75]

nevermind, it liked the name being massimport.sh - sorry.

[ethandee178]

Good job with the Mass import script.
You can use a simple script to download an online blacklist and import it into the blacklist database.
Keep in mind, I'm no pro at scripting. Here is an example of a weekly cron job. and below is the daily job. There are plenty of sites to download blacklists. Here is one. http://joewein.net/spam/bl-text.htm Be sure to donate to these folks if you feel up to it.

#!/bin/bash
#weekly
cd /root
#Remove old base file
rm ./dom-bl-base.txt*
rm ./dom-bl.txt*
#Download basefile and incremental file
wget http://www.joewein.net/dl/bl/dom-bl-base.txt
wget http://www.joewein.net/dl/bl/dom-bl.txt
#Copy to master blacklist file
cat ./dom-bl-base.txt | sed 's/;.*//' > blacklist.txt
cat ./dom-bl.txt | sed 's/;.*//' >> blacklist.txt
cat ./from-bl.txt | sed 's/;.*//' >> blacklist.txt
#Add files you personally want to blacklist to the localblacklist file
#This then gets copied to master blacklist file every time
cat ./localblacklist.txt >> blacklist.txt
#Had to split blacklist file into many files. because of mysql errors
split -l 10000 ./blacklist.txt blacklist
#Rename first one to keep it out of below loops
mv ./blacklistaa ./black1.txt
echo "Processing /root/blacklistaa file..."
./massimport.sh -f ./black1.txt -b -o -q
#For loop for adding each file to mysql database
FILES=/root/blacklista*
for f in $FILES
do
echo "Processing $f file..."
./massimport.sh -f $f -b -a
done
#For loop for adding each file that starts with b to mysql database
FILES=/root/blacklistb*
for f in $FILES
do
echo "Processing $f file..."
./massimport.sh -f $f -b -a
done
#Cleanup
rm ./black1.txt
rm ./blacklista*
rm ./blacklistb*

############################# Daily File #########################################
#!/bin/bash
#daily
cd /root
#Download the daily file
wget http://www.joewein.net/dl/bl/dom-bl.txt
#Copy the Weekly file and the daily file into one file
cat ./dom-bl-base.txt | sed 's/;.*//' > blacklist.txt
cat ./dom-bl.txt | sed 's/;.*//' >> blacklist.txt
#For senders you want to black list yourself, add to the localblacklist.txt file
#Append that file to the downloaded file
cat ./localblacklist.txt >> blacklist.txt
#I had to split the blacklist file into several because of some mysql errors
split -l 10000 ./blacklist.txt blacklist
#Rename the first one so i can exclude from the loop below
mv ./blacklistaa ./black1.txt
echo "Processing /root/blacklistaa file..."
./massimport.sh -f ./black1.txt -b -o -q
FILES=/root/blacklista*
#Loop that imports the files one at a time.
for f in $FILES
do
echo "Processing $f file..."
./massimport.sh -f $f -b -a
done
#Loop again for the one's that start with a B
FILES=/root/blacklistb*
for f in $FILES
do
echo "Processing $f file..."
./massimport.sh -f $f -b -a
done
#cleanup
rm ./dom-bl.txt*
rm ./black1.txt
rm ./blacklista*
rm ./blacklistb*

[shawniverson]