PowerDNS con replicación MySQL

En este artículo vamos a montar un servidor PowerDNS con backend MySQL, gracias a la replicación MySQL ya no hará falta configurar la replicación de zona clásica mediante AXFR.

Configuramos la replicación MySQL tal como se explicó en este artículo anterior

Creamos las tablas en el Master tal como se indica en la página de PowerDNS:
https://doc.powerdns.com/authoritative/backends/generic-mysql.html

CREATE DATABASE pdns;
USE pdns

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,
  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,
  published             BOOL DEFAULT 1,
  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);

ALTER TABLE records ADD CONSTRAINT `records_domain_id_ibfk` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE comments ADD CONSTRAINT `comments_domain_id_ibfk` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE domainmetadata ADD CONSTRAINT `domainmetadata_domain_id_ibfk` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE cryptokeys ADD CONSTRAINT `cryptokeys_domain_id_ibfk` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Creamos el usuario de acceso a la base de datos que utilizará PowerDNS:

CREATE USER pdns@'localhost' IDENTIFIED BY 'PASSWORD';
GRANT SELECT, INSERT, UPDATE, DELETE ON pdns.* to pdns@'localhost';
FLUSH PRIVILEGES;

Configuramos las use flags para compilar PowerDNS con soporte para MySQL:

echo "net-dns/pdns -debug -doc -geoip -ldap -libressl -lua-records -luajit -mydns mysql -postgres -protobuf -remote -sodium -sqlite -systemd -test -tinydns tools" > /etc/portage/package.use/pdns

Compilamos e instalamos PowerDNS:

emerge -av net-dns/pdns

Configuramos PowerDNS para que utilice MySQL como backend:
https://doc.powerdns.com/authoritative/guides/basic-database.html

Hacemos una copia del fichero de configuración en ambos servidores:

cp /etc/powerdns/pdns.conf /etc/powerdns/pdns.conf.ori

MASTER:

cat >/etc/powerdns/pdns.conf «EOL
launch=gmysql
gmysql-host=127.0.0.1
gmysql-dbname=pdns
gmysql-user=pdns
gmysql-password=PASSWORD
setgid=pdns
setuid=pdns

local-address=MASTER_IP
master=yes
default-soa-name=ns1.alfaexploit.com.
allow-axfr-ips=SLAVE_IP
also-notify=SLAVE_IP
EOL

SLAVE:

cat >/etc/powerdns/pdns.conf «EOL
launch=gmysql
gmysql-host=127.0.0.1
gmysql-dbname=pdns
gmysql-user=pdns
gmysql-password=PASSWORD
setgid=pdns
setuid=pdns

local-address=SLAVE_IP
slave=yes
default-soa-name=ns1.alfaexploit.com.
allow-notify-from=MASTER_IP/32
EOL

Arrancamos PowerDNS:

/etc/init.d/pdns start
rc-update add pdns default

Comprobamos que el servicio funciona, creamos una zona en el MASTER:

pdnsutil create-zone alfaexploit.com ns1.alfaexploit.com
pdnsutil add-record alfaexploit.com ns1 A MASTER_IP
pdnsutil add-record alfaexploit.com ns2 A SLAVE_IP
pdnsutil add-record alfaexploit.com test A 1.2.3.4

Realizamos algunas querys tanto al MASTER como al SLAVE:

dig @MASTER_IP test.alfaexploit.com +short

1.2.3.4

dig @SLAVE_IP test.alfaexploit.com+short

1.2.3.4

La mejor manera de editar la zona es mediante edit-zone ya que nos presentará un editor como si se tratase de una zona en txt:

pdnsutil edit-zone alfaexploit.com

; Warning - every name in this file is ABSOLUTE!  
$ORIGIN .  
alfaexploit.com 3600    IN      SOA     ns1.alfaexploit.com hostmaster.alfaexploit.com 1 10800 3600 604800 3600  
alfaexploit.com 3600    IN      NS      ns1.alfaexploit.com  
ns1.alfaexploit.com     3600    IN      A       MASTER_IP  
ns2.alfaexploit.com     3600    IN      A       SLAVE_IP  
test.alfaexploit.com    3600    IN      A       1.2.3.4