PowerDNS + PowerAdmin Master-Slave

# Update repository & upgrade sistem
sudo apt update && sudo apt upgrade -y

# Install utility dasar
sudo apt install -y curl wget gnupg2 lsb-release \
  software-properties-common dnsutils net-tools \
  ca-certificates apt-transport-https ufw

# Nonaktifkan systemd-resolved agar Port 53 tidak bentrok
sudo systemctl stop systemd-resolved
sudo systemctl disable systemd-resolved
sudo rm -f /etc/resolv.conf

# Set DNS resolver independen
echo "nameserver 8.8.8.8" | sudo tee /etc/resolv.conf
echo "nameserver 1.1.1.1" | sudo tee -a /etc/resolv.conf

# Konfigurasi UFW Firewall
sudo ufw allow 22/tcp
sudo ufw allow 53/tcp
sudo ufw allow 53/udp
sudo ufw allow 80/tcp
sudo ufw allow 443/tcp
sudo ufw --force enable
sudo ufw status verbose

# Install MariaDB Server & Client
sudo apt install -y mariadb-server mariadb-client

# Pastikan service MariaDB berjalan
sudo systemctl enable --now mariadb

# Amankan instalasi MariaDB
sudo mysql_secure_installation

# Buat direktori keyring
sudo install -d -m 0755 /etc/apt/keyrings

# Download & simpan GPG Public Key PowerDNS
curl -fsSL https://repo.powerdns.com/FD380FBB-pub.asc | \
  sudo tee /etc/apt/keyrings/auth-51-pub.asc > /dev/null

# Tambahkan APT repository PowerDNS Auth 5.1 untuk Debian Trixie
echo "deb [signed-by=/etc/apt/keyrings/auth-51-pub.asc] http://repo.powerdns.com/debian trixie-auth-51 main" | \
  sudo tee /etc/apt/sources.list.d/pdns.list

# Set APT Preference Pinning
cat <<EOF | sudo tee /etc/apt/preferences.d/auth-51
Package: pdns-*
Pin: origin repo.powerdns.com
Pin-Priority: 600
EOF

# Update & Install PowerDNS dengan MySQL/MariaDB Backend
sudo apt update
sudo apt install -y pdns-server pdns-backend-mysql

# Hapus file konfigurasi backend BIND default
sudo rm -f /etc/powerdns/pdns.d/bind.conf

sudo mysql -u root -p

CREATE DATABASE powerdns CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- User khusus daemon PowerDNS
CREATE USER 'pdns'@'localhost' IDENTIFIED BY 'PasswordPdnsSangatKuat123!';
GRANT ALL PRIVILEGES ON powerdns.* TO 'pdns'@'localhost';

-- User khusus PowerAdmin Web UI
CREATE USER 'poweradmin'@'localhost' IDENTIFIED BY 'PasswordPoweradminSangatKuat123!';
GRANT ALL PRIVILEGES ON powerdns.* TO 'poweradmin'@'localhost';

FLUSH PRIVILEGES;
EXIT;

# Import schema SQL standar PowerDNS
sudo mysql -u pdns -p'PasswordPdnsSangatKuat123!' powerdns < /usr/share/pdns-backend-mysql/schema/schema.mysql.sql

# Verifikasi tabel tersimpan
sudo mysql -u pdns -p'PasswordPdnsSangatKuat123!' powerdns -e "SHOW TABLES;"

launch+=gmysql
gmysql-host=127.0.0.1
gmysql-port=3306
gmysql-dbname=powerdns
gmysql-user=pdns
gmysql-password=PasswordPdnsSangatKuat123!
gmysql-dnssec=yes

# Mode Primary/Master
primary=yes
secondary=no

# Izinkan AXFR Transfer ke IP Slave
allow-axfr-ips=202.157.103.1

# Kirim DNS NOTIFY otomatis ke IP Slave saat zona diperbarui
also-notify=202.157.103.1

# Binding Port & IP
local-address=0.0.0.0
local-port=53

# Integrasi API & Webserver Internal (untuk PowerAdmin)
api=yes
api-key=APIKeySangatRahasiaDanPanjang123!
webserver=yes
webserver-address=127.0.0.1
webserver-port=8081
webserver-allow-from=127.0.0.1

# Default Content SOA
default-soa-content=ns1.example.com hostmaster.example.com 0 10800 3600 604800 3600
include-dir=/etc/powerdns/pdns.d

sudo chown -R pdns:pdns /etc/powerdns/pdns.d/pdns.local.gmysql.conf
sudo chmod 640 /etc/powerdns/pdns.d/pdns.local.gmysql.conf
sudo systemctl restart pdns
sudo systemctl enable pdns
sudo systemctl status pdns

sudo mysql -u root -p -e "
CREATE DATABASE powerdns CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'pdns'@'localhost' IDENTIFIED BY 'PasswordPdnsSangatKuat123!';
GRANT ALL PRIVILEGES ON powerdns.* TO 'pdns'@'localhost';
FLUSH PRIVILEGES;"

# Import schema
sudo mysql -u pdns -p'PasswordPdnsSangatKuat123!' powerdns < /usr/share/pdns-backend-mysql/schema/schema.mysql.sql

launch+=gmysql
gmysql-host=127.0.0.1
gmysql-port=3306
gmysql-dbname=powerdns
gmysql-user=pdns
gmysql-password=PasswordPdnsSangatKuat123!
gmysql-dnssec=yes

# Mode Secondary/Slave
primary=no
secondary=yes

# Aktifkan Autosecondary (Supermaster Auto Provisioning)
autosecondary=yes

# Terbuka menerima NOTIFY dari IP Master
allow-notify-from=202.157.102.1

# Durasi cek rutin perubahan zona
xfr-cycle-interval=60

local-address=0.0.0.0
local-port=53
include-dir=/etc/powerdns/pdns.d

sudo mysql -u pdns -p'PasswordPdnsSangatKuat123!' powerdns -e "
INSERT INTO supermasters (ip, nameserver, account)
VALUES ('202.157.102.1', 'ns1.example.com', 'admin');"

sudo chown -R pdns:pdns /etc/powerdns/pdns.d/pdns.local.gmysql.conf
sudo chmod 640 /etc/powerdns/pdns.d/pdns.local.gmysql.conf
sudo systemctl restart pdns
sudo systemctl enable pdns
sudo systemctl status pdns

# Install Nginx & PHP FPM beserta ekstensi pendukung
sudo apt install -y nginx php-fpm php-cli php-mysql \
  php-intl php-mbstring php-xml php-curl php-gd php-zip unzip

# Cek socket PHP-FPM terpasang
ls -la /run/php/

cd /tmp
# Ambil rilis PowerAdmin resmi via GitHub API
PA_VER=$(curl -s https://api.github.com/repos/poweradmin/poweradmin/releases/latest | grep tag_name | cut -d '"' -f 4 | tr -d 'v')
wget https://github.com/poweradmin/poweradmin/archive/refs/tags/v${PA_VER}.zip
unzip v${PA_VER}.zip

sudo mkdir -p /var/www/poweradmin
sudo cp -r poweradmin-${PA_VER}/* /var/www/poweradmin/
sudo chown -R www-data:www-data /var/www/poweradmin
sudo chmod -R 755 /var/www/poweradmin

server {
    listen 80;
    listen [::]:80;
    server_name poweradmin.example.com;

    root /var/www/poweradmin;
    index index.php index.html;

    access_log /var/log/nginx/poweradmin.access.log;
    error_log  /var/log/nginx/poweradmin.error.log;

    # Security Headers
    add_header X-Frame-Options "SAMEORIGIN" always;
    add_header X-Content-Type-Options "nosniff" always;
    add_header X-XSS-Protection "1; mode=block" always;
    add_header Referrer-Policy "strict-origin-when-cross-origin" always;

    location / {
        try_files $uri $uri/ /index.php?$query_string;
    }

    location ~ \.php$ {
        include snippets/fastcgi-php.conf;
        # Menggunakan wildcard socket PHP Debian
        fastcgi_pass unix:/run/php/php-fpm.sock;
        fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
        include fastcgi_params;
    }

    location ~ /\. {
        deny all;
    }
}

# Buat symlink socket fpm jika bernama php8.x-fpm.sock
PHP_SOCK=$(ls /run/php/php*-fpm.sock 2>/dev/null | head -n 1)
if [ -n "$PHP_SOCK" ] && [ ! -e /run/php/php-fpm.sock ]; then
    sudo ln -s "$PHP_SOCK" /run/php/php-fpm.sock
fi

sudo ln -sf /etc/nginx/sites-available/poweradmin /etc/nginx/sites-enabled/
sudo nginx -t
sudo systemctl reload nginx

# Install Certbot Nginx Plugin
sudo apt install -y certbot python3-certbot-nginx

# Request sertifikat SSL & Otomatisasi HTTPS Redirect
sudo certbot --nginx -d poweradmin.example.com \
  --agree-tos -m [email protected] --redirect

# Test Auto Renewal Timer
sudo certbot renew --dry-run

sudo rm -rf /var/www/poweradmin/install

# Convert BIND9 config & zone files ke sintaks SQL PowerDNS
zone2sql --named-conf=/etc/bind/named.conf \
         --gmysql \
         --transactions \
         --delete-matching-domains \
         > /tmp/bind-import.sql

# Import kueri SQL ke Database MariaDB
mysql -u pdns -p'PasswordPdnsSangatKuat123!' powerdns < /tmp/bind-import.sql

# Set tipe zona yang diimport agar menjadi MASTER
mysql -u pdns -p'PasswordPdnsSangatKuat123!' powerdns -e \
  "UPDATE domains SET type='MASTER' WHERE type='NATIVE' OR type IS NULL;"

# Trigger NOTIFY ke Slave
sudo pdns_control notify *

# Kirim NOTIFY ke semua zone yang ada
mysql -u pdns -p'PasswordPdnsSangatKuat123!' powerdns -N -e "SELECT name FROM domains;" | while read zone; do
  echo "NOTIFY → $zone"
  pdns_control notify "$zone"
done

# Atau paksa semua sekaligus (jika didukung)
# pdns_control notify *

# Contoh satu zone (syntax PowerDNS 5.x)
pdnsutil zone create-secondary contoh.com 202.157.102.1
pdns_control retrieve contoh.com

# Script untuk banyak zone (siapkan /tmp/zones.txt dari NS1)
while read zone; do
  echo "=== $zone ==="
  pdnsutil zone create-secondary "$zone" 202.157.102.1
  pdns_control retrieve "$zone"
  sleep 1
done < /tmp/zones.txt

# Cek daftar zona terdaftar
mysql -u pdns -p'PasswordPdnsSangatKuat123!' powerdns -e "SELECT id, name, type FROM domains;"

# Test kueri DNS lokal ke Master
dig @202.157.102.1 example.com SOA +short
dig @202.157.102.1 example.com NS +short

# Cek apakah domain terbuat otomatis di Slave
mysql -u pdns -p'PasswordPdnsSangatKuat123!' powerdns -e "SELECT id, name, type, master FROM domains;"

# Test kueri DNS ke Slave
dig @202.157.103.1 example.com SOA +short

# Monitor log PowerDNS secara realtime
sudo journalctl -u pdns -f -n 100

# Monitor Nginx Error Log
sudo tail -f /var/log/nginx/poweradmin.error.log

0 2 * * * mysqldump -u pdns -p'PasswordPdnsSangatKuat123!' powerdns | gzip > /var/backups/powerdns-$(date +\%F).sql.gz