Setting up PostgreSQL for Unblu
To create the unblu
database and users on PostgreSQL, run the following commands:
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.
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 configurationcom.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 ofsslmode=prefer
:Listing 4. JDBC configuration that enforces TLS connectionscom.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 thesslrootcert
parameter:Listing 5. JDBC configuration with server certificate validationcom.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 deploymentconfigMapGenerator: - 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.
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:
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.