Linux: list SSL certificates

Source: List all available ssl ca certificates

locate .pem | grep "\.pem$"

Perl SSL: get debug details when it's not working

When trying to understand why LWP::UserAgent cannot post to HTTPS, you can try:

use IO::Socket::SSL qw(debug4);

so as to get more details.

postgreSQL: streaming replication delay

From the standby DB:

crmmbqt=# select now() - pg_last_xact_replay_timestamp() as replication_delay;
┌───────────────────────┐
│   replication_delay   │
├───────────────────────┤
│ 1 day 05:45:26.277667 │
└───────────────────────┘

or get more details with

crmmbqt=# pg_last_xlog_receive_location(), pg_last_xlog_replay_location(), pg_last_xact_replay_timestamp(), case when pg_last_xlog_receive_location() = pg_last_xlog_replay_location() then 0 else extract(epoch from now() - pg_last_xact_replay_timestamp()) end as replication_delay;

AWS: Launch an EC2 template over multiple subnets

Source: AWS Ec2 - Launch template spanning multiple subnets / availability zones
First create a launch template in the VPC with a Security Group.
Then, at the end, select Create Auto Scaling Group.

And then specify the subnets.

AWS: add .pem to SSH agent

Be sure the SSH agent is running.

[ec2-user@ip-10-75-40-54 .ssh]$ ssh-add par-vas-key.pem 
Could not open a connection to your authentication agent.

Start the ssh-agent with:

[ec2-user@ip-10-75-40-54 .ssh]$ eval `ssh-agent -s`
Agent pid 32703

Add .pem:

[ec2-user@ip-10-75-40-54 .ssh]$ ssh-add par-vas-key.pem 
Identity added: par-vas-key.pem (par-vas-key.pem)

Check public key known to the agent:

[ec2-user@ip-10-75-40-54 .ssh]$ ssh-add -L
ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQClUygT8u3RS02SLEL44XkE... par-vas-key.pem

Docker Alpine Linux: ssh keeps asking for password

Suppose we are running an sshd Docker Alpine (3.11) container. Even if properly configured by installing an SSH public key for root, you may keep being prompted for the root password by sshing the container.

[jerome@jeroboam] > docker run -e 'SSH_PUBLIC_KEY="..." -it --rm -p 2223:22 --name ss-ussd-sshd ss-ussd/sshd
[jerome@jeroboam] > ssh -p 2223 root@localhost
Warning: Permanently added '[localhost]:2223' (ECDSA) to the list of known hosts.
root@localhost's password: 

Solution: can't config ssh service when i use alpine:3.9 #28
Root cause: CVE-2019-5021

Bottom line: add this command in the Dockerfile:

# make sure root login is disabled
RUN sed -i -e 's/^root::/root:!:/' /etc/shadow

Validate CloudFormation

CloudFormation Linter
MacOS Install

[jerome@jeroboam] > brew install cfn-lint

Run

[jerome@jeroboam] > cfn-lint ussd_vas.yaml

Bash: for loop alphabet

Source: For loop with Alphabet

[jerome@jeroboam] > for i in `seq 97 102`; do printf "\\$(printf %o $i)\n"; done
a
b
c
d
e
f

Linux: sum numbers

Source: Shell command to sum integers, one per line?

$ grep ussd\. MBQTUSG_20200302_001.csv | head -5
ussd.mt,8,02/03/2020 00:00:00,02/03/2020 23:59:59,88060
ussd.mo,576,02/03/2020 00:00:00,02/03/2020 23:59:59,172099
ussd.mt,576,02/03/2020 00:00:00,02/03/2020 23:59:59,172099
ussd.mo,1,02/03/2020 00:00:00,02/03/2020 23:59:59,254078
ussd.mt,34,02/03/2020 00:00:00,02/03/2020 23:59:59,254078

$ grep ussd\.m MBQTUSG_20200302_001.csv | cut -d, -f2 | perl -lpe '$c+=$_}{$_=$c'
243648

Format json file

The simplest way to reformat a one line JSON file into multiple lines is

jq . file.json

macOS: add PYTHONPATH to PostgreSQL brew service

Source: Changing the Homebrew Apache PATH variable.

Let's suppose we installed Python requests module.
On my system, requests has been installed in /usr/local/lib/python2.7/site-packages/.
Then, trying to import it from postgres like in

create or replace function tutu(o_status out int) as
$$
import sys, os
import requests, json
plpy.info(sys.version)
plpy.info(sys.path)
return (500)
$$ language plpython2u security definer;

will cause

select * from tutu()

to output

Query 1 ERROR: ERROR:  ImportError: No module named requests

By default, postgres will look in /Library/Python/2.7/site-packages/.
We then need to update PYTHONPATH.
With brew starting postgres, we will update the postgres plist file.

[jerome@jeroboam] > vi /usr/local/opt/postgresql\@9.4/homebrew.mxcl.postgresql\@9.4.plist

  KeepAlive
  
  Label
  homebrew.mxcl.postgresql@9.4
  EnvironmentVariables
  
    PYTHONPATH
    /usr/local/lib/python2.7/site-packages
  
  ProgramArguments
  
    /usr/local/opt/postgresql@9.4/bin/postgres
    -D
    /usr/local/var/postgresql@9.4
  
  RunAtLoad
  
  WorkingDirectory
  /usr/local
  StandardErrorPath
  /usr/local/var/log/postgresql@9.4.log

Then

[jerome@jeroboam] > brew services stop postgresql@9.4
[jerome@jeroboam] > brew services start postgresql@9.4

Then

select * from tutu()

outputs

INFO:  2.7.16 (default, Dec 13 2019, 18:00:32) 
[GCC 4.2.1 Compatible Apple LLVM 11.0.0 (clang-1100.0.32.4) (-macos10.15-objc-s
INFO:  ['/usr/local/lib/python2.7/site-packages', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python27.zip', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-darwin', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-mac', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/plat-mac/lib-scriptpackages', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-tk', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-old', '/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/lib-dynload', '/Users/jerome/Library/Python/2.7/lib/python/site-packages', '/Library/Python/2.7/site-packages', '/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python', '/System/Library/Frameworks/Python.framework/Versions/2.7/Extras/lib/python/PyObjC']

macOS AnyConnect: AnyConnect cannot confirm it is connected to your secure gateway

Workaround: AnyConnect: Got an error after updating macOS Catalina

macOS: ld: warning: directory not found for option '-L/Library/Developer/CommandLineTools/SDKs/MacOSX10.13.sdk/usr/lib'

If your PostgreSQL extension Makefile uses PGXS like in

PG_CONFIG = pg_config
...
PGXS := $(shell $(PG_CONFIG) --pgxs)
include $(PGXS)

And you updated MacOS since you installed PostgreSQL, you may experience the following error at compile time :

[jerome@jeroboam] > make
...
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -D_XOPEN_SOURCE  -L/usr/local/Cellar/postgresql@9.4/9.4.18/lib -L/usr/local/opt/openssl/lib -L/usr/local/opt/readline/lib -L/Library/Developer/CommandLineTools/SDKs/MacOSX10.13.sdk/usr/lib  -Wl,-dead_strip_dylibs   -bundle -bundle_loader /usr/local/Cellar/postgresql@9.4/9.4.18/bin/postgres -o src/pg_proctab.so src/pg_proctab.o
ld: warning: directory not found for option '-L/Library/Developer/CommandLineTools/SDKs/MacOSX10.13.sdk/usr/lib'

Then replace MacOSX10.13 for CPPFLAGS and LDFLAGS in

[jerome@jeroboam] > sudo vi /usr/local/Cellar/postgresql\@9.4/9.4.18/lib/pgxs/src/Makefile.global

with e.g. MacOSX10.15.

MacOS: fatal error: 'sys/vfs.h' file not found

[jerome@jeroboam] > make
clang -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -D_XOPEN_SOURCE  -I. -I./ -I/usr/local/Cellar/postgresql@9.4/9.4.18/include/server -I/usr/local/Cellar/postgresql@9.4/9.4.18/include/internal -I/usr/local/opt/openssl/include -I/usr/local/opt/readline/include -I/Library/Developer/CommandLineTools/SDKs/MacOSX10.13.sdk/usr/include/libxml2   -c -o src/pg_proctab.o src/pg_proctab.c
src/pg_proctab.c:14:10: fatal error: 'sys/vfs.h' file not found
#include 
         ^~~~~~~~~~~
1 error generated.
make: *** [src/pg_proctab.o] Error 1

Then replace

#include <sys/vfs.h>

with

#if defined(__APPLE__)
#include <sys/mount.h>
#else
#include <sys/vfs.h>
#endif

byobu: name a new session

byobu new -s <session-name>

Clojure: if key exists: update, otherwise: assoc

Source : if key exists: update, otherwise: assoc

user> (update {:a [1]} :a (fnil conj []) 2)
{:a [1 2]}
user> (update {:a [1]} :b (fnil conj []) 2)
{:a [1], :b [2]}

macOS: install PHP on Catalina

Source: How to Install a PHP 7.2 on macOS 10.15 Catalina using Homebrew and PECL

macOS: know the details of running httpd

[jerome@jeroboam] > $(ps ax -o comm | grep -m 1 '[a]pache\|[h]ttpd') -V
Server version: Apache/2.4.41 (Unix)
Server built:   Oct  1 2019 10:31:38
Server's Module Magic Number: 20120211:88
Server loaded:  APR 1.7.0, APR-UTIL 1.6.1
Compiled using: APR 1.7.0, APR-UTIL 1.6.1
Architecture:   64-bit
Server MPM:     prefork
  threaded:     no
    forked:     yes (variable process count)
Server compiled with....
 -D APR_HAS_SENDFILE
 -D APR_HAS_MMAP
 -D APR_HAVE_IPV6 (IPv4-mapped addresses enabled)
 -D APR_USE_SYSVSEM_SERIALIZE
 -D APR_USE_PTHREAD_SERIALIZE
 -D SINGLE_LISTEN_UNSERIALIZED_ACCEPT
 -D APR_HAS_OTHER_CHILD
 -D AP_HAVE_RELIABLE_PIPED_LOGS
 -D DYNAMIC_MODULE_LIMIT=256
 -D HTTPD_ROOT="/usr/local/Cellar/httpd/2.4.41_1"
 -D SUEXEC_BIN="/usr/local/opt/httpd/bin/suexec"
 -D DEFAULT_PIDLOG="/usr/local/var/run/httpd/httpd.pid"
 -D DEFAULT_SCOREBOARD="logs/apache_runtime_status"
 -D DEFAULT_ERRORLOG="logs/error_log"
 -D AP_TYPES_CONFIG_FILE="/usr/local/etc/httpd/mime.types"
 -D SERVER_CONFIG_FILE="/usr/local/etc/httpd/httpd.conf"

macOS: where to find crash files.

On macOS, crashes of processes can be found in /Library/Logs/DiagnosticReports (system-wide) and ~/Library/Logs/DiagnosticReports (user).
These plain text files can be opened by the Console app or the System Reports app.

macOS: httpd: child pid 45333 exit signal Segmentation fault (11)

• macOS 10.15 (Catalina)
• php 7.3.10
• Apache 2.4.41

If you try to do SOAP requests with PHP 7 on macOS and you get permanent segmentation faults in the httpd error log, this is due to a bug in the PHP Apache module.
You can have an idea of the reasons of the segmentation fault in the .crash files automatically generated on macOS.

[jerome@jeroboam] > less /Users/jerome/Library/Logs/DiagnosticReports/httpd_2019-10-22-214827_jeroboam.crash

Thread 0 Crashed:: Dispatch queue: com.apple.main-thread
0   libsystem_platform.dylib            0x00007fff71d21732 _platform_strlen + 18
1   libphp7.so                          0x000000010e5808e9 get_param + 56
2   libphp7.so                          0x000000010e581569 serialize_response_call2 + 648
3   libphp7.so                          0x000000010e57b371 serialize_response_call + 3807
4   libphp7.so                          0x000000010e57a01f zim_SoapServer_handle + 7188
5   libphp7.so                          0x000000010e739fa5 ZEND_DO_FCALL_SPEC_RETVAL_UNUSED_HANDLER + 412
6   libphp7.so                          0x000000010e709df9 execute_ex + 98
7   libphp7.so                          0x000000010e709f76 zend_execute + 319
8   libphp7.so                          0x000000010e6cfab7 zend_execute_scripts + 277
9   libphp7.so                          0x000000010e67b1a0 php_execute_script + 636
10  libphp7.so                          0x000000010e777f2f php_handler + 1092
11  httpd                               0x000000010dac6443 ap_run_handler + 51
12  httpd                               0x000000010dac69fd ap_invoke_handler + 240
13  httpd                               0x000000010dafc690 ap_internal_redirect + 54
14  mod_rewrite.so                      0x000000010dc895af handler_redirect + 128
15  httpd                               0x000000010dac6443 ap_run_handler + 51
16  httpd                               0x000000010dac69fd ap_invoke_handler + 240
17  httpd                               0x000000010dafc1cb ap_process_async_request + 864
18  httpd                               0x000000010dafc262 ap_process_request + 20
19  httpd                               0x000000010daf9078 ap_process_http_connection + 385
20  httpd                               0x000000010dad6c2c ap_run_process_connection + 51
21  mod_mpm_prefork.so                  0x000000010dc73807 child_main + 1085
22  mod_mpm_prefork.so                  0x000000010dc732c6 make_child + 409
23  mod_mpm_prefork.so                  0x000000010dc7330e startup_children + 72
24  mod_mpm_prefork.so                  0x000000010dc7258b prefork_run + 282
25  httpd                               0x000000010dad8f5f ap_run_mpm + 64
26  httpd                               0x000000010dacd58f main + 2119
27  libdyld.dylib                       0x00007fff71b23405 start + 1

The bug is known and referenced in Bug #73906.
A workaround is also proposed, which consists in having your PHP function return a hash map.
For example, if your function return an array of requestId + sessionAuthId, then replace it with

array('requestId' => $result[0], 'sessionAuthId' => $result[1])

.