Smokes your problems, coughs fresh air.

Tag: pdns

Upgrading PowerDNS from Debian Squeeze to Wheezy

As I mentioned here, I had to manually create a database schema when I installed PowerDNS on Debian Squeeze. Later versions apparently create one themselve.

I just upgraded my Sqeeuze machine, and the package manager said that the DB needed to be migrated. But, MySQL maintenance always seems to go wrong on Debian upgrades. It started asking me the same questions as if PowerDNS wasn’t installed; it wanted to create a new user and such. I don’t know why it keeps doing that; it’s not the first time that happened. Needless to say, this process failed.

So, I looked at another PowerDNS server and I made the SQL myself:

alter table records add ordername varchar(255);
alter table records add auth tinyint(1);
update records set auth=1;
 
CREATE TABLE `cryptokeys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `flags` int(11) NOT NULL,
  `active` tinyint(1) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  CONSTRAINT `cryptokeys_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
CREATE TABLE `domainmetadata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_id` int(11) NOT NULL,
  `kind` varchar(16) DEFAULT NULL,
  `content` text,
  PRIMARY KEY (`id`),
  KEY `domain_id` (`domain_id`),
  CONSTRAINT `domainmetadata_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
CREATE TABLE `tsigkeys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `algorithm` varchar(255) DEFAULT NULL,
  `secret` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `namealgoindex` (`name`,`algorithm`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Adding foreign key constraints to PowerDNS default schema

In an earlier post I made, I described how to manually create the SQL schema, because it didn’t itself. When I installed Power DNS 3.1-4 on a Raspberry Pi, I noticed it had created the scheme itself this time. However, it did not make the foreign key constraints. This is dumb. I regularly need to remove domains from the DB and I don’t want stale or incorrect references as a result.

To create them by hand:

alter table records add foreign key (domain_id) references domains(id) on delete cascade;
alter table cryptokeys add foreign key (domain_id) references domains(id) on delete cascade;
alter table domainmetadata add foreign key (domain_id) references domains(id) on delete cascade;

It must be a bug that this was omitted, because I got the original scheme from the PowerDNS website. Question is, is this a bug in the Rasbian/Debian package, or in PowerDNS?

Configuring a Power DNS superslave server

Power DNS, as opposed to Bind, has the option to be a superslave. This means that it will initiate any zone transfer from trusted hosts, avoiding the need to configure each zone on both master and slave.

Power DNS has separate back-ends, of which you must choose one. I chose mysql, and I use the generic mysql engine (this is different, and better, than normal MySQL, or something like that…).

First install Power DNS:

aptitute -P install pdns-server pdns-backend-mysql

Then create a database and user:

create database pdns character set utf8;
grant all on pdns.* to 'pdns'@'localhost' identified by 'password';

Then create this schema (found it in the Power DNS docs):

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 DEFAULT NULL,                                                                                                                       
 account         VARCHAR(40) DEFAULT NULL,                                                                                                               
 primary key (id)                                                                                                                                        
)type=InnoDB;                                                                                                                                            
                                                                                                                                                         
CREATE UNIQUE INDEX name_index ON domains(name);                                                                                                         
                                                                                                                                                         
CREATE TABLE records (                                                                                                                                   
  id              INT auto_increment,                                                                                                                    
  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,
  primary key(id),
  CONSTRAINT `records_ibfk_1` FOREIGN KEY (`domain_id`) REFERENCES `domains` (`id`) ON DELETE CASCADE
)type=InnoDB;
 
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 pdns;
GRANT ALL ON domains TO pdns;
GRANT ALL ON records TO pdns;

Then create /etc/powerdns/pdns.d/pdns-mysql-backend with this in it:

launch=gmysql
gmysql-host=127.0.0.1
gmysql-user=pdns
gmysql-password=password
gmysql-dbname=pdns

Then insert a supermaster:

insert into supermasters values ('1.2.3.4','ns1.example.com','identifiername');

Lastly, don’t forget to enable slave mode in /etc/powerdns/pdns.conf:

slave=yes

© 2024 BigSmoke

Theme by Anders NorenUp ↑