Lukáš Zapletal: engineer in pyjama

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

Enable serial console for libvirt

QEMU/KVM libvirt virtual machine can be acessed via serial console. When a new VM is created, serial console device is created. However to fully utilize this, several steps are needed on the guest machine.

The first option is to start getty service on serial console to get a login prompt when system finishes booting. This is as easy as:

# systemctl enable --now serial-getty@ttyS0.service

To access serial console via libvirt command line do:

# virsh console virtual_machine_name

This approach is simple enough, but when something goes wrong and VM does not boot, it is not possible to access the VM during early boot or even bootloader. In that case, perform the additional configuration:

# grep console /etc/default/grub
GRUB_TERMINAL_INPUT="console serial"
GRUB_TERMINAL_OUTPUT="console serial"
GRUB_CMDLINE_LINUX="... console=ttyS0"

Then write new grub configuration, for EFI systems do the following:

# grub2-mkconfig -o /boot/efi/EFI/redhat/grub.cfg

For BIOS systems do:

# grub2-mkconfig -o /boot/grub2/grub.cfg

Reboot and connect early to access grub or to see early boot messages. These commands will work on Fedora, CentOS, Red Hat Enterprise Linux and clones.

Read on
31 March 2021 | linux | fedora | rhel

Thunderbolt bridge connection in Fedora 33

My home network is extremely slow, because I have CAT5e cables everywhere. I was wondering if I can use Thunderbolt ports which I have both on the new Mac M1 and Intel NUC with Fedora. So without my breath, since some Thunderbolt docks are known to brick the new Macs, I connected the two guys. And it worked automatically!

Fedora’s (33) kernel automatically recognized thunderbolt0 device and NetworkManager created a new connection named “Wired connection 1”. There must be some autonegotiation in the spec, because the two devices created 169.254/16 network and picked some IP addresses. I was not expecting that, I mean maybe if this was Linux to Linux but with MacOS involved I thought this is not gonna work. Let’s see how fast is my 100Mbps connection:

mac$ nc -v -l 2222 > /dev/null

linux$ dd if=/dev/zero bs=1024K count=512 | nc -v 192.168.1.55 2222
Ncat: Version 7.80 ( https://nmap.org/ncat )
Ncat: Connected to 192.168.1.5:2222.
512+0 záznamů přečteno
512+0 záznamů zapsáno
536870912 bajtů (537 MB, 512 MiB) zkopírováno, 45,8012 s, 11,7 MB/s
Ncat: 536870912 bytes sent, 0 bytes received in 45.86 seconds.

That’s expected on a 100Mbps ethernet. On a gigabit network, which I considered to upgrade to, we should see something like 117 MB/s for my ideal case (just a switch). But let’s see how Thunderbolt works for me:

linux$ dd if=/dev/zero bs=1024K count=512 | nc -v 169.254.145.73 2222
Ncat: Version 7.80 ( https://nmap.org/ncat )
Ncat: Connected to 169.254.145.73:2222.
512+0 záznamů přečteno
512+0 záznamů zapsáno
536870912 bajtů (537 MB, 512 MiB) zkopírováno, 0,788541 s, 681 MB/s
Ncat: 536870912 bytes sent, 0 bytes received in 0.79 seconds.

Holy Moly! It’s not 1.1 Gbps but almost 900 MB/s that’s insane. This is a USB-C cable which is the best thing I currently have (this came with my LG screen). I am dropping a proper Thunderbolt3 into a basket to see how faster this can be. I mean in theory, I don’t have that fast SSD in my Intel NUC server.

Allright, so that’s looks like should be my preferred connection between my desktop and Linux. Let’s rename the connection first:

nmcli con modify "Drátové připojení 1" connection.id thunderbolt0

Oh gosh, I need to switch back to English from Czech language. Next up, set static IP address.

nmcli con modify thunderbolt0 ipv4.method static ipv4.address 192.168.13.4/24
nmcli con down thunderbolt0
nmcli con up thunderbolt0

And after quick update in a MacOS network dialog and /etc/hosts change, the connection between my new desktop and my working Linux machine is 10Gbps.

Read on
27 February 2021 | linux | fedora

What is waking my HDD up in Linux

When my disks wake up during the day, I am angry. I want silence, so I started investigating which process makes them to do that. I suspect that something is browsing Samba share, but to confirm I created this simple SystemTap script:

# cat syscall_open.stp 
#!/usr/bin/env stap
#
# System-wide strace-like tool for catching file open syscalls.
#
# Usage: stap syscall_open filename_regexp
#
probe syscall.open* {
  if (filename =~ @1) {
    printf("%s(%d) opened %s\n", execname(), pid(), filename)
  }
}

It’s as easy as starting this up and waiting until the process is found. It accepts regular expression, not a glob:

# dnf install systemtap systemtap-runtime
# stap syscall_open.stp '/mnt/int/data.*'

This will work on all SystemTap operating systems, I tested this on Fedora and any EL distribution should work too.

Read on
22 February 2021 | linux | fedora
twitter.com linkedin.com
google.com/+ facebook.com
flickr.com youtube.com