Category Archives: MySQL

Migrating FreshRSS from Ubuntu to Alpine Linux

Intro

For some time I’ve had all my home lab systems running on LXD. For me at least, LXD predated Docker and so I’ve stuck with it. The containers are a bit more pet like and less cattle like – but that’s OK. They’re there for me to learn with – plus I can totes do docker in LXD inception if I wanna! (I’ll figure my way over to Incus some other month)

So years and years ago I found out bout FreshRSS as a great way to self host an RSS reader. This meant I didn’t have to install any apps on my phone, my feeds could stay synced and since I already had a VPN setup, it was trivial to access while on the road. Lovin’ it! I’d set this up around late 2018.

Fast forward to yesterday, I flippantly upgraded FresshRSS to the latest, as I occasionally do, and the site started having a fatal error, as it often does after I upgrade ;) I pulled up the error logs on the FressRSS LXD container running Apache and MariaDB and immediately saw some sort of unexpected { character in function blah blah on line 1-oh-smang-thirty error. Huh, what’s that about?

Turns out in the latest dev release of FreshRSS, they’d formally removed support for PHP 7.x and required PHP >8.0. Specifically, this was because they’re using Union Types in their function declarations. This is cool stuff! you can see the int|string values in the sample function here:

// Declaring a function with Union Type
function getMixedValue(int|string $value): int|string {
   return $value;
}

This is no problem! I’ll just update PH….P…. oh yeah – I’m on hella old Ubuntu 18…so then I’ll just find some third party apt repo and add that and then…. Hrrmm…might be more trouble than it’s worth. That’s cool! I’ll just deploy a new Ubuntu container on 24.04. Oh, well, that’s gonna take a chunk of disk space – I’ve been using a bit of Alpine to build small Docker images at work, what about a pet Alpine in LXD? They have an image – why not!

Preparing the leave the old system

Before we sudo rm -rf / on the old box (er, container), let’s get our data outa there. We need to first make a dump of the database. We’re root so we can just zing right through any permissions with a one liner. Next up we can zip up our old files into one big ol’ honkin zip file. That looks like this:

mysqldump freshrss > ~/freshrss.sql
cd /var/www/localhost/htdocs/
zip -r ~/freshrss.zip .

Finally, we can generate an SSH key for this root user to easily copy to the new container – I knowingly didn’t add a password because I’m about to delete the container and we’re all friends here:

ssh-keygen -t ed25519 
cat /root/.ssh/id_ed25519.pub

Ok – I’ll hold on to that pub key for the “One last trip to the old digs” section below.

Bless the new hotness

Here’s how to bloop out an Alpine container in LXD:

lxc launch images:alpine/3.20 freshrss
lxc shell freshrss

That’s it! You’re now sitting as root on the new instance. Let’s install the base packages including Apache, MariaDB, OpenSSH and PHP with all it’s libraries:

apk add \
   mariadb mariadb-client openssh \
   apache2 apache2-http2 php83 \
   php83-cli php83-apache2 php83-session php83-curl \
   php83-gmp php83-intl php83-mbstring php83-sqlite3 \
   php83-xml php83-zip php83-ctype php83-fileinfo \
   php83-dom php83-pdo

Now let’s ensure the three services start at boot and then we can start Apache and OpenSSH (MariaDB will have to wait):

rc-update add apache2
rc-update add sshd
rc-update add mariadb
rc-service apache2 start
rc-service sshd start

As well, that pub key you got from the old server? Let’s add that in on the new server:

mkdir ~/.ssh
echo "ssh-ed25519 AAAAC3Nz-SNIP-NplQ3 root@freshrss-old" > ~/.ssh/authorized_keys
chmod 700 ~/.ssh/
chmod 600 ~/.ssh/authorized_keys

One last trip to the old digs

As final hurrah at the old server, now that our SSH key is on the new server, let’s copy over the zip archive and the SQL dump. Be sure to replace 192.168.68.217 with your real IP!

scp ~/freshrss.* 192.168.68.217:

Go SQL!

Now that we have our server with all the software installed and all the data copied over, we just need to pull together all the correct configs. First, let’s run setup for MariaDB and then harden it. Note that it’s called mysql… , but that’s just for backwards compatibility:

rc-service mariadb setup
rc-service mariadb start
mysql_secure_installation

That last command will ask questions – default answers are all good! And the initial password is empty, so you can just hit return when prompted for the current password. Maybe check out passphraseme if you need a password generation tool? Let’s add the database, user and perms now. Be sure to not use password as your password though!

echo "CREATE USER 'freshrss'@'localhost' IDENTIFIED BY 'password';" | mysql
echo "GRANT ALL PRIVILEGES ON *.* TO 'freshrss'@'localhost' WITH GRANT OPTIO" | mysql
echo "GRANT ALL PRIVILEGES ON *.* TO 'freshrss'@'localhost' WITH GRANT OPTION;" | mysql

Now we can load up the SQL and move all the PHP files to their correct home. Again, we’re root so no SQL password, and again, this is actually MariaDB, not MySQL:

mysql  freshrss < freshrss.sql 
unzip freshrss.zip 
mv * /var/www/localhost/htdocs/.
mv .* /var/www/localhost/htdocs/.

Go Apache!

Apache just needs four updates – easy!

Edit /etc/apache2/httpd.conf with your favorite editor. Find these three lines and uncomment them – they won’t be next to each other:

#LoadModule rewrite_module modules/mod_rewrite.so
#LoadModule session_module modules/mod_session.so
#LoadModule remoteip_module modules/mod_remoteip.so

Now find the one line where DocumentRoot is set and change it to this value and add two more lines. One to allow encoded slashes and one to set the server name. Be sure to use the IP address or FQDN of the server – don’t use rss.plip.com!

DocumentRoot "/var/www/localhost/htdocs/p"
AllowEncodedSlashes On
ServerName rss.plip.com:80

Now that apache has been configured, let’s restart it so all the settings are loaded:

rc-service apache2 restart

Conclusion

The old FreshRSS install should now be running on your new Alpine based container – congrats! This has been a fun adventure to appreciate how Alpine works as compared to Ubuntu. This really came down to two main differences:

systemd vs OpenRC – Ubuntu has used systemd for sometime now and the primary interface to that is systemctl. Alpine on the other hand uses OpenRC which you interface with rc-update and rc-service. Alpine picked this up from when it split off from Gentoo.
apt vs apk – Package management is slightly different! I found this to be an inconsequential change.

There’s plenty of guides out there that do the same as this one. Heck, you’re likely better of just using a pre-built docker image (though the top results were pinned to PHP7)! However, I wanted to document this for myself and hopefully I’ll save someone a bunch of little trips off to this wiki or that FAQ to understand how to migrate off of Ubuntu to Alpine.

Cheers!

Legba the Net-tracker

1 Reply

4 minutes, 15 seconds

Intro

I’d been meaning to learn how to write an app using something more than CSV files, but less than MariaDB, to store files – I’m thinking SQLite of course! Then along came the desire to have a simple way to track when a computer was on a network as a proxy for kids’ daily screen time. After all, the network is the computer, right?

While there’s so very many ways to solve detecting if a computer is online (more on this later), I thought it’d be fun to write a simple app that could correlate multiple IPs to a single person, and then give a histogram of minutes per day per person. Given this is just a proxy for screen time, it’s fine if it doesn’t have alerting, password protection or even a way to prevent going over the allotted time per day. The goal will be for any interested parties to see how long a device has been on for the current day. It’s then up to the family to have a discussion about what it means to go over your daily allotment.

Ok, let’s do this! We have a requirement to track computers being online and to write and read the results to a SQLite DB. I’ve been groovin’ on learning Python, so let’s double down and use that. I did some Wikipedia exploring and read about Papa Legba, and thought it made a mighty fine sounding name. Finally, after some nudging from a friend, we’ll package it up in Docker so it’s easy to try out and host in an isolated container.

Ping FTW

The first step to using Legba, is to define a list of users and which IPs they’ll be on. Very likely the best way to do this is to either use static IPs on your LAN clients, or have your DHCP server set the same IPs per MAC every time.

Then you’ll create a conf.py file copied from the conf.example.py file and fill it out. Here we see Jon and Habib have one IP each, where as Mohamed has 2:

trackme = {
    'Jon': ["192.168.1.82"],
    'Habib': ["192.168.1.12"],
    'Mohamed, ': ["192.168.1.240", "192.168.1.17"]
}

The code to track if a device is achieved via the subprocess module via a ping() function with just two lines that send a single ICMP packet:

# thanks https://stackoverflow.com/a/10402323
def ping(host):
    """ Ping a host on the network. Returns boolean """
    command = ["ping", "-c", "1", "-w1", host]
    return subprocess.run(args=command, stdout=subprocess.DEVNULL, stderr=subprocess.DEVNULL).returncode == 0

Back in the main() function, we then read in config, loop over each person and try and ping() each of their IPs. If we see them online, we write to the DB via record(). It ended up, just as I’d hoped, that Python’s SQLite libraries are robust and it’s just 6 lines to insert a row:

sql = ''' INSERT INTO status(name,state,date) VALUES(?,?,?) '''
cur = sqlite.cursor()
activity = (name, state, datetime.now())
cur.execute(sql, activity)
sqlite.commit()

return cur.lastrowid

Just before the end of the loop we call probably the most complex function of the lot output_stats_html(). This function is responsible for reading the day’s active users, getting each users activity by hour, the total for the day and finally output static HTML as well as a static JSON file that will get called via AJAX so the stats will auto-refresh.

At the end of the loop we sleep for 60 seconds. In theory if you had hundreds (thousands?!) of IPs to track and they were on connections with >500ms latency, it would take way longer than 60 seconds. Legba will not scale to this level. It’s currently been comfortably tested with 5-10 devices on a LAN where each device has ~20ms of latency.

A histogram is worth a 1000 words

After you’ve done a bit of a git clone with a lil pip3 install and fleshed out your own config.py and done a little systemd love, you’ll have some sweet sweet histograms! (Some keen eyed readers may note this histogram looks familiar ;)

It’s interesting to note that mobile devices, as seen withe “Adnon Cell”, are effectively on all the time. In this sense, Legba is not much use to track a cell phone. Meanwhile, Bobby Table’s desktop, Adnon’s Laptop and Chang’s Nintendo Switch all work as expected (NB – I didn’t actually test with a Switch).

Existing Solutions

I’ve been running this for solution for just about 4 months now. It’s been a great way for our family to have an open discussion about what it means to spend too much time on the computer and it’s been rock solid. Checking ls and select count(*) from status; I see my DB is 23MB and has 487,069 rows.

Given the simplicity of this app, could this DB and rows be easily stored and retrieved elsewhere? When I wrote the app, I didn’t care – I just wanted to write it for the fun of writing it! However, I was listening to episode 171 of Late Night Linux and they mentioned how utilitarian Telegraf is. It struck me that, indeed, if you had a Telegraf, InfluxDB and Grafana (aka “the TIG stack”) already set up, it would be pretty trivial to capture these same stats. I would do this by setting up a centralized instance of Telegraf and either use the built in Ping plugin, or possibly the more extensible [[inputs.exec]] input type. With the latter, you could even re-use parts of Legba to pretty trivially input the data to InfluxDB. Then, it would be equally trivial, to slice up the ping counts per hour, per user and have a slick dashboard. Just food for thought!

Otherwise, I hope some else than me gives Legba a try!

Howto: Sympa 6.1 on Ubuntu 16.04

1 Reply

3 minutes, 6 seconds

Recently I was tasked at work to get an instance of Sympa set up. Their docs are a bit scattered, but I found a promising post on debian.org which suggested I could get away with an apt-get install instead of needing to compile from source. Well, it turns out I did get it working, but only after a lot of trial and error. Given that some one else might be trying to do this, and because I had to document the exact steps for work, here’s a handy dandy blog post which I hope will help some one trying to do the same thing.

Good news for those looking to do this for Sympa 6.2 (latest at time of publishing), I have a post on how to do this exact thing on the soon to be released Ubuntu 17.04 with Sympa 6.2. Stay tuned!

Assumptions/Prerequisites

This post assumes you have root on your box. It assumes you have Apache2 installed. It assumes you’re running a stock Ubuntu 16.04 install. It assumes you want to run Sympa on your server. It also assumes you’ll be using Postfix as the lists MTA. It assumes you have a DNS entry (A record) for the server. As well it assumes you also have an MX record pointing to the A record or no MX record so the MX defaults to the A record. If this doesn’t apply to you, caveat emptor!

To recap, that’s:

Apache 2 installed and working
Postfix as MTA
Ubuntu 16.04 server
Existing DNS entry
Run all commands as root

I also was using this server solely to serve Sympa mail and web traffic so if you have a multi-tenant/multi-use server, it may be more complicated.

Steps

These steps assume you’re going to install Sympa on list.example.com. There’s no reason you couldn’t use example.com instead.

Install sympa:
```
apt-get install -y sympa
```
When prompted during this install:
1. Choose a good mysql root password and enter it when prompted
2. Please select the mail server configuration type that best meets your needs: Internet Site
3. System mail name: list.example.com
4. Which Web Server(s) are you running?: apache 2
5. Database type to be used by sympa: mysql
6. MySQL application password for sympa: <blank> (will assign random one)
Sympa 6.1 has a present! It ships with a bug. Fix the regex on line 126 of /usr/share/sympa/lib/SympaSession.pm so it looks like this (note “{” is now “\{” ):
```
if ($cookie and $cookie =~ /^\d\{,16}$/) {
```
Edit /etc/sympa/wwsympa.conf to change line 81 to 1 instead of 0:
```
 use_fast_cgi 1
```
If you don’t do this step, you’ll see full HTML pages show up in /var/logs/syslog and only 500 errors in the browser :(

lists.example.com should show the sympa UI, w00t!

Ensure Sympa starts at boot:

update-rc.d sympa defaults
update-rc.d sympa enable

ensure postfix is updated in /etc/postfix/main.cf edit these values to match:

myhostname = lists.example.com
smtpd_tls_cert_file=/full/path/to/cert/apache/uses.pem
smtpd_tls_key_file=/full/path/to/key/apache/uses.pem
alias_maps = hash:/etc/aliases,hash:/etc/mail/sympa/aliases
alias_database = hash:/etc/aliases,hash:/etc/mail/sympa/aliases
mydestination = $myhostname, lists.example.com, , localhost
relay_domains = $mydestination, lists.example.com

Update /etc/sympa/sympa.conf so that these values match:

listmaster email1@example.com,other_here@domain.com
domain lists.example.com
wwsympa_url https://lists.example.com/wws

update /etc/sympa/wwsympa.conf so that these values match:
```
default_home lists
create_list intranet
```
The “intranet” value will prevent some one from signing up and requesting a list with any approval.

add default aliases for sympa at the top of /etc/mail/sympa/aliases:

## main sympa aliases
sympa: "| /usr/lib/sympa/bin/queue sympa@lists.example.com"
listmaster: "| /usr/lib/sympa/bin/queue sympa@lists.example.com"
bounce+*: "| /usr/lib/sympa/bin/bouncequeue sympa@lists.example.com"
sympa-request: email1@example.com
sympa-owner: email1@example.com

reboot and rebuild aliases:
```
newaliases
reboot
```

Sympa should now be up and running at lists.example.com! All mail and and out should work so you can run your own list server. Please report any problems so I can keep this post updated and accurate – thanks!

tableMaker: Open Source PHP MySQL CRUD GUI library (Updated!)

plip blog