9.1

Edit

Database connection encryption

General

The 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 SSL key pair for the services (MySQL, Postgres, MongoDB).

In the guide, we will use certificates located on DB server in the Nginx SSL folder.

Authentication method:

Classic login/password authentication through SSL connection.

Terminology

The approach described in this document is valid for WorkFusion SPA 9.0.X and 9.1.X.

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

Application-level encryption of data in transit

PostgreSQL

  1. 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

  2. Enable SSL support in postgresql config files.

    • 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
    • Find the string in /opt/workfusion/postgres/data/pg_hba.conf.
    host    all             all             0.0.0.0/0                md5
    • Replace it with:
    hostssl    all             all             0.0.0.0/0                md5

  3. Start the server and try to connect without SSL from APP Server:

    psql "sslmode=disable host=db_hostname"

    As you disabled non-SSL connections, you should get the following error:

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

  4. Try to connect with SSL from APP Server:

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

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

MySQL

  1. Copy certificates to the MySQL directory.

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

  2. 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

  3. Restart MySQL.

    wfmanager restart mysql

  4. Connect to MySQL with SSL from APP Server.

    mysql -h db_hostname -u workfusion -p --ssl
MySQL [(none)]> status;
SSL: Cipher in use is DHE-RSA-AES256-SHA

    SSL for MySQL is successfully configured.

MongoDB

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

Do not perform the instructions in the section below until fixed rpa-manager and OCR applications are out. The section describes how to configure mongodb server to use SSL.

The MongoDB server configuration is as follows:

  1. 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 the 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.

  4. Check that SSL works.

    mongo --ssl --sslAllowInvalidCertificates --host db_hostname:27016

WorkFusion settings

Perform the following steps 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. Run the next commands to create a file with SSL properties that 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

Perform the following steps 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 →