Might be useful if you're running your own DNSBL
As many of us know, DNS records let you have wildcards, like this: *.example.com.
It means that this record covers all subdomain requests,
which might be useful, for example, for blogger.com, who gives every registered user
a subdomain for their blog: <username>.blogger.com,
with all these subdomains pointing to the same server(s).
However, DNS doesn't let you have wildcards or arbitrary depth:
you can't have records like *.*.example.com, and
(maybe that's why?)
blogger.com doesn't let you have multiple sub-blogs
like techno.<username>.blogger.com, arts.<username>.blogger.com, etc.
It becomes a real pain when you're managing a DNS blocklist, even for domains.
For example, if I add just spammer's domain example.com to my blocklist,
they can simply start spamming from subdomains like tho9.example.com.
I can add *.example.com to fight all first-level subdomains,
but then they add one more subdomain level
and start spamming from sub-subdomains like mdh8.rcsj.example.com.
And here I can't do anything, because *.*.example.com is not a valid entry.
Luckily, spammers actually don't use subdomains much, for some reason. But we still can prepare for this. PowerDNS, when using SQL backend, lets you change queries used to fetch records. And even provides sane defaults. :-) So we can just change them to queries which will try all parent subdomains, up until some limit.
First, see the default queries for your backend:
pdns_server --launch=BACKEND --config=default
Second, change the ones you need :)
In case of gsqlite3 backend, I saw multiple queries with name=:qname condition in WHERE part of query.
It should be enough to replace this condition with
name=:qname OR
name like '%.rhsbl.shpakovsky.ru'
AND :qname LIKE '%.' || name
(line breaks added for readability, add braces as needed),
where rhsbl.shpakovsky.ru is the apex domain name for blacklist.
This way, requesting any domain under *.rhsbl.shpakovsky.ru will return all records for all its parent domains.
Note the importance of limiting how far we want to "climb":
you don't want to return A record for rhsbl.shpakovsky.ru domain itself
when someone is using your service to check if some domain is in your blocklist
(they do it by requesting subdomain, like example.com.rhsbl.shpakovsky.ru,
and checking if it resolves to an IP address -
and if it does - it means the domain is blocked by your blocklist).
Hence, importance of name like '%.rhsbl.shpakovsky.ru' check.
Also, you probably need to change the part in SELECT where it returns the domain name,
to return name of domain that was asked instead of domain that's in database.
This way you won't confuse PowerDNS itself by giving it DNS records for one domain
when it asked you (SQL database) about another.
So here are the relevant entries from my pdns.conf file:
gsqlite3-any-id-query=SELECT content,ttl,prio,type,domain_id,disabled,:qname AS name,auth FROM records WHERE disabled=0 and (name=:qname OR name like '%.rhsbl.shpakovsky.ru' AND :qname LIKE '%.' || name) and domain_id=:domain_id
gsqlite3-any-query=SELECT content,ttl,prio,type,domain_id,disabled,:qname AS name,auth FROM records WHERE disabled=0 and (name=:qname OR name like '%.rhsbl.shpakovsky.ru' AND :qname LIKE '%.' || name)
gsqlite3-basic-query=SELECT content,ttl,prio,type,domain_id,disabled,:qname AS name,auth FROM records WHERE disabled=0 and type=:qtype and (name=:qname OR name like '%.rhsbl.shpakovsky.ru' AND :qname LIKE '%.' || name)
gsqlite3-id-query=SELECT content,ttl,prio,type,domain_id,disabled,:qname AS name,auth FROM records WHERE disabled=0 and type=:qtype and (name=:qname OR name like '%.rhsbl.shpakovsky.ru' AND :qname LIKE '%.' || name) and domain_id=:domain_id
Adding proper indexes is left as exercise to the reader. Just kidding, please let me know if you find a good index suitable for these queries. Maybe index on subexpressions? 🤔 My email is at the bottom of this page, as always.