9.0

Edit

Database connection encryption

The page 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.

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

Certificates

Connection encryption configuration requires a SSL key pair for the services (MySQL, Postgres, MongoDB).

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

The authentication method is login and password authentication through a SSL connection.

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

Application-level encryption of data in transit

PostgreSQL

  1. Copy certificates to the 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:

    1. Find a string in /opt/workfusion/postgres/data/postgresql.conf:
    #ssl                       = off
    1. Make sure it iss uncommented (Remove the # sign at the beginning of the line), and change "off" to "on", as shown below:
    ssl                       = on
    1. Find a 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

To test PostgreSQL, perform the following steps:

  1. 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 get the following error:

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

  2. Try to connect with SSL from APP Server:

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

MySQL

  1. Copy certificates to the MySQL directory:

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


## Make sure file permissions should be 600 
##HowTodo


chmod 600 /opt/workfusion/mysql/ssl/db.key /opt/workfusion/mysql/ssl/db.cert /opt/workfusion/mysql/ssl/ca.cert

  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

MongoDB

In versions 9.0.x and 9.1.x, the RPA Manager and OCR applications are unable to establish connection with MONGODB via SSL.

Do not perform section below until the fixed RPA Manager and OCR applications are out. The section below describes how to configure MongoDB Server to use SSL.

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

SPA settings

Perform the following steps on DB Server:

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

In APM, a MySQL Major Outage alert appears. To clear a Major Outage issue, do as follows:

On DB server
browse to wfagent/settings folder
cat mysql.yml | grep -i ssl
url: "jdbc:mysql://%mysql_hostname%:%mysql_port%/%mysql_db%?characterEncoding=UTF-8&autoReconnect=true&useSSL=true"
 
Turn on SSL as true and restart wfagent service.
#wfmanger restart wfagent
← PreviousNext →