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

  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