We handle DNS for thousands of domains for our customers and whilst our existing solution worked it was very messy to maintain and work with so we decided to trial a new solution for our offices to see how it would perform. We wanted something that could be database driven for ease of maintenance and we were personally recommended PowerDNS, so we decided to trial that one first.
For the database we would normally go with MySQL but we wanted an instance of PostgreSQL to play with as we are considering moving our main platform to it at some point in the future.
Our DNS server is running on Ubuntu Gutsy and everything we need is fortunately in the repositories so installing it is as easy as:
apt-get install pdns-backend-pgsql pdns-doc pdns-recursor pdns-server postgresql postgresql-contrib postgresql-doc
After all the software is installed we need to tell PowerDNS to use our PostgreSQL server in /etc/powerdns/pdns.conf
launch=gpgsql
gpgsql-host=127.0.0.1
gpgsql-user=powerdns
gpgsql-password=password
gpgsql-dbname=powerdns
We then need to configure the database, tables and user permissions in PostgreSQL.
To create the user we must become a superuser which typically involves changing to the postgres unix user and taking advantage of the ident based authentication.
root@sambuca:~# su postgres
postgres@sambuca:/root$ psql
Welcome to psql 8.2.5, the PostgreSQL interactive terminal.
postgres=# CREATE USER powerdns WITH PASSWORD 'password';
CREATE USER
You can check the user has been created through the psql client too.
postgres=# select * from pg_shadow;
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig
----------+----------+-------------+----------+-----------+-------------------------------------+----------+-----------
postgres | 10 | t | t | t | | |
powerdns | 16385 | f | f | f | md5e954fb1203f8da7392a0c7406f83d765 | |
(2 rows)
We then need to create and switch to the new database
postgres=# create database powerdns;
CREATE DATABASE
postgres=# l
List of databases
Name | Owner | Encoding
-----------+----------+----------
postgres | postgres | UTF8
powerdns | postgres | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(4 rows)
postgres=# c powerdns
You are now connected to database "powerdns".
The table structure is
create table domains (
id SERIAL PRIMARY KEY,
name VARCHAR(255) NOT NULL,
master VARCHAR(128) DEFAULT NULL,
last_check INT DEFAULT NULL,
type VARCHAR(6) NOT NULL,
notified_serial INT DEFAULT NULL,
account VARCHAR(40) DEFAULT NULL
);
CREATE UNIQUE INDEX name_index ON domains(name);
CREATE TABLE records (
id SERIAL PRIMARY KEY,
domain_id INT DEFAULT NULL,
name VARCHAR(255) DEFAULT NULL,
type VARCHAR(6) DEFAULT NULL,
content VARCHAR(255) DEFAULT NULL,
ttl INT DEFAULT NULL,
prio INT DEFAULT NULL,
change_date INT DEFAULT NULL,
CONSTRAINT domain_exists
FOREIGN KEY(domain_id) REFERENCES domains(id)
ON DELETE CASCADE
);
CREATE INDEX rec_name_index ON records(name);
CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);
create table supermasters (
ip VARCHAR(25) NOT NULL,
nameserver VARCHAR(255) NOT NULL,
account VARCHAR(40) DEFAULT NULL
);
GRANT SELECT ON supermasters TO powerdns;
GRANT ALL ON domains TO powerdns;
GRANT ALL ON domains_id_seq TO powerdns;
GRANT ALL ON records TO powerdns;
GRANT ALL ON records_id_seq TO powerdns;
And then we can look at them!
powerdns=# \d
List of relations
Schema | Name | Type | Owner
--------+----------------+----------+----------
public | domains | table | postgres
public | domains_id_seq | sequence | postgres
public | records | table | postgres
public | records_id_seq | sequence | postgres
public | supermasters | table | postgres
(5 rows)
After the user is created we need to edit /etc/postgresql/8.2/main/ph_hba.conf to grant that user access to the database from localhost
host powerdns powerdns 127.0.0.0/16 md5
We then need to reload PostgreSQL for the changes to take effect.
root@sambuca:~# /etc/init.d/postgresql-8.2 reload
We then need to populate it with the important SOA and NS records. All the records take a creation date as a timestamp, so we also created a function to return the current timestamp.
create function epoch() returns int AS 'select extract(epoch from now())::int;';
insert into records (domain_id, name, type, content, ttl, prio,change_date) values (1, 'btn.com', 'NS', 'dnsserver.btn.com',600,10,epoch());
insert into records (domain_id, name, type, content, ttl, prio,change_date) values (1, 'btn.com', 'SOA', 'dnsserver 2005091301 10800 3600 604800 600',600,10,epoch());
insert into records (domain_id, name, type, content, ttl, prio,change_date) values (1, 'dnsserver.btn.com', 'A', '10.0.0.1',600,10,epoch());
Now all we need to do is edit /etc/resolv.conf to use the new nameserver
nameserver 10.0.0.1
and check that it works!
rus@absinthe:~$ host dnsserver.btn.com
dnsserver.btn.com has address 10.0.0.1
Hi,
When i try to use the create function epoch, missing language error:
ERROR: no language specified
In statement:
create function epoch() returns int AS ‘select extract(epoch from now())::int;’
you may forgot to define a language in this tutorial
How to resolve this?
Thanx
Another error when inserting data on records table:
ERROR: insert or update on table “records” violates foreign key constraint “domain_exists”
DETAIL: Key (domain_id)=(1) is not present in table “domains”.
I got this same error. It’s the result of an entry not being created in the ‘domains’ table first.
Adding the following query before the records insert completes sucessfully.
INSERT INTO domains(name, type) VALUES(‘btn.com’, ‘NATIVE’);
***
As in:
INSERT INTO domains(name, type) VALUES(‘btn.com’, ‘NATIVE’);
insert into records (domain_id, name, type, content, ttl, prio,change_date) values (1, ‘btn.com’, ‘NS’, ‘dnsserver.btn.com’,600,10,epoch());
the function above worked for me as SQL, not PLPGSQL
CREATE FUNCTION epoch() RETURNS int AS ‘select extract(epoch from now())’ LANGUAGE ‘sql’;