I needed to do this in order to make a readonly user to consume TimescaleDB from Grafana. In Grafana users can write arbitrary SQL directly to the DB so for safety reasons a readonly user makes sense.

I realized that adding a read-only user forced a bit more understanding than what’s necessary to use PostgreSQL from an application or conduct performance analysis, the typical developer tasks, so I would not be surprised if many devs, like me, haven’t bothered, so I really recommend doing it.

Rather than post my own notes, I prefer the comments on this gist from Tomek, which I will archive the main gist here now and link to the GitHub hosted gist down below which is worth a look for the excellent follow-up discussions.

1
2
3
4
5
6
7
8
9
10
11
12
13

-- Create a group
CREATE ROLE readaccess;

-- Grant access to existing tables
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;

-- Grant access to future tables
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readaccess;

-- Create a final user with password
CREATE USER tomek WITH PASSWORD 'secret';
GRANT readaccess TO tomek;

Link to the original gist and discussion

By understanding this I was able to upgrade my mental model about PostgreSQL to the following: the world must be composed of users and groups called roles which have certain privilege to databases, schemas, and tables (also known as relations). Databases contain schemas and schemas contain tables.

I was relatively ignorant about the role of schemas before, but here we can see it is the mechanism by which future table privileges can be indicated. Do you know other reasons for which it may be valuable to know about schemas and their nuances?

This is part of a series about PostgreSQL & TimescaleDB – in the next one I will show how to deal with a TimescaleDB/PostgreSQL instance that is running out of disk space. We’ll do this by adding retention policies to certain relations after doing a bit of analysis.

How to get Mikrotik RouterOS on Unraid

Follow the steps from your Unraid terminal. Have the webgui handy as well.

1. download the image

1

wget https://download.mikrotik.com/routeros/6.48.1/chr-6.48.1.vmdk

2. convert it to qcow2 image to be stored in your domain cache:

1
2

mkdir /mnt/cache/domains/routeros
qemu-img convert -f vmdk -O qcow2 chr-6.48.1.vmdk /mnt/cache/domains/routeros/chr-6.48.1.qcow2

3. create the VM

Go to VMS, click Add, choose Linux
Switch to Form View if you are in XML view.
Set Name to RouterOS
Set BIOS as SeaBIOS (Very important!!!)
Set Disk to Manual with Format qcow2 and Path to the qcow2 file and bus SATA
Set Network to the Bridge you want to use and/or pass in any PCI devices

Uncheck “Start VM after creation” and click Create. If you forgot to uncheck it just Stop the VM

4. edit the VM (click the logo of your VM for edit dropdown button to appear)

5. switch to XML view and locate the network card. looks something like this:

1
2
3
4
5
6

<interface type='bridge'>
  <mac address='52:54:00:1c:b9:4a'/>
  <source bridge='br1'/>
  <model type='virtio'/>
  <address type='pci' domain='0x0000' bus='0x01' slot='0x00' function='0x0'/>
</interface>

Change the model type from virtio to vmxnet3

This is required in order for routeros to see the interface and be able to interact with it

Click Update after you’re done editing.

6. Start the VM and connect with the built-in VNC viewer.

7. Login as admin (no password) and configure your new router.

you can check what IP address it was given with the following mikrotik command:

1

ip address print

You can also connect using winbox available here https://mikrotik.com/download and discover it on the Neighbors tab

bonus step: add the winbox icon so you can set it in the VM manager!

````
curl -o /usr/local/emhttp/plugins/dynamix.vm.manager/templates/images/winbox.png dfh.name/images/winbox-mikrotik-icon.png

1
2
3
4
5

You may also find it's a good time to apply some basic firewall rules... depending on what you're planning on doing. 

The example below makes sense for my purposes and may be helpful to examine:

/ip firewall filter
add action=accept chain=input comment=”Accept new input on tcp/8291 from computer running winbox” connection-state=new dst-port=8291 protocol=tcp src-address=10.27.0.198
add action=accept chain=input comment=”Accept new input from gateway” connection-state=new src-address=10.14.52.1
add action=accept chain=input comment=”Accept established or related input” connection-state=established,related
add action=drop chain=input comment=”Drop all input on ether1” in-interface=ether1 log=yes
```

On Linux these days it is very easy to serve any window through a browser.

You’ll need to install websockify and novnc for this technique. This post continues from the steps described here:

https://www.server-world.info/en/note?os=Ubuntu_18.04&p=desktop&f=7

Now we will launch the two relevant programs together. A quick program I remembered that can do this is a node package called concurrently.

I use i3 as my window manager, and would like to create a program called wincat that I can launch with dmenu to automate the technique.

So here’s /usr/local/bin/wincat

1
2

#!/bin/bash
i3-sensible-terminal -e _wincat

And here’s /usr/local/bin/_wincat

1
2

#!/bin/bash
/home/dfh/.asdf/shims/npx concurrently "x11vnc -id pick -forever" "websockify --web=/usr/share/novnc/ --cert=/etc/ssl/novnc.pem 6080 localhost:5900"

Now I can run wincat, click the window I want to serve, and access the server at https://hostname:6080 – when I’m done I just send interrupt signal to the terminal to kill the process tree.

I wish I could run ncdu in Unraid but it does not ship there.

Docker does though, can’t I use that?

1
2

$ docker run ncdu
Unable to find image 'ncdu:latest' locally

Hmm… nope. Well I know how to get ncdu, can I teach docker how?

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

# Need a place within which to write a Dockerfile...
mkdir -p /tmp/dish/taps/ncdu

# And here's that, it just needs to install a usable ncdu
cat <<EOF > /tmp/dish/taps/ncdu/Dockerfile
from ubuntu
env DEBIAN_FRONTEND=noninteractive
run apt-get update && apt-get install -y ncdu
run mkdir -p /mnt
workdir /mnt
EOF

# Build it
docker build -t ncdu /tmp/dish/taps/ncdu

# I need a place to put my shim bin
mkdir -p /tmp/dish/bin

# Here's the shim bin, it just needs to mount the volume and use ncdu
cat <<EOF > /tmp/dish/bin/ncdu
#!/bin/bash
docker run --rm -v \$(realpath \$@):/dir -it ncdu ncdu /dir
EOF
chmod a+x /tmp/dish/bin/ncdu

# Make the shim accessible
export PATH="/tmp/dish/bin:$PATH"

Now I can run ncdu in Unraid.

Looks like a neat pattern that comes down to 2 things. Installation and Usage.

We are using docker as a given while populating “taps” (Guess I am stealing a concept from Homebrew) in which we define how to acquire/install a particular tool followed by usage deployed as a binstub or shim or what have you.

If I notice this happening a lot maybe I’ll do something about it like make a public registry of these things for different architectures and load it behind a simple curl installer or a plugin to asdf or something like that.

Thank you for reading. I hope you enjoyed it and learned something useful.

The other day, as I opened grafana to check how much electricity my heater was using, I noticed I had been missing data for hours.

I attached to the logs of the container using Unraid’s log view feature but nothing was obvious. No new output. It was just another Docker lockup. Keeping the code very simple apparently was not enough to prevent random lockups.

I had two questions, followed by two actionable answers:

1. Does the container freeze often enough to warrant attention?

To answer this I needed to setup alerting. I used a private Discord server for this. Discord is wonderful – one particular feature is the ease with which one can generate webhooks.

I configured Grafana to alert on missing meter data. First I had to create an alert channel for discord, and then configure a panel with the appropriate query and alert conditions.

I discovered that yes, it happens frequently and randomly … once every couple of days to sometimes multiple times in one day.

2. Can I use the alert mechanism to perform the mundane act of restarting the container for me?

To answer this I imagined another container that accepts webhook to issue the appropriate docker restart ... command. Yes it appears possible with Grafana’s alert channel design decision, so to whip up a nodejs & express:

1
2
3
4
5
6
7
8
9
10

const app = require('express')();

app.post('/restart-studio-electricity-meter', function (req, res) {
    require('child_process').execSync("docker restart studio-electricity-meter"); 
    res.send('ok');
});

app.listen(1337, () =>{
    console.log('listening on 1337')
});

And create the appropriate channel on the Grafana side…

This works great and I no longer lose data as my container is automatically restarted, and I am notified in Discord too.

Grafana alerting seems to be so good that I can probably play with the timings in order not to be alerted at all, if it should start to annoy me. But at this point I need to remain aware and so I could not be happier with the current situation!

A few more thoughts on Docker, perfectionism, amelioration of disdain for bandaid solutions, and getting over mental paralysis

Now, if you’re like me (a bit of a perfectionist) you may be wondering why I am so jovial about a band-aid solution. The answer is I’ve used docker a lot (over 5 years now) and for all you docker honeymooners out there, know that frozen containers continue to be a problem with little understanding by the Docker team. That’s just one example of a stale ticket. When I was dealing with this for a Rails client just a couple of months ago, I found many other examples of stale tickets. The best reference I had found from the Gusto blog.

It’s a bad situation that few seem to discuss, but I’m pretty sure everyone seriously using Docker knows it exists and actively avoids it by being hygenic about their Docker deployments. The Gusto story (in short, they had “too much output” on STDOUT for Docker. Yes, really, that was “the problem” – is your faith in Docker shaken yet?) for my electricity meter it makes no sense (it only prints once every ~1200 milliseconds) and just further reduces Docker’s credit in my eyes (I still love it though).

I assumed that the issue is within Docker itself: out of my hands. The nodejs code inside the container which is powering the electricity meter middleware could not possibly be simpler or more straightforward. All it does is grab info from the USB Serial device (maybe Docker has a problem holding onto the device, the –device flag could be a less supported code path with contention issues from the OS), do a bit of arithmetic, and then invoke curl to send the data to an endpoint.

Yes, all that said, it remains true that this is still a band-aid. It is important to remain aware that sometimes band-aids are OK and that being staunch perfectionist especially in the world of hacking (the MIT definition, by the way) and security (there’s your mass-media definition for hacking, if you must), being overly perfectionist is an effective way to paralyze the self. I’m not interested in that kind of thinking. It does not serve anyone. It’s worth discussing why paralysis occurs, however. I believe it results from knowledge of limited resources versus the suspicion that the goal is positioned far beyond their point of depletion. I still struggle with this “fact” but taking it easy and just keep hacking has been effective advice from friends. I put “fact” in quotations because you can very easily argue that both the amount of resources available to you and the effort (or path) required to achieve a goal are fluid. Therefore a conclusion towards inaction is fundamentally invalid.

As I considered the data flow I further appreciated the elgance of the band-aid solution and grew to appreciate it and things like it. If I think of a chatroom as a message bus where machines (and humans) talk to each other then it is not unreasonable to think that machine A might inform machine B that machine C is misbehaving and needs a kick in the ass. In the case of my band-aid, however, it’s simpler than that, as grafana’s beautiful design for alert configuration lends itself to directly informing the ass-kicker of the story (source code link below). In other words I did not need to write code to catch outputs from Discord and parse them and take action on them. All I had to do was create an “alert channel” that hit my custom web service, disable the “alert has ended” messaging (so it only hits the webhook once, when the alert starts, not again once the alert is over) and add that alongside my Discord alerts.

The result is that I get to see this nice “bot conversation” in discord, letting me know that, sure, things are breaking down, but they are also getting fixed promptly, and sometimes that’s the best we can do in an imperfect world and that’s okay.

Resources

Source code repository on GitHub for the rebooter

Background Information

Back when I was into the GPU cryptocurrency mining scene (I fell deeply into it during the end of 2017) it became important to measure energy consumption. To do this, I rigged a couple of Hall-effect current sensors to an Arduino and fed the data through a BeagleBone Black that kicked the data up to an influxdb instance on an ESXi on a Mac Mini… Needless to say this was convoluted and I’d since ditched all these complexities (along with GPU mining itself) and later began using Unraid which I’ve blogged about a few articles back. I highly recommend it for its ability to bring the absolute Zoo of home serving under control with a solid persistence layer.

Project Story

I purchased these sensors on eBay for ~$9 each. I’m told they are not the best for AC current measurement but they work pretty well and are fairly accurate if you’re willing to tolerate up to an amp of error (I know that’s a lot but it’s the first draft of the hardware in this case).

Equipped with two of these sensors, I rigged the two phases of my sub-panel by slipping each phase wire into its own sensor, and then wired the sensor’s pins to the Arduino Uno. I believe it was a simple analog value that I had to make it read. I had to write some code to convert the analog reading to amperes and then write those values out together on one line for every “tick” which turned out to be just over one second. It looks like this to anyone reading the serial port:

1
2
3

0 0
1 2
2 4

I describe it like that because it was a bit of an archeological experience reviving this piece of hardware from almost three years ago. I don’t know about you, but I have so many projects behind me now that I am often finding myself reverse engineering my own creations. If the interfaces are not simple this is very painful. Luckily in this case it is a very simple and logical interface.

In the above example there was no usage (0 amps on each phase), then a little usage (1 amp on the first phase, 2 amps on the second phase), then a bit more… you get the idea.

I have lost this code and recall feeling bad about its quality (due to the wrong sensor selection for the use case, leading to poor precision and bad smells leaking into the code I had to write, presumably why I have “lost” it and never wrote about it at the time), so I look forward to rewriting this part of the system later without being in a rush and placing more attention on part selection for more accuracy and precision.

Downstream of the Arduino I had originally implemented a design that I was not proud of: Instead of computing the cost accrual for each interval, I was pushing the raw data into the time-series database and then retrospectively querying and computing costs! Although this sharpened my teeth on using influxdb from a consumption point of view, this was a bad design resulting in an uncomfortable workflow requiring me to modify dates in source code that I’d execute in order to generate a report. Wouldn’t it be nice to utilize Grafana’s date range query powers? At the time I believe I was using Chronograf (InfluxData’s grafana competitor) but the crypto project really got me well-versed in Grafana, so I knew this time around how I wanted things to work.

This time around we are executing the appropriate math upfront so that the relevant information (the “cost per tick”) is sent to the time-series database. By doing this math upfront we leverage grafana’s ability to total a series of accruals and can generate the financial report for any time interval right from the browser! In the following screenshot you can see this demonstrated in the panel entitled “Total Cost” below. Notice this is for the “last 12 hours” time range in grafana. In this way, at the end of the month, I can ask grafana how much currency accrued during that month. Bear in mind this is all in spite of the fact that we have a variable time-of-use rate policy here where I live. This complexity is taken care of in the software that processes the data from the arduino prior to shipping it off to the time-series database.

The deployment can be described tersely as follows:

1. Arduino writes over Serial port through internal USB-Serial device to my Unraid tower.
2. Unraid tower runs a docker container that uses this device (–device=/dev/ttyUSB0) to read values.
3. The docker container contains extra information about our provider’s rate schedule and computes cost per tick.
4. Finally, the values are pushed to InfluxDB for viewing by Grafana. These are also docker (Community Apps) on Unraid.

Project Improvements

• Hall-effect ammeters are good for measuring DC current, not AC current. For this project they work okay but are very low resolution. There is a major loss due to the diminished faith in the data resulting from the low resolution. Replacing these sensors is a good future task.
• Serial access is great but wireless access is more interesting. Consider applying BLE knowledge here, although powering the device becomes a problem too.
• Explore data for the purpose of expansion. For example let’s say we want to load-shift more expensive time to cheaper time by installing batteries that we drain during the day and reload during the night. We would need to do some capacity planning. Now we are equipped with sufficient behavioral information in order to perform such analysis and planning which would directly inform the design and component selection decisions.

Article Improvements

• The arduino code is simple, but it is missing. When replacing the sensors be sure to include that code as well.

Resources

1. Source code repository can be accessed through GitHub here

GlobalChat Draw is a new open-source end-to-end encrypted chat system with drawing capabilities built using Apple’s latest graphics APIs. A mobile client is also in development. Before you complain about the price of the Mac app, remind yourself that Apple charges developers $100/year just to have the privilege of publication. Furthermore be aware that the clients (both the mobile and Mac apps) and server are all provided open-source from the author’s github page.

The technology used to create this is very interesting, especially if you’re learning, but today we’ll focus on hosting a server. Perhaps in another post we can delve into the source code for each component and understand how it works.

Server Deployment

Now let’s deploy a GlobalChat server. We’ll use the instructions from the official gdraw.chat website:

Anyone can host a GlobalChat server. The current recommended way is to use Ubuntu snaps with Linux.

snap install --beta crystal-gchat-server

crystal-gchat-server.change-passwords -i

crystal-gchat-server

Here is a systemd unit we can use to automatically launch it.

1
2
3
4
5
6
7
8
9
10
11
12
13

[Unit]
Description=GlobalChat Draw Server
After=network.target
StartLimitIntervalSec=0
[Service]
Type=simple
Restart=always
RestartSec=1
User=root
ExecStart=/snap/bin/crystal-gchat-server

[Install]
WantedBy=multi-user.target

Install that in /etc/systemd/system/gchat.service

Enable it so that it runs automatically on startup

systemctl enable gchat

Start it now

systemctl start gchat

And make sure it’s running

systemctl status gchat

To access the logs

journalctl -fxu gchat

Now there is a new server publicly visible within the application! This works because there is a master server list (also open source) to which the server software registers itself to. Thanks to Apple’s strict policies, there are moderation tools built-in as well – notice that you can set your admin password during server setup.

Discovered an excellent wine in docker solution today and so I thought it would be fun to use it to run winbox on my linux laptop. This was very easy to do, with great results, so I am writing it here for future use.

You will need:

• A working docker installation in Linux
• A NIC for docker to bridge for use by winbox

1. Create directories for wine data and templates
   mkdir -p ~/docker-data/{wine-data,wine-templates}
2. Write the winbox wine template script:

   1 2 3 4 5 6 7 8 9 10 11 12 13 14

   cat <<EOF > ~/docker-data/wine-templates/winbox.template #!/bin/bash export WINEPREFIX=/home/docker/wine/winbox export WINEARCH='win32' if [ ! -f /home/docker/wine/winbox/drive_c/winbox/winbox.exe ] then winetricks liberation corefonts mkdir -p /home/docker/wine/winbox/drive_c/winbox cd /home/docker/wine/winbox/drive_c/winbox curl -SLo winbox.exe https://mt.lv/winbox fi wine /home/docker/wine/winbox/drive_c/winbox/winbox.exe exit EOF

3. Write the docker run script. Be sure to update the parent NIC specified with yours.

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

   cat <<EOF > ~/docker-winbox.sh xhost +si:localuser:$(whoami) >/dev/null docker network create -d macvlan -o parent=enp7s0 winbox_net docker run \ --rm \ -ti \ --network=winbox_net \ -e DISPLAY \ -v /tmp/.X11-unix:/tmp/.X11-unix:ro \ -v ~/docker-data/wine-templates:/mnt \ -v ~/docker-data/wine-data:/home/docker/wine/ \ -u docker \ yantis/wine sh /mnt/winbox.template docker network remove winbox_net EOF chmod +x ~/docker-winbox.sh

4. Execute it as needed, but first read the note below
   ~/docker-winbox.sh

The first execution will try to download assets from the web such as fonts for wine and the executable to winbox itself. These get cached and internet access is not needed for subsequent invocations. Running a custom network is liable to break the internet within the container, so execute the following in order to get everything cached, and then flip back to the script for future invocations.

1
2
3
4
5
6
7
8
9
10

xhost +si:localuser:$(whoami) >/dev/null
docker run \
  --rm \
  -ti \
  -e DISPLAY \
  -v /tmp/.X11-unix:/tmp/.X11-unix:ro \
  -v ~/docker-data/wine-templates:/mnt \
  -v ~/docker-data/wine-data:/home/docker/wine/ \
  -u docker \
  yantis/wine sh /mnt/winbox.template

Reference

https://docs.docker.com/network/macvlan/
https://hicu.be/macvlan-vs-ipvlan

Another blog post while I wait for Array to be created.

How does Unraid being non-free square against the circle of retaining data sovereignty?

You should retain access to your own stuff without having to ask permission by some third party who may or may not even be reachable let alone necessarily agreeable in the future.

How does this work? How can I be OK with this?

My guess is that at the end of the day Unraid can be thought of as merely a very advanced WebGUI management layer between you and the free XFS file system, on top of the free Linux kernel.

Therefore by purchasing Unraid I am not giving up too much freedom, rather I am only deferring learning and building what would be an ideal WebGUI (Unraid) for managing the underlying complexity. I am paying for the hard work by the Unraid developers and to support the community.

This system helps users to enjoy a more convenient usage of the underlying system which is too complex for mere mortal like me. There is no ethical issue here about data sovereignty because XFS is free. If Unraid team stops supporting Unraid, I can access the regular linux shell and recover my data.

By supporting Unraid, the negative outcome I’m already prepared for becomes less likely.

This is OK, but what happens in the future as I grow reliant on that UI in a disaster scenario where I can’t receive an update – does it stay alive?

A couple of relevant communication in the Unraid Wiki FAQ sort of answer.

How hard is unRAID to use if I don’t know Linux?
Not hard at all. Although the unRAID server software is based on a slimmed-down Linux, it is managed almost entirely from your web browser, typically on a Windows or Mac computer. Some users are happy with that, but many want to take advantage of the many tweaks and addons for unRAID, and these usually require a little hands-on work. But they are completely optional, and are generally accompanied with lots of help and instructions, and there is a very helpful user community. Many users find this makes for a good introduction to Linux, at their own pace. See also this thread, especially this post, for more comments.

Does unRAID need Internet access?
The unRAID server software generally does not require Internet access. Of course, you will need Internet access from another desktop to download the software and software updates, and to read the unRAID forums and this Wiki!
However, there are many benefits to providing Internet access to your unRAID server. The unRAID software and your plugins and Docker containers can all be updated from within the software. Usability and manageability are improved with email and other notifications. unRAID supports NTP, the Internet time service, so if you enable the NTP service, your server will keep accurate time. In addition, expert unRAID users have created many addons for unRAID, such as plugins, Dockers, and VM’s, that can provide numerous application services such as torrent support, etc. All of these benefits are completely optional. See also this.

Can Unraid replicate?

I didn’t see any official support for the concept of pairing two servers together, no. Not yet.

Unraid really helps with learning about Disk Health

Having a UI for easily inspecting disk health, performing tests, and downloading SMART reports and a community from which to learn about these topics is wonderful.

Paranoid about Flash Drive going bad?

Better take a backup of it soon as described in the wiki https://wiki.unraid.net/UnRAID_6/Changing_The_Flash_Device

More reading here on that subject: https://unraid.net/blog/unraid-new-users-blog-series

Paranoid about disks themselves?

Read about that here https://www.reddit.com/r/unRAID/comments/dg9x7r/unraid_drive_reliability_calculator/

Let me start with a bit of meta-blogging since it has been awhile since I’ve edited my website…

This is really just a test post to get my feet wet with editing my site again after all this time.

It seems that I don’t write here anymore, like I used to.

When creating this blog site my goal was to keep it purely technical and apolitical.

This has been mostly a success, but as our political climate grows more tumultuous, this site grows silent. Why?

This is due in part to the realization that even the technical work is political, and growing fear that kept me silent (apolitical) in my writing goals from the outset.

I’ll focus only on the former briefly, and segue to the main thrust of this post.

For example, when it comes to choosing where to store my (~ 10 TB) data – do I put it on Google? DropBox?

What causes do they support? Do I support them? Do I trust them? Even if I did, do I trust their provider to keep them online? To secure my data? What about my own ISP?

Of course if you really follow the plot you realize you’ve got your work cut out for you.

Instead, I’ll self-host and then that’s fodder for a blog post to help others.

I’m currently investigating this problem and am excited to buy into Unraid.

The biggest selling point of Unraid (and there are many) is the community application system.

This system is what I dreamed for around 2015 when I learned Docker.

But let’s be clear that the Docker Hub is for developers; using it or pushing it onto home users is a corruption of its intended purpose.

This is why you have such a rich ecosystem around Docker (and by rich I mean complicated and hard to understand, case in point: k8s).

I think the community application system within Unraid understands and addresses this gap, and so I’ll begin documenting this process here as it unfolds.