CentOS7 x64 with Apache and PDNS 3.4.7 returns ServFail for all requests

KyferEz commented 8 years ago

Not sure if this is a bug or what the issue is but I have been unable to resolve it. CentOS 7 x64 is up to date. EPEL repository has been added and all packages updated.

Followed the following guide for everything except Nginx because I had Apache already running: https://www.rosehosting.com/blog/install-powerdns-and-on-a-centos-7-vps/

Firewall is disabled. Selinux is set to monitor only.

Below is my database and do note that the web management for powerdns works just fine.

However I get servfail on any dig attempt. See database output and pdns conf below.

Please help me figure out what is wrong! Note that I was using BIND and it was working perfectly but I want a database driven DNS system so am trying to get PDNS working. BIND is stopped during these tests as you can see from the netstat output.

MariaDB [powerdns]> show tables;
+--------------------+
| Tables_in_powerdns |
+--------------------+
| domains            |
| migrations         |
| perm_items         |
| perm_templ         |
| perm_templ_items   |
| records            |
| records_zone_templ |
| supermasters       |
| users              |
| zone_templ         |
| zone_templ_records |
| zones              |
+--------------------+

MariaDB [powerdns]> select * from domains;

+----+----------------------+--------+------------+--------+-----------------+---------+
| id | name                 | master | last_check | type   | notified_serial | account |
+----+----------------------+--------+------------+--------+-----------------+---------+
|  2 | example.com | NULL   |       NULL | MASTER |            NULL | NULL    |
+----+----------------------+--------+------------+--------+-----------------+---------+

MariaDB [powerdns]> select * from records;

+----+-----------+------------------+------+------------------------------------------------------------------------------------------+-------+------+-------------+
| id | domain_id | name             | type | content                                                                                  | ttl   | prio | change_date |
+----+-----------+------------------+------+------------------------------------------------------------------------------------------+-------+------+-------------+
|  5 |         2 | example.com      | SOA  | ns1.example.com noreply@example.com 2016012205 28800 7200 604800 86400                   | 86400 |    0 |  1453430301 |
|  6 |         2 | example.com      | A    | 123.123.123.123                                                                          | 86400 |    0 |  1453430985 |
|  7 |         2 | mail.example.com | A    | 123.123.123.123                                                                          | 86400 |    0 |  1453430985 |
|  8 |         2 | ns1.example.com  | A    | 123.123.123.123                                                                          | 86400 |    0 |  1453430985 |
+----+-----------+---------------------------+------+------------------------------------------------------------------------------------------+-------+------+-------------+

[root@s01 admin]# dig @127.0.0.1

; <<>> DiG 9.9.4-RedHat-9.9.4-29.el7_2.1 <<>> @127.0.0.1
; (1 server found)
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: SERVFAIL, id: 3023
;; flags: qr aa rd; QUERY: 1, ANSWER: 0, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available
;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1680
;; QUESTION SECTION:
;.                              IN      NS
;; Query time: 1 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Fri Jan 22 18:10:21 EST 2016
;; MSG SIZE  rcvd: 28

PDNS /etc/pdns/pdns.conf:

setuid=pdns
setgid=pdns
#launch=bind
launch=gmysql
gmysql-host=localhost
gmysql-user=powerdns
gmysql-password=lolololol
gmysql-dbname=powerdns

[root@s01 admin]# netstat -tap

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
tcp        0      0 localhost:cslistener    0.0.0.0:*               LISTEN      950/php-fpm: master
tcp        0      0 0.0.0.0:mysql           0.0.0.0:*               LISTEN      1783/mysqld
tcp        0      0 0.0.0.0:ndmp            0.0.0.0:*               LISTEN      2561/perl
tcp        0      0 0.0.0.0:domain          0.0.0.0:*               LISTEN      12283/pdns_server
tcp        0      0 0.0.0.0:8822            0.0.0.0:*               LISTEN      1233/sshd
tcp        0      0 localhost:smtp          0.0.0.0:*               LISTEN      1826/master
tcp6       0      0 [::]:http               [::]:*                  LISTEN      900/httpd
tcp6       0      0 [::]:8821               [::]:*                  LISTEN      15532/vsftpd
tcp6       0      0 [::]:8822               [::]:*                  LISTEN      1233/sshd
tcp6       0      0 localhost:smtp          [::]:*                  LISTEN      1826/master

[root@s01 admin]# netstat -uap

Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name
udp        0      0 0.0.0.0:ndmp            0.0.0.0:*                           2561/perl
udp        0      0 0.0.0.0:domain          0.0.0.0:*                           12283/pdns_server
udp        0      0 0.0.0.0:bootpc          0.0.0.0:*                           1028/dhclient
udp        0      0 localhost:323           0.0.0.0:*                           621/chronyd
udp        0      0 0.0.0.0:13651           0.0.0.0:*                           1028/dhclient
udp6       0      0 localhost:323           [::]:*                              621/chronyd
udp6       0      0 [::]:50691              [::]:*                              1028/dhclient

setharnold commented 8 years ago

Can you give examples of queries for e.g. example.com or reverse lookups against 123.123.123.123 that fail?

Thanks

KyferEz commented 8 years ago

Here are examples: [root@s01 /]# dig example.com @localhost

; <<>> DiG 9.9.4-RedHat-9.9.4-29.el7_2.1 <<>> example.com @localhost
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: SERVFAIL, id: 62343
;; flags: qr aa rd; QUERY: 1, ANSWER: 0, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1680
;; QUESTION SECTION:
;example.com.          IN      A

;; Query time: 1 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Fri Jan 22 19:16:29 EST 2016
;; MSG SIZE  rcvd: 49

[root@s01 /]# dig 123.123.123.123 @localhost

; <<>> DiG 9.9.4-RedHat-9.9.4-29.el7_2.1 <<>> 123.123.123.123 @localhost
;; global options: +cmd
;; Got answer:
;; ->>HEADER<<- opcode: QUERY, status: SERVFAIL, id: 48927
;; flags: qr aa rd; QUERY: 1, ANSWER: 0, AUTHORITY: 0, ADDITIONAL: 1
;; WARNING: recursion requested but not available

;; OPT PSEUDOSECTION:
; EDNS: version: 0, flags:; udp: 1680
;; QUESTION SECTION:
;123.123.123.123.               IN      A

;; Query time: 4 msec
;; SERVER: 127.0.0.1#53(127.0.0.1)
;; WHEN: Fri Jan 22 19:15:17 EST 2016
;; MSG SIZE  rcvd: 44

[root@s01 /]# nslookup example.com localhost

;; Got SERVFAIL reply from 127.0.0.1, trying next server
;; connection timed out; trying next origin
;; Got SERVFAIL reply from 127.0.0.1, trying next server

KyferEz commented 8 years ago

Any ideas? Should I setup a syslog server so we can review logs or is there any way to make PowerDNS log to file?

ahupowerdns commented 8 years ago

syslog is the way to go. these look like very old database schemas though?

KyferEz commented 8 years ago

It's the schema from the guide I linked to in the first post. I'm willing to drop the tables and import a new one you suggest. I'll start on the syslog. Any recommendation for using same server for syslog as pdns is running on?

KyferEz commented 8 years ago

Here are the syslog message from a dig which failed with the same message from above.

[root@s01]# dig example.com @localhost

Jan 23 18:14:41 s01 pdns[27536]: PowerDNS comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to redistribute it according to the terms of the GPL version 2.
Jan 23 18:14:41 s01 pdns[27536]: Creating backend connection for TCP
Jan 23 18:14:41 s01 systemd: Started PowerDNS Authoritative Server.
Jan 23 18:14:41 s01 pdns[27536]: About to create 3 backend threads for UDP
Jan 23 18:14:41 s01 pdns[27536]: Done launching threads, ready to distribute questions
Jan 23 18:15:09 s01 pdns[27536]: Backend reported permanent error which prevented lookup (GSQLBackend lookup query:Failed to execute mysql_query, perhaps connection died? Err=1: Unknown column 'disabled' in 'field list'), aborting
Jan 23 18:15:09 s01 pdns[27536]: Backend error: GSQLBackend lookup query:Failed to execute mysql_query, perhaps connection died? Err=1: Unknown column 'disabled' in 'field list'

KyferEz commented 8 years ago

Where do I find the full SQL schema? I found partial here: https://github.com/poweradmin/poweradmin/blob/master/sql/poweradmin-mysql-db-structure.sql but it's missing tables like domains and others.

KyferEz commented 8 years ago

I found it. The rest of the schema was here: https://doc.powerdns.com/md/authoritative/installation/

So I removed all the tables from my original guide and then recreated the tables from the schema below which is a combination of the two links I found. Once done I restarted the pdns service, re-entered my example.com master entry, and viola! IT WORKS!

CREATE TABLE users (
  id          INTEGER      NOT NULL AUTO_INCREMENT,
  username    VARCHAR(64)  NOT NULL,
  `password`  VARCHAR(128) NOT NULL,
  fullname    VARCHAR(255) NOT NULL,
  email       VARCHAR(255) NOT NULL,
  description TEXT         NOT NULL,
  perm_templ  TINYINT      NOT NULL,
  active      TINYINT      NOT NULL,
  use_ldap    TINYINT      NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

START TRANSACTION;
    INSERT INTO users ( id, username, `password`, fullname, email
                      , description, perm_templ, active, use_ldap )
    VALUES ( 1, 'admin', '21232f297a57a5a743894a0e4a801fc3', 'Administrator'
           , 'admin@example.net', 'Administrator with full rights.', 1, 1, 0 );
COMMIT;

CREATE TABLE perm_items (
  id INTEGER       NOT NULL AUTO_INCREMENT,
  name VARCHAR(64) NOT NULL,
  descr TEXT       NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

START TRANSACTION;
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 41, 'zone_master_add', 'User is allowed to add new master zones.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 42, 'zone_slave_add', 'User is allowed to add new slave zones.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 43, 'zone_content_view_own', 'User is allowed to see the content and meta data of zones he owns.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 44, 'zone_content_edit_own', 'User is allowed to edit the content of zones he owns.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 45, 'zone_meta_edit_own', 'User is allowed to edit the meta data of zones he owns.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 46, 'zone_content_view_others', 'User is allowed to see the content and meta data of zones he does not own.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 47, 'zone_content_edit_others', 'User is allowed to edit the content of zones he does not own.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 48, 'zone_meta_edit_others', 'User is allowed to edit the meta data of zones he does not own.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 49, 'search', 'User is allowed to perform searches.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 50, 'supermaster_view', 'User is allowed to view supermasters.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 51, 'supermaster_add', 'User is allowed to add new supermasters.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 52, 'supermaster_edit', 'User is allowed to edit supermasters.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 53, 'user_is_ueberuser', 'User has full access. God-like. Redeemer.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 54, 'user_view_others', 'User is allowed to see other users and their details.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 55, 'user_add_new', 'User is allowed to add new users.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 56, 'user_edit_own', 'User is allowed to edit their own details.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 57, 'user_edit_others', 'User is allowed to edit other users.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 58, 'user_passwd_edit_others', 'User is allowed to edit the password of other users.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 59, 'user_edit_templ_perm', 'User is allowed to change the permission template that is assigned to a user.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 60, 'templ_perm_add', 'User is allowed to add new permission templates.' );
    INSERT INTO perm_items ( id, name, descr ) VALUES ( 61, 'templ_perm_edit', 'User is allowed to edit existing permission templates.' );
COMMIT;

CREATE TABLE perm_templ (
  id    INTEGER      NOT NULL AUTO_INCREMENT,
  name  VARCHAR(128) NOT NULL,
  descr TEXT         NOT NULL,
  PRIMARY KEY  (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

START TRANSACTION;
    INSERT INTO perm_templ ( id, name, descr )
        VALUES ( 1, 'Administrator'
               , 'Administrator template with full rights.' );
COMMIT;

CREATE TABLE perm_templ_items (
  id INTEGER       NOT NULL AUTO_INCREMENT,
  templ_id INTEGER NOT NULL,
  perm_id INTEGER  NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

START TRANSACTION;
    INSERT INTO perm_templ_items ( id, templ_id, perm_id )
    VALUES ( 1, 1, 53 );
COMMIT;

CREATE TABLE zones (
  id            INTEGER NOT NULL AUTO_INCREMENT,
  domain_id     INTEGER NOT NULL,
  owner         INTEGER NOT NULL,
  `comment`     TEXT,
  zone_templ_id INTEGER NOT NULL,
  PRIMARY KEY (id),
  KEY owner (owner)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE zone_templ (
  id    INTEGER      NOT NULL AUTO_INCREMENT,
  name  VARCHAR(128) NOT NULL,
  descr TEXT         NOT NULL,
  owner INTEGER      NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE zone_templ_records (
  id            INTEGER      NOT NULL AUTO_INCREMENT,
  zone_templ_id INTEGER      NOT NULL,
  name          VARCHAR(255) NOT NULL,
  `type`        VARCHAR(6)   NOT NULL,
  content       VARCHAR(255) NOT NULL,
  ttl           INTEGER      NOT NULL,
  prio          INTEGER      NOT NULL,
  PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE records_zone_templ (
    domain_id INTEGER NOT NULL,
    record_id INTEGER NOT NULL,
    zone_templ_id INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

CREATE TABLE migrations (
    version VARCHAR(255) NOT NULL,
    apply_time INTEGER NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

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)
) Engine=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(10) DEFAULT NULL,
  content               VARCHAR(64000) DEFAULT NULL,
  ttl                   INT DEFAULT NULL,
  prio                  INT DEFAULT NULL,
  change_date           INT DEFAULT NULL,
  disabled              TINYINT(1) DEFAULT 0,
  ordername             VARCHAR(255) BINARY DEFAULT NULL,
  auth                  TINYINT(1) DEFAULT 1,
  PRIMARY KEY (id)
) Engine=InnoDB;

CREATE INDEX nametype_index ON records(name,type);
CREATE INDEX domain_id ON records(domain_id);
CREATE INDEX recordorder ON records (domain_id, ordername);

CREATE TABLE supermasters (
  ip                    VARCHAR(64) NOT NULL,
  nameserver            VARCHAR(255) NOT NULL,
  account               VARCHAR(40) NOT NULL,
  PRIMARY KEY (ip, nameserver)
) Engine=InnoDB;

CREATE TABLE comments (
  id                    INT AUTO_INCREMENT,
  domain_id             INT NOT NULL,
  name                  VARCHAR(255) NOT NULL,
  type                  VARCHAR(10) NOT NULL,
  modified_at           INT NOT NULL,
  account               VARCHAR(40) NOT NULL,
  comment               VARCHAR(64000) NOT NULL,
  PRIMARY KEY (id)
) Engine=InnoDB;

CREATE INDEX comments_domain_id_idx ON comments (domain_id);
CREATE INDEX comments_name_type_idx ON comments (name, type);
CREATE INDEX comments_order_idx ON comments (domain_id, modified_at);

CREATE TABLE domainmetadata (
  id                    INT AUTO_INCREMENT,
  domain_id             INT NOT NULL,
  kind                  VARCHAR(32),
  content               TEXT,
  PRIMARY KEY (id)
) Engine=InnoDB;

CREATE INDEX domainmetadata_idx ON domainmetadata (domain_id, kind);

CREATE TABLE cryptokeys (
  id                    INT AUTO_INCREMENT,
  domain_id             INT NOT NULL,
  flags                 INT NOT NULL,
  active                BOOL,
  content               TEXT,
  PRIMARY KEY(id)
) Engine=InnoDB;

CREATE INDEX domainidindex ON cryptokeys(domain_id);

CREATE TABLE tsigkeys (
  id                    INT AUTO_INCREMENT,
  name                  VARCHAR(255),
  algorithm             VARCHAR(50),
  secret                VARCHAR(255),
  PRIMARY KEY (id)
) Engine=InnoDB;

CREATE UNIQUE INDEX namealgoindex ON tsigkeys(name, algorithm);

Habbie commented 8 years ago

Good to hear it! Closing ticket :)

haizei1024 commented 6 years ago

It help me a lot! Thank you!

kfliongrevenue commented 5 years ago

Thank you!!! I was trying to resolve this issue for few hours and looked at tons of guides. All of them are using the old schema which caused this problem. After re-installing with new mysql schema it worked.

Can I suggest that the installation includes sql codes for creating the latest schema like most software? This will resolve the issue when schema changes.

PowerDNS / pdns

CentOS7 x64 with Apache and PDNS 3.4.7 returns ServFail for all requests #3289