Configure SSL for PostgreSQL and Platform Analytics Consumer

Communication between the Platform Analytics consumer and a PostgreSQL database can be configured to use SSL for encryption and authentication. For more information, see the PostgreSQL documentation.

Windows
Linux

PostgreSQL Server Side Configuration

You must have OpenSSL version 1.1.0 or later installed.

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 every line because the commands are being executed with the OpenSSL application. If the certificates and keys are being generated on a Unix system, you may need to include openssl before every line.
Copy
```
genrsa -des3 -out server.key 1024
rsa -in server.key -out server.key
```
Create the server certificate:

The OpenSSL application may need to be relaunched 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=IP or HOSTNAME"
```
Open 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:
Copy
```
copy server.crt root.crt
```

Add the following to the postgres.conf file:

Copy

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

Uncomment and change the following:

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

In the pg_hba.conf file, add or modify the following:

You may need to comment out host entries.

hostssl enforces SSL for DB_USERNAME.

clientcert=1 enforces client authentication (two way authentication).
Copy
```
hostssl   platform_analytics_wh   DB_USERNAME   
CLIENT_IP/32   cert clientcert=1
```
Save the changes to both files.
Open Start > Services and restart PostgreSQL or MicroStrategy Repository.

Client Side Setup

Create the private key and certificate:

Copy

genrsa -des3 -out postgresql.key 1024
rsa -in postgresql.key -out postgresql.key
req -new -key postgresql.key -out postgresql.csr -subj "/CN=DB_USERNAME"
x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial

If you receive an error, you may need to comment out tsa_policy1 in the openssl.cnf file. 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 Platform Analytics requires that the key file be in DER format rather than PEM format.
Copy
```
pkcs8 -topk8 -inform PEM -in postgresql.key -outform 
DER -nocrypt -out postgresql.key.der
```
Depending on the ODBC driver being used for PostgreSQL, a key store may be required. To create a key store:
Copy
```
pkcs12 -export -in postgresql.crt -inkey 
postgresql.key -out postgresql.p12
```

Copy the files that were created to the client machine and update the PAConsumerConfig.yaml file with the below path to the certificate and key.

The client key is in DER format.

Copy

pgWarehouseDbConnection:
    pgWhHost: YOUR_HOST
    pgWhUser: DB_USERNAME
    pgWhPasswd: YOUR_PASSWORD
    pgWhPort: 5432
    pgWhDb: platform_analytics_wh
    pgWhSSLcert: \LOCATION_OF_FILE\postgresql.crt
    pgWhSSLkey: \LOCATION_OF_FILE\postgresql.key.der
    pgWhSSLrootcert: \LOCATION_OF_FILE\root.crt
    pgWhSSLmode: verify-ca

PostgreSQL Server Side Configuration

Generate a private key using OpenSSL. You must provide a passphrase when generating a private key:
Copy
```
openssl genrsa -des3 -out server.key 1024
```

Remove the passphrase:

Copy

openssl rsa -in server.key -out server.key

Grant access to the key file.

The permissions on server.key must disable any access to world or group. Do this by setting the chmod permission to 0600. Alternatively, the file can be owned by root and have group read access, that is, chmod 0640 permissions. This setup is intended for installations where certificate and key files are managed by the operating system. The user under which the PostgreSQL server runs should then be made a member of the group that has access to those certificate and key files.

Change the file permissions and owner to the system user running PostgreSQL:
Copy
```
chown PSQL_OWNER:PSQL_OWNER server.key
chmod 0600 server.key
```
If owned by root:
Copy
```
chown root:root server.key
chmod 06040 server.key
```
Create the server certificate and certificate authority:

-subj is a shortcut to avoid prompting for the info.

-x509 produces a self signed certificate rather than a certificate request.
Copy
```
openssl req -new -key server.key -days 3650 -out server.crt -x509 -subj "/CN=IP OR HOSTNAME"
cp server.crt root.crt
```

Do the following for all certificates created:

Copy

chown PSQL_OWNER:PSQL_OWNER server.crt
chmod 0600 server.crt

chown PSQL_OWNER:PSQL_OWNER root.crt
chmod 0600 root.crt

Add the following to the postgres.conf file:

Copy

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

Uncomment and change the following:

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

In the pg_hba.conf file, add or modify the following:

You may need to comment out host entries.

hostssl enforces SSL for DB_USERNAME.

clientcert=1 enforces client authentication (two way authentication).
Copy
```
hostssl   platform_analytics_wh   DB_USERNAME   CLIENT_IP/32   cert clientcert=1
```
Restart PostgreSQL or MicroStrategy Repository.

If PostgreSQL was installed outside of MicroStrategy, use a command like the following:
Copy
```
systemctl restart postgresql-11
```
If MicroStrategy Repository is used for Platform Analytics, use the following command:

This command cannot be run as root.
Copy
```
cd /opt/MicroStrategy/PlatformAnalytics/bin 
./mstr_pg_ctl restart
```

Client Side Setup

Create the private key and certificate:

Copy

openssl genrsa -des3 -out postgresql.key 1024
openssl rsa -in postgresql.key -out postgresql.key
openssl req -new -key postgresql.key -out postgresql.csr -subj "/CN=DB_USERNAME"
openssl x509 -req -in postgresql.csr -CA root.crt -CAkey server.key -out postgresql.crt -CAcreateserial

Convert the private key into DER format using the command below:

The JDBC PostgreSQL driver used by Platform Analytics requires that the key file be in DER format rather than PEM format.
Copy
```
openss1 pkcs8 -topk8 -inform PEM -in postgresql.key -outform DER -nocrypt -out postgresql.key.der
```
Depending on the ODBC driver being used for PostgreSQL, a key store may be required. To create a key store:
Copy
```
openss1 pkcs12 -export -in postgresql.crt -inkey postgresql.key -out postgresql.p12
```

Copy the files that were created to the client machine and update the PAConsumerConfig.yaml file with the below path to the certificate and key.

The client key is in DER format.

Copy

pgWarehouseDbConnection:
    pgWhHost: YOUR_HOST
    pgWhUser: DB_USERNAME
    pgWhPasswd: YOUR_PASSWORD
    pgWhPort: 5432
    pgWhDb: platform_analytics_wh
    pgWhSSLcert: /LOCATION_OF_FILE/postgresql.crt
    pgWhSSLkey: /LOCATION_OF_FILE/postgresql.key.der
    pgWhSSLrootcert: /LOCATION_OF_FILE/root.crt
    pgWhSSLmode: verify-ca