====== PowerDNS via Apache2 with PostgreSQL on Debian and PowerDNS-Admin ======

How to setup a [[https://www.powerdns.com/|PowerDNS]] Authoritative Server with [[https://github.com/ngoduykhanh/PowerDNS-Admin|PowerDNS-Admin]] as management web interface.

This guide expects an existing [[https://www.postgresql.org/|PostgreSQL]] 13 (should work on older versions) and [[https://httpd.apache.org/|Apach2]] installation on a [[https://www.debian.org/|Debian]] 10 server.

Used software:
  * Debian 10.6
  * PowerDNS Authoritative Server 4.3.1
  * PostgreSQL 13
  * Python 3.7.3
  * uWSGI 2.0.19.1
  * Apache 2.4.46

===== Install PowerDNS =====

==== Configure APT Repository ====

Create a file with the repositories of PowerDNS (see [[https://repo.powerdns.com/]]).
<code>
vim /etc/apt/sources.list.d/pdns.list
</code>

<code - pdns.list>
deb http://repo.powerdns.com/debian buster-rec-43 main
deb http://repo.powerdns.com/debian buster-auth-43 main
deb http://repo.powerdns.com/debian buster-dnsdist-15 main
</code>

Add the key for this repositories.
<code>
curl https://repo.powerdns.com/FD380FBB-pub.asc | sudo apt-key add -
</code>

Install PowerDNS Authoritative Server with PostgreSQL backend.
<code>
apt install pdns-backend-pgsql
</code>

==== Prepare PostgreSQL Database ====

Open psql.
<code>
su - postgres
psql
</code>

Create the user u_powerdns and the database db_powerdns. Change the example password with your own generated one (eg. [[https://pwgen.ch/]]).
<code sql>
CREATE USER u_powerdns WITH PASSWORD 'iC0iB9kQ5hR4oG5uW2nD2nV0gK6vN2eSoM2eI8kT0gA9rF2pS3wW7mO4sJ4aT5tN';
CREATE DATABASE db_powerdns OWNER u_powerdns;
\q
</code>

Edit pg_hba.conf to configure the connections for the new database.
<code>
vim /etc/postgresql/13/main/pg_hba.conf
</code>

Add following line to provide access to the database db_powerdns via unix socket.
<code - pg_hba.conf>
local   db_powerdns         u_powerdns                      peer
</code>

Reload the PostgreSQL cluster.
<code>
pg_ctlcluster 13 main reload
</code>

==== Import Schema ====

Get the database schema for the related PowerDNS Authoritative Server version from GitHub and import it.
<code>
su - postgres
wget https://raw.githubusercontent.com/PowerDNS/pdns/rel/auth-4.3.x/modules/gpgsqlbackend/schema.pgsql.sql
psql -U u_powerdns -f schema.pgsql.sql db_powerdns
</code>

==== Configure PowerDNS Authoritative Server ====

Edit the PowerDNS configuration file (you may want to create a backup before).
<code>
vim /etc/powerdns/pdns.conf
</code>

Replace the database password with the one you generated before. [[https://pwgen.ch/|Generate]] your own API key (used for PowerDNS-Admin). Change the other settings according to your needs.
<code ini pdns.conf>
# https://doc.powerdns.com/authoritative/settings.html
#webserver-address=127.0.0.1,::1

setuid=pdns
setgid=pdns

# https://doc.powerdns.com/authoritative/backends/generic-postgresql.html
launch=gpgsql
gpgsql-host=/var/run/postgresql
#gpgsql-port=
gpgsql-dbname=db_powerdns
gpgsql-user=u_powerdns
gpgsql-password=iC0iB9kQ5hR4oG5uW2nD2nV0gK6vN2eSoM2eI8kT0gA9rF2pS3wW7mO4sJ4aT5tN
gpgsql-dnssec=yes

loglevel=4
master=yes
slave=no
webserver=yes
api=yes
api-key=aF3kD4eJ0hB1uI1jV8vR2yC0eK8lP9mO
daemon=no
guardian=no
default-publish-cdnskey=1
default-publish-cds=2,4
webserver-port=8081
webserver-allow-from=127.0.0.1,::1
#allow-axfr-ips=0.0.0.0/0,::/0
#allow-notify-from=0.0.0.0/0,::/0
enable-lua-records=1
version-string=anonymous
default-soa-edit=INCEPTION-INCREMENT
</code>

===== Install PowerDNS-Admin =====

==== Prepare PostgreSQL Database ====

Open psql.
<code>
su - postgres
psql
</code>

Create the user u_powerdns and the database db_powerdns. Change the example password with your own generated one (eg. [[https://pwgen.ch/]]).
<code sql>
CREATE USER u_powerdnsadmin WITH PASSWORD 'hW2bD6rM7vK0vB8fN7dJ8iI1lH3eJ5eQiD7qE7kH6gT1rE9vV2gT2hH3vL2lD7jW';
CREATE DATABASE db_powerdnsadmin OWNER u_powerdnsadmin;
\q
</code>

Edit pg_hba.conf to configure the connections for the new database.
<code>
vim /etc/postgresql/13/main/pg_hba.conf
</code>

Add following line to provide access to the database db_powerdns via unix socket.
<code - pg_hba.conf>
local   db_powerdnsadmin    u_powerdnsadmin                 peer
</code>

Reload the PostgreSQL cluster.
<code>
pg_ctlcluster 13 main reload
</code>

==== Install NodeJS ====

Install NodeJS according to [[https://github.com/nodesource/distributions/blob/master/README.md]].

<code>
curl -fsSL https://deb.nodesource.com/setup_20.x | bash - &&\
apt install -y nodejs
</code>

==== Install Yarn ====

Install Yarn according to [[https://classic.yarnpkg.com/en/docs/install/#debian-stable]].
<code>
curl -sS https://dl.yarnpkg.com/debian/pubkey.gpg | sudo apt-key add -
echo "deb https://dl.yarnpkg.com/debian/ stable main" | sudo tee /etc/apt/sources.list.d/yarn.list
apt update
apt install yarn
</code>

==== Setup PowerDNS-Admin from GitHub ====

Create the local directories and clonde the GitHub repository of [[https://github.com/ngoduykhanh/PowerDNS-Admin/|PowerDNS-Admin]].
<code>
mkdir -p /opt/python/powerdns-admin/
git clone https://github.com/ngoduykhanh/PowerDNS-Admin.git /opt/python/powerdns-admin/app
cd /opt/python/powerdns-admin/app
</code>

Install the requirements.
<code>
apt install libmariadb-dev build-essential python3-dev libldap2-dev libsasl2-dev pkg-config libxmlsec1-dev
</code>

This requirements are expected by this python libraries:
  * python-ldap: build-essential python3-dev libldap2-dev libsasl2-dev slapd ldap-utils tox lcov valgrind
  * SQLAlchemy: mysqlclient, libmariadbclient-dev


Create the config file  /opt/python/powerdns-admin/app/configs/config.py.
<code>
vim /opt/python/powerdns-admin/app/configs/config.py
</code>

Generate new values for the variables SALT and SECRET_KEY (32 chars).

Generate a new salt.
<code bash>
/opt/python/powerdns-admin/venv/bin/python3 -c 'import bcrypt; print(bcrypt.gensalt().decode("utf-8"))'
</code>
Example.
<code bash>
# /opt/python/powerdns-admin/venv/bin/python3 -c 'import bcrypt; print(bcrypt.gensalt().decode("utf-8"))'
$2b$12$E0Dn1LmXonAUiCP8sM0htu
</code>

Generate a new secret key:
<code bash>
tr -dc _A-Z-a-z-0-9 </dev/urandom | head -c32; echo;
</code>
<code bash>
# tr -dc _A-Z-a-z-0-9 </dev/urandom | head -c32; echo;
iz7g4zpfvbnK_eb0lWZeFEuXn5UV93Yz
</code>

This config.py is based on /opt/python/powerdns-admin/app/powerdnsadmin/default_config.py.
Details about Unix domain connections of SQLAlchemy you can find in their documentation: [[https://docs.sqlalchemy.org/en/13/dialects/postgresql.html#unix-domain-connections|Documentation]]

<code python config.py>
import os
basedir = os.path.abspath(os.path.abspath(os.path.dirname(__file__)))

### BASIC APP CONFIG
SALT = "$2b$12$E0Dn1LmXonAUiCP8sM0htu"
SECRET_KEY = "iz7g4zpfvbnK_eb0lWZeFEuXn5UV93Yz"
BIND_ADDRESS = "0.0.0.0"
PORT = 9191
HSTS_ENABLED = False
OFFLINE_MODE = False

### DATABASE CONFIG
SQLA_DB_DRIVER = "postgresql" # mysql, postgresql
SQLA_DB_USER = "u_powerdnsadmin"
SQLA_DB_PASSWORD = ''
#SQLA_DB_HOST = '127.0.0.1'
SQLA_DB_HOST = ""
SQLA_DB_NAME = "db_powerdnsadmin"
SQLALCHEMY_TRACK_MODIFICATIONS = True

### DATABASE - MySQL
SQLALCHEMY_DATABASE_URI = f"{SQLA_DB_DRIVER}://{SQLA_DB_USER}:{SQLA_DB_PASSWORD}@{SQLA_DB_HOST}/{SQLA_DB_NAME}"

### DATABASE - SQLite
# SQLALCHEMY_DATABASE_URI = 'sqlite:///' + os.path.join(basedir, 'pdns.db')

# SAML Authnetication
SAML_ENABLED = False
SAML_ASSERTION_ENCRYPTED = True
</code>

Create and activate a venv for PowerDNS-Admin.
<code>
python3 -m venv /opt/python/powerdns-admin/venv
. /opt/python/powerdns-admin/venv/bin/activate
</code>

**VENV** Change to the required directory for the next steps.
<code>
cd /opt/python/powerdns-admin/app/
</code>

**VENV** Install all the requirements from pip.
<code>
pip install wheel
pip install -r /opt/python/powerdns-admin/app/requirements.txt
pip install psycopg2-binary
</code>

**VENV** Define the flask variables. 
<code bash>
export FLASK_CONF="/opt/python/powerdns-admin/app/configs/config.py"
export FLASK_APP="/opt/python/powerdns-admin/app/powerdnsadmin/__init__.py"
</code>

**VENV** Initialize the database.
<code>
flask db upgrade
</code>

**VENV** Install the JavaScript components.
<code>
yarn install --pure-lockfile
</code>

**VENV** Build webassets (JavaScript and CSS files).
<code>
flask assets build
</code>

**VENV** Quit the venv.
<code>
deactivate
</code>

Set the permissions.
<code>
chown -R www-data:www-data /opt/python/powerdns-admin/
</code>

==== Setup uWSGI ====

Create and set the permissions for the directory which uWSGI will use for the pid file and the unix sockets.

Create the uWSGI configuration directory.
<code>
mkdir /etc/uwsgi/
</code>

Create the uWSGI configuration file for the PowerDNS-Admin service.
<code>
vim /etc/uwsgi/powerdns-admin.ini
</code>

The number of processes shouldn't be higher than the numbers of CPUs in the system.
<code ini powerdns-admin.ini>
[uwsgi]
strict = true
vacuum = true
single-interpreter = true
die-on-term = true
need-app = true
uid = www-data
gid = www-data
uwsgi-socket = /run/uwsgi_%n/service.sock
wsgi-file = /opt/python/%n/app/wsgi.py
processes = 1
threads = 4
master = true
max-requests = 1000
auto-procname = true
procname-prefix-spaced = %n
venv = /opt/python/%n/venv/
buffer-size = 8192
;disable-logging = true
;log-4xx = true
;log-5xx = true
</code>

Create the start file for the application.
<code>
vim /opt/python/powerdns-admin/app/wsgi.py
</code>

<code python wsgi.py>
#!/usr/bin/env python3
import sys

APP_PATH = "/opt/python/powerdns-admin/app/"
CONFIG_PATH = f"{APP_PATH}configs/config.py"

sys.path.insert(0, APP_PATH)

from powerdnsadmin import create_app
application = create_app(CONFIG_PATH)
</code>

Create a service file for systemd.
<code>
vim /etc/systemd/system/uwsgi@powerdns-admin.service
</code>

<code service uwsgi@powerdns-admin.service>
[Unit]
Description=uWSGI service unit %i
After=syslog.target
After=postgresql.service

[Service]
User=www-data
Group=www-data
RuntimeDirectory=uwsgi_%i
PIDFile=/run/uwsgi_%i/service.pid
RemainAfterExit=yes
ExecStart=/usr/local/bin/uwsgi --ini /etc/uwsgi/%i.ini
ExecReload=/bin/kill -HUP $MAINPID
ExecStop=/bin/kill -INT $MAINPID
Restart=always
KillSignal=SIGQUIT
Type=notify
NotifyAccess=all
SuccessExitStatus=15 17 29 30
NoNewPrivileges=yes
LimitNOFILE=65536


[Install]
WantedBy=multi-user.target
</code>

Let systemd re-read the service files.
<code>
systemctl daemon-reload
</code>

Install uwsgi globally on the system (not in a venv).
<code>
python3 -m pip install uwsgi
</code>

Start PowerDNS-Admin and let it start on the system startup.
<code>
systemctl start uwsgi@powerdns-admin.service
systemctl enable uwsgi@powerdns-admin.service
</code>

Enable the uWSGI extension for mod_proxy.
<code>
a2enmod proxy_uwsgi
</code>

Setup a VHost configuration. Change the name, certificate and the Content-Security-Policy according to your needs.
<code>
/etc/apache2/sites-available/pdnsadmin.example.com.conf
</code>

<code apache pdnsadmin.example.com.conf>
<VirtualHost *:80>
    ServerName pdnsadmin.example.com
    ServerAdmin hostmaster@example.com

    Redirect / https://pdnsadmin.example.com/

    ErrorLog ${APACHE_LOG_DIR}/pdnsadmin.example.com-error.log
    LogLevel warn
    CustomLog ${APACHE_LOG_DIR}/pdnsadmin.example.com-access.log combined
</VirtualHost>

<IfModule mod_ssl.c>
<VirtualHost *:443>
    ServerName pdnsadmin.example.com
    ServerAdmin hostmaster@example.com

    DocumentRoot /opt/python/powerdns-admin/app/powerdnsadmin/

    <Directory "/opt/python/powerdns-admin/app/powerdnsadmin/static">
        AllowOverride None
        Require all granted
    </Directory>

    ProxyPass "/static/" "!"
    ProxyPass "/favicon.ico" "!"
    ProxyPass "/.well-known/" "!"
    ProxyPass "/" "unix:/run/uwsgi_powerdns-admin/service.sock|uwsgi://powerdns-admin/"

    ErrorLog ${APACHE_LOG_DIR}/pdnsadmin.example.com-error.log
    LogLevel warn
    CustomLog ${APACHE_LOG_DIR}/pdnsadmin.example.com-access.log combined

    SSLEngine on
    SSLCertificateFile    /etc/dehydrated/certs/example.com/fullchain.pem
    SSLCertificateKeyFile /etc/dehydrated/certs/example.com/privkey.pem

    <IfModule mod_headers.c>
        # enable HSTS
        Header always set Strict-Transport-Security "max-age=15768000"
        # allow frames, needed for settings page
        Header always set X-Frame-Options SAMEORIGIN
        # Content Security Policy (https://content-security-policy.com/)
        Header set Content-Security-Policy: "default-src 'self' *.example.com 'unsafe-inline' 'unsafe-eval'"
    </IfModule>
</VirtualHost>
</IfModule>
</code>

Enable the new VHost.
<code>
a2ensite pdnsadmin.example.com
</code>

==== PowerDNS-Admin Configuration via Browser ====

  * Open a browser and access this VHost (eg. [[https://pdnsadmin.example.com/]])
  * Create an account
  * Configure the PDNS API settings based on pdns.conf
    * Settings, PDNS
    * PDNS API URL: http://127.0.0.1:8081/
    * PDNS API KEY: aF3kD4eJ0hB1uI1jV8vR2yC0eK8lP9mO
    * PDNS VERSION: 4.3.1

==== Python Upgrade Notes  ====

If you upgrade Python, for example while you upgrade Debian 10 to Debian 11, you have to update the virtual environment.

If you forget this, you can't start the systemd unit an may see an error like this:
<code>
Oct 18 18:49:14 ns1.example.com uwsgi[731953]: /usr/local/bin/uwsgi: error while loading shared libraries: libpython3.7m.so.1.0: cannot open shared object file: No such file or directory
</code>

Upgrade your venv.
<code>
python3 -m venv --upgrade --upgrade-deps /opt/python/powerdns-admin/venv/
</code>

And start the Systemd unit again.
<code>
systemctl start uwsgi@powerdns-admin.service
</code>


===== Appendix =====

  * If you want to migrate from Bind to PowerDNS, you can find some notes here: [[os:linux:bind_to_powerdns|Bind to PowerDNS]]
  * [[os:linux:powerdns-admin_update|How to Update PowerDNS-Admin]]