Of course we cannot always share details about our work with customers, but nevertheless it is nice to show our technical achievements and share some of our implemented solutions.
In a previous article (New authoritative DNS server setup with user interface in 2019 (a comparison)), I mentioned that I will write an article series about PowerDNS and this is the article first of that series. It will cover the basic installation of PowerDNS using a MySQL (or MariaDB) database as backend.
Why PowerDNS?
I have been happy with BIND over the last decade (and I still am), but in a larger environment with around 1000 domains and multiple administrators, BIND on the command line can be tricky. The more people are working in the zone files, the more errors can happen, which may affect the whole DNS server. That's why a user interface for less cli-aware users is necessary. And PowerDNS also comes with an API, so this allows (in theory) programmatic DNS changes, e.g. from a CI/CD script. See article New authoritative DNS server setup with user interface in 2019 (a comparison) for more information.
Why MySQL Backend?
My initial test installation was PowerDNS with the Bind backend. Which means PowerDNS is a management layer on top of classic Bind Zone files. However there is one major problem:
root@pdns:~# pdnsutil create-zone example.com ns1.example.com
Jan 23 16:06:56 [bindbackend] Done parsing domains, 0 rejected, 0 new, 0 removed
Creating empty zone 'example.com'
Domain 'example.com' was not created!
Yep, the domain/zone was not created. PowerDNS is not able to create the new zones on its own when using the Bind backend. However it is required that this command works for the user interface I chose (more on that in a follow-up article).
See https://github.com/PowerDNS/pdns/issues/5783 and https://github.com/PowerDNS/pdns/issues/6954 .
Why PowerDNS 4.1? 4.2 is available!
As of the current end of life statements in the official documentation (March 2019), 4.1 is still the currently supported release.
The currently supported release train of PowerDNS Authoritative Server is 4.1.
4.2 is available yes, but I'm not certain if this is still considered an early release. In my first tests I installed 4.2 which basically worked fine but the Opera DNS User Interface was not compatible with it. So I decided for 4.1.
PowerDNS is a DNS server (d'uh!) and DNS runs on udp/53 (and also on tcp/53). But in Ubuntu 18.04 something is already using that port. What could it be? systemd!
root@pdns:~# netstat -lntup | grep 53
tcp 0 0 127.0.0.53:53 0.0.0.0:* LISTEN 622/systemd-resolve
udp 0 0 127.0.0.53:53 0.0.0.0:* 622/systemd-resolve
Yep, systemd-resolved runs by default as local DNS resolver so we must get rid of it first.
root@pdns:~# systemctl stop systemd-resolved
root@pdns:~# systemctl disable systemd-resolved
root@pdns:~# systemctl mask systemd-resolved
You also need to fix your /etc/resolv.conf now because it is a symlink which still points to the systemd-resolved service:
root@pdns:~# ll /etc/resolv.conf
lrwxrwxrwx 1 root root 39 May 22 2018 /etc/resolv.conf -> ../run/systemd/resolve/stub-resolv.conf
Example /etc/resolv.conf with public resolvers:
root@pdns:~# cat <
nameserver 1.1.1.1
nameserver 8.8.8.8
EOF
As this HowTo is about PowerDNS 4.1, this package can be found in the official Ubuntu repositories. However it makes sense to use the dedicated repository directly from PowerDNS itself for faster upstream fixes.
root@pdns:~# echo "deb http://repo.powerdns.com/ubuntu bionic-auth-41 main" >> /etc/apt/sources.list.d/powerdns.list
root@pdns:~# cat <
Package: pdns-*
Pin: origin repo.powerdns.com
Pin-Priority: 600
EOF
root@pdns:~# curl -s https://repo.powerdns.com/FD380FBB-pub.asc | sudo apt-key add -
OK
As you can see I also pinned the pdns-* packages to use PowerDNS's repositories.
The installation of the core package (pdns-server) and the MySQL backend (pdns-backend-mysql) is quickly done:
root@pdns:~# apt-get update
root@pdns:~# apt-get install pdns-server pdns-backend-mysql
We could argue about good vs evil here but to PowerDNS it doesn't matter whether the MySQL backend is really a MySQL or a MariaDB. As we're on an Ubuntu 18.04 here, we could install either easily from the official repositories. I went for mysql-server here.
root@pdns:~# apt-get install mysql-server
root@pdns:~# mysql_secure_installation
Yep, don't forget to set MySQL root password, etc. using mysql_secure_installation!
Add the following InnoDB related configuration into /etc/mysql/mysql.conf.d/mysqld.cnf:
# InnoDB
innodb_log_file_size = 64M
default-storage-engine=INNODB
innodb_buffer_pool_size=1G
innodb_buffer_pool_instances = 2
innodb_autoinc_lock_mode = 2
innodb_doublewrite = 1
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 60
innodb_locks_unsafe_for_binlog = 1
innodb_stats_on_metadata = 0
transaction-isolation=READ-COMMITTED
Then restart MySQL:
root@pdns:~# service mysql restart
Create the database for PowerDNS and create an application user:
mysql> create database powerdns;
mysql> grant all on powerdns.* to 'pdns'@'localhost' identified by 'secret';
Next you need to load the PowerDNS schemas (the empty tables) into the database. Download the schema directly from the official PowerDNS repository on GitHub:
root@pdns:~# wget https://raw.githubusercontent.com/PowerDNS/pdns/rel/auth-4.1.x/modules/gmysqlbackend/schema.mysql.sql
Note: You have to make sure you are using the correct schema for PowerDNS 4.1! I made the mistake and loaded the 4.2 schema into the database on my test install. That will cause you headaches later... so verify this right now.
For completeness' sake, the schema file currently looks like this:
root@pdns:~# cat schema.mysql.sql
CREATE TABLE domains (
id INT AUTO_INCREMENT,
name VARCHAR(255) NOT NULL,
master VARCHAR(128) DEFAULT NULL,
last_check INT DEFAULT NULL,
type VARCHAR(6) NOT NULL,
notified_serial INT DEFAULT NULL,
account VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL,
PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';
CREATE UNIQUE INDEX name_index ON domains(name);
CREATE TABLE records (
id BIGINT AUTO_INCREMENT,
domain_id INT DEFAULT NULL,
name VARCHAR(255) DEFAULT NULL,
type VARCHAR(10) DEFAULT NULL,
content VARCHAR(64000) DEFAULT NULL,
ttl INT DEFAULT NULL,
prio INT DEFAULT NULL,
change_date INT DEFAULT NULL,
disabled TINYINT(1) DEFAULT 0,
ordername VARCHAR(255) BINARY DEFAULT NULL,
auth TINYINT(1) DEFAULT 1,
PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';
CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);
CREATE INDEX ordername ON records (ordername);
CREATE TABLE supermasters (
ip VARCHAR(64) NOT NULL,
nameserver VARCHAR(255) NOT NULL,
account VARCHAR(40) CHARACTER SET 'utf8' NOT NULL,
PRIMARY KEY (ip, nameserver)
) Engine=InnoDB CHARACTER SET 'latin1';
CREATE TABLE comments (
id INT AUTO_INCREMENT,
domain_id INT NOT NULL,
name VARCHAR(255) NOT NULL,
type VARCHAR(10) NOT NULL,
modified_at INT NOT NULL,
account VARCHAR(40) CHARACTER SET 'utf8' DEFAULT NULL,
comment TEXT CHARACTER SET 'utf8' NOT NULL,
PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';
CREATE INDEX comments_name_type_idx ON comments (name, type);
CREATE INDEX comments_order_idx ON comments (domain_id, modified_at);
CREATE TABLE domainmetadata (
id INT AUTO_INCREMENT,
domain_id INT NOT NULL,
kind VARCHAR(32),
content TEXT,
PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';
CREATE INDEX domainmetadata_idx ON domainmetadata (domain_id, kind);
CREATE TABLE cryptokeys (
id INT AUTO_INCREMENT,
domain_id INT NOT NULL,
flags INT NOT NULL,
active BOOL,
content TEXT,
PRIMARY KEY(id)
) Engine=InnoDB CHARACTER SET 'latin1';
CREATE INDEX domainidindex ON cryptokeys(domain_id);
CREATE TABLE tsigkeys (
id INT AUTO_INCREMENT,
name VARCHAR(255),
algorithm VARCHAR(50),
secret VARCHAR(255),
PRIMARY KEY (id)
) Engine=InnoDB CHARACTER SET 'latin1';
CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm);
Load the schema into the database:
root@pdns:~# mysql -u root -p powerdns < schema.mysql.sql
First the default configurations inside /etc/powerdns/pdns.d/ should be removed. Everything seems to be relating to a default bind backend.
root@pdns:~# rm /etc/powerdns/pdns.d/*
Now create a new config file for the MySQL backend /etc/powerdns/pdns.d/pdns.local.gmysql.conf:
root@pdns:~# cat /etc/powerdns/pdns.d/pdns.local.gmysql.conf
# MySQL Configuration file
launch=gmysql
gmysql-host=localhost
gmysql-dbname=powerdns
gmysql-user=pdns
gmysql-password=secret
Enter the MySQL credentials you've used before when you created the database.
Restart PowerDNS:
root@pdns:~# systemctl restart pdns
You are now able to create a zone and add some DNS records using the PowerDNS commands:
root@pdns:~# pdnsutil create-zone example.com
Creating empty zone 'example.com'
root@pdns:~# pdnsutil add-record example.com www A 192.168.100.100
New rrset:
www.example.com. IN A 3600 192.168.100.100
root@pdns:~# dig -t A www.example.com @localhost
; <<>> DiG 9.11.3-1ubuntu1.5-Ubuntu <<>> -t A www.example.com @localhost
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: NOERROR, id: 19205
;; flags: qr aa rd; QUERY: 1, ANSWER: 1, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available
;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1680
;; QUESTION SECTION:
;www.example.com. IN A
;; ANSWER SECTION:
www.example.com. 3600 IN A 192.168.100.100
;; Query time: 0 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Tue Mar 26 16:30:26 CET 2019
;; MSG SIZE rcvd: 60
Bravo, your first zone is up and can be resolved on the local PowerDNS.
By default PowerDNS starts the zone's serial from 1 and increases it by +1 every time the zone is told to increase the serial. For example:
root@pdns:~# pdnsutil increase-serial example.com
This will increase the serial of the zone example.com. If this is the first time increase-serial was called on that zone, it will now have a serial of 2.
However I'm more a fan of the serial in format of the current date YYYYMMDDNN, e.g. 2019032701 where the last two fields (NN) stand for the revision within the same day. From RFC1912:
The recommended syntax is YYYYMMDDnn (YYYY=year, MM=month, DD=day, nn=revision number. This won't overflow until the year 4294.
To tell PowerDNS it should use this format, adjust /etc/powerdns/pdns.conf and set the default-soa-edit to INCEPTION-INCREMENT:
#################################
# default-soa-edit Default SOA-EDIT value
#
# default-soa-edit=
default-soa-edit=INCEPTION-INCREMENT
Usually you only create and edit zones on the primary DNS server or DNS master. You may want to fix this in the config file, too:
#################################
# default-soa-name name to insert in the SOA record if none set in the backend
#
# default-soa-name=a.misconfigured.powerdns.server
default-soa-name=ns1.example.com
Same as for the primary server, you may want to set a default contact e-mail address:
#################################
# default-soa-mail mail address to insert in the SOA record if none set in the backend
#
# default-soa-mail=
default-soa-mail=dnsadmin.example.com
After saving pdns.conf, restart PowerDNS:
root@pdns:~# systemctl restart pdns
The next article in this series will cover a DNS Master-Slave replication using PowerDNS.