

Discover more from hrbrmstr's Daily Drop
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.
mitmproxy 9
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.
FIN
The neighbors have Christmas lights up already. O_O
☮
Drop #132 (2022-11-03): War: What Is It Good For?
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.