From patchwork Tue Sep 27 16:48:38 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6016 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 4McQZ2271hz3wfW for ; Tue, 27 Sep 2022 16:48:58 +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 4McQZ04NKLzDp; Tue, 27 Sep 2022 16:48:56 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ03CWMz2yV1; Tue, 27 Sep 2022 16:48:56 +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 4McQYy3fm9z2xLr for ; Tue, 27 Sep 2022 16:48: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) server-digest SHA384) (No client certificate requested) by mail01.ipfire.org (Postfix) with ESMTPSA id 4McQYx68N9zDp; Tue, 27 Sep 2022 16:48:53 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297333; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding; bh=MmKPFN/2eQIESUeE02XuKKwNCTL3aXkI70k4GdcO/Vs=; b=5KZfrcWx8lTkt3ctBdFFODLQCLzjDlx9Ma1nUvGVbH2obLgZcivYv0XwCXm/ZvsIHm/iEO Fy2F+KpVXuxi/wAA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297333; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding; bh=MmKPFN/2eQIESUeE02XuKKwNCTL3aXkI70k4GdcO/Vs=; b=Ped/HPbtFlBCtqR1+a59PzMOS0mS/tDiiBlCsfiz0zjqWVCSi9693LmHGUAo0yv75b/3hY nPEx5rtt6m3ZAhujnx/b4hKWhogC2iW6g7Ogl7N2WE+OMP2mbPOBostsIGu7h1B0gCGSKC BI7CqtqppgYpWAEaNs74o+iDkVAUAK7aReGqib7McETeitttNvshF20z5i1YmghzUMs57I ermMd7TayYryl7jIR0XYcO/CWyZwBv4qvXyIRT2huwgc4Sw87VHjp5JOol7XMct7F0x1Z9 Q5KHe+XloSh9wkGtY3ltpGhzEyG35WFDiazOog5tdG4BQHigziPXhbgHHavLCQ== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 01/10] importer: Store geofeed URLs from RIR data Date: Tue, 27 Sep 2022 16:48:38 +0000 Message-Id: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 40 +++++++++++++++++++++++++++++++- 1 file changed, 39 insertions(+), 1 deletion(-) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index 9faf23b..5bd5da3 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -182,6 +182,11 @@ class CLI(object): 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); + -- geofeeds + CREATE TABLE IF NOT EXISTS network_geofeeds(network inet, url text); + CREATE UNIQUE INDEX IF NOT EXISTS network_geofeeds_unique + ON network_geofeeds(network); + -- overrides CREATE TABLE IF NOT EXISTS autnum_overrides( number bigint NOT NULL, @@ -799,6 +804,16 @@ class CLI(object): inetnum[key].append(val) + # Parse the geofeed attribute + elif key == "geofeed": + inetnum["geofeed"] = val + + # Parse geofeed when used as a remark + elif key == "remark": + m = re.match(r"^(?:geofeed|Geofeed)\s+(https://.*)", val) + if m: + inetnum["geofeed"] = m.group(1) + # Skip empty objects if not inetnum or not "country" in inetnum: return @@ -810,7 +825,6 @@ class CLI(object): # 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 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" % \ @@ -823,6 +837,30 @@ class CLI(object): "%s" % single_network, inetnum.get("country")[0], inetnum.get("country"), source_key, ) + # Update any geofeed information + geofeed = inetnum.get("geofeed", None) + + # Store/update any geofeeds + if geofeed: + self.db.execute(""" + INSERT INTO + network_geofeeds( + network, + url + ) + VALUES( + %s, %s + ) + ON CONFLICT (network) DO + UPDATE SET url = excluded.url""", + "%s" % single_network, geofeed, + ) + + # Delete any previous geofeeds + else: + self.db.execute("DELETE FROM network_geofeeds WHERE network = %s", + "%s" % single_network) + def _parse_org_block(self, block, source_key): org = {} for line in block: From patchwork Tue Sep 27 16:48:39 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6015 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 "R3" (verified OK)) by web04.haj.ipfire.org (Postfix) with ESMTPS id 4McQZ20wJ4z3wdD for ; Tue, 27 Sep 2022 16:48:58 +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 4McQZ0562Tz2M1; Tue, 27 Sep 2022 16:48:56 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ03T0lz30M1; Tue, 27 Sep 2022 16:48:56 +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 4McQYy4JxBz2xNS for ; Tue, 27 Sep 2022 16:48: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) server-digest SHA384) (No client certificate requested) by mail01.ipfire.org (Postfix) with ESMTPSA id 4McQYy1K0Rzh6; Tue, 27 Sep 2022 16:48:54 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=VyCrmy9Y66nBGJShUO/ob0Tn3fKXbNvjHfQiSMiecGs=; b=cOaPLXF9zPIfR2DM7l3Bjw2dmfddCdbud9D2yKMMnqVkTReR83kR7+yXs41R77bBQKjrjZ mlW1deSzjH3X6hBQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=VyCrmy9Y66nBGJShUO/ob0Tn3fKXbNvjHfQiSMiecGs=; b=d56h6EHxHrFk7FukA2y1JsRO6tCMAuUzt+rDiG/oej9tOqwiVEjY5BStxPbZK1TjVOCO/p 58XFN48c5OW8goy1UvI4FHsnwv3eFHpQXGWlb87Y9K1DoYHkMSCCiZcBsV03m0GaBEv4CU f2zs7E5wl2ttUawsGF3lDXOfA31di0zhz1K/eRSyGhv29JAtqvNGIbNlRCWram6DgnEYPI kKqcQGDLdtPIWBj9InWHHFKWNlTaWXq4ioShfImOefUIOcPe77MXm4Ta37hL6Iwk+oeaqf D3+vCpM0froQrVsl65IUNjMRjrefulXOY+Hy0heayqUcbfDupiSyynvZe8TlLA== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 02/10] importer: Add command to import geofeeds into the database Date: Tue, 27 Sep 2022 16:48:39 +0000 Message-Id: <20220927164847.3409646-2-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 143 +++++++++++++++++++++++++++++++ 1 file changed, 143 insertions(+) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index 5bd5da3..ddec376 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -18,6 +18,7 @@ ############################################################################### import argparse +import concurrent.futures import ipaddress import json import logging @@ -95,6 +96,11 @@ class CLI(object): update_announcements.add_argument("server", nargs=1, help=_("Route Server to connect to"), metavar=_("SERVER")) + # Update geofeeds + update_geofeeds = subparsers.add_parser("update-geofeeds", + help=_("Update Geofeeds")) + update_geofeeds.set_defaults(func=self.handle_update_geofeeds) + # Update overrides update_overrides = subparsers.add_parser("update-overrides", help=_("Update overrides"), @@ -183,6 +189,25 @@ class CLI(object): CREATE INDEX IF NOT EXISTS networks_search ON networks USING GIST(network inet_ops); -- geofeeds + CREATE TABLE IF NOT EXISTS geofeeds( + id serial primary key, + url text, + status integer default null, + updated_at timestamp without time zone default null + ); + CREATE UNIQUE INDEX IF NOT EXISTS geofeeds_unique + ON geofeeds(url); + CREATE TABLE IF NOT EXISTS geofeed_networks( + geofeed_id integer references geofeeds(id) on delete cascade, + network inet, + country text, + region text, + city text + ); + CREATE INDEX IF NOT EXISTS geofeed_networks_geofeed_id + ON geofeed_networks(geofeed_id); + CREATE INDEX IF NOT EXISTS geofeed_networks_search + ON geofeed_networks(network); CREATE TABLE IF NOT EXISTS network_geofeeds(network inet, url text); CREATE UNIQUE INDEX IF NOT EXISTS network_geofeeds_unique ON network_geofeeds(network); @@ -1253,6 +1278,124 @@ class CLI(object): # Otherwise return the line yield line + def handle_update_geofeeds(self, ns): + # Fetch all Geofeeds that require an update + geofeeds = self.db.query(""" + SELECT + id, + url + FROM + geofeeds + WHERE + updated_at IS NULL + OR + updated_at <= CURRENT_TIMESTAMP - INTERVAL '1 week' + ORDER BY + id + """) + + with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor: + results = executor.map(self._fetch_geofeed, geofeeds) + + for result in results: + print(result) + + def _fetch_geofeed(self, geofeed): + log.debug("Fetching Geofeed %s" % geofeed.url) + + with self.db.transaction(): + # Open the URL + try: + req = urllib.request.Request(geofeed.url, headers={ + "User-Agent" : "location/%s" % location.__version__, + + # We expect some plain text file in CSV format + "Accept" : "text/csv, text/plain", + }) + + # XXX set proxy + + # Send the request + with urllib.request.urlopen(req, timeout=10) as f: + # Remove any previous data + self.db.execute("DELETE FROM geofeed_networks \ + WHERE geofeed_id = %s", geofeed.id) + + # Read the output line by line + for line in f: + line = line.decode() + + # Strip any newline + line = line.rstrip() + + # Skip empty lines + if not line: + continue + + # Try to parse the line + try: + fields = line.split(",", 5) + except ValueError: + log.debug("Could not parse line: %s" % line) + continue + + # Check if we have enough fields + if len(fields) < 4: + log.debug("Not enough fields in line: %s" % line) + continue + + # Fetch all fields + network, country, region, city, = fields[:4] + + # Try to parse the network + try: + network = ipaddress.ip_network(network, strict=False) + except ValueError: + log.debug("Could not parse network: %s" % network) + continue + + # XXX Check the country code + + # Write this into the database + self.db.execute(""" + INSERT INTO + geofeed_networks ( + geofeed_id, + network, + country, + region, + city + ) + VALUES (%s, %s, %s, %s, %s)""", + geofeed.id, + "%s" % network, + country, + region, + city, + ) + + # Catch any HTTP errors + except urllib.request.HTTPError as e: + self.db.execute("UPDATE geofeeds SET status = %s \ + WHERE id = %s", e.code, geofeed.id) + + # Catch any other errors + except urllib.request.URLError as e: + log.error("Could not fetch URL %s: %s" % (geofeed.url, e)) + + # Mark the geofeed as updated + else: + self.db.execute(""" + UPDATE + geofeeds + SET + updated_at = CURRENT_TIMESTAMP, + status = NULL + WHERE + id = %s""", + geofeed.id, + ) + def handle_update_overrides(self, ns): with self.db.transaction(): # Only drop manually created overrides, as we can be reasonably sure to have them, From patchwork Tue Sep 27 16:48:40 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6018 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 4McQZ26zwjz3whh for ; Tue, 27 Sep 2022 16:48:58 +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 4McQZ10212z2R1; Tue, 27 Sep 2022 16:48:56 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ03hgpz301B; Tue, 27 Sep 2022 16:48:56 +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 4McQYy59LKz2xLr for ; Tue, 27 Sep 2022 16:48: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) server-digest SHA384) (No client certificate requested) by mail01.ipfire.org (Postfix) with ESMTPSA id 4McQYy3pFrzDp; Tue, 27 Sep 2022 16:48:54 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=gVPUWXmvvFMX5mWJdrxid+s7Oyi39oHwEGmkXCZCa8s=; b=huGWv+q4q45VzkQTbLnfTBKqO9M2pQX3cpTOBJ1/na8cC8bB4BTiVkGgyCQQ4jMW4ApWPr cW7l+zT+rbVHUgBA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=gVPUWXmvvFMX5mWJdrxid+s7Oyi39oHwEGmkXCZCa8s=; b=R9WJiTCnfFxek/wyo0FsYtQayrgOW4IlogeiddibyiNgqpgzcqJfZPCMF5i1bgLIH4VKXS l/jrpfkBXRJwvhoeYJTqCOk3DlTCtDGgDSMBe2DgKv1hZfE+WE7DGt+iGpH2MmEukSmRar vXIi5kgXRWj6PO4bl2yZ05Obr6Yh8MQ2pM2IrIX/0Inl0R4ldziJIlpbjS38/4KYmAU7Ms cG2m25eHEkoJKDsOuz0jOeHFNPDla4PgLcouSie7I/Ja+7/dUFmLFUyffppLFEddWCmYyR xdnV4EHhksbTvZeg+AyAhiekSuRlsVnP6xcxqifNoj4vRBDH580eqm/h1ksgHg== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 03/10] importer: Just fetch any exception from the executor Date: Tue, 27 Sep 2022 16:48:40 +0000 Message-Id: <20220927164847.3409646-3-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Any exceptions will only be raised in the main thread when they are fetched from the executor. We do not need to print any other return values here. Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 3 ++- 1 file changed, 2 insertions(+), 1 deletion(-) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index ddec376..014f3b6 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -1297,8 +1297,9 @@ class CLI(object): with concurrent.futures.ThreadPoolExecutor(max_workers=10) as executor: results = executor.map(self._fetch_geofeed, geofeeds) + # Fetch all results to raise any exceptions for result in results: - print(result) + pass def _fetch_geofeed(self, geofeed): log.debug("Fetching Geofeed %s" % geofeed.url) From patchwork Tue Sep 27 16:48:41 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6017 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 4McQZ260T2z3wgT for ; Tue, 27 Sep 2022 16:48:58 +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 4McQZ103zjz2RG; Tue, 27 Sep 2022 16:48:57 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ03vZZz301x; Tue, 27 Sep 2022 16:48:56 +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 4McQYy5tZzz2xNS for ; Tue, 27 Sep 2022 16:48: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) server-digest SHA384) (No client certificate requested) by mail01.ipfire.org (Postfix) with ESMTPSA id 4McQYy4bRwzh6; Tue, 27 Sep 2022 16:48:54 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=2sHkC8WKbqh7llPBOijIa2DUrZCWrfBsAgRBLXrfY8s=; b=4AoEaJKzWPKruml7XDSKUimcZBwr66qVTlCw001+e0/KascnZ+iP0fY9TfIQk89NM36qXM PnGPPnRmsaE5UgAw== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=2sHkC8WKbqh7llPBOijIa2DUrZCWrfBsAgRBLXrfY8s=; b=NyOVclwZN+14qehklORNUI45sSAtuhAHcTSk1KUCCDPyRBlnzXzSHm3ozAukLvfT4tPhPC Etu45gJqmbIbH6Tw+xN4PKKNBSXLqzo4EI+hxzEIr0k4zmbUkxihBCXbaUPjYkdrn4bCKr N2S+Xzrjo6EpxZaiASlSfbV3UFf0kBOL7dFqqhR20bU6a0YcfbUU5rCxH0wUliDfIRQmj/ ElP0tlIj8i9JuQ0EIXgMacx0iR+fhJ2Pe0WATc1qaP4Ka2HibofDZiU4FwXCfH79R3cnz9 UyruQ6eM9iyLRRvhvjyTkesJvKSTJccBMyYA9o07vtL4Xr21dqZk7/HY73fkfA== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 04/10] importer: Sync geofeeds Date: Tue, 27 Sep 2022 16:48:41 +0000 Message-Id: <20220927164847.3409646-4-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Geofeeds are kept in a separate table to only fetch them once per URL. This needs to be kept in sync which is done before we update any feeds. Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 32 ++++++++++++++++++++++++++++++++ 1 file changed, 32 insertions(+) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index 014f3b6..12035f1 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -1279,6 +1279,38 @@ class CLI(object): yield line def handle_update_geofeeds(self, ns): + # Sync geofeeds + with self.db.transaction(): + # Delete all geofeeds which are no longer linked + self.db.execute(""" + DELETE FROM + geofeeds + WHERE + NOT EXISTS ( + SELECT + 1 + FROM + network_geofeeds + WHERE + geofeeds.url = network_geofeeds.url + )""", + ) + + # Copy all geofeeds + self.db.execute(""" + INSERT INTO + geofeeds( + url + ) + SELECT + url + FROM + network_geofeeds + ON CONFLICT (url) + DO NOTHING + """, + ) + # Fetch all Geofeeds that require an update geofeeds = self.db.query(""" SELECT From patchwork Tue Sep 27 16:48:42 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6019 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 4McQZ33sB5z3wdD for ; Tue, 27 Sep 2022 16:48:59 +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 4McQZ11rQqz2Rc; Tue, 27 Sep 2022 16:48:57 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ049T2z3032; Tue, 27 Sep 2022 16:48:56 +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 4McQYz1FWgz2xNS for ; Tue, 27 Sep 2022 16:48:55 +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 4McQYy5MnJz2Qn; Tue, 27 Sep 2022 16:48:54 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=VZVsXz43rmil1d6Ug+KNWKEyJEsZ7L2C/3I3OJ2C1zA=; b=2cjDXbcPj86Zwa0/QfAYyveD4lUw2Fh3txsevVzicrC0RXbSwfW4sjoWLq9Ejt59DtT0eY 7dHDArP6q8SRoADQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297334; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=VZVsXz43rmil1d6Ug+KNWKEyJEsZ7L2C/3I3OJ2C1zA=; b=B7k7TEp2Cn6/j998S+Rp1aPAKbYk5jHCzT45Qp6duehAomfIk40i8nCuJqgDSdGEzIqOZM 4XQkfgjUhvlIXHq2neyDHkGrEY13l5nfGDyF0V4AwypCgwEuvnkHAmX5qJ0wHdPU85awHb uKh6uo6kdHiOS0tRqNtyYf4r/gvQNg2Fph9vho1EWnYRWt+TSBRKrSKsQkiWraB96SW+w6 /vYWVmraN9oeDL8DlRMiYbZyo5DV15S9IS6L0NqygAYBVwSpF6deCi7lgWckasJ7LBc5jN JYmQTej4G/nvC6b5QBrybONcdhy8mDWsddXKUsDhOTWzqYAiEMS7LsPmhgcE4w== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 05/10] importer: Use geofeeds for country assignment Date: Tue, 27 Sep 2022 16:48:42 +0000 Message-Id: <20220927164847.3409646-5-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 25 +++++++++++++++++++++++++ 1 file changed, 25 insertions(+) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index 12035f1..f8d2dc8 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -291,6 +291,8 @@ class CLI(object): SELECT network FROM networks UNION SELECT network FROM network_overrides + UNION + SELECT network FROM geofeed_networks ), ordered_networks AS ( @@ -333,6 +335,29 @@ class CLI(object): SELECT country FROM autnum_overrides overrides WHERE networks.autnum = overrides.number ), + ( + SELECT + geofeed_networks.country AS country + FROM + network_geofeeds + + -- Join the data from the geofeeds + LEFT JOIN + geofeeds ON network_geofeeds.url = geofeeds.url + LEFT JOIN + geofeed_networks ON geofeeds.id = geofeed_networks.geofeed_id + + -- Check whether we have a geofeed for this network + WHERE + networks.network <<= network_geofeeds.network + AND + networks.network <<= geofeed_networks.network + + -- Filter for the best result + ORDER BY + masklen(geofeed_networks.network) DESC + LIMIT 1 + ), networks.country ) AS country, From patchwork Tue Sep 27 16:48:43 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6020 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 4McQZ34Dngz3wvP for ; Tue, 27 Sep 2022 16:48:59 +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 4McQZ12l3Cz2Xl; Tue, 27 Sep 2022 16:48:57 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ04NLkz2yVB; Tue, 27 Sep 2022 16:48:56 +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 4McQYz243Hz2yVB for ; Tue, 27 Sep 2022 16:48:55 +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 4McQYz0hD4zDp; Tue, 27 Sep 2022 16:48:55 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=aZ+K/g+tc/Mp8ipZGD51g81X/8m2zW93+NnnA9IfKcY=; b=ZDTkzVAXcdA6UQImWOLGq+v+dDEf0mpz3OBIC2SLRqmMzzSUaFjbG9fZF8wGbycxcfOErv PLlmvtLYZlc+3+CQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=aZ+K/g+tc/Mp8ipZGD51g81X/8m2zW93+NnnA9IfKcY=; b=oYXORcH61UpKOPJa468qF4wZWT0gwGrn/NSFC4D8GMKcm8SqncemOTjayrI9xXD0lVkhLE UlFreLHHw8U3k8d0Z24jB3xbldu/51Rwj7ly6hoVvdpklYUP8zamvYEfMI+uMhRBKKOLR0 l9bbKk1GWHhCpjbxMjcUhBQh7PnntdpeGcfqugljlNUCrLKAiU40oD5iMWQDOexcZLcREf 8YCrrEPNuYEUymbTJU3d/lXcASd8P2HhkWyj1B4Kz6AEEo5zA/LsPzQRp8A8r3gda1AIW8 REm8Pep+8lYu/wNiCCgxwCsyWhzCzUjxy8L86jbkOCrj3PmxJtovHl9pDdos3A== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 06/10] importer: Use a GIST index for networks from geofeeds Date: Tue, 27 Sep 2022 16:48:43 +0000 Message-Id: <20220927164847.3409646-6-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index f8d2dc8..5759e0c 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -207,7 +207,7 @@ class CLI(object): CREATE INDEX IF NOT EXISTS geofeed_networks_geofeed_id ON geofeed_networks(geofeed_id); CREATE INDEX IF NOT EXISTS geofeed_networks_search - ON geofeed_networks(network); + ON geofeed_networks USING GIST(network inet_ops); CREATE TABLE IF NOT EXISTS network_geofeeds(network inet, url text); CREATE UNIQUE INDEX IF NOT EXISTS network_geofeeds_unique ON network_geofeeds(network); From patchwork Tue Sep 27 16:48:44 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6021 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 4McQZ34LsSz3wwb for ; Tue, 27 Sep 2022 16:48:59 +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 4McQZ12wM1z2Y7; Tue, 27 Sep 2022 16:48:57 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ04Z9Jz30Vf; Tue, 27 Sep 2022 16:48:56 +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 4McQYz2wKpz2xNS for ; Tue, 27 Sep 2022 16:48:55 +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 4McQYz1WTczh6; Tue, 27 Sep 2022 16:48:55 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=BWMCUTXaOEboIYjlIoMrY6QTQizO6l7kgSom/gQEAfQ=; b=AJx4KnjDWOc6DFv4o2PnpMzZ+JsGFvAdxjydmbI5rQ/3AGwMccvPnfBISVuJrLuf8YKnWK 0UplR746a9ksjzCA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=BWMCUTXaOEboIYjlIoMrY6QTQizO6l7kgSom/gQEAfQ=; b=WQppEZVZBR4k4R7WnjPd/VWbIXT0Sqlp+bfLDy7mvdo/d/BJuHIfQr7rRctc7quMoB+fqP TCev+mrovBK098jINve7x8qPqVYMrKFCxb+HjOnYgCXX4e/XXyHRdpUM7H6G5hJSCeGxLI 9iYIR1F+SdG6LV2OsfwY8juJTJjIMefvsdjE+8lGn2E+xBkNHFmiaum30t3alhhc4x7nTB J0dBFHQjBWjQ9VU+hKkfqAqv3Ov+AaJOa9B7xO/BxjqZJvJZtri8thynq9RDOOYWkG9g8/ DFfpBGdFVJ3B3RALqQO398WtKtLCpMFmU1v5HbroKMznqmrym1q4kA5K+P8hsQ== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 07/10] importer: Add a search index match geofeed networks quicker Date: Tue, 27 Sep 2022 16:48:44 +0000 Message-Id: <20220927164847.3409646-7-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 4 ++++ 1 file changed, 4 insertions(+) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index 5759e0c..7e2136e 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -211,6 +211,10 @@ class CLI(object): CREATE TABLE IF NOT EXISTS network_geofeeds(network inet, url text); CREATE UNIQUE INDEX IF NOT EXISTS network_geofeeds_unique ON network_geofeeds(network); + CREATE INDEX IF NOT EXISTS network_geofeeds_search + ON network_geofeeds USING GIST(network inet_ops); + CREATE INDEX IF NOT EXISTS network_geofeeds_url + ON network_geofeeds(url); -- overrides CREATE TABLE IF NOT EXISTS autnum_overrides( From patchwork Tue Sep 27 16:48:45 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6022 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 4McQZ35QWYz3x0R for ; Tue, 27 Sep 2022 16:48:59 +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 4McQZ140hRz2YV; Tue, 27 Sep 2022 16:48:57 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ04lVmz30Vp; Tue, 27 Sep 2022 16:48:56 +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 4McQYz3lNkz2yVB for ; Tue, 27 Sep 2022 16:48:55 +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 4McQYz2PCQzwd; Tue, 27 Sep 2022 16:48:55 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=fbPMy9QxuiOU4a661eQnAq8zHvDZ7dAn/vojYgeGKA0=; b=nnob2XeM4QoeJb9dK+9Da9p/DSGTJFMLUVb25ChH1/eknSAHzyvCjXuOZX3Q/tW8VZbPf3 hx6Zg8p1epyNWaDQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=fbPMy9QxuiOU4a661eQnAq8zHvDZ7dAn/vojYgeGKA0=; b=svTE4Vg9ey2dw7k/cShAYE5gpGnk3Ne7MDpaxHqJTaNNDEU1EKtVIltCnhsQDgg8QNlEsK d/hcHhfCHgeUzmXpUzzRGxX67b9fs8ETwpKPvDYCo9l/ZcUJaMxPg9Q2KRDGTHn5Jvyd2L oTAql/JPWH6zMISuzd6o8Bz2q5N5AtCPSx3rVnR7AtNW7/d7TJNhhWApwviS6+nVLW+9lk TJZrXXjPYAyHFbl0c0vCzeW1OXnVwi6gsTe5/zfeaoFsZfAHnUh3uU2mhRC5WUOElTzLcT KnORewEV+Cn/4dQvZpthavWjtIlKwTleE/0ZUPwcVHXvKz0YYBwdvhbWLUgpUA== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 08/10] importer: Fix reading Geofeeds from remarks Date: Tue, 27 Sep 2022 16:48:45 +0000 Message-Id: <20220927164847.3409646-8-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Only RIPE has a dedicated geofeed field. For others, this data needs to be read from the remarks section. Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index 7e2136e..0e2764e 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -863,8 +863,8 @@ class CLI(object): inetnum["geofeed"] = val # Parse geofeed when used as a remark - elif key == "remark": - m = re.match(r"^(?:geofeed|Geofeed)\s+(https://.*)", val) + elif key == "remarks": + m = re.match(r"^(?:Geofeed)\s+(https://.*)", val) if m: inetnum["geofeed"] = m.group(1) From patchwork Tue Sep 27 16:48:46 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6023 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 4McQZ36f8wz3xGr for ; Tue, 27 Sep 2022 16:48:59 +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 4McQZ15Kg0z2Yn; Tue, 27 Sep 2022 16:48:57 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ1539jz2y0H; Tue, 27 Sep 2022 16:48:57 +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 4McQYz4Xhzz2xNS for ; Tue, 27 Sep 2022 16:48:55 +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 4McQYz3Cg2zDp; Tue, 27 Sep 2022 16:48:55 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=bI9Sx0ZqL9JuVY6v86H7pqJiL/ZXjNNUYaIHKyGbImw=; b=sdEL5NgBzoHpLVQQxVrBmDeVuImpXMG6hWIjEge56t14+YnnCIP3DAQNG4Hm+a+kf4fysZ 0XLFpJ9Z4AO6IPCA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=bI9Sx0ZqL9JuVY6v86H7pqJiL/ZXjNNUYaIHKyGbImw=; b=LR32ExVXyjGoDzPjfz/iED0TlP/lyB4gXSiR4HthW1rT4T3UHmdE7RBSiFZQk7/qnuuOzd hbbphtAEdtpLvbzf7h5eCTd+TPT0xb8mYYAjk6dhKdRaIxQFeX6DpvwUz/W2TQkI37NHwF fK3N673pRQ/ZKSNpVLU5Z6GPyiVp5/QEdXVm2ii9qFhti4VAgAPF+q9G0S9FRP4szanBjn wYTq+btO6DycOS+ur3JX9Ysrcp5m2UG9nDjqVnw/CvYKOUb7HVvmICwT/p62ypb1S49yCl TJGaqUqLB7ifoo19lEBrgAptZg73AY8Go856drXs//aeMfR3nDl9VBppEm8wng== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 09/10] importer: Ensure that we only use HTTPS URLs for Geofeeds Date: Tue, 27 Sep 2022 16:48:46 +0000 Message-Id: <20220927164847.3409646-9-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 5 +++++ 1 file changed, 5 insertions(+) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index 0e2764e..d0384b5 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -894,6 +894,11 @@ class CLI(object): # Update any geofeed information geofeed = inetnum.get("geofeed", None) + # Make sure that this is a HTTPS URL + if geofeed and not geofeed.startswith("https://"): + log.warning("Geofeed URL is not using HTTPS: %s" % geofeed) + geofeed = None + # Store/update any geofeeds if geofeed: self.db.execute(""" From patchwork Tue Sep 27 16:48:47 2022 Content-Type: text/plain; charset="utf-8" MIME-Version: 1.0 Content-Transfer-Encoding: 7bit X-Patchwork-Submitter: Michael Tremer X-Patchwork-Id: 6024 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 4McQZ371QQz3xgM for ; Tue, 27 Sep 2022 16:48:59 +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 4McQZ15hJwz2Z3; Tue, 27 Sep 2022 16:48:57 +0000 (UTC) Received: from mail02.haj.ipfire.org (localhost [127.0.0.1]) by mail02.haj.ipfire.org (Postfix) with ESMTP id 4McQZ15J4nz2yV1; Tue, 27 Sep 2022 16:48:57 +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 4McQYz5QX7z2yV1 for ; Tue, 27 Sep 2022 16:48:55 +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 4McQYz43kBz1qx; Tue, 27 Sep 2022 16:48:55 +0000 (UTC) DKIM-Signature: v=1; a=ed25519-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003ed25519; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=j9O558/IrEUhlWp2GzkRx0jSnKeIcEzxk8LWywC4SdQ=; b=ihsV86jJ3vlXM9TdmI6CHAdg/+tTV3tOAIH7UFVeDOzVlOyxQ5znnv/OZHxNb9WERfj8qX lEFFUBUKxgPiBjBA== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=ipfire.org; s=202003rsa; t=1664297335; h=from:from:reply-to:subject:subject:date:date:message-id:message-id: to:to:cc:cc:mime-version:mime-version: content-transfer-encoding:content-transfer-encoding: in-reply-to:in-reply-to:references:references; bh=j9O558/IrEUhlWp2GzkRx0jSnKeIcEzxk8LWywC4SdQ=; b=vslHWfa+Ir8XdhNw5KlgT0A8X3WEHGfTBsbnndMORO8bZBrWs5oF1rA0g4yB9CgRRsWJTy Q5QTPfsExnvF2LNAKL1Rpj3pgDtLxsj/YNuRf0xESAiEIFKOHFOIAn0lRAf293PqTypFGh vatSNRjj+5W/Q0lvy0XZWd/owI0usYMrhH5o6PzYUgraZ6qKHXzsdqS0W+IzWt6ovv1W9d BE2YsnX/F1rcl+6qR9hlrysmNvOR8c+nKGU2m1d7eoEdVJlzVobozX2A4ozkFeiq7Rn5nd 2e2iiDw7ZkhGyxpsyZl/5Xv6+ChVfpLP3ZAGZrj/3swkH8hOTQ6vOdv++URZZA== From: Michael Tremer To: location@lists.ipfire.org Subject: [PATCH 10/10] importer: Validate country codes from Geofeeds Date: Tue, 27 Sep 2022 16:48:47 +0000 Message-Id: <20220927164847.3409646-10-michael.tremer@ipfire.org> In-Reply-To: <20220927164847.3409646-1-michael.tremer@ipfire.org> References: <20220927164847.3409646-1-michael.tremer@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: , Cc: Michael Tremer Errors-To: location-bounces@lists.ipfire.org Sender: "Location" Signed-off-by: Michael Tremer --- src/scripts/location-importer.in | 10 +++++++++- 1 file changed, 9 insertions(+), 1 deletion(-) diff --git a/src/scripts/location-importer.in b/src/scripts/location-importer.in index d0384b5..d8f9cbf 100644 --- a/src/scripts/location-importer.in +++ b/src/scripts/location-importer.in @@ -1421,7 +1421,15 @@ class CLI(object): log.debug("Could not parse network: %s" % network) continue - # XXX Check the country code + # Strip any excess whitespace from country codes + if country: + country = country.strip() + + # Check the country code + if not location.country_code_is_valid(country): + log.warning("Invalid country code in Geofeed %s: %s" \ + % (geofeed.url, country)) + continue # Write this into the database self.db.execute("""