DNS entries from NetXMS

Some time ago, our group of engineers got hooked tightly on the NetXMS monitoring system . The killer feature turned out to be a graphical console:



Nothing as obvious and convenient (yes, this is a very subjective criterion) in the familiar to us OpenNMS and Zabbix is not close. It became possible to use the monitoring system not just as an alert mechanism for emerging problems, but primarily for analyzing the current state of the network.
')
We have already used several completely unrelated databases, which from one side or another took into account all or part of the equipment we operate. It was tempting to reduce the number of these databases by taking the NetXMS database as the primary source of information. The first victim was the internal DNS server.

Indeed, what's the point for each of several hundred hosts to make the corresponding A and PTR records? Let us not be too tense, just editing the / etc / hosts file for dnsmasq , but you can avoid this completely unnecessary work.

We chose PostgreSQL as a DBMS for NetXMS, respectively, we needed a DNS server that could get at least A and PTR from PostgreSQL. Immediately found the following options:

• Dynamically Loadable Zones for BIND
• Generic PgSQL backend for PowerDNS
• Relative exotic - implementations of perl or python DNS servers, specifically designed to be extended by their own handlers, this category also includes PipeBackend and RemoteBackend for PowerDNS

We rejected too exotic solutions, to bind we have an irrational disgust, that's why PowerDNS remained. It was necessary to add to its configuration:

load-modules=gpgsql launch=gpgsql gpgsql-host=nxhost gpgsql-dbname=nxdb gpgsql-user=nxreader gpgsql-password=nxreaderpwd recursor=8.8.8.8 

PowerDNS expects to see the following tables in the nxdb database:

 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 TABLE records ( id SERIAL PRIMARY KEY, domain_id INT DEFAULT NULL, name VARCHAR(255) DEFAULT NULL, type VARCHAR(10) DEFAULT NULL, content VARCHAR(65535) DEFAULT NULL, ttl INT DEFAULT NULL, prio INT DEFAULT NULL, change_date INT DEFAULT NULL ); 

Of course, there are no such tables there, but instead of them there is the following:

 nxdb=> SELECT id, name, primary_ip, last_modified FROM nodes INNER JOIN object_properties ON id = object_id; id | name | primary_ip | last_modified ------+------------------------+-----------------+--------------- 1 | ns | 10.1.1.1 | 1376221181 

Therefore, the required tables are easily replaced with views:

 CREATE OR REPLACE VIEW domains AS SELECT 0::INT AS id, 'domain.local'::TEXT AS name, NULL::TEXT AS master, NULL::INT as last_check, 'NATIVE'::TEXT as type, NULL::INT as notified_serial, NULL::TEXT as account; CREATE OR REPLACE VIEW records AS SELECT 0::INT AS id, 0 AS domain_id, 'domain.local' AS name, 'SOA'::TEXT AS type, 'ns.domain.local'::TEXT AS content, 0 AS ttl, NULL::INT AS prio, NULL::TEXT AS change_date UNION SELECT id, 0 AS domain_id, LOWER(name) AS name, 'A'::TEXT AS type, primary_ip::TEXT AS content, 0 AS ttl, NULL::INT AS prio, last_modified::TEXT AS change_date FROM nodes INNER JOIN object_properties ON id = object_id; 

Result:

 $ nslookup > ns Server: 10.1.1.1 Address: 10.1.1.1#53 Name: ns.domain.local Address: 10.1.1.1 

The reverse zone can be implemented in a similar way, but it can be a little more interesting. Any host can have several interfaces with different addresses, so the name corresponding to the address can be composed of the actual host name and interface name. Then the last view will change as follows:

 CREATE OR REPLACE VIEW domains AS SELECT 0::INT AS id, 'domain.local'::TEXT AS name, NULL::TEXT AS master, NULL::INT as last_check, 'NATIVE'::TEXT as type, NULL::INT as notified_serial, NULL::TEXT as account; CREATE OR REPLACE VIEW records AS SELECT 0::INT AS id, 0 AS domain_id, 'domain.local' AS name, 'SOA'::TEXT AS type, 'ns.domain.local'::TEXT AS content, 0 AS ttl, NULL::INT AS prio, NULL::TEXT AS change_date UNION SELECT id, 0 AS domain_id, LOWER(name) AS name, 'A'::TEXT AS type, primary_ip::TEXT AS content, 0 AS ttl, NULL::INT AS prio, last_modified::TEXT AS change_date FROM nodes INNER JOIN object_properties ON id = object_id UNION SELECT 0::INT AS id, 0 AS domain_id, 'in-addr.arpa' AS name, 'SOA'::TEXT AS type, 'ns.domain.local'::TEXT AS content, 0 AS ttl, NULL::INT AS prio, NULL::TEXT AS change_date UNION SELECT interfaces.id, 0 AS domain_id, reverse_address(interfaces.ip_addr::INET) AS name, 'PTR'::TEXT AS type, (object_properties.name||'.'||interfaces.description)::TEXT AS content, 0 AS ttl, NULL::INT AS prio, last_modified::TEXT AS change_date FROM interfaces INNER JOIN nodes on nodes.id = interfaces.node_id INNER JOIN object_properties on nodes.id = object_id; 

Result:

 $ nslookup > 10.7.1.1 Server: 10.1.1.1 Address: 10.1.1.1#53 1.1.1.10.in-addr.arpa name = ns.eth0. 

The code mentions the reverse_address function, its implementation looks like this:

 CREATE OR REPLACE FUNCTION reverse_address(addr inet) RETURNS text AS $t$ DECLARE mask integer; parts text[]; part text; retval text; rounds integer; host text; BEGIN retval := ''; rounds := 0; mask := masklen(addr); host := host(addr); IF (family(addr) = 4) THEN IF (mask < 8) THEN rounds := 4; ELSIF (mask < 16) THEN rounds := 3; ELSIF (mask < 24) THEN rounds := 2; ELSIF (mask < 32) THEN rounds := 1; END IF; FOREACH part in ARRAY regexp_split_to_array(host, '\.') LOOP rounds := rounds + 1; IF (rounds <= 4) THEN retval := part || '.' || retval; END IF; END LOOP; RETURN retval || 'in-addr.arpa'; ELSE RETURN 'ip6.arpa'; END IF; END; $t$ LANGUAGE 'plpgsql' IMMUTABLE; 

And this is the only PostgreSQL-specific implementation part. Everything else can be done in a similar way on other DBMS, supported by NetXMS and PowerDNS.

Finally, about performance: this solution, of course, is not suitable for public DNS servers. For internal use - completely. At the first execution, all requests fit into 100 msec, then the cache works. There is a field for optimization using the parameters gpgsql - * - query instead of views, but for now we have enough.