Of course we cannot always share details about our work with customers, but nevertheless it is nice to show our technical achievements and share some of our implemented solutions.
Updating a single DNS record in PowerDNS is easy. There are several possibilities to do that:
But the problem is: What if you know you have hundreds or thousands of domains and you need to update the same kind of record (for example a CNAME, A or TXT record) for all domains?
This article describes how PowerDNS (with a MySQL backend) stores DNS records and how they are assigned to a domain name (zone) and how to use a special script (pdns-search-replace.sh) to do bulk DNS changes, which searches for a specific string in a record and replaces this found string with a replacement string (which can be empty).
Luckily the data structure inside the powerdns database is very sane. The amount of tables are small and the names are well chosen:
mysql> use powerdns;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_powerdns |
+--------------------+
| comments |
| cryptokeys |
| domainmetadata |
| domains |
| records |
| supermasters |
| tsigkeys |
+--------------------+
7 rows in set (0.00 sec)
It is pretty self-explaining what kind of data is stored in which table. DNS records can therefore be found in the records table, domains (zones) can be found in the domains table.
A typical DNS record looks like this:
mysql> SELECT * FROM records LIMIT 0,1;
+----+-----------+---------------+------+---------------+------+------+-------------+----------+-----------+------+
| id | domain_id | name | type | content | ttl | prio | change_date | disabled | ordername | auth |
+----+-----------+---------------+------+---------------+------+------+-------------+----------+-----------+------+
| 5 | 1 | infiniroot.com | A | 212.103.71.210 | 38400 | 0 | NULL | 0 | NULL | 1 |
+----+-----------+---------------+------+---------------+------+------+-------------+----------+-----------+------+
1 row in set (0.00 sec)
The most important columns here are the following:
Now that we know that for this particular DNS record, the related domain_id is 1, we can retrieve this value from the database, too:
mysql> SELECT * FROM domains WHERE id = 1;
+----+----------------+--------+------------+--------+-----------------+---------+
| id | name | master | last_check | type | notified_serial | account |
+----+----------------+--------+------------+--------+-----------------+---------+
| 1 | infiniroot.com | | NULL | MASTER | 2021021601 | |
+----+----------------+--------+------------+--------+-----------------+---------+
1 row in set (0.00 sec)
Now that we know how DNS records and their related domains are stored in the database, the records themselves can be tackled. A TXT record (for SPF) needs to be adjusted and a couple of entries removed from the list. But let's first search for the occurrence we need to replace (ip4:3.3.3.3):
# mysql -e "SELECT id,content FROM powerdns.records WHERE content LIKE '%ip4:3.3.3.3%'"
+-------+------------------------------------------------------------------------------------------------------------------+
| id | content |
+-------+------------------------------------------------------------------------------------------------------------------+
| 369 | "v=spf1 ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ip4:7.7.7.7/24 ~all" |
| 1416 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 3728 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 3744 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 4558 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 8800 | "v=spf1 ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 include:spf.example.com ~all" |
| 8852 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 8925 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9868 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9873 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9876 | "v=spf1 mx ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 include:spf.mandrillapp.com ~all" |
| 9881 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9884 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9888 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9898 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9905 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9908 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9913 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9918 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9923 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9928 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9933 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9936 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9949 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9957 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 9961 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 10015 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 10074 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 10080 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 10090 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 10115 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 10121 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
| 10127 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:3.3.3.3 ip4:4.4.4.4 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
+-------+------------------------------------------------------------------------------------------------------------------+
Yep, there are a couple SPF TXT record which hold that particular IP address. How can this IP be replaced with a new IP or be removed from the TXT record? If you know SQL, the first thought would probably be to UPDATE the record. However UPDATE requires that the full content is written back into the database. This means: First retrieve the existing record, manipulate the data (which is not exactly the same as you can see above), update the record with the manipulated data.
Luckily there's something easier: The MySQL built-in function replace(). The great advantage of this function is that it keeps the data of a field and works on string replacement, kind of comparable to a search/replace using sed on the command line. The other advantage is that it works not just on one database row but on the whole table. Let's try this:
mysql> UPDATE powerdns.records SET content = replace(content, 'ip4:3.3.3.3 ip4:4.4.4.4 ', '');
Query OK, 33 rows affected (0.05 sec)
The search string "ip4:3.3.3.3 ip4:4.4.4.4" was replaced by an empty string, therefore removing these two entries from the SPF TXT record. Verification:
mysql> SELECT id,content FROM records WHERE id = 10127;
+-------+---------------------------------------------------------------------+
| id | content |
+-------+---------------------------------------------------------------------+
| 10127 | "v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all" |
+-------+---------------------------------------------------------------------+
1 row in set (0.00 sec)
The SPF entries ip4:3.3.3.3 and ip4:4.4.4.4 were removed, the rest of the SPF TXT record is intact!
Now this domain must be identified (using domain_id column) and the zone's serial increased and then reloaded:
# pdnsutil increase-serial example.com
# pdns_control reload
And in case you have a PowerDNS master slave setup, launch a notify to inform the slaves to transfer the updated zone:
# pdns_control notify example.com
Now doing all this demands for internal understanding of the PowerDNS MySQL backend - and manual work. Wouldn't it be nice, if there were a script to do this?
All the steps described above are bundled together in a handy script pdns-search-replace.sh, which is publicly available on GitHub. It supports multiple parameters and allows to quickly run through the all DNS records matching the search string, defined using the -s parameter. All occurrences will be replaced by the replace string, defined using the -r parameter. Besides these two parameters, the database user (-u) is required, too.
Download the script from GitHub:
# wget https://raw.githubusercontent.com/Napsty/scripts/master/pdns/infoblox2pdns.sh
Or clone the full repository:
# git clone https://github.com/Napsty/scripts.git
Then make sure the permissions on the script are correct (they should already be set to 755) and execute the script with the required parameters:
root@powerdns:~/scripts# ./pdns-search-replace.sh -s "ip4:3.3.3.3 ip4:4.4.4.4 " -r "" -u root
Manipulating DNS records can cause severe damage in your zone. Would you like to create a backup first? Y/N ? Y
Saving MySQL dump of powerdns in /tmp/powerdns.1614693776.sql
Found 33 domains that will be affected of 'ip4:3.3.3.3 ip4:4.4.4.4 ' being replaced by ''
Increasing serial (2020062703) for domain example.com
SOA serial for zone example.com set to 2021030201
PowerDNS reloading zones
Ok
Sending notify to slave(s)
Added to queue
Increasing serial (2019110501) for domain example2.com
SOA serial for zone example2.com set to 2021030201
PowerDNS reloading zones
Ok
Sending notify to slave(s)
Added to queue
Increasing serial (2019110501) for domain example3.com
SOA serial for zone example3.com set to 2021030201
PowerDNS reloading zones
Ok
Sending notify to slave(s)
Added to queue
As you can see, the script also asks to create a backup (database dump) first. If the script is executed non-interactively, this prompt can be skipped using the -B (for batch) parameter.
The updated TXT record can now be queried on all the DNS servers:
ckadm@mintp ~ $ dig -t TXT example.com @ns1.example.com +short
"v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"
ckadm@mintp ~ $ dig -t TXT example.com @ns2.example.com +short
"v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"
ckadm@mintp ~ $ dig -t TXT example.com @ns3.example.com +short
"v=spf1 mx ip4:1.1.1.1 ip4:2.2.2.2 ip4:5.5.5.5/22 ip4:6.6.6.6 ~all"
The SPF entries ip4:3.3.3.3 and ip4:4.4.4.4 were successfully removed.