sudo -u postgres psql  
postgres=# create database mydb;  
postgres=# create user myuser with encrypted password 'mypass';  
postgres=# grant all privileges on database mydb to myuser;

and as of postgres 15 there’s a change to the public schema due to security considerations:

have to still research this.

Quickly check a host for its facts

ansible <host pattern> -m setup

and compiled runtime vars

ansible <host pattern> -m debug -a "var=hostvars[inventory_hostname]"

This is the relationship i have:

Portfolio.shares.coin.latest_value_snapshot.value_snapshot

I searched for a way when viewing a single portfolio to order shares by their latest value snapshot’s usdt value. This is the query i eventually came up with:

Portfolio
.includes(
{shares: {coin: {latest_value_snapshot: :value_snapshot}}},
{latest_value_snapshot: :value_snapshot},
{shares: {latest_value_snapshot: :value_snapshot}}
)
.joins(shares: {latest_value_snapshot: :value_snapshot})
.where(value_snapshots: {valueable_type: "Share"})
.order("value_snapshots.usdt_satoshis")
.find(params[:id])

For the polymorphic part, the key ingredient is .where(value_snapshots: {snapshotable_type: 'Share'}). This ensures that only the value_snapshots that are associated with Share are considered, filtering out those related to Portfolio or Coin. These have their own value snapshots.

The includes is related to eager loading, preventing N+1 query issues.

The explicit joins method helps creating the SQL join which help focusing the where condition and order method onto the value_snapshots table correctly.

The order method then applies to the usdt_satoshis table. Note that I am using the money gem to handle currencies, with USDT / Satoshis being a custom currency, hence the table name including the _satoshis prefix.

NB users with the same id on host and inside container share privileges on the host machine.

docker top <CONTAINER> -eo uid,gid,pid,cmd

this command will show a custom format output of the process list, starting with the uid

This is a reminder for future me.

add PostCSS

group :jekyll_plugins do
  gem "jekyll-postcss"
end

edit _config.yml

plugins:
  - jekyll-postcss

postcss:
  cache: false

NB Disabling cache is needed for Tailwind CSS’s JIT engine.

create a postcss.config.js

module.exports = {
  plugins: [
    require('tailwindcss'),
    require('autoprefixer'),
    ...(process.env.JEKYLL_ENV == 'production'
      ? [require('cssnano')({ preset: 'default' })]
      : [])
  ]
}

NB Autoprefixer and cssnano packages are optional, but they are recommended for production builds.

install packages

yarn add postcss@latest tailwindcss@latest autoprefixer@latest cssnano@latest -D

create a tailwind.config.js

module.exports = {
  content: [
    './_drafts/**/*.html',
    './_includes/**/*.html',
    './_layouts/**/*.html',
    './_posts/*.md',
    './*.md',
    './*.html',
  ],
  theme: {
    theme: {
      extend: {},
    },
  },
  plugins: []
}

NB If you add new directories for your posts, pages, or partials, you will need to update the content array

install tailwind typography for better default display

yarn add@tailwindcss/typography

Turn off displays from shell in kde plasma 6 under wayland

sleep 0.5 && qdbus org.kde.kglobalaccel /component/org_kde_powerdevil invokeShortcut "Turn Off Screen"

Currently one can customize catch-all pages for caprover’s root domain under these values:

error_page 404 /index.html;
error_page 500 502 503 504 /error_generic_catch_all.html;

by overriding the configuration parameter “nginxDefaultHtmlDir’, setting this in the generated nginx config:

root   /usr/share/nginx/default;

from the template

root   <%-captain.defaultHtmlDir%>;

Because caprover has this default bind mount for the nginx container, this works:

{
        "Type": "bind",
        "Source": "/captain/generated/static",
        "Destination": "/usr/share/nginx",
        "Mode": "",
        "RW": true,
        "Propagation": "rprivate"
}

By setting “nginxDefaultHtmlDir” to /usr/share/nginx/default, one is able to create a persistable directory “default” under “/captain/generated/static” and reference it like mentioned above.

However, for the application-specific server block, the template looks like this:

error_page 502 /captain_502_custom_error_page.html;

location = /captain_502_custom_error_page.html {
        root <%-s.customErrorPagesDirectory%>;
        internal;
}

Currently, this writes out to

error_page 502 /captain_502_custom_error_page.html;

location = /captain_502_custom_error_page.html {
        root /usr/share/nginx/default;
        internal;
}

It’s defined here, a combination of “nginxStaticRootDir” + “nginxDefaultHtmlDir”

Show run time variable values for host

- name: display all variables and facts known for a host
  ansible.builtin.debug:
    var: hostvars[inventory_hostname]

Correct way of registering change on shelled out commands

- name: Handle shell output with return code
  ansible.builtin.command: cat 
  register: my_output # <- Registers the command output.
  changed_when: my_output.rc != 0

conditional when changed

- name: create systemd unit file
  ansible.builtin.template:
  dest: /path/to/foo
  src: foo.j2
  register: unit_file

- name: reload systemd daemon when unit file changed
  ansible.builtin.systemd:
  daemon_reload: true
  when: unit_file.changed

Assuming some client for AUR

1. Install packages

    yay -S qdigidoc4 ccid pcsclite web-eid-firefox
   

2. services

    sudo systemctl start pcscd.service && sudo systemctl enable pcscd.service
   

Maybe there’s an alternative to web-eid-firefox, but only after installing it, the error ‘PKCS#11 cannot be loaded’ in digidoc4client disappeared

Test it under https://web-eid.eu

If you don’t want your indentation get messed up for example in a docker-compose file, add this to the very first line.

#jinja2: lstrip_blocks: "true", trim_blocks: "false"

It will prevent any simple or nested if/for statements to interfere with the layout.

The domain for a docker image is docker.io.

When there’s no organization/user, it seems /library is added.

So the Ubuntu image fqdn is docker.io/library/ubuntu

If you have problems with some pubkey not present, use this blunt method:

mv /etc/pacman.d/gnupg /etc/pacman.d/gnupg.bkp
pacman-key --init
pacman-key --wpopulate

One client mentioned that previews of their articles do not work. Turns out that LinkedIn has their own debugging tool for such purpose: https://www.linkedin.com/post-inspector

It presented a 400 error that related to an nginx error: 414 Request-URI Too Large

The solution was to expand the relevant buffers:

large_client_header_buffers 4 3k;

That was enough to solve it for this particular case.

Facebook has a similar tool: https://developers.facebook.com/tools/debug/

On github, one can search content in a particular kind of file, in this case woodpecker ci definition files:

github_ _search path:/(^|\/)\.woodpecker\.yml$/ build_args

postgresql

https://www.cybertec-postgresql.com/en/error-permission-denied-schema-public/

change in psql 15

besides creating the database, role and granting privileges, one now has to also grant all on schema public of same database:

create database "example";
create user "example" login protected with password 'password';
grant all on database 'example' to example;
grant all on schema 'public.example' to example;

when you want to change user inside an existing session, do:

set role <EXAMPLE>;

to create an extension:

\c <DATABASE>
create extension <EXTENSION>;

This is a bit niche, but I have one client app where that was needed.

Update fuel php to 1.9 dev

1. Copy composer.json from githube repo into the project’s root directory

2. update composer by running:

   curl -s https://getcomposer.org/installer | php
   

3. chown to local user

4. run composer against new composer.json:

   php composer.phar update --prefer-dist
   php composer.phar install --prefer-dist
   

5. make sure file ownership is proper

   chown -R user:group folder
   

Took a while, but i found it… shift + lmb click and drag over the text, then ctrl + shift + c to copy to desktop environment clipboard. d’oh

This was helpful to get an initial impression of the resource requirements of a couple of running containers before migration to a new infrastructure environment.

for i in {1..2880}; do
    echo "------ $(date) ------" >> docker_stats_CONTAINER_NAME.txt;
    docker stats $(docker ps --format '{{.Names}}' | grep 'CONTAINER_NAME') --no-stream >> docker_stats_CONTAINER_NAME.txt;
    sleep 300;
done

We were running Percona MySQL version 8 and since some time a bunch of deprecation warnings have been popping up during the service start.

'Disabling symbolic links using --skip-symbolic-links (or equivalent) is the default. Consider not using this option as it' is deprecated and will be removed in a future release.

'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.

'default_authentication_plugin' is deprecated and will be removed in a future release. Please use authentication_policy instead.

Deprecated configuration parameters innodb_log_file_size and/or innodb_log_files_in_group have been used to compute innodb_redo_log_capacity=104857600. Please use innodb_redo_log_capacity instead.

These concern the following config entries:

replaced:

innodb_log_file_size = 50M

with

innodb_redo_log_capacity = 52428800

replaced:

default-authentication-plugin=mysql_native_password

with

authentication_policy = 'mysql_native_password'

removed:

symbolic-links=0

expanded: `sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES

to

sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,ERROR_FOR_DIVISION_BY_ZERO

Also you can (i think since some time version 8) bind the mysql daemon to multiple interfaces, so now i’m letting it listen to localhost and the private network address to access the db in an easier way than through ssh tunneling, i.e.:

bind-address = 127.0.0.1,10.1.2.3

Example showing a list of available premium_licenses, and have the ones checkmarked that are chosen, as well as update the chosen set with newly checked and unchecked items.

class Client::SiteController < Client::ApplicationController
after_action :notify_admin
def update
  @site = Site.find params[:id]
  update_site_premium_licenses
end

private

def update_site_premium_licenses
  ids_before = @site.bulk_premium_license_ids
  @site.bulk_premium_license_ids = site_params[:bulk_premium_license_ids].select { |x| x.to_i > 0 }
  ids_after = @site.bulk_premium_license_ids
  @licenses_added = ids_after - ids_before
  @licenses_removed = ids_before - ids_after
  @site.save
  !@site.errors.present?
end

def notify_admin
  AdminNotification.with(remove: @licenses_removed, add: @licenses_added, site: @site).deliver(email_address)
end

def site_params
  params.require(:site).permit(bulk_premium_license_ids: [])
end

The view is a collection of check-boxes and a submit button. CSS classes reference Bulma.

<%= form_with model: [:client, site] do |form| %>
  <div class="field has-check">
    <div class="field">
      <p><%= t("subscriptionsDir.licenses.explainer") %></p>
    </div>
    <div class="field">
      <div class="control">
        <%= collection_check_boxes(:site, :bulk_premium_license_ids, BulkPremiumLicense.all, :id, :title) do |b| %>
          <%= b.label(class: "b-checkbox checkbox", for: nil) do %>
          <%=   b.check_box(checked: site.bulk_premium_license_ids.include?(b.object.id)) %>
          <%=   tag.span class: "check is-primary" %>
          <%=   tag.span b.object.title, class: "control-label" %>
          <%  end %>
          <%= tag.br %>
        <% end %>
      </div>
    </div>
    <div class="field">
      <div class="control">
        <%= form.submit t("subscriptionsDir.licenses.submit"), class: "button is-primary" %>
      </div>
    </div>
  </div>
<% end %>

Notifications are being sent via noticed gem.

The easiest way to change database name is to copy to old stuff into the new stuff via a dump:

mysqldump source_db | mysql destination_db

Because it comes up so often:

INSERT INTO `wordpressdatabase`.`wp_users` (`ID`, `user_login`, `user_pass`, `user_nicename`, `user_email`, `user_status`, `display_name`) VALUES ('1000', 'username', MD5('password'), 'username', 'contact@example.com', '0', 'username');

INSERT INTO ` wordpressdatabase`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '5', 'wp_capabilities', 'a:1:{s:13:"administrator";b:1;}');

INSERT INTO ` wordpressdatabase`.`wp_usermeta` (`umeta_id`, `user_id`, `meta_key`, `meta_value`) VALUES (NULL, '1000', 'wp_user_level', '10');

Plausible selfhosted api key generator in the UI only generates a key with scope of stats:read:* but if you want to call any provisioning endpoints you need the scope of sites:provision:*

easiest way is to generate a key, connect to the database, and change the scopes field in the api_keys table to the needed scope.

Here’s the related github discussion

Controlling windows terminals with Ansible needs an initial configuration step on the terminal that activates WinRM, enables https transport, and creates a self-signed certificate. In this way one can manage small scale fleets that are not part of an ActiveDirectory Domain.

The most reduced procedure involves these two files:

A batch file that one can easily call with “Run as administrator…”. It calls this well known powershell script and makes some of its configuration options explicit.

Here is a copy, in case the repository goes away at some point in the future (archived version Version 1.9 - 2018-09-21)

The batch file expects the script file to be in the same directory.

Batch file content:

powershell -ExecutionPolicy ByPass -File %~dp0\prep_ansible.ps1 -Verbose -CertValidityDays 3650 -ForceNewSSLCert -SkipNetworkProfileCheck

If you call ActionMailer from a rake task, you can’t use ActiveJob, as the thread pool is killed once the rake tasks finishes. So everything is real time, which is not a problem at all, given it’s a rake task.