Connect to oracle db from openshift for Nodejs Application

This document lists the steps to config openshift to enable the connection to oracle db using Nodejs, followed by the example from NRIS team and the example from Zero team

1. Generate a csr file and send to DBA

Run the following command

keytool -genkeypair -keyalg RSA -keysize 2048 -validity 3650 -dname "CN=NRFC,O=bcgov" -ext "SAN=email:[email protected],URI:urn:apps.nrs.gov.bc.ca:nrfc,URI:urn:storage:openshift" -storetype PKCS12 -keystore keystore.p12 -alias orakey

keytool -certreq  -keyalg RSA -file cert.csr -keystore keystore.p12 -alias orakey

CN is common name, could use the abbreviation of the project name. Keystore password is the default one “changeit”. More info about keytool command options is here

2. DBA provides signed certificates .crt files

A root certificate, a chain certificate, a user certificate and a server certificate

3. Create the wallet using the signed certificates

There are two ways to create the wallet, the first one is using the Oracle Fusion middleware (need a full oracle fusion middleware install to get the orapki command line tool), the second one is using the SQLCL (light install with some java scripts to simulate the behavior of orapki command line tool)

3.1 Method one: using the Oracle Fusion middleware

• Download Oracle Fusion middleware to get orapki command line tool

• Install java if don’t have

• Open a terminal go to the download folder, run java -jar fmw_14.1.1.0.0_wls_lite_quick_generic.jar to install the oracle fusion middle ware, this will create a ‘wls1411’ folder and have all the files in it

• Check where the java runtime is, for Mac user, run /usr/libexec/java_home, for example it returns ‘/Library/Java/JavaVirtualMachines/jdk-11.0.15.1.jdk/Contents/Home’

• Set the java home env variable, run export JAVA_HOME=/Library/Java/JavaVirtualMachines/jdk-11.0.15.1.jdk/Contents/Home, this will set the env variable works temporarily, so if open a new terminal window, need to rerun this

• Run the following command under the the directory /Downloads/wls1411/oracle_common/bin/

  # Create a wallet  
  ./orapki wallet create -wallet ./wallet -auto_login_only  
  
  # Add a trusted certificate to the wallet  
  ./orapki wallet add -wallet ./wallet -trusted_cert -cert /Users/cmeng/Downloads/root.crt -auto_login_only  
  
  # Add a server certificate  
  ./orapki wallet add -wallet ./wallet -trusted_cert -cert /Users/cmeng/Downloads/server.crt -auto_login_only  
  
  # Add a chain certificate  
  ./orapki wallet add -wallet ./wallet -trusted_cert -cert /Users/cmeng/Downloads/chain.crt -auto_login_only
  
  # To display the wallet content
  ./orapki wallet display -wallet ./wallet -auto_login_only
  

3.2 Method two: using SQLCL

• Install SQLCL for pre-requisite libs

• Install java if don’t have

• For Mac user, create a orapki.sh file with the following content, update the 'SQLCL=' line to be the directory of the lib folder in the sqlcl folder, for example "/Users/username/Downloads/sqlcl/lib". For Windows user, use the orapki.bat file from the nris team example. For Linux user, check the discussion under this post

  #!/bin/bash  
  # set classpath for orapki - align this to your local SQLcl installation. 
  SQLCL=[directory of sqlcl folder]  
  CLASSPATH=${SQLCL}/oraclepki.jar:${SQLCL}/osdt_core.jar:${SQLCL}/osdt_cert.jar  
  # simulate orapki command  
  java -classpath ${CLASSPATH} oracle.security.pki.textui.OraclePKITextUI "$@"  
  

• Use the orapki file to create the wallet, suppose the orapki.sh and certificates are in the same directory. The following is the example for Mac user. For Windows user, check the nris example

  bash ./orapki.sh wallet create -wallet ./wallet -auto_login_only
  bash ./orapki.sh wallet add -wallet ./wallet -trusted_cert -cert root.crt -auto_login_only  
  bash ./orapki.sh wallet add -wallet ./wallet -trusted_cert -cert chain.crt -auto_login_only  
  bash ./orapki.sh wallet add -wallet ./wallet -trusted_cert -cert server.crt -auto_login_only  
  

4. Upload wallet to openshift and config container

• Login to the namespace
• Create a configmap called ora-wallet (could use any name) to store the wallet oc create configmap ora-wallet --from-file=./wallet, --from-file is from the directory where the wallet is at
• In the backend docker file, add a step to create a empty wallet directory cd /opt/oracle/ && mkdir wallet, this could be anywhere
• In the backend container yaml file, add the volume claim for the config map, and mount the volume to the wallet directory created above. So the certificate wallet in the configmap will be copied over to the expected location

  spec:
    volumes:
      - name: ora-wallet-volume
        configMap:
          name: ora-wallet
    containers:
      - volumeMounts:
          - name: ora-wallet-volume
            mountPath: "/opt/oracle/wallet/"
  

• Inside the sqlnet.ora file, set the wallet location to be the same as above. Could check the sqlnet.ora file in our repo, locates at backend/src/sqlnet.ora

  WALLET_LOCATION=(SOURCE=(METHOD=FILE)(METHOD_DATA=(DIRECTORY="/opt/oracle/wallet")))
  

• In the backend docker file, copy the sqlnet.ora file to the 'network/admin' directory under where the oracle instant client is installed cp sqlnet.ora /opt/oracle/instantclient*/network/admin/. Could check the docker file in our repo locates at backend/Dockerfile

Note for running application locally

• If run the application locally, the sqlnet.ora file need to be copied to where the oracle instalt client is installed locally, for example under /Users/username/Downloads/instantclient_19_8/network/admin
• Also the wallet location in the sqlnet.ora needs to be the place where the wallet is stored locally