External database

Edit online

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

Edit online

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.

The database admin can create the BTS database with CREATE DATABASE btsdb. BTS creates the necessary tables automatically when you configured database. The database user is required to have the CREATE privilege on the schema.

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

Edit online

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

Edit online

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

Edit online

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

Edit online

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.