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

• Client-Side Setup

PostgreSQL Server-Side Configuration

1. 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 include openssl before every line.

   Copy

   genrsa -out server.key 1024

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

   Copy

   req -new -key server.key -days 3650 -out server.crt -x509 -subj "/CN=your.domain.com"

3. Open a command prompt or File Explorer and navigate to where the server certificate is located.

4. Copy the newly created server certificate to create the certificate authority.

   Copy

   copy server.crt root.crt

5. Add the line shown below to postgres.conf.

   Copy

   listen_addresses = '*' # what IP address(es) to listen on;

6. Uncomment and modify the code below.

   Copy

   ssl = 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

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

   Copy

   hostssl   mstr_library   DB_USERNAME   CLIENT_IP/32   cert clientcert=1

8. Save your changes in both files.

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

1. Create the private key and the certificate for the mstr_library user:

   Copy

   genrsa -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 -CAcreateserial

   If you receive an error, you may need to comment out tsa_policy1 in openssl.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 

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

   Copy

   pkcs8 -topk8 -inform PEM -outform DER -in mstr_library.key -out mstr_library.pk8 -nocrypt

3. Similar to step 1 and 2, create the private key and certificate for the mstr_library_application user:

   Copy

   genrsa -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

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