SQL Databases

SQL database, used by the CKEditor Collaboration Server needs to meet software and hardware requirements presented in the database requirements article.

The following databases are currently supported:

• PostgreSQL in version 14 (recommended) or 12 (minimal supported version)
• MySQL in version 8.4 (recommended), 8.0 or 5.7 (minimal supported version)

By default, the MySQL database driver is used.
It can be changed by using the DATABASE_DRIVER environmental variables.

Currently, two options are supported:

• postgres - PostgreSQL
• mysql - MySQL (default)

To connect to the database at least the DATABASE_HOST needs to be set.
It is highly recommended to change the default values of the DATABASE_USER and DATABASE_PASSWORD variables.

DATABASE_HOST                            - required
DATABASE_PORT                            - optional (default: 3306)
DATABASE_USER                            - optional (default: "root")
DATABASE_PASSWORD                        - optional (default: "password")
DATABASE_DATABASE                        - optional (default: "cs-on-premises")
DATABASE_SCHEMA                          - optional (default: "cs-on-premises", used for Postgres driver)

• If your database server is configured to require SSL connections, you can use DATABASE_SSL_CA, DATABASE_SSL_KEY and DATABASE_SSL_CERT to pass certificates to the database driver.
• If your database server is configured to require SSL connections and the certificate of the database server is signed by a trusted third party, you can set DATABASE_SSL_ENABLE to true instead of passing the certificates.

DATABASE_SSL_ENABLE                      - optional (default: false)
DATABASE_SSL_CA                          - optional
DATABASE_SSL_KEY                         - optional
DATABASE_SSL_CERT                        - optional

You can provide the certificates in two ways:

To pass the certificate as a file you need to provide the path to it, which is available inside of the application container.

DATABASE_SSL_CA: /path/to/certificate/server-certificate.pem

It may require configuring volumes, to make the certificate available under the path provided to the DATABASE_SSL_CA variable.

Another way of providing the required certificates is by providing them as text.

DATABASE_SSL_CA: "-----BEGIN CERTIFICATE-----\nMIIEBjCCAu6gAwIBAgIJAMc0ZzaSUK51MA0GCSqGSIb3DQEBCwUAMIGPMQswCQYD\nVQQGEwJVUzEQMA4GA1UEBwwHU2VhdHRsZTETMBEGA1UECAwKV2FzaGluZ3RvbjEi\n............\nzPW4CXXvhLmE02TA9/ZeCw3KkHIwicNuEfa=\n-----END CERTIFICATE-----"
DATABASE_SSL_CERT: |
  -----BEGIN CERTIFICATE-----
  MIIEBjCCAu6gAwIBAgIJAMc0ZzaSUK51MA0GCSqGSIb3DQEBCwUAMIGPMQswCQYD
  ...rest_of_certificate_content
  zPW4CXXvhLmE02TA9/ZeCw3KkHIwicNuEfa=
  -----END CERTIFICATE-----
DATABASE_SSL_KEY: LS0tLS1CRUdJTiBDRVJUSUZJQ0FURS0tLS0tCk1JSUVCakNDQXU2Z0F3SUJBZ0lKQU1jMFoKelBXNENYWHZoTG1FMDJUQTkvWmVDdzNLa0hJd2ljTnVFZmE9Ci0tLS0tRU5EIENFUlRJRklDQVRFLS0tLS0=

The DATABASE_SSL_CERT can be provided in three formats:

• as a one-liner, where new lines should be represented by a newline character (\n).
• in the original certificate format using the Literal Block Scalar character (|).
• as a base64 encoded string. It could solve problems related to newline character encoding by cloud hosting platforms.

Various cloud providers e.g. Azure, AWS rotate their root certificates periodically, it may require manual certificate updates by the system administrator to keep the application up and running.

Read a detailed article about the case in the Azure documentation

To ensure high availability of MySQL, we recommend using MySQL Master-Slave Replication with one active master. You can find more information about MySQL replication here.

We recommend using one master because the data in the application is modified quickly and it is often the case that the data between nodes is not synchronized due to replication lag.

In case of a failure, the application will not automatically switch between the nodes, so it is worth looking into a load balancer that will automatically turn the connection between the nodes.

MySQL 8.4 deprecates the mysql_native_password authentication plugin. It is recommended to use the caching_sha2_password plugin instead.
When the database was created before MySQL 8.0, the mysql_native_password plugin was used by default.

In such a case, you will receive the following error:

MySQL Error: Plugin 'mysql_native_password' is not loaded"

To run the application with MySQL 8.4, you need to upgrade the database to use the `caching_sha2_password` plugin.

To migrate already existing users to the `caching_sha2_password` plugin, you can use the following SQL commands:

```sql
SELECT user, host, plugin FROM mysql.user ORDER BY user, host;

-- Switch both root accounts (and any app users still on native) to caching_sha2_password:
ALTER USER 'root'@'localhost' IDENTIFIED WITH caching_sha2_password BY 'password';
ALTER USER 'root'@'%'         IDENTIFIED WITH caching_sha2_password BY 'password';

-- Example for other app user:
-- ALTER USER 'app'@'%' IDENTIFIED WITH caching_sha2_password BY 'STRONG_PASSWORD';

FLUSH PRIVILEGES;

-- Verify none remain on native plugin
SELECT user, host, plugin FROM mysql.user WHERE plugin='mysql_native_password';

Please note that you need to change the password of the user to the password used to connect to the database by the user.

• Read more about SQL Database monitoring.