May 2021 - Location - lists.ipfire.org

[PATCH 0/8] debian: Mitigate bulk of Lintian issues
by Valters Jansons 14 Jul '21

14 Jul '21

This series breaks apart the previously submitted block of debian/ changes into individual logical changes, which can be individually reviewed and discussed, and approved. This series is however missing the change for d/clean and d/libloc1.symbols, which are both pending rework as discussed in the superseded patch. Valters Jansons (8): debian: Add all temporary files to Gitignore debian: Move libloc1 to 'libs' section debian: Set 'Multi-Arch: foreign' hint for Python debian: Update copyright format link to use HTTPS debian: Add examples/python/ to documentation debian: Drop unintended files from location-python debian: Add watch configuration for uscan systemd: Add Documentation= to location-update debian/.gitignore | 9 +++++---- debian/control | 5 +++-- debian/copyright | 2 +- debian/location-python.examples | 1 + debian/rules | 8 ++++++++ debian/watch | 3 +++ src/systemd/location-update.service.in | 1 + 7 files changed, 22 insertions(+), 7 deletions(-) create mode 100644 debian/location-python.examples create mode 100644 debian/watch -- 2.31.1

2 22

[PATCH] location-importer.in: import additional IP information for Amazon AWS IP networks
by Peter Müller 10 Jun '21

10 Jun '21

Amazon publishes information regarding some of their IP networks primarily used for AWS cloud services in a machine-readable format. To improve libloc lookup results for these, we have little choice other than importing and parsing them. Unfortunately, there seems to be no machine-readable list of the locations of their data centers or availability zones available. If there _is_ any, please let the author know. Fixes: #12594 Signed-off-by: Peter Müller <peter.mueller(a)ipfire.org> --- src/python/location-importer.in | 110 ++++++++++++++++++++++++++++++++ 1 file changed, 110 insertions(+) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index 1e08458..5be1d61 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -19,6 +19,7 @@ import argparse import ipaddress +import json import logging import math import re @@ -931,6 +932,10 @@ class CLI(object): TRUNCATE TABLE network_overrides; """) + # Update overrides for various cloud providers big enough to publish their own IP + # network allocation lists in a machine-readable format... + self._update_overrides_for_aws() + for file in ns.files: log.info("Reading %s..." % file) @@ -998,6 +1003,111 @@ class CLI(object): else: log.warning("Unsupported type: %s" % type) + def _update_overrides_for_aws(self): + # Download Amazon AWS IP allocation file to create overrides... + downloader = location.importer.Downloader() + + try: + with downloader.request("https://ip-ranges.amazonaws.com/ip-ranges.json", return_blocks=False) as f: + aws_ip_dump = json.load(f.body) + except Exception as e: + log.error("unable to preprocess Amazon AWS IP ranges: %s" % e) + return + + # XXX: Set up a dictionary for mapping a region name to a country. Unfortunately, + # there seems to be no machine-readable version available of this other than + # https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availabil… + # (worse, it seems to be incomplete :-/ ); https://www.cloudping.cloud/endpoints + # was helpful here as well. + aws_region_country_map = { + "af-south-1": "ZA", + "ap-east-1": "HK", + "ap-south-1": "IN", + "ap-south-2": "IN", + "ap-northeast-3": "JP", + "ap-northeast-2": "KR", + "ap-southeast-1": "SG", + "ap-southeast-2": "AU", + "ap-southeast-3": "MY", + "ap-northeast-1": "JP", + "ca-central-1": "CA", + "eu-central-1": "DE", + "eu-central-2": "CH", + "eu-west-1": "IE", + "eu-west-2": "GB", + "eu-south-1": "IT", + "eu-south-2": "ES", + "eu-west-3": "FR", + "eu-north-1": "SE", + "me-south-1": "BH", + "sa-east-1": "BR" + } + + # Fetch all valid country codes to check parsed networks aganist... + rows = self.db.query("SELECT * FROM countries ORDER BY country_code") + validcountries = [] + + for row in rows: + validcountries.append(row.country_code) + + with self.db.transaction(): + for snetwork in aws_ip_dump["prefixes"] + aws_ip_dump["ipv6_prefixes"]: + try: + network = ipaddress.ip_network(snetwork.get("ip_prefix") or snetwork.get("ipv6_prefix"), strict=False) + except ValueError: + log.warning("Unable to parse line: %s" % snetwork) + continue + + # Sanitize parsed networks... + if not self._check_parsed_network(network): + continue + + # Determine region of this network... + region = snetwork["region"] + cc = None + is_anycast = False + + # Any region name starting with "us-" will get "US" country code assigned straight away... + if region.startswith("us-"): + cc = "US" + elif region.startswith("cn-"): + # ... same goes for China ... + cc = "CN" + elif region == "GLOBAL": + # ... funny region name for anycast-like networks ... + is_anycast = True + elif region in aws_region_country_map: + # ... assign looked up country code otherwise ... + cc = aws_region_country_map[region] + else: + # ... and bail out if we are missing something here + log.warning("Unable to determine country code for line: %s" % snetwork) + continue + + # Skip networks with unknown country codes + if not is_anycast and validcountries and cc not in validcountries: + log.warning("Skipping Amazon AWS network with bogus country '%s': %s" % \ + (cc, network)) + return + + # Conduct SQL statement... + self.db.execute(""" + INSERT INTO network_overrides( + network, + country, + is_anonymous_proxy, + is_satellite_provider, + is_anycast + ) VALUES (%s, %s, %s, %s, %s) + ON CONFLICT (network) DO NOTHING""", + "%s" % network, + cc, + None, + None, + is_anycast, + ) + + @staticmethod def _parse_bool(block, key): val = block.get(key) -- 2.26.2

2 11

[PATCH v2] location-importer.in: track original countries as well
by Peter Müller 02 Jun '21

02 Jun '21

This helps us to determine how many network objects have more than one country set, and what their original country code set looked like. The second version of this patch uses ALTER TABLE to add the column for original countries, preventing existing SQL setups from breaking. Signed-off-by: Peter Müller <peter.mueller(a)ipfire.org> --- src/python/location-importer.in | 52 ++++++++++++++++++++------------- 1 file changed, 32 insertions(+), 20 deletions(-) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index e5f55af..c3f908a 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -165,6 +165,7 @@ class CLI(object): -- networks CREATE TABLE IF NOT EXISTS networks(network inet, country text); + ALTER TABLE networks ADD COLUMN IF NOT EXISTS 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 +378,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[] 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); @@ -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, 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 +422,8 @@ class CLI(object): WITH candidates AS ( SELECT _rirdata.network, - _rirdata.country + _rirdata.country, + _rirdata.original_countries FROM _rirdata WHERE @@ -434,6 +436,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 +450,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 +621,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 +741,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): -- 2.20.1

2 6

[PATCH v2 1/2] location-importer.in: keep track of sources for networks, ASNs, and organisations
by Peter Müller 31 May '21

31 May '21

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. The second version of this patchset uses ALTER TABLE to add the source columns, avoiding breaking existing SQL setups. Signed-off-by: Peter Müller <peter.mueller(a)ipfire.org> --- src/python/location-importer.in | 91 ++++++++++++++++++--------------- 1 file changed, 49 insertions(+), 42 deletions(-) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index e5f55af..f796652 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -156,6 +156,7 @@ class CLI(object): -- autnums CREATE TABLE IF NOT EXISTS autnums(number bigint, name text NOT NULL); + ALTER TABLE autnums ADD COLUMN IF NOT EXISTS source text NOT NULL; CREATE UNIQUE INDEX IF NOT EXISTS autnums_number ON autnums(number); -- countries @@ -165,6 +166,7 @@ class CLI(object): -- networks CREATE TABLE IF NOT EXISTS networks(network inet, country text); + ALTER TABLE networks ADD COLUMN IF NOT EXISTS 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 +371,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 +397,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 +410,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 +424,8 @@ class CLI(object): WITH candidates AS ( SELECT _rirdata.network, - _rirdata.country + _rirdata.country, + _rirdata.source FROM _rirdata WHERE @@ -434,6 +438,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 +452,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 +468,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 +546,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 +581,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 +643,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 +663,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 +696,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 +736,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): -- 2.20.1

2 7

Re: Update AS, Country ...
by Peter Müller 22 May '21

22 May '21

Hello Sebastian, first, please keep the list CC'ed for transparency reasons (https://wiki.ipfire.org/devel/contact). Second: To the best of my knowledge, the Tor people currently fetch a version of location database a few days before they publish a new release, and convert it into the MaxMind database format they have used before. That implies (a) huge delays for getting updated databases to Tor users, especially if they use LTS versions packaged by Debian or similar distributions, which usually backport security issues only. Given the fact that the MaxMind database was even more inaccurate, I guess the status quo is a significant achievement already. Perhaps the Tor folks might slowly like to reduce delays of location database updates, but I don't know. (b) the statement "Tor is using the IPFire location database" might be kind of misleading. Technically, they do, but currently, they do not use _libloc_ as such. Not sure if that might change in the future; if so, shipping daily updates of the location databases to Tor users would be possible in theory, but comes with other caveats. :-) Sorry for not being able to help you on that issue. Please reach out to an appropriate Tor mailing list, if necessary. In case you do, I would be happy to be CC'ed on that conversation. :-) Thanks, and best regards, Peter Müller > Hi, > > Thanks for the fast reply. Im running Debian Buster with precompiled Tor and it seems there is no update script :-( Contabo Singapore is indeed correct but i guess i have to wait until they use a more recent db. > -- > > Sincerely yours / M.f.G. / Sincères salutations > > Sebastian Urbach > > > > Am 22. Mai 2021 12:35:43 schrieb Peter Müller <peter.mueller(a)ipfire.org>: > >> Hello Sebastian, >> >> thanks for reaching out. >> >> According to APNIC, that IP address nowadays belongs to "Contabo Asia Private Limited", having it's >> country code set to "SG" (Singapore) in the APNIC database. >> >> As far as I am concerned, the current version of IPFire's location database has this covered: >> >>> user@host:~$ location --version >>> location 0.9.6 >>> user@host:~$ location version >>> Sat, 22 May 2021 05:22:23 GMT >>> user@host:~$ location lookup 194.233.71.100 >>> 194.233.71.100: >>> Network : 194.233.68.0/22 >>> Country : Singapore >>> Autonomous System : AS141995 - Contabo Asia Private Limited >> >> May I ask you to run "location update" on your system (there should be a cron job or systemd timer >> for it, as we publish a new database every morning) and see if this solves your problem? >> >> Thanks, and best regards, >> Peter Müller > >

1 0

Update AS, Country ...
by Sebastian Urbach 22 May '21

22 May '21

Dear List, The Ipfire location database needs an update regarding the IP 194.233.71.100. Seems to me that the owner of the IP block has changed. Please check with: https://wq.apnic.net/static/search.html?query=194.233.71.100 Thanks -- Sincerely yours / M.f.G. / Sincères salutations Sebastian Urbach

2 1

[PATCH 1/2] location-importer.in: keep track of sources for networks, ASNs, and organisations
by Peter Müller 21 May '21

21 May '21

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 <peter.mueller(a)ipfire.org> --- 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): -- 2.20.1

1 2

[PATCH] location-importer.in: track original countries as well
by Peter Müller 21 May '21

21 May '21

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 <peter.mueller(a)ipfire.org> --- 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): -- 2.26.2

2 3

[PATCH] location.in: fix search_networks() function call
by Peter Müller 18 May '21

18 May '21

Fixes: #12617 Signed-off-by: Peter Müller <peter.mueller(a)ipfire.org> --- src/python/location.in | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/python/location.in b/src/python/location.in index ad2ccf5..e02b4e8 100644 --- a/src/python/location.in +++ b/src/python/location.in @@ -506,7 +506,7 @@ class CLI(object): f = writer(sys.stdout, prefix=country_code) # Print all matching networks - for n in db.search_networks(country_code=country_code, family=ns.family): + for n in db.search_networks(country_codes=[country_code], family=ns.family): f.write(n) f.finish() -- 2.26.2

3 3

[PATCH] location-importer.in: treat AQ and BV as invalid countries
by Peter Müller 18 May '21

18 May '21

Both the Bouvet Island (BV) and Antarctica (AQ) are unpopulated at the time of writing. Network owners/operators putting these countries into their RIR data objects are either completely braindead or doing so for hostile reasons. While we might correct these locations to something useful by manually creating overrides for them, the rationale behind this patch is not to let these countries appear on productive systems in the first place, as we know they _cannot_ be true. Therefore, this patch skips any network object that has either AQ or BV country code set. See also: https://lists.ipfire.org/pipermail/location/2020-October/000199.html Signed-off-by: Peter Müller <peter.mueller(a)ipfire.org> --- src/python/location-importer.in | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/src/python/location-importer.in b/src/python/location-importer.in index 1e08458..ac678dc 100644 --- a/src/python/location-importer.in +++ b/src/python/location-importer.in @@ -624,7 +624,7 @@ class CLI(object): return # Skip objects with unknown country codes - if validcountries and inetnum.get("country") not in validcountries: + if validcountries and (inetnum.get("country") not in validcountries or inetnum.get("country") in ["AQ", "BV"]): log.warning("Skipping network with bogus country '%s': %s" % \ (inetnum.get("country"), inetnum.get("inet6num") or inetnum.get("inetnum"))) return -- 2.26.2

2 7

2024

2023

2022

2021

2020

2019

2018

2017

Location May 2021