ITSA Blog

DNS management with Bind has traditionally been flat files and slave/master configurations. Bind also has a feature/extension called DLZ — dynamically loaded zones. This feature can be very useful when designing applications that use databases or directories for storage rather than having to design your application to address a filesystem to create resource records or zone files.

In this article, I will explain how to set this up for a configuration where there are thousands of name-based virtual hosts hosted on the same VIP/email infrastructure using the same resource record on a CentOS 5.X system using MySQL to store records. The Bind version is 9.6.0-P1.

The first step is to install any pre-requisites:

yum install openssl-devel mysql-devel openldap-devel unixODBC-devel gcc

Note that you’ll want to uninstall gcc after this process is complete to reduce the likelihood of an attacker compiling an exploit on this box if they were to gain unprivileged access.

Next, download and extract the Bind sources:

pushd /tmp/
curl -C - -L -O 'http://ftp.isc.org/isc/bind9/9.6.0-P1/bind-9.6.0-P1.tar.gz'
tar xzvf bind-9.6.0-P1.tar.gz
pushd bind-9.6.0-P1

If compiling on a 64 bit system, you might have to setup some variables so that the appropriate mysql libraries are found:

export CPPFLAGS="-I/usr/lib64/mysql $CPPFLAGS"
export LDFLAGS="-L/usr/lib64/mysql $LDFLAGS"
export LD_LIBRARY_PATH="/usr/lib64/mysql"


The next step is to run configure — this example uses mysql only:

./configure  \
  --prefix=/usr/local/bind  \
  --disable-openssl-version-check \
  --with-dlz-mysql=yes

Once successful with configure, the next step is to install:

make && sudo make install


Be sure to add a user and group, as well as setup some basic directories for data:

groupadd -r -g 25 named
useradd -r -u 25 -s /bin/nologin -d /usr/local/named -g named named
mkdir /var/cache/bind
chown named:named /var/cache/bind


Now that the easy part is finished, the next step is to setup MySQL to store the appropriate DNS records.

In this example, data is populated in MySQL via a stored procedure in SQL Server via a linked server to a MySQL master (ODBC). A python script then creates the necessary entries in the postfix database to allow mail routing to occur. One of the tables populated here is the postfix.domains table. This is simply a list of all domains that are hosted at this site. I take advantage of this to replicate only this table to each of my DNS servers running MySQL and Bind-DLZ locally. This explanation will help the reader understand the next portion where I setup tables and views and populate them with data.

The next step is to create the database which will store the records. I use a database called postfix since my setup is tightly coupled with email routing and replication from the email database. (Login to MySQL to perform the following actions or script as appropriate.)

mysql> create database postfix;


Next, I create a template of resource records that will apply to all zones hosted within MySQL. (Note that this is a site which hosts thousands of domains on the same VIP/email architecture.)

DROP TABLE IF EXISTS dns_values;
CREATE TABLE dns_values (
  host VARCHAR(255) DEFAULT '' NOT NULL,
  type ENUM('SOA','NS','MX','A','CNAME','TXT','HINFO','PTR') NOT NULL DEFAULT 'SOA',
  data VARCHAR(255),
  ttl INT(11) DEFAULT 300 NOT NULL,
  mx_priority VARCHAR(10),
  refresh INT(11) DEFAULT 0 NOT NULL,
  retry INT(11) DEFAULT 0 NOT NULL,
  expire INT(11) DEFAULT 0 NOT NULL,
  minimum INT(11) DEFAULT 0 NOT NULL,
  serial BIGINT(20) DEFAULT 0 NOT NULL,
  resp_person VARCHAR(255),
  primary_ns VARCHAR(255),
  key host_index (host),
  key type_index (type)
) ENGINE=MyISAM;

The next step is to populate the basic set:

mysql> LOCK TABLES `dns_values` WRITE;
/*!40000 ALTER TABLE `dns_values` DISABLE KEYS */;
INSERT INTO `dns_values` VALUES
('@','SOA','root.mail.example.com.',300,NULL,10800,900,604800,600,2009020401,'root.mail.example.com.','ns1.example.com.'),
('@','NS','ns1.example.com.',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('@','NS','ns2.example.com.',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('@','A','xxx.xxx.xxx.xxx',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('images','A','xxx.xxx.xxx.xxx',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('mail','A','xxx.xxx.xxx.xxx',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('*','A','xxx.xxx.xxx.xxx',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('imap','CNAME','mail.example.com.',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('smtp','CNAME','mail.example.com.',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('@','TXT','v=spf2.0/pra mx ip4:xxx.xxx.xxx.0/24 -all',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('@','TXT','v=spf1 mx ip4:xxx.xxx.xxx.0/24 -all',300,NULL,10800,900,604800,600,2009020401,NULL,NULL),
('@','MX','mail.example.com.',300,'10',10800,900,604800,600,2009020401,NULL,NULL),
('webmail','CNAME','mail.example.com.',300,NULL,10800,900,604800,600,2009020401,NULL,NULL);
/*!40000 ALTER TABLE `dns_values` ENABLE KEYS */;
UNLOCK TABLES;


Create the postfix.domains table:

mysql> CREATE TABLE domains (
  domain varchar(128) NOT NULL default '',
  PRIMARY KEY  (domain)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Go ahead and populate the domains table with some values. Note that I replicate data from another table but you can just as well enter any values manually.

mysql> insert into domains (domain) values('example.com');


The next step is to create a view that will combine the dns_values table with the domains table to present all records as one table:

mysql>CREATE VIEW dns_records AS
SELECT
  d.domain        as zone
  ,dv.host        as host
  ,dv.type        as type
  ,dv.data        as data
  ,dv.ttl         as ttl
  ,dv.mx_priority as mx_priority
  ,dv.refresh     as refresh
  ,dv.retry       as retry
  ,dv.expire      as expire
  ,dv.minimum     as minimum
  ,dv.serial      as serial
  ,dv.resp_person as resp_person
  ,dv.primary_ns  as primary_ns
FROM domains d, dns_values dv ;

Next, setup grants on MySQL to allow the user who is accessing MySQL from Bind access:

mysql> GRANT USAGE,SELECT ON postfix.* TO binddlz@localhost identified by 'trickypassword';
FLUSH PRIVILEGES;


I started with a pretty basic named.conf file:

key rndc {
  algorithm hmac-md5 ;
  secret "longsecret";
};

controls {
  inet 127.0.0.1 allow { localhost; } keys { rndc; };
};

include "/usr/local/bind/etc/named.conf.options";

// prime the server with knowledge of the root servers
zone "." {
  type hint;
  file "/usr/local/bind/etc/db.root";
};

// setup local zones
zone "localhost" {
  type master;
  file "/usr/local/bind/etc/db.local";
};

zone "127.in-addr.arpa" {
  type master;
  file "/usr/local/bind/etc/db.127";
};

zone "0.in-addr.arpa" {
  type master;
  file "/usr/local/bind/etc/db.0";
};

zone "255.in-addr.arpa" {
  type master;
  file "/usr/local/bind/etc/db.255";
};

include "/usr/local/bind/etc/named.custom.zones";
include "/usr/local/bind/etc/named.dlz.zones";

As far as named.conf.options, it is also pretty basic:

options {
  directory "/var/cache/bind";
  allow-transfer { xxx.xxx.xxx.xxx; xxx.xxx.xxx.xxx; };
  zone-statistics yes;
  statistics-file "/usr/local/bind/var/stats/named-stats.out";
  recursion no;
};

As you can see, I simply included the following configuration portion as named.dlz.zones.

dlz "mysql zone" {
  database "mysql
  {host=localhost dbname=postfix user=binddlz pass=trickypassword ssl=false}
  {select zone from dns_records where zone = '%zone%'}
  {select ttl, type, mx_priority, case
      when lower(type)='txt' then concat('\"', data, '\"')
      when lower(type) = 'soa' then concat_ws(' ', data, resp_person, serial, refresh, retry, expire, minimum)
    else data end from dns_records_view where zone = '%zone%' and host = '%record%'}";
};

Now start Bind with the following command and test:

/usr/local/bind/sbin/named -c /usr/local/bind/etc/named.conf -f -g -u named


Useful tips:
* do not include both ns and contact in SOA record, use only respo_contact in either data or resp_contact fields
* make sure you restart Bind every time you restart MySQL or you will lose your connection(s)
* if you would like to create the dns_records table without a view, simply use the dns_values table and add the zone as the first column