This document is about PowerDNS 4.X. If you have PowerDNS 3.X, please see the PowerDNS 3.X documentation

Generic SQL Backends

The generic SQL backends (like gmysql, gpgsql and godbc) are backends with easily configurable SQL statements, allowing you to graft PowerDNS on any SQL database of your choosing. Because all database schemas will be different, a generic backend is needed to cover all needs.

Warning: Host names and the MNAME of a SOA records are NEVER terminated with a '.' in PowerDNS storage! If a trailing '.' is present it will inevitably cause problems, problems that may be hard to debug.

Note: Since 4.0.0, a root zone or record should have a name of '.' (no quotes). This is the only exception to the 'no terminating dot in SQL storage' rule.

Basic functionality

All domains in the generic SQL backends have a 'type' field that describes the mode of operation.

Native operation

To add a domain, issue the following:

INSERT INTO domains (name, type) VALUES ('powerdns.com', 'NATIVE');

The records table can now be filled by with the domain_id set to the id of the domains table row just inserted.

Slave operation

These backends are fully slave capable. To become a slave of the 'example.com' domain, execute this:

INSERT INTO domains (name, master, type) VALUES ('example.com', '198.51.100.6', 'SLAVE');

And wait a while for PowerDNS to pick up the addition - which happens within one minute (this is determined by the slave-cycle-interval setting). There is no need to inform PowerDNS that a new domain was added. Typical output is:

Apr 09 13:34:29 All slave domains are fresh
Apr 09 13:35:29 1 slave domain needs checking
Apr 09 13:35:29 Domain powerdns.com is stale, master serial 1, our serial 0
Apr 09 13:35:30 [gPgSQLBackend] Connected to database
Apr 09 13:35:30 AXFR started for 'powerdns.com'
Apr 09 13:35:30 AXFR done for 'powerdns.com'
Apr 09 13:35:30 [gPgSQLBackend] Closing connection

From now on, PowerDNS is authoritative for the 'powerdns.com' zone and will respond accordingly for queries within that zone.

Periodically, PowerDNS schedules checks to see if domains are still fresh. The default slave-cycle-interval is 60 seconds, large installations may need to raise this value. Once a domain has been checked, it will not be checked before its SOA refresh timer has expired. Domains whose status is unknown get checked every 60 seconds by default.

PowerDNS has support for multiple masters per zone, separate master IP addresses by commas:

INSERT INTO domains (name, master, type) VALUES ('example.com', '198.51.100.6, 2001:0DB8:15:4AF::4', 'SLAVE');

Superslave operation

To configure a supermaster with IP address 203.0.113.53 which lists this installation as 'autoslave.example.com', issue the following:

INSERT INTO supermasters VALUES ('203.0.113.53', 'autoslave.example.com', 'internal');

From now on, valid notifies from 203.0.113.53 that list a NS record containing 'autoslave.example.com' will lead to the provisioning of a slave domain under the account 'internal'. See Supermaster for details.

Master operation

The generic SQL backend is fully master capable with automatic discovery of serial changes. Raising the serial number of a domain suffices to trigger PowerDNS to send out notifications. To configure a domain for master operation instead of the default native replication, issue:

INSERT INTO domains (name, type) VALUES ('powerdns.com', 'MASTER');

Make sure that the assigned id in the domains table matches the domain_id field in the records table!

Disabled data

PowerDNS understands the notion of disabled records. They are marked by setting "disabled" to 1 (for PostgreSQL: true). By extension, when the SOA record for a domain is disabled, the entire domain is considered to be disabled.

Effects: the record (or domain, respectively) will not be visible to DNS clients. The REST API will still see the record (or domain). Even if a domain is disabled, slaving still works. Slaving considers a disabled domain to have a serial of 0; this implies that a slaved domain will not stay disabled.

Autoserial

The autoserial functionality makes PowerDNS generate the SOA serial when the SOA serial set to 0 in the database. The serial in SOA responses is set to what's provided by zone-lastchange-query. By default, this is the highest value of the change_date field in the "records" table).

Handling DNSSEC signed zones

To enable DNSSEC processing, the backend-dnssec option must be set to 'yes'.

Rules for filling out DNSSEC fields

Two additional fields in the 'records' table are important: 'auth' and 'ordername'. These fields are set correctly on an incoming zone transfer, and also by running pdnsutil rectify-zone.

The 'auth' field should be set to '1' for data for which the zone itself is authoritative, which includes the SOA record and its own NS records.

The 'auth' field should be 0 however for NS records which are used for delegation, and also for any glue (A, AAAA) records present for this purpose. Do note that the DS record for a secure delegation should be authoritative!

The 'ordername' field needs to be filled out depending on the NSEC/NSEC3 mode. When running in NSEC3 'Narrow' mode, the ordername field is ignored and best left empty. In NSEC/NSEC3 mode, the ordername field should be NULL for any glue but filled in for all delegation NS records and all authoritative records. In NSEC3 opt-out mode, ordername is NULL for any glue and insecure delegation NS records, but filled in for secure delegation NS records and all authoritative records.

In 'NSEC' mode, it should contain the relative part of a domain name, in reverse order, with dots replaced by spaces. So 'www.uk.powerdnssec.org' in the 'powerdnssec.org' zone should have 'uk www' as its ordername.

In 'NSEC3' non-narrow mode, the ordername should contain a lowercase base32hex encoded representation of the salted & iterated hash of the full record name. pdnsutil hash-zone-record zone record can be used to calculate this hash.

In addition, PowerDNS fully supports empty non-terminals. If you have a zone example.com, and a host a.b.c.example.com in it, rectify-zone (and the AXFR client code) will insert b.c.example.com and c.example.com in the records table with type NULL (SQL NULL, not 'NULL'). Having these entries provides several benefits. We no longer reply NXDOMAIN for these shorter names (this was an RFC violation but not one that caused trouble). But more importantly, to do NSEC3 correctly, we need to be able to prove existence of these shorter names. The type=NULL records entry gives us a place to store the NSEC3 hash of these names.

If your frontend does not add empty non-terminal names to records, you will get DNSSEC replies of 3.1-quality, which has served many people well, but might lead to issues in the future.

Queries

From version 4.0.0 onward, the generic SQL backends use prepared statements for their queries. Before 4.0.0, queries were expanded using the C function 'snprintf' which implies that substitutions are performed on the basis of %-placeholders.

To see the default queries for a backend, run pdns_server --no-config --launch=BACKEND --config.

Regular Queries

For regular operation, several queries are used for record-lookup. These queries must return the following fields in order:

Please note that the names of the fields are not relevant, but the order is!

DNSSEC queries

These queries are used by e.g. pdnsutil rectify-zone. Make sure to read Rules for filling out fields in database backends if you wish to calculate ordername and auth without using pdns-rectify.

Domain and zone manipulation

Master/slave queries

These queries are used to manipulate the master/slave information in the database. Most installations will have zero need to change the following queries.

On masters

On slaves

On superslaves

TSIG

Comment queries

For listing/modifying comments.

Specifying queries

The queries above are specified in pdns.conf. For example, the basic-query for the Generic MySQL backend would appear as:

gmysql-basic-query=SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type=? and name=?

Queries can span multiple lines, like this:

gmysql-basic-query=SELECT content,ttl,prio,type,domain_id,disabled,name,auth \
FROM records WHERE disabled=0 and type=? and name=?