[Pdns-users] Recursor: Black list

[Aki Tuomi]

On Mon, Oct 27, 2014 at 01:56:17PM -0300, Ciro Iriarte wrote:
> 2014-10-27 3:46 GMT-03:00 Aki Tuomi <cmouse at youzen.ext.b2.fi>:
> >
> > In a way i'd chosen sqlite3 instead as it is pretty much on par with cdb.
> > But, to make it work properly, i'd just add "*.domain.com", and when you lookup,
> > you could reduce it like this with get()
> >
> > www.my.long.name.com => NOT FOUND
> > *.my.long.name.com => NOT FOUND
> > *.long.name.com => NOT FOUND
> > *.name.com => FOUND
> >
> > (
> > of course you could continue with
> > *.com
> > *
> > )
> >
> > Aki
> 
> Hi Aki!, I couldn't find a (finished) benchmark that compares directly
> sqlite3 vs cdb, but the unfinished tests imply that cdb is faster.
> Given it's SQL I assume we can just use a SELECT with LIKE clause to
> match an "ending" on the DB with the requested fqdn, would it be
> faster than doing multiple cdb queries (one for each part of the
> requested fqdn)?
> 
> Regards,
> 
> -- 
> Ciro Iriarte
> http://iriarte.it
> --
>

The difference, to my eyes, is the diference between

  SELECT name FROM table WHERE name LIKE '%suffix';

and

  SELECT name FROM table WHERE name = 'www.my.long.name.com';
  SELECT name FROM table WHERE name = '*.my.long.name.com';
  SELECT name FROM table WHERE name = '*.long.name.com';
  SELECT name FROM table WHERE name = '*.name.com';
  SELECT name FROM table WHERE name = '*.com';

(assuming you'll want to filter out, say, *.xxx)

Obviously using suffix would require you to know what you are
doing, since you'd have to know what suffix to look for, otherwise
you'll end up with very unpredicable behaviour. 

Consider, you have www.name.com in your blacklist, you'll look for
%.name.com. It'll always return match. So it's safer to go with
repeated lookups for *.parent. 

Performance-wise you should consider that your most likely usage 
patterns are, 

not blacklisted:
  SELECT name FROM table WHERE name = 'www.name.com';
  SELECT name FROM table WHERE name = '*.name.com';
  SELECT name FROM table WHERE name = '*.com';

blacklisted:
  SELECT name FROM table WHERE name = 'www.name.com';

or:
  SELECT name FROM table WHERE name = 'www.name.com';
  SELECT name FROM table WHERE name = '*.name.com';


to give proper answer whether SQLite3 or CDB is better, you'd have to 
run benchmark tests against these use cases as they cover most of your
situations.

Also, you might want to consider early-break on any query ending with
in-addr.arpa and i6.arpa, unless you are required to filter these too,
because you can get pretty long iterations especially with IPv6 reverses. 

All in all, i'd say go with cdb, since you already have the code there
and it's not a big mod to make. Just keep this is mind. 

---
Aki