9.4

Edit

Database connection encryption

General

This document provides the guidelines on database connection encryption for MySQL, PostgreSQL, MongoDB using SSL certificates. SSL certificates solve two problems: traffic encryption and verification of trust. What certificates to use: Connection encryption configuration requires an SSL key pair for the services (MySQL, Postgres, MongoDB). Authentication method: Classic login/password authentication through SSL connection.

Document terms

Predefined common variables:

  • WorkFusion user. Default is wfuser.
  • WorkFusion Home Directory. Default is /opt/workfusion.

In the guide, we will use certificates located on DB server in the /nginx/ssl folder.

User values should be entered whenever you see the [ ] placeholder.

Application-level encryption of data in transit

PostgreSQL

PostgreSQL server configuration:

  1. SSH to DB server and switch to <WF_USER>, for example:

    sudo su - wfuser

  2. Copy certificates to PostgreSQL data directory and make sure the permissions for the files are set to 600.

    cp /opt/workfusion/nginx/ssl/db.key /opt/workfusion/postgres/data/server.key
cp /opt/workfusion/nginx/ssl/db.crt /opt/workfusion/postgres/data/server.crt
cp /opt/workfusion/nginx/ssl/ca.crt /opt/workfusion/postgres/data/root.crt


chmod 600 /opt/workfusion/postgres/data/server.key
chmod 600 /opt/workfusion/postgres/data/server.crt
chmod 600 /opt/workfusion/postgres/data/root.crt

  3. Enable SSL support in PostgreSQL config files.

    1. Find the string in /opt/workfusion/postgres/data/postgresql.conf.
    #ssl                       = off

    Make sure it's uncommented (Remove the ## sign at the beginning of the line), and change "off" to "on", as shown below:

    ssl                       = on
    1. Find string in /opt/workfusion/postgres/data/pg_hba.conf.
    host    all             all             0.0.0.0/0                md5
    1. Replace it with:
    hostssl    all             all             0.0.0.0/0                md5

  4. Restart PostgreSQL.

    wfmanager restart postgresql

  5. Modify mysql_server foreign server settings (foreign data wrapper for mysql). Specify $workfusion_analytics_user username and path to root certificate.

    psql -U analytics -d wf_datastore -c "alter server mysql_server OPTIONS (ssl_ca '/opt/workfusion/postgres/data/root.crt');"

    -U analytics: default $workfusion_analytics_user username

  6. Try to connect without SSL from APP Server.

    1. SSH to the APP server and switch to <WF_USER>.
    2. Execute the following:
    psql "sslmode=disable host=[db_hostname]"

    As we disabled non-ssl connections, we should get following error:

    psql: FATAL: no pg_hba.conf entry for host " dbhost_ip_adress ", user "wfuser", database "wfuser", SSL off

  1. Try to connect with SSL from APP Server:

    psql "sslmode=require host=[db_hostname] dbname=wf_datastore" -U [workfusion]

    where [workfusion] is workfusion_datastore_user of PostgreSQL DB.

It’s ok now. Serverside SSL configuration for postgresql is completed.

MySQL

MySQL server configuration is as follows:

  1. SSH to DB server and switch to <WF_USER>, e.g.

    sudo su - wfuser

  2. Copy certificates to the mysql directory.

    cp -r /opt/workfusion/nginx/ssl /opt/workfusion/mysql

  3. Add the following strings in the mysql configuration /opt/workfusion/mysql/usr/my.cnf.

    [mysqld]
require_secure_transport=ON
ssl-ca=/opt/workfusion/mysql/ssl/ca.crt
ssl-cert=/opt/workfusion/mysql/ssl/db.crt
ssl-key=/opt/workfusion/mysql/ssl/db.key

  4. Restart MySQL.

    wfmanager restart mysql

  5. Connect to MySQL with SSL from APP Server.

    1. SSH to the APP server and switch to <WF_USER>.
    2. Execute the following:
    mysql -h [db_hostname] -u [workfusion] -p --ssl
MySQL [(none)]> status;
SSL: Cipher in use is DHE-RSA-AES256-SHA

As we can see SSL for MySQL was successfully configured.

MongoDB

note

In versions 9.0.x-9.2.x, the rpa-manager and OCR applications are unable to establish connection with MONGODB via SSL.

caution

Do not perform section below until fixed rpa-manager and OCR applications are out.

Section below describes how to configure MongoDB server to use SSL.

  1. SSH to DB server and switch to <WF_USER>, e.g.

    sudo su - wfuser

    MongoDB uses only one file that holds certificate and serverkey.

    cd /opt/workfusion/mongodb/etc
cat /opt/workfusion/nginx/ssl/db.key /opt/workfusion/nginx/ssl/db.crt > /opt/workfusion/mongodb/etc/db.pem

  2. Add following section to MongoDB configuration in the /opt/workfusion/mongodb_db/etc/mongodb.conf file to enable SSL support.

    net:
  ssl:
    mode: requireSSL
    PEMKeyFile: /opt/workfusion/mongodb/etc/db.pem

  3. Restart MongoDB.

    wfmanager restart mongodb

  4. Check that SSL works:

    1. SSH to the APP server and switch to <WF_USER>.
    2. Execute the following:
    mongo --ssl --sslAllowInvalidCertificates --host [db_hostname]:27016

WorkFusion settings

The following steps should be performed on DB server.

  1. Fill the next variables and execute in shell:

    export DB_HOSTNAME=< YOUR_DB_SERVER_HOSTNAME_HERE>
export MYSQL_PORT=<YOUR_MYSQL_PORT_HERE>
export POSTGRESQL_PORT=<YOUR_POSTGRESQL_PORT_HERE>

  2. The next commands will create a file with SSL properties which will be uploaded to VAULT with loader.sh.

    cd /opt/workfusion/wf-sec-storage/

cat << EOF > /opt/workfusion/wf-sec-storage/config-ssl.properties
mturkds.database.url=jdbc:mysql://$DB_HOSTNAME:$MYSQL_PORT/wfdb?noAccessToProcedureBodies=true&useLegacyDatetimeCode=false&noAccessToProcedureBodies=true&serverTimezone=UTC&rewriteBatchedStatements=true&failOverReadOnly=false&verifyServerCertificate=false&useSSL=true 
wf.datastore.database.url=jdbc:postgresql://$DB_HOSTNAME:$POSTGRESQL_PORT/wf_datastore?targetServerType=master&sslmode=require&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
tableau.db.url=jdbc:postgresql://$DB_HOSTNAME:$POSTGRESQL_PORT/wfml_automation?targetServerType=master&sslmode=require&ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory
EOF
./loader.sh workfusion config-ssl.properties

The following steps should be performed on APP server.

  1. Change the datasource URL in /opt/workfusion/wfbi/conf/Catalina/localhost/service-wfbi.xml.

    url="jdbc:postgresql://db_hostname:postgresql_port/wfml_automation?targetServerType=master&amp;useSSL=true&amp;sslmode=require&amp;ssl=true"

  2. Change the datasource URL in /opt/workfusion/workspace_sandbox/conf/workspace-sandbox.properties.

    dataSource.url=jdbc:postgresql://hostname:port/virtualizer_sandbox?targetServerType=master&sslmode=require&ssl=true

  3. Change the datasource URL in /opt/workfusion/workspace_production/conf/workspace.properties.

    dataSource.url=jdbc:postgresql://hostname:port/virtualizer?targetServerType=master&&sslmode=require&ssl=true

  4. Change the datasource URL in /opt/workfusion/sqc/conf/Catalina/localhost/sqc-rest.xml.

    url="jdbc:mysql://db_hostname:mysql_port/sqc?useLegacyDatetimeCode=false&amp;serverTimezone=UTC&amp;rewriteBatchedStatements=true&amp;autoReconnect=true&amp;useSSL=true"

  5. Restart workfusion applications on APP server.

    wfmanager restart all
← PreviousNext →