Question

The author does a great job of explaining AXFR (Full Zone Transfer) for the example zone identified by the APEX Zone from "zonetransfer.me" his NameServer nsztm1.digi.ninja.

The project is to consume this file and ingest it into a PostgreSQL model using a Python psycopg2 based client with SQL statements or COPY, or possibly stored procedures or a combination.

The data are very simple, so the model may be as simple as a handful of tables.

The data format is simple so you can parse it easily in Python in order to ingest it, or format it into a CSV in order to use the COPY command in Python or via psql to bring it within the database and then execute a further SQL or PL/pgSQL to add it to the target table.

Along with a working solution, assemble a report which illustrates how you got the data into the database including an ERD.

Submit at least 3 artifacts (but may be more):

The code, including the DDL for the data model

Evidence of the output, ideally as text capture but screenshots are also acceptable

The report itself which explains the steps and may include the DDL / ERD and execution evidence

Data

zonetransfer.me. 7200 IN SOA nsztm1.digi.ninja. robin.digi.ninja. 2014101601 172800 900 1209600 3600 zonetransfer.me. 300 IN HINFO "Casio fx-700G" "Windows XP" zonetransfer.me. 301 IN TXT "google-site-verification=tyP28J7JAUHA9fw2sHXMgcCC0I6XBmmoVi04VlMewxA" zonetransfer.me. 7200 IN MX 0 ASPMX.L.GOOGLE.COM. zonetransfer.me. 7200 IN MX 10 ALT1.ASPMX.L.GOOGLE.COM. zonetransfer.me. 7200 IN MX 10 ALT2.ASPMX.L.GOOGLE.COM. zonetransfer.me. 7200 IN MX 20 ASPMX2.GOOGLEMAIL.COM. zonetransfer.me. 7200 IN MX 20 ASPMX3.GOOGLEMAIL.COM. zonetransfer.me. 7200 IN MX 20 ASPMX4.GOOGLEMAIL.COM. zonetransfer.me. 7200 IN MX 20 ASPMX5.GOOGLEMAIL.COM. zonetransfer.me. 7200 IN A 217.147.180.162 zonetransfer.me. 7200 IN NS nsztm1.digi.ninja. zonetransfer.me. 7200 IN NS nsztm2.digi.ninja. _sip._tcp.zonetransfer.me. 14000 IN SRV 0 0 5060 www.zonetransfer.me. 164.180.147.217.IN-ADDR.ARPA.zonetransfer.me. 7200 IN PTR www.zonetransfer.me. asfdbauthdns.zonetransfer.me. 7900 IN AFSDB 1 asfdbbox.zonetransfer.me. asfdbbox.zonetransfer.me. 7200 IN A 127.0.0.1 asfdbvolume.zonetransfer.me. 7800 IN AFSDB 1 asfdbbox.zonetransfer.me. canberra-office.zonetransfer.me. 7200 IN A 202.14.81.230 cmdexec.zonetransfer.me. 300 IN TXT "\; ls" contact.zonetransfer.me. 2592000 IN TXT "Remember to call or email Pippa on +44 123 4567890 or pippa@zonetransfer.me when making DNS changes" dc-office.zonetransfer.me. 7200 IN A 143.228.181.132 deadbeef.zonetransfer.me. 7201 IN AAAA dead:beaf:: dr.zonetransfer.me. 300 IN LOC 53 20 56.558 N 1 38 33.526 W 0.00m 1m 10000m 10m DZC.zonetransfer.me. 7200 IN TXT "AbCdEfG" email.zonetransfer.me. 2222 IN NAPTR 1 1 "P" "E2U+email" "" email.zonetransfer.me.zonetransfer.me. email.zonetransfer.me. 7200 IN A 74.125.206.26 Info.zonetransfer.me. 7200 IN TXT "ZoneTransfer.me service provided by Robin Wood - robin@digi.ninja. See http://digi.ninja/projects/zonetransferme.php for more information." internal.zonetransfer.me. 300 IN NS intns1.zonetransfer.me. internal.zonetransfer.me. 300 IN NS intns2.zonetransfer.me. intns1.zonetransfer.me. 300 IN A 167.88.42.94 intns2.zonetransfer.me. 300 IN A 167.88.42.94 office.zonetransfer.me. 7200 IN A 4.23.39.254 ipv6actnow.org.zonetransfer.me. 7200 IN AAAA 2001:67c:2e8:11::c100:1332 owa.zonetransfer.me. 7200 IN A 207.46.197.32 robinwood.zonetransfer.me. 302 IN TXT "Robin Wood" rp.zonetransfer.me. 321 IN RP robin.zonetransfer.me. robinwood.zonetransfer.me. sip.zonetransfer.me. 3333 IN NAPTR 2 3 "P" "E2U+sip" "!^.*$!sip:customer-service@zonetransfer.me!" . sqli.zonetransfer.me. 300 IN TXT "' or 1=1 --" sshock.zonetransfer.me. 7200 IN TXT "() { :]}\; echo ShellShocked" staging.zonetransfer.me. 7200 IN CNAME www.sydneyoperahouse.com. alltcpportsopen.firewall.test.zonetransfer.me. 301 IN A 127.0.0.1 testing.zonetransfer.me. 301 IN CNAME www.zonetransfer.me. vpn.zonetransfer.me. 4000 IN A 174.36.59.154 www.zonetransfer.me. 7200 IN A 217.147.180.162 xss.zonetransfer.me. 300 IN TXT "'>" zonetransfer.me. 7200 IN SOA nsztm1.digi.ninja. robin.digi.ninja. 2014101601 172800 900 1209600 3600

I need

1. The python and postgreSQL code, including the DDL for the data model

2. Evidence of the output, ideally as text capture but screenshots are also acceptable

3. The report itself which explains the steps and may include the DDL / ERD and execution evidence

4. A ERD for the whole model