Setting PowerDNS with MariaDB Replication
I’ve been wanting to change to PowerDNS for a little but havnt got around to it. After taking some PTO from work and buying a bunch of new Raspberry Pi’s i figured now is as good a time as any to build this out.
If you google this topic, there are loads of guides out there, all of them seem to be a rehash of the same source instructions which…doesnt work!
If you do follow those instructions you will get working DNS servers but you will NOT get working replication. At first glance it may look like it is but as soon as you try and push an update replication will fail. After several hours of hair pulling and googling i have managed to get this working!
This will be split into 4 parts, setup for both servers, setup for ns1 (master), setup for ns2 (slave) and finally the PowerDNS Admin page setup.
Both PowerDNS servers
Firstly, lets install MariaDB
apt-get install mariadb-server
Now lets run through some configuration for MariaDB before we start creating databases, tables and users:
Firstly, run through ‘mysql_secure_installation’ to set your root password etc
Then In:
/etc/mysql/mariadb.conf.d/50-server.conf
Add in the following, under the MariaDB heading:
# 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 MariaDB with
systemctl restart mariadb
Next, log into MariaDB as root with
mysql -u root -p
and add enter the following:
SET GLOBAL binlog_format = 'ROW';
CREATE DATABASE powerdns;
Then create the user:
GRANT ALL ON powerdns.* TO 'powerdns'@'localhost' \ IDENTIFIED BY '<SECRET_PASSWORD>';
FLUSH PRIVILEGES; USE powerdns;
Now we need to create the tables:
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 UNSIGNED 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);
and finally, quit:
quit;
Next we need to stop and disable the sytemd resolver running on machine.
systemctl disable systemd-resolved systemctl stop systemd-resolved
Now we need to install pdns, the mysql backend and also pdns-recursor if we are running both a recursive and Authoritive DNS server. If you are just running an Authoritive DNS server then dont add in pdns-recursor.
apt-get install pdns-server pdns-recursor pdns-backend-mysql
Once that lot is installed we can go about configuring it to use MariaDB for the backend.
First i’d suggest removing the current ‘bind’ config:
rm -rf /etc/powerdns/pdns.d/bind.conf
/etc/powerdns/pdns.d/pdns.local.gmysql.conf
Put in the database details you setup earlier, like this:
# SQL Configuration (MariaDB) # Launch gmysql backend launch=gmysql # gmysql parameters gmysql-host=localhost gmysql-port=3306 gmysql-dbname=powerdns gmysql-user=powerdns gmysql-password=pasword gmysql-dnssec=yes
Next lets rename the default config, we dont want to remove it as its a good resource for checking out diferent settings:
mv /etc/powerdns/pdns.conf /etc/powerdns/pdns.conf-orig
Now create a new pdns.conf with the following settings:
# PowerDNS configuration file # Replace ns1.example.com with your primary nameserver's hostname default-soa-name=ns1.example.com include-dir=/etc/powerdns/pdns.d launch= security-poll-suffix= setgid=pdns setuid=pdns # Starts the API service api=yes # Sets webserver ad API to listen on specific address webserver-address=192.168.0.70 # Allows access to the web server and api server to only these IP ranges. webserver-allow-from=192.168.0.0/24,172.16.22.0/24 # Sets the port for the webserver webserver-port=8081 # Enables the webserver webserver=yes # Replace <RANDOM_KEY> with a randomly generated key for API access api-key=stupid-random-api-KEY # Sets this server as the master server master=yes # Sets this server to NOT be a slave slave=no # Sets the serial of zones to default default-soa-edit=INCEPTION-INCREMENT # Defualt zone admin email default-soa-mail=admin.haynet.host # Only include the following 3 if you are running a recursive AND authoritive DNS server on the same machine local-ipv6= # Set the authoritive server to listen only on loopback interface local-address=127.0.0.1 # Sets the authoritive servers listening port. local-port=5300
# Only include the following 3 if you are running a recursive AND authoritive DNS server on the same machine local-ipv6= # Set the authoritive server to listen only on loopback interface local-address=127.0.0.1 # Sets the authoritive servers listening port. local-port=5300
systemctl restart pdns
It should restart fine, but likely wont be working properly yet.
Now we need to do the same for recursor.conf, rename it so we can reference it later if we need to:
mv /etc/powerdns/recursor.conf /etc/powerdns/recursor.conf-orig
Then create a new recursor.conf with the following entries:
# allow-from If set, only allow these comma separated netmasks to recurse allow-from=127.0.0.0/8, 10.0.0.0/8, 100.64.0.0/10, 169.254.0.0/16, 192.168.0.0/16, 172.16.0.0/12, ::1/128, fc00::/7, fe80::/10 # api-key Static pre-shared authentication key for access to the REST API api-key=testing123 # config-dir Location of configuration directory (recursor.conf) config-dir=/etc/powerdns # forward-zones Zones for which we forward queries, comma separated domain=ip pairs forward-zones=example.com=127.0.0.1:5300 # hint file If set, load root hints from this file hint-file=/usr/share/dns/root.hints # include-dir Include *.conf files from this directory include-dir=/etc/powerdns/recursor.d # local-address IP addresses to listen on, separated by spaces or commas. Also accepts ports. local-address=0.0.0.0 # lua-config-file More powerful configuration options lua-config-file=/etc/powerdns/recursor.lua # quiet Suppress logging of questions and answers quiet=yes # security-poll-suffix Domain name from which to query security update notifications # security-poll-suffix=secpoll.powerdns.com. security-poll-suffix= # setgid If set, change group id to this gid for more security setgid=pdns # setuid If set, change user id to this uid for more security setuid=pdns # webserver Start a webserver (for REST API) webserver=yes # webserver-address IP Address of webserver to listen on webserver-address=0.0.0.0 # webserver-allow-from Webserver access is only allowed from these subnets webserver-allow-from=192.168.0.0/24, 172.16.22.0/24 # webserver-password Password required for accessing the webserver # webserver-password= # webserver-port Port of webserver to listen on webserver-port=8082
Make sure you change the ‘example.com’ to the domain you are authoritive for. You can add extra lines underneath for me.
Restart pdns-recursor with:
systemctl restart pdns-recursor
and you should now be able to get replies from your DNS servers.
MariaDB Replication – Master only
These settings apaply to the master only.
In this file:
/etc/mysql/mariadb.conf.d/50-server.cnf
bind-address = 0.0.0.0 server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 max_binlog_size = 100M binlog_do_db = powerdns
Then restart MariaDB with:
systemctl restart mariadb
Next we need to add the slave user:
mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'pdns-secondary'@'<SECONDARY_SERVER_IP>' IDENTIFIED BY '<SECRET_PASSWORD>';
FLUSH PRIVILEGES;
You can see if its working by running:
show master status;
Which will give you something like this:
+------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 634 | powerdns | | +------------------+----------+--------------+------------------+ 1 row in set (0.01 sec)
The ‘File’ and ‘Position’ are important, we will come back to them shortly.
MariaDB Replication – Slave server
On the slave server we are again editing the same file:
/etc/mysql/mariadb.conf.d/50-server.cnf
This time, editing to look like this:
server-id=2 relay-log=slave-relay-bin relay-log-index=slave-relay-bin.index replicate-do-db=powerdns
If you are adding more serveres, you can bump the server-id on each one.
Now restart MariaDB with:
systemtctl restart mariadb
Now is the time we need those two details from the ‘show master status’ so it would be wise to run it again. Take note of the ‘File’ and the ‘Position’.
On the secondary, log into MariaDB.
mysql -u root -p
Enter the following, its all one line so copy it into notepad, edit it, then paste it into the MariaDB console.
change master to master_host='<PRIMARY_SERVER_IP>', master_user='pdns-secondary', master_password='<SECRET_PASSWORD>', master_log_file='mysql-bin.000001', master_log_pos=634;
start slave;
You can see the status of the slave with:
show slave status\G
It will show you an output a bit like this:
MariaDB [(none)]> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.253 Master_User: pdns-secondary Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 363730 Relay_Log_File: slave-relay-bin.000002 Relay_Log_Pos: 208937 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: powerdns Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 363730 Relay_Log_Space: 209246 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_SSL_Crl: Master_SSL_Crlpath: Using_Gtid: No Gtid_IO_Pos: Replicate_Do_Domain_Ids: Replicate_Ignore_Domain_Ids: Parallel_Mode: conservative SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it Slave_DDL_Groups: 0 Slave_Non_Transactional_Groups: 0 Slave_Transactional_Groups: 182 1 row in set (0.001 sec)
That is it for the PowerDNS config. You should now have 2 DNS servers in-sync which (if you chose) are both recursive and authoritive.
PowerDNS Admin setup
The final part of this is to setup PowerDNS Admin. I have opted to run this on a seperate server, so somethings like install mariadb you dont need to so if you are installing it on your NS1.
Firstly, install MariaDB:
apt-get install mariadb-server
Log into MariaDB and create the database and user:
mysql -u root -p CREATE DATABASE pdnsadmin CHARACTER SET utf8 COLLATE utf8_general_ci; GRANT ALL PRIVILEGES ON pdnsadmin.* TO 'pdnsadminuser'@'%' IDENTIFIED BY '<SECRET_PASSWORD>'; FLUSH PRIVILEGES; quit;
Now install the dependencies:
apt-get install python3-dev libmariadb-dev-compat libmariadb-dev python-mysqldb libsasl2-dev libffi-dev libldap2-dev libssl-dev libxml2-dev libxslt1-dev libxmlsec1-dev pkg-config virtualenv -y
Next we need to install ‘yarn’
curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add - echo "deb https://dl.yarnpkg.com/debian/ stable main" > /etc/apt/sources.list.d/yarn.list apt update && apt install yarn -y
Now we grab PowerDNS Admin:
wget https://github.com/ngoduykhanh/PowerDNS-Admin/archive/master.tar.gz
Create new directory for it:
mkdir /opt/web
tar -C /opt/web -xvf master.tar.gz
Rename the directory to make it a little cleaner:
mv /opt/web/PowerDNS-Admin-master /opt/web/powerdns-admin
Finally, change into that directory and setup the virtualenv.
virtualenv -p python3 flask
Now we activate that virtualenv:
. ./flask/bin/activate
and install the PowerDNS Admin requirements:
pip install -r requirements.txt
A quick note that it may look like the process has locked up on
"Collecting xmlsec>=0.6.0"
pip install python-dotenv
cp config_template.py config.py
and configure the databse with details you createed earlier in this section:
SQLA_DB_USER = 'pdnsadminuser' SQLA_DB_PASSWORD = '<SECRET_PASSWORD>' SQLA_DB_HOST = '127.0.0.1' SQLA_DB_NAME = 'pdnsadmin' SQLALCHEMY_TRACK_MODIFICATIONS = True
Now we need to create the DB Schema:
export FLASK_APP=app/__init__.py flask db upgrade
and initialise the DB.
flask db migrate -m "Init DB"
Now we need to generate the assets:
yarn install --pure-lockfile
and then build them:
flask assets build
Again this may take a little time.
Once that is done, you can check that the app runs without errors with:
./run.py
(flask) [email protected]:/opt/web/powerdns-admin# ./run.py * Serving Flask app "app" (lazy loading) * Environment: production WARNING: Do not use the development server in a production environment. Use a production WSGI server instead. * Debug mode: on [2019-10-29 20:44:01,051] [INFO] | * Running on http://127.0.0.1:9191/ (Press CTRL+C to quit) [2019-10-29 20:44:01,053] [INFO] | * Restarting with stat [2019-10-29 20:44:02,798] [WARNING] | * Debugger is active! [2019-10-29 20:44:02,799] [INFO] | * Debugger PIN: 355-347-408
You can stop it with CTRL + C.
Next we need to create the sytsemd service file:
Create the following file:
/etc/systemd/system/powerdns-admin.service
[Unit] Description=PowerDNS-Admin After=network.target [Service] User=root Group=root WorkingDirectory=/opt/web/powerdns-admin ExecStart=/opt/web/powerdns-admin/flask/bin/gunicorn --workers 2 --bind unix:/opt/web/powerdns-admin/powerdns-admin.sock app:app [Install] WantedBy=multi-user.target
Now, run a daemon-reload, enable and start:
systemctl daemon-reload systemctl start powerdns-admin systemctl enable powerdns-admin
A ‘systemctl status powerdns-admin’ should look like this:
● powerdns-admin.service - PowerDNS-Admin Loaded: loaded (/etc/systemd/system/powerdns-admin.service; enabled; vendor preset: enabled) Active: active (running) since Tue 2019-10-29 20:55:01 GMT; 21s ago Main PID: 5317 (gunicorn) Tasks: 3 (limit: 4915) Memory: 69.3M CGroup: /system.slice/powerdns-admin.service ├─5317 /opt/web/powerdns-admin/flask/bin/python3 /opt/web/powerdns-admin/flask/bin/gunicorn --workers 2 --bind unix:/opt/web/powerd ├─5320 /opt/web/powerdns-admin/flask/bin/python3 /opt/web/powerdns-admin/flask/bin/gunicorn --workers 2 --bind unix:/opt/web/powerd └─5321 /opt/web/powerdns-admin/flask/bin/python3 /opt/web/powerdns-admin/flask/bin/gunicorn --workers 2 --bind unix:/opt/web/powerd Oct 29 20:55:01 dnsadmin.haynet.host systemd[1]: Started PowerDNS-Admin. Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5317] [INFO] Starting gunicorn 19.7.1 Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5317] [INFO] Listening at: unix:/opt/web/powerdns-admin/powe Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5317] [INFO] Using worker: sync Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5320] [INFO] Booting worker with pid: 5320 Oct 29 20:55:02 dnsadmin.haynet.host gunicorn[5317]: [2019-10-29 20:55:02 +0000] [5321] [INFO] Booting worker with pid: 5321
The final part of the setup is to instll NGINX:
apt-get install nginx
Create a new config file here:
/etc/nginx/conf.d/powerdns-admin.conf
And add the following into it, remembering to change the domain name:
server { listen *:80; server_name <YOUR_DOMAIN_NAME>; index index.html index.htm index.php; root /opt/web/powerdns-admin; access_log /var/log/nginx/powerdns-admin.local.access.log combined; error_log /var/log/nginx/powerdns-admin.local.error.log; client_max_body_size 10m; client_body_buffer_size 128k; proxy_redirect off; proxy_connect_timeout 90; proxy_send_timeout 90; proxy_read_timeout 90; proxy_buffers 32 4k; proxy_buffer_size 8k; proxy_set_header Host $host; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_headers_hash_bucket_size 64; location ~ ^/static/ { include /etc/nginx/mime.types; root /opt/web/powerdns-admin/app; location ~* \.(jpg|jpeg|png|gif)
{
expires 365d;
}
location ~* ^.+.(css|js)
{
expires 7d;
}
}
location / {
proxy_pass http://unix:/opt/web/powerdns-admin/powerdns-admin.sock;
proxy_read_timeout 120;
proxy_connect_timeout 120;
proxy_redirect off;
}
}
Get NGINX to check the config is sane with:
nginx -t
Then reload NGINX
systemctl reload nginx
That should be it. You should now be able to hit your PowerDNS Admin interface by hostname.
All you need to do now is put in 3 details in PowerDNS Admin to connect to your NS1 via its API.
First create an account on PowerDNS Admin by going to:
http://yourdomain/register
Then login, you will get a box asking you for:
- PDNS API URL
- PDNS API KEYPDNS VERSION
Because i have PDNS listening on the outside interface, rather than the loopback adapter, i need to set the URL to the public IP of the master server.
The API key is set in the master’s PDNS.conf
The veresion i used was 4.1.6.
You can find the version with pdns –version on the master.
That should be it. You can now add domain via the interface and they get replicated out to slaves via MariaDB replication.