PDNS & MySQL: How to Setup for local DNS on Centos 7

Posted on 31 May 2019 in Tutorial

It’s fairly easy to setup and configure PowerDNS to get ready to use in your work environment. I’m gonna show step by step how to run it under PostgreSQL and MySQL. It’s gonna be a long post so i will divide post into two with different database apps i use.

Preparation

install mysql latest stable version, in this case i will using MySQL 5.7 because i heard pdns does not support version 8 ….. yet.

Enable mysql 5.7 respository with the following command

yum localinstall https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm

Now, install mysql as any other package using yum command

yum install mysql-community-server

Next step is installing powerdns recursor and authoritative using the following command

yum install epel-release yum-plugin-priorities &&
curl -o /etc/yum.repos.d/powerdns-rec-42.repo https://repo.powerdns.com/repo-files/centos-rec-42.repo &&
yum install pdns-recursor

yum install epel-release yum-plugin-priorities &&
curl -o /etc/yum.repos.d/powerdns-auth-42.repo https://repo.powerdns.com/repo-files/centos-auth-42.repo &&
yum install pdns

Setup

Now we must prepare mysql server to be ready to operate by secure install mysql server.

Start and enable automatically run on boot with the following command

systemctl enable mysqld
systemctl start mysqld

before we start secure install, grap the generated temporary password using this command;

grep 'temporary password' /var/log/mysqld.log

The output should look something like this

Now, run secure mysql installation with following command

mysql_secure_installation

Output should look something like this

mysql -u root -p

Create empty database and user called “pdns”on the MySQL server

CREATE DATABASE pdns;
CREATE USER 'pdns'@'localhost' IDENTIFIED BY '<your preferred password>';

grant the user all privileges to database pdns

GRANT ALL PRIVILEGES ON pdns.* TO 'pdns'@'localhost'
    IDENTIFIED BY '<your preferred password>';
FLUSH PRIVILEGES;
EXIT;

Now, copy paste database structure to your database server

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) DEFAULT NULL,
  PRIMARY KEY (id)
) Engine=InnoDB;

CREATE UNIQUE INDEX name_index ON domains(name);


CREATE TABLE records (
  id                    INT 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;

CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);
CREATE INDEX recordorder ON records (domain_id, ordername);


CREATE TABLE supermasters (
  ip                    VARCHAR(64) NOT NULL,
  nameserver            VARCHAR(255) NOT NULL,
  account               VARCHAR(40) NOT NULL,
  PRIMARY KEY (ip, nameserver)
) Engine=InnoDB;


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) NOT NULL,
  comment               VARCHAR(64000) NOT NULL,
  PRIMARY KEY (id)
) Engine=InnoDB;

CREATE INDEX comments_domain_id_idx ON comments (domain_id);
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;

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;

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;

CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm);

ALTER TABLE `records` ADD CONSTRAINT `records_ibfk_1` FOREIGN KEY (`domain_id`)
REFERENCES `domains` (`id`) ON DELETE CASCADE;

Or you can simply import this structure database to your database server

mysql -u root -p pdns < powerdns.sql

Or you can import from pdns schema by download powerdns backend schema using yum command

you need to downloand backend for you database with following command

yum install pdns-backend-mysql

import the schema from pdns-backend folder to your database

mysql -uroot -p pdns < /usr/share/doc/pdns-backend-mysql-4.2.0/schema.mysql.sql

Configuration

Open powerdns configuration file using nano or text editor you like

nano /etc/pdns/pdns.conf
vim /etc/pdns/pdns.conf

By default powerdns using bind as their backend. In this case, you need to put a comment tag “#” in front of “launch=bind” and copy paste the following command

launch=gmysql
gmysql-host=localhost
gmysql-user=pdns
gmysql-password=<pdnspassword you set on before>
gmysql-dbname=pdns

And don’t forget to set the following command

api=yes
api-key=<your preferred api key>
daemon=yes
default-soa-name=ns1.yourdomain.com
disable-axfr=no
disable-syslog=no
local-address=127.0.0.1
log-dns-details=yes
logging-facility=0
local-port=5300
webserver=yes
guardian=yes
setgid=pdns
setuid=pdns
webserver-address=127.0.0.1
webserver-allow-from=0.0.0.0/0,::/0
webserver-port=8081

Start and Enable powerdns service on startup

systemctl start pdns
systemctl enable pdns

Install PowerDNS Admin for GUI admin panel

This powerdns GUI will make your job easier as it help you to add new records in Graphical interface.

Prerequisite

Create database and user for powerdns-admin

CREATE DATABASE powerdnsadmin CHARACTER SET utf8 COLLATE utf8_general_ci;

Grant all permission to it’s user

GRANT ALL PRIVILEGES ON powerdnsadmin.* TO 'pdnsadminuser'@'%' IDENTIFIED BY 'p4ssw0rd';

Apply the configuration

FLUSH PRIVILEGES;

Installation

install epel and ius repositories

yum install epel-release
yum install https://centos7.iuscommunity.org/ius-release.rpm

Install Python 3.6 and tools

yum install python36u python36u-devel python36u-pip
pip3.6 install -U pip
pip install -U virtualenv
rm -f /usr/bin/python3 && ln -s /usr/bin/python3.6 /usr/bin/python3

Install required packages for building python libraries from requirements.txt file

yum install gcc mysql-community-devel openldap-devel xmlsec1-devel xmlsec1-openssl libtool-ltdl-devel

Install yarn to build asset files + Nodejs 10

curl -sL https://rpm.nodesource.com/setup_10.x | bash -
curl -sL https://dl.yarnpkg.com/rpm/yarn.repo -o /etc/yum.repos.d/yarn.repo
yum install yarn

Checkout source code and create virtualenv

git clone https://github.com/ngoduykhanh/PowerDNS-Admin.git /opt/web/powerdns-admin
cd /opt/web/powerdns-admin
virtualenv -p python3 flask

Activate your python3 environment and install libraries:

$ . ./flask/bin/activate
(flask) [[email protected] powerdns-admin] pip install python-dotenv
(flask) [[email protected] powerdns-admin] pip install -r requirements.txt

Note : Before running PowerDNS-Admin, make sure you have config.py available.
You can clone it from config_template.py and adjust the database connection string.

Once your config.py is ready. Create the database schema by running the following command

(flask) [[email protected] powerdns-admin] export FLASK_APP=app/__init__.py
(flask) [[email protected] powerdns-admin] flask db upgrade

Generate asset file

(flask) [[email protected] powerdns-admin] yarn install --pure-lockfile
(flask) [[email protected] powerdns-admin] flask assets build

Now you can run PowerDNS-Admin by following command

(flask) [[email protected] powerdns-admin] ./run.py

Open your web browser and access to

http://localhost:9191

to visit PowerDNS-Admin web interface. Register an user. The first user will be in Administrator role.

At the first time you login into the PDA UI, you will be redirected to setting page to configure the PDNS API information.

Note: For production environment, i would recommend you to run PowerDNS-Admin with gunicorn or uwsgi instead of flask’s built-in web server, take a look at WIKI page to see how to configure them.

For more detail information how to properly install powerdns-admin , you can check from this link

The Dummit

MySQL 5.7 : How to configure Replication (Master/Slave) Database on Centos 7

MySQL 8: How to reset root password on Centos 7