Thread: [Q] DNS(bind) ER model

Anyone aware of an ER model for holding name server records?

Working on the zone file data and I am getting close but keep running
into the differences between MX records (with a priority) and the others
that can hold either a domain/sub-domain/host name or an IP address
depending on whether is an A, TXT, PTR, etc. or a CNAME.

Much of the database will be populated and changed automagically so the
controller for the application will do the right thing but humans will
get involved every so often.  I hope I can get the database to make the
right thing easy and the wrong thing "impossible" for them.

Any suggestions?


Rod
--

Mr Anderson-

you use an enum to indicate your DNSrecordtype as in this MySQL example
CREATE TABLE dns_updates
| Field              |Type  | Null | Key | Default | Extra  || id    | int(11) | NO   | PRI | NULL | auto_increment |
| bd_order_id    | int(11)          | YES  |     | NULL                |
| domain           | varchar(70)      | NO   |     |                     |
| dns_entry_type  | enum('MX','TXT') | YES  |     | NULL           |
| dns_entry_value   | varchar(255)     | YES  |     |                     |
| created_timestamp | datetime         | NO   | MUL | 0000-00-00 00:00:00 |

for Postgres sub in text[] columns dns_entry_type forthe specified enum datatype
and insert in '{"MX", "TXT"}'  for dns_entry_type values

Anyone else?
Martin



> Date: Thu, 14 Aug 2008 16:20:14 -0700
> From: raanders@acm.org
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] [Q] DNS(bind) ER model
>
> Anyone aware of an ER model for holding name server records?
>
> Working on the zone file data and I am getting close but keep running
> into the differences between MX records (with a priority) and the others
> that can hold either a domain/sub-domain/host name or an IP address
> depending on whether is an A, TXT, PTR, etc. or a CNAME.
>
> Much of the database will be populated and changed automagically so the
> controller for the application will do the right thing but humans will
> get involved every so often. I hope I can get the database to make the
> right thing easy and the wrong thing "impossible" for them.
>
> Any suggestions?
>
>
> Rod
> --
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Get Windows Live and get whatever you need, wherever you are. Start here.

On Thu, Aug 14, 2008 at 04:20:14PM -0700, Roderick A. Anderson wrote:
> Anyone aware of an ER model for holding name server records?

What about a datatype?  I have reason to believe that a company I used
to work for implemented such a thing.  There was some talk of
releasing it, but I think there was some combination of insufficient
demand, worries about competitive advantage, and concern about long
term support.

Because of my involvement in that project, I can't make direct
recommendations about this topic.  But supposing one was to go to
various DNS-related lists, it seems to me it wouldn't be that hard to
get information about the wire datatypes such that you'd have enough
information to implement them in Postgres (assuming you know something
about Postgres datatypes).

One hint: remember the "unknown" RRTYPE.  If you have questions about
RRTYPEs, I'm happy to answer.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Friday 15 August 2008, Roderick A. Anderson wrote:
> Anyone aware of an ER model for holding name server records?
>
> Working on the zone file data and I am getting close but keep running
> into the differences between MX records (with a priority) and the others
> that can hold either a domain/sub-domain/host name or an IP address
> depending on whether is an A, TXT, PTR, etc. or a CNAME.
>
> Much of the database will be populated and changed automagically so the
> controller for the application will do the right thing but humans will
> get involved every so often.  I hope I can get the database to make the
> right thing easy and the wrong thing "impossible" for them.
>
> Any suggestions?
>
>
> Rod
> --

Have you looked at mydns?  It is a database driven DNS server - and it
works just fine with Postgresql.

David

On Fri, Aug 15, 2008 at 10:26:54AM +0100, David Goodenough wrote:
> Have you looked at mydns?  It is a database driven DNS server - and it
> works just fine with Postgresql.

Given that the references section of its documentation doesn't include
a number of important RFCs, are you quite sure it's really a complete
DNS server?  (Note that I haven't tested it, so I'm asking in genuine
ignorance.  But the lack of a reference to RFC 2671, which defines
EDNS0, sure doesn't give me warm fuzzies.  If you're deploying a name
server today and it doesn't support EDNS0, it's possible you're going
to find that some users can't resolve your names in the near future.)

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

Andrew Sullivan wrote:
> On Thu, Aug 14, 2008 at 04:20:14PM -0700, Roderick A. Anderson wrote:
>> Anyone aware of an ER model for holding name server records?
>
> What about a datatype?  I have reason to believe that a company I used
> to work for implemented such a thing.  There was some talk of
> releasing it, but I think there was some combination of insufficient
> demand, worries about competitive advantage, and concern about long
> term support.

Thanks A.  I did a quick look and a custom TYPE may be useful.
>
> Because of my involvement in that project, I can't make direct
> recommendations about this topic.  But supposing one was to go to
> various DNS-related lists, it seems to me it wouldn't be that hard to
> get information about the wire datatypes such that you'd have enough
> information to implement them in Postgres (assuming you know something
> about Postgres datatypes).

More research is on the schedule this week-end.

> One hint: remember the "unknown" RRTYPE.  If you have questions about
> RRTYPEs, I'm happy to answer.

Thanks again.  This is a pretty specialized application (at this time)
so the RRTYPEs used are limited.  I am trying to make the model and Pg
implementation as generic as possible in case it gets released into the
wild later.

Thanks for the offer on the RRTYPEs.  Zone files /can/ get pretty hairy
plus the company I'm doing this for gets some strange requests from
their customers -- not always correct or logical.  :-(


Rod
--

David Goodenough wrote:
> On Friday 15 August 2008, Roderick A. Anderson wrote:
>> Anyone aware of an ER model for holding name server records?
>>
>> Working on the zone file data and I am getting close but keep running
>> into the differences between MX records (with a priority) and the others
>> that can hold either a domain/sub-domain/host name or an IP address
>> depending on whether is an A, TXT, PTR, etc. or a CNAME.
>>
>> Much of the database will be populated and changed automagically so the
>> controller for the application will do the right thing but humans will
>> get involved every so often.  I hope I can get the database to make the
>> right thing easy and the wrong thing "impossible" for them.
>>
>> Any suggestions?
>>
>>
>> Rod
>> --
>
> Have you looked at mydns?  It is a database driven DNS server - and it
> works just fine with Postgresql.

David,

I believe I looked at mydns, and there was a sqldns also, quite awhile
ago thinking to use that model as a starting point but the project got
side-lined so I never followed up.  The project is back on so I'll look
again.

Thanks,
Rod
--
>
> David
>

On Fri, Aug 15, 2008 at 07:44:36AM -0700, Roderick A. Anderson wrote:

> Thanks again.  This is a pretty specialized application (at this time) so
> the RRTYPEs used are limited.  I am trying to make the model and Pg
> implementation as generic as possible in case it gets released into the
> wild later.

I made the mistake in the past of not supporting the unknown type, and
regretted it.  The nice thing about implementing unknown is that you
can automatically add another RR later, even if you're not sure what
it's supposed to look like.

> plus the company I'm doing this for gets some strange requests from their
> customers -- not always correct or logical.  :-(

We DNS geeks have seen every mistake in the book, and some of the
worst ideas are still being developed.  (In Dublin, I heard someone
from the DKIM working group at last suggest that maybe using the TXT
RRTYPE wasn't such a hot idea.  I think it's now 5 years since the DNS
folks pointed out that TXT was going to cause headaches later.  Sigh.)

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Aug 15, 2008, at 12:16 PM, Andrew Sullivan wrote:

> On Fri, Aug 15, 2008 at 07:44:36AM -0700, Roderick A. Anderson wrote:
>
>> Thanks again.  This is a pretty specialized application (at this
>> time) so
>> the RRTYPEs used are limited.  I am trying to make the model and Pg
>> implementation as generic as possible in case it gets released into
>> the
>> wild later.
>
> I made the mistake in the past of not supporting the unknown type, and
> regretted it.  The nice thing about implementing unknown is that you
> can automatically add another RR later, even if you're not sure what
> it's supposed to look like.

+1

>> plus the company I'm doing this for gets some strange requests from
>> their
>> customers -- not always correct or logical.  :-(
>
> We DNS geeks have seen every mistake in the book, and some of the
> worst ideas are still being developed.  (In Dublin, I heard someone
> from the DKIM working group at last suggest that maybe using the TXT
> RRTYPE wasn't such a hot idea.  I think it's now 5 years since the DNS
> folks pointed out that TXT was going to cause headaches later.  Sigh.)

The DKIM people have been pointing that out for at least as long.

Guess why they still went with the TXT record? Mostly because of the
number of lame self-service DNS interfaces that don't support much
apart from A, MX, CNAME and TXT. (To bring it on-topic, mostly
because they use very simplistic database backends, I suspect...)

Back to the original problem... I'm not sure there's a generic good
structure for DNS data, it'd depend a lot on what you were planning
on doing with it. Serving DNS directly out of the database is
a very different set of needs to basic self-service management, which
is a different set of needs to enterprise intranet DNS and so on.

Cheers,
   Steve

Roderick A. Anderson wrote:
> Anyone aware of an ER model for holding name server records?
>
> Working on the zone file data and I am getting close but keep running
> into the differences between MX records (with a priority) and the others
> that can hold either a domain/sub-domain/host name or an IP address
> depending on whether is an A, TXT, PTR, etc. or a CNAME.
>
> Much of the database will be populated and changed automagically so the
> controller for the application will do the right thing but humans will
> get involved every so often.  I hope I can get the database to make the
> right thing easy and the wrong thing "impossible" for them.
>
> Any suggestions?

looks like you want to write your own "powerdns" ? :-)
http://www.powerdns.com/

Greets
Tino

On Fri, Aug 15, 2008 at 09:54:26PM +0200, Tino Wildenhain wrote:
> looks like you want to write your own "powerdns" ? :-)
> http://www.powerdns.com/

Oh, right, I forgot they use a SQL back end.  They do EDNS0, too :)

(Note, however, that if you plan to deploy DNSSEC you're out of luck
with them.  Bert is hostile to it.)

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

Steve Atkins wrote:
>
> On Aug 15, 2008, at 12:16 PM, Andrew Sullivan wrote:
>
>> On Fri, Aug 15, 2008 at 07:44:36AM -0700, Roderick A. Anderson wrote:
>>
>>> Thanks again.  This is a pretty specialized application (at this
>>> time) so
>>> the RRTYPEs used are limited.  I am trying to make the model and Pg
>>> implementation as generic as possible in case it gets released into the
>>> wild later.
>>
>> I made the mistake in the past of not supporting the unknown type, and
>> regretted it.  The nice thing about implementing unknown is that you
>> can automatically add another RR later, even if you're not sure what
>> it's supposed to look like.
>
> +1
>
>>> plus the company I'm doing this for gets some strange requests from
>>> their
>>> customers -- not always correct or logical.  :-(
>>
>> We DNS geeks have seen every mistake in the book, and some of the
>> worst ideas are still being developed.  (In Dublin, I heard someone
>> from the DKIM working group at last suggest that maybe using the TXT
>> RRTYPE wasn't such a hot idea.  I think it's now 5 years since the DNS
>> folks pointed out that TXT was going to cause headaches later.  Sigh.)
>
> The DKIM people have been pointing that out for at least as long.
>
> Guess why they still went with the TXT record? Mostly because of the
> number of lame self-service DNS interfaces that don't support much
> apart from A, MX, CNAME and TXT. (To bring it on-topic, mostly
> because they use very simplistic database backends, I suspect...)
>
> Back to the original problem... I'm not sure there's a generic good
> structure for DNS data, it'd depend a lot on what you were planning
> on doing with it. Serving DNS directly out of the database is
> a very different set of needs to basic self-service management, which
> is a different set of needs to enterprise intranet DNS and so on.

Here is the hitch.  It is for a application to be used in-house.
Actually several applications with all tied together quite closely.

1. Domain information: owner, webmaster, registrar, email manager, etc.
2. IP address allocation:  There are several non-contiguous blocks of
addresses and they tend to be assigned to a specific system or client.
3. DNS things:  This will not be a name server but be used build
named.conf and the zone files.
4. And tying the above together an application to let a
tech/support/sales person add a new domain and have it automagically
assigned the correct IPs for web, mail, ftp, and all that other stuff.
5. Finally a application to allow the system people to add, change,
reassign, and delete domain and zone file entries.


Rod
--

>
> Cheers,
>   Steve
>
>

On Thu, 2008-08-14 at 16:20 -0700, Roderick A. Anderson wrote:
> Anyone aware of an ER model for holding name server records?
>
> Working on the zone file data and I am getting close but keep running
> into the differences between MX records (with a priority) and the others
> that can hold either a domain/sub-domain/host name or an IP address
> depending on whether is an A, TXT, PTR, etc. or a CNAME.

Don't add a column for the prio of the MX record. A lot of designs do
this but IMHO it's crap. If you do it for MX records you also have to do
it for SRV records and who knows what other (future) records).

We (@work) use an in house designed database that use the best technique
I've ever seen.

What it does is use a table to list all the valid RR types(1) along with
an ID and regular expressions that describe what the name and rdata
should look like.

In the table that holds the dns records the type of the record is
foreign key referencing the type table. And insert/update triggers are
used to check that records match the patters in the types table.

With this technique supporting a new record type is as easy as inserting
a new row in the types table. And it also garanties that all records in
the database is at least syntactically correct. (Assuming your patters
are correct of course.)

> Much of the database will be populated and changed automagically so the
> controller for the application will do the right thing but humans will
> get involved every so often.  I hope I can get the database to make the
> right thing easy and the wrong thing "impossible" for them.
>
> Any suggestions?

HTH.

Cheers,
Mark.

Mark wrote:
> On Thu, 2008-08-14 at 16:20 -0700, Roderick A. Anderson wrote:
>> Anyone aware of an ER model for holding name server records?
>>
>> Working on the zone file data and I am getting close but keep running
>> into the differences between MX records (with a priority) and the others
>> that can hold either a domain/sub-domain/host name or an IP address
>> depending on whether is an A, TXT, PTR, etc. or a CNAME.
>
> Don't add a column for the prio of the MX record. A lot of designs do
> this but IMHO it's crap. If you do it for MX records you also have to do
> it for SRV records and who knows what other (future) records).

I was working towards that direction.  I really hated the idea of a
sparse table and even a sparse column.

>
> We (@work) use an in house designed database that use the best technique
> I've ever seen.
>
> What it does is use a table to list all the valid RR types(1) along with
> an ID and regular expressions that describe what the name and rdata
> should look like.

Interesting idea.  A project I worked on awhile ago did something
similar.  Actually stored some Perl code.

> In the table that holds the dns records the type of the record is
> foreign key referencing the type table. And insert/update triggers are
> used to check that records match the patters in the types table.

Better and better.

> With this technique supporting a new record type is as easy as inserting
> a new row in the types table. And it also garanties that all records in
> the database is at least syntactically correct. (Assuming your patters
> are correct of course.)


Thanks,
Rod
--
>> Much of the database will be populated and changed automagically so the
>> controller for the application will do the right thing but humans will
>> get involved every so often.  I hope I can get the database to make the
>> right thing easy and the wrong thing "impossible" for them.
>>
>> Any suggestions?
>
> HTH.
>
> Cheers,
> Mark.
>
>

Andrew Sullivan wrote:
> On Fri, Aug 15, 2008 at 09:54:26PM +0200, Tino Wildenhain wrote:
>> looks like you want to write your own "powerdns" ? :-)
>> http://www.powerdns.com/
>
> Oh, right, I forgot they use a SQL back end.  They do EDNS0, too :)
>
> (Note, however, that if you plan to deploy DNSSEC you're out of luck
> with them.  Bert is hostile to it.)

recent(upcoming 2.9.22) powerdns code actually does have dnssec support
(a bit limited though).
On the other hand getting your typical database backed DNS-managment GUI
to grok DNSSEC is probably turing out to become a challenge.


Stefan