Page 1 of 1

How To Mass Import Whitelists and Blacklists

Posted: 21 Sep 2014 19:40
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.

Re: How To Mass Import Whitelists and Blacklists

Posted: 17 Feb 2017 15:19
by SupportOU
Hola!

Is there also a mass export functionality??

Grtz,
Ronald

Re: How To Mass Import Whitelists and Blacklists

Posted: 17 Feb 2017 23:33
by shawniverson
Should be pretty straightforward.

Re: How To Mass Import Whitelists and Blacklists

Posted: 17 Feb 2017 23:33
by shawniverson

Re: How To Mass Import Whitelists and Blacklists

Posted: 29 Mar 2018 12:08
by enricosx
thanks for yours hard work.

what is the correct syntax?

domain.com
or
*domain.com
or
*@domain.com

Re: How To Mass Import Whitelists and Blacklists

Posted: 05 Jan 2019 18:48
by shawniverson
Wildcards should not be used.

Re: How To Mass Import Whitelists and Blacklists

Posted: 14 Feb 2020 20:26
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/.*://'`"

Re: How To Mass Import Whitelists and Blacklists

Posted: 27 May 2020 09:38
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?

Re: How To Mass Import Whitelists and Blacklists

Posted: 21 Sep 2020 12:43
by bhaskar
Hi,

Please tell me how to uninstall EFA in centos 7

Re: How To Mass Import Whitelists and Blacklists

Posted: 26 May 2023 06:05
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'

Re: How To Mass Import Whitelists and Blacklists

Posted: 29 May 2023 05:25
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.