This document is about how to install and configure IBM Business Automation Workflow (BAW) 23.0.2 on premise with PostgreSQL using client certificate authentication.

1. Enable SSL and configure client certificate authentication on PostgreSQL

1. To configure the PostgreSQL database to use encrypted communications and export the server certificate, see Using SSL.

2. To configure your PostgreSQL database server uses certificate authentication, make sure that the database server is configured as described in Using Client Certificates.

3. The following steps are a configuration example.

4. login as root.

5. cd /var/lib/pgsql/15/data

6. Create root crt of PostgreSQL.

   1. openssl req -new -nodes -text -out root.csr -keyout root.key -subj "/CN=TAPALLINONE"

   2. CN must be host name of the PostgreSQL machine.

   3. chmod og-rwx root.key

   4. openssl x509 -req -in root.csr -text -days 3650 -extfile /etc/pki/tls/openssl.cnf -extensions v3_ca -signkey root.key -out root.crt

   5. openssl x509 -in root.crt -out rootnew.crt

   6. modify format of root.crt, make sure it’s a correct X.509 certificate, refer to https://stackoverflow.com/questions/9889669/error-importing-ssl-certificate-not-an-x-509-certificate

   7. mv root.crt root.crt.bak2

   8. mv rootnew.crt root.crt

7. Create server.crt of PostgreSQL

   1. openssl req -new -nodes -text -out server.csr -keyout server.key -subj "/CN=TAPALLINONE"

   2. CN must be host name of the PostgreSQL machine.

   3. chmod og-rwx server.key

   4. openssl x509 -req -in server.csr -text -days 3650 -CA root.crt -CAkey root.key -CAcreateserial -out server.crt

8. Create client crt for BPM

   1. openssl req -new -nodes -text -config /etc/pki/tls/openssl.cnf -out postgresql.csr -keyout postgresql.key -subj "/CN=postgres"

   2. CN must be the db user, that is, bpm.de.authenticationAlias.2.user from BPMConfig property file.

   3. create a client certificate signed by the new root certificate authority.

   4. openssl x509 -req -in postgresql.csr -text -days 3650 -CA root.crt -CAkey root.key -CAcreateserial -out postgresql.crt

   5. convert PEM key to DER format

   6. openssl pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -out postgresql.pk8 -v1 PBE-MD5-DES -nocrypt

9. chown postgres:postgres root.*

10. chown postgres:postgres server.*

11. chown postgres:postgres postgresql.pk8

12. chown postgres:postgres postgresql.crt

13. chown postgres:postgres postgresql.csr

14. chown postgres:postgres postgresql.key

15. Modify /var/lib/pgsql/15/data/postgresql.conf as below.

    1. ssl = on 

    2. ssl_ca_file = '/var/lib/pgsql/15/data/root.crt' 

    3. ssl_cert_file = '/var/lib/pgsql/15/data/server.crt'  

    4. ssl_key_file = '/var/lib/pgsql/15/data/server.key'

16. modify /var/lib/pgsql/15/data/pg_hba.conf, add following contents in section # IPv4 local connections:

    1. hostssl      all    all    0.0.0.0/0     cert

    2. hostssl      all    all    0.0.0.0/0     md5 clientcert=verify-full

    3. hostnossl      all    all    0.0.0.0/0     reject

17. Comment out “host    all             all             127.0.0.1/32            trust”.

18. systemctl restart postgresql-15.service

19. systemctl status postgresql-15.service

20. Check if SSL is enabled.

21. [postgres@TAPALLINONE ~]$ psql -c 'show ssl'

     ssl 

    -----

     on

    (1 row)

22. [root@TAPALLINONE data]# lsof -i:5432

    COMMAND     PID     USER   FD   TYPE DEVICE SIZE/OFF NODE NAME

    postmaste 26841 postgres    6u  IPv4 319973      0t0  TCP *:postgres (LISTEN)

    postmaste 26841 postgres    7u  IPv6 319974      0t0  TCP *:postgres (LISTEN)

2. Install BAW

You may install BAW according to document https://www.ibm.com/docs/en/baw/23.x?topic=workflow-installing-configuring-business-automation.

3. Configure BAW

1. You may configure BAW according to document https://www.ibm.com/docs/en/baw/23.x?topic=workflow-installing-configuring-business-automation.

2. Copy PostgreSQL JDBC driver to BAW machine.

3. Prepare property file of BPMConfig, make sure bpm.de.deferSchemaCreation=true.

4. create an empty directory on BAW machine for bpm.de.messagingEngine.fileStoreDirectory. Sample ommands are as below.

   1. cd /opt/bpm/BPMAutomationROOT/

   2. mkdir mestore

5. Generate DB scripts, sample command is: /opt/ibm/bpm/bin/BPMConfig.sh -create -sqlfiles /root/Downloads/bpmconfig.properties -outputDir /opt/ibm/bpm/dbscripts

6. For bpmdb, run generated DB script. Sample commands are as below.

   1. su - postgres

   2. cd /opt/ibm/bpm/dbscripts/TAPALLINONECell01.BPM/PostgreSQL/bpmdb

   3. psql -U postgres -f dropDatabase.sql

   4. psql -U postgres -f createDatabase.sql

   5. psql -U postgres -f createSchema.sql

   6. modify createSchema_Standard.sql and createProcedure_Standard.sql, add following lines at the beginning of the file.

      1. -- Connect to db

      2. \c bpmdb

      3. SET ROLE postgres;

   7. psql -U postgres -f createSchema_Standard.sql

   8. psql -U postgres -f createProcedure_Standard.sql

7. For pdwdb, run generated DB script. Sample commands are as below.

   1. su - postgres

   2. cd /opt/ibm/bpm/dbscripts/TAPALLINONECell01.BPM/PostgreSQL/pdwdb

   3. psql -U postgres -f dropDatabase.sql

   4. psql -U postgres -f createDatabase.sql

   5. psql -U postgres -f createSchema.sql

   6. vi createSchema_Standard.sql, add following lines at the beginning of the file.

      1. -- Connect to db

      2. \c pdwdb

      3. SET ROLE postgres;

   7. psql -U postgres -f createSchema_Standard.sql

8. For icndb, run generated DB script. Sample commands are as below.

   1. use root user or the user you used to install BAW.

   2. mkdir /opt/bpm/BPMAutomationROOT/postgresql/

   3. cd /opt/bpm/BPMAutomationROOT/postgresql/

   4. mkdir icndb

   5. chown -R postgres:postgres icndb/

   6. su - postgres

   7. cd /opt/ibm/bpm/dbscripts/TAPALLINONECell01.BPM/PostgreSQL/icndb

   8. psql -U postgres -f dropDatabase.sql

   9. vi dropTablespace.sql

   10. file content is as below:

   11. DROP TABLESPACE IF EXISTS POSTGRESDOSDATA;DROP TABLESPACE IF EXISTS POSTGRESTOSDATA;

   12. psql -U postgres -f dropTablespace.sql

   13. ./createDatabase_ECM.sh

   14. psql -U postgres -f "./createSchema_ICN.sql"

   15. psql -U postgres -f createTablespace_Standard.sql

   16. vi createSchema_Standard.sql, add following lines at the beginning of the file.

       1. -- Connect to db

       2. \c icndb

       3. SET ROLE postgres;

   17. psql -U postgres -f createSchema_Standard.sql

9. use root user or the user you used to install BAW.

10. create DE. Sample command is as below.

11. /opt/ibm/bpm/bin/BPMConfig.sh -create -de /root/Downloads/bpmconfig.properties

12. start dmgr.

13. start node agent.

14. Update properties of all datasources.

    1. WAS admin console -> Resources -> JDBC -> data sources -> select a datasource -> Custom Properties -> URL

    2. Change URL to URLs like below, modify it according to your environment:

    3. jdbc:postgresql://TAPALLINONE:5432/bpmdb?sslmode=verify-full&sslrootcert=/var/lib/pgsql/15/data/root.crt&sslcert=/var/lib/pgsql/15/data/postgresql.crt&sslkey=/var/lib/pgsql/15/data/postgresql.pk8

    4. jdbc:postgresql://TAPALLINONE:5432/pdwdb?sslmode=verify-full&sslrootcert=/var/lib/pgsql/15/data/root.crt&sslcert=/var/lib/pgsql/15/data/postgresql.crt&sslkey=/var/lib/pgsql/15/data/postgresql.pk8

    5. jdbc:postgresql://TAPALLINONE:5432/icndb?sslmode=verify-full&sslrootcert=/var/lib/pgsql/15/data/root.crt&sslcert=/var/lib/pgsql/15/data/postgresql.crt&sslkey=/var/lib/pgsql/15/data/postgresql.pk8

    6. After changes are saved, click “test connection“ to make sure connection to DB is successful.

15. Import certificates into WAS.

    1. Stop BAW.

    2. Import the certificate to /jre/lib/security/cacerts key store

       1. /opt/ibm/bpm/java/bin/keytool -import -file /var/lib/pgsql/15/data/root.crt -keystore /opt/ibm/bpm/java/jre/lib/security/cacerts -alias psqlssl

       2. Enter keystore password, default password is changeit

    3. If you are using Java's default mechanism (not LibPQFactory) to create the SSL connection you will need to make the server certificate available to Java, the first step is to convert it to a form Java understands.

       1. cd /var/lib/pgsql/15/data

       2. openssl x509 -in server.crt -out server.crt.der -outform der

       3. /opt/ibm/bpm/java/bin/keytool -keystore $JAVA_HOME/lib/security/cacerts -alias postgresql -import -file /var/lib/pgsql/15/data/server.crt.der

       4. Enter keystore password: changeitRe-enter new password: changeit

    4. chown postgres:postgres root.*

    5. chown postgres:postgres server.*

16. Run bootstrapProcessServerData. Sample commands are as below. 

17. /opt/ibm/bpm/profiles/Dmgr01/bin/bootstrapProcessServerData.sh -clusterName BPM.AppCluster

4. Verify BAW 

Now you've finished configuring BAW functions except case management functions, and you can start BAW to verify your environment. You can verify BAW according to document https://www.ibm.com/docs/en/baw/23.x?topic=workflow-starting-your-environment-verifying-installation.

5. Configure your system for case management

If you need to use case management functions, you may continue configuring it according to document https://www.ibm.com/docs/en/baw/23.x?topic=workflow-configuring-your-system-case-management. If you don't need to use case management functions, you don't need to do it.

6. Verify case management functions

1. Now you've finished configuring BAW case management functions, and you can verify your environment.

2. Restart BAW, including dmgr, node agent and application servers.

3. Verify case management functions. If your environment is production environment, you may refer to document https://www.ibm.com/docs/en/baw/23.x?topic=cpecm-verifying-business-automation-workflow-applications-in-production-environment. If your environment is development environment, you may refer to document https://www.ibm.com/docs/en/baw/23.x?topic=management-verifying-case-applications-in-development-environment.