Micro service for course statistics from Ladok Uppföljningsdatabas using Stunnel.

See document Certifikat för uppföljningsdatabasen i kursstatistik-api in Confluence.

After ordering a certificate from Ladok, you will receive an email with instructions. Follow these instructions and download certficate (PFX file) and password files. The email might also include instructions on how to extract key and client certificate. Below is a modified set of instructions (based in this post). The modification is necessary so that the password to the key can be removed later (this modification might not be necessary with an improved Stunnel configuration.) Extract key and certificate in a suitable folder.

Here we assume that the certificate name we received is [email protected]. If you get errors when running the below openssl-commands, it will probably be fixed by passing the flag --legacy

# Extract private key from PFX file
$ openssl pkcs12 -in [email protected] -nocerts -out [email protected] -nodes

# Set secure file permissions on private key file
$ chmod 400 [email protected]

# Extract client certificate from PFX file
$ openssl pkcs12 -in [email protected] -out [email protected] -clcerts -nokeys

Afterwards, make sure to remove anything before the initial -----BEGIN PRIVATE KEY----- from the [email protected] file before running the following commands.

# Convert private key to base64 and copy to clipboard
$ cat [email protected] | base64 | pbcopy

# Convert client certificate to base64 and copy to clipboard
$ cat [email protected] | base64 | pbcopy

Set the resulting strings as LADOK3_CERT_KEY and LADOK3_CERT respectively

Remove all the certificate and password files from your machine

This assumes that you have Homebrew installed. If not, follow the instructions on brew.sh.

$ brew install stunnel

After ordering a certificate from Ladok, you will also receive an email with instructions on how to configure Stunnel. Below is a modified set of instructions and suggested configuration. During the last couple of updates, we have not received an updated certificate chain file.

You may, or may not, choose to use a config folder. These instructions assume that all files are in /usr/local/etc/stunnel, simply called the stunnel folder,

1. Save the certificate chain file, e.g. UF-prod-ca-bundle.txt, to the stunnel folder.
2. Move the private key file and the client certificate file to the stunnel folder.
3. Change the stunnel config file, stunnel.conf to:

debug = 7
foreground = yes
[db2_ufhsk_ENVIRONMENT]
client = yes
accept = localhost:11000
connect = [url received in email from ladok]
key = [email protected]
verify = 2
cert = [email protected]
CAfile = [certificate chain file]

# In /usr/local/etc/stunnel
$ stunnel

TODO: Add instructions to run Stunnel in any folder.

If localhost port 11000 is already in use:

# Find out which service that uses port 11000
$ lsof -nP -i4TCP:11000 | grep LISTEN

# stunnel 6850 [account name]   12u  IPv4 0x9fb72bfe23991e4b      0t0  TCP 127.0.0.1:11000 (LISTEN)

# Kill process
$ kill 6850

Database connection details will also be included in the emails sent from Ladok. Set the following variables in .env for the database connection string:

LADOK3_USERNAME=xxxxx
LADOK3_PASSWORD=xxxxx
LADOK3_DATABASE=xxxxx
STUNNEL_HOST=localhost
STUNNEL_PORT=11000
STUNNEL_CONFIGURATION_NAME=db2_ufhsk_ENVIRONMENT
STUNNEL_CONFIGURATION_CONNECT_URL=xxxxx

The connection string looks like this:

DATABASE=${process.env.LADOK3_DATABASE};HOSTNAME=${process.env.STUNNEL_HOST};UID=${process.env.LADOK3_USERNAME};PWD=${process.env.LADOK3_PASSWORD};PORT=11000;PROTOCOL=TCPIP

Test data is available in document KIP - Testdata - Förstagångsregistrerade och examinationsgrad.xlsx.

The ibm_db package does not run on newer macs with M1/M2 chipset. If you are using VSCode and want to develop/test locally, you can use the supplied devcontainer-configuration to start the project in a devcontainer.

Tip: npm install (sometimes?) does not work in devcontainer because postinstall-script for ibm_db fails. A workaround is to ignore scripts: npm install --ignore-scripts

If you want to run the docker image, you have to replace line 1 in the Dockerfile with the following:

# FROM ubuntu:22.04 # Replace this
FROM --platform=linux/amd64 ubuntu:22.04 # with this

Make sure that only the line NODE_ENV: 'development' is uncommented Then run docker-compose -f docker-compose.yml up

Running the Docker we do not have to put any secrets into config/secrets and you do not have to configure stunnel locally. Make sure you have all the needed variables in your .env-file, then they are read everytime you run docker-compose up

The needed variables seem to be:

LADOK3_CERT
LADOK3_CERT_KEY
LADOK3_USERNAME
LADOK3_PASSWORD
TUNNEL_CONFIGURATION_NAME
TUNNEL_CONFIGURATION_CONNECT_URL
LADOK3_DATABASE
STUNNEL_HOST
STUNNEL_PORT