Category: Linux

Upgrading Legacy Versions of Ubuntu

The standard Ubuntu versions go EOL quite quickly and it’s easy to miss the upgrade window such that running do-release-upgrade yields:

An upgrade from 'groovy' to 'impish' is not supported with this tool.

In this example, Ubuntu is looking to upgrade from 20.10 to 21.10 skipping 21.04 which is not supported. You’ve probably also reached a situation where you cannot even upgrade your current packages as the repositories have also been EOL’d and do not exist.

To upgrade step-wise, we need to upgrade our current platform first. You need to be logged in as root or using sudo for all of the following.

Start by changing your mirror in /etc/apt/sources.list to use old-releases.ubuntu.com. For example, in my case my mirror was ie.archive.ubuntu.com and so I can replace that via:

sed -i -e 's/ie.archive.ubuntu.com/old-releases.ubuntu.com/g' /etc/apt/sources.list

Once that’s done, upgrade your current system as usual:

apt-get update
apt-get dist-upgrade
shutdown -r now

Now that your current system is up-to-date, we need to do a distribution upgrade to 21.04 hirsute (in my case). do-release-upgrade will still not work so we need to manually download the upgrade tool and run that ourselves. Find the appropriate UpgradeTool file from Ubuntu’s meta-release page here. In my case the appropriate upgrade file was hirsute.tar.gz and I downloaded that via:

wget http://archive.ubuntu.com/ubuntu/dists/hirsute-updates/main/dist-upgrader-all/current/hirsute.tar.gz

You now need to extract and run the tool:

mkdir hirsute_files
cd hirsute_files
tar zxf ../hirsute.tar.gz
./hirsute

If you’re as fortunate as me, this will run cleanly just as it would have via do-release-upgrade. If you have no more intermediary versions, you can do the final upgrade via do-release-upgrade as normal. Remember also that upgrading from one LTS version to another is also supported by that tool.

Using Laraval Eloquent Models for API Results

There’s a very interesting package called calebporzio/sushi for Laravel that allows one to use arrays as Eloquent drivers / sources of data. @calebporzio posted his own example of using this to front API results here.

It’s a very interesting proof of concept for this use case (probably needs more work and more knobs for production use). So interesting, I had a quick look myself with a bare bones Laravel app:

$ laravel new test-sushi
$ cd test-sushi
$ composer require calebporzio/sushi
$ composer require kitetail/zttp
$ php artisan make:model IxpdbProviders

The only interesting part of the model, IxpdbProviders, is the getRows() function:

public function getRows()
{
  return Cache::remember( 'IxpdbProvider::rows', 3600, function() {

    return array_map( function( $a ) {
      foreach( $a as $k => $v ) {
        if( is_array( $v ) ) {
          unset( $a[$k] );
        }
      }
      return $a;
    },
    Zttp::get('https://api.ixpdb.net/v1/provider/list')->json()
  );

});

There’s a few interesting things happening here:

  1. I’m using the cache to store the array result of:
    • the fairly large API response for one hour;
    • the array_map() which is required to remove sub-arrays (sub-objects) within the response as Sushi requires flat rows.
  2. Using Zttp out of curiosity rather than Guzzle directly.
  3. Sushi then takes the array of IXPs (the result of the API call) and stores these in a dedicated in-memory Sqlite database for the duration of the request.

We can now query this as if it were a typical database table:

$ php artisan tinker

>>> App\IxpdbProvider::count();
=> 581

>>> App\IxpdbProvider::where( 'name', 'like', 'inex%')->pluck('name')
=> Illuminate\Support\Collection {#3002
     all: [
       "INEX LAN1",
       "INEX LAN2",
       "INEX Cork",
     ],
   }

Kamailio v5.3 and MySQL 8

As installed on Ubuntu 19.10, Kamailio v5.3 will not work out of the box with MySQL 8 due to changes in the way in which users are created and privileges granted between MySQL 5.x and 8.

To fix this, edit /usr/lib/x86_64-linux-gnu/kamailio/kamctl/kamdbctl.mysql as follows:

# diff /usr/lib/x86_64-linux-gnu/kamailio/kamctl/kamdbctl.mysql.orig  /usr/lib/x86_64-linux-gnu/kamailio/kamctl/kamdbctl.mysql
163,164c163,166
<       sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
<               GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST' IDENTIFIED BY '$DBROPW';"
---
>       sql_query "" "CREATE USER '$DBRWUSER'@'$DBHOST' IDENTIFIED BY '$DBRWPW';
>                     CREATE USER '$DBROUSER'@'$DBHOST' IDENTIFIED BY '$DBROPW';
>               GRANT ALL PRIVILEGES ON $1.* TO '${DBRWUSER}'@'$DBHOST';
>               GRANT SELECT ON $1.* TO '${DBROUSER}'@'$DBHOST';"
172,173c174,177
<               sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost' IDENTIFIED  BY '$DBRWPW';
<                       GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';"
---
>               sql_query "" "CREATE USER '$DBRWUSER'@'localhost' IDENTIFIED BY '$DBRWPW';
>                               CREATE USER '$DBROUSER'@'localhost' IDENTIFIED BY '$DBROPW';
>                       GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'localhost';
>                       GRANT SELECT ON $1.* TO '$DBROUSER'@'localhost';"
181,182c185,188
<               sql_query "" "GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED  BY '$DBRWPW';
<                       GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';"
---
>               sql_query "" "CREATE USER '$DBRWUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBRWPW';
>                             CREATE USER '$DBROUSER'@'$DBACCESSHOST' IDENTIFIED BY '$DBROPW';
>                       GRANT ALL PRIVILEGES ON $1.* TO '$DBRWUSER'@'$DBACCESSHOST';
>                       GRANT SELECT ON $1.* TO '$DBROUSER'@'$DBACCESSHOST';"

The above worked fine for me but do note:

  • Make sure the database and users do not already exist on the database (or delete them if they do).
  • Use a different username for the read-only and read-write users.
  • MySQL 8 has a bug so issue FLUSH PRIVILEGES if you have trouble manually removing a user.

WireGuard – Linux Based VPN Server for iOS

Okay, I lied. WireGuard won’t just run on Linux for the server side but that is what it was originally designed for. Linux is the first class citizen as the WireGuard implementation there exists within the kernel.

I also lied about the clients – it’ll work on nearly any OS. We have been using OpenVPN with great success with many customers for years. We have our own management software and my macOS Viscosity client (highly recommended) has over 30 endpoints at this time. For various reasons, we use tap interfaces which just do not work for iOS.

I came across WireGuard a while ago and was intrigued by some of it’s design principles. Specifically:

  • UDP only (I remain, to this day, completely bewildered and baffled by any VPN running over TCP – yes, Mikrotik, I’m looking at your OpenVPN implementation);
  • how it presents as a simple network interface (and thus is configured via the normal iproute2 tools such as ip); and
  • its ssh-like public/private key exchange mechanism.

But I turned away as it stated that it was still a work in progress. It still states this but it looks pretty mature. Two gaps I have with OpenVPN right now seem to be filled by WireGuard: simple just works client for Apple iOS; and easy set-up mechanism for small deployments (e.g. I just want to get remote access to my home server without setting up a certificate authority or using static keys).

So, let’s look at setting up a server (Linux) / client (iOS) with WireGuard. As usual, I’m running the latest Ubuntu LTS on my server – in this case 18.04.

Important note about VPNs and dual-stack networks: many VPNs only work on IPv4. When using such VPNs on a foreign network with IPv6 support, you will only be protected for traffic that transit the IPv4 VPN. Any traffic that works over IPv6 will not go through your VPN – and today, this is a good chunk of traffic. The configuration below assumes your server is dual-stacked – which, today, it should be.

Note also in the examples below, I am using Google’s public DNS. You should install your own DNS resolver on your VPN server rather than using a third party one.

As WireGuard routes packets to and from its encrypted interface, you will need to ensure packet forward is enabled on your server:

sysctl net.ipv4.ip_forward=1
sysctl net.ipv6.conf.all.forwarding=1

Make this permanent by editing /etc/sysctl.conf.

Install WireGuard using its PPA via:

add-apt-repository ppa:wireguard/wireguard
apt-get update
apt-get install wireguard

WireGuard uses DKMS to build the module for the kernel you are running. It would be useful to do a dist-upgrade and reboot before installing this to put yourself on the latest kernel.

The installation of WireGuard above will install and build the kernel module, install the tools and create the /etc/wireguard directory. Let’s go there now and create keys for the server:

cd /etc/wireguard
# create a private server key:
wg genkey >server-private.key
chmod go-rwx server-private.key
# and create a public key from the private key:
cat server-private.key | wg pubkey >server-public.key

We may as well get ahead of ourselves and generate a key pair for our iOS client now also. When we’ve generated the configuration for the server and client, we can delete these key files from the server. In fact you should do this.

wg genkey >client1-private.key
cat client1-private.key | wg pubkey >client1-public.key

Now let’s create the server side configuration in /etc/wireguard/wg0.conf:

[Interface]
Address = 10.97.98.1/24, fd80:10:97:98::1/64
SaveConfig = false
DNS = 8.8.8.8, 2a00:1450:400b:c01::8b
ListenPort = 51820
PrivateKey = <contents of server-private.key>

# client1
[Peer]
PublicKey = <contents of client1-public.key>
AllowedIPs = 10.97.98.2/32, fd80:10:97:98::2/64

Again, chmod go-rwx wg0.conf.

The IPv6 addresses chosen above are unique local addresses (rfc4193) – similar to RFC1918 private addresses in IPv4. When choosing your IPv6 ULA, use a prefix generator such as this one. As we are using ULA addresses, we have to NAT IPv6. I hate doing this but it makes the example simple. If you have routable IPv6 addresses, try and use a real prefix without NAT.

You can now bring the tunnel up and down using the useful utility commands: wg-quick up wg0 and wg-quick down wg0. But you’ll probably want to enable them on systemd for auto-start on system boot:

systemctl enable wg-quick@wg0
systemctl start wg-quick@wg0

When up and running, you can examine the interface with ifconfig wg0 and see the state of clients with just wg:

# ifconfig wg0
wg0: flags=209<UP,POINTOPOINT,RUNNING,NOARP>  mtu 1420
        inet 10.97.98.1  netmask 255.255.255.0  destination 10.97.98.1
        inet6 fd80:10:97:98::1  prefixlen 64  scopeid 0x0<global>
        unspec 00-00-00-00-00-00-00-00-00-00-00-00-00-00-00-00  txqueuelen 1000  (UNSPEC)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0
# wg
interface: wg0
  public key: w29jZeurXAcABTTvA0V5pIOgK8jUZuYxNE9dCciN7Q8=
  private key: (hidden)
  listening port: 51821

peer: WrZzlF0fjMWKFqn/krqPrdyfYnlshLMwDNNiweEocRE=
  allowed ips: 10.97.98.2/32, fd80:10:97:98::2/128

WireGuard has an iOS client – download it from the AppStore here. One of its most useful features is the ability to add a configuration via a QR code (you will need to apt install qrencode on your server). Let’s create a client configuration in a text file on the server now:

[Interface]
PrivateKey = <contents of client1-private.key>
Address = 10.97.98.2/24, fd80:10:97:98::2/64
DNS = 8.8.8.8, 2a00:1450:400b:c01::8b

[Peer]
PublicKey = <contents of server-public.key>
Endpoint = <server-ip/hostname>:51820
AllowedIPs = 0.0.0.0/0, ::/0

Then generate the qrcode and display to screen with: qrencode -t ansiutf8 <client.conf. You’ll be able to import it by pointing your phone at the screen. Sample QR code:

There’s still a couple things you need to do to make this all work: allow UDP packets in your firewall and allow the forwarding and NATing of tunnelled traffic between the tunnel interface and the public internet facing interface(s). I don’t like to over-prescribe how to do this as there are different ways and different topologies. But let me give a basic example.

Start with allowing WireGuard traffic in your firewall – you need an iptables rules such as:

iptables  -A INPUT -p udp --dport 51820 -j ACCEPT
ip6tables -A INPUT -p udp --dport 51820 -j ACCEPT

For forwarding traffic, there are a number of options but the easiest is to use stateful rules to allow established / related traffic and assume everything coming in your encrypted tunnelled WireGuard interfaces is okay:

iptables -A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT
iptables -A FORWARD -i wg+ -j ACCEPT

ip6tables -A FORWARD -m state --state ESTABLISHED,RELATED -j ACCEPT
ip6tables -A FORWARD -i wg+ -j ACCEPT

Lastly, for NAT – and assuming eth0 is your public interface, use:

iptables  -t nat -A POSTROUTING -o eth+ -s 10.97.98.0/24 -j MASQUERADE
ip6tables -t nat -A POSTROUTING -o eth+ -s fd80:10:97:98::/64 -j MASQUERADE

Finally, test your set-up works for IPv4 and IPv6 using sites such as ipv6-test.com or ipleak.net.

You can add more peers by editing /etc/wireguard/wg0.conf and then restarting the tunnel interface via systemctl restart wg-guard@wg0. This will briefly disrupt existing tunnel traffic but it’s the simplest method. There are ways to add new tunnels on the command line but you need to remember to keep the configuration file in sync.

Linux (Ubuntu 16.04), PHP and MS SQL

In the many years I’ve been using the traditional LAMP stack, I’ve successfully managed to avoid having anything to do with MS SQL server. Until 2016. This year I’ve had to work quiet a bit with it – administration, backups and, now, scripted queries from Linux with PHP.

I suspect I’m (a) lucky I haven’t had to do this before now; and (b) that Azure seems to have pushed Microsoft into greater Linux based support for MS SQL. The evidence? This open source Mircosoft repository with a MS SQL PHP binary driver for Linux released just a few months ago.

NB: installing the Microsoft PHP driver is different to installing the Microsoft ODBC driver for SQL Server on Linux. These may even be incompatible.

For me, I just took a standard Ubuntu 16.04 install (64bit obviously) with PHP 7.0 and downloaded the latest MS PHP SQL extension (for me, at time of writing, this was 4.0.6. When you untar the Ubuntu16.tar file, copy the .so files to /usr/lib/php/20151012/ and then create a /etc/php/7.0/mods-available/msphpsql.ini file with contents:

extension=php_pdo_sqlsrv_7_nts.so
extension=php_sqlsrv_7_nts.so

Note that the tar also contains two ‘ts’ versions of these files. Trying to use those resulted in errors. Link this for Apache2 / CLI as required. E.g. for PHP CLI:

cd /etc/php/7.0/cli/conf.d/
ln -s ../../mods-available/msphpsql.ini 20-msphpsql.ini

You can confirm it’s working via:

$ php -i | grep sqlsrv
Registered PHP Streams => https, ftps, compress.zlib, php, file, glob, data, http, ftp, sqlsrv, phar
PDO drivers => sqlsrv
pdo_sqlsrv
pdo_sqlsrv support => enabled
pdo_sqlsrv.client_buffer_max_kb_size => 10240 => 10240
pdo_sqlsrv.log_severity => 0 => 0
sqlsrv
sqlsrv support => enabled
sqlsrv.ClientBufferMaxKBSize => 10240 => 10240
sqlsrv.LogSeverity => 0 => 0
sqlsrv.LogSubsystems => 0 => 0
sqlsrv.WarningsReturnAsErrors => On => On

And, finally, for using it, following the the sample scripts from the repository worked a charm.