From patchwork Sun Sep 6 09:47:59 2020 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: 3433 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) server-digest SHA384 client-signature ECDSA (P-384) client-digest SHA384) (Client CN "mail01.haj.ipfire.org", Issuer "Let's Encrypt Authority X3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4Bkmnr5H57z3x3L for ; Sun, 6 Sep 2020 09:48:48 +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 "Let's Encrypt Authority X3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4Bkmnr1ycXzwf; Sun, 6 Sep 2020 09:48:48 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4Bkmnr0sxCz2xdN; Sun, 6 Sep 2020 09:48:48 +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 "Let's Encrypt Authority X3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4Bkmnp5dw6z2yK0 for ; Sun, 6 Sep 2020 09:48:46 +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) (Client did not present a certificate) by mail01.ipfire.org (Postfix) with ESMTPSA id 4Bkmnm4hCQzkg for ; Sun, 6 Sep 2020 09:48:44 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1599385726; 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=SMv6NkwQLnIZ8ZSjmIfXjGzSvJ8nutviy51XK7oiQdo=; b=lNKnpzJXJF/I/zsUwB6NCRPVWTrLYMFRfTFlizmX3Vpi4a62qi+q0r5v1mlgdwxadaltx+ AFNp0LnTJm/B+5BA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1599385726; 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=SMv6NkwQLnIZ8ZSjmIfXjGzSvJ8nutviy51XK7oiQdo=; b=FcLKCR3WW9ILe4SOI1JUT0mW5cuxhzh/oagsSJ7gkHHcu5/yTeXAqmF7dQeturimeww2Vs jugooZkbxWyohO8AzBza95ELf/VLo622SX6luEKYNboawJKFZKmTALWRB3q8ufWsWn2Wwf SOii/A6Iw+toFHAlsXXlxg6tR9+9X1B6WiHHfcC87sgpceFhJ3P5LqewFYuaT6578KVvAJ F0Ap0FnlC/Z2PQKG4sOJBh9afpceiTC5IyAxpopCfH9znvzwiKetmlqhdr17+XYn8zDJwa I/WTecxN6mxOovkpWxHB0QpTiA+rJ8snsyIrW6TC1NSIQsYFuJz6FIfYG3TL1Q== To: location@lists.ipfire.org From: =?utf-8?q?Peter_M=C3=BCller?= Subject: [PATCH 1/3] location-importer.in: avoid violating NOT NULL constraints during JOIN Message-ID: <3e511a13-07cc-d5a2-1bd4-2bfc7285990d@ipfire.org> Date: Sun, 6 Sep 2020 09:47:59 +0000 MIME-Version: 1.0 Content-Language: en-US Authentication-Results: mail01.ipfire.org; auth=pass smtp.mailfrom=peter.mueller@ipfire.org 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/location-importer.in | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index f5ae4a9..77952f2 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -360,7 +360,7 @@ class CLI(object): ON COMMIT DROP; CREATE UNIQUE INDEX _autnums_number ON _autnums(number); - CREATE TEMPORARY TABLE _organizations(handle text, name text) + CREATE TEMPORARY TABLE _organizations(handle text, name text NOT NULL) ON COMMIT DROP; CREATE UNIQUE INDEX _organizations_handle ON _organizations(handle); """) @@ -373,7 +373,7 @@ class CLI(object): self.db.execute(""" INSERT INTO autnums(number, name) SELECT _autnums.number, _organizations.name FROM _autnums - LEFT JOIN _organizations ON _autnums.organization = _organizations.handle + JOIN _organizations ON _autnums.organization = _organizations.handle ON CONFLICT (number) DO UPDATE SET name = excluded.name; """) From patchwork Sun Sep 6 09:48:31 2020 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: 3434 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 "Let's Encrypt Authority X3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4BkmpN0l0bz3x3L for ; Sun, 6 Sep 2020 09:49:16 +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 "Let's Encrypt Authority X3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4BkmpM1ccSzl9; Sun, 6 Sep 2020 09:49:15 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4BkmpM1HGjz2xjj; Sun, 6 Sep 2020 09:49:15 +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 "Let's Encrypt Authority X3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4BkmpL1YVxz2xdN for ; Sun, 6 Sep 2020 09:49:14 +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)) (Client did not present a certificate) by mail01.ipfire.org (Postfix) with ESMTPSA id 4BkmpJ1ck8zl9 for ; Sun, 6 Sep 2020 09:49:11 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1599385753; 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: in-reply-to:in-reply-to:references:references; bh=GO1aE+HWUuzSCWRScTebnMumGZoYvrEgtc7FFKL8Gic=; b=uPChC0/UQV+1BGpSjrar5iHg9PhSSu+0PHI0nUIgz025WW/Ncv91ukEvJWzkda9UeS0UJK 5x0dKPGAFJOc6VCg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1599385753; 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: in-reply-to:in-reply-to:references:references; bh=GO1aE+HWUuzSCWRScTebnMumGZoYvrEgtc7FFKL8Gic=; b=dcbX2lyMDC7t0tPzAKDRbowI5yrJOWEXxm18mIEhqPKNZpzNmr6+MhuXIJMpwX5PIXbOYL 6A1wSL56Dr4Yuu9Y7lYefiDJbCk2cuAJLgEBOE9FncbOF0GQtTOmoZ4ykRYFdmWBEl8N4n HsEcdHb8OjEXNT4K3fj1CyVnVxEWZhlnYoBdL1N4Ek/MTGkR2zeKU0+fKjfUHoZKiisTz0 l3wpA7GeYFeBKudlJwVHJMRLDZc6P/MEdgtJP2HJzjM8fWFs9/8ljf5xb80GuagrQQlFPI XVmmsQa25SMPosznJz5mbcdta3nbcRnl6gYwzvDZb4zhWDPUep2nslSFW2uLlQ== Subject: [PATCH 2/3] importer: Import raw sources for inetnum's again To: location@lists.ipfire.org References: <3e511a13-07cc-d5a2-1bd4-2bfc7285990d@ipfire.org> From: =?utf-8?q?Peter_M=C3=BCller?= Message-ID: <9e67c95c-cbbd-bda6-0d26-befe24dfa70a@ipfire.org> Date: Sun, 6 Sep 2020 09:48:31 +0000 MIME-Version: 1.0 In-Reply-To: <3e511a13-07cc-d5a2-1bd4-2bfc7285990d@ipfire.org> Content-Language: en-US Authentication-Results: mail01.ipfire.org; auth=pass smtp.mailfrom=peter.mueller@ipfire.org 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" The extended feeds do not have enough detailed information for us, so that we need to import inetnums from RIRs where possible. Signed-off-by: Peter Müller --- src/python/importer.py | 14 ++++---- src/python/location-importer.in | 58 +++++++++++++++++++++++++++++++++ 2 files changed, 65 insertions(+), 7 deletions(-) diff --git a/src/python/importer.py b/src/python/importer.py index de20f37..f19db4b 100644 --- a/src/python/importer.py +++ b/src/python/importer.py @@ -30,8 +30,8 @@ WHOIS_SOURCES = ( "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.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", @@ -45,8 +45,8 @@ WHOIS_SOURCES = ( # 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.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", @@ -55,10 +55,10 @@ WHOIS_SOURCES = ( EXTENDED_SOURCES = ( # African Network Information Centre - "https://ftp.afrinic.net/pub/stats/afrinic/delegated-afrinic-extended-latest", + #"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", + #"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", @@ -67,7 +67,7 @@ EXTENDED_SOURCES = ( "http://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", + #"https://ftp.ripe.net/pub/stats/ripencc/delegated-ripencc-extended-latest", ) class Downloader(object): diff --git a/src/python/location-importer.in b/src/python/location-importer.in index 77952f2..fbc402f 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -393,6 +393,10 @@ class CLI(object): if line.startswith("aut-num:"): return self._parse_autnum_block(block) + # inetnum + if line.startswith("inet6num:") or line.startswith("inetnum:"): + return self._parse_inetnum_block(block) + # organisation elif line.startswith("organisation:"): return self._parse_org_block(block) @@ -422,6 +426,60 @@ class CLI(object): autnum.get("asn"), autnum.get("org"), ) + def _parse_inetnum_block(self, block): + logging.debug("Parsing inetnum block:") + + inetnum = {} + for line in block: + logging.debug(line) + + # Split line + key, val = split_line(line) + + if key == "inetnum": + start_address, delim, end_address = val.partition("-") + + # Strip any excess space + start_address, end_address = start_address.rstrip(), end_address.strip() + + # Convert to IP address + try: + start_address = ipaddress.ip_address(start_address) + end_address = ipaddress.ip_address(end_address) + except ValueError: + logging.warning("Could not parse line: %s" % line) + return + + # Set prefix to default + prefix = 32 + + # Count number of addresses in this subnet + num_addresses = int(end_address) - int(start_address) + if num_addresses: + prefix -= math.log(num_addresses, 2) + + inetnum["inetnum"] = "%s/%.0f" % (start_address, prefix) + + elif key == "inet6num": + inetnum[key] = val + + elif key == "country": + if val == "UNITED STATES": + val = "US" + + inetnum[key] = val.upper() + + # Skip empty objects + if not inetnum: + return + + network = ipaddress.ip_network(inetnum.get("inet6num") or inetnum.get("inetnum"), strict=False) + + self.db.execute("INSERT INTO networks(network, country) \ + VALUES(%s, %s) ON CONFLICT (network) DO UPDATE SET country = excluded.country", + "%s" % network, inetnum.get("country"), + ) + def _parse_org_block(self, block): org = {} for line in block: From patchwork Sun Sep 6 09:49:10 2020 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: 3435 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 "Let's Encrypt Authority X3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4Bkmq73mjzz3x3L for ; Sun, 6 Sep 2020 09:49:55 +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 "Let's Encrypt Authority X3" (verified OK)) by mail01.ipfire.org (Postfix) with ESMTPS id 4Bkmq72282zv8; Sun, 6 Sep 2020 09:49:55 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4Bkmq71mXgz2xjj; Sun, 6 Sep 2020 09:49:55 +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 "Let's Encrypt Authority X3" (verified OK)) by mail02.haj.ipfire.org (Postfix) with ESMTPS id 4Bkmq667yCz2xdN for ; Sun, 6 Sep 2020 09:49:54 +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)) (Client did not present a certificate) by mail01.ipfire.org (Postfix) with ESMTPSA id 4Bkmq30STwzv8 for ; Sun, 6 Sep 2020 09:49:50 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1599385792; 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: in-reply-to:in-reply-to:references:references; bh=ETRUWFhCkVdP1aZ/4HZudzs64ar8ZUJBGxLtESm0aAc=; b=yxQlriJz7bPMlUuiEFpRTlHR7Hb6vowwY3j4nziW7aJnsxB/rbeqcOE4piRmjRs8xCbMh/ HVXaXhPg4vuv/nDw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1599385792; 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: in-reply-to:in-reply-to:references:references; bh=ETRUWFhCkVdP1aZ/4HZudzs64ar8ZUJBGxLtESm0aAc=; b=pxfsLQSfK6EaUqAL4N1PvCQg8g29kxrgIf3OTeXt3ych0i5y9DkYqRgo324CCpF89b/51g 0nJI8Wtvi5rDWcrLz/UIAGoWnCPk/r3Nguy6+9Gbi31E27vihYBRBSDOlsVtXXkYyADP9J nT7pLEZU47HaNjew1k+9ghCeyaJLyhL7HTlNtNgwW5sLY8gxf2OPcMXMWEHBv2ikmLoxOP JLYHP3kSjUOzqcyjK+eSLSudnYs3yIpADzR45ILPvG68hYZy8xSM8NGYKZ0bYtpHq1NP75 eSEf5lH3RH6yr4QcDLBQMy83P9C86LcroZbtAOIHuf/ddlkYVEZMcTrz1Jbzjg== Subject: [PATCH 3/3] importer: Purge any redundant entries To: location@lists.ipfire.org References: <3e511a13-07cc-d5a2-1bd4-2bfc7285990d@ipfire.org> <9e67c95c-cbbd-bda6-0d26-befe24dfa70a@ipfire.org> From: =?utf-8?q?Peter_M=C3=BCller?= Message-ID: <42f16516-2006-5d9e-be61-9134a6fb443a@ipfire.org> Date: Sun, 6 Sep 2020 09:49:10 +0000 MIME-Version: 1.0 In-Reply-To: <9e67c95c-cbbd-bda6-0d26-befe24dfa70a@ipfire.org> Content-Language: en-US Authentication-Results: mail01.ipfire.org; auth=pass smtp.mailfrom=peter.mueller@ipfire.org 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" When importing inetnums, we might import various small networks which are not relevant for us as long as they do not have a different country code than their parent network. Therefore we delete all these entries to keep the database smaller without losing any information. Signed-off-by: Peter Müller --- src/python/location-importer.in | 7 +++++++ 1 file changed, 7 insertions(+) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index fbc402f..ea72790 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -375,6 +375,13 @@ class CLI(object): SELECT _autnums.number, _organizations.name FROM _autnums JOIN _organizations ON _autnums.organization = _organizations.handle ON CONFLICT (number) DO UPDATE SET name = excluded.name; + + --- Purge any redundant entries + DELETE FROM networks candidates WHERE EXISTS ( + SELECT FROM networks + WHERE networks.network >> candidates.network + AND networks.country = candidates.country + ); """) # Download all extended sources