Saturday, August 31, 2013

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!

40 comments:

  1. Your post has many detail information. I read all your article and I really like it. Thank you for sharing this great post.
    - gta 5 cheats

    ReplyDelete
  2. Thank you so much for posting this. I really appreciate your work. Keep it up. Great work!Best software training company with placement in Hyderabad

    ReplyDelete
  3. Thanks for the blog post buddy! Keep them coming... PostgreSQL alias

    ReplyDelete
  4. Excellent erudition Providing by your Article, thank you for taking the time to share with us such a nice article. Amazing insight you have on this, it's nice to find a website that details so much information about different artists. Kindly visit the LiveWebTutors website we providing the best online assignment help services in Australia.

    For More Info: Assignment Writing Help

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. It was really a nice experience to be on your blog. Mobilerepairfactory.com.au offers mobile phone and Tablet repair services in Sydney. Get repair your mobile by visiting on IPhone Repair Sydney

    ReplyDelete
  7. Nice piece of content you have shared. I read your article for getting good source of information. Thanks for sharing it.

    Topnotch SEO services

    ReplyDelete
  8. Thank you so much for posting this. I really appreciate your work. Keep it up. Great work!

    Best SEO company with placement in Ontario

    ReplyDelete
  9. Great post. I have learned a lot. Thanks.
    There are some discount drapes. beautiful indoor curtains, please try that.

    ReplyDelete
  10. Great job for publishing such a nice article. Your article isn’t only useful but it is additionally really informative. Thank you because you have been willing to share information with us. Get the LG Phone Repair services at officialphonerepair.co.uk.

    ReplyDelete
  11. i am very impres of your post.

    https://grfreightservices.com/

    ReplyDelete
  12. I visited your site for the first time and started reading this one blog. It kept me stuck to your content. You have written it so well that I can’t keep my eyes off from it and reached the end of this.
    local businesses

    ReplyDelete
  13. I enjoyed reading this post a lot
    This is a reality that blogging isn’t a
    quick money-making business.
    But it’s a nice business. thanks for
    sharing this post.izr

    ReplyDelete
  14. This article is very useful. Very good article. Thank you for telling me about this article. I know of a school & fashion house that has a good education in fashion. best fashion design schools in accra Riohs Originate is a Creative Art Service and Training Institute in Ghana.
    They provide services and training for individuals/organizations that value their image, style, color and insist on promptness, accuracy and consistency.
    best fashion schools in accra ghana
    top fashion schools in accra

    fashion training schools in accra

    names of fashion schools in accra

    fashion design schools in accra ghana

    ReplyDelete
  15. Baby Frock Nice post! This is a very nice blog that I will definitively come back to more times this year! Thanks for informative post.

    ReplyDelete
  16. http://www.greysquall.com/2016/06/wasting-money-advertising-on-facebook.html?showComment=1598084096181#c1154877203345843834 Awesome article, it was exceptionally helpful! I simply began in this and I'm becoming more acquainted with it better! Cheers, keep doing awesome!

    ReplyDelete
  17. Frock for girls We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work.

    ReplyDelete
  18. Frock design This is such a great resource that you are providing and you give it away for free. I love seeing blog that understand the value of providing a quality resource for free.

    ReplyDelete
  19. Frock This is my first visit to your web journal! We are a group of volunteers and new activities in the same specialty. Website gave us helpful data to work.

    ReplyDelete
  20. Frock style I like your post. It is good to see you verbalize from the heart and clarity on this important subject can be easily observed...

    ReplyDelete
  21. baby Frock design Very informative post! There is a lot of information here that can help any business get started with a successful social networking campaign.

    ReplyDelete
  22. Frock design for girls Wow, What a Excellent post. I really found this to much informatics. It is what i was searching for.I would like to suggest you that please keep sharing such type of info.Thanks

    ReplyDelete
  23. baby girl dresses Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too.

    ReplyDelete
  24. baby girls dresses Decent post! This is an exceptionally decent blog that I will absolutely return to more occasions this year! A debt of gratitude is in order for enlightening post.

    ReplyDelete
  25. Online shopping in Pakistan We are really grateful for your blog post. You will find a lot of approaches after visiting your post. I was exactly searching for. Thanks for such post and please keep it up. Great work.

    ReplyDelete
  26. bridal dresses I like your post. It is good to see you verbalize from the heart and clarity on this important subject can be easily observed...

    ReplyDelete
  27. fancy dress for girls Very informative post! There is a lot of information here that can help any business get started with a successful social networking campaign.

    ReplyDelete
  28. wedding dresses for girls Wow, What a Excellent post. I really found this to much informatics. It is what i was searching for.I would like to suggest you that please keep sharing such type of info.Thanks

    ReplyDelete
  29. wedding dresses Great job for publishing such a beneficial web site. Your web log isn’t only useful but it is additionally really creative too.

    ReplyDelete
  30. girls dresses Decent post! This is an exceptionally decent blog that I will absolutely return to more occasions this year! A debt of gratitude is in order for enlightening post.

    ReplyDelete
  31. Online Shopping for girls Decent post! This is an exceptionally decent blog that I will absolutely return to more occasions this year! A debt of gratitude is in order for enlightening post.

    ReplyDelete
  32. Nice article and explanation Keep continuing to write an article like this. This is a topic that’s near to my heart… Best wishes! I like your Post and helped me.

    No more waste time to searching #HDimages, #PCwallpaper, #smartphone_wallpaper, #socialmedia_banner, #socialmedia_image_post, #natureHDimages, and many more awesome pictures. Just visit All Awesome Pictures Only For Love Hearts and choose your best choice.
    All Awesome Pictures Only For Love Hearts
    All Awesome Pictures Only For Love Hearts
    All Awesome Pictures Only For Love Hearts

    ReplyDelete
  33. This is the information exactly what I am looking on internet today. i learned useful information from this post. article is nicely explained and easy to understand. thanks for sharing this valuable information with us. keep your good work.

    ReplyDelete
  34. Great Blog. Thanks for generous us a great list go men fashion Blog. I found it very exciting.
    CPP Boxes

    ReplyDelete