From patchwork Sat May 15 11:57:04 2021 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Patchwork-Submitter: =?utf-8?q?Peter_M=C3=BCller?= X-Patchwork-Id: 4277 Return-Path: Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "R3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4Fj3m55jdfz3wbh for ; Sat, 15 May 2021 11:57:09 +0000 (UTC) Received: from mail02.haj.ipfire.org (mail02.haj.ipfire.org [172.28.1.201]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail02.haj.ipfire.org", Issuer "R3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4Fj3m51fbrz6YH; Sat, 15 May 2021 11:57:09 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4Fj3m50NZrz2yj1; Sat, 15 May 2021 11:57:09 +0000 (UTC) Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "R3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4Fj3m40Fp9z2xmd for ; Sat, 15 May 2021 11:57:08 +0000 (UTC) Received: from location02.haj.ipfire.org (location02.haj.ipfire.org [172.28.1.170]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "location02.haj.ipfire.org", Issuer "R3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4Fj3m31V5Gzjl; Sat, 15 May 2021 11:57:07 +0000 (UTC) Received: by location02.haj.ipfire.org (Postfix, from userid 0) id 4Fj3m30gq0zyq0; Sat, 15 May 2021 11:57:07 +0000 (UTC) From: =?utf-8?q?Peter_M=C3=BCller?= To: location@lists.ipfire.org Subject: [PATCH 1/2] location-importer.in: keep track of sources for networks, ASNs, and organisations Date: Sat, 15 May 2021 11:57:04 +0000 Message-Id: <20210515115705.9794-1-peter.mueller@ipfire.org> X-Mailer: git-send-email 2.20.1 MIME-Version: 1.0 X-BeenThere: location@lists.ipfire.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: location-bounces@lists.ipfire.org Sender: "Location" This allows us to trace back concrete changes or anomalies to their RIR source, without having to parse everything again. Further, it enables adding 3rd party sources such as IP feeds from Amazon, without loosing track of the changes introduced by them. Depending on the individual systems, it might be necessary to DROP the tables for autnums and networks first. Signed-off-by: Peter Müller --- src/python/location-importer.in | 93 +++++++++++++++++---------------- 1 file changed, 49 insertions(+), 44 deletions(-) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index e5f55af..fd2bde1 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -155,7 +155,7 @@ class CLI(object): CREATE INDEX IF NOT EXISTS announcements_search ON announcements USING GIST(network inet_ops); -- autnums - CREATE TABLE IF NOT EXISTS autnums(number bigint, name text NOT NULL); + CREATE TABLE IF NOT EXISTS autnums(number bigint, name text NOT NULL, source text NOT NULL); CREATE UNIQUE INDEX IF NOT EXISTS autnums_number ON autnums(number); -- countries @@ -164,7 +164,7 @@ class CLI(object): CREATE UNIQUE INDEX IF NOT EXISTS countries_country_code ON countries(country_code); -- networks - CREATE TABLE IF NOT EXISTS networks(network inet, country text); + CREATE TABLE IF NOT EXISTS networks(network inet, country text, source text NOT NULL); CREATE UNIQUE INDEX IF NOT EXISTS networks_network ON networks(network); CREATE INDEX IF NOT EXISTS networks_family ON networks USING BTREE(family(network)); CREATE INDEX IF NOT EXISTS networks_search ON networks USING GIST(network inet_ops); @@ -369,15 +369,15 @@ class CLI(object): with self.db.transaction(): # Create some temporary tables to store parsed data self.db.execute(""" - CREATE TEMPORARY TABLE _autnums(number integer, organization text) + CREATE TEMPORARY TABLE _autnums(number integer, organization text, source text NOT NULL) ON COMMIT DROP; CREATE UNIQUE INDEX _autnums_number ON _autnums(number); - CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL) + CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL, source text NOT NULL) ON COMMIT DROP; CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle); - CREATE TEMPORARY TABLE _rirdata(network inet NOT NULL, country text NOT NULL) + CREATE TEMPORARY TABLE _rirdata(network inet NOT NULL, country text NOT NULL, source text NOT NULL) ON COMMIT DROP; CREATE INDEX _rirdata_search ON _rirdata USING BTREE(family(network), masklen(network)); CREATE UNIQUE INDEX _rirdata_network ON _rirdata(network); @@ -395,10 +395,11 @@ class CLI(object): for row in rows: validcountries.append(row.country_code) - for source in location.importer.WHOIS_SOURCES: - with downloader.request(source, return_blocks=True) as f: - for block in f: - self._parse_block(block, validcountries) + for source_key in location.importer.WHOIS_SOURCES: + for single_url in location.importer.WHOIS_SOURCES[source_key]: + with downloader.request(single_url, return_blocks=True) as f: + for block in f: + self._parse_block(block, source_key, validcountries) # Process all parsed networks from every RIR we happen to have access to, # insert the largest network chunks into the networks table immediately... @@ -407,8 +408,8 @@ class CLI(object): for family in (row.family for row in families): smallest = self.db.get("SELECT MIN(masklen(network)) AS prefix FROM _rirdata WHERE family(network) = %s", family) - self.db.execute("INSERT INTO networks(network, country) \ - SELECT network, country FROM _rirdata WHERE masklen(network) = %s AND family(network) = %s", smallest.prefix, family) + self.db.execute("INSERT INTO networks(network, country, source) \ + SELECT network, country, source FROM _rirdata WHERE masklen(network) = %s AND family(network) = %s", smallest.prefix, family) # ... determine any other prefixes for this network family, ... prefixes = self.db.query("SELECT DISTINCT masklen(network) AS prefix FROM _rirdata \ @@ -421,7 +422,8 @@ class CLI(object): WITH candidates AS ( SELECT _rirdata.network, - _rirdata.country + _rirdata.country, + _rirdata.source FROM _rirdata WHERE @@ -434,6 +436,7 @@ class CLI(object): DISTINCT ON (c.network) c.network, c.country, + c.source, masklen(networks.network), networks.country AS parent_country FROM @@ -447,10 +450,11 @@ class CLI(object): masklen(networks.network) DESC NULLS LAST ) INSERT INTO - networks(network, country) + networks(network, country, source) SELECT network, - country + country, + source FROM filtered WHERE @@ -462,19 +466,20 @@ class CLI(object): ) self.db.execute(""" - INSERT INTO autnums(number, name) - SELECT _autnums.number, _organizations.name FROM _autnums + INSERT INTO autnums(number, name, source) + SELECT _autnums.number, _organizations.name, _organizations.source FROM _autnums JOIN _organizations ON _autnums.organization = _organizations.handle ON CONFLICT (number) DO UPDATE SET name = excluded.name; """) # Download all extended sources - for source in location.importer.EXTENDED_SOURCES: - with self.db.transaction(): - # Download data - with downloader.request(source) as f: - for line in f: - self._parse_line(line, validcountries) + for source_key in location.importer.EXTENDED_SOURCES: + for single_url in location.importer.EXTENDED_SOURCES[source_key]: + with self.db.transaction(): + # Download data + with downloader.request(single_url) as f: + for line in f: + self._parse_line(line, source_key, validcountries) def _check_parsed_network(self, network): """ @@ -539,23 +544,23 @@ class CLI(object): # be suitable for libloc consumption... return True - def _parse_block(self, block, validcountries = None): + def _parse_block(self, block, source_key, validcountries = None): # Get first line to find out what type of block this is line = block[0] # aut-num if line.startswith("aut-num:"): - return self._parse_autnum_block(block) + return self._parse_autnum_block(block, source_key) # inetnum if line.startswith("inet6num:") or line.startswith("inetnum:"): - return self._parse_inetnum_block(block, validcountries) + return self._parse_inetnum_block(block, source_key, validcountries) # organisation elif line.startswith("organisation:"): - return self._parse_org_block(block) + return self._parse_org_block(block, source_key) - def _parse_autnum_block(self, block): + def _parse_autnum_block(self, block, source_key): autnum = {} for line in block: # Split line @@ -574,13 +579,13 @@ class CLI(object): return # Insert into database - self.db.execute("INSERT INTO _autnums(number, organization) \ - VALUES(%s, %s) ON CONFLICT (number) DO UPDATE SET \ + self.db.execute("INSERT INTO _autnums(number, organization, source) \ + VALUES(%s, %s, %s) ON CONFLICT (number) DO UPDATE SET \ organization = excluded.organization", - autnum.get("asn"), autnum.get("org"), + autnum.get("asn"), autnum.get("org"), source_key, ) - def _parse_inetnum_block(self, block, validcountries = None): + def _parse_inetnum_block(self, block, source_key, validcountries = None): log.debug("Parsing inetnum block:") inetnum = {} @@ -636,12 +641,12 @@ class CLI(object): break # Everything is fine here, run INSERT statement... - self.db.execute("INSERT INTO _rirdata(network, country) \ - VALUES(%s, %s) ON CONFLICT (network) DO UPDATE SET country = excluded.country", - "%s" % single_network, inetnum.get("country"), + self.db.execute("INSERT INTO _rirdata(network, country, source) \ + VALUES(%s, %s, %s) ON CONFLICT (network) DO UPDATE SET country = excluded.country", + "%s" % single_network, inetnum.get("country"), source_key, ) - def _parse_org_block(self, block): + def _parse_org_block(self, block, source_key): org = {} for line in block: # Split line @@ -656,13 +661,13 @@ class CLI(object): if not org: return - self.db.execute("INSERT INTO _organizations(handle, name) \ - VALUES(%s, %s) ON CONFLICT (handle) DO \ + self.db.execute("INSERT INTO _organizations(handle, name, source) \ + VALUES(%s, %s, %s) ON CONFLICT (handle) DO \ UPDATE SET name = excluded.name", - org.get("organisation"), org.get("org-name"), + org.get("organisation"), org.get("org-name"), source_key, ) - def _parse_line(self, line, validcountries = None): + def _parse_line(self, line, source_key, validcountries = None): # Skip version line if line.startswith("2"): return @@ -689,9 +694,9 @@ class CLI(object): return if type in ("ipv6", "ipv4"): - return self._parse_ip_line(country_code, type, line) + return self._parse_ip_line(country_code, type, line, source_key) - def _parse_ip_line(self, country, type, line): + def _parse_ip_line(self, country, type, line, source_key): try: address, prefix, date, status, organization = line.split("|") except ValueError: @@ -729,10 +734,10 @@ class CLI(object): if not self._check_parsed_network(network): return - self.db.execute("INSERT INTO networks(network, country) \ - VALUES(%s, %s) ON CONFLICT (network) DO \ + self.db.execute("INSERT INTO networks(network, country, source) \ + VALUES(%s, %s, %s) ON CONFLICT (network) DO \ UPDATE SET country = excluded.country", - "%s" % network, country, + "%s" % network, country, source_key, ) def handle_update_announcements(self, ns): From patchwork Sat May 15 11:57:05 2021 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 8bit X-Patchwork-Submitter: =?utf-8?q?Peter_M=C3=BCller?= X-Patchwork-Id: 4276 Return-Path: Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "R3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4Fj3m53R90z3wbf for ; Sat, 15 May 2021 11:57:09 +0000 (UTC) Received: from mail02.haj.ipfire.org (mail02.haj.ipfire.org [172.28.1.201]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "mail02.haj.ipfire.org", Issuer "R3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4Fj3m50cLMzjl; Sat, 15 May 2021 11:57:09 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4Fj3m50CfZz2xPx; Sat, 15 May 2021 11:57:09 +0000 (UTC) Received: from mail01.ipfire.org (mail01.haj.ipfire.org [172.28.1.202]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) client-signature ECDSA (P-384)) (Client CN "mail01.haj.ipfire.org", Issuer "R3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4Fj3m36vntz2xPx for ; Sat, 15 May 2021 11:57:07 +0000 (UTC) Received: from location02.haj.ipfire.org (location02.haj.ipfire.org [172.28.1.170]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "location02.haj.ipfire.org", Issuer "R3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4Fj3m31fNHzpJ; Sat, 15 May 2021 11:57:07 +0000 (UTC) Received: by location02.haj.ipfire.org (Postfix, from userid 0) id 4Fj3m30p99zyXN; Sat, 15 May 2021 11:57:07 +0000 (UTC) From: =?utf-8?q?Peter_M=C3=BCller?= To: location@lists.ipfire.org Subject: [PATCH 2/2] importer.py: add source information for RIR data feeds Date: Sat, 15 May 2021 11:57:05 +0000 Message-Id: <20210515115705.9794-2-peter.mueller@ipfire.org> X-Mailer: git-send-email 2.20.1 In-Reply-To: <20210515115705.9794-1-peter.mueller@ipfire.org> References: <20210515115705.9794-1-peter.mueller@ipfire.org> MIME-Version: 1.0 X-BeenThere: location@lists.ipfire.org X-Mailman-Version: 2.1.29 Precedence: list List-Id: List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Signed-off-by: Peter Müller --- src/python/importer.py | 66 +++++++++++++++++++++++++++--------------- 1 file changed, 42 insertions(+), 24 deletions(-) diff --git a/src/python/importer.py b/src/python/importer.py index 5f46bc3..4c8406c 100644 --- a/src/python/importer.py +++ b/src/python/importer.py @@ -25,50 +25,68 @@ import urllib.request log = logging.getLogger("location.importer") log.propagate = 1 -WHOIS_SOURCES = ( +WHOIS_SOURCES = { # African Network Information Centre - "https://ftp.afrinic.net/pub/pub/dbase/afrinic.db.gz", + "AFRINIC": [ + "https://ftp.afrinic.net/pub/pub/dbase/afrinic.db.gz" + ], # Asia Pacific Network Information Centre - "https://ftp.apnic.net/apnic/whois/apnic.db.inet6num.gz", - "https://ftp.apnic.net/apnic/whois/apnic.db.inetnum.gz", - #"https://ftp.apnic.net/apnic/whois/apnic.db.route6.gz", - #"https://ftp.apnic.net/apnic/whois/apnic.db.route.gz", - "https://ftp.apnic.net/apnic/whois/apnic.db.aut-num.gz", - "https://ftp.apnic.net/apnic/whois/apnic.db.organisation.gz", + "APNIC": [ + "https://ftp.apnic.net/apnic/whois/apnic.db.inet6num.gz", + "https://ftp.apnic.net/apnic/whois/apnic.db.inetnum.gz", + #"https://ftp.apnic.net/apnic/whois/apnic.db.route6.gz", + #"https://ftp.apnic.net/apnic/whois/apnic.db.route.gz", + "https://ftp.apnic.net/apnic/whois/apnic.db.aut-num.gz", + "https://ftp.apnic.net/apnic/whois/apnic.db.organisation.gz" + ], # American Registry for Internet Numbers # XXX there is nothing useful for us in here - #"https://ftp.arin.net/pub/rr/arin.db", + # ARIN: [ + # "https://ftp.arin.net/pub/rr/arin.db" + # ], # Latin America and Caribbean Network Information Centre # XXX ??? # Réseaux IP Européens - "https://ftp.ripe.net/ripe/dbase/split/ripe.db.inet6num.gz", - "https://ftp.ripe.net/ripe/dbase/split/ripe.db.inetnum.gz", - #"https://ftp.ripe.net/ripe/dbase/split/ripe.db.route6.gz", - #"https://ftp.ripe.net/ripe/dbase/split/ripe.db.route.gz", - "https://ftp.ripe.net/ripe/dbase/split/ripe.db.aut-num.gz", - "https://ftp.ripe.net/ripe/dbase/split/ripe.db.organisation.gz", -) - -EXTENDED_SOURCES = ( + "RIPE": [ + "https://ftp.ripe.net/ripe/dbase/split/ripe.db.inet6num.gz", + "https://ftp.ripe.net/ripe/dbase/split/ripe.db.inetnum.gz", + #"https://ftp.ripe.net/ripe/dbase/split/ripe.db.route6.gz", + #"https://ftp.ripe.net/ripe/dbase/split/ripe.db.route.gz", + "https://ftp.ripe.net/ripe/dbase/split/ripe.db.aut-num.gz", + "https://ftp.ripe.net/ripe/dbase/split/ripe.db.organisation.gz" + ], +} + +EXTENDED_SOURCES = { # African Network Information Centre - #"https://ftp.afrinic.net/pub/stats/afrinic/delegated-afrinic-extended-latest", + # "ARIN": [ + # "https://ftp.afrinic.net/pub/stats/afrinic/delegated-afrinic-extended-latest" + # ], # Asia Pacific Network Information Centre - #"https://ftp.apnic.net/apnic/stats/apnic/delegated-apnic-extended-latest", + # "APNIC": [ + # "https://ftp.apnic.net/apnic/stats/apnic/delegated-apnic-extended-latest" + # ], # American Registry for Internet Numbers - "https://ftp.arin.net/pub/stats/arin/delegated-arin-extended-latest", + "ARIN": [ + "https://ftp.arin.net/pub/stats/arin/delegated-arin-extended-latest" + ], # Latin America and Caribbean Network Information Centre - "https://ftp.lacnic.net/pub/stats/lacnic/delegated-lacnic-extended-latest", + "LACNIC": [ + "https://ftp.lacnic.net/pub/stats/lacnic/delegated-lacnic-extended-latest" + ], # Réseaux IP Européens - #"https://ftp.ripe.net/pub/stats/ripencc/delegated-ripencc-extended-latest", -) + # "RIPE": [ + # "https://ftp.ripe.net/pub/stats/ripencc/delegated-ripencc-extended-latest" + # ], +} class Downloader(object): def __init__(self):