Swap Y and Z keys on US keyboard in MacOS

I work most of my day-to-day time on MacOS these days and one thing I am struggling with is slightly different keyboard layout when it comes to dead keys (AltGr combinations). I use Czech keyboard layout for most of my career even for programming. These combinations are different on MacOS. I am able to probably learn back to US keyboard layout, as I was using it previously and it should not be a problem. Since almost all my text typing is in English anyway, there are some advantages to that like havin’ single quote much more accessible.

One thing, however, I cannot work with is Y and Z keys swapped. See, Czech layout uses QWERTZ whereas on US layout it’s QWERTY. Problem can be easily solved, I setup both US and Czech QWERTY layouts in the operating system and then I can swap those two keys on the hardware (USB) level. It is as easy as running the following command in the Terminal:

hidutil property --set '{"UserKeyMapping": [{"HIDKeyboardModifierMappingSrc":0x70000001D, "HIDKeyboardModifierMappingDst":0x70000001C},{"HIDKeyboardModifierMappingSrc":0x70000001C, "HIDKeyboardModifierMappingDst":0x70000001D}]}'

Keep in mind that the keys are swapped on the hardware level, so if you use other keyboard layouts which have both QWERTZ and QWERTY, you might get into issues with keys being swapped in the other layout. If you use just a single layout or all your layouts are the same QWERTX type, it’s not an issue at all. To revert this swap back at any point, just do the following command:

hidutil property --set '{"UserKeyMapping": [{}]}'

To make this change permanent, make sure the first command is executed after MacOS startup. One way to do that would be something like:

cat << EOF | sudo tee -a /Library/LaunchDaemons/org.custom.keyboard-remap.plist
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
  <dict>
    <key>Label</key>
    <string>org.custom.keyboard-remap</string>
    <key>ProgramArguments</key>
    <array>
      <string>/usr/bin/hidutil</string>
      <string>property</string>
      <string>--set</string>
      <string>{"UserKeyMapping": [{"HIDKeyboardModifierMappingSrc":0x70000001D, "HIDKeyboardModifierMappingDst":0x70000001C},{"HIDKeyboardModifierMappingSrc":0x70000001C, "HIDKeyboardModifierMappingDst":0x70000001D}]}</string>
    </array>
    <key>RunAtLoad</key>
    <true/>
    <key>KeepAlive</key>
    <false/>
  </dict>
</plist>
EOF
sudo launchctl load -w /Library/LaunchDaemons/org.custom.keyboard-remap.plist

That’s all for now!

Read on

29 November 2021 | macos

Fedora Silverblue: not only for your grandma

I have migrated my grandparents to Fedora Silverblue, previously they used CentOS. I was impressed how everything worked well and I like where Fedora is going overall. Less pre-installed software, I am hoping for more packages to be dropped - Evolution backend, on-line accounts, Maps and others. Overall, it works great.

Setting up Fedora Silverblue was easy, installation smooth. Then I had to ensure screen does not lock (they hate this):

gsettings set org.gnome.desktop.screensaver lock-enabled false

Timezone was not right, I had to misclick during installation:

timedatectl set-timezone Europe/Prague

I have found out that the default policy is not to automaticaly update OS without askint, what I want is auto updating without any question because they would never confirm it:

grep Update /etc/rpm-ostreed.conf
AutomaticUpdatePolicy=stage

This configuration should stage updates for automatic installation (I mean boot). This finishes the configuration:

rpm-ostree reload
systemctl enable rpm-ostreed-automatic.timer --now

Now, I want to access those workstation via ssh, but my parents are stuck behind home firewalls. I came up with a solution - reverse ssh tunelling. I enabled ssh, generated keys and connected to my server:

systemctl enable --now sshd
ssh-keygen
ssh xssh@xxx.zapletalovi.com

A new user service will be started to create the tunnel:

mkdir -p ~/.config/systemd/user

I am forwarding both ports 22 and 5900:

cat ~/.config/systemd/user/reverse-ssh.service
[Unit]
Description=Reverse SSH connection
After=network.target
[Service]
Type=simple
ExecStart=/usr/bin/ssh -g -N -T -o "ServerAliveInterval 10" -o "ExitOnForwardFailure yes" -R 40122:localhost:22 -R 40159:localhost:5900 xssh@xxx.zapletalovi.com
Restart=always
RestartSec=10m
[Install]
WantedBy=default.target

To enable the service:

systemctl --user daemon-reload
systemctl --user enable --now reverse-ssh

Unfortunately, I was not able to get VNC running. After I enabled Remote Desktop in Gnome, I could not find any VNC client that would work. The best I could achieve was a frozen screen when connected on my LAN. Maybe I will find one later on, there are some developments in Fedora 35.

That should do it, ideal OS for elders - no reboots into dnf, no viruses, just internet and a printer. Upgrading to major Fedora releases should be also easy and I can do it via ssh:

ostree remote refs fedora
rpm-ostree rebase fedora:fedora/XX/x86_64/silverblue

To revert to the previus version, simply pick the previous boot entry and perform rpm-ostree rollback. Which reminds me - Fedora 35 is out, I should probably upgrade them now.

Read on

11 November 2021 | linux | fedora

A sane vim configuration for Fedora

When you start Vim with the --clean option, it shows up in “vanilla” mode. No plugins, no configuration, just back to the roots. I have collected a ton of configuration statements over the years some of them dating from MS-DOS or Windows 3.1. Here is the deal: I am going to start from scratch to find a good starting-point configuration with just plugins which are available in Fedora 35. Will I survive a week of coding? Let’s find out!

Let’s set the rules: Minimum possible configuration statements and only plugins which ship with Fedora 35+. By the way, if you are not Fedora user, continue reading. You can always install these plugins from your OS package manager, manually or via a Vim plugin manager.

Before we start, there’s the elephant in the room: Vim or Neovim (fork of Vim) question. Well, this is up to you, everything that is in this post should work for both, however, I only tested with Vim. All the skill will come handy when you logon to a server where only vi is available. It can be either an old UNIX system, Linux server with minimum software installed for better security, an interative shell in a container or an embedded system where space is precious.

Without further ado, here is what I distilled to the absolute bare minimum to be effective with Vim for coding:

# dnf install --allowerasing vim-default-editor \
	vim-enhanced \
	vim-ctrlp \
	vim-airline \
	vim-trailing-whitespace \
	vim-fugitive \
	vim-ale \
	ctags

Do not worry about the --allowerasing option, just review the installation transaction prior confirming. This option is there to tell the package manager to replace existing package nano-default-editor with vim-default-editor. It is a small package that drops a shell configuration files to set EDITOR environment variable to vim and this is a must have if you want to use Vim (e.g. with git). This is a special thing for Fedora, you will not need to do this on other distributions or OSes - just make sure your EDITOR shell variable is correctly set.

A quick overview what I consider a good and clean plugin set:

ctrlp - smallest possible fuzzy-finder plugin (pure vimscript)
fugitive - a must have tool for git
trailing-whitespace - shows and fixes, well, trailing whitespace
airline - an improved status line (pure vimscript)
ale - highlights typos or syntax errors as you type
ctags - not a Vim plugin but a very much needed tool

There are other fuzzy-finder plugins like Command-T or my faviourite (very fast) fzf.vim. Thing is, fzf.vim is not in Fedora and I want the smallest possible configuration. CtrlP will do just fine and it is much more easier to configure as it requires nothing.

If I were to choose absolute minimum configuration it would be:

# cat ~/.vimrc
let mapleader=","
let maplocalleader="_"
filetype plugin indent on
let g:ctrlp_map = '<leader><leader>'
let g:ctrlp_user_command = ['.git/', 'git --git-dir=%s/.git ls-files -oc --exclude-standard']
set exrc
set secure

But that is probably too extreme, so here is slightly bigger configuration with my detail explanation below:

" vim: nowrap sw=2 sts=2 ts=2 et:

" leaders
let mapleader=","
let maplocalleader="_"

" filetype and intent
filetype plugin indent on

" incompatible plugins
if has('syntax') && has('eval')
  packadd! matchit
end

" be SSD friendly (can be dangerous!)
"set directory=/tmp

" move backups away from projects
set backupdir=~/.vimbackup

" fuzzy searching
let g:ctrlp_map = '<leader><leader>'
let g:ctrlp_user_command = ['.git/', 'git --git-dir=%s/.git ls-files -oc --exclude-standard']
nnoremap <leader>b :CtrlPBuffer<cr>
nnoremap <leader>t :CtrlPTag<cr>
nnoremap <leader>f :CtrlPBufTag<cr>
nnoremap <leader>q :CtrlPQuickfix<cr>
nnoremap <leader>m :CtrlPMRU<cr>

" buffers and quickfix
function! ToggleQuickFix()
  if empty(filter(getwininfo(), 'v:val.quickfix'))
    copen
  else
    cclose
  endif
endfunction
nnoremap <leader>w :call ToggleQuickFix()<cr>
nnoremap <leader>d :bd<cr>

" searching ang grepping
nnoremap <leader>g :copen<cr>:Ggrep! <SPACE>
nnoremap K :Ggrep "\b<C-R><C-W>\b"<cr>:cw<cr>
nnoremap <leader>s :set hlsearch! hlsearch?<cr>

" ctags generation
nnoremap <leader>c :!ctags -R .<cr><cr>

" per-project configs
set exrc
set secure

I like having my leader key mapped to comma instead of the default backslash. It is the closest free key in Vim when your hands are in writing position. Also this key is same in most keyboard layouts while \ varies per model or layout. I rarely use local leader but underscore looks like a good fit.

Further reading:

:help map-which-keys: https://vimhelp.org/map.txt.html#map-which-keys
https://vim.fandom.com/wiki/Unused_keys

Next up it is the very important filetype command. See, Vim comes with “batteries included”, version 8.2 contains syntax highlighting for 644 languagues, 251 filetype definitions (ftplugins) and intentation rules for 138 languages. However, indentation is not enabled by default perhaps to deliver a consistent editing experience for all. I like to enable it.

A quick tip: If you are editing a very large file and Vim feels slow, you may want to disable syntax highlighting to speed things up. Just type :syn off command.

Further reading:

:help filetype: https://vimhelp.org/filetype.txt.html
:help syntax: https://vimhelp.org/syntax.txt.html
:help indent: https://vimhelp.org/indent.txt.html

Vim even comes with some extra plugins which makes some feature incompatible, one of these is quite useful. It is the matchit plugin which makes % key which finds matching paren to work with some languages. Typically, you can find beginning or end of a block (begin and end) or HTML matching tags and similar.

Further reading:

:help matchit: https://vimhelp.org/usr_05.txt.html#matchit-install

One of the many settings I want to keep from my old config is using /tmp for swap and creating backups in a separate directory in my home which you need to create with mkdir ~/.vimbackup. Now, it is important to understand that Vim creates a copy called “swap file” when you start editing and all the unsaved work is saved in this file. So even if there is a power outage, your swap will contain most of the unsaved work. I prefer using tmpfs as all my laptops and servers are protected with UPS and I am used to save quite often. Also, most of the times you will utilize swap files when your ssh connection is lost rather than thank to a power outage. Swap files can be quite big for large files and I value my SSD wear so I am making the decision here, if you are unsure remove this statement to use /var/tmp which is safer.

Further reading:

:help swap-file: https://vimhelp.org/recover.txt.html#swap-file

Now, the fuzzy finder is a plugin I cannot live without. Opening files via commands like :Ex or :e or :tabe is okayish on a server when you need to open like 20 files a day. When coding, we usually need to open hundreds of them. As I said, CtrlP does the job nicely, it is small, no dependencies, pure Vim. It opens with Ctrl-P combination which is a bit weird to me, I know that some famous editors use it (VSCode I think). Thing is, these are already important Vim keybindings I do not want to override. So the winner for me is leader+leader (comma pressed twice).

The ctrlp_user_command just changes how CtrlP is getting the file list, instead the build-in recursive file lister (glob) it uses git ls-files which is usually better as it ignores things from .gitignore so things like node_modules or other irrelevant directories which can slow down the listing are not in the way.

Leader+b/t/f/q/m to open list of buffers, tags, tags from current file, quick fix buffer and most recently used files is very useful too. Specifically, once you generated a taglist with ctags, this is essentially “Go To Definition” for hundreds of programming languages - no plugins needed! This is all built-in Vim. Now to put thigs straigt, when I type leader+b it means pressing comma and then pressing b key, not together like with Control or Shift.

Further reading:

:help Explore: https://vimhelp.org/pi_netrw.txt.html#netrw-explore
https://github.com/kien/ctrlp.vim

Although Vim supports tabs these days, buffer management is an important skill for mastering Vim, what I usually end up with is having too many buffers and I need to do :bdelete way too often. Well, leader+d seems like a good option to do that faster. I also like to be able to close quickfix window so there is the leader+q combination for that too. I use this very often when browsing search results.

Further reading:

:help buffer-hidden: https://vimhelp.org/windows.txt.html#buffer-hidden

Speaking about searching, it is as important as opening files. I want to be able grep the codebase, for that there is the awesome :Ggrep command from fugitive plugin which uses git grep which by design ignores junk files and only searches what’s in git. Since Shift-K is a free key in Vim, it is a great fit for automatically grepping the term under cursor. And finally, being able to enter arbitrary search pattern via leader+g is also nice. Note this opens a window which is called Quickfix window where you can navigate the results, go to next occurance, previous, last, first and more. The same window is used for output from compilators or other tools so get familiar with it. I suggest further reading in the documentation if this is new to you.

Further reading:

:help quickfix: https://vimhelp.org/quickfix.txt.html
https://github.com/tpope/vim-fugitive

By the way, searching via / key is smart sensitive, meaning if all characters are lower case, Vim searches ignoring case. By default it highlights results and I think I typed :noh (turn of highlighting) about a million times, that’s why I have leader+s to toggle this. I suggest to read more about searching in the manual later on too.

Searching and grepping is next. The fugitive plugin has you covered. Use the command :Ggrep pattern to do a git grep, results will go into the Quickfix window. Then simply navigate through the results via quick fix commands (:cn, :cp etc) or simply use :CtrlPQuickfix (or leader+q) to scroll them visually. What is cool about the CtrlP quick fix integration is you can further search the results just by typing to match filenames or content as well, if it makes sense. Searching the results of a search.

Further reading:

:help grep: https://vimhelp.org/quickfix.txt.html#grep
:help noh: https://vimhelp.org/pattern.txt.html#noh
https://github.com/tpope/vim-fugitive

Leader+c to generate ctags file for better navigation is useful when I am dealing with a new codebase or doing some longer coding session with lots of jumps around. Ctags supports hundreds of languages and Vim can use all this knowledge to navigate it. More about how to configure it later. Note we already discussed leader+t to open fuzzy search for all tags, remember? It is the very same thing.

Further reading:

:help ctags: https://vimhelp.org/tagsrch.txt.html
https://ctags.io

Being able to override any other setting in projects by creating .vimrc file in a project directory is a good idea to do. Just put it in the (global) .gitignore to make sure you don’t need to edit thousands of git ignore files in each project. Such a project .vimrc could be something like (for C/C++ project with GNU Makefile):

" coding style
set tabstop=4
set softtabstop=4
set shiftwidth=4
set noexpandtab
" include and autocomplete path
let &path.="/usr/local/include"
" function keys to build and run the project
nnoremap <F9> :wall!<cr>:make!<cr><cr>
nnoremap <F10> :!LD_LIBRARY_PATH=/usr/local/lib ./project<cr><cr>

As you can see, I typically map F2-F10 keys to compile, run, test and similar actions. Using F9 for calling make sounds about right, remember the blue Borland IDE from MS-DOS?

As mentioned earlier, is a good idea to ignore both .vimrc and tags (generated by ctags) globally so there is no need to update every each .gitignore:

# git config --global core.excludesfile ~/.gitignore
# cat ~/.gitignore
/.vimrc
/tags
/TAGS

There are actually few more statements in my personal config which are only relevant for those with non-US keyboard layouts (I am on Czech). I need to use dead keys for many characters and it is simply not possible and I’d rather type the command instead of doing those hard-to-reach combinations. Here is a solution to the problem:

" CTRL-] is hard on my keyboard layout
map <C-K> <C-]>
" CTRL-^ is hard on my keyboard layout
nnoremap <F1> :b#<cr>
nnoremap <F2> :bp<cr>
nnoremap <F3> :bn<cr>
" I hate entering Ex mode by accient
map Q <Nop>

Further reading:

:help map: https://vimhelp.org/map.txt.html

Function keys are all free in Vim, except F1 which is bound to help. I don’t need help, not that I would already know everything about Vim. Not at all. But I can simply type :help if needed. And F1 is cruical key, so close to the Esc key. I like to use buffer swapping (:b#) for that as well as F2-F3 for next/previous. The more you work with buffers the more you will need this. If you haven’t used Ctrl-^ I suggest to get used to it. Oh, have you ever entered the Ex mode with the ugly type :visual? Many begginners had no idea how to quit Vim from that mode, for me it is just disturbing as I rarely use it.

Now, getting familiar with ctags is a key thing to be successful with Vim. This tool supports hundreds of languages and it can easily create tags for files you do not want to create, therefore I suggest to ignore typical junk directories:

# cat ~/.ctags.d/local.ctags
--recurse=yes
--exclude=.git
--exclude=build/
--exclude=.svn
--exclude=vendor/*
--exclude=node_modules/*
--exclude=public/webpack/*
--exclude=db/*
--exclude=log/*
--exclude=test/*
--exclude=tests/*
--exclude=\*.min.\*
--exclude=\*.swp
--exclude=\*.bak
--exclude=\*.pyc
--exclude=\*.class
--exclude=\*.cache

I must not forget about vim-airline plugin, out of the two in Fedora this one is light, no external dependencies are needed and it works out of box with all my fonts. You can customize it, there are themes and such things. I just happen to like the default setting.

One thing I must mention is that there are two main ctag projects, Exuberant Ctags and Universal Ctags. The latter is a more modern fork, if your distribution have it, use that. If you are on Fedora 35+ all you need to know that you are now on Universal Ctags.

Before I wrap it up, here is what I suggest. Try to keep your Vim configuration slick and clean. It will pay off in the future. After I switched, I had to re-learn “write and quit” command because I was typing it as :Wq accidentaly all the time and I had a “hack” in the old configuration that actually did what I meant. Okay, this one might be actually useful and make the cut, I hope you get what I mean:

:command Wq wq
:command WQ wq

Here is a final quick tip - you may need to change your default Vim configuration a lot while finding the sweetspot of what I presented you here and your own taste. Use the following alias so you don’t need to search the history all the time. Trust me, when a Vim user searches history for “vim”, nothing is relevant:

alias vim-vimrc='vim ~/.vimrc'

There you have it, maybe this can help you navigating through the rich world of Vim without ton of plugins. “Vanilla” Vim is fun!

To try out what you just read, install the packages and check out the config:

test -f ~/.vimrc && mv ~/.vimrc ~/.vimrc.backup
curl -s https://raw.githubusercontent.com/lzap/vim-lzap/master/.vimrc -o ~/.vimrc
mkdir ~/.vimbackup

Special thanks to Marc Deop and Melanie Corr for reviewing my article.

Read on

11 November 2021 | linux | fedora

Denormalizing PostgreSQL join tables

Foreman OpenSCAP plugin stores security scanner results in Foreman’s PostgreSQL database providing integrated UI, API and CLI experience. To simplify our use case, let’s assume that each report has many-to-many association to security rules with some result (pass or fail). This gives us two main SQL tables: report and rule and a join table between them. For simplicity, let’s ignore the result which should be an extra column in the join table.

Just for the record, such l report looks like this when presented in UI or CLI:

Report for host.example.com performed on 2021-01-01 12:30:
--
Minimum password length set to 10 characters: PASS
RPM database is valid: FAIL
Audit daemon is enabled and running: PASS
Mount /home has nodev option: PASS
...

In typical workflow a report is created, rules associated through join table and it is all kept for some time (from weeks to years depending on user preference) until a cron job deletes old reports. Users search for reports by report date and time, by total number of passing or failed rules, by rule names and rule results. Now, the current design using normal form with the join table is extremely slow when amount of records in the join table goes into higher counts, almost all operations are slow - inserting, selecting, and, believe it or not, deleting is huge pain. When database is under heavy load and more and more new reports are being inserted, postgres needs to lock the table when deleting records and since the deletion process is also slow, users experience transaction errors.

There must be more efficient way of storing reports, perhaps avoiding the join table alltogether. The use case is heavy on data insert and delete (thousands of hosts can upload their reports per hour) while searching is not performed very often (few dozens of searches per day typically). My very first idea was to store rules in a text column and searching could be performed as a regular expression table scan. I implemented the first prototype, but then I stumbled upon array data type and GIN index with intarray extension.

It is simple, report ids can be stored in a multiple array columns: passing_rules and failed_rules. When presenting report to screen, rules can be easily retrieved and sorted by name or result. Inserts and deletes will be much faster as there is no join table and data should be stored pretty efficiently. But searching is big question I need to test - table scan is probably not an option, however, there is a way to create index on array column.

Let’s simulate the old scenario with the join table by creating the following tables:

rule - rule name and id
report_association - report name and id
report_association_rule - join table between report_association and rule

Typical report has somewhere between 50 and 100 rules and typical Foreman deployment has tens of thousands of hosts with tens of reports kept in the database, until they are deleted. I performed testing with 100000 records and 500 rules associated for each record from pool of 5000 rules total.

# select count(*) from rule;
 count
-------
  5000
(1 row)

# select count(*) from report_association;
 count
--------
 100000
(1 row)

# select count(*) from report_association_rule;
  count
----------
 50000000
(1 row)

This gives us a good starting point - 50 million records in join table. I know there are users with more records, but this should give us rough idea and keep testing reasonably fast on my hardware. Now, let’s create a second table which will hold reports and rules in an array column:

report_array - report name, id and int arrays rules_fail, rules_pass

Let’s create a new database and do some DDL statements:

create table rule (id serial primary key, name varchar not null);
create table report_association (id serial primary key, name varchar not null);

create table report_association_rule (
  report_id int references report_association(id) on update cascade on delete cascade,
  rule_id int references rule(id) on update cascade on delete cascade
);

create table report_array (id serial primary key, name varchar not null, rules_fail int[], rules_pass int[]);

Time to load some data. For names let’s use MD5 hexstring of primary key just to have something to show. Create rules and reports:

insert into rule(name) select md5(generate_series::text) from generate_series(1, 5000);
insert into report_association(name) select md5(generate_series::text) from generate_series(1, 100000);

Now, let’s create associations for the join table and at the same time, insert records into the table which stores associations in arrays. This will ensure both queries are giving exactly same results. The following block will pick 500 rules (or less as duplicite values are eliminated) from the rule table randomly and then insert records into the join table and then into the report_array table as well:

do $$
declare
  rules int[];
  report_id int;
  rule_id int;
begin
  for report_id in 1..100000 loop
    rules := array_agg(distinct round(random() * (5000 - 1)) + 1) from generate_series (1, 500);
    -- association
    foreach rule_id in array rules loop
      insert into report_association_rule(report_id, rule_id) values (report_id, rule_id);
    end loop;
    -- array column
    insert into report_array(name, rules_fail, rules_pass) values (md5(report_id::text), rules, rules);
  end loop;
end; $$;

Creating records in a loop ensures that associations are the same for both join table and array columns. Before we create any indices, let’s try table scan across all reports finding rule with id 747. I executed every query several times so data could be loaded into memory and cache and picked a typical (read “average”) result. First off, via join table:

explain analyze select report_association.name from report_association inner join report_association_rule on report_association.id = report_association_rule.report_id where report_association_rule.rule_id = 747;
                                                                         QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.29..463425.99 rows=9484 width=33) (actual time=0.305..1609.657 rows=9510 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Nested Loop  (cost=0.29..461477.59 rows=3952 width=33) (actual time=0.205..1597.797 rows=3170 loops=3)
         ->  Parallel Seq Scan on report_association_rule  (cost=0.00..458402.31 rows=3952 width=4) (actual time=0.176..1580.169 rows=3170 loops=3)
               Filter: (rule_id = 747)
               Rows Removed by Filter: 15858843
         ->  Index Scan using report_association_pkey on report_association  (cost=0.29..0.78 rows=1 width=37) (actual time=0.004..0.004 rows=1 loops=9510)
               Index Cond: (id = report_association_rule.report_id)
 Planning Time: 0.193 ms
 Execution Time: 1610.327 ms

Now via array column:

explain analyze select report_array.name from report_array where report_array.rules_fail @> '{747}';
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Seq Scan on report_array  (cost=0.00..24725.00 rows=500 width=33) (actual time=0.045..865.309 rows=9510 loops=1)
   Filter: (rules_fail @> '{747}'::integer[])
   Rows Removed by Filter: 90490
 Planning Time: 0.094 ms
 Execution Time: 866.616 ms

Array is already two times faster via table scan. Both queries returned 9510 results, to confirm they are processing the same data. Let’s create indices, first the join table:

create index ix_report_association_rule_report_id on report_association_rule(report_id);
create index ix_report_association_rule_rule_id on report_association_rule(rule_id);

Now, let’s create index for array to speed up searching by rule id. There are couple of options in PostgreSQL 12:

GIN with default operators
GIN with operators from intarray extension
GiST with operators for small arrays from intarray extension
GiST with operators for large arrays from intarray extension

The intarray extension provides functions, operators and optimized index for integer (int4 only) arrays. However, I was not able to see any difference for this use case. I asked on PostgreSQL IRC channel and guys told me that intarray could show its potential for very large datasets (larger arrays than hundreds of elements) or more complex queries.

I tried both GIN and GiST types and GiST index was smaller, faster for updates but slower for search. Since GIN index type is recommended for arrays and GIN without intarray extension was giving me exactly the same results, I have decided to use just plain GIN index type. Feel free to test intarray extension and compare results to plain GIN index and let me know if you are able to see any difference. In that case, load the extension and create indices using opclasses:

-- DO NOT do this unless you want to compare intarray performance
create extension intarray;
create index ix_report_array_rules_pass on report_array using gist(rules_pass gist__intbig_ops);
create index ix_report_array_rules_fail on report_array using gin(rules_fail gin__int_ops);

So let’s create GIN indices on both passed and failed rules. No extension is required, this is also possible to do in Ruby on Rails without writing any SQL statements:

create index ix_report_array_rules_pass on report_array using gin(rules_pass);
create index ix_report_array_rules_fail on report_array using gin(rules_fail);

Before we do any select statements, let’s take a moment and see how much data is used. First, just data:

select pg_size_pretty(pg_table_size('report_association') + pg_table_size('report_association_rule'));
 pg_size_pretty
----------------
 1652 MB

select pg_size_pretty(pg_table_size('report_array'));
 pg_size_pretty
----------------
 395 MB

It is no surprise that data stored in arrays are more compact, after all it is just a single table versus two tables. Let’s count size of indexes:

select pg_size_pretty(pg_indexes_size('report_association') + pg_indexes_size('report_association_rule'));
 pg_size_pretty
----------------
 2044 MB

select pg_size_pretty(pg_indexes_size('report_array'));
 pg_size_pretty
----------------
 345 MB

It looks like GIN indexes on arrays are significantly smaller than index on join table. Just for the record, here are total numbers:

select pg_size_pretty(pg_total_relation_size('report_association') + pg_total_relation_size('report_association_rule'));
 pg_size_pretty
----------------
 3696 MB

select pg_size_pretty(pg_total_relation_size('report_array'));
 pg_size_pretty
----------------
 740 MB

Good, so what we know until now is that array of integers is faster on table scan and both data and indexes are more compact. But more important question to answer is searching with index. Will GIN index outperform B-Tree on a join table? Let’s find out, analysis of the traditional approach via join table:

explain analyze select report_association.name from report_association inner join report_association_rule on report_association.id = report_association_rule.report_id where report_association_rule.rule_id = 747;
                                                                       QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=4048.07..36312.66 rows=9484 width=33) (actual time=38.989..60.094 rows=9510 loops=1)
   Hash Cond: (report_association_rule.report_id = report_association.id)
   ->  Bitmap Heap Scan on report_association_rule  (cost=182.07..31563.76 rows=9484 width=4) (actual time=2.658..12.127 rows=9510 loops=1)
         Recheck Cond: (rule_id = 747)
         Heap Blocks: exact=9510
         ->  Bitmap Index Scan on ix_report_association_rule_rule_id  (cost=0.00..179.69 rows=9484 width=0) (actual time=1.401..1.401 rows=9510 loops=1)
               Index Cond: (rule_id = 747)
   ->  Hash  (cost=1834.00..1834.00 rows=100000 width=37) (actual time=36.153..36.154 rows=100000 loops=1)
         Buckets: 65536  Batches: 4  Memory Usage: 2272kB
         ->  Seq Scan on report_association  (cost=0.00..1834.00 rows=100000 width=37) (actual time=0.010..16.153 rows=100000 loops=1)
 Planning Time: 0.222 ms
 Execution Time: 60.400 ms

Only 60ms, that is a very good result, now arrays with index:

explain analyze select report_array.name from report_array where report_array.rules_fail @> '{747}';
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on report_array  (cost=19.88..1790.49 rows=500 width=33) (actual time=2.705..8.456 rows=9510 loops=1)
   Recheck Cond: (rules_fail @> '{747}'::integer[])
   Heap Blocks: exact=8170
   ->  Bitmap Index Scan on ix_report_array_rules_fail  (cost=0.00..19.75 rows=500 width=0) (actual time=1.625..1.625 rows=9510 loops=1)
         Index Cond: (rules_fail @> '{747}'::integer[])
 Planning Time: 0.067 ms
 Execution Time: 8.729 ms

Only 9ms, that is significantly faster. We almost have a winner, now the most important part: performance of updates (insert). In our use case, the bottleneck is performance of report uploads which tend to slow down once indexes cannot fit into memory. Let’s measure 1000 inserts into the join table, then the same amount of inserts into report table with arrays:

\timing on
do $$
declare
  rules int[];
  report_id int;
  rule_id int;
begin
  for report_id in 1..1000 loop
    rules := array_agg(round(random() * (5000 - 1)) + 1) from generate_series (1, 500);
    foreach rule_id in array rules loop
      insert into report_association_rule(report_id, rule_id) values (report_id, rule_id);
    end loop;
  end loop;
end; $$;
Time: 26055,616 ms (00:26,056)

do $$
declare
  rules int[];
  report_id int;
  rule_id int;
begin
  for report_id in 1..1000 loop
    rules := array_agg(round(random() * (5000 - 1)) + 1) from generate_series (1, 500);
    insert into report_array(name, rules_fail, rules_pass) values (md5(report_id::text), rules, rules);
  end loop;
end; $$;
Time: 2456,793 ms (00:02,457)
\timing off

Inserting 1000*500 records into join table with 50 million of records takes 26 seconds, inserting 1000 records with two arrays with 500 elements into table with 100000 records takes 2.5 seconds. This includes updating of indexes, that is great performance.

In conclusion, it is possible to save significant amount of disk, memory and cpu cycles by denormalizing join tables with integer array column containing primary keys or associated table. It makes sense in scenarios with heavy updates, searching with or without index is also faster. On the other hand, records must be fetched manually and there is no database integrity. This is fine for this specific case (reports kept for record purposes). Also this test was performed with small number of associated records (up to 1:500), your mileage may vary.

Read on

30 August 2021 | linux | fedora | foreman

Finding the right cost for bcrypt/pbkdf2

Foreman uses bcrypt with variable cost as the default password hashing approach, but I learned that bcrypt is not approved for passwords by NIST the other day. Before finishing my patch, I wanted to see what are the sane iteration counts for PBKDF2-HMAC-SHA algorithm which is approved by NIST. Here are results to give you rough estimation from my Intel NUC i3 8th gen running i3-8109U, a CPU from 2018:

       user     system      total        real
PBKDF2 SHA-1 with 1 iters	  0.000024   0.000007   0.000031 (  0.000028)
PBKDF2 SHA-1 with 100001 iters	  0.064736   0.000000   0.064736 (  0.064842)
PBKDF2 SHA-1 with 200001 iters	  0.129461   0.000000   0.129461 (  0.129587)
PBKDF2 SHA-1 with 300001 iters	  0.195255   0.000000   0.195255 (  0.195449)
PBKDF2 SHA-1 with 400001 iters	  0.259314   0.000000   0.259314 (  0.259565)
PBKDF2 SHA-1 with 500001 iters	  0.324826   0.000000   0.324826 (  0.325150)
PBKDF2 SHA-1 with 600001 iters	  0.388826   0.000000   0.388826 (  0.389216)
PBKDF2 SHA-1 with 700001 iters	  0.453290   0.000000   0.453290 (  0.453753)
PBKDF2 SHA-1 with 800001 iters	  0.518169   0.000000   0.518169 (  0.518704)
PBKDF2 SHA-1 with 900001 iters	  0.583946   0.000000   0.583946 (  0.584603)

       user     system      total        real
PBKDF2 SHA-256 with 1 iters	  0.000041   0.000000   0.000041 (  0.000040)
PBKDF2 SHA-256 with 100001 iters	  0.100197   0.000000   0.100197 (  0.100307)
PBKDF2 SHA-256 with 200001 iters	  0.200945   0.000000   0.200945 (  0.201145)
PBKDF2 SHA-256 with 300001 iters	  0.301221   0.000000   0.301221 (  0.301541)
PBKDF2 SHA-256 with 400001 iters	  0.402317   0.000000   0.402317 (  0.402714)
PBKDF2 SHA-256 with 500001 iters	  0.502404   0.000000   0.502404 (  0.502949)
PBKDF2 SHA-256 with 600001 iters	  0.607353   0.000000   0.607353 (  0.607923)
PBKDF2 SHA-256 with 700001 iters	  0.702174   0.000000   0.702174 (  0.702893)
PBKDF2 SHA-256 with 800001 iters	  0.804487   0.000000   0.804487 (  0.805271)
PBKDF2 SHA-256 with 900001 iters	  0.904246   0.000000   0.904246 (  0.905123)

       user     system      total        real
PBKDF2 SHA-512 with 1 iters	  0.000020   0.000000   0.000020 (  0.000020)
PBKDF2 SHA-512 with 100001 iters	  0.069437   0.000000   0.069437 (  0.069507)
PBKDF2 SHA-512 with 200001 iters	  0.138220   0.000000   0.138220 (  0.138372)
PBKDF2 SHA-512 with 300001 iters	  0.206984   0.000000   0.206984 (  0.207207)
PBKDF2 SHA-512 with 400001 iters	  0.278088   0.000000   0.278088 (  0.278450)
PBKDF2 SHA-512 with 500001 iters	  0.344130   0.000000   0.344130 (  0.344481)
PBKDF2 SHA-512 with 600001 iters	  0.413116   0.000000   0.413116 (  0.413551)
PBKDF2 SHA-512 with 700001 iters	  0.482472   0.000000   0.482472 (  0.482973)
PBKDF2 SHA-512 with 800001 iters	  0.553838   0.000000   0.553838 (  0.554417)
PBKDF2 SHA-512 with 900001 iters	  0.620699   0.000000   0.620699 (  0.621316)

This is an output from a quick benchmark written in Ruby which uses OpenSSL library from Fedora 34 with 40 bytes salt, password and output. The script is below if you want to run it. Anyway.

Say I want to target 40ms password hashing time on this class of CPU, which should be a sane default for an on-premise intranet web application. In that case, these are the recommended iteration counts:

PBKDF2-HMAC-SHA1: 600_000 iterations
PBKDF2-HMAC-SHA256: 400_000 iterations
PBKDF2-HMAC-SHA512: 600_000 iterations

For roughly 20ms calculation time you can go with 250_000 iterations which should be probably a safe but reasonable minimum in 2021 for a web app.

One thing is interesting tho, SHA256 is actually slower than SHA512. I would not expect that, it looks like some padding. Or maybe an error in my benchmark or the fact the test is written in Ruby? That should not be the case because one call into OpenSSL native library is 40ms. To verify, I have rewritten the code in Crystal, an LLVM Ruby-like language which recently hit the 1.0.0 version milestone. It was pretty much copy and paste, here is the result:

                                      user     system      total        real
PBKDF2 SHA-1 with 1 iters	        0.000006   0.000020   0.000026 (  0.000022)
PBKDF2 SHA-1 with 100001 iters	   0.064808   0.000050   0.064858 (  0.064933)
PBKDF2 SHA-1 with 200001 iters	   0.129531   0.000014   0.129545 (  0.129681)
PBKDF2 SHA-1 with 300001 iters	   0.194418   0.000000   0.194418 (  0.194606)
PBKDF2 SHA-1 with 400001 iters	   0.259126   0.000000   0.259126 (  0.259377)
PBKDF2 SHA-1 with 500001 iters	   0.324371   0.000000   0.324371 (  0.324689)
PBKDF2 SHA-1 with 600001 iters	   0.389384   0.000000   0.389384 (  0.389780)
PBKDF2 SHA-1 with 700001 iters	   0.454766   0.000000   0.454766 (  0.455242)
PBKDF2 SHA-1 with 800001 iters	   0.518768   0.000000   0.518768 (  0.519265)
PBKDF2 SHA-1 with 900001 iters	   0.584092   0.000000   0.584092 (  0.584682)
                                        user     system      total        real
PBKDF2 SHA-256 with 1 iters	        0.000015   0.000000   0.000015 (  0.000015)
PBKDF2 SHA-256 with 100001 iters	   0.100220   0.000000   0.100220 (  0.100331)
PBKDF2 SHA-256 with 200001 iters	   0.200525   0.000000   0.200525 (  0.200722)
PBKDF2 SHA-256 with 300001 iters	   0.301427   0.000000   0.301427 (  0.301713)
PBKDF2 SHA-256 with 400001 iters	   0.401965   0.000000   0.401965 (  0.402360)
PBKDF2 SHA-256 with 500001 iters	   0.501238   0.000000   0.501238 (  0.501742)
PBKDF2 SHA-256 with 600001 iters	   0.605589   0.000000   0.605589 (  0.606171)
PBKDF2 SHA-256 with 700001 iters	   0.702972   0.000000   0.702972 (  0.703676)
PBKDF2 SHA-256 with 800001 iters	   0.803881   0.000000   0.803881 (  0.804708)
PBKDF2 SHA-256 with 900001 iters	   0.902646   0.000000   0.902646 (  0.903572)
                                        user     system      total        real
PBKDF2 SHA-512 with 1 iters	        0.000015   0.000000   0.000015 (  0.000014)
PBKDF2 SHA-512 with 100001 iters	   0.068897   0.000000   0.068897 (  0.068960)
PBKDF2 SHA-512 with 200001 iters	   0.137699   0.000000   0.137699 (  0.137853)
PBKDF2 SHA-512 with 300001 iters	   0.206790   0.000000   0.206790 (  0.206982)
PBKDF2 SHA-512 with 400001 iters	   0.275695   0.000000   0.275695 (  0.275972)
PBKDF2 SHA-512 with 500001 iters	   0.344550   0.000000   0.344550 (  0.344882)
PBKDF2 SHA-512 with 600001 iters	   0.412838   0.000000   0.412838 (  0.413224)
PBKDF2 SHA-512 with 700001 iters	   0.482600   0.000000   0.482600 (  0.483100)
PBKDF2 SHA-512 with 800001 iters	   0.551040   0.000000   0.551040 (  0.551562)
PBKDF2 SHA-512 with 900001 iters	   0.619910   0.000000   0.619910 (  0.620500)

It is roughly the same. Which means you can take these numbers when building non-Ruby projects (C, Go, Python) too.

For “comparison”, here is the ouput from bcrypt (from ruby-bcrypt library which uses a copy-paste implementation) from my Intel NUC i3 2018 brick:

       user     system      total        real
bcrypt with cost 6	  0.003510   0.000000   0.003510 (  0.003549)
bcrypt with cost 7	  0.006642   0.000000   0.006642 (  0.006669)
bcrypt with cost 8	  0.013120   0.000000   0.013120 (  0.013149)
bcrypt with cost 9	  0.026097   0.000000   0.026097 (  0.026154)
bcrypt with cost 10	  0.052030   0.000000   0.052030 (  0.052104)
bcrypt with cost 11	  0.103912   0.000000   0.103912 (  0.104034)
bcrypt with cost 12	  0.207882   0.000000   0.207882 (  0.208111)
bcrypt with cost 13	  0.415320   0.000000   0.415320 (  0.415777)
bcrypt with cost 14	  0.830609   0.000000   0.830609 (  0.831516)
bcrypt with cost 15	  1.660890   0.000000   1.660890 (  1.662613)
bcrypt with cost 16	  3.321980   0.000000   3.321980 (  3.325642)
bcrypt with cost 17	  6.641207   0.000000   6.641207 (  6.647944)

It has an exponential characteristic, 40ms is roughly cost 13 and 20ms is cost 12. It is still a good choice, however keep in mind that bcrypt is not available in most Linux distributions (including Red Hat Enterprise Linux) and, again, not approved by NIST.

I do have another brick: Apple Mac Mini M1 16GB from 2021, just wondering how it compares to the i3 from 2018. I will not be pasting all tests because everything was pretty much the same - Apple M1 was a tad slower in the Ruby test. Unfortunately, Crystal is not yet available for the M1 chip.

       user     system      total        real
bcrypt with cost 6	  0.004288   0.000079   0.004367 (  0.004395)
bcrypt with cost 7	  0.007669   0.000018   0.007687 (  0.007686)
bcrypt with cost 8	  0.015171   0.000076   0.015247 (  0.015254)
bcrypt with cost 9	  0.030117   0.000248   0.030365 (  0.030366)
bcrypt with cost 10	  0.060343   0.000530   0.060873 (  0.060873)
bcrypt with cost 11	  0.119880   0.000926   0.120806 (  0.120805)
bcrypt with cost 12	  0.240576   0.002135   0.242711 (  0.242947)
bcrypt with cost 13	  0.478940   0.003749   0.482689 (  0.482706)
bcrypt with cost 14	  0.957543   0.007271   0.964814 (  0.964815)
bcrypt with cost 15	  1.914573   0.014784   1.929357 (  1.929367)
bcrypt with cost 16	  3.829219   0.028846   3.858065 (  3.858162)
bcrypt with cost 17	  7.669053   0.056666   7.725719 (  7.726456)

The Ruby script:

require 'benchmark'
require 'openssl'
require 'bcrypt'

asha = "bbd2a53e6feb515d644090c4fefba1c2756cc19b"

Benchmark.bm do |bench|
  (1..1000000).step(100000).each do |iters|
    bench.report("PBKDF2 SHA-1 with #{iters} iters\t") do
      OpenSSL::PKCS5.pbkdf2_hmac_sha1(asha, asha, iters, 40)
    end
  end
end

Benchmark.bm do |bench|
  (1..1000000).step(100000).each do |iters|
    bench.report("PBKDF2 SHA-256 with #{iters} iters\t") do
      OpenSSL::PKCS5.pbkdf2_hmac(asha, asha, iters, 40, OpenSSL::Digest.new("SHA256"))
    end
  end
end

Benchmark.bm do |bench|
  (1..1000000).step(100000).each do |iters|
    bench.report("PBKDF2 SHA-512 with #{iters} iters\t") do
      OpenSSL::PKCS5.pbkdf2_hmac(asha, asha, iters, 40, OpenSSL::Digest.new("SHA512"))
    end
  end
end

Benchmark.bm do |bench|
  (6..17).each do |cost|
    bench.report("bcrypt with cost #{cost}\t") do
      BCrypt::Password.create(asha, cost: cost)
    end
  end
end

The same script in Crystal language:

require "benchmark"
require "openssl"

asha = "bbd2a53e6feb515d644090c4fefba1c2756cc19b"

Benchmark.bm do |bench|
  (1..1000000).step(100000).each do |iters|
    bench.report("PBKDF2 SHA-1 with #{iters} iters\t") do
      OpenSSL::PKCS5.pbkdf2_hmac_sha1(asha, asha, iters, 40)
    end
  end
end

Benchmark.bm do |bench|
  (1..1000000).step(100000).each do |iters|
    bench.report("PBKDF2 SHA-256 with #{iters} iters\t") do
      OpenSSL::PKCS5.pbkdf2_hmac(asha, asha, iters, OpenSSL::Algorithm::SHA256, 40)
    end
  end
end

Benchmark.bm do |bench|
  (1..1000000).step(100000).each do |iters|
    bench.report("PBKDF2 SHA-512 with #{iters} iters\t") do
      OpenSSL::PKCS5.pbkdf2_hmac(asha, asha, iters, OpenSSL::Algorithm::SHA512, 40)
    end
  end
end

Well, there you have it. Drop me a comment on twitter @lzap and have a nice day!

Read on

11 May 2021 | linux | fedora | foreman

Remap US key next to enter to enter in MacOS

The Czech keyboard layout on a physical US Mac keyboard has some keys which are pretty much useless. For example the key | aka \ next to the enter is actually also available on tilde key next to left shift in Czech layout and since I am used to wide enter key I end up pressing it when I want to hit enter. It renders to a weird “double tilde” character which I never use anyway. Well, an easy help. This can be remapped pretty easily:

hidutil property --set '{"UserKeyMapping":
    [{"HIDKeyboardModifierMappingSrc":0x700000031,
      "HIDKeyboardModifierMappingDst":0x700000058}]
}'

That’s all, really. No need to restart anything, but to do this after each boot a property list for launcher must be created. Here it is:

cat << EOF | sudo tee -a /Library/LaunchDaemons/org.custom.keyboard-remap.plist
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN" "http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
  <dict>
    <key>Label</key>
    <string>org.custom.keyboard-remap</string>
    <key>ProgramArguments</key>
    <array>
      <string>/usr/bin/hidutil</string>
      <string>property</string>
      <string>--set</string>
      <string>{"UserKeyMapping": [{"HIDKeyboardModifierMappingSrc":0x700000031, "HIDKeyboardModifierMappingDst":0x700000058}] }</string>
    </array>
    <key>RunAtLoad</key>
    <true/>
    <key>KeepAlive</key>
    <false/>
  </dict>
</plist>
EOF
sudo launchctl load -w /Library/LaunchDaemons/org.custom.keyboard-remap.plist

Worry do not, this is still a Linux blog. I just ended up using MacOS on desktop a bit more lately, I still run Linux remote shells :-)

Read on

19 April 2021 | macos

Crop and resize video to get rid of borders

We stream our community demos on youtube via Google Meet and there are borders on each side which makes the content to be smaller and less readable. Luckily, it is in the middle of the screen, so the following command will crop the image to its 1/1.29 of the size, stretch it back to 720p and reencodes it for YouTube copying the audio stream.

ffmpeg -i intput.mp4 -vf "crop=iw/1.29:ih/1.29,scale=-1:720" -y output.mp4

If your source video is different, just play around with the 1.29 constant to get the desired output. Use -t option to encode just the first 10 seconds of the video to speed testing up:

ffmpeg -i intput.mp4 -vf "crop=iw/1.29:ih/1.29,scale=-1:720" -t 00:00:10.0 -y output.mp4

That is all.

Read on

19 April 2021 | linux | fedora

FreeIPA and Foreman Proxy development setup

I have been avoiding this for like ten years now, but today is the day when I will setup a FreeIPA with Foreman Proxy for development and testing purposes and here are my notes.

The goal is to deploy a libvirt VM with IPA server and Foreman Proxy intergated with it. The domain will be ipa.lan and the host named ipa.ipa.lan. This is NOT how you should deploy production Foreman FreeIPA integration! For that, reading our official documentation and using foreman-installer is suggested instead.

We need a VM, let’s go with CentOS 8.

virt-builder centos-8.2 --output /var/lib/libvirt/images/ipa.img --root-password password:redhat --hostname ipa.ipa.lan
virt-install --name ipa.ipa.lan --memory 2048 --vcpus 2 --disk /var/lib/libvirt/images/ipa.img --import --os-variant rhel8.3 --update
virsh console ipa.ipa.lan

We need a static IP for this VM:

nmcli con modify enp1s0 \
  ip4 192.168.122.5/24 \
  gw4 192.168.122.1 \
  ipv4.dns 192.168.122.1
nmcli con down enp1s0
nmcli con up enp1s0

Make sure the hostname is correct:

hostnamectl set-hostname ipa.ipa.lan

Make sure to fix hosts file, FQDN must resolve to the IP address not localhost:

grep ipa /etc/hosts
192.168.122.5 ipa.ipa.lan ipa

The installation is very smooth, expect just couple of questions like administrator password or the actual domain:

dnf module enable idm:DL1
dnf module install idm:DL1/dns
ipa-server-install --setup-dns --auto-forwarder --auto-reverse

Ensure firewall ports are enabled:

firewall-cmd --add-service=http --add-service=https --add-service=ldap --add-service=ldaps \
    --add-service=ntp --add-service=kerberos --add-service=dns --add-port=8000/tcp --permanent

Next up, install Foreman Proxy:

dnf -y install https://yum.theforeman.org/releases/2.4/el8/x86_64/foreman-release.rpm
dnf -y install foreman-proxy

Create the foreman user with minimum required permissions to manage Foreman hosts, create and configure keytab file. When asked for admin password, use the one used when installing the IPA server:

foreman-prepare-realm admin realm-smart-proxy
mv freeipa.keytab /etc/foreman-proxy/freeipa.keytab
chown foreman-proxy:foreman-proxy /etc/foreman-proxy/freeipa.keytab

Configure and start the Foreman Proxy service. This is for development purposes, so let’s only use HTTP. You may also want to add some trusted_hosts entries to allow access from Foreman:

cat /etc/foreman-proxy/settings.yml
---
:settings_directory: /etc/foreman-proxy/settings.d
:http_port: 8000
:log_level: DEBUG

Enable Realm module:

cat /etc/foreman-proxy/settings.d/realm.yml
---
:enabled: true
:use_provider: realm_freeipa

And enable FreeIPA plugin:

cat /etc/foreman-proxy/settings.d/realm_freeipa.yml
---
:keytab_path: /etc/foreman-proxy/freeipa.keytab
:principal: realm-smart-proxy@IPA.LAN
:ipa_config: /etc/ipa/default.conf
:remove_dns: true
:verify_ca: true

And start it up:

systemctl enable --now foreman-proxy

Realm feature should be available:

curl http://ipa.ipa.lan:8000/features
["realm"]

To show a host entry in IPA via CLI:

kinit admin
ipa host-show rex-dzurnak.ipa.lan
  Host name: rex-dzurnak.ipa.lan
  Class: ipa-debian-10
  Password: True
  Keytab: False
  Managed by: rex-dzurnak.ipa.lan

Add the foreman proxy into Foreman and start developing or testing. Have fun!

Read on

12 April 2021 | linux | fedora

The Lounge web IRC client in Fedora

My graphics card died and thanks to COVID and Bitcoin, it will be a long wait until it’s back. I am on Mac M1 at the moment and it looks like there are not many good IRC clients on MacOS.

Let’s run a simple web-based IRC client which can also work as a bouncer (no need of ZNC). I randomly selected one which is called The Lounge, looks nice and works okay for me. This one is written in NodeJS and since there is no package in Fedora, I’ve decided to build it via yarn. It needs only one native dependency I think - sqlite3 so do not expect any problems on that front:

# dnf install nodejs yarn sqlite-devel
# dnf groupinstall "Development Tools"
# mkdir ~/.thelounge
# cd ~/.thelounge
# yarn add thelounge

If you prefer installing it into /usr/local then run yarn global add thelounge.

Create a user service, I will be running it as a regular user:

# cat /home/lzap/.config/systemd/user/thelounge.service
[Unit]
Description=The Lounge IRC client
After=network-online.target
[Service]
ExecStart=/home/lzap/.thelounge/node_modules/.bin/thelounge start
[Install]
WantedBy=multi-user.target

Start it to create a default configuration file:

# systemctl --user daemon-reload
# systemctl --user enable --now thelounge

Optionally, stop the service for now and review the configuration. There are couple of things I recommend to tune. By default the service listens on HTTP (9090), no HTTPS is configured, it stores all logs in both sqlite3 and text files and it is configured as “private” instance, meaning you need to login with a username and password:

# vim ~/.thelounge/config.js

Create new user:

# ~/.thelounge/node_modules/.bin/thelounge add lzap

Visit http://localhost:9090 or https://localhost:9090 if you’ve configured SSL. There you can create one or more IRC connections, join all channels, everything will be written into a separate ~/.thelounge/users/user.js configuration file which is nice. If you disabled sqlite3 logging, everything is stored in text files which I appreciate a lot.

If you want a simple letsencrypt tutorial for Fedora, read my prevous blog post:

# grep "https: {" -A5  ~/.thelounge/config.js
  https: {
    enable: true,
    key: "/etc/pki/tls/private/home.zapletalovi.com.key",
    certificate: "/var/lib/acme/certs/home.zapletalovi.com.crt",
    ca: "",
  },

No intermediate CAs are needed for letsencrypt so you can leave the field blank. Have fun.

Read on

31 March 2021 | linux | fedora

Letsencrypt a Fedora server

I was looking for a simple letsencrypt tutorial for my home server running Fedora but it looks like the official (and quite capable) certbot is not availble in Fedora repos. So I have decided to go a more simple route of using acme-tiny shell script which is present and does the same, at least if you are running Apache httpd.

First off, install Apache httpd, SSL support and acme script itself:

# dnf install httpd mod_ssl acme-tiny

Let’s assume that the Apache server is already serving some files and is available on the desired domain via HTTP (not HTTPS yet):

# systemctl enable --now httpd
# curl -s http://home.zapletalovi.com | grep -o "Test Page"
Test Page

We are almost there, trust me. Generate a new certificate request. OpenSSL tool will ask several questions like name, organization and this stuff. Make sure that the Common Name (CN) is correct.

# cd /etc/pki/tls
# ln -s /var/lib/acme/csr .
# openssl req -new -nodes -keyout private/home.zapletalovi.com.key -out csr/home.zapletalovi.com.csr
# chmod 0400 private/home.zapletalovi.com.key

The next step is the actual communication with the authority, putting the challenge hash into /var/www/challenges directory which is exported by Apache httpd and downloading the signed request:

# systemctl start acme-tiny

See system journal for any errors. If you encounter one, just start the script manually but make sure to use acme user account not root:

# su acme -s /bin/bash
# /usr/libexec/acme-tiny/sign

And that’s really all! You should have your certificate signed by letsencrypt now. Configure the desired software to use the new certificate and the key from the following paths:

# find /var/lib/acme /etc/pki/tls/private
/var/lib/acme
/var/lib/acme/certs
/var/lib/acme/certs/home.zapletalovi.com.crt
/var/lib/acme/csr
/var/lib/acme/csr/home.zapletalovi.com.csr
/var/lib/acme/private
/var/lib/acme/private/account.key
/etc/pki/tls/private/home.zapletalovi.com.key

For example I want to actually configure the Apache httpd itself:

# grep zapletalovi /etc/httpd/conf.d/ssl.conf
SSLCertificateFile /var/lib/acme/certs/home.zapletalovi.com.crt
SSLCertificateKeyFile /etc/pki/tls/private/home.zapletalovi.com.key

If you are like me and running under SELinux enforcing, make sure that the newly generated certificates have the proper label:

# semanage fcontext -a -f a -t cert_t '/var/lib/acme/certs(/.*)?'
# restorecon -rv /var/lib/acme/certs

The final and the most important step - enable systemd timer which will automatically extend the certificate for you:

# systemctl enable --now acme-tiny.timer

That was easy.

Read on

31 March 2021 | linux | fedora