Rails 7 and Turbo Streams Subscribed Only to Focused Windows

When using streams to broadcast notifications it happens what it should: notification are broadcasted! Which means all open window that subscribed to the notification channel will get the message. This is most of the time the desired behavior, but not always.

In our case, streams are used to notify the user of her last action status. Something like “Yeah, it worked! Well done” or “OMG! It failed, try differently“. It doesn’t make sense to publish this message to all open windows, just the one focused will suffice plenty, because it’s a direct notification. It might even make sense for all kind of user’s notification, even to notify the status of the last background task.

Broadcasting still make sense when targeted to update the page content for other purpose than notifications, so I wrote a little piece of code to toggle the stream connection when the user “blurs” or “focuses” windows. Something that it’s probably worth sharing.

class Focus {

  static boot() {
    this.onFocusStreams()
  }

  /** Enable streams with the class 'on-focus' only when the focus is on the window */
  static onFocusStreams() {
    let isWindowFocused = true

    window.addEventListener('focus', () => {
      isWindowFocused = true
      document.querySelectorAll("turbo-cable-stream-source.on-focus").forEach((i) => {
        if (!Turbo.session.streamObserver.sources.has(i)) {
          i.connectedCallback()
        }
      })
    })

    window.addEventListener('blur', () => {
      isWindowFocused = false
      document.querySelectorAll("turbo-cable-stream-source.on-focus").forEach((i) => {
        i.disconnectedCallback()
      })
    })

    document.addEventListener('turbo:load', () => {
      if (!isWindowFocused) {
        document.querySelectorAll("turbo-cable-stream-source.on-focus").forEach((i) => {
          i.disconnectedCallback()
        })
      }
    })
  }
}

export { Focus }

Import Focus, run Focus.boot() once and let the magic happen.

Nginx-Ingress Reverse Proxy in Front of an Object Storage for inflexible WebGL and stubborn CORS

Rationale: in an Unity App generating WebGL with remote-assets loading where we have very little control on the generated code, I’m limited to one solution and it’s to comply with CORS: hosting assets in the same domain as the app is running in.
In this context it seems indeed impossible to set correctly the crossorigin attribute in the generate WebGL code. If you think otherwise please teach me in the comments.

TLDR; It works fine of course, but don’t waste your time deploying your own reverse proxy if you want to keep the benefit of a CDN and if performances is a key in your deployment.

This wouldn’t be an issue if the current provider, DigitalOcean, would allowing me to add custom hostname to its Object Storage offer (Spaces). It actually allows it but only for domain managed by DigitalOcean itself. Which is nonsense to anyone able to add a DNS entry (anyone with two fingers and a tong?) and no-go for us as our DNS is hosted by CloudFlare and acts as a good protection for the infrastructure.

CloudFlare is a reverse proxy (and more) and hide our origin servers from the wild wild web. I would have expect this provider to allow me to proxy our origin server to any FQDN. For instance front.org.tld to forward requests and return content from cdn.location.digitaloceanspaces.com. This is unfortunately not available for the free tier and it seems available to the business plan under the name CNAME Setup, but I don’t agree with the price for such a “simple” feature. We are already paying with our data and it seems that concurrent are offering this feature to free tiers.

I excluded the idea to “write” a reverse proxy in Javascript and use CloudFlare workers because… well it’s nonsense shitty tech. The net is not a trash and needs no more bullshit solutions.

Our services are running in a Kubernetes cluster so if I tolerate the performance trade off to run a reverse proxy in my own rented infrastructure, this solution is “free”, relatively clean and allow us to get forward with our project.

There is drawbacks. Many. For anticipated heads-up and warnings, see the end of this post.

With the bellow service and ingress settings, requests sent to OBJECT-STORAGE-RP.ORG-DOMAIN.TLD (any FQDN) will return content from OBJECT-STORAGE-HOSTNAME (Object Storage on DigitalOcean Spaces, Amazon S3, Google Cloud Storage, etc.).

---
apiVersion: v1
kind: Service
metadata:TODO
  namespace: APP-NAMESPACES
  name: OBJECT-STORAGE-RP
  labels:
    app: OBJECT-STORAGE-RP
spec:
  externalName: OBJECT-STORAGE-HOSTNAME
  type: ExternalName
  selector:
    app: OBJECT-STORAGE-RP

---
apiVersion: networking.k8s.io/v1
kind: Ingress
metadata:
  namespace: APP-NAMESPACES
  annotations:
    kubernetes.io/ingress.class: "nginx"
    cert-manager.io/cluster-issuer: ORG-SSL-ISSUER
    nginx.ingress.kubernetes.io/backend-protocol: https
    nginx.ingress.kubernetes.io/upstream-vhost: "OBJECT-STORAGE-HOSTNAME"
    nginx.ingress.kubernetes.io/server-snippet: |
      proxy_ssl_name OBJECT-STORAGE-HOSTNAME;
      proxy_ssl_server_name on;
  name: OBJECT-STORAGE-RP-ingress
  labels:
    app: OBJECT-STORAGE-RP

spec:
  tls:
  - hosts:
    - OBJECT-STORAGE-RP.ORG-DOMAIN.TLD
    secretName: OBJECT-STORAGE-RP-ORG-TLS
  rules:
  - host: OBJECT-STORAGE-RP.ORG-DOMAIN.TLD
    http:
      paths:
      - path: /
        pathType: Prefix
        backend:
          service:
            name: OBJECT-STORAGE-RP
            port:
              number: 443

Where variables are:

  • OBJECT-STORAGE-RP = a name to define this reverse proxy
  • OBJECT-STORAGE-HOSTNAME = source host, where assets are hosted (Spaces in my case)
  • ORG-SSL-ISSUER = when using SSL, might be letsencrypt (certbot) and this is cluster-dependent
  • APP-NAMESPACES = a meaningful or random namespace
  • OBJECT-STORAGE-RP.ORG-DOMAIN.TLD = the reverse proxy FQDN, might completely differ from OBJECT-STORAGE-RP, or not

On one hand there is notable performances trade offs: most object storage provider offer a CDN option to boost assets access in client’s browsers and this feature won’t be a viable option.
If you use signed access it won’t be possible to add a caching CDN in front of this reverse proxy. This instance can still be hidden by CloudFlare for instance, but with no performances improvement as the caching won’t work.

Doing so I’m wasting resources because my queries are going through many gateways (proxies) and encrypt/decrypt queries for nothing more than renaming. And I’m paying (a bit) for it.

On the other hand there is advantages. Doing so is also blurring lines, which is a security improvement (and even a business if you look at CloudFront and other Cloud-Behemoth).

It’s working, settings are flexible and this means that we can continue to work without CORS issues.

However this is not a solution in itself. The right way would be either of the following:

  • being able to setup our own hostname (FQDN) directly for the bucket of our Object Storage provider.
  • setting up the Cloud reverse proxy directly to point to the right origin server. CloudFlare in our case.

Resources:

Remove VPN/Network from Unify Controller using Command Line

After migrating a site from a self hosted network controller to a new Unify Cloud Key, I found myself in an annoying position: not being able to remove an old VTI VPN (from the previous configuration). The UI just didn’t offer this option like it should and actually does for other networks and VPNs. Searching the wild wild web didn’t help either, so I had to be creative.

But first let’s roll back a bit in time to better explain the issue: right after importing the site configuration, I had two sites configured. The “default” and “SITE2” sites. My newly imported “SITE2” site wasn’t the default one and this was an issue. I had to change it manually using this CLI technique because the UI doesn’t allow it.

So, based on the above mentioned technique, I succeeded to remove an old network from the settings, where the UI wasn’t competent.

SSH to your Cloud Key/Docker/Server wherever the Unify Network Controller is hosted. Then start the mongo DB CLI with mongo --port 27117.

Switch to the Network Controller database with use ace then get the list of networks with db.networkconf.find(). You should get something like this:

{ "_id" : ObjectId("HEX1"), "attr_no_delete" : true, "attr_hidden_id" : "WAN", "wan_networkgroup" : "WAN", "site_id" : "345634563465", "purpose" : "wan", "name" : "Default (WAN1)", "wan_type" : "pppoe", "wan_ip" : "80.153.183.45", "wan_username" : "LOGIN", "wan_type_v6" : "disabled", "x_wan_password" : "MAYBEMAYBE", "wan_provider_capabilities" : { "download_kilobits_per_second" : 250000, "upload_kilobits_per_second" : 40000 }, "report_wan_event" : false, "wan_load_balance_type" : "failover-only", "wan_load_balance_weight" : 50, "wan_vlan_enabled" : false, "wan_vlan" : "", "wan_egress_qos" : "", "wan_smartq_enabled" : true, "mac_override_enabled" : false, "wan_dhcp_options" : [ ], "wan_ip_aliases" : [ ], "wan_dns_preference" : "auto", "setting_preference" : "manual", "wan_smartq_up_rate" : 40000, "wan_smartq_down_rate" : 250000 }
{ "_id" : ObjectId("HEX2"), "purpose" : "guest", "networkgroup" : "LAN", "dhcpd_enabled" : true, "dhcpd_leasetime" : 86400, "dhcpd_dns_enabled" : false, "dhcpd_gateway_enabled" : false, "dhcpd_time_offset_enabled" : false, "ipv6_interface_type" : "none", "ipv6_pd_start" : "::2", "ipv6_pd_stop" : "::7d1", "gateway_type" : "default", "nat_outbound_ip_addresses" : [ ], "name" : "Guests", "vlan" : "2", "ip_subnet" : "192.168.7.1/24", "dhcpd_start" : "192.168.7.100", "dhcpd_stop" : "192.168.7.254", "dhcpguard_enabled" : true, "dhcpd_ip_1" : "192.168.7.1", "enabled" : true, "is_nat" : true, "dhcp_relay_enabled" : false, "vlan_enabled" : true, "site_id" : "123453", "lte_lan_enabled" : false, "setting_preference" : "manual", "mdns_enabled" : false, "auto_scale_enabled" : false, "upnp_lan_enabled" : false }
{ "_id" : ObjectId("HEX3"), "attr_hidden_id" : "WAN_LTE_FAILOVER", "wan_networkgroup" : "WAN_LTE_FAILOVER", "purpose" : "wan", "name" : "LTE Failover WAN", "site_id" : "3563465", "wan_type" : "static", "report_wan_event" : true, "wan_load_balance_type" : "failover-only", "wan_ip" : "IPADDRESS", "wan_gateway" : "IPADDR", "wan_netmask" : "255.255.255.254", "enabled" : true, "ip_subnet" : "192.168.123.161/30", "wan_dns_preference" : "auto", "setting_preference" : "auto" }
{ "_id" : ObjectId("HEX4"), "enabled" : true, "purpose" : "remote-user-vpn", "ip_subnet" : "192.168.2.1/24", "l2tp_interface" : "wan", "l2tp_local_wan_ip" : "any", "vpn_type" : "l2tp-server", "x_ipsec_pre_shared_key" : "SECRET", "setting_preference" : "auto", "site_id" : "12345, "name" : "VPN Server", "l2tp_allow_weak_ciphers" : false, "require_mschapv2" : false, "dhcpd_dns_enabled" : false, "radiusprofile_id" : "1234" }

Find the network that you cannot remove from the UI and type db.networkconf.deleteOne({ _id: ObjectId("HEX3") }) where HEX3 is your network ID. exit the CLI and check in the UI that the network has indeed been removed.

You default network should be the imported network now.

A nice nix-shell for Odoo

Since I recently switch to nixos and I’m working with odoo for a project, I had to update my debianesk habits and take what nixos has probably best to offer: an ultra-customized and optimized development environment using nix-shell.

This comes at the cost of extra readings and code, but the result offer a great flexibility. Now with 2 simple files I’m can load the whole environment and its dependencies just by cd‘ing in my project’s folder.

Beforehand, one needs to setup Direnv as described in the nix-shell doc.

Then, the following shell.nix file will suffice to load the whole environment when entering its folder:

{ pkgs ? import  {} }:
pkgs.mkShell {
  name = "odoo-env";
  buildInputs = with pkgs; [ python3 xclip openldap cyrus_sasl postgresql ];
  src = null;
  shellHook = ''
    # Allow the use of wheels.
    SOURCE_DATE_EPOCH=$(date +%s)

    VENV=.venv
    if test ! -d $VENV; then
      python -m venv $VENV
      source $VENV/bin/activate
      pip install -r requirements.txt
    fi
    source $VENV/bin/activate

    export PYTHONPATH=`pwd`/$VENV/${pkgs.python.sitePackages}/:$PYTHONPATH
    # export LD_LIBRARY_PATH=${with pkgs; lib.makeLibraryPath [ libGL xorg.libX11 xorg.libXext xorg.libXrender stdenv.cc.cc mtdev ]}
  '';
}
{ pkgs ? import {} }: pkgs.mkShell { name = "odoo-env"; buildInputs = with pkgs; [ python3 xclip openldap cyrus_sasl postgresql ]; src = null; shellHook = '' # Allow the use of wheels. SOURCE_DATE_EPOCH=$(date +%s) VENV=.venv if test ! -d $VENV; then python -m venv $VENV source $VENV/bin/activate pip install -r requirements.txt fi source $VENV/bin/activate export PYTHONPATH=`pwd`/$VENV/${pkgs.python.sitePackages}/:$PYTHONPATH # export LD_LIBRARY_PATH=${with pkgs; lib.makeLibraryPath [ libGL xorg.libX11 xorg.libXext xorg.libXrender stdenv.cc.cc mtdev ]} ''; }

Just a note about odoo’s dependencies on nixos. I haven’t been able to install proprely pyldap 2.4.28 which is the required version for odoo 12. Instead I installed the version 3.0.0 which seems to do just fine with odoo 12 as well. To do so, I updated the requirements.txtfile and changed this line

pyldap==2.4.28; sys_platform != 'win32'

with the appropriate version

pyldap==3; sys_platform != 'win32'

rsnapshot on Qnap with Firmware 4.x

I find that rsync is still the best solution to plan backups of remote hosts (and in general) and rsnapshot is its best companion. This combo enable incremental backups on any sort of device running a decent *nix os.

The story with Qnap and community packages is quite long. In a nutshell, the installation of rsnapshot on a Qnap NAS with a recent firmware (4.x) depends on Entware. In my case a Qnap TS-269L with a firmware 4.3.4.1190.

Entware is the latest package manager successor for a variety of NAS. This will install the opkg command which this turn enables to install rsnapshot. The “Entware App” can be downloaded here and installed in the App Center of the Qnap UI. The small icon on the top right in the App Center enables to install the *.qpgk downloaded file.

manual install icon

While loading the file you might see a warning about alternative sources. The process continues then in a terminal, using ssh.

Once logged in as admin, the following command will suffice to install rsnapshot and its dependencies.

$ opkg install rsnapshot

Settings for rsnapshot can then be found in /opt/etc/rsnapshot.conf.

Then it’s just a matter of adding rsnapshot to cron, my setup looks like this:

$ crontab -e
5 * * * * /opt/bin/rsnapshot -c /opt/etc/rsnapshot.conf hourly
0 2 * * * /opt/bin/rsnapshot -c /opt/etc/rsnapshot.conf daily
30 3 1 * * /opt/bin/rsnapshot -c /opt/etc/rsnapshot.conf monthly
30 4 * * 6 /opt/bin/rsnapshot -c /opt/etc/rsnapshot.conf weekly

Enjoy a decent backup solution! The next step will be to monitor it…

Thinkpad Carbon X1 1st-3d Gen and Ubuntu 18.04

My dear friend Dirk just bought a second-hand Carbon X1 first generation, just like mine (3448 serie). I thought he might need a quick help to get going with Ubuntu 18.04 and this machine. Note that the content of this tutorial works for the 2nd and 3rd generation (20BS serie) as well.

UPDATE(2019-08-18) I just bought a second-hand T460s with a bit more ram than the Carbon X1 and followed this tutorial.

I had 3 complains after installing a brand new Ubuntu 18.04 LTS on an encrypted disk:

  • no hibernate feature
  • no fingerprint scanner feature
  • a too sensitive trackpad

Hopefully all these problems can be solved to provide the OS this laptop deserves.

Hibernate

This chapter is the trickiest part of this tutorial, therefore it comes first. Keep in mind that we are going to change partitions sizes, it’s best to do it right after linux have been installed: when all you can loose is your time, not your data. If your laptop has a running system with sensitive data: please do a backup first.

In order to enable the hibernation, we’ll need to fix the swap partition size, so the RAM content can fit in it when the machine goes to deep sleep. Then we’ll need to setup systemd so it suspends when the lid is closed, and hibernate after a (defined) while if not resumed in the meantime. And to close this chapter, we’ll setup some policies to enable the “hibernate” button in the system menu.

Partitioning

If you didn’t partition the SSD yourself during the installation, high are the chance that your swap partition is smaller than the amount of RAM in your machine. And if you succeeded to install Ubuntu 18.04 with a custom partitioning and and encrypted file-system, than please leave a note in the comment to explain how. I tried many different approach without success: whether the install failed or the machine didn’t boot properly. So let’s start with the assumption that you installed Ubuntu with an encrypted file-system, by letting the installer partition your disk.

You’ll need to boot on the installation disk/usb-drive again, because we shouldn’t change the partition of a running system. Open a Terminal window and decrypt the encrypted partition of your installed system:

ubuntu@ubuntu:~$ sudo cryptsetup luksOpen /dev/sda3 crypt1
Enter passphrase for /dev/sda3

In that example, ensure that /dev/sda3 is your encrypted volume. If you are not sure, check with fdisk -l which is the big partition on your hard-drive, this is most likely the last one. On a NVMe hard-drive the name might be very different.

Once the volume decrypted, scan the LVM volume-groups and active them with the following commands:

root@ubuntu:~# vgscan --mknode
Reading volume groups from cache.
Found volume group "ubuntu-vg" using metadata type lvm2
root@ubuntu:~# vgchange -ay
2 logical volume(s) in volume group "ubuntu-vg" now active

You’ll be then able to check that the volume-group has been activated correctly with:

root@ubuntu:~# pvs
PV VG Fmt Attr PSize PFree
/dev/mapper/crypt1 ubuntu-vg lvm2 a-- 237.25g 48.00m

and list logical-volumes with:

root@ubuntu:~# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
root ubuntu-vg -wi-a----- 236.25g
swap_1 ubuntu-vg -wi-a----- 976.00m

It is recommended to scan the file-system prior to modifications:

root@ubuntu:~# e2fsck -f /dev/mapper/ubuntu--vg-root

Now you can resize your root partition to give some room for the swap partition. There is a whole science to calculate how big your swap should be if you want to hibernate, or you can follow the rule of thumb: RAM amount + 4GB. If the swap is exactly the size of your RAM this might work as well, a bit extra is recommended if your machine is actually swapping – rarely on modern machine – but this depend on you usage. If you start docker or virtual box sometime, follow the rule of thumb…

So in my case the volume-group is about 237GB big, I subtracted 12GB for the swap it leaves 225GB for the root (system) partition:

root@ubuntu:~# resize2fs -p /dev/mapper/ubuntu--vg-root 225g
resize2fs 1.44.1 (24-Mar-2018)
Resizing the filesystem on /dev/mapper/ubuntu--vg-root to 58982400 (4k) blocks.
Begin pass 2 (max = 2947)
Relocating blocks XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Begin pass 3 (max = 1880)
Scanning inode table XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Begin pass 4 (max = 24242)
Updating inode references XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
The filesystem on /dev/mapper/ubuntu--vg-root is now 58982400 (4k) blocks long.

To be sure, just check the file-system again with:

root@ubuntu:~# e2fsck -f /dev/mapper/ubuntu--vg-root
e2fsck 1.44.1 (24-Mar-2018)
Pass 1: Checking inodes, blocks, and sizes
Pass 2: Checking directory structure
Pass 3: Checking directory connectivity
Pass 4: Checking reference counts
Pass 5: Checking group summary information
/dev/mapper/ubuntu--vg-root: 177123/14745600 files (0.1% non-contiguous), 2426029/58982400 blocks

you can now safely reduce the size of the logical-volume with:

root@ubuntu:~# lvreduce -L 225G -r /dev/ubuntu-vg/root 
fsck from util-linux 2.31.1
/dev/mapper/ubuntu--vg-root: clean, 177123/14745600 files, 2426029/58982400 blocks
resize2fs 1.44.1 (24-Mar-2018)
The filesystem is already 58982400 (4k) blocks long. Nothing to do!
Size of logical volume ubuntu-vg/root changed from 236.25 GiB (60481 extents) to 225.00 GiB (57600 extents).
Logical volume ubuntu-vg/root successfully resized.

You can remove the old swap volume:

root@ubuntu:~# lvremove /dev/ubuntu-vg/swap_1 
Do you really want to remove and DISCARD active logical volume ubuntu-vg/swap_1? [y/n]: y
Logical volume "swap_1" successfully removed

…and create a new bigger one. This following command says “use the remaining space”, and note that volume names are kept the same.

root@ubuntu:~# lvcreate -l 100%FREE -n swap_1 ubuntu-vg 
Logical volume "swap_1" created.

Check what’s just been done, the list of logical-volumes:

root@ubuntu:~# lvs
LV VG Attr LSize Pool Origin Data% Meta% Move Log Cpy%Sync Convert
root ubuntu-vg -wi-a----- 225.00g
swap_1 ubuntu-vg -wi-a----- 12.25g

Create the swap file-system:

root@ubuntu:~# mkswap -L swap_1 /dev/ubuntu-vg/swap_1 
Setting up swapspace version 1, size = 12.3 GiB (13157527552 bytes)
LABEL=swap_1, UUID=4a10e0f5-44d7-4f57-ae23-de172958e7f1

And adapt the fstab if required. If you gave it the same logical-volume name than it already was, you can skip this step. Ubuntu 18.04 uses LV names instead of UUID.

root@ubuntu:~# mount /dev/ubuntu-vg/root /mnt/
root@ubuntu:~# vi /mnt/etc/fstab
root@ubuntu:~# umount /mnt

Write last changes to the disk:

root@ubuntu:~# pvchange -x n /dev/mapper/crypt1
Physical volume "/dev/mapper/crypt1" changed
1 physical volume changed / 0 physical volumes not changed

…and deactivate open volume-groups:

root@ubuntu:~# vgchange -an
0 logical volume(s) in volume group "ubuntu-vg" now active
root@ubuntu:~# cryptsetup luksClose crypt1

You can now restart your machine and boot your installed operating system. You will be prompted for the password at boot to decrypt the LUKS volume. Everything should work as before, but this time with a bigger swap partition.

Lastly, you’ll have to change a line in /etc/default/grub to tell your kernel that there possibly is the content of your RAM in your swap when booting. If so, it should “resume” from hibernation.

GRUB_CMDLINE_LINUX_DEFAULT="resume=UUID=4a10e0f5-44d7-4f57-ae23-de172958e7f1"

Where the value of UUID is the string returned when running mkswap to make the swap. If you don’t find it back in your terminal, just run:

lsblk -o NAME,UUID

Once GRUB defaults updated you will need to update the actual GRUB config files with the command

sudo update-grub

Reboot your machine, test the hibernation. If this works as it should, tap your own shoulder: Congratulation, the hardest work is done.

Configure hardware events and enable “hibernate” in the system actions (top right menu)

I wanted my laptop to suspend when I close the lid and hibernate after a defined time if the suspend isn’t resumed (namely: the lid kept closed)

To enable that, edit as system-logind configuration file:

sudo vim /etc/systemd/logind.conf

You’ll be prompted for the root password. If you don’t know how to use vim just grow up and learn.
Vi won’t be be removed from major distribution anytime soon.

Well, uncomment and edit the HandleHibernateKey and HandleLidSwitch variables:

[Login]
#NAutoVTs=6
#ReserveVT=6
#KillUserProcesses=no
#KillOnlyUsers=
#KillExcludeUsers=root
#InhibitDelayMaxSec=5
#HandlePowerKey=poweroff
#HandleSuspendKey=suspend
HandleHibernateKey=hibernate
HandleLidSwitch=suspend-then-hibernate
#HandleLidSwitchDocked=ignore
#PowerKeyIgnoreInhibited=no
#SuspendKeyIgnoreInhibited=no
#HibernateKeyIgnoreInhibited=
#LidSwitchIgnoreInhibited=yes
#HoldoffTimeoutSec=30
#IdleAction=ignore
#IdleActionSec=30min
#RuntimeDirectorySize=10%
#RemoveIPC=yes
#InhibitorsMax=8192
#SessionsMax=8192
#UserTasksMax=33%

The timeout to switch from suspend to hibernate can be set in /etc/systemd/sleep.conf. The file might not yet exists.

sudo vim /etc/systemd/sleep.conf
[sleep]
HibernateDelaySec=900

I found 15 minutes a good delay for an old 1th generation with a battery holding about 2h. For a 3rd generation with a much better battery (and economic architecture), I switched it to one hour.

When you are done, restart systemd-logind service with the following command:

sudo systemctl restart systemd-logind.service

If you need to debug the service, and possibly find a mistake in your config file, just check the logs:

sudo journalctl -u systemd-logind.service

Next, to enable the “hibernate” button in the system menu edit the /etc/polkit-1/localauthority/50-local.d/com.ubuntu.enable-hibernate.pkla file.

sudo vim /etc/polkit-1/localauthority/50-local.d/com.ubuntu.enable-hibernate.pkla

with the following content

[Re-enable hibernate by default in upower]
Identity=unix-user:* Action=org.freedesktop.upower.hibernate ResultActive=yes

[Re-enable hibernate by default in logind]
Identity=unix-user:* Action=org.freedesktop.login1.hibernate;org.freedesktop.login1.handle-hibernate-key;org.freedesktop.login1;org.freedesktop.login1.hibernate-multiple-sessions;org.freedesktop.login1.hibernate-ignore-inhibit ResultActive=yes

When your computer is in hibernation and you push the power button, you’ll be asked for a password, just like a normal boot. That’s right, the content of your RAM has been saved to the hard-drive and the machine completely turned off. To resume it, the volume need to be decrypted again, and for that your password is asked.

That’s it for the hibernation.

Trackpad

If you find that the trackpad is too sensitive, install the synaptic driver

xserver-xorg-input-synaptics-hwe-18.04

and copy the following content in /etc/X11/Xsession.d/56_synaptic_fix :

 export `xinput list | grep -i touchpad | awk '{ print $6 }'`
xinput --set-prop "$id" "Synaptics Noise Cancellation" 20 20
xinput --set-prop "$id" "Synaptics Finger" 35 45 250
xinput --set-prop "$id" "Synaptics Scrolling Distance" 180 180
true

Ensure that the owner of the file is root and permissions 644. Restart your session and you’ll have a usable trackpad.

Fingerprint scanner

You should probably not use the fingerprint scanner if you have strong security expectations. Considering that the filesystem is encrypted, one need a password to start the system. However, if the system is just in power-save mode and you wake it up, you’ll probably see a login prompt. This is the main case one could attack the fingerprint reader and try to gain access to the system.

Just install the pam package to enable fingerprint authentication on Ubuntu:

sudo apt install libpam-fprint

Then run the following command to teach your system to use the fingerprint as an authentication system:

sudo pam-auth-update

…and select the fingerprint option

You’ll find a new “Fingerprint login” option in users settings to register one finger.

Keep in mind that the finger-print authentication is not the safest thing ever. Your finger isn’t a password, it’s an image and the computer will try to “guess” if this is you, with potential mistakes. You will be able to log in with this technique, but not to unlock your password-keychain. For the latter you’ll always need a real password.

References

http://ubuntuhandbook.org/index.php/2018/05/add-hibernate-option-ubuntu-18-04/

https://help.ubuntu.com/community/PowerManagement/Hibernat

https://help.ubuntu.com/community/ResizeEncryptedPartition

https://askubuntu.com/questions/1049526/fingerprint-activation-on-ubuntu-18-04

https://ubuntu-mate.community/t/hibernate-resume-from-hibernation-ubuntu-mate-18-04/16924

https://help.ubuntu.com/community/EnableHibernateWithEncryptedSwap

https://askubuntu.com/questions/1072504/lid-closed-suspend-then-hibernate

https://wiki.archlinux.org/index.php/Dm-crypt/Swap_encryption

Rails/Devise authenticating using AWS/Cognito Identity

Background

For a while now, I’m developing a sort of IoT controller with Rails 4. Until now, Devise was used to authenticate users locally using the Devise’s provided :database_authenticable module.

Things changed recently, and I had to move some features of this IoT controller toward AWS. Authentication against AWS/Cognito Identity is one part of the project.
But Why?” you might wonder. Because we need to extend authentication to other products, using a common user database. Cognito fits the description and helps boost the development on the AWS ecosystem. In other words, we want to be able to use our IoT controller alongside with other new products without depending exclusively on the authentication part of the IoT controller.
This means that local user database will still be used for the application-related data like permissions, session, extended profile attributes or database relationship, but pure authentication happen exclusively on Cognito. I moved to AWS just the first A of AAA. This is a disputable choice, let’s call it team work.

So, the purpose of this post is to synthesize the procedure required in order to:

  • authenticate against Cognito
  • handle passwords manipulation and accounts validation
  • migrate user data with a Lambda triggered by Cognito
  • create new local users if they are not already existing

The following example should work well with recent Rails versions.

Dependencies

Obviously Rails and Devise are required, in addition to the following gem:

gem 'aws-sdk', '~> 3'

Rails 4/Devise adaptation

Environment variables will carry all the AWS specific access information to Rails. Depending on the setup,  /.env file probably already exists in which we can add the two following Cognito Pool info (TLDR; Create a new App client without App client secret) :

AWS_COGNITO_USER_POOL_ID=eu-west-1_234567
AWS_COGNITO_CLIENT_ID=01234567890asdfghjkllqwertzuiop1234567890

where the variables values matches the AWS setup.

We’ll need a new a new Devise strategy, a safe location for this file is in /config/initializers/cognito_authenticable.rb :

require 'aws-sdk'
require 'devise/strategies/authenticatable'

module Devise
  module Strategies
    class CognitoAuthenticatable < Authenticatable
      def authenticate!
        if params[:user]

          client = Aws::CognitoIdentityProvider::Client.new

          begin

            resp = client.initiate_auth({
              client_id: ENV["AWS_COGNITO_CLIENT_ID"],
              auth_flow: "USER_PASSWORD_AUTH",
              auth_parameters: {
                "USERNAME" => email,
                "PASSWORD" => password
              }
            })

            if resp
              user = User.where(email: email).try(:first)
              if user
                success!(user)
              else
                user = User.create(email: email, password: password, password_confirmation: password)
                if user.valid?
                  success!(user)
                else
                  return fail(:failed_to_create_user)
                end
              end
            else
              return fail(:unknow_cognito_response)
            end

          rescue Aws::CognitoIdentityProvider::Errors::NotAuthorizedException => e

            return fail(:invalid_login)

          rescue

            return fail(:unknow_cognito_error)

          end

        end
      end

      def email
        params[:user][:email]
      end

      def password
        params[:user][:password]
      end

    end
  end
end

This strategy will create a new local user if Cognito authenticated it, but the user doesn’t exist locally.

The we can configure Devise to use the strategy by giving it the class name in the file /config/initializers/devise.rb

Devise.setup do |config|

  # (...)

  config.warden do |manager|
    manager.strategies.add(:cognito, Devise::Strategies::CognitoAuthenticatable)
    manager.default_strategies(:scope => :user).unshift :cognito
  end

  # (...)

end

Now Devise should authenticate against the Cognito user database.

The next step would be to allow password reset using the password recovery system of AWS Cognito.

class Users::PasswordsController < Devise::PasswordsController

  skip_before_action :assert_reset_token_passed

  def create

    raise ArgumentError, "Unexpected block given for requested action: #{params.inspect}" if block_given?

    begin

      client = Aws::CognitoIdentityProvider::Client.new
      resp = client.forgot_password({
        client_id: ENV["AWS_COGNITO_CLIENT_ID"],
        username: params[:user][:email]
      })

      session[:reset_password_email] = params[:user][:email]

      redirect_to edit_user_password_path

    rescue

      flash[:alert] = I18n.t("devise.errors.unknown_error")
      redirect_to new_user_password_path

    end

  end

  def edit

    gon.flash_notice = I18n.t("devise.notices.change_password_email")
    super

  end

  def update

    if params[:user][:password].blank?

      flash[:alert] = I18n.t("activerecord.errors.models.user.attributes.password.blank")
      redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])

    elsif params[:user][:password] != params[:user][:password_confirmation]

      flash[:alert] = I18n.t("activerecord.errors.models.user.attributes.password.mismatch")
      redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])

    elsif params[:user][:reset_password_token].blank?

      flash[:alert] = I18n.t("devise.errors.verification_code_missing")
      redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])

    elsif session[:reset_password_email].nil?

      flash[:alert] = I18n.t("devise.errors.verification_code_expired")
      redirect_to new_user_password_path

    else

      begin

        client = Aws::CognitoIdentityProvider::Client.new
        resp = client.confirm_forgot_password({
          client_id: ENV["AWS_COGNITO_CLIENT_ID"],
          confirmation_code: params[:user][:reset_password_token],
          username: session[:reset_password_email],
          password: params[:user][:password]
        })

        session.delete :reset_password_email

        redirect_to unauthenticated_root_path, notice: I18n.t("devise.notices.password_changed")

      rescue Aws::CognitoIdentityProvider::Errors::InvalidPasswordException => e

        flash[:alert] = e.to_s
        redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])

      rescue

        flash[:alert] = I18n.t("devise.errors.unknown_error")
        redirect_to edit_user_password_path(reset_password_token: params[:user][:reset_password_token])

      end

    end

  end

end

In this case, the sign up procedure is still handled by Rails on the local database (see below), therefore account confirmation (with Devise’s :confirmable  module) is handled by Devise without any changes.

Migrating users from Rails to Cognito

We wanted to migrate a user from the Rails database to Cognito if the user isn’t already existing in the Cognito database. For that at least a new endpoint in config/routes.rb  is required:

​post '/aws/auth',
 to: 'users#aws_auth',
 defaults: {format: 'json'},
 as: 'aws_auth'

For this route it is supposed that controllers/users_controller.rb  have a aws_auth  method:

class UsersController < ApplicationController

  # skip_filter other access restrictions...
  before_filter :restrict_access, only: [:aws_auth]

  # (...)

  def aws_auth

    defaults = {
      id: nil,
      first_name: nil,
      last_name: nil,
      email: nil,
      authentication_hash: nil
    }
    user = User.where(email: aws_auth_params[:email]).first

    if user
      answer = user.as_json(only: defaults.keys)
      answer[:user_exists] = true
      answer[:success] = user.valid_password?(aws_auth_params[:password])
    else
      answer = defaults
      answer[:success] = false
      answer[:user_exists] = false
    end

    respond_to do |format|
      format.json { render json: answer }
    end

  end

  # (...)

  private

    def restrict_access
      head :unauthorized unless params[:access_token] == TOKEN_AUTH_OF_YOUR_CHOICE
    end

end

That’s all for the Rails side, now in Cognito Pool of the AWS console there is a Trigger, in which lambdas can be attached. The User Migration trigger can be set to the following JavaScript lambda:

'use strict';

console.log('Loading function');

const https = require('https');

const attributes = (response) => {
  return {
    "email": response.email,
    "email_verified": "true",
    "name": response.first_name + " " + response.last_name,
    "custom:rails_app_id": response.id
  };
};

const checkUser = (server, data, callback) => {
  let postData = JSON.stringify( data );

  let options = {
    hostname: server,
    port: 443,
    path: "/aws/auth",
    method: 'POST',
    headers: {
         'Content-Type': 'application/json',
         'Content-Length': postData.length
       }
  };

  let req = https.request(options, (res) => {

    let data = "";
    res.on('data', (chunk) => {
      data += chunk;
    });
    res.on('end', () => {
      if ( data ){
        let response = JSON.parse( data );
        console.log( 'response:', JSON.stringify(response, null, 2) );
        callback( null, response);
      } else {
        callback( "Authentication error");
      }
    });
  });

  req.on('error', (e) => {
    callback( e );
  });

  req.write( postData );
  req.end();
}
exports.handler = (event, context, callback) => {

  console.log('Migrating user:', event.userName);

  let rails_server_url = process.env.rails_server_url || "rails.app.your_company.com";

  checkUser( rails_server_url, {
    email: event.userName,
    password: event.request && event.request.password,
    access_token: process.env.rails_server_access_token
  }, (err, response ) => {
    if ( err ){
      return context.fail("Connection error");
    }
    if ( event.triggerSource == "UserMigration_Authentication" ) {
      // authenticate the user with your existing user directory service
      if ( response.success ) {
          event.response.userAttributes = attributes( response ) ;
          event.response.finalUserStatus = "CONFIRMED";
          event.response.messageAction = "SUPPRESS";
          console.log('Migrating user:', event.userName);
          context.succeed(event);
      } else if ( response.user_exists ) {
          context.fail("Bad password");
      } else {
        context.fail("Bad user");
      }
    } else if ( event.triggerSource == "UserMigration_ForgotPassword" ) {
      if ( response.user_exists ) {
        event.response.userAttributes = attributes( response ) ;
        event.response.messageAction = "SUPPRESS";
        console.log('Migrating user with password reset:', event.userName);
        context.succeed(event);
      } else {
        context.fail("Bad user");
      }
    } else {
      context.fail("Bad triggerSource " + event.triggerSource);
    }
  });
};

This scripts use the two following environment variables, which should be set with the proper information:

rails_server_url=URL
rails_server_access_token=TOKEN_AUTH_OF_YOUR_CHOICE

Conclusion

It’s possible.

Raspberry Pi 3 and bluetooth speaker setup [command line]

Streaming sound from a Raspberry Pi 3 to a bluetooth speaker was not as easy as it should. Internet is overloaded with outdated information about this issue. So I compiled a small tutorial for the latest Raspbian (at the date of writing). My setup is composed with the following elements:

Trying to connect the device to the Raspberry Pi with the following code will raise an error:  Failed to connect: org.bluez.Error.Failed

pi@raspi:~# sudo bluetoothctl -a
Agent registered
[bluetooth]# default-agent
Default agent request successful
[bluetooth]# power on
Changing power on succeeded
[bluetooth]# scan on
Discovery started
[CHG] Controller B8:27:EB:XX:XX:XX Discovering: yes
[NEW] Device 40:EF:4C:AA:BB:CC JBL GO
[CHG] Device 40:EF:4C:AA:BB:CC UUIDs:
0000110d-0000-1000-8000-00805f9baabb
0000110b-0000-1000-8000-00805f9baabb
0000110e-0000-1000-8000-00805f9baabb
0000110f-0000-1000-8000-00805f9baabb
0000111e-0000-1000-8000-00805f9baabb
00001108-0000-1000-8000-00805f9baabb
00001131-0000-1000-8000-00805f9baabb
[bluetooth]# pair 40:EF:4C:AA:BB:CC
Attempting to pair with 40:EF:4C:AA:BB:CC
[CHG] Device 40:EF:4C:AA:BB:CC Connected: yes
[bluetooth]# trust 40:EF:4C:AA:BB:CC
[CHG] Device 40:EF:4C:AA:BB:CC Trusted: yes
Changing 40:EF:4C:AA:BB:CC trust succeeded
[CHG] Device 40:EF:4C:AA:BB:CC UUIDs:
00001108-0000-1000-8000-00805f9baabb
0000110b-0000-1000-8000-00805f9baabb
0000110c-0000-1000-8000-00805f9baabb
0000110e-0000-1000-8000-00805f9baabb
0000111e-0000-1000-8000-00805f9baabb
[CHG] Device 40:EF:4C:AA:BB:CC Paired: yes
Pairing successful
[CHG] Device 40:EF:4C:AA:BB:CC Connected: no
[bluetooth]# connect 40:EF:4C:AA:BB:CC
Attempting to connect to 40:EF:4C:AA:BB:CC
Failed to connect: org.bluez.Error.Failed

It is not obvious, but what is missing to the connecting device is the answer from the host saying that it can stream sound. In order to send the correct answer to the device, pulseaudio should be started first and should correctly load the bluetooth module.

First, ensure that all requirements are installed:

pi@raspi:~# sudo apt install pi-bluetooth pulseaudio-module-bluetooth mpg123

Then start pulseaudio an check that the module is indeed loaded:

pi@raspi:~# sudo pulseaudio --start
W: [pulseaudio] main.c: This program is not intended to be run as root (unless --system is specified).
pi@raspi:~# pactl list | grep bluetooth
Name: module-bluetooth-policy
module.description = "When a bluetooth sink or source is added, load module-loopback"
Name: module-bluetooth-discover

At this point, if you try to connect your device, this should work. Most of the devices produce a sound when the connection succeed. Within bluetoothctl , it is not required to redo the pairing procedure, but before continuing you may need to ensure that the bluetooth controller is on with [bluetooth]# power on .

[bluetooth]# connect 40:EF:4C:AA:BB:CC
Attempting to connect to 40:EF:4C:AA:BB:CC
[CHG] Device 40:EF:4C:AA:BB:CC Connected: yes
Connection successful

Load a MP3 on your Raspberry Pi and play it using mpg123 your.mp3 .

If you restart your Raspberry Pi now, your device will fail to connect again.
pulseaudio is not really ment to start system-wide at boot, but in the case of embedded device it makes sense.

You could use /etc/rc.local  but it’s a bit punk, so instead let’s create a systemd service.
Add the following content to this file /lib/systemd/system/pulseaudio.service :

[Unit]
Description=PulseAudio Daemon

[Install]
WantedBy=multi-user.target

[Service]
Type=simple
PrivateTmp=true
ExecStart=/usr/bin/pulseaudio --system --realtime --disallow-exit --no-cpu-limit

And start the service by hand using systemctl start pulseaudio . When running systemctl status pulseaudio , you may find a warning about starting pulseaudio system-wide, but don’t worry, with this setup it makes sense.
Add pulseaudio to the list of services to start at boot: systemctl enable pulseaudio.service

Don’t forget to add both root and pi users to the group pulse-access in /etc/groups :

pulse-access:x:115:root,pi

Note that the group ID 115 may be different on your system.

Reboot your Raspberry Pi and you are good to go, if the speaker in ON, it should be automatically connected to your system.

Let’s encrypt (il buono), rails (il brutto) and heroku (il cattivo)

Il buono

This article is just paraphrasing this one with a bit more accuracy, corrections and cynicism.


First step: Update your rails code on heroku

The route /.well-known/acme-challenge/KEY  should be added to your config/routes.rb  file like so

get ‘/.well-known/acme-challenge/:id’ => ‘CONTROLLER#letsencrypt’

where CONTROLLER  is the controller of your choice, in which the method should look like this

 def letsencrypt
   if params[:id] == ENV['LETSENCRYPT_KEY']
     render text: ENV['LETSENCRYPT_CHALLENGE']
   else
     render text: "nope"
   end
 end

and don’t forget to make it “public”, so if you are using cancancan the following line is required on top of your controller file

skip_authorization_check only: [:letsencrypt]

Push it on heroku

> git push heroku # this may differ depending on your setup

and wait for it be deployed.


Second step: Install require software and generate the key

On ubuntu you can install letsencrypt  command like this

> sudo apt install letsencrypt

The run the command with root privileges

> sudo letsencrypt certonly --manual

follow the instructions and when it asks you to verify that the given URL is reachable, don’t presse ENTER but follow third step instead.


Third step: Update Heroku variables

Go on Heroku console, in settings>Reveal config vars and add LETSENCRYPT_KEY  and LETSENCRYPT_CHALLENGE  keys with their corresponding values from letsencrypt  command, a step before.

Restart Heroku within UI or with the following command where YOUR_APP_NAME  is… your app name.

> heroku restart -a YOUR_APP_NAME

It would be a good idea to try the URL from your browser before coninuing.


Fourth step: Verify the challenge and push certificate to Heroku

If your SSL endpoint is not yet setup on Heroku, take the time and money to do it

> heroku addons:create ssl:endpoint -a YOUR_APP_NAME

Then you will be able to push the certificate to your Heroku instance.

> sudo heroku certs:add /etc/letsencrypt/live/YOUR_DOMAIN/fullchain.pem /etc/letsencrypt/live/YOUR_DOMAIN/privkey.pem -a YOUR_APP_NAME

If it’s a certificate update, replace the certs:add  by certs:update  and your are good.


Fifth and last step: Behold!

Give yourself some time for a walk and think about the beauty of living, yet still away from the coming technological singularity.

Slow Query with “LIMIT 1” in Postgresql

Recently I had issues on a production database running slow on tables with more than 1M rows.
This is a complex Ruby on Rails app connected to a Postgresql database, I went through the whole stack to figure out that it’s a simple index problem. Very few or no related doc was found about this issue, therefor I quickly post a summary of my adventure.

In Rails I often write something simple as ActivityLog.first or Player.find(123).activity_logs.first , where the model ActivityLog have a default scope describe like so:

class ActivityLog < ActiveRecord::Base
  default_scope { order("created_at desc") }
end

This leads to a SQL query looking like this:

SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 123 ORDER BY created_at ASC LIMIT 1;

This is probably one of the most common query in my Rails applications, and certainly not only in my applications.

Unfortunately, this may become a slow query on big tables. Indeed, if this query is often called your app may become from sticky to unusable (the latter was my case).

The following example illustrate the issue.
I recall it: this may be a very common query:

# EXPLAIN ANALYSE SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 123 ORDER BY created_at ASC LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..464.30 rows=1 width=708) (actual time=1124.357..1124.357 rows=0 loops=1)
-> Index Scan using index_activity_logs_on_created_at on activity_logs (cost=0.42..421660.15 rows=909 width=708) (actual time=1124.356..1124.356 rows=0 loops=1)
Filter: (player_id = 123)
Rows Removed by Filter: 1033340
Planning time: 0.084 ms
Execution time: 1124.378 ms
(6 rows)

This query on my dev machine, which is way more powerful than our production VPS takes 1’124ms to run. If called only once per user’s click, this would immediately degrade your Apdex score.

As described in Postresql doc and by reading the EXPLAIN (ANALYZE, BUFFERS) output, a lot of disk access caused by the filter on player_id makes the query slow.

The numbers provided by BUFFERS help to identify which parts of the query are the most I/O-intensive.

# EXPLAIN (ANALYSE, BUFFERS) SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 123 ORDER BY created_at ASC LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..464.30 rows=1 width=708) (actual time=1127.860..1127.860 rows=0 loops=1)
Buffers: shared hit=405695 read=445170
-> Index Scan using index_activity_logs_on_created_at on activity_logs (cost=0.42..421660.15 rows=909 width=708) (actual time=1127.858..1127.858 rows=0 loops=1)
Filter: (player_id = 123)
Rows Removed by Filter: 1033340
Buffers: shared hit=405695 read=445170
Planning time: 0.083 ms
Execution time: 1127.880 ms
(8 rows)

The planner took the decision to use an index scan on created_at (the ORDER BY  attribute) and then filter player_id which in this situation isn’t the best choice. Disabling it with SET enable_indexscan = OFF;  improve drastically the performances, but it’s not a production solution.

# SET enable_indexscan = OFF;
# EXPLAIN ANALYSE SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 123 ORDER BY created_at ASC LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3395.09..3395.09 rows=1 width=708) (actual time=0.026..0.026 rows=0 loops=1)
-> Sort (cost=3395.09..3397.36 rows=909 width=708) (actual time=0.025..0.025 rows=0 loops=1)
Sort Key: created_at
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on activity_logs (cost=19.47..3390.54 rows=909 width=708) (actual time=0.021..0.021 rows=0 loops=1)
Recheck Cond: (player_id = 123)
-> Bitmap Index Scan on index_activity_logs_on_player_id (cost=0.00..19.24 rows=909 width=0) (actual time=0.019..0.019 rows=0 loops=1)
Index Cond: (player_id = 268)
Planning time: 0.087 ms
Execution time: 0.052 ms
(10 rows)

The query execution time drop down to 0.05ms which is factor of 22’480 from the previous planner decision !

As described here another way to trick the planner is to add another field in the ORDER BY  clause.

# EXPLAIN (ANALYSE, BUFFERS) SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 268 ORDER BY created_at ASC, id ASC LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=3395.09..3395.09 rows=1 width=708) (actual time=0.038..0.038 rows=0 loops=1)
Buffers: shared read=3
-> Sort (cost=3395.09..3397.36 rows=909 width=708) (actual time=0.037..0.037 rows=0 loops=1)
Sort Key: created_at, id
Sort Method: quicksort Memory: 25kB
Buffers: shared read=3
-> Bitmap Heap Scan on activity_logs (cost=19.47..3390.54 rows=909 width=708) (actual time=0.034..0.034 rows=0 loops=1)
Recheck Cond: (player_id = 268)
Buffers: shared read=3
-> Bitmap Index Scan on index_activity_logs_on_player_id (cost=0.00..19.24 rows=909 width=0) (actual time=0.032..0.032 rows=0 loops=1)
Index Cond: (player_id = 268)
Buffers: shared read=3
Planning time: 0.093 ms
Execution time: 0.060 ms
(14 rows)

Which improve the execution time in a comparable way. In a hurry this the solution a adopted, I have quickly patched my scope that way to improve globally the performances. It was safe and easy to commit with the advantage not to expand database indices size.

class ActivityLog < ActiveRecord::Base
  default_scope { order("created_at desc, id desc") }
end

But for the sake of a better understanding I dug a bit more this issue. I have more than one big table, and I have more than one app online… I don’t like to keep such gray zone and smoking work-around in my code.

First, I tried to help the planner change its decision by tweaking statistics on every column, but it didn’t changed planner decision and didn’t improve the execution time.

# ALTER TABLE activity_logs ALTER COLUMN created_at SET STATISTICS 1000;
# EXPLAIN (ANALYSE, BUFFERS) SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 268 ORDER BY created_at ASC LIMIT 1;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..474.81 rows=1 width=707) (actual time=1145.167..1145.167 rows=0 loops=1)
Buffers: shared hit=405660 read=445205
-> Index Scan using index_activity_logs_on_created_at on activity_logs (cost=0.42..421251.54 rows=888 width=707) (actual time=1145.165..1145.165 rows=0 loops=1)
Filter: (player_id = 268)
Rows Removed by Filter: 1033340
Buffers: shared hit=405660 read=445205
Planning time: 0.092 ms
Execution time: 1145.187 ms
(8 rows)

Then I realized that simply building an index on player_id, my condition, and created_at, my ordering attribute, should help Postgersql extracting my query while using the actual planner decision.

CREATE INDEX activity_logs_player_id_created_at ON activity_logs (player_id, created_at);

…and the end result is pleasant enough. 0.03ms – my work is almost done.

# SET enable_indexscan = ON;
# EXPLAIN (ANALYSE, BUFFERS) SELECT "activity_logs".* FROM "activity_logs" WHERE "activity_logs"."player_id" = 268 ORDER BY created_at ASC LIMIT 1;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.42..4.45 rows=1 width=707) (actual time=0.013..0.013 rows=0 loops=1)
Buffers: shared hit=3
-> Index Scan using activity_logs_player_id_created_at on activity_logs (cost=0.42..3570.31 rows=888 width=707) (actual time=0.012..0.012 rows=0 loops=1)
Index Cond: (player_id = 268)
Buffers: shared hit=3
Planning time: 0.099 ms
Execution time: 0.031 ms
(7 rows)

On the Rails side a simple migration like the following change my user’s life, and restored my peace of mind.

class AddIndicesToBigTables < ActiveRecord::Migration
  def change
    add_index :activity_logs, [:player_id, :created_at], unique: false
  end
end