Show/Hide Toolbars

GeoIP lookup in VPOP3's Service Access Restrictions can be used to specify that only IP addresses from certain countries can access VPOP3 services. Many people use this type of restriction to prevent access from countries commonly used by attackers.

In fact, the VPOP3 GeoIP facility isn’t limited to GeoIP data. Essentially it has a database which matches IP address ranges to a ‘tag’ and then you can check against the tags. A common use would be GeoIP – the tags would be country codes – but it could be used for other things as well. As the database is queried live, you could even update the database dynamically and have VPOP3 update its access restrictions automatically.

GeoIP database

However, a common use is GeoIP, so that is what this article will describe.

Installing GeoIP data

The first thing is that VPOP3 does not include any GeoIP data. This is partly due to licensing restrictions of such data, and also because it often changes, and there are different sources.

So, to use the GeoIP facility, you need to obtain GeoIP data and import it into VPOP3.

One free GeoIP database source is GeoLite from Maxmind, so that is what this article will use. You need to decide what sort of data you will be checking against. For this example we will use the GeoLite Country database. You can also check against cities, or ASNs (essentially checking against Internet providers’ ranges).

For these steps you need the CSV database, so to download the GeoLite Country database, you need to sign up for an account first. The details are here: https://blog.maxmind.com/2019/12/18/significant-changes-to-accessing-and-using-geolite2-databases/

After creating an account, go to your MaxMind account, go to Download Files, and download GeoLite2 Country: CSV Format (ZIP file). Unzip this file. It contains multiple files. The important ones are "GeoLite2-Country-Locations-en.csv" which contains the mappings for numeric IDs to country codes, and "GeoLite2-Country-Blocks-IPv4.csv" which contains the mappings for IP subnets to numeric IDs

If you want, you can open up this file to see what it contains. It will easily open in a text editor, or a spreadsheet program.

We have a tool called VPOP3GeoImport.exe which will import data from a CSV file into the VPOP3 database. To use it, download the file from http://www.pscs.co.uk/downloads/vpop3/VPOP3ImportGeoIP.zip and extract into the VPOP3 installation directory

However, this tool requires there to be a single CSV containing IP subnets or ranges and the 'result' wanted (the country code, in this case). Because MaxMind no longer supply the data in a single file, it needs some manipulation to get it into the correct form. Two possible ways to do this are:

Manipulate data in Excel

Assuming you have access to Excel or similar spreadsheet software, this will not require any other software and the required transformation can be done using a basic Excel formula.

1) Open the GeoLite2-Country-Blocks-IPv4.csv file in Excel

2) Create a new 'sheet' (Sheet2) and use Data -> From Text/CSV  to load the GeoLite2-Country-Locations-en.csv file into that second sheet

3) In cell 'I2' put the formula =VLOOKUP(B2, Sheet2!A:E, 5)

4) Copy/paste that formula into the whole 'I' column. Now the 'I' column will contain the country code

Save the file as a CSV file

Run

VPOP3GeoImport [options] importcsv <filename> 19

Use Python script

This method requires you to have access to Python, but you can then just run a script and it will produce a file in a suitable format. Download the Python script from http://www.pscs.co.uk/downloads/vpop3/transform_geoiplite_2020.zip

Put this file in the same directory as the data files you downloaded above, then run

python transform_geoilite.py

This will create a file called 'output.csv' which has the IP subnet in column 1 and the country code in column 2, so, to import the data into VPOP3's database, run:

VPOP3GeoImport [options] importcsv <filename> 12

 

Normally you won’t need any options, but if you run “VPOP3GeoImport -?”, you will get a list of options available. (The numbers at the end tells the program which columns to import from the CSV file).

This program will delete all existing data in the GeoIP database within VPOP3 and then add the data from the CSV file. Note that you do not need to stop VPOP3 while you perform this operation.

Using the GeoIP data

To use the GeoIP data, in the relevant VPOP3 Access Restrictions settings, select the GeoIP Lookup type in your access restriction, and enter the desired ‘tags’ in the Address box. In the case of the above country GeoIP data, the tags would be ISO country codes, eg US, GB, etc. To specify multiple tags to look for, you can separate them with commas or semicolons. The Access Restriction rule will match if any of the tags are matched.

Technical

The GeoIP data is stored in a database table called geoipv4. This has 4 columns:

id – numeric primary key, assigned by the database

addrfrom – IP address at the start of the range

addrto – IP address at the end of the range

result – the data to be retrieved if the searched IP address is within the range addrfrom <= address <= addrto

If multiple entries match the searched IP address, then each result will be checked against the specified access restrictions.

 

If you think this help topic could be improved, please send us constructive feedback