Install PowerDNS with LEMP+Poweradmin and PostgreSQL

Sample configuration and installation with two nameservers as classic master-slave replication model for public and custom DNS zones. Also this config and all services need to be refactored and re-deployed in Docker.

Host system is Debian 10 Buster with configured firewall and CIS recomendations.

2pcs VPS from cloud provider aeza.net with basic plan and simple configuration:

• 2GHz 1pcs vcpu
• 8GiB vram
• 20GiB vda
• 1 NIC with public IPv4

Got parent ISP or Domain registrator sample records in zone:

Type	IP address	Target
A	10.10.10.1	ns1.yourdomain.com
A	20.10.10.1	ns2.yourdomain.com

Install PostgreSQL

Got official documentation of installation from .deb packages published in repository

Create file for postgresql repository configuration

# sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'

Got public key signing repo and add to keyring

# wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

Update cache and install software

# apt-get update && apt-get -y install postgresql

Enable startup of postgresql daemon via systemd

# systemctl enable postgresql

Connect to postgresql cluster as postgres user

# su - postgres
# psql

Create database and user, grant acces to role

> create user powerdns_admin with password 'strong_password';
> create database powerdns_database;
> grant all priveleges on *.powerdns_database to powerdns_admin;

Upload table scheme to server and apply on blank database

Download pgsql_pdns.sql file

# wget -O /tmp/pgsql_pdns.sql "https://forge.avallon.pw/avallon.labs/powerdns-init/raw/branch/master/modules/pgsql_pdns.sql"

Create scheme and apply to powerdns_database

# psql -U powerdns_admin -P powerdns_database < /tmp/pgsql_pdns.sql

Install PowerDNS

Take advantage roll up with official repository

Create the file pdns.list with version 4.8.X (stable) content:

# echo 'deb [arch=amd64] http://repo.powerdns.com/debian buster-auth-48 main' | tee -a /etc/apt/sources.list.d/pdns.list

Make config to apt use this repo by default, create file pdns:

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

Add repository public key to keyring and install packages:

# curl https://repo.powerdns.com/CBC8B383-pub.asc | apt-key add - &&

Update cache and install software from repo:

# apt-get update && apt-get install pdns-server pdns-backend-pgsql -y

Setup for pgsql backend in documentation

Make basic config in /etc/powerdns/pdns.conf add launcher gpgsql:

#################################
# launch  Which backends to launch and order to query them in
#
# launch=
launch=gpgsql

Create /etc/powerdns/pdns.d/gpgsql-backend.conf with parameters:

Now we use connection to postgresql main cluster on localhost, so use unix-socket as connection.

* /var/run/postgresql -- runtime directory with pgsql socket location;
* powerdns_admin -- example name for special account in pgsql;
* powerdns_database -- example name for db;
* strong_password -- example password string;

gpgsql-backend.conf

---

################################
# postgresql backend connection
##

# Host (ip address) to connect to. If pgsql-host begins with a slash, it specifies Unix-domain communication rather than TCP/IP communication; the value is the name of the directory in which the socket file is stored. Default: not set.
gpgsql-host=/var/run/postgresql

# User to connect as. Default: not set.
gpgsql-user=`powerdns_admin`

# The password to for gpgsql-user. Default: not set.
gpgsql-password=`strong_password`

# The port to connect to on gpgsql-host. Default: not set.
# gpgsql-port=

# Name of the database to connect to. Default: not set.
gpgsql-dbname=`powerdns_database`

# Enable DNSSEC processing for this backend. Default: no.
# gpgsql-dnssec=

# Extra connection parameters to forward to postgres. If you want to pin a specific certificate for the connection you should set this to sslmode=verify-full sslrootcert=<path-to-CA-cert>. Accepted parameters are documented in the PostgreSQL documentation. Default: "".
# gpgsql-extra-connection-parameters

# Prepare statements for better performance, instead of sending parameterized queries. Might not work with connection poolers. Default: yes.
# gpgsql-prepared-statements

Modify config pg_hba.conf add access rules to powerdns_admin:

...
## add line before local contex for all connection string
local 		powerdns_database		powerdns_admin				scram-sha-256
...

Reload postgresql configuration

# pg_ctlcluster 15 main reload

Install LEMP stack

Take config of NGINX official linux packages from repository for mainline branch of nginx

Install the prerequisites:

# apt install curl gnupg2 ca-certificates lsb-release debian-archive-keyring -y

Import an official nginx signing key so apt could verify the packages authenticity. Fetch the key:

# curl https://nginx.org/keys/nginx_signing.key | gpg --dearmor \
    | tee /usr/share/keyrings/nginx-archive-keyring.gpg >/dev/null

Verify that the downloaded file contains the proper key:

# gpg --dry-run --quiet --no-keyring --import --import-options import-show /usr/share/keyrings/nginx-archive-keyring.gpg

The output should contain the full fingerprint 573BFD6B3D8FBC641079A6ABABF5BD827BD9BF62 as follows:

pub   rsa2048 2011-08-19 [SC] [expires: 2024-06-14]
      573BFD6B3D8FBC641079A6ABABF5BD827BD9BF62
uid                      nginx signing key <signing-key@nginx.com>

If the fingerprint is different, remove the file.

For obtain mainline nginx packages, run the following command:

# echo "deb [signed-by=/usr/share/keyrings/nginx-archive-keyring.gpg] \
http://nginx.org/packages/mainline/debian `lsb_release -cs` nginx" \
    | tee /etc/apt/sources.list.d/nginx.list

Set up repository pinning to prefer our packages over distribution-provided ones:

# echo -e "Package: *\nPin: origin nginx.org\nPin: release o=nginx\nPin-Priority: 900\n" \
    | tee /etc/apt/preferences.d/99nginx

To install nginx, run the following commands:

# apt update && apt install nginx

Configuration with SSL block for site nginx

Make basic generation in Mozilla SSL Configurator

Obtain LE certificates

Install certbot and make request

# apt update && apt-get install python3-certbot-nginx certbot -y

Install PHP

As requests from official documentation on github poweradmin latest version need an php-8.1 and some modules:

• PHP 8.1
• PHP intl extension
• PHP gettext extension
• PHP openssl extension
• PHP pdo extension
• PHP pdo-mysql, pdo-pgsql or pdo-sqlite extension
• PHP ldap extension (optional)
• MySQL/MariaDB, PostgreSQL or SQLite database
• PowerDNS authoritative server 4.0.0+

Make an installation of all needed:

# apt update && apt-get install -y php8.1-{cli,common,fpm,gettext,intl,ldap,mbstring,opcache,pgsql,readline,xml}

Create the basic configuration file:

# touch /var/www/html/poweradmin/inc/config.inc.php

Give to the service account ownership of the directory:

# chown -R www-data: /var/www/html/poweradmin

Go to the web browser and visit web site with the URL or use IP address of server:

• https://ns1.yourdomain.com/poweradmin/install/