.. _database:

Database
========

Basic Setup
-----------

You need :term:`PostgreSQL` version 11 or higher – suggested is version 14.
Setting it up is out of scope for this guide, but there are many ways to install it.

Version 14 should have the least amount of surprises.
Development and testing is done using version 14, and thinking long-term,
this is the best version to start with.
Ideally, you also have something in place, to make this a robust database service.

If you already have a PostgreSQL instance, you could also just create a new "database" and "user".
CoCalc's services only needs to know the IP (or host name),
the username and of course, the password as a :term:`Secret`.
This secret is called ``postgresql-password``
(if you name it differently, you have to configure it in
``global.database.secretName`` in your :ref:`my-values.yaml <my-values.yaml>`).

To create the secret, you can use the following command::

    kubectl [--namespace cocalc] create secret generic postgresql-password --from-literal=postgresql-password=[password]

-  Directory ``/database`` contains some notes about running the Postgres HELM chart from bitnami.
   Could be outdated, hence please refer to the upstream documentation!
-  One eventually important setting is to increase the ``max_connections = 100``.

.. note::

  CoCalc needs pretty extensive permissions to the database (aka "superuser").
  One reason is that upon startup, it checks if the database exists,
  creates tables and schema, later checks up on all tables, indices, etc.


.. _database-timezone:

.. note::

  The database must run in the ``UTC`` timezone (i.e. the timezone, which has no localization) –
  just like the :ref:`nodes <project-node-setup>`.

  To double check if this is the case, connect to the database and run ``show timezone;``::

      cocalc=# show timezone;
      TimeZone
      ----------
      UTC
      (1 row)

.. _db-tls:

Database TLS
------------

Since version ``v2.14.0`` it is possible to configure TLS for the database connection.
This works by creating secrets in your :doc:`./namespace` and then configuring the
``global.database.tls`` section in your :ref:`my-values.yaml <my-values.yaml>`.

A Kubernetes secret like ``cocalc-db-ssl-ca`` containing the CA certificate file
is created from a file called ``ca.pem`` with the following command::

    kubectl [--namespace cocalc] create secret generic cocalc-db-ssl-ca --from-file=ca.pem

The secrets are then used to either mount the files to be read in by CoCalc or to set environment variables.
Look into the source code of the function `sslConfigFromCoCalcEnv in backend/data.ts <https://github.com/sagemathinc/cocalc/blob/master/src/packages/backend/data.ts#L90>`_ for all the details.

For more information about the various settings, check out the `PostgreSQL Connection Setup <https://github.com/sagemathinc/cocalc/wiki/PostgreSQL-Connection-Configurations>`_ documentation.

To actually configure this, the ``global.database.tls`` dict has a few parameters – all of them are optional!
  - ``enabled``: If ``true``, ``SMC_DB_SSL`` is set to ``"true"``. This setting enables SSL connection using the default system certificate store. This means that the certificate presented by the external RDBMS must be in the host OS' trust store. If any of the next four variables are set, it is assumed that SSL is to be enforced and hence implicitly enabled.
  - ``ca``: The name of the secret (e.g. ``cocalc-db-ssl-ca``) containing the CA certificate file ``ca.pem`` (see above). This sets ``SMC_DB_SSL_CA_FILE``.
  - ``cert``: e.g. ``cocalc-db-ssl-cert`` containing ``cert.crt``, setting ``SMC_DB_SSL_CLIENT_CERT_FILE``.
  - ``key``:  e.g. ``cocalc-db-ssl-key`` containing ``key.pem`` setting ``SMC_DB_SSL_CLIENT_KEY_FILE``.
  - ``passphrase``: e.g. ``cocalc-db-ssl-passphrase`` containing ``passphrase.txt`` setting ``SMC_DB_SSL_CLIENT_KEY_PASSPHRASE`` to the passphrase.

Here is a table summarizing the settings:

+---------------------+---------------------------------+--------------------------+----------------------------------------+
| key in tls dict     | value (secret name)             | filename                 | env variable                           |
+=====================+=================================+==========================+========================================+
| ``enabled``         | ``true`` or ``false``           |                          | ``SMC_DB_SSL``                         |
+---------------------+---------------------------------+--------------------------+----------------------------------------+
| ``ca``              | ``cocalc-db-ssl-ca``            | ``ca.pem``               | ``SMC_DB_SSL_CA_FILE``                 |
+---------------------+---------------------------------+--------------------------+----------------------------------------+
| ``cert``            | ``cocalc-db-ssl-cert``          | ``cert.crt``             | ``SMC_DB_SSL_CLIENT_CERT_FILE``        |
+---------------------+---------------------------------+--------------------------+----------------------------------------+
| ``key``             | ``cocalc-db-ssl-key``           | ``key.pem``              | ``SMC_DB_SSL_CLIENT_KEY_FILE``         |
+---------------------+---------------------------------+--------------------------+----------------------------------------+
| ``passphrase``      | ``cocalc-db-ssl-passphrase``    | ``passphrase.txt``       | ``SMC_DB_SSL_CLIENT_KEY_PASSPHRASE``   |
+---------------------+---------------------------------+--------------------------+----------------------------------------+

.. warning::

  The filenames are fixed and must be set exactly as shown in the table above.

  Only the names of the secrets are configurable.

.. note::

    After changing any of these setting, you have to restart ``hub`` and ``manage``
    as explained in :ref:`restart services <troubleshoot-restart-db-services>`,
    to ensure they're picking up the new settings and the database related errors
    in their logs should be gone.