[Pdns-dev] Small code change to support split-horizon - GEO Routing using SQL

[Chris Savery]

Hello,

I have been experimenting with some (very) small changes to provide a 
function I desired - Geo Based DNS Routing. I'm very new to the PDNS 
code and just started yesterday browsing the source. I realize there is 
a GeoBackend available but it doesn't really do what I want. I want to 
also have my DNS records in MySql and be able to use a web console to 
manage them easily and assign regions that way.

So I will give an explanation of what I've done here and if anyone is 
interested please feel free to ask for more. I haven't been reading the 
list so I'm not aware of other mods to accomplish this. I did do some 
googling though and didn't see anything. I think this is a pretty nifty 
little mod to accomplish quite a bit of additional flexibility. In fact, 
I was looking at Wikipedia and they have a DNS server comparison chart 
that indicates PowerDNS could do but doesn't currently have "split 
horizon" support. I believe this would provide a very simple version of 
that. So here goes...

My idea was that the only thing that we really need was to get the IP of 
the source request into the SQL query and then the rest could be handled 
there by returning suitable results. I looked at how PDNS code was 
structured and it's very nice. The GSQL backend is the parent of the 
other more specific SQL backends so making a very small change there 
enables this capability for all of them.

I modified gsqlbackend.cc so that the 4 main query builder statements  
using sprintf have an additional parameter. Normally the sql query 
doesn't see this parameter because it by default only contains 2 %s 
substitution variables. However, if you provide a custom set of queries 
in the pdns.conf file then you can use another %s that will be replaced 
with the IP address of your source request.

The code change is trivial - just add pkt_p->getRemote.c_str() after the 
2 or 3 other parameters to the sprintf on lines: 260, 267, 280, 287 - 
sorry I have not made  a patch to do this (yet). This change shouldn't 
affect normal use with default queries.

Now, if you add your own custom queries then you can use them to do GEO 
based routing with SQL as described below. I think you could also do 
other stuff fairly easily too but these are the ones I'm using for what 
I need.

First - I have added a region_id (int) to the records table. This allows 
me to assign any record to be used only for a certain region or if zero 
(0) then it will be valid for any region. Next, I created a couple more 
tables to provide the simple GEO backend. You can get away with one 
table but by having two you can support multiple mappings of regions to 
domain records. For example, if you want to have multiple customers in 
the DNS records and they have different ways of mapping country codes to 
their own servers. So this first table could easily be skipped if 
multiple mappings aren't needed.

table - geomap
region_id - int -- this is what maps countries to the regions used in 
the records table
geo_id - int -- this is same as below, country code

table - cidrmap
cidr unsigned int -- this is an unsigned int version of the start of 
each ip block
geo_id - int  -- this is a numeric equivalent to the 2 letter country code

Ok. Now I wrote a small php script to take the Maxmind country data and 
import it into the cidr table. It stores the IP address as an unsigned 
int in acsending order such that each value represents a change in 
assigned block. So if your ip value is greater than one number (but 
below the next) then that means it's in this block. This makes it very 
fast an easy to locate the block for any IP. The result of the lookup is 
a geo_id code that represents the country (or whatever region division 
the cidr corresponds to).

The geo_id code could be used directly to match against the records 
table. But if you allow for one level of indirection by stepping it 
through the geomap table first then you can have multiple mappings of 
country to DNS records. Also this allows you to map many countries into 
one region_id that connects to one server DNS entry. It's very easy and 
fast. Just need to fill in country data to cidrmap table (my php script 
does that) and then any user can select countries and map them to DNS 
zones which would fill in the entries of geomap.

So to make this work you need to mod the default queries with a bit of 
extra joining and stuff. Here's an example that works for me (you add 
this to pdns.conf along with same idea for 3 other queries):

gmysql-basic-query=select content,ttl,prio,type,domain_id,name from 
records r, geomap g where r.type='%s' and r.name='%s' and 
((r.region_id=g.region_id and g.geo_id=(select geo_id from cidrmap where 
cidr <= inet_aton('%s') order by cidr desc limit 1)) or r.region_id=0)

Notice here that the mysql function inet_aton takes a text dotted ip 
value and returns an unsigned int value. Just the magic needed.

The other queries are modified likewise. In short, we select the geo_id 
for the cidr block that the ip is in by finding the ones less or equal 
and flipping them upside down and taking the first only. Then we join 
that id value to the geomap table and get the region_id. We then use 
that to select only suitable DNS records. At the end we also have a term 
( or r.region_id=0 ) that gets any records with zero region. These ones 
are available to all regions or if no region specific records exist for 
the query.

I haven't had the time to test this a lot. But it does work for my first 
test cases and it even appears to be <almost> as fast as the the default 
queries. That is, when I use the host to test it reports the times to 
get results as being only slightly more, eg. 4 ms instead of 3ms, or 
some such value.

The Maxmind country data that I imported ends up with about 106,000 
records in the cidrmap table taking about 780K total (and another 1MB 
for indexing too). This isn't too bad and mysql will only load some 
portions into memory so I haven't noticed much increase in PDNS memory 
footprint. The geomap table uses about 190 records to map each country 
to a few regions. Finer granularity could be obtained by adding more 
cidrmap records as needed. The Maxmind citysource  data is about 15 
times bigger (100MB). I haven't tried that. Other source data could be 
used. you could add code to ping back the source IP and build a table of 
times on the fly for each requestor and link that to regions.

Now, finally - I 'm sure there will be an issue with the packet cache in 
a situation like this. So this is the question I have for developers 
here who are much more familiar with PDNS. I think that probably the 
packetcache.cc file can be modified slightly such that source IP value 
is part of the cache key. This would cache the packets such that wrong 
ones aren't given out to other requesters (from different geo regions). 
I intuitively think it's as simple as that or just turning off the cache 
(which seems a real shame). I was going to work on making that change 
tonight and testing if that was enough. Then I thought that probably 
someone here really knows if that will work as expected. And what about 
the query cache? I haven't even started to look at that but maybe it 
stores the queries so that they already get keyed correctly? What else 
may need to be fixed for this to work correctly?

Anyway, I'm open to feedback on this whole thing. Is there some reason 
it will fail miserably? It is working as expected for my very small 
tests here. I set up 3 regions for each of US, EU and AS and then tried 
requests that returned correct A records for the region when the source 
IP matched countries in the region. Not thorough but a first start. If 
anyone wants more details or the php code to import the cidrmap table 
then please go ahead and ask. Should I make up a patch for this? I 
haven't done that before but it could be fun to learn. It would add an 
exciting new functionality to PDNS.

Thanks for your time and I hope this helps someone or at least 
stimulates thought. Who have thunk it was so easy to get Geo mapping out 
of PDNS...

Chris :)






-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://mailman.powerdns.com/pipermail/pdns-dev/attachments/20080901/4649b9fb/attachment.htm