http://evilcode.net/sjg/infernal/PowerDNS/SCHEMA/
I have been looking at PowerDNS for a while now, and after regular confirmation that it is in fact performing extremely admirably over at DreamHost I decided that it was time to deploy it.
While PowerDNS is the least braindead DNS server I have ever come across, there were a couple of things that I was not 100% happy with, at least in terms of coupling it to a web frontend.
- SOA records are stored space-delimited. This would hardly be a problem except that our serial is stored here. In its defense, PowerDNS has an alternate method of handling serials that is probably better in most circumstances. Hardly, but we would still have to break it apart and put it back together again to edit the minimum (default in practice) TTL, etc.
- Record types are stored textually. Even when implemented as an enumerated value this still violates DRY, as you must re-state these values in your frontend code.
- Everything must be represented fully qualified. This = FAIL from a normalization perspective.
Here I have come up with a somewhat optimal schema from the point of view of my web interface, and I have tied it to PowerDNS’s preferred table structure via domain logic. This could have been handled in other ways of course, but I tend to like this one for a number of reasons.
- First, the alternative is to add custom queries to the PowerDNS configuration file to make it understand whatever schema we might have in place, PowerDNS actually makes this very easy.
- Another alternative would be to use dynamic (normal) views.
On to the benefits, some being quite minor.
- Querying against serialized views will have performance benefits versus the above two options, this of course has to be weighed against the cost of maintaining the views.
- As mentioned, PowerDNS has two methods of handling serials, either in the SOA record, which we are keeping up to date with our domain logic. Alternatively PowerDNS will scan each record for you to find the most recently updated (if you maintain change_date). The former should logically be more performant, so we have implemented that option. This could have been handled either way in the domain logic, but most importantly we aren’t relying on our web frontend to keep our serials up to date.
- Most importantly, namely for debuggability, data on master’s and slave’s “looks the same”.
To get you rolling your PowerDNS configuration file need not be any more complicated than this:
launch=gpgsql
gpgsql-host=hostname
gpgsql-user=powerdns
gpgsql-password=password
gpgsql-dbname=pdnstest
daemon=yes
I haven’t tried slaving yet, but I suspect it will work without a hitch. Will update here when I do and when this rolls out.
I wanted to store A records in postgres’ inet type so I made a view for the records table and added three rules for insert, delete and update. Here is the update example:
CREATE OR REPLACE RULE records_update_rule AS
ON INSERT TO records DO INSTEAD SELECT records_view_update(new.id, new.domain_id, new.name, new.”type”, new.content::text, new.ttl, new.prio, new.change_date) AS records_view_update;
CREATE OR REPLACE FUNCTION trolls.records_view_update(record_id bigint, record_domain_id integer, record_name character varying, record_type character varying, record_content text, record_ttl integer, record_prio integer, record_change_date integer)
RETURNS boolean AS
$BODY$BEGIN
IF record_type ILIKE ‘A’ THEN
UPDATE dns_a_records SET domain_id = record_domain_id, “name” = record_name, “type” = record_type, “content” = substring(record_content from ‘d{1,3}.d{1,3}.d{1,3}.d{1,3}’)::inet, ttl = record_ttl, prio = record_prio, change_date = record_change_date WHERE id = record_id ;
ELSE
UPDATE dns_misc_records SET domain_id = record_domain_id, “name” = record_name, “type” = record_type, “content” = record_content, ttl = record_ttl, prio = record_prio, change_date = record_change_date WHERE id = record_id;
END IF;
RETURN TRUE;
END$BODY$
LANGUAGE ‘plpgsql’ VOLATILE;
Where the tables dns_a_records and dns_misc_records share a sequence for id and have content types inet and text respectively.
Working so far!
Did you use a custom web interface or something from a package?
rocker,
This is a great addition, thanks for sharing. We are using a fairly simple/pragmatic custom Rails-based frontend for management.
I hope you have found this useful 😉
Thanks,
Sam