[Pdns-users] Adding multiple records to all zones at once

[Klaus Darilion]

Von: Andrea Biancalani <a.biancalani at conmet.it>
Gesendet: Freitag, 13. Oktober 2023 11:16
An: All about using and deploying powerdns <pdns-users at mailman.powerdns.com>
Cc: Klaus Darilion <klaus.darilion at nic.at>
Betreff: Re: [Pdns-users] Adding multiple records to all zones at once


Thanks to Klaus & Chris to help me figure out how to go ahead.
I'm not that good using MySQL, I'm not a backend dev , but I know how to use bash scripting to reach my goals (even if that is a longer road...).

For whom interested in doing something similar this is my free to use bash script to solve (change values to your needs):


#!/bin/bash

clear

domain=""
autodiscover_value="<AUTODISCOVER HOSTNAME>"
autoconfig_value="<AUTOCONFIG HOSTNAME>"

# GET ALL DOMAIN_IDs

mysql <<EOF > all_ids

use powerdns;
select domain_id from records;

EOF

# GET DOMAINS_ID WHICH ALREADY CONTAINS MISSING VALUES

mysql <<EOF > excluded_ids

use powerdns;
select domain_id from records where content like '%autodiscover%' and (type = 'cname' or type = 'srv');
select domain_id from records where content like '%autoconfig%' and (type = 'cname' or type = 'srv');

EOF

# EXCLUDING FROM ALL DOMAIN_ID THOSE THAT INCLUDES ALREADY AN AUTOCONFIG/AUTODISCOVER RECORD IN IT

grep -vf excluded_ids all_ids|sort --version-sort -f|uniq|grep -v domain_id > good_ids

# PUSHING DIFFS IN DB

for i in $(cat good_ids);
do

# GET DOMAIN NAME FROM DOMAIN_ID IN DB

mysql <<EOF > domain_name
use powerdns;
select name from domains where id = '$i';
EOF

# SET A VARIABLE TO LOOP THROUGH

domain=$(cat domain_name|grep "\.")

# DEBUG: UNCOMMENT IF YOU WANT JUST VIEW QUERY OUTPUT. COMMENT OUT ACTION QUERIES TOO!!

# echo "insert into records(domain_id,name,type,content,ttl) VALUES ('$i','autoconfig.$domain','CNAME','$autoconfig_value','3600');"
# echo "insert into records(domain_id,name,type,content,ttl) VALUES ('$i','autodiscover.$domain','CNAME','$autodiscover_value','3600');"

### ACTION QUERIES ###

echo "Working on : $domain"

mysql <<EOF
use powerdns;
insert into records(domain_id,name,type,content,ttl) VALUES ('$i','autoconfig.$domain','CNAME','$autoconfig_value','3600');
insert into records(domain_id,name,type,content,ttl) VALUES ('$i','autodiscover.$domain','CNAME','$autodiscover_value','3600');
EOF

######################

done

rm all_ids excluded_ids good_ids domain_name

echo -e "\n\n"
echo "Now if you want to increase zones serial use this loop:"
echo ""
echo "for i in $(pdnsutil list-all-zones|grep -v in-addr.arpa|sort); do pdnsutil increase-serial $i; done"

In this loop you should add "pdnsutil rectify-zone $i", or after the loop make a "pdnsutil rectify-all-zones"

regards
Klaus

echo ""
echo "Slaves using AXFER will be automatically updated."


Already tested and working. Goal achieved ;)

Have a nice day everybody.
Andrea

Il 13/10/2023 09:16, Klaus Darilion via Pdns-users ha scritto:

Why not do something along the lines of (PostgreSQL syntax as that's

what I know, and completely untested):



INSERT INTO records (domain_id, name, type, content, ttl)

   SELECT domain.id, 'autoconfig.' || domains.name, 'CNAME, '<my autoconfig

zone>, 3600

       FROM domains

       JOIN records ON domains.id = records.domain_id

       WHERE domains.id NOT IN (

           SELECT domains_id FROM records

               JOIN domains ON domains.id = records.domain_id

               WHERE name = 'autoconfig.' || domains.name );



You may want to just run the SELECT first to see that it gives you what

you want.



When you insert records directly into the DB, the zone may be broken (NSEC, NSEC3 ordername, empty non-terminal RRs ...).



Therefore you have to call "rectify" for every zone that was changed and PowerDNS will check and if necessary fix the zone.



regards

Klaus

_______________________________________________

Pdns-users mailing list

Pdns-users at mailman.powerdns.com<mailto:Pdns-users at mailman.powerdns.com>

https://mailman.powerdns.com/mailman/listinfo/pdns-users

--
Andrea Biancalani - Rep. Commerciale e Tecnico
Connessioni Metropolitane srl
Via G. Valentini, 14 - Prato (PO) - 59100
Sito web: https://www.conmet.it
Tel. 0574 536553
Fax. 0574 536554
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mailman.powerdns.com/pipermail/pdns-users/attachments/20231013/905a6d4c/attachment.htm>