From patchwork Fri May 14 16:55:28 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: 4274 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 4FhZQv2Ytnz44RX for ; Fri, 14 May 2021 16:55:35 +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 4FhZQt3CMNzxV; Fri, 14 May 2021 16:55:34 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4FhZQs50xqz2xlC; Fri, 14 May 2021 16:55:33 +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) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "mail01.haj.ipfire.org", Issuer "R3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4FhZQr4kDFz2xbr for ; Fri, 14 May 2021 16:55:32 +0000 (UTC) Received: from [127.0.0.1] (localhost [127.0.0.1]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits) key-exchange X25519 server-signature ECDSA (P-384) server-digest SHA384) (No client certificate requested) by mail01.ipfire.org (Postfix) with ESMTPSA id 4FhZQq3g7LzxV for ; Fri, 14 May 2021 16:55:31 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1621011332; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding; bh=vfw4mIpCwbUBx2b1hwOSdHeTBnXy+DDNFENODGhW5os=; b=sAH6233ChBQQJUt+dNNCn41S0H4MRbxokoODjHu1HyOJR3TPp0R+sGjQoYKNDJZ33bweKU ytyMuOef/C2KYxBQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1621011332; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:mime-version:mime-version:content-type:content-type: content-transfer-encoding:content-transfer-encoding; bh=vfw4mIpCwbUBx2b1hwOSdHeTBnXy+DDNFENODGhW5os=; b=DjKwjU31k9U5sANpxRucqULPw50aSCb7M9noDXxqSRqKixIIWMPCKqAKRzRjvPhskT3/ax YZiI094xMAbsvIaPW5rDyxQXoOZiSfRTj4WG12rbYi8kzjXrwEattS6nSRK3f7u7kZrnlp zowCUm4+sFoYCMuAc9JVJQAR2neDJ9rl7OlpalguNjHOErDe0GxW8IOOFYtN2VlggMuYTF qD6TtfD5gfdH6HRdcooAStXwEqNa4DkLimbX/Y3H7dpf5M64313XUZbk816xTNDFvd5/oM XVIX9DQ5otnCgiXHf0WGTxggUXCijG2K5PDwN++Tkz0+KQAH2L9398JIT6eU7A== To: "IPFire: Location" From: =?utf-8?q?Peter_M=C3=BCller?= Subject: [PATCH] location-importer.in: track original countries as well Message-ID: Date: Fri, 14 May 2021 18:55:28 +0200 MIME-Version: 1.0 Content-Language: en-US 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 helps us to determine how many network objects have more than one country set, and what their original country code set looked like. Signed-off-by: Peter Müller --- src/python/location-importer.in | 53 ++++++++++++++++++++------------- 1 file changed, 32 insertions(+), 21 deletions(-) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index e5f55af..c7162cf 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -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, original_countries text[]); 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); @@ -377,7 +377,7 @@ class CLI(object): 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, original_countries text[]) ON COMMIT DROP; CREATE INDEX _rirdata_search ON _rirdata USING BTREE(family(network), masklen(network)); CREATE UNIQUE INDEX _rirdata_network ON _rirdata(network); @@ -407,8 +407,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, original_countries) \ + SELECT network, country, original_countries 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 +421,8 @@ class CLI(object): WITH candidates AS ( SELECT _rirdata.network, - _rirdata.country + _rirdata.country, + _rirdata.original_countries FROM _rirdata WHERE @@ -434,6 +435,7 @@ class CLI(object): DISTINCT ON (c.network) c.network, c.country, + c.original_countries, masklen(networks.network), networks.country AS parent_country FROM @@ -447,10 +449,11 @@ class CLI(object): masklen(networks.network) DESC NULLS LAST ) INSERT INTO - networks(network, country) + networks(network, country, original_countries) SELECT network, - country + country, + original_countries FROM filtered WHERE @@ -617,28 +620,36 @@ class CLI(object): inetnum[key] = [ipaddress.ip_network(val, strict=False)] elif key == "country": - inetnum[key] = val.upper() + # Catch RIR data objects with more than one country code... + if not key in inetnum.keys(): + inetnum[key] = [] + else: + if val.upper() in inetnum.get("country"): + # ... but keep this list distinct... + continue + + inetnum[key].append(val.upper()) # Skip empty objects if not inetnum or not "country" in inetnum: return + # Prepare skipping objects with unknown country codes... + invalidcountries = [singlecountry for singlecountry in inetnum.get("country") if singlecountry not in validcountries] + # Iterate through all networks enumerated from above, check them for plausibility and insert # them into the database, if _check_parsed_network() succeeded for single_network in inetnum.get("inet6num") or inetnum.get("inetnum"): if self._check_parsed_network(single_network): - - # Skip objects with unknown country codes - to avoid log spam for invalid or too small - # networks, this check is - kinda ugly - done at this point - if validcountries and inetnum.get("country") not in validcountries: - log.warning("Skipping network with bogus country '%s': %s" % \ - (inetnum.get("country"), inetnum.get("inet6num") or inetnum.get("inetnum"))) + # Skip objects with unknown country codes if they are valid to avoid log spam... + if validcountries and invalidcountries: + log.warning("Skipping network with bogus countr(y|ies) %s (original countries: %s): %s" % \ + (invalidcountries, inetnum.get("country"), inetnum.get("inet6num") or inetnum.get("inetnum"))) 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, original_countries) \ + VALUES(%s, %s, %s) ON CONFLICT (network) DO UPDATE SET country = excluded.country", + "%s" % single_network, inetnum.get("country")[0], inetnum.get("country"), ) def _parse_org_block(self, block): @@ -729,10 +740,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, original_countries) \ + VALUES(%s, %s, %s) ON CONFLICT (network) DO \ UPDATE SET country = excluded.country", - "%s" % network, country, + "%s" % network, country, [country], ) def handle_update_announcements(self, ns):