Set up PowerDNS with MySQL backend on Ubuntu/Debian

debian-setup-powerdns-mysql-primary.sh

#!/bin/bash

set -eoux pipefail

PDNS_VERSION=4.6
PDNS_VERSION_STRING=46

# Remember to replace these IPs with your secondary nameserver ip, and ensure tcp and udp port 53 is accessible from this primary
PDNS_SECONDARY_IPS=1.2.3.4,4,3,2,1
PDNS_THIS_PRIMARY_HOSTNAME=$(hostname)


# Credentials are stored here during setup.

CREDFILE="/root/.credentials"
if [[ -f $CREDFILE ]]; then
  echo "Already configured. See $CREDFILE"
  exit 0
fi

touch $CREDFILE


echo "Updating packages..."
cat > /etc/apt/sources.list <<EOL
deb http://deb.debian.org/debian bullseye main
deb-src http://deb.debian.org/debian bullseye main
deb http://security.debian.org/debian-security bullseye-security main
deb-src http://security.debian.org/debian-security bullseye-security main
deb http://deb.debian.org/debian bullseye-updates main
deb-src http://deb.debian.org/debian bullseye-updates main
deb http://deb.debian.org/debian bullseye-backports main
deb-src http://deb.debian.org/debian bullseye-backports main
EOL

apt update -y
apt upgrade -y
apt install -y gpg mariadb-server
echo "done"

cat > /etc/apt/sources.list.d/pdns.list <<EOL
deb [arch=amd64] http://repo.powerdns.com/debian $(lsb_release -sc)-auth-$PDNS_VERSION_STRING main
EOL

cat > /etc/apt/preferences.d/pdns << EOL
Package: pdns-*
Pin: origin repo.powerdns.com
Pin-Priority: 600
EOL

wget -qO- https://repo.powerdns.com/FD380FBB-pub.asc | gpg --dearmor > /etc/apt/trusted.gpg.d/pdns.gpg

echo "Installing and configuring PowerDNS"
sudo apt update -y && apt install -y pdns-server pdns-backend-mysql

# Optional?
#echo "Disabling built-in resolver systemd-resolved"
#sudo systemctl stop systemd-resolved
#sudo systemctl disable systemd-resolved
#echo "nameserver 8.8.8.8" > /etc/resolv.conf


echo "Setting up local mysql server for pdns"
PDNS_DB=pdns
PDNS_USER=pdns_user
PDNS_PASS=$(openssl rand -hex 32)
ROOT_PASS=$(openssl rand -hex 32)

echo "Saving MySQL Server credentials to $CREDFILE"
cat >> $CREDFILE <<EOL

echo # MySQL Config
User: root
Pass: $ROOT_PASS

# PowerDNS Config
Database: $PDNS_DB
Username: $PDNS_USER
Password: $PDNS_PASS

EOL


# Create a tempoary conf file to store mysql config (avoid passing passwords to mysql command)
cat > /tmp/my.cnf <<EOF
[client]
user=root
password=$ROOT_PASS
EOF

# Create a temporary SQL file to run mysql_secure_installation unattended, and initialize database for pdns
cat > /tmp/init.sql <<EOF
# mysql_secure_installation
UPDATE mysql.user SET Password=PASSWORD('$ROOT_PASS') WHERE User='root';
DELETE FROM mysql.user WHERE User='';
DELETE FROM mysql.user WHERE User='root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db='test' OR Db='test\\_%';

CREATE DATABASE pdns;
CREATE USER 'pdns_user'@'%' identified by '$PDNS_PASS';
GRANT ALL PRIVILEGES ON pdns.* TO 'pdns_user'@'%' WITH GRANT OPTIONS;
FLUSH PRIVILEGES;
EOF

mysql --defaults-extra-file=/tmp/my.cnf --connect-expired-password </tmp/init.sql


cat > /tmp/my.cnf <<EOF
[client]
user=$PDNS_USER
password=$PDNS_PASS
EOF

mysql --defaults-extra-file=/tmp/my.cnf $PDNS_DB < /usr/share/pdns-backend-mysql/schema/schema.mysql.sql

echo "Done configuring database."


echo "Configuring PowerDNS.."
rm -f /etc/powerdns/pdns.d/bind.conf

cat > /etc/powerdns/pdns.d/pdns.local.gmysql.conf <<EOL
launch=gmysql
gmysql-host=127.0.0.1
gmysql-port=3306
gmysql-dbname=$PDNS_DB
gmysql-user=$PDNS_USER
gmysql-password=$PDNS_PASS
gmysql-dnssec=yes
EOL


PDNS_API_KEY=$(openssl rand -hex 64)
echo # PDNS API
API KEY: '$PDNS_API_KEY

' >> $CREDFILE

cat > /etc/powerdns/pdns.d/pdns.custom.conf <<EOF
api=yes
api-key=$PDNS_API_KEY
webserver-address=0.0.0.0
webserver-allow-from=0.0.0.0/0,::/0

primary=yes
allow-notify-from=127.0.0.1/8

allow-axfr-ips=$PDNS_SECONDARY_IPS
also-notify=$PDNS_SECONDARY_IPS
EOF

chown pdns: /etc/powerdns/pdns.d/*.conf
chmod 640 /etc/powerdns/pdns.d/*.conf

echo "Done. Restarting pdns.service"
systemctl restart pdns

# Cleanup
rm -f /tmp/my.cnf
rm -f /tmp/init.sql
unset ROOT_PASS

echo "Done. See $CREDFILE for credentials."

#ufw default deny
#ufw allow 22/tcp
#ufw allow 53/udp
#ufw allow 53/tcp
#ufw --force enable

debian-setup-powerdns-secondary-sqlite.sh

#!/bin/sh
# Set up PowerDNS 4.6 as Authoritive secondary with SQlite backend for a hidden primary.

# Ensure the $(hostname) of this secondary machine matches its fqdn. If not set the hostname:
# you@machine$ sudo hostnamectl set-hostname ns1.example.com
#

set -eoux pipefail

PDNS_VERSION=4.6
PDNS_VERSION_STRING=46
PDNS_PRIMARY_IP=${PDNS_PRIMARY_IP}
PDNS_THIS_SECONDARY_HOSTNAME=$(hostname)

cat > /etc/apt/sources.list <<EOL
deb http://deb.debian.org/debian bullseye main
deb-src http://deb.debian.org/debian bullseye main
deb http://security.debian.org/debian-security bullseye-security main
deb-src http://security.debian.org/debian-security bullseye-security main
deb http://deb.debian.org/debian bullseye-updates main
deb-src http://deb.debian.org/debian bullseye-updates main
deb http://deb.debian.org/debian bullseye-backports main
deb-src http://deb.debian.org/debian bullseye-backports main
EOL

apt update -y
apt upgrade -y
apt install -y gpg sqlite3

cat > /etc/apt/sources.list.d/pdns.list <<EOL
deb [arch=amd64] http://repo.powerdns.com/debian $(lsb_release -sc)-auth-$PDNS_VERSION_STRING main
EOL

cat > /etc/apt/preferences.d/pdns << EOL
Package: pdns-*
Pin: origin repo.powerdns.com
Pin-Priority: 600
EOL

wget -qO- https://repo.powerdns.com/FD380FBB-pub.asc | gpg --dearmor > /etc/apt/trusted.gpg.d/pdns.gpg

apt update -y
apt install -y pdns-backend-sqlite3

TMP_MIGRATION_FILE=/tmp/schema.sqlite3.sql
PDNS_DB_FILE=/data/pdns.db

wget https://raw.githubusercontent.com/PowerDNS/pdns/rel/auth-$PDNS_VERSION.x/modules/gsqlite3backend/schema.sqlite3.sql -O $TMP_MIGRATION_FILE

mkdir -p /data
cat $TMP_MIGRATION_FILE | sqlite3 $PDNS_DB_FILE

# Add primary
sqlite3 $PDNS_DB_FILE "INSERT INTO supermasters VALUES('${PDNS_PRIMARY_IP}', '${PDNS_THIS_SECONDARY_HOSTNAME}', '');"

# Wrap up
chown -R pdns:pdns /data

rm /etc/powerdns/pdns.d/bind.conf

cat > /etc/powerdns/pdns.d/pdns.custom.conf <<EOL
disable-axfr=yes
api=no
secondary=yes
autosecondary=yes
xfr-cycle-interval=15
allow-notify-from=$PDNS_PRIMARY_IP
setuid=pdns
setgid=pdns
launch=gsqlite3
gsqlite3-database=$PDNS_DB_FILE
EOL

systemctl enable pdns.service
systemctl start pdns.service

echo "Done"