DLZ PostgreSQL help needed
Brian Hechinger <[email protected]>
| Newsgroups | gmane.network.dns.bind9.dlz |
|---|---|
| Message-ID | <[email protected]> |
Hello DLZ Team,
I started working with DLZ and PostgreSQL a couple months ago. I first
setup a very basic table that worked with the queries provided in the
examples. Or so I thought. I didn't completely test it, I just plowed
on to turning it into stored procedures and then finally insane stored
procedures that worked with the app I was writing.
In that final form it mostly works, but there are some parts of it that
are just giving me a terribly hard time. They tend to be the SOA/NS/MX
type records that I just can't seem to get right.
So I've decided that perhaps a bit of "back to basics" is in order. My
plans for this were two-fold. First, I really wanted to understand
exactly what was going on at a basic level so I could make my more
complicated version work. Second, I really struggled with the dlz pgsql
docs when I first started working with this. I wanted to be able to
have a set of example data and example stored procedures that I could
contribute to the DLZ project so that people in the future who wanted to
do exactly what I'm attempting to do don't have to struggle as much as I
have had to.
Here's what I am currently running into. My table looks like this:
CREATE TABLE dns_records (
oid SERIAL PRIMARY KEY NOT NULL,
zone VARCHAR,
ttl INTEGER,
recordtype VARCHAR,
host VARCHAR,
mx_priority INTEGER,
data VARCHAR,
primary_ns VARCHAR,
resp_person VARCHAR,
serial INTEGER,
refresh INTEGER,
retry INTEGER,
expire INTEGER,
minimum INTEGER
);
My queries are directly out of the documentation, but I'll post them
anyway just to make sure I didn't fat-finger something.
dlz "postgres zone" {
database "postgres 2
{host=localhost port=5432 dbname=dns_test user=dns password=xxxxx}
{select zone from dns_records where zone = '%zone%'}
{select ttl, recordtype, mx_priority, case when lower(recordtype)='txt'
then '\"' || data
|| '\"' else data end from dns_records where zone = '%zone%' and host
= '%record%'
and not (recordtype = 'SOA' or recordtype = 'NS')}
{select ttl, recordtype, mx_priority, data, resp_person, serial,
refresh, retry, expire,
minimum from dns_records where zone = '%zone%' and (recordtype = 'SOA'
or recordtype='NS')}
{select ttl, recordtype, host, mx_priority, data, resp_person, serial,
refresh, retry, expire,
minimum from dns_records where zone = '%zone%'}
{select zone from xfr_table where zone = '%zone%' and client =
'%client%'}";
};
The data format is going to be ugly, so I'll do it as CSV:
oid,zone,ttl,recordtype,host,mx_priority,data,primary_ns,resp_person,serial,refresh,retry,expire,minimum
1,example.com,300,SOA,,,,ns1.example.com,dnsadmin.example.com,2008011701,8600,8600,8600,8600
2,example.com,300,NS,@,,ns1.example.com,,,,,,,
3,example.com,300,NS,@,,ns2.example.com,,,,,,,
4,example.com,300,MX,@,10,mail1.example.com,,,,,,,
5,example.com,300,MX,@,20,mail2.example.com,,,,,,,
6,example.com,300,A,machine1,,10.0.0.1,,,,,,,
7,example.com,300,A,machine2,,10.0.0.2,,,,,,,
8,0.0.10.in-addr.arpa,300,PTR,1,,machine1.example.com.,,,,,,,
9,0.0.10.in-addr.arpa,300,PTR,2,,machine2.example.com.,,,,,,,
10,example.com,300,CNAME,www,,machine1,,,,,,,
11,example.com,300,CNAME,ftp,,machine2,,,,,,,
As far as I can tell, this is good data, maybe that's part of the
problem? Do I have my data wrong and that's what's causing my problems?
If I do a simple A record lookup, I get results:
$ dig @localhost A www.example.com
;; ANSWER SECTION:
www.example.com. 300 IN CNAME machine1.example.com.
machine1.example.com. 300 IN A 10.0.0.1
However, this is the last thing in the log file:
21-Jan-2008 08:24:59.787 984 query is 'select ttl, recordtype,
mx_priority, data, resp_person, serial, refresh, retry, expire,
minimum from dns_records where zone = 'example.com' and
(recordtype = 'SOA' or recordtype='NS')'
21-Jan-2008 08:24:59.787 984 executing query for 0 time
21-Jan-2008 08:24:59.788 984 rs ok
21-Jan-2008 08:24:59.788 984 cleaning up
21-Jan-2008 08:24:59.788 984 unlocking mutex
21-Jan-2008 08:24:59.789 984 returning
21-Jan-2008 08:24:59.789 dns_rdata_fromtext: buffer-0xb7a75ee4:1: near
eof: unexpected end of input
21-Jan-2008 08:24:59.789 dns_sdlz_putrr returned error. Error code was:
unexpected end of input
That query returns these results:
dns_test=# select ttl, recordtype, mx_priority, data, resp_person,
serial, refresh, retry, expire,
dns_test-# minimum from dns_records where zone = 'example.com' and
(recordtype = 'SOA' or recordtype='NS');
ttl | recordtype | mx_priority | data | resp_person
| serial | refresh | retry | expire | minimum
-----+------------+-------------+-----------------+----------------------+------------+---------+-------+--------+---------
300 | SOA | | |
dnsadmin.example.com | 2008011701 | 8600 | 8600 | 8600 | 8600
300 | NS | | ns1.example.com |
| | | | |
300 | NS | | ns2.example.com |
| | | | |
(3 rows)
Is that not correct? That certainly looks right to me.
I don't get NS record info back with my queries, so the fact that it's
failing that last query makes sense that I don't get the data related to
that. But what is wrong with that query that it doesn't like?
If I try to lookup an MX record, I get the same error on the same query.
A side question to that is why does that query return mx_priority if
it's looking at only SOA and NS, neither of which would ever have that
data?
Since I've taken the queries from the documentation, I can only assume
I've gotten my data wrong somehow.
Please help, this is extremely frustrating!!!
Thanks,
-brian
-------------------------------------------------------------------------
This SF.net email is sponsored by: Microsoft
Defy all challenges. Microsoft(R) Visual Studio 2008.
http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/
_______________________________________________
Bind-dlz-testers mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bind-dlz-testers
dlz.email
(text/plain, 5.7 KB)
Hello DLZ Team,
I started working with DLZ and PostgreSQL a couple months ago. I first
setup a very basic table that worked with the queries provided in the
examples. Or so I thought. I didn't completely test it, I just plowed
on to turning it into stored procedures and then finally insane stored
procedures that worked with the app I was writing.
In that final form it mostly works, but there are some parts of it that
are just giving me a terribly hard time. They tend to be the SOA/NS/MX
type records that I just can't seem to get right.
So I've decided that perhaps a bit of "back to basics" is in order. My
plans for this were two-fold. First, I really wanted to understand
exactly what was going on at a basic level so I could make my more
complicated version work. Second, I really struggled with the dlz pgsql
docs when I first started working with this. I wanted to be able to
have a set of example data and example stored procedures that I could
contribute to the DLZ project so that people in the future who wanted to
do exactly what I'm attempting to do don't have to struggle as much as I
have had to.
Here's what I am currently running into. My table looks like this:
CREATE TABLE dns_records (
oid SERIAL PRIMARY KEY NOT NULL,
zone VARCHAR,
ttl INTEGER,
recordtype VARCHAR,
host VARCHAR,
mx_priority INTEGER,
data VARCHAR,
primary_ns VARCHAR,
resp_person VARCHAR,
serial INTEGER,
refresh INTEGER,
retry INTEGER,
expire INTEGER,
minimum INTEGER
);
My queries are directly out of the documentation, but I'll post them
anyway just to make sure I didn't fat-finger something.
dlz "postgres zone" {
database "postgres 2
{host=localhost port=5432 dbname=dns_test user=dns password=xxxxx}
{select zone from dns_records where zone = '%zone%'}
{select ttl, recordtype, mx_priority, case when lower(recordtype)='txt' then '\"' || data
|| '\"' else data end from dns_records where zone = '%zone%' and host = '%record%'
and not (recordtype = 'SOA' or recordtype = 'NS')}
{select ttl, recordtype, mx_priority, data, resp_person, serial, refresh, retry, expire,
minimum from dns_records where zone = '%zone%' and (recordtype = 'SOA' or recordtype='NS')}
{select ttl, recordtype, host, mx_priority, data, resp_person, serial, refresh, retry, expire,
minimum from dns_records where zone = '%zone%'}
{select zone from xfr_table where zone = '%zone%' and client = '%client%'}";
};
The data format is going to be ugly, so I'll do it as CSV:
oid,zone,ttl,recordtype,host,mx_priority,data,primary_ns,resp_person,serial,refresh,retry,expire,minimum
1,example.com,300,SOA,,,,ns1.example.com,dnsadmin.example.com,2008011701,8600,8600,8600,8600
2,example.com,300,NS,@,,ns1.example.com,,,,,,,
3,example.com,300,NS,@,,ns2.example.com,,,,,,,
4,example.com,300,MX,@,10,mail1.example.com,,,,,,,
5,example.com,300,MX,@,20,mail2.example.com,,,,,,,
6,example.com,300,A,machine1,,10.0.0.1,,,,,,,
7,example.com,300,A,machine2,,10.0.0.2,,,,,,,
8,0.0.10.in-addr.arpa,300,PTR,1,,machine1.example.com.,,,,,,,
9,0.0.10.in-addr.arpa,300,PTR,2,,machine2.example.com.,,,,,,,
10,example.com,300,CNAME,www,,machine1,,,,,,,
11,example.com,300,CNAME,ftp,,machine2,,,,,,,
As far as I can tell, this is good data, maybe that's part of the
problem? Do I have my data wrong and that's what's causing my problems?
If I do a simple A record lookup, I get results:
$ dig @localhost A www.example.com
;; ANSWER SECTION:
www.example.com. 300 IN CNAME machine1.example.com.
machine1.example.com. 300 IN A 10.0.0.1
However, this is the last thing in the log file:
21-Jan-2008 08:24:59.787 984 query is 'select ttl, recordtype, mx_priority, data, resp_person, serial, refresh, retry, expire,
minimum from dns_records where zone = 'example.com' and (recordtype = 'SOA' or recordtype='NS')'
21-Jan-2008 08:24:59.787 984 executing query for 0 time
21-Jan-2008 08:24:59.788 984 rs ok
21-Jan-2008 08:24:59.788 984 cleaning up
21-Jan-2008 08:24:59.788 984 unlocking mutex
21-Jan-2008 08:24:59.789 984 returning
21-Jan-2008 08:24:59.789 dns_rdata_fromtext: buffer-0xb7a75ee4:1: near eof: unexpected end of input
21-Jan-2008 08:24:59.789 dns_sdlz_putrr returned error. Error code was: unexpected end of input
That query returns these results:
dns_test=# select ttl, recordtype, mx_priority, data, resp_person, serial, refresh, retry, expire,
dns_test-# minimum from dns_records where zone = 'example.com' and (recordtype = 'SOA' or recordtype='NS');
ttl | recordtype | mx_priority | data | resp_person | serial | refresh | retry | expire | minimum
-----+------------+-------------+-----------------+----------------------+------------+---------+-------+--------+---------
300 | SOA | | | dnsadmin.example.com | 2008011701 | 8600 | 8600 | 8600 | 8600
300 | NS | | ns1.example.com | | | | | |
300 | NS | | ns2.example.com | | | | | |
(3 rows)
Is that not correct? That certainly looks right to me.
I don't get NS record info back with my queries, so the fact that it's
failing that last query makes sense that I don't get the data related to
that. But what is wrong with that query that it doesn't like?
If I try to lookup an MX record, I get the same error on the same query.
A side question to that is why does that query return mx_priority if
it's looking at only SOA and NS, neither of which would ever have that
data?
Since I've taken the queries from the documentation, I can only assume
I've gotten my data wrong somehow.
Please help, this is extremely frustrating!!!
Thanks,
-brian