Drop #132 (2022-11-03): War: What Is It Good For?
IP addresses as spoils of war; mitmproxy 9; SELECT * FROM certificate_and_identities;
IP addresses as spoils of war
Russia's war to conquer Ukraine has had many global side effects. The humanitarian crisis is paramount, with millions of displaced Ukrainians needing food, shelter, healthcare, and education. The impacts to the global supply chain have created food crises in many regions of the world and increased prices of fuel, food, and other goods in almost every nation. Furthermore, global sanctions against Russia and Russia-aligned nations have made daily life much more difficult for the average Russian citizen.
Back in the day, most folks did not really consider the copper wires connecting legacy "landline" phones to the broader physical telecommunications network. Today, similar lack of consideration is given to the fact that — on demand — you will receive an IPv4 or IPv6 address that will get you connected to the internet. Local network IP addresses usually aren't enough to actually get on the internet, which is why you have a home router and businesses have considerable routing infrastructures that are directly connected to the internet to bridge the gap. These routers have IP addresses, as do the servers that feed cat pictures to you. These IP addresses are dolled out by regional internet registries (RIRs). The aforementioned sanctions had a very real impact on IP addresses, with a number of RIR downstream resource holders having their assets frozen, including the ability to acquire further resources or transfer existing ones.
Disabling the ability of sanctioned entities to engage in typical IP address allocation operations is not the only impact. It turns out that IP addresses themselves are becoming the spoils of war. As Russian troops occupy Ukrainian towns and cities, they aren't just committing despicable war crimes against local men, women, and children. They are also, quite literally, taking IP address assignments away from ISPs and business at gunpoint. And, at a recent RIPE meeting, there was a sober but vibrant discussion regarding mitigating the impact of sanctions on access to the Internet. Many Ukrainian ISPs and businesses want RIPE (which is the ultimate governing registry for Europe) to freeze Ukraine IP address operations as well.
It's a complex subject, but I heartily encourage folks to dig in to the linked resources to gain a better understanding of this aspect of the war, and what fragile things these invisible integers that connect our glowing rectangles together really are.
If you have ever needed to look at the interactions of between web clients and servers, chances are you've used some type of interception proxy to do so. Mitmproxy is one of the more longstanding and popular tools to perform such interception and introspection, and the recent update to it added a neat new feature that will make it even easier to capture traffic thanks to support for WireGuard, a robust and modern VPN.
WireGuard support in mitmproxy version nine comes via the Python mitmproxy-wireguard package, which provides a user space implementation of a WireGuard server (so, no root/admin privileges required).
mitmproxy_wireguard may have a Pythonic wrapper, but it is written in Rust using some great Rust crates, including:
Tokio, an asynchronous runtime for Rust.
boringtun, an implementation of the WireGuard protocol.
smoltcp, a standalone, user space TCP/IP stack.
PyO3, a project that provides Python ↔ Rust interoperability, including support for bridging async runtimes of the two languages.
Maturin, a build tool for Python packages that are implemented in Rust.
The links to the above are in the mitmproxy announcement post, which you absolutely should read, as it explains the functionality very well with plenty of examples.
I'll be trying this out over the weekend and will report back on how well it does or does not work with Tailscale, which is what I use for WireGuard on all my servers and devices.
SELECT * FROM certificate_and_identities;
My cyber and data science worlds collided (once again) yesterday when Jan Schaumann (@jschauma) dropped this on Twitter:
I noticed that due to someone tapping in JD Long (@CMastication) into the convo, and given the topic I felt the need to comment in-thread, and now, here, about why you shouldn't put databases directly on the public internet.
TL;DR: the epic crt.sh — a service setup by the Rob Stradling of Sectigo to let folks inspect all the public SSL/TLS certificates of the world. (Learn more about it in this podcast episode) has some seekrit abilities.
While you can use the web search interface to poke at, say, all my rud.is certs, the site has an additional parameter you can append to the request to get the SQL for the query, with said results at the bottom of the page:
WITH ci AS ( SELECT min(sub.CERTIFICATE_ID) ID, min(sub.ISSUER_CA_ID) ISSUER_CA_ID, array_agg(DISTINCT sub.NAME_VALUE) NAME_VALUES, x509_commonName(sub.CERTIFICATE) COMMON_NAME, x509_notBefore(sub.CERTIFICATE) NOT_BEFORE, x509_notAfter(sub.CERTIFICATE) NOT_AFTER, encode(x509_serialNumber(sub.CERTIFICATE), 'hex') SERIAL_NUMBER FROM (SELECT * FROM certificate_and_identities cai WHERE plainto_tsquery('certwatch', $1) @@ identities(cai.CERTIFICATE) AND cai.NAME_VALUE ILIKE ('%' || $1 || '%') LIMIT 10000 ) sub GROUP BY sub.CERTIFICATE ) SELECT ci.ISSUER_CA_ID, ca.NAME ISSUER_NAME, ci.COMMON_NAME, array_to_string(ci.NAME_VALUES, chr(10)) NAME_VALUE, ci.ID ID, le.ENTRY_TIMESTAMP, ci.NOT_BEFORE, ci.NOT_AFTER, ci.SERIAL_NUMBER FROM ci LEFT JOIN LATERAL ( SELECT min(ctle.ENTRY_TIMESTAMP) ENTRY_TIMESTAMP FROM ct_log_entry ctle WHERE ctle.CERTIFICATE_ID = ci.ID ) le ON TRUE, ca WHERE ci.ISSUER_CA_ID = ca.ID ORDER BY le.ENTRY_TIMESTAMP DESC NULLS LAST;
Why show this? It's because Sectigo has the (read only) PostgreSQL database cluster directly on the internet. If you have the client installed, you can do:
psql -h crt.sh -p 5432 -U guest certwatch
right now. Here's the first page of tables (names only due to the horrible Substack code blocks):
accepted_roots applicable_purpose bugzilla_attachment bugzilla_bug bugzilla_mention ca ca_certificate ca_id_seq ca_issuer ca_trust_purpose cached_response ccadb_caowner ccadb_certificate certificate certificate_2013andbefore certificate_2014 certificate_2015 certificate_2016 certificate_2017 certificate_2018 certificate_2019 certificate_2020 certificate_2021 certificate_2022 certificate_2023 certificate_2024andbeyond certificate_and_identities certificate_id_seq certificate_identity certificate_lifecycle crl ...
I bring this up here to note that ROB KNOWS WHAT HE IS DOING and this is NOT sensitive data. I used to measure exposure on the internet using some very high-tech scanning infrastructure. So many people make the mistake of exposing databases directly, and the number of compromised database servers out there is staggering and saddening. If you don't believe me, go ask Censys.
I guarantee you are likely nowhere near as clever or competent as Rob, and SHOULD NOT DO THIS unless you also really know what you are doing. Honestly, with the WireGuard tech mentioned in the previous section, no team or business really need to do something like this to make their own databases more easily accessible.
The neighbors have Christmas lights up already.
I saw the Mitmproxy update last week and discovered yesterday that the homebrew formula isn't up to date. I can't decide if my weekend activity will be trying to contribute to correcting that or if I'll just test with a container. Very excited about the new convenience and features.