External database

Business Teams Service (BTS) can be configured with an external database. By default, BTS deploys the integrated EDB PostgreSQL database automatically in the Red Hat OpenShift cluster. You can also configure the external database for the BTS with one of the following methods:

External database
- Configuring an external database with the BTS custom resource
- Configuring an external database with the Kubernetes ConfigMap

Configuring an external database with the BTS custom resource

The BTS custom resource can be used to configure an external database connection for the BTS service. You must specify the spec.databaseConfig parameter in the YAML file to configure the database. For more information about the spec.databaseConfig parameter, see CRD API Reference.

The sample database configuration is as follows:

    apiVersion: operator.ibm.com/v1alpha1
    kind: BusinessTeamsService
    metadata:
      name: bts
      namespace: cp4ba
    spec:
      databaseConfig:
        serverName: mypostgresql.ibm.com
        portNumber: 5432
        databaseName: umsdb02
        userSecretName: postgresql-user
        ssl: "true"
        sslMode: verify-ca
        sslSecretName: postgres-ssl

If you set the databaseConfig parameter, BTS data is hosted in an existing database and the default EDB PostgreSQL installation is not deployed for the BTS installation.

You can configure the databaseConfig parameter with the properties such as serverName, portNumber, and databaseName. The properties define the database endpoint and name that can be used to configure the database. BTS creates the necessary tables automatically in the configured database.

The userSecretName parameter directs to an existing secret in the same namespace where BTS is installed. The following is a sample secret for the userSecretName parameter:

   apiVersion: v1
   kind: Secret
   metadata:
     name: postgresql-user
   type: Opaque
   stringData:
     username: "postgres"
     password: "Passw0rd"

The values of the username and password parameters in the secret are the credentials for login to the database.

SSL configuration

You can enable SSL encryption to create a private communication between the BTS and the database. To enable the SSL encryption, set the ssl: "true" parameter in the yaml file.

Set the values of sslMode parameter with verify-ca or verify-full to validate the certificate of the database. For more information, see PostgreSQL JDBC connection parameters.

Note: If you need to verify the certificate of the database only, set sslMode with verify-ca. If you need to verify the certificate and hostname of the database, set sslMode with verify-full.

The following is a sample configuration with SSL encryption:

    apiVersion: operator.ibm.com/v1alpha1
    kind: BusinessTeamsService
    metadata:
      name: bts
      namespace: cp4ba
    spec:
      databaseConfig:
        serverName: mypostgresql.ibm.com
        portNumber: 5432
        databaseName: umsdb02
        userSecretName: postgresql-user
        ssl: "true"
        sslMode: verify-ca
        sslSecretName: postgres-ssl

The sslSecretName parameter directs to an existing secret in the same namespace where BTS is installed. The following is an example of the secret with the postgres-ssl secret name:

   apiVersion: v1
   kind: Secret
   metadata:
     name: postgres-ssl
   type: Opaque
   stringData:
     ca.crt: |-
       -----BEGIN CERTIFICATE-----
       ...<PEM encoded CA certificate>...
       -----END CERTIFICATE-----
     tls.crt: |-
       -----BEGIN CERTIFICATE-----
       ...<PEM encoded certificate>...
       -----END CERTIFICATE-----

In the example, ca.crt and tls.crt are the keys of the secret that contain the respective certificate data as PEM encoded strings. You can also set only one of the keys and the content of the secret is mounted in the BTS container. ca.crt and tls.crt are the values of the sslrootcert and sslcert parameters of the data source. For more information, see PostgreSQL JDBC connection parameters.

SSL client certificate authentication

BTS can be used with SSL client certificate authentication. The following is a sample configuration with SSL client certificate authentication:

    apiVersion: operator.ibm.com/v1alpha1
    kind: BusinessTeamsService
    metadata:
      name: bts
      namespace: cp4ba
    spec:
      databaseConfig:
        serverName: mypostgresql.ibm.com
        portNumber: 5432
        databaseName: umsdb02
        ssl: "true"
        sslMode: verify-ca
        sslSecretName: postgres-ssl
        customProperties:
        - name: sslkey
          value: "/opt/ibm/wlp/usr/shared/resources/security/db/clientkey.pk8"
        - name: user
          value: postgres

Note: SSL client-certificate authentication method does not require a password and it is not necessary to set the userSecretName parameter. You can specify the sslkey and user custom properties to indicate the location of the client key and name of the user.

The following is an example of the SSL secret postgres-ssl:

   apiVersion: v1
   kind: Secret
   metadata:
     name: postgres-ssl
   type: Opaque
   data:
     ca.crt: ...base64 encoded data...
     tls.crt: ...base64 encoded data...
     clientkey.pk8: ...base64 encoded data...

You can create the secret with the following command:

`oc create secret generic postgres-ssl --dry-run=client --from-file=clientkey.pk8=myclientkey.pk8 --from-file=ca.crt=ca.crt --from-file=tls.crt=tls.crt -oyaml >postgres-ssl.yaml`

The command assumes that the myclientkey.pk8, ca.crt, and tls.crt files are located in the same folder. The command creates a new postgres-ssl.yaml file with the secret in the same folder.

Note: The client key in PEM format needs to be converted to pk8 format before you create the secret. Run the following command to convert the format:

`openssl pkcs8 -topk8 -inform PEM -in myclientkey.pem -outform DER -nocrypt -out myclientkey.pk8`

Custom properties

You can set the custom properties to manage the special configuration cases. The following is an example of the custom properties:

   apiVersion: operator.ibm.com/v1alpha1
   kind: BusinessTeamsService
   metadata:
     name: bts
     namespace: cp4ba
   spec:
     databaseConfig:
       userSecretName: postgresql-user
       customProperties:
       - name: connectTimeout
         value: "5"
       - name: URL
         value: jdbc:postgresql://mypostgresql.ibm.com:5432/umsdb02

For example, the JDBC URL and connectTimeout properties are directly added in the customProperties parameter. It is not necessary to specify the host, port, and database name in the databaseConfig section when you directly specify URL in the customProperties parameter. The value of connectTimeout is set to 5 seconds.

Note: Set the userSecretName parameter in the spec.databaseConfig section to authenticate the database with the credentials in a confidential way. It is not recommended to set the username and password directly in the custom properties.

For more information about the supported customProperties, see PostgreSQL JDBC connection parameters.

Configuring an external database with the Kubernetes ConfigMap

You can use a Kubernetes ConfigMap to configure an external database connection for the BTS. The parameters that are specified in the Kubernetes ConfigMap are the same as in the BTS custom resource. For more information about the parameters, SSL configuration, and custom properties, see Configuring an external database with the BTS custom resource.

The following is a sample ConfigMap:

   apiVersion: v1
   kind: ConfigMap
   metadata:
     name: ibm-bts-config-extension
     namespace: cp4ba
   data:
     serverName: unwon1.fyre.ibm.com
     portNumber: "5432"
     databaseName: umsdb02
     ssl: "true"
     sslMode: verify-ca
     sslSecretName: postgres-ssl
     userSecretName: postgresql-user
     customPropertyName1: connectTimeout
     customPropertyValue1: "5"
     customPropertyName2: URL
     customPropertyValue2: "jdbc:postgresql://unwon1.fyre.ibm.com:5432/umsdb02"

The custom properties are defined in a numbered way such as customPropertyName1, customPropertyValue1, customPropertyName2, and customPropertyValue2 to pass the name and value of the specified custom properties.

For example, the connectTimeout and URL properties are specified in the customPropertyName1 and customPropertyName2 parameters. The values of the connectTimeout and URL properties are specified in the customPropertyValue1 and customPropertyValue1 parameters.