MicroStrategy ONE
Configure SSL for PostgreSQL and the Storage Service
Communication between the Microstrategy Storage service and a PostgreSQL database can be configured to use SSL for encryption and authentication. See the PostgreSQL documentation for more information.
PostgreSQL Server-Side Configuration
You must have OpenSSL version 1.1.0 or later installed.
If you already have PostgreSQL server-side configured for Platform Analytics or Collaboration Service, skip to Client-Side Setup by using a generated server.key
and root.crt
-
Run the OpenSSL application as an administrator to generate a private key. You must provide a passphrase when generating the private key.
openssl
is not included at the beginning of each line since the commands are executed with the OpenSSL application. If the certificates and keys are being generated on a Unix system or Windows command line, you may need to includeopenssl
before every line.Copygenrsa -out server.key 1024
-
Create the server certificate.
The OpenSSL application may need to be re-launched to successfully create the server certificate.
-subj
is a shortcut to avoid prompting for information.-x509
produces a self-signed certificate rather than a certificate request.Copyreq -new -key server.key -days 3650 -out server.crt -x509 -subj "/CN=your.domain.com"
-
Open a command prompt or File Explorer and navigate to where the server certificate is located.
-
Copy the newly created server certificate to create the certificate authority.
Copycopy server.crt root.crt
-
Add the line shown below to
postgres.conf
.Copylisten_addresses = '*' # what IP address(es) to listen on;
-
Uncomment and modify the code below.
Copyssl = on
ssl_ca_file = '\\LOCATION_OF_FILE\\root.crt'
ssl_cert_file = '\\LOCATION_OF_FILE\\server.crt'
ssl_key_file = '\\LOCATION_OF_FILE\\server.key'
ssl_ciphers = 'HIGH:MEDIUM:+3DES:!aNULL' # allowed SSL ciphers
ssl_prefer_server_ciphers = on -
In
pg_hba.conf
, add or modify the code below.You may need to comment out host entries.
hostssl
enforces SSL for DB_USERNAME.clientcert=1
enforces client authentication (two way authentication).Copyhostssl mstr_library DB_USERNAME CLIENT_IP/32 cert clientcert=1
-
Save your changes in both files.
-
Choose Start > Services and restart PostgreSQL or MicroStrategy Repository.
Client-Side Setup
After finishing the PostgreSQL server-side configuration, you can start client-side configuration and setup. This generates certificates and keys for the Storage service.
You must create two pairs of keys and certificates for the mstr_library
user and the mstr_library_application
user.
-
Create the private key and the certificate for the
mstr_library
user:Copygenrsa -out mstr_library.key 1024
req -new -key mstr_library.key -out mstr_library.csr -subj "/CN=mstr_library"
x509 -req -in mstr_library.csr -CA root.crt -CAkey server.key -out mstr_library.crt -CAcreateserialIf you receive an error, you may need to comment out
tsa_policy1
inopenssl.cnf
. Save and relaunch openssl as an administrator.Copy# Policies used by the TSA examples.
#tsa_policy1 = 1.2.3.4.1
tsa_policy2 = 1.2.3.4.5.6
tsa_policy3 = 1.2.3.4.5.7 -
Convert the private key into DER format using the command below.
The JDBC PostgreSQL driver used by Storage service requires the key file to be in DER format, rather than PEM format.
Copypkcs8 -topk8 -inform PEM -outform DER -in mstr_library.key -out mstr_library.pk8 -nocrypt
-
Similar to step 1 and 2, create the private key and certificate for the
mstr_library_application
user:Copygenrsa -out mstr_library_application.key 1024
req -new -key mstr_library_application.key -out mstr_library_application.csr -subj "/CN=mstr_library_application"
x509 -req -in mstr_library_application.csr -CA root.crt -CAkey server.key -out mstr_library_application.crt -CAcreateserial
pkcs8 -topk8 -inform PEM -outform DER -in mstr_library_application.key -out mstr_library_application.pk8 -nocrypt -
Copy the files that were created on the Library server and update
configOverride.properties
with the absolute path to the certificate and key.The
configOverride.properties
file can be found in:Windows:
<DEPLOY_LOCATION>\webapps\MicroStrategyLibrary\WEB-INF\classes\config\ConfigOverride.properties
Linux:
<DEPLOY_LOCATION>/webapps/MicroStrategyLibrary/WEB-INF/classes/config/configOverride.properties
Copy#postgresql database connection config
datasource.url=jdbc:postgresql://YOUR_HOST:5432/mstr_library
datasource.tlsEnabled=true
datasource.sslMode=require
datasource.sslRootCert=/LOCATION_OF_FILE/root.crt
#for Windows: datasource.sslRootCert=Z:\LOCATION_OF_FILE\root.crt
datasource.username=mstr_library_application
datasource.password=<any string>
datasource.sslCert=/LOCATION_OF_FILE/mstr_library_application.crt
datasource.sslKey=/LOCATION_OF_FILE/mstr_library_application.pk8
datasource.admin.username=mstr_library
datasource.admin.password=<any string>
datasource.admin.sslCert=/LOCATION_OF_FILE/mstr_library.crt
datasource.admin.sslKey=/LOCATION_OF_FILE/mstr_library.pk8