24 Oct
2022
24 Oct
'22
8:40 p.m.
On 10/24/22 11:40, Gary wrote:
Is it some kind of a Monte Carlo simulation against a
ruleset over a data space?
Modeling it in SQL, so just a query or two. (I parse raw
iptables-save in XML format to grab the data.)
One of the big surprises was finding you can model the
address space as a square box in cartesian coordinates
(upper left x, upper left y, lower right x, lower right y)
mapped from (IP address range begin, port range begin,
IP address range end, port range end), then use conventional
logic for "does box A cover box B", which PostgreSQL has
operators for. A simplfiied sketch of the query to
discover rules that cover other rules is:
SELECT mumble
FROM rules a
JOIN rules b
ON a.rulenum < b.rulenum
AND covers(
a.srcipaddrbegin, a.srcportend, a.srcipaddrend,
a.srcportbegin,
b.srcipaddrbegin, b.srcportend, b.srcipaddrend,
b.srcportbegin
)
AND covers(
a.dstipaddrbegin, a.dstportend, a.dstipaddrend,
a.dstportbegin,
b.dstipaddrbegin, b.dstportend, b.dstipaddrend,
b.dstportbegin
);
where covers() is a function:
CREATE OR REPLACE FUNCTION covers(ulxa DOUBLE PRECISION,
ulya DOUBLE PRECISION,
lrxa DOUBLE PRECISION,
lrya DOUBLE PRECISION,
ulxb DOUBLE PRECISION,
ulyb DOUBLE PRECISION,
lrxb DOUBLE PRECISION,
lryb DOUBLE PRECISION
)
RETURNS BOOLEAN
AS $$
-- Interpret two (IP address:port) ranges
-- geometrically, return TRUE if "A" covers "B"
-- or is coincident (perfectly overlaps).
DECLARE
covered boolean;
BEGIN
SELECT INTO covered
box( point(ulxb, ulyb), point(lrxb, lryb))
<@ -- "covered by OR on"
box( point(ulxa, ulya), point(lrxa, lrya));
RETURN covered;
END;
$$ LANGUAGE plpgsql;