vernon.wenberg

There are several places where you will need to choose a custom value, such as password. Please keep that in mind.

apt update
apt upgrade -y

apt install mariadb-server mariadb-client -y

Set MySQL root password

mysql_secure_installation

Login to MySQL with root

mysql -u root -p

We will now create the powerdns table. Use the following commands within the MySQL console line by line.

CREATE DATABASE powerdns;
GRANT ALL ON powerdns.* TO 'powerdns'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
USE powerdns;
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);

Disable systemd-resolved

systemctl disable systemd-resolved
systemctl stop systemd-resolved

Replace resolv.conf

rm /etc/resolv.conf
echo "nameserver 8.8.8.8" > /etc/resolv.conf

apt install pdns-server pdns-backend-mysql virtualenv -y

Configure PowerDNS configuration file

nano /etc/powerdns/pdns.d/pdns.local.gmysql.conf

Replace the contents of the config file with

launch+=gmysql
gmysql-host=localhost
gmysql-port=3306
gmysql-dbname=powerdns
gmysql-user=powerdns
gmysql-password=password
gmysql-dnssec=yes

Restart PowerDNS

systemctl restart pdns

Verify that PowerDNS is running

systemctl status pdns

Install PowerDNS-Admin prerequisites

apt install python3-dev libmysqlclient-dev python-mysqldb libsasl2-dev libffi-dev libldap2-dev libssl-dev libxml2-dev libxslt1-dev libxmlsec1-dev pkg-config -y

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 -y
apt install  yarn -y

git clone https://github.com/ngoduykhanh/PowerDNS-Admin.git /opt/web/powerdns-admin
cd /opt/web/powerdns-admin
virtualenv -p python3 flask
. ./flask/bin/activate
pip install -r requirements.txt

Edit the PowerDNS-Admin config with your database info

mv config_template.py config.py
nano config.py

Use the following config ….

SQLA_DB_USER = 'powerdns'
SQLA_DB_PASSWORD = 'password'
SQLA_DB_HOST = 'localhost'
SQLA_DB_NAME = 'powerdns'
# MySQL
SQLALCHEMY_DATABASE_URI = 'mysql://'+SQLA_DB_USER+':'+SQLA_DB_PASSWORD+'@'+SQLA_DB_HOST+'/'+SQLA_DB_NAME
# SQLite
SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'pdns.db')

Create the database schema

export FLASK_APP=app/__init__.py
flask db upgrade
flask db migrate -m "Init DB"

Generate assets with yarn

yarn install --pure-lockfile
flask assets build

apt install nginx -y

Edit nginx configuration file. There are various methods to do this. I am assuming PowerDNS admin is the only page hosted on this host.

nano /etc/nginx/sites-available/default

Use the following config in your nginx site config …

server {
 listen *:80;
 server_name               example.com;
 
 index                     index.html index.htm index.php;
 root                      /opt/web/powerdns-admin;
 access_log                /var/log/nginx/powerdns.access.log combined;
 error_log                 /var/log/nginx/powerdns.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;
 }
}

Restart nginx

systemctl restart nginx

nano /etc/systemd/system/powerdns.service

Insert the following config in the new file …

[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

Enable the PowerDNS service

systemctl daemon-reload
systemctl start powerdns
systemctl enable powerdns