Configuring PostgreSQL storage

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

1. Initialize the 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 the script with more secure passwords.

   CREATE ROLE adsdesigner WITH INHERIT LOGIN PASSWORD 'sampleDesignerPassword';
   CREATE ROLE adsruntime WITH INHERIT LOGIN PASSWORD 'sampleRuntimePassword';
   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. Create two database secrets to provide the credentials for PostgreSQL roles to connect to the databases: one role for Decision Designer to connect to the PostgreSQL database, and the other role for the decision runtime.

   The names of these secrets are referenced in the custom resource file at a later stage through parameters spec.decision_designer.database.secret_name and spec.decision_runtime.database.secret_name.

   Example of database secrets:

   apiVersion: v1
   kind: Secret
   metadata:
     name: "my-designer-database-secret"
   type: Opaque
   stringData:
     username: "adsdesigner"
     password: "sampleDesignerPassword"

   Parameter	Description
   username	Username in the database. It has the owner privileges on the PostgreSQL schema that is specified in the spec.decision_designer.database.current_schema parameter.
   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 from 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.
   Sample of TLS database secrets:

   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	TLS certificate for client authentication if required by the server.
   tls.key	TLS private key that is associated with the TLS client certificate if provided.

4. Install the pgcrypto module inside the current schema. For more information, see https://www.postgresql.org/docs/current/pgcrypto.html .