<div dir="ltr"><div>WE still keep IP's in string fields. This email is NOT about that issue (but I know it would help).</div><div><br></div><div>I'm focusing on how we can improve some of our IP address fields and make sure that we have some increased form of authentication of user-entered IPs. </div><div><br></div><div>This assume a pre IX-F world; but in fact there's zero reason to say that statement. IX-F exists and yet I think I can continue to proceed independent of that.</div><div><br></div><div>Background: I've focused once or twice (along with Grizz, Andy D, etc) in the IPs used within PeeringDB (1.0 and 2.x). Collectively, we have cleaned up so much crud; however there more to do.</div><div><br></div><div>Here are some dumps of data that may show some still incorrect data. I'm singularly focusing on the <b>prefix</b> column of the <b>peeringdb_ixlan_prefix</b> table.</div><div><br></div>So I want to address a few LANs as I believe there's nearly always a need for these to actually be something different (/64's being the norm for v6, /24's for v4). <div><br></div><div>In the first case, I think we have some interesting choices out there (and the HKIX allocation was a good example of an early v6 IX allocation); however it's now now and these seems wrong. I can't explain the /128's ... as that's not a "LAN".</div><div><br></div><div><div><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">; </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">; IPv6 smaller than /64's</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">; </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196);min-height:14px"><span style="font-variant-ligatures:no-common-ligatures"></span><br></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">mysql> SELECT <a href="http://peeringdb_ix.name">peeringdb_ix.name</a> AS ix_name, peeringdb_ix.country AS ix_cc, <a href="http://peeringdb_ixlan.name">peeringdb_ixlan.name</a> AS prefix_name, protocol, prefix</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> FROM peeringdb_ixlan_prefix</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> LEFT JOIN peeringdb_ixlan ON <a href="http://peeringdb_ixlan.id">peeringdb_ixlan.id</a> = peeringdb_ixlan_prefix.ixlan_id</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> LEFT JOIN peeringdb_ix ON <a href="http://peeringdb_ix.id">peeringdb_ix.id</a> = peeringdb_ixlan.ix_id</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"><font color="#4c2f2d"> WHERE prefix LIKE '</font><font color="#ff0000">%/9%</font><font color="#4c2f2d">' OR prefix LIKE '</font><font color="#ff0000">%/1%</font><font color="#4c2f2d">'</font></span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> ORDER BY ix_cc, ix_name;</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">+---------------+-------+----------------+----------+-----------------------------------+</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| ix_name | ix_cc | prefix_name | protocol | prefix | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">+---------------+-------+----------------+----------+-----------------------------------+</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| TorIX | CA | | IPv6 | 2001:504:1a::34:0/111 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| WPGIX | CA | | IPv6 | 2001:504:2c::/116 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DIX | DK | <a href="http://rs01.dix.dk">rs01.dix.dk</a> | IPv6 | 2001:7f8:1f::248/128 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DIX | DK | <a href="http://rs02.dix.dk">rs02.dix.dk</a> | IPv6 | 2001:7f8:1f::249/128 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| FR-IX | FR | | IPv6 | 2001:7f8:59:0:75::/96 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| MyIX | MY | | IPv6 | 2001:de8:10::/112 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| EPIX.Katowice | PL | | IPv6 | 2001:7f8:5b::1/128 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DataLine-IX | RU | SHARED-UNICAST | IPv6 | 2001:7f8:62:52::100/128 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DataLine-IX | RU | SHARED-UNICAST | IPv6 | 2001:7f8:62:52::200/128 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| TPIX-TW | TW | | IPv6 | 2406:d400:1:133:203:163:222:0/112 |</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| UA-IX | UA | | IPv6 | 2001:7f8:5d::/116 | </span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">+---------------+-------+----------------+----------+-----------------------------------+</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">11 rows in set (0.01 sec)</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">mysql></span></p><div><br></div></div></div><div><div>In the second case, I think these are RIR allocations placed in PDB vs the allocated LAN. I skipped the /48's as there are tons of those!</div><div><br></div><div>I believe they also really operate as /64's.<br></div></div><div><br></div><div><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">; </span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">; These are IXs that have placed their site allocations into PDB vs their LAN. In all cases, I bet they are really /64's</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">; </span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196);min-height:14px"><span style="font-variant-ligatures:no-common-ligatures"></span><br></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">mysql> SELECT <a href="http://peeringdb_ix.name">peeringdb_ix.name</a> AS ix_name, peeringdb_ix.country AS ix_cc, <a href="http://peeringdb_ixlan.name">peeringdb_ixlan.name</a> AS prefix_name, protocol, prefix</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> FROM peeringdb_ixlan_prefix</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> LEFT JOIN peeringdb_ixlan ON <a href="http://peeringdb_ixlan.id">peeringdb_ixlan.id</a> = peeringdb_ixlan_prefix.ixlan_id</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> LEFT JOIN peeringdb_ix ON <a href="http://peeringdb_ix.id">peeringdb_ix.id</a> = peeringdb_ixlan.ix_id</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;background-color:rgb(223,219,196)"><span style="color:rgb(76,47,45)"><span style="font-variant-ligatures:no-common-ligatures"> WHERE </span><span style="font-family:courier">protocol = "IPv6" AND ( </span>( prefix LIKE '</span><font color="#ff0000">%/32</font><font color="#4c2f2d">' OR prefix LIKE '</font><font color="#ff0000">%/4%</font><font color="#4c2f2d">' OR prefix LIKE '</font><font color="#ff0000">%/5%</font><font color="#4c2f2d">' ) AND prefix NOT like '</font><font color="#ff0000">%/48</font><font color="#4c2f2d">' )</font></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> ORDER BY ix_cc, ix_name;</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">+--------------+-------+--------------+----------+-----------------------+</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| ix_name | ix_cc | prefix_name | protocol | prefix | </span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">+--------------+-------+--------------+----------+-----------------------+</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| AuvernIX | FR | AuvernIX LAN | IPv6 | 2001:7f8:81::/56 |</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| AuvernIX | FR | LyonIX LAN | IPv6 | 2001:7f8:81:100::/56 |</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"></span></p><p style="margin:0px;line-height:normal;background-color:rgb(223,219,196)"><font color="#4c2f2d" face="courier"><span style="font-size:12px">| Hopus | FR | | IPv6 | 2a02:e5c::/32 |</span></font></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| IX Liverpool | GB | Titanic | IPv6 | 2001:7f8:a2::/49 |</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| IX Liverpool | GB | Mersey | IPv6 | 2001:7f8:a2:8000::/49 |</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| SGIX | SG | | IPv6 | 2001:de8:12::/56 |</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">+--------------+-------+--------------+----------+-----------------------+</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">5 rows in set (0.00 sec)</span></p><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">mysql></span></p></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div><div><span style="font-variant-ligatures:no-common-ligatures">Third case. What about DIX ? We seem to allow:</span></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div><div><span style="font-variant-ligatures:no-common-ligatures"><p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">mysql> SELECT <a href="http://peeringdb_ix.name">peeringdb_ix.name</a> AS ix_name, peeringdb_ix.country AS ix_cc, <a href="http://peeringdb_ixlan.name">peeringdb_ixlan.name</a> AS prefix_name, protocol, prefix</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> FROM peeringdb_ixlan_prefix</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> LEFT JOIN peeringdb_ixlan ON <a href="http://peeringdb_ixlan.id">peeringdb_ixlan.id</a> = peeringdb_ixlan_prefix.ixlan_id</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> LEFT JOIN peeringdb_ix ON <a href="http://peeringdb_ix.id">peeringdb_ix.id</a> = peeringdb_ixlan.ix_id</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"><font color="#4c2f2d"> WHERE ix_name = '</font><font color="#ff0000">DIX</font><font color="#4c2f2d">'</font></span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures"> ORDER BY ix_cc, ix_name;</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">+---------+-------+-------------+----------+----------------------+</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| ix_name | ix_cc | prefix_name | protocol | prefix |</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">+---------+-------+-------------+----------+----------------------+</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DIX | DK | | IPv6 | 2001:7f8:1f::/48 |</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DIX | DK | DIX LAN | IPv4 | <a href="http://192.38.7.0/24">192.38.7.0/24</a> |</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DIX | DK | DIX LAN | IPv6 | 2001:7f8:1f::/64 |</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DIX | DK | <a href="http://rs01.dix.dk">rs01.dix.dk</a> | IPv4 | <a href="http://192.38.7.248/32">192.38.7.248/32</a> |</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DIX | DK | <a href="http://rs01.dix.dk">rs01.dix.dk</a> | IPv6 | 2001:7f8:1f::248/128 |</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DIX | DK | <a href="http://rs02.dix.dk">rs02.dix.dk</a> | IPv4 | <a href="http://192.38.7.249/32">192.38.7.249/32</a> |</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">| DIX | DK | <a href="http://rs02.dix.dk">rs02.dix.dk</a> | IPv6 | 2001:7f8:1f::249/128 |</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">+---------+-------+-------------+----------+----------------------+</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">7 rows in set (0.01 sec)</span></p>
<p style="margin:0px;font-size:12px;line-height:normal;font-family:courier;color:rgb(76,47,45);background-color:rgb(223,219,196)"><span style="font-variant-ligatures:no-common-ligatures">mysql></span></p></span></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div><div><span style="font-variant-ligatures:no-common-ligatures">A /48 and a /64 (plus two /128's). Huh? I guess we are an IPAM now. :)</span></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div><div><span style="font-variant-ligatures:no-common-ligatures">While these are the v6 examples, the same holds true for v4; but with plenty of variance. ESPANIX is a pair of /25's (from a single /24) that make up their two LANs. No issue there. There's are plenty or /23 or /22 LANs and there are some smaller lLANs.</span></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div><div><span style="font-variant-ligatures:no-common-ligatures">There is some background here. The prefix column technically defines the range of an IP see within the <b>ipaddr4</b> and <b>ipaddr6</b> fields within <b>peeringdb_network_ixlan</b>. That is a vital restriction placed on the users data.</span></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div><div><span style="font-variant-ligatures:no-common-ligatures">I would like to recommend a cleanup or realization of why we have this extra data in the <b>peeringdb_ixlan_prefix</b> table.</span></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div><div><span style="font-variant-ligatures:no-common-ligatures">Thought,</span></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div><div><span style="font-variant-ligatures:no-common-ligatures">Martin</span></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div><div><span style="font-variant-ligatures:no-common-ligatures"><br></span></div></div>