Perl+PostgreSQL+GeoIP = Awesome!

EDIT: WARNING, THIS USES A LEGACY DATABASE THAT IS NOT UPDATED ANYMORE. Please take a look at GeoIP revisited for an updated version that uses an up-to-date version of the MaxMind GeoIP database.

Sometimes you have to work with GeoIP, e.g. mapping an IP address to the origin country. Be it for legal reasons (geoblocking) or just so you know where your target audience is coming from.

You could just make online lookups for every request. But if you are running a PostgreSQL database backend anyway, there is a simple way to do it in DB, since PostgreSQL supports a CIDR column type.

First, let us define a database table:

CREATE TABLE geoip
(
  netblock cidr NOT NULL,
  country_code text NOT NULL,
  country_name text NOT NULL,
  CONSTRAINT geoip_pk PRIMARY KEY (netblock)
  USING INDEX TABLESPACE "NAMEOFINDEXTABLESPACE"
)
WITH (
  OIDS=FALSE
)
TABLESPACE "NAMEOFDATATABLESPACE";
ALTER TABLE geoip OWNER TO "mydatabaseuser";
[download]

Next, we need a bash script we can run from crontab for our daily update:

#!/usr/bin/env bash

cd /home/myuser/src/geoip
wget http://geolite.maxmind.com/download/geoip/database/GeoIPCountryCS
+V.zip
wget http://geolite.maxmind.com/download/geoip/database/GeoIPv6.csv.gz

gunzip GeoIPv6.csv.gz
unzip GeoIPCountryCSV.zip

rm GeoIPCountryCSV.zip
perl inserttodb.pl

rm *.csv
[download]

And of course some perl script to parse it all and write it to the database. Small problem here, the GeoIP files list IP ranges, but we need to convert it to subnet notation (CIDR). Net::CIDR to the rescue!

#/usr/bin/env perl

use DBI;

use Net::CIDR;

my $dbh = DBI->connect("dbi:Pg:dbname=Cables_DB;host=localhost", 'myda
+tabaseuser', 'secretpassword', {AutoCommit => 0, RaiseError => 0})
        or die("can't connect to DB");

$dbh->do("TRUNCATE geoip");
my $insth = $dbh->prepare_cached("INSERT INTO geoip (netblock, country
+_code, country_name) VALUES (?,?,?)")
        or die($dbh->errstr);

foreach my $file (qw[GeoIPCountryWhois.csv GeoIPv6.csv]) {
    print "Running on file $file...\n";
    open(my $ifh, '<', $file) or die("Can't open $file");
    my $linecount = 0;

    while((my $line = <$ifh>)) {
        $linecount++;
        chomp $line;
        my ($firstip, $lastip, undef, undef, $countrycode, $countrynam
+e) = split/\"\,\ ?\"/, $line;
        $firstip = sanitize($firstip);
        $lastip = sanitize($lastip);
        $countrycode = sanitize($countrycode);
        $countryname = sanitize($countryname);

        my @cidr = Net::CIDR::range2cidr($firstip . '-' . $lastip);

        foreach my $subcidr (@cidr) {
            $insth->execute($subcidr, $countrycode, $countryname) or d
+ie("Fail on $linecount $line: " . $dbh->errstr);
        }

    }
    close $ifh;
}

$dbh->commit;

sub sanitize {
    my ($val) = @_;

    $val =~ s/^\"//;
    $val =~ s/\"$//;

    return $val;
}
[download]

And add a crontab entry with crontab -e:

20 1 * * 1 /bin/bash /home/myuser/src/geoip/updategeoip.sh
[download]

Now we can request the country code for any IP address we encounter:


my $geoip_country = '';
my $geosth = $dbh->prepare("SELECT country_code FROM geoip WHERE ? << 
+netblock LIMIT 1")
        or croak($dbh->errstr);
if(!$geosth->execute($host)) {
    $dbh->rollback; # Not a big problem, GEOIP is just for information
+ anyway
} else {
    my $line = $geosth->fetchrow_hashref;
    if(defined($line->{country_code})) {
        $geoip_country = $line->{country_code};
    } else {
        $geoip_country = '??';
    }
    $dbh->rollback;
}
[download]

Of course, now that the up-to-date geoip lists are in the database, it's even possible to use an ON INSERT OR UPDATE trigger to any table that needs geoip data. But that i will leave as an excercise for the reader...

perl -e 'use MIME::Base64; print decode_base64("4pmsIE5ldmVyIGdvbm5hIGdpdmUgeW91IHVwCiAgTmV2ZXIgZ29ubmEgbGV0IHlvdSBkb3duLi4uIOKZqwo=");'

Back to Cool Uses for Perl