# SQL Databases

<a id="requirements">

## Requirements

SQL database, used by the CKEditor Collaboration Server needs to meet software and hardware requirements presented in the [database requirements](../../requirements.md#databases) article.

<a id="supported-sql-databases">

## Supported SQL databases

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)

<a id="driver-selection">

## Driver selection

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)

<a id="connection-and-basic-credentials">

## Connection and basic credentials

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.

```bash
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)
```

<a id="tls-connection-and-certificates">

## TLS connection and certificates

* 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.

```bash
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:

<a id="passing-certificates-as-files">

### Passing certificates as files

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

```yaml
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.

<a id="passing-certificates-as-text">

### Passing certificates as text

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

```yaml
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.

<a id="root-certificates-rotation">

### Root certificates rotation

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](https://learn.microsoft.com/en-us/azure/mysql/single-server/concepts-certificate-rotation)

<a id="mysql-high-availability">

## MySQL High availability

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](https://dev.mysql.com/doc/refman/5.7/en/replication.html).

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.

<a id="mysql-upgrading-to-version-84">

## MySQL upgrading to version 8.4

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:

````bash
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.

<a id="next-steps">

## Next steps

* [Read more about SQL Database monitoring](../../resources.md).

---

Full index of the Cloud Services documentation: [llms.txt](../../../../../llms.txt)