Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
Hello, On Sep 3, 2012, at 21:23 , Peter van Dijk wrote: >> Great tips from #postgresql: >> 1. ORDER BY col USING ~<~ - apparently undocumented, but it sorts the >> right way. >> >> 1) can use indexes that use the text_pattern_ops opclass > > Comparisons like < and > would then be replaced by ~<~ and ~>~ etcetera. > > "the obvious choice of index would be records (domain_id, ordername > text_pattern_ops)" > > (thanks to Andrew 'RhodiumToad' Gierth of #postgresql) I have now implemented this and it works perfectly. Kind regards, -- Peter van Dijk Netherlabs Computer Consulting BV - http://www.netherlabs.nl/ ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
Hello, On Sep 4, 2012, at 20:50 , Peter van Dijk wrote: >> 2. ALTER TABLE records ADD order name VARCHAR(255) BINARY >> Then you don't care about the CHARSET used by the server. >> This syntax always set the binary collation specific for that charset > > This is a good tip I did not know about. I will look into this. The downside > of VARBINARY is that queries also become case-sensitive, which is acceptable > for PowerDNS but makes debugging harder. Thanks! This works just as well as VARBINARY, indeed. Sadly, it does also make queries case-sensitive. If anyone has a suggestion that avoids case-sensitivity, please let us know. Otherwise we will go with VARCHAR BINARY. Thanks! Kind regards, -- Peter van Dijk Netherlabs Computer Consulting BV - http://www.netherlabs.nl/ ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
Hello Erkan, On Sep 4, 2012, at 11:34 , erkan yanar wrote: > On Mon, Sep 03, 2012 at 07:19:45PM +0200, Peter van Dijk wrote: >> OUR QUESTIONS: >> 1b. Is VARBINARY the best way to do it for MySQL? > > Afaik you want only to have the ordering (collation) to be binary. So you > have some ways to do it without touching the character set. > 1. SELECT .. ORDER BY BINARY > Just change the Query This, presumably, rules out index usage, which is bad. > 2. ALTER TABLE records ADD order name VARCHAR(255) BINARY > Then you don't care about the CHARSET used by the server. > This syntax always set the binary collation specific for that charset This is a good tip I did not know about. I will look into this. The downside of VARBINARY is that queries also become case-sensitive, which is acceptable for PowerDNS but makes debugging harder. Thanks! Kind regards, -- Peter van Dijk Netherlabs Computer Consulting BV - http://www.netherlabs.nl/ ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
On Mon, Sep 03, 2012 at 07:19:45PM +0200, Peter van Dijk wrote: > Hello, > > we are working hard to get 3.1.1 out the door, fixing the last remaining > DNSSEC issues. Since 3.1, we have discovered two issues that require some > re-engineering and may have database impact. We could really use some input > on these issues. > > > ISSUE 1: ordername sorting > [snip] > > MySQL, depending on charset settings (cannot reproduce right now), will also > not do the right thing for us. However, for MySQL there are a few reliable > workarounds: > ALTER TABLE records ADD order name VARCHAR(255) COLLATE latin1_bin; > or > ALTER TABLE records ADD order name VARBINARY(255); > > Both of these will make order name sort correctly - the first one applies > when latin1 is already active, the second one is generic. > > SQLite mostly seems to do the right thing, at least with default settings. > > OUR QUESTIONS: > 1b. Is VARBINARY the best way to do it for MySQL? Afaik you want only to have the ordering (collation) to be binary. So you have some ways to do it without touching the character set. 1. SELECT .. ORDER BY BINARY Just change the Query 2. ALTER TABLE records ADD order name VARCHAR(255) BINARY Then you don't care about the CHARSET used by the server. This syntax always set the binary collation specific for that charset Regards Erkan -- über den grenzen muß die freiheit wohl wolkenlos sein ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
Hello Peter, I removed some parts from your email to make it easier to read. > ISSUE 1: ordername sorting > > 1a. How do we tell Postgres to do "the right thing" for us, preferably > in a way that does not force all users to do a dump/restore? We > wouldn't mind an ALTER TABLE or the like! > 1b. Is VARBINARY the best way to do it for MySQL? > 1c. Should we cave in and encode ordername in some way that will sort > reliably, regardless of database settings? Base64 perhaps? This does > involve stretching ordername beyond 255 chars, which presumably is okay > with all common versions of PG, My and SQLite3. 1a. I don't use Postgres and I don't know enough about it to say anything about it. 1b. When it works and is fast it would sound like a good option for me. 1c. Encoding it also requires that it is decoded when it is requested? That sounds like extra processor/RAM usage and I'm not a fan of that. > ISSUE 2: non-empty terminals > > OUR QUESTIONS: > 2a. Do you think adding these records to the records table is sensible > at all? If not, how else would we do it? > 2b. Do you think type=NULL (SQL NULL) is an ugly hack? If so, what else > should we do? > 2c. If we accept type=NULL as an acceptable notation, should we still > have this extra field just to make cleanup easier? 2a. It sounds like they are missing, so adding them sounds like the best idea. 2b. Type=pdns sounds better to me (that will probably never be used in an RFC and makes it clear when reading the database that it is an internal record for PowerDNS. 2c. If type= says that it is automatically created/maintained I don't think the extra field will be necessary (at least when the right indexes are created/are there). Regards, Mark ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
Hello Seth, On Sep 3, 2012, at 19:54 , Seth Mattinen wrote: >> 1b. Is VARBINARY the best way to do it for MySQL? > > Yes. A similar issue was seen in sql-based bayes databases for > SpamAssassin tokens and the solution is to use BINARY for the token col > instead of CHAR. Generic fixes are best. Agreed. Always good to hear from experience :) >> 2b. Do you think type=NULL (SQL NULL) is an ugly hack? If so, what else >> should we do? > > If it's internal-only and handled automatically by rectify-zone it makes > sense to not have a type and that it would be null. External management > tools can be easily modified to ignore rows were type is null. Yes, agreed. >> 2c. If we accept type=NULL as an acceptable notation, should we still have >> this extra field just to make cleanup easier? > > A 'virt' flag wold be more future-proof if in the future virtual records > needed a type for some currently unforeseen reason. Management tools can > likewise ignore 'virt=true' records. If they have a type, they're not virtual - as far as I can tell for now. I also don't foresee many more changes to the fundamentals of DNS, after DNSSEC. We will ponder it some more... Kind regards, -- Peter van Dijk Netherlabs Computer Consulting BV - http://www.netherlabs.nl/ ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
Hello, On Sep 3, 2012, at 21:15 , Peter van Dijk wrote: > Great tips from #postgresql: > 1. ORDER BY col USING ~<~ - apparently undocumented, but it sorts the right > way. > > 1) can use indexes that use the text_pattern_ops opclass Comparisons like < and > would then be replaced by ~<~ and ~>~ etcetera. "the obvious choice of index would be records (domain_id, ordername text_pattern_ops)" (thanks to Andrew 'RhodiumToad' Gierth of #postgresql) Kind regards, -- Peter van Dijk Netherlabs Computer Consulting BV - http://www.netherlabs.nl/ ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
Hello,
On Sep 3, 2012, at 19:19 , Peter van Dijk wrote:
> OUR QUESTIONS:
> 1a. How do we tell Postgres to do "the right thing" for us, preferably in a
> way that does not force all users to do a dump/restore? We wouldn't mind an
> ALTER TABLE or the like!
> 1b. Is VARBINARY the best way to do it for MySQL?
> 1c. Should we cave in and encode ordername in some way that will sort
> reliably, regardless of database settings? Base64 perhaps? This does involve
> stretching ordername beyond 255 chars, which presumably is okay with all
> common versions of PG, My and SQLite3.
Great tips from #postgresql:
1. ORDER BY col USING ~<~ - apparently undocumented, but it sorts the right
way.
2. ORDER BY convert_to(col, current_setting('server_encoding')) - should also
work, I have not tested it
1) can use indexes that use the text_pattern_ops opclass
2) could work with a functional index
Thanks to mastermind (#powerdns) for pointing me to the right folks in
#postgresql. Keep those ideas coming!
Kind regards,
--
Peter van Dijk
Netherlabs Computer Consulting BV - http://www.netherlabs.nl/
___
Pdns-users mailing list
Pdns-users@mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
On 9/3/12 10:19 AM, Peter van Dijk wrote: > > ISSUE 1: ordername sorting > > > Passing '-l C' to createdb fixes this, but that would involve a dump/restore. > I also understand that with Postgres 9.1, there are ways to alter the > column's collation settings without a full dump/restore, but many users are > not running 9.1 yet. I have not managed to find a suitable way to emulate the > VARBINARY trick (below) that works for MySQL, in Postgres. > > MySQL, depending on charset settings (cannot reproduce right now), will also > not do the right thing for us. However, for MySQL there are a few reliable > workarounds: > ALTER TABLE records ADD order name VARCHAR(255) COLLATE latin1_bin; > or > ALTER TABLE records ADD order name VARBINARY(255); > > Both of these will make order name sort correctly - the first one applies > when latin1 is already active, the second one is generic. > > SQLite mostly seems to do the right thing, at least with default settings. > > OUR QUESTIONS: > 1a. How do we tell Postgres to do "the right thing" for us, preferably in a > way that does not force all users to do a dump/restore? We wouldn't mind an > ALTER TABLE or the like! > 1b. Is VARBINARY the best way to do it for MySQL? Yes. A similar issue was seen in sql-based bayes databases for SpamAssassin tokens and the solution is to use BINARY for the token col instead of CHAR. Generic fixes are best. > 1c. Should we cave in and encode ordername in some way that will sort > reliably, regardless of database settings? Base64 perhaps? This does involve > stretching ordername beyond 255 chars, which presumably is okay with all > common versions of PG, My and SQLite3. No, the database should be able to handle it properly. If it truly can't then mark the feature as unsupported for that one outlier. > > ISSUE 2: non-empty terminals > > If a zone contains a name like 'a.b.c.example.com' but no 'b.c.example.com' > or 'c.example.com', PowerDNS, when asked for b or b.c, will currently report > NXDOMAIN. This is relatively harmless. However, when running with NSEC3, > these NXDOMAINs can in fact translate to a.b.c.example.com becoming > unreachable with some resolvers. This is not a bug in those resolvers! For > correct NSEC3 operation, PowerDNS needs to pretend that b.c and c exist. > Other name servers, that store their names in a tree structure in memory, get > this for free. PowerDNS, when using SQL, does not. > > The most common proposal for fixing this is to add 'b.c.example.com' and > 'c.example.com' to the records table with type=NULL. This is in fact what the > oraclebackend (not the goraclebackend) already does. For gsql, rectify-zone > would automatically add (and, if necessary, remove) these records, if we go > down this path. > > Kees Monshouwer has sent me a patch that does this, while also adding a > 'virt' BOOL field indicating whether a record is real or "emulated" in this > sense. > > OUR QUESTIONS: > 2a. Do you think adding these records to the records table is sensible at > all? If not, how else would we do it? > 2b. Do you think type=NULL (SQL NULL) is an ugly hack? If so, what else > should we do? If it's internal-only and handled automatically by rectify-zone it makes sense to not have a type and that it would be null. External management tools can be easily modified to ignore rows were type is null. > 2c. If we accept type=NULL as an acceptable notation, should we still have > this extra field just to make cleanup easier? > A 'virt' flag wold be more future-proof if in the future virtual records needed a type for some currently unforeseen reason. Management tools can likewise ignore 'virt=true' records. ~Seth ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
Re: [Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
Hello, On Sep 3, 2012, at 19:19 , Peter van Dijk wrote: > ISSUE 2: non-empty terminals This heading should have said 'empty non-terminals'. I don't think I will ever learn. Apologies! Kind regards, -- Peter van Dijk Netherlabs Computer Consulting BV - http://www.netherlabs.nl/ ___ Pdns-users mailing list Pdns-users@mailman.powerdns.com http://mailman.powerdns.com/mailman/listinfo/pdns-users
[Pdns-users] PowerDNS needs your thoughts on two important DNSSEC matters
Hello,
we are working hard to get 3.1.1 out the door, fixing the last remaining DNSSEC
issues. Since 3.1, we have discovered two issues that require some
re-engineering and may have database impact. We could really use some input on
these issues.
ISSUE 1: ordername sorting
As you may know, when using NSEC (not NSEC3), PowerDNS converts records.name to
records.ordername, reversing the order of labels in the process (i.e.
'a.b.c.example.com' becomes 'c b a'). This is done so that the database can
find previous/next names for us quickly and easily, using an index.
However, it turns out that not all databases, in their default settings, sort
the underscore correctly:
Correct order, as demonstrated by ASCII values (Python):
>>> l=sorted(list('_abc*'))
>>> l
['*', '_', 'a', 'b', 'c']
>>> map(ord,l)
[42, 95, 97, 98, 99]
>>> sorted(['test sub', 'test www' ,'_underscore','very-long-txt'])
['_underscore', 'test sub', 'test www', 'very-long-txt']
What Postgres tends to do with default settings:
test sub
test www
_underscore
very-long-txt
Settings from psql -l:
Name| Owner | Encoding | Collation |Ctype| Access
privileges
---+--+--+-+-+---
pdnstest | vagrant | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
Passing '-l C' to createdb fixes this, but that would involve a dump/restore. I
also understand that with Postgres 9.1, there are ways to alter the column's
collation settings without a full dump/restore, but many users are not running
9.1 yet. I have not managed to find a suitable way to emulate the VARBINARY
trick (below) that works for MySQL, in Postgres.
MySQL, depending on charset settings (cannot reproduce right now), will also
not do the right thing for us. However, for MySQL there are a few reliable
workarounds:
ALTER TABLE records ADD order name VARCHAR(255) COLLATE latin1_bin;
or
ALTER TABLE records ADD order name VARBINARY(255);
Both of these will make order name sort correctly - the first one applies when
latin1 is already active, the second one is generic.
SQLite mostly seems to do the right thing, at least with default settings.
OUR QUESTIONS:
1a. How do we tell Postgres to do "the right thing" for us, preferably in a way
that does not force all users to do a dump/restore? We wouldn't mind an ALTER
TABLE or the like!
1b. Is VARBINARY the best way to do it for MySQL?
1c. Should we cave in and encode ordername in some way that will sort reliably,
regardless of database settings? Base64 perhaps? This does involve stretching
ordername beyond 255 chars, which presumably is okay with all common versions
of PG, My and SQLite3.
ISSUE 2: non-empty terminals
If a zone contains a name like 'a.b.c.example.com' but no 'b.c.example.com' or
'c.example.com', PowerDNS, when asked for b or b.c, will currently report
NXDOMAIN. This is relatively harmless. However, when running with NSEC3, these
NXDOMAINs can in fact translate to a.b.c.example.com becoming unreachable with
some resolvers. This is not a bug in those resolvers! For correct NSEC3
operation, PowerDNS needs to pretend that b.c and c exist. Other name servers,
that store their names in a tree structure in memory, get this for free.
PowerDNS, when using SQL, does not.
The most common proposal for fixing this is to add 'b.c.example.com' and
'c.example.com' to the records table with type=NULL. This is in fact what the
oraclebackend (not the goraclebackend) already does. For gsql, rectify-zone
would automatically add (and, if necessary, remove) these records, if we go
down this path.
Kees Monshouwer has sent me a patch that does this, while also adding a 'virt'
BOOL field indicating whether a record is real or "emulated" in this sense.
OUR QUESTIONS:
2a. Do you think adding these records to the records table is sensible at all?
If not, how else would we do it?
2b. Do you think type=NULL (SQL NULL) is an ugly hack? If so, what else should
we do?
2c. If we accept type=NULL as an acceptable notation, should we still have this
extra field just to make cleanup easier?
Thank you for reading this far. Please let us know if you have -any- thoughts
on either of these subjects. Please also post if any of this is unclear to you,
we love to share knowledge. We depend on you!
Kind regards,
--
Peter van Dijk
Netherlabs Computer Consulting BV - http://www.netherlabs.nl/
___
Pdns-users mailing list
Pdns-users@mailman.powerdns.com
http://mailman.powerdns.com/mailman/listinfo/pdns-users
