On Network Interfaces and Sockets and MySQL Users

Network Interfaces

A computer usually has one or more network interfaces. Traditionally these correspond with actual physical network interface cards (NICs,) but there are also virtual interfaces (for virtual networks) and sub-interfaces. Each interface has an address for each protocol it speaks. For example:

$ ifconfig
eth0      Link encap:Ethernet  HWaddr 08:00:27:27:9f:5b 
          inet addr:131.181.125.21  Bcast:131.181.125.255  Mask:255.255.255.0
          inet6 addr: fe80::7bf0:89d2:82d6:52b5/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:498614 errors:0 dropped:0 overruns:0 frame:0
          TX packets:164551 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:178470125 (178.4 MB)  TX bytes:20115698 (20.1 MB)

eth1      Link encap:Ethernet  HWaddr 08:00:27:04:d1:46 
          inet addr:192.168.56.101  Bcast:192.168.56.255  Mask:255.255.255.0
          inet6 addr: fe80::77ca:11b1:4693:6c4e/64 Scope:Link
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:5545 errors:0 dropped:0 overruns:0 frame:0
          TX packets:976 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:599732 (599.7 KB)  TX bytes:189542 (189.5 KB)

lo        Link encap:Local Loopback 
          inet addr:127.0.0.1  Mask:255.0.0.0
          inet6 addr: ::1/128 Scope:Host
          UP LOOPBACK RUNNING  MTU:65536  Metric:1
          RX packets:630 errors:0 dropped:0 overruns:0 frame:0
          TX packets:630 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1
          RX bytes:66588 (66.5 KB)  TX bytes:66588 (66.5 KB)

tun0      Link encap:UNSPEC  HWaddr 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00 
          inet addr:172.28.19.180  P-t-P:172.28.19.180  Mask:255.255.255.255
          UP POINTOPOINT RUNNING NOARP MULTICAST  MTU:1300  Metric:1
          RX packets:70344 errors:0 dropped:0 overruns:0 frame:0
          TX packets:66166 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:500
          RX bytes:19308394 (19.3 MB)  TX bytes:4501547 (4.5 MB)

I have two physical (called eth0 and eth1) and two virtual (lo – loopback – and tun0 – my VPN tunnel) interfaces. The physical interfaces have Ethernet addresses ("HWaddr"), all of them have IPv4 addresses ("inet addr"), and all but the tunnel have IPv6 addresses ("inet6 addr"). Any IPv4 packet that comes out of an interface has the interface's IPv4 address in its "source address" header field. I.e. when you connect through an interface, you use that interface's address.

Sub-interfaces are a logical way to assign multiple addresses to a single interface. For example, on eprints01:

eth1      Link encap:Ethernet  HWaddr 00:50:56:81:4A:F9 
          inet addr:131.181.108.175  Bcast:131.181.108.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1
          RX packets:3061071845 errors:0 dropped:0 overruns:0 frame:0
          TX packets:3088297840 errors:0 dropped:0 overruns:0 carrier:0
          collisions:0 txqueuelen:1000
          RX bytes:2051440318461 (1.8 TiB)  TX bytes:1906068417248 (1.7 TiB)

eth1:1    Link encap:Ethernet  HWaddr 00:50:56:81:4A:F9 
          inet addr:131.181.108.87  Bcast:131.181.108.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

eth1:2    Link encap:Ethernet  HWaddr 00:50:56:81:4A:F9 
          inet addr:131.181.108.95  Bcast:131.181.108.255  Mask:255.255.255.0
          UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

Sockets

In typical network programming the bind(2) function is used to bind a newly-created socket to a network interface, thus giving all communications over that socket the interface's local address. It's possible to pass a "null" interface parameter to bind(), in which case the operating system chooses which interface/address to use.

MySQL Users

MySQL users are identified by a combination of the user-supplied username and the IP address of the client. (E.g. 'matty'@'131.181.125.21') Sometimes it's written as a DNS name (e.g. 'matty'@'some-computer.library.qut.edu.au') but the MySQL server is clever enough to cross-reference DNS names and IP addresses as required.

To connect to a MySQL server, therefore, you need to be able to control which interface your socket is bound to. In some cases there is so much abstraction involved there's no way for the developer to instruct the MySQL client to bind to a particular interface. Presumably, in that case, it defaults to "null" (i.e. operating system's choice.)

Which is stupid, because that's a really important variable to be able to control, as it's part of the connecting user's identity.

Route Tables

On Linux you can use the command ip route to view and update your IP routing tables. I'm given to believe that this can be used to force network communications out over a particular interface. Here's the route tables on eprints01:

$ ip route show
131.181.108.0/24 dev eth1  proto kernel  scope link  src 131.181.108.175
131.181.186.0/24 dev eth0  proto kernel  scope link  src 131.181.186.58
131.181.185.0/24 dev eth2  proto kernel  scope link  src 131.181.185.49
169.254.0.0/16 dev eth2  scope link
default via 131.181.186.1 dev eth0

For some reason it's configured to send all connections to 169.254.* out on eth2, but most other outward connections should – by default – go out on eth0. So if you set a up a route for your MySQL server you should be able to control the user's address. Sadly, there doesn't seem to be a way to add routes that use sub-interfaces.