Contact usRequest a demo

Setting up PostgreSQL for Unblu

To create the unblu database and users on PostgreSQL, run the following commands:

Listing 1. Create the unblu database and users 
create database unblu;

-- activate database: \c unblu
create user unblu with password '<user-pwd>';
create user unblu_admin with password '<admin-pwd>';
grant usage, create on schema public to unblu_admin;

-- run as unblu_admin
alter default privileges for user unblu_admin in schema public grant select, update, insert, delete on tables to unblu;
alter default privileges for user unblu_admin in schema public grant usage, select on sequences to unblu;

Next, configure Unblu to use your PostgreSQL database. The example configuration below connects to the database unblu and relies on the default schema public, which you don’t need to create.

Listing 2. Unblu PostgreSQL configuration 
com.unblu.storage.database.platform=org.eclipse.persistence.platform.database.PostgreSQLPlatform
com.unblu.storage.database.driver=org.postgresql.Driver
com.unblu.storage.database.url=jdbc\:postgresql\://<server>\:5432/unblu (1)
com.unblu.storage.database.user=unblu
com.unblu.storage.database.password=<user-pwd> (2)
com.unblu.storage.database.adminUser=unblu_admin
com.unblu.storage.database.adminPassword=<admin-pwd> (3)
com.unblu.storage.database.jdbcProperties=sslmode=require,tcpKeepAlive=true,socketTimeout=630,options=-c statement_timeout=600s
com.unblu.storage.database.adminJdbcProperties=sslmode=require,tcpKeepAlive=true,socketTimeout=3630,options=-c statement_timeout=3600s
com.unblu.storage.database.schema=public
com.unblu.storage.database.liquibaseSchema=public
1 Replace <server> with your database hostname or IP address.
2 Replace <user-pwd> with the password of the unblu user.
3 Replace <admin-pwd> with the password of the unblu_admin user.

Configuring the JDBC driver

In addition to setting the configuration properties listed above, you must configure the JDBC connection Unblu Spark uses to connect to the PostgreSQL database. This is done with two configuration properties, com.unblu.storage.database.jdbcProperties and com.unblu.storage.database.adminJdbcProperties, for the unblu user and unblu_admin user, respectively.

The value of each JDBC configuration property consists of a list of connection parameters. Which values you should use depends on your organization’s requirements:

  • A basic configuration of the JDBC driver looks like this:

    Listing 3. Basic JDBC configuration 
    com.unblu.storage.database.jdbcProperties=sslmode=prefer,tcpKeepAlive=true,socketTimeout=630,options=-c statement_timeout=600s
com.unblu.storage.database.adminJdbcProperties=sslmode=prefer,tcpKeepAlive=true,socketTimeout=3630,options=-c statement_timeout=3600s

  • If you want to enforce TLS connections, use sslmode=require instead of sslmode=prefer:

    Listing 4. JDBC configuration that enforces TLS connections 
    com.unblu.storage.database.jdbcProperties=sslmode=require,tcpKeepAlive=true,socketTimeout=630,options=-c statement_timeout=600s
com.unblu.storage.database.adminJdbcProperties=sslmode=require,tcpKeepAlive=true,socketTimeout=3630,options=-c statement_timeout=3600s

  • If you want to verify the server certificate, use sslmode=verify-ca and add the sslrootcert parameter:

    Listing 5. JDBC configuration with server certificate validation 
    com.unblu.storage.database.jdbcProperties=sslmode=verify-ca,sslrootcert=/etc/unblu/ca.crt,tcpKeepAlive=true,socketTimeout=630,options=-c statement_timeout=600s
com.unblu.storage.database.adminJdbcProperties=sslmode=verify-ca,sslrootcert=/etc/unblu/ca.crt,tcpKeepAlive=true,socketTimeout=3630,options=-c statement_timeout=3600s

    Add the server certificate to the ConfigMap of your Collaboration Server deployment:

    Listing 6. ConfigMapGenerator that adds server certificate to Collaboration Server deployment 
    configMapGenerator:
  - name: collaboration-server-config
    behavior: merge
    files:
      - unblu-customer.properties
      - ca.crt

Using client certificates

To use a client certificate when connecting to PostgreSQL, you must also add the sslcert and sslkey parameters.

Listing 7. Basic JDBC configuration with client certificates 
com.unblu.storage.database.jdbcProperties=sslmode=verify-ca,sslrootcert=/etc/unblu/ca.crt,sslcert=/path/to/client.crt,sslkey=/path/to/client_key.pk8,tcpKeepAlive=true,socketTimeout=630,options=-c statement_timeout=600s (1)
com.unblu.storage.database.adminJdbcProperties=sslmode=verify-ca,sslrootcert=/etc/unblu/ca.crt,sslcert=/path/to/client.crt,sslkey=/path/to/client_key.pk8,tcpKeepAlive=true,socketTimeout=3630,options=-c statement_timeout=3600s (1)
1 Replace /path/to/client.crt and /path/to/client_key.pk8 with the paths to the certificate and key files, respectively.

Your certificates must be PEM or binary DER encoded, and the key file must be a binary DER encoded key (for example PKCS #8). You can convert PEM files to the PKCS #8 format as follows:

Listing 8. Convert PEM files to PKCS #8 
openssl pkcs8 -topk8 -nocrypt \
  -in client_key.pem -inform pem \
  -out client_key.pk8 -outform der

For more information on PostgreSQL encryption options, refer to the "Encryption Options" section of the PostgreSQL documentation.

See also

For more information, refer to the PostgreSQL JDBC driver documentation.