How To Mass Import Whitelists and Blacklists

Questions and answers about how to do stuff
Post Reply
User avatar
shawniverson
Posts: 3644
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

How To Mass Import Whitelists and Blacklists

Post by shawniverson »

Mass import script is now available here:

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

To download:

Code: Select all

wget https://raw.githubusercontent.com/E-F-A/v3/master/tools/listimport.sh 
chmod ugo+x userimport.sh
EFA Mass Whitelist and Blacklist Import Help

listimport.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: listimport.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/listimport.sh -f /path/to/mywhitelist.txt -a -w

Code: Select all

sudo /path/to/listimport.sh -f /path/to/myblacklist.txt -a -b
Use the overwrite feature with caution. Always back up prior to executing your mass import.
SupportOU
Posts: 47
Joined: 12 Sep 2016 18:47

Re: How To Mass Import Whitelists and Blacklists

Post by SupportOU »

Hola!

Is there also a mass export functionality??

Grtz,
Ronald
User avatar
shawniverson
Posts: 3644
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: How To Mass Import Whitelists and Blacklists

Post by shawniverson »

Should be pretty straightforward.
enricosx
Posts: 3
Joined: 29 Mar 2018 11:52

Re: How To Mass Import Whitelists and Blacklists

Post by enricosx »

thanks for yours hard work.

what is the correct syntax?

domain.com
or
*domain.com
or
*@domain.com
User avatar
shawniverson
Posts: 3644
Joined: 13 Jan 2014 23:30
Location: Indianapolis, Indiana USA
Contact:

Re: How To Mass Import Whitelists and Blacklists

Post by shawniverson »

Wildcards should not be used.
liam87
Posts: 5
Joined: 05 Dec 2017 08:38

Re: How To Mass Import Whitelists and Blacklists

Post by liam87 »

If you would like to use this script with eFa 4.0 just replace the line

Code: Select all

MAILWATCHSQLPWD=`grep MAILWATCHSQLPWD /etc/EFA-Config | sed 's/.*://'`
with

Code: Select all

MAILWATCHSQLPWD="`grep MAILWATCHSQLPWD /etc/eFa/MailWatch-Config | sed 's/.*://'`"
PABBY
Posts: 1
Joined: 19 May 2020 13:04

Re: How To Mass Import Whitelists and Blacklists

Post by PABBY »

I'm posting this in case anyone comes across a similar problem while migrating their white and black lists from a Windows environment.

I recently installed my first EFA4 appliance using the ISO image. After a few minor teething problems (DNS), it was soon up and running, and I was keen to migrate our white and black lists from our existing system (Sophos PureMessage running on Windows 2016 & Exchange 2016). I scoured the website & forums and eventually ended up on this "How to" post.

I installed the script with no issues and tested it with a couple of domain names - perfect. I went ahead and exported my lists from PureMessage, transferred them over to the appliance and imported them using the script, all with no issues. My white and black lists were fully populated - job done.

Except… only domains I entered manually into the lists were working and those I'd imported were being ignored.

I re-scoured the forums and found a couple of people with similar problems, but no resolution. I then came across an article that detailed how to export your lists from the MySQL database for transfer into another appliance, so I tried this with my lists, but the results were the same - only manually entered domains were being processed against incoming email.

It was only when I loaded the exported SQL table into vi that I discovered what my problem was - each imported domain name had \r tagged onto the end of it, similar to this example:

Code: Select all

(438,'default','','sampledomain.com\r')
I used vi to search and replace all instances of \r in the exported SQL file and re-imported it into my database. My white and black lists now worked perfectly.

I know why the domain names are appended with \r but can find no simple way of avoiding it when migrating lists from a Windows system. Is the removal of \r something that could be included in the import script to avoid others experiencing the same problem?
bhaskar
Posts: 1
Joined: 21 Sep 2020 12:35

Re: How To Mass Import Whitelists and Blacklists

Post by bhaskar »

Hi,

Please tell me how to uninstall EFA in centos 7
efagokan1
Posts: 24
Joined: 28 Mar 2022 05:57

Re: How To Mass Import Whitelists and Blacklists

Post by efagokan1 »

Hello

I am getting the following error while using it. This address is neither on the white nor black list.
How can I go about this error?

error:
ERROR 1062 (23000) at line 2: Duplicate entry 'xyz@gmail.com' for key 'whitelist_uniq'
User avatar
pdwalker
Posts: 1553
Joined: 18 Mar 2015 09:16

Re: How To Mass Import Whitelists and Blacklists

Post by pdwalker »

What were you doing when this error occured?

The error seems self explanatory - it is saying that EFA is trying to insert xyz@gmail.com into the whitelist table that already has that address in table.

The

Code: Select all

CREATE TABLE `whitelist` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `to_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `to_domain` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `from_address` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `whitelist_uniq` (`to_address`(100),`from_address`(100))
) ENGINE=InnoDB AUTO_INCREMENT=14 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
The key statement is
UNIQUE KEY `whitelist_uniq` (`to_address`(100),`from_address`(100))

So somehow you are trying to add in a duplicate to, from address pair into the table.
Post Reply