Skip to content

Latest commit

 

History

History
502 lines (411 loc) · 17.7 KB

nat.org

File metadata and controls

502 lines (411 loc) · 17.7 KB

Notes regarding the NAT schema

Concept / Requirements

I’ll assume you’ve looked at the sql table definitions. All we want to realize (by means of foreign keys or otherwise) are the following relations:

Relational concept / nomenclature

In there, we have:

Dom
Nat domain. In practice, everything will be under a single nat domain of ID 1.
id
int
name
str
InNet
Internal network. The network of e.g. a user.
nat_domain
int
ip_network
inet
gateway
inet
OutIp
Outside IP. The thing a user is NATted to.
nat_domain
int
ip_address
inet
owner
int
Trans
Translation
nat_domain
int
outside_address
inet
inside_network
inet
owner
int
Forw
Port forwarding.
nat_domain
int
outside_address
inet
protocol
ip_protocol
outside_port
ip_port
inside_address
inet
inside_port
ip_port
comment
text
Res
DHCP host reservation.
nat_domain
int
ip
inet
mac
macaddr

Definitions of the relations

outip_dom (Hard FKey)

FKey outip(dom) → dom(id)

Technically, this key is redundant, because the domain is already given my the domain of the referenced inside network. However, since the inside network is referenced only “softly” (i.e., without an FKey), it’s helpful to include it.

innet_dom (Hard FKey)

FKey innet(dom) → dom(id)

res_dom (Hard FKey)

FKey innet(dom) → dom(id)

trans_outip (Hard (composite) FKey)

Composite FKey trans(out_ip, dom) → outip(ip, dom)

forw_outip (Hard (composite) FKey)

Composite FKey forw(dom, out_ip) → outip(dom, ip)

forw_inip (Hard (composite) FKey)

Composite FKey forw(dom, in_ip) → res(dom, in_ip)

trans_innet

Left totality

**Each translation must be contained in a corresponding insidenetwork**

Left uniqueness

$\text{trans}∼ \text{inet1} ∧ \text{trans}∼ \text{inet2} \implies \text{inet1} = \text{inet2}$

This implies that there may not be two overlapping insidenetworks.

Ergo: **Insidenetworks must be disjoint**

res_inip

Left totality

**Each reservation must be contained in a corresponding insidenetwork**

Left uniqueness

Same argumentation as for trans_innet: **Insidenetworks must be disjoint**

Additional Requirements

  • trans_outip should be surjective
  • Trans.innet should be disjoint (is there an abstract reason why?)

Commutativity of refs

Surely, a translation should always reference an in_net and an out_ip of the same domain. Speaking more precisely, this means that the following references should commute:$$ \text{trans\_innet}\then\text{innet\_dom} = \text{trans\_outip}\then\text{outip\_dom} $$

To sum up, we get the following commutativity rules:

Behavioral requirements

Cascades

Most relationships given there should have corresponding ON UPDATE / ON DELETE options. To be listed.

A user changes his IP

A user moves out

  • OutIp.owner will be set to null
  • All corresponding Host reservations (ref’d by a forwarding ref’ing this IP) should be deleted
  • All corresponding Forwardings (ref’ing this out_ip) should get deleted
  • All corresponding translations should get deleted

Local Requirements

  • IPs should be v4
  • Outside-Ips should be /32
  • gateway of InsideNetwork should be part of the network
  • Forwarding protocol must be in $\{6,17,33,132\}$
  • IP ports must be in $\{1..65535\}$
  • IP protocols must be in $\{0..255\}$ (okay, that’s kinda redundant, but if we’re doing custom types anyway, we might as well itnroduce that one)

Motivating example

Consider

  • A Domain with id 1 (not explicitly mentioned in the other tables)
  • An OutsideIpAddress(1.1.1.1/32, owner=Hans)
  • An InternalNetwork(10.64.1.0/24), meant to be Hans’ Home network
  • An InternalNetwork(10.100.1.0/24), meant to be the roaming network of some location used by many people
  • A Translation(10.64.1.0/24, 1.1.1.1/32)
  • A Translation(10.100.1.1/32, 1.1.1.1/32)

This already emphasizes some key features of this schema:

  • An OutsideIpAddress may have multiple translations associated to it
  • The inclusion Translation.internal_network $\subseteq$ InternalNetwork.ip_network can be proper

Realization (Schrader)

Custom domains

CREATE DOMAIN public.ip_port AS integer
	CONSTRAINT ip_port_check CHECK (((VALUE >= 1) AND (VALUE <= 65535)));
CREATE DOMAIN public.ip_protocol AS smallint
	CONSTRAINT ip_protocol_check CHECK (((VALUE >= 0) AND (VALUE <= 255)));

Perhaps follow the JSON implementation

Also, see the CreateColumn example

Testing the custom type

To test, perhaps select the following minimal example:

from sqlalchemy import Table, Metadata, Column, Integer, String
from sqlalchemy import schema

metadata = MetaData()

table = Table('mytable', MetaData(),
        Column('x', Integer, info={"special":True}, primary_key=True),
        Column('y', String(50)),
        Column('z', String(20), info={"special":True})
    )

metadata.create_all(conn)

…and in a test, we might do the following:

import pytest
from pycroft.model.types import IpPort

@pytest.fixture(scope='session')
def metadata():
    metadata = MetaData()
    table = Table(
        'mytable', metadata,
        Column('x', Integer, primary_key=True),
        Column('z', IpPort)
    )
    return metadata

@pytest.fixture(scope='session')
def conn(metadata):
    try:
        this.metadata.create_all(this.conn)
    except OperationalError:
        this.fail("Could not create table with custom type")
    finally:
        pass  # TODO rollback stuff

def test_value_set(conn):
    # TODO add a row with that value and retrieve it again
    pass

def test_value_filter(conn):
    # TODO add two rows with that value and filter after the one
    pass

def test_constraint(conn):
    # TODO try to add something violating the check constraints
    pass

Perhaps the following should suffice:

class IpPort(sqltypes.TypeEngine):
    """Represent our custom IpPort type.
    """

    __visit_name__ = "Test What should go here"


ischema_names["ip_port"] = IpPort

Local Constraints

Collect PKey-Constraints

Collect Uniq-Constraints

OutsideIpAddress.ip_address is /32,v4

--- OutsideIPAddress is /32v4
CREATE TABLE public."OutsideIPAddress" (
    nat_domain integer NOT NULL,
    ip_address inet NOT NULL,
    owner integer,
    CONSTRAINT "OutsideIPAddress_ip_address_host"
        CHECK (((family(ip_address) = 4) AND (masklen(ip_address) = 32)))
);

Realizable as follows:

__table_args__ = (
    CheckConstraint(
        '(((family(ip_address) = 4) AND (masklen(ip_address) = 32)))',
        name='outside_ip_is_host_and_v4',
    ),
)

OutsideIpAddress.ip is /32,v4

--- DhcpHostReservation refs /32v4
ALTER TABLE public."DHCPHostReservation"
    ADD CONSTRAINT "DHCPHostReservation_ip_check"
    CHECK (((family(ip) = 4) AND (masklen(ip) = 32))) NOT VALID;

Forwarding.protocol $∈ \{6, 17, 33, 132\}$ 

--- Forwarding protocol in ${6,17,33,132}$
ALTER TABLE public."Forwarding"
    ADD CONSTRAINT "Forwarding_protocol_port_check" CHECK (
CASE
    WHEN ((outside_port IS NOT NULL) OR (inside_port IS NOT NULL))
    THEN ((protocol)::smallint = ANY (ARRAY[6, 17, 33, 132]))
    ELSE NULL::boolean
END) NOT VALID;

InsideNetwork.(gateway << ip_network)

--- Gateway << InsideNetwork
ALTER TABLE public."InsideNetwork"
    ADD CONSTRAINT "InsideNetwork_gateway_check"
    CHECK ((gateway << ip_network)) NOT VALID;

InsideNetwork.ip_network is /32,v4

--- in_net is v4
ALTER TABLE public."InsideNetwork"
    ADD CONSTRAINT "InsideNetwork_ip_network_family_check"
    CHECK ((family(ip_network) = 4)) NOT VALID;

Rel constraints

Collect FKey-Constraints

Forwarding needs a translation (unused)

--- TODO hold on a sec, this is not even used!
CREATE FUNCTION public."Forwarding_Translation_exists"() RETURNS trigger
    LANGUAGE plpgsql
    AS $$BEGIN
	IF NOT EXISTS(
            SELECT FROM "Translation"
            WHERE nat_domain = NEW.nat_domain
            AND outside_address = NEW.outside_address
            AND inside_network >> NEW.inside_address
        ) THEN
		RAISE EXCEPTION 'No corresponding Translation exists for Forwarding: %',
                NEW USING ERRCODE = 'integrity_constraint_violation';
	END IF;
	RETURN NEW;
END$$;
'col2 > col3 + 5', name='check1'

InsideNetwork.ip_network disjoint

-- in_nets are disjoint
ALTER TABLE ONLY public."InsideNetwork"
    ADD CONSTRAINT "InsideNetwork_nat_domain_ip_network_excl"
    EXCLUDE USING gist (nat_domain WITH =, ip_network inet_ops WITH &&);

DHCPHostReservation.inside_network exists

reasoning

This realizes the weak fkey =DHCPHostReservation.inside_network<<=InsideNetwork

src

CREATE FUNCTION public."DHCPHostReservation_InsideNetwork_exists"()
 RETURNS trigger
    LANGUAGE plpgsql STABLE STRICT LEAKPROOF
    AS $$BEGIN
	IF NOT EXISTS(
            SELECT FROM "InsideNetwork"
            WHERE nat_domain = NEW.nat_domain
            AND ip_network >> NEW.ip
        ) THEN
		RAISE EXCEPTION 'InsideNetwork contains no ip_network for IP % in nat_domain %',
                NEW.ip, NEW.nat_domain USING ERRCODE = 'integrity_constraint_violation';
	END IF;
	RETURN NEW;
END$$;
CREATE CONSTRAINT TRIGGER "DHCPHostReservation_InsideNetwork_exists"
    AFTER INSERT OR UPDATE OF ip ON public."DHCPHostReservation"
    DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW
    EXECUTE PROCEDURE public."DHCPHostReservation_InsideNetwork_exists"();

Translation.inside_network disjoint

--- Translation in_nets excluded
ALTER TABLE ONLY public."Translation"
    ADD CONSTRAINT "Translation_Inside_excl"
    EXCLUDE USING gist (nat_domain WITH =, inside_network inet_ops WITH &&);

∀Translation ∃InsideNetwork

reasoning

This realizes the weak fkey Translation.inside_network<<=InsideNetwork

src

CREATE FUNCTION public."Translation_InsideNetwork_exists"() RETURNS trigger
    LANGUAGE plpgsql STRICT
    AS $$BEGIN
	IF NOT EXISTS(SELECT FROM "InsideNetwork" WHERE nat_domain = NEW.nat_domain AND ip_network >>= NEW.inside_network) THEN
		--RAISE EXCEPTION 'No corresponding InsideNetwork for Translation: %', NEW USING ERRCODE = 'integrity_constraint_violation';
		RAISE EXCEPTION integrity_constraint_violation USING DETAIL = FORMAT('No corresponding %I for %I: %s', 'InsideNetwork', 'Translation', NEW), TABLE = 'Translation';
	END IF;
	RETURN NEW;
END$$;
CREATE CONSTRAINT TRIGGER "Translation_InsideNetwork_exists"
    AFTER INSERT OR UPDATE OF inside_network ON public."Translation"
    DEFERRABLE INITIALLY IMMEDIATE
    FOR EACH ROW EXECUTE PROCEDURE public."Translation_InsideNetwork_exists"();

Cascades

InsideNetwork del/up/trunc

reasoning

These are effectively cascades to DHCPHostReservation, Translation. This is necessary because

  • DHCPHostReservation.ip << InsideNetwork.ip_network
  • Translation.inside_network << InsideNetwork.ip_network

are weak couplings instead of FKeys.

src

CREATE FUNCTION public."InsideNetwork_delete"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	DELETE FROM "DHCPHostReservation" WHERE nat_domain = OLD.nat_domain AND ip << OLD.Ip_network;
	DELETE FROM "Translation" WHERE nat_domain = OLD.nat_domain AND inside_network <<= OLD.ip_network;
	RETURN NULL;
END$$;
CREATE TRIGGER "InsideNetwork_delete" AFTER DELETE ON public."InsideNetwork" FOR EACH ROW EXECUTE PROCEDURE public."InsideNetwork_delete"();

CREATE FUNCTION public."InsideNetwork_truncate"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	TRUNCATE "DHCPHostReservation";
	TRUNCATE "Translation";
	RETURN NULL;
END$$;
CREATE TRIGGER "InsideNetwork_truncate" AFTER TRUNCATE ON public."InsideNetwork" FOR EACH STATEMENT EXECUTE PROCEDURE public."InsideNetwork_truncate"();

CREATE FUNCTION public."InsideNetwork_update"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	DELETE FROM "DHCPHostReservation" WHERE nat_domain = OLD.nat_domain AND ip << OLD.ip_network AND NOT ip << NEW.ip_network;
	DELETE FROM "Translation" WHERE nat_domain = OLD.nat_domain AND inside_network <<= OLD.ip_network AND NOT inside_network <<= NEW.ip_network;
	RETURN NULL;
END$$;
CREATE TRIGGER "InsideNetwork_update" AFTER UPDATE OF ip_network ON public."InsideNetwork" FOR EACH ROW EXECUTE PROCEDURE public."InsideNetwork_update"();

Translation del/up (unused)

--- UNUSED
CREATE FUNCTION public."Translation_delete"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	DELETE FROM "Forwarding" WHERE nat_domain = OLD.nat_domain AND inside_address <<= OLD.inside_network;
	RETURN NULL;
END$$;

--- UNUSED
CREATE FUNCTION public."Translation_update"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	DELETE FROM "Forwarding" WHERE nat_domain = OLD.nat_domain AND inside_address <<= OLD.inside_network AND NOT inside_address <<= NEW.inside_network;
	RETURN NULL;
END$$;

Translation truncate

reasoning?

This realizes a cascade to Forwarding – but wouldn’t this make more sense on InsideNetwork or OutsideIpAddress? Or anything to which we have a weak relationship?

src

CREATE FUNCTION public."Translation_truncate"() RETURNS trigger
    LANGUAGE plpgsql STRICT LEAKPROOF
    AS $$BEGIN
	TRUNCATE "Forwarding";
	RETURN NULL;
END$$;
CREATE TRIGGER "Translation_truncate" AFTER TRUNCATE ON public."Translation" FOR EACH STATEMENT EXECUTE PROCEDURE public."Translation_truncate"();
--- FKEY-Constraints