Configuring PostgreSQL storage

Decision Intelligence Client Managed Software requires two databases on a PostgreSQL server: one for Decision Designer and the other one for the decision runtime.

1. To use external PostgreSQL instances, you need to initialize the external PostgreSQL instances with roles, databases, and schemas for Decision Designer and the decision runtime.

   You can use the following PSQL script to do so. Run this script when it's connected as the PostgreSQL superuser role, typically the postgres role.

   Modify the passwords in this script with more secure passwords.

   CREATE ROLE adsdesigner WITH INHERIT LOGIN PASSWORD 'designerSecretSample';
   CREATE ROLE adsruntime WITH INHERIT LOGIN PASSWORD 'runtimeSecretSample';
   CREATE DATABASE designerdb WITH OWNER adsdesigner ENCODING 'UTF8';
   CREATE DATABASE runtimedb WITH OWNER adsruntime ENCODING 'UTF8';
   \c designerdb
   CREATE SCHEMA ads AUTHORIZATION adsdesigner;
   \c runtimedb
   CREATE SCHEMA ads AUTHORIZATION adsruntime;
   CREATE EXTENSION pgcrypto SCHEMA ads;

2. To configure external PostgreSQL databases, create one secret for Decision Designer and another secret for the decision runtime to provide credentials for database connection. The names of these secrets are used at a later stage to complete the CR through parameters datasource_configuration.dc_ads_designer_datasource.database_instance_secret and datasource_configuration.dc_ads_runtime_datasource.database_instance_secret.
   Example PostgreSQL credential secret for Decision Designer:

   apiVersion: v1
   kind: Secret
   metadata:
     name: "my-ads-designer-database-credentials"
   type: Opaque
   stringData:
     username: "adsdesigner"
     password: "designerSecretSample"

   Parameter	Description	Mandatory or Optional
   username	Username in the database. It has the owner privileges on the PostgreSQL schema that is specified in the current_schema parameter.	Mandatory
   password	Password for the access in the database. This parameter might be omitted if the PostgreSQL server is configured to be passwordless authentication; for example, if it's based on a TLS client authentication.

   Note:

   • This role is not required to be the PostgreSQL superuser role.
3. Optional: If the connection to the external PostgreSQL server is encrypted with TLS, a certificate authority (CA) must be provided in a secret. If the authentication of the roles that is described earlier is based on TLS client certificates, a certificate and the associated private key must be provided. Two secrets are required: one for Decision Designer and the other one for the decision runtime.
   Sample of TLS database secrets for Decision Designer:

   apiVersion: v1
   kind: Secret
   metadata:
     name: "my-ads-designer-database-tls-secret"
   type: Opaque
   stringData:
     ca.crt: |
       -----BEGIN CERTIFICATE-----
       ...
       -----END CERTIFICATE-----
     tls.crt: |
       -----BEGIN CERTIFICATE-----
       ...
       -----END CERTIFICATE-----
     tls.key: |
       -----BEGIN EC PRIVATE KEY-----
       ...
       -----END EC PRIVATE KEY-----

   Parameter	Description
   ca.crt	CA certificate to be used to validate the TLS certificate that is presented by the PostgreSQL server
   tls.crt	(Optional) TLS certificate for client authentication if it's required by the server.
   tls.key	TLS private key that is associated with the TLS client certificate if it's provided.

4. Install the pgcrypto module inside the current schema. For more information, see PostgreSQL pgcrypto documentation .