BIND/named with PostgreSQL back end at CentOS 6.4

I've been trying to find a good documentation on setting up Bind with PostgreSQL as its back end. So far the information is sparse so I've decided to put this recipe together. There is much more than this but the idea is to give you a start.

1) First, make sure you have bind, bind-sdb, postgresql-server RPMs (and their dependencies) installed.

2) After having the RPMs in your system, you have to configure the iptables to allow DNS queries. Edit /etc/sysconfig/iptables to allow queries to the DNS port. Include the following statement after the SSH entry:

-A INPUT -m state --state NEW -m udp -p udp --dport 53 -j ACCEPT

Make sure you reload your iptables for this change to get the new rules into effect.



3) Configure named to load the database support. Append the "ENABLE_SDB=yes" statement to /etc/sysconfig/named

echo "ENABLE_SDB=yes" >> /etc/sysconfig/named

4) The zone format to support PostgreSQL back end is the following:

zone "example.com." IN {        type master;
        database "pgsql  <db_name> <db_table> <db_host> <db_user> <db_password>";
};

Note: Make sure to create a database, user and password for BIND. In the following example we are using dbname=binddns, dbhost="localhost", dbuser="binduser", dbpass="bindpass".

For this example we are going to create a .lab domain and a reverse zone for a private IP. We created a "dom_lab" table to hold the .lab domain and a "rev_192_168_77" table to hold the reverse zone for 192.168.77.0/24.

We append the following information to /etc/named.conf

zone "lab." IN {
    type master;
    database "pgsql binddns dom_lab localhost binduser bindpass";
};

zone "77.168.192.in-addr.arpa." IN {
        type master;
        database "pgsql binddns rev_192_168_77 localhost binduser bindpass";
};


4) Setting up the PostgreSQL database and tables.

The bind schema only requires the following columns: name, ttl, rdtype, rdata. In our case we want to have additional columns for primary key (for easier update/delete transactions) and we want to keep track of when the last update to a record and the dbuser that did it.

So, the first part is to create the auto update function for our trigger. We load the plpgsql language and drop any predefined function with the same name.

CREATE LANGUAGE plpgsql;

DROP FUNCTION modtime() CASCADE;

CREATE FUNCTION modtime() RETURNS trigger AS $modtime$
    BEGIN
        -- Check that name,rdtype,rdata are given
        IF NEW.name IS NULL THEN
            RAISE EXCEPTION 'name cannot be null';
        END IF;
        IF NEW.rdtype IS NULL THEN
            RAISE EXCEPTION '% cannot have null record type', NEW.name;
        END IF;
        IF NEW.rdata IS NULL THEN
            RAISE EXCEPTION '% cannot have null data', NEW.name;
        END IF;

        -- Remember last update to table
        NEW.modtime := current_timestamp;
        NEW.moduser := current_user;
        RETURN NEW;
    END;
$modtime$ LANGUAGE plpgsql;

This functions assume that the table have a modtime and moduser column to update.

Lets create the .lab domain forwarding records. For this we create the table, insert some records and create the trigger to track updates.

drop table dom_lab;

create table dom_lab (
    id serial not null,
    name    TEXT not null,
    ttl     INTEGER not null DEFAULT '120',
    rdtype  TEXT not null,
    rdata   TEXT not null,
    modtime timestamp DEFAULT current_timestamp,
    moduser TEXT DEFAULT current_user,
    PRIMARY KEY(id)
);

INSERT INTO dom_lab (name, ttl, rdtype, rdata) values ('lab','3600','SOA','localhost. support.savantadvisors.com. 1 1d 2h 4w 1h');
INSERT INTO dom_lab (name, ttl, rdtype, rdata) values ('lab','3600','NS','dns1.lab.');
INSERT INTO dom_lab (name, ttl, rdtype, rdata) values ('lab','3600','NS','localhost.');
INSERT INTO dom_lab (name, ttl, rdtype, rdata) values ('dns1.lab','120','A','192.168.77.19');
INSERT INTO dom_lab (name, ttl, rdtype, rdata) values ('dns1.lab','120','A','192.168.77.162');
INSERT INTO dom_lab (name, ttl, rdtype, rdata) values ('ovirt.lab','120','A','192.168.77.19');
INSERT INTO dom_lab (name, ttl, rdtype, rdata) values ('lab','120', 'MX','5 mx.lab.');
INSERT INTO dom_lab (name, ttl, rdtype, rdata) values ('mx.lab','120', 'A','192.168.77.19');

CREATE TRIGGER dom_lab_timestamp BEFORE INSERT OR UPDATE ON dom_lab
    FOR EACH ROW EXECUTE PROCEDURE modtime();

Now lets create the table to hold the reverse zone for 192.168.77.0/24 network. For this we create the table, insert some values and create the trigger.

drop table rev_192_168_77;

create table rev_192_168_77 (
    id serial not null,
    name    TEXT not null,
    ttl     INTEGER not null DEFAULT '120',
    rdtype  TEXT not null,
    rdata   TEXT not null,
    modtime timestamp DEFAULT current_timestamp,
    moduser TEXT DEFAULT current_user,
    PRIMARY KEY(id)
);

INSERT INTO rev_192_168_77 (name, ttl, rdtype, rdata) values ('77.168.192.in-addr.arpa','3600','SOA','localhost. support.savantadvisors.com. 1 1d 2h 4w 1h');
INSERT INTO rev_192_168_77 (name, ttl, rdtype, rdata) values ('77.168.192.in-addr.arpa','3600','NS','localhost.');
INSERT INTO rev_192_168_77 (name, ttl, rdtype, rdata) values ('77.168.192.in-addr.arpa','3600','NS','dns1.lab.');
INSERT INTO rev_192_168_77 (name, ttl, rdtype, rdata) values ('19.77.168.192.in-addr.arpa','120','PTR','ovirt.lab');
INSERT INTO rev_192_168_77 (name, ttl, rdtype, rdata) values ('19.77.168.192.in-addr.arpa','120','PTR','dns1.lab');
INSERT INTO rev_192_168_77 (name, ttl, rdtype, rdata) values ('162.77.168.192.in-addr.arpa','120','PTR','ovirt.lab');

CREATE TRIGGER rev_192_168_77_timestamp BEFORE INSERT OR UPDATE ON rev_192_168_77
    FOR EACH ROW EXECUTE PROCEDURE modtime();

Finally, here are some outputs:

binddns=# select * from dom_lab;
 id |   name    | ttl  | rdtype |                        rdata                         |          modtime           | moduser 
----+-----------+------+--------+------------------------------------------------------+----------------------------+----------
  1 | lab       | 3600 | SOA    | localhost. support.savantadvisors.com. 1 1d 2h 4w 1h | 2013-08-31 15:28:45.751118 | binduser
  2 | lab       | 3600 | NS     | dns1.lab.                                            | 2013-08-31 15:28:45.757822 | binduser
  3 | lab       | 3600 | NS     | localhost.                                           | 2013-08-31 15:28:45.766146 | binduser
  4 | dns1.lab  |  120 | A      | 192.168.77.19                                        | 2013-08-31 15:28:45.774814 | binduser
  5 | dns1.lab  |  120 | A      | 192.168.77.162                                       | 2013-08-31 15:28:45.78318  | binduser
  6 | ovirt.lab |  120 | A      | 192.168.77.19                                        | 2013-08-31 15:28:45.791461 | binduser
  7 | lab       |  120 | MX     | 5 mx.lab.                                            | 2013-08-31 15:28:45.799364 | binduser
  8 | mx.lab    |  120 | A      | 192.168.77.162                                       | 2013-08-31 15:31:35.230772 | binduser
(8 rows)

binddns=# 

binddns=# select * from rev_192_168_77;
 id |            name             | ttl  | rdtype |                        rdata                         |          modtime           | moduser 
----+-----------------------------+------+--------+------------------------------------------------------+----------------------------+----------
  1 | 77.168.192.in-addr.arpa     | 3600 | SOA    | localhost. support.savantadvisors.com. 1 1d 2h 4w 1h | 2013-08-31 15:34:39.063912 | binduser
  2 | 77.168.192.in-addr.arpa     | 3600 | NS     | localhost.                                           | 2013-08-31 15:34:39.070303 | binduser
  3 | 77.168.192.in-addr.arpa     | 3600 | NS     | dns1.lab.                                            | 2013-08-31 15:34:39.079037 | binduser
  4 | 19.77.168.192.in-addr.arpa  |  120 | PTR    | ovirt.lab                                            | 2013-08-31 15:34:39.087333 | binduser
  5 | 19.77.168.192.in-addr.arpa  |  120 | PTR    | dns1.lab                                             | 2013-08-31 15:34:39.095371 | binduser
  6 | 162.77.168.192.in-addr.arpa |  120 | PTR    | ovirt.lab                                            | 2013-08-31 15:34:39.103927 | binduser
(6 rows)
binddns=# UPDATE rev_192_168_77 SET ttl='3600' where id='4';
UPDATE 1
binddns=# DELETE FROM rev_192_168_77 WHERE id='5';
DELETE 1

NOTE: You can only have one reverse pointer per address so I deleted the second entry

binddns=# select * from rev_192_168_77;
 id |            name             | ttl  | rdtype |                        rdata                         |          modtime           | moduser 
----+-----------------------------+------+--------+------------------------------------------------------+----------------------------+----------
  1 | 77.168.192.in-addr.arpa     | 3600 | SOA    | localhost. support.savantadvisors.com. 1 1d 2h 4w 1h | 2013-08-31 15:34:39.063912 | binduser
  2 | 77.168.192.in-addr.arpa     | 3600 | NS     | localhost.                                           | 2013-08-31 15:34:39.070303 | binduser
  3 | 77.168.192.in-addr.arpa     | 3600 | NS     | dns1.lab.                                            | 2013-08-31 15:34:39.079037 | binduser
  6 | 162.77.168.192.in-addr.arpa |  120 | PTR    | ovirt.lab                                            | 2013-08-31 15:34:39.103927 | binduser
  4 | 19.77.168.192.in-addr.arpa  | 3600 | PTR    | ovirt.lab                                            | 2013-08-31 16:18:03.470766 | binduser 
(5 rows)
 
binddns=#

We can verify the results using the regular DNS utilities from bind-utils


server# host ovirt.lab 127.0.0.1
Using domain server:
Name: 127.0.0.1
Address: 127.0.0.1#53
Aliases:

ovirt.lab has address 192.168.77.19

server# host 192.168.77.162 127.0.0.1
Using domain server:
Name: 127.0.0.1
Address: 127.0.0.1#53
Aliases:

162.77.168.192.in-addr.arpa domain name pointer ovirt.lab.

server# host 192.168.77.19 127.0.0.1
Using domain server:
Name: 127.0.0.1
Address: 127.0.0.1#53
Aliases:

19.77.168.192.in-addr.arpa domain name pointer ovirt.lab.
  
Hope this helps anyone looking for this kind of information. Thanks for stopping by!