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

Transparent encrypted SSL connection

This additional configuration property enables the connection to use SSL encryption (connecting a SSL-enabled database):

Listing 3. Unblu PostgreSQL SSL configuration

com.unblu.storage.database.jdbcProperties=\
  sslmode\=verify-ca,\
  sslrootcert\=path/to/root-ca.pem.crt,\ (1)
  sslcert\=path/to/client-cert.pem.crt,\ (1)
  sslkey\=path/to/client-key.pem.pk8 (1)

1	Replace the values with valid paths and filenames

Your certificates must be 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 4. Commands to convert PEM files to PKCS #8

# Create key file
openssl pkcs8 -topk8 \
  -in KEY_FILE.pem -inform pem \
  -out KEY_FILE.pem.pk8 -outform der \ -nocrypt

# Create certificate file
openssl x509 \
  -in CERT_FILE.pem \
  -out CERT_FILE.pem.crt -outform DER

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