Database

Basic Setup

You need 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 Secret. This secret is called postgresql-password (if you name it differently, you have to configure it in global.database.secretName in your 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.

Note

The database must run in the UTC timezone (i.e. the timezone, which has no localization) – just like the nodes.

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

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

Database TLS

Since version v2.14.0 it is possible to configure TLS for the database connection. This works by creating secrets in your Namespace and then configuring the global.database.tls section in your 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 for all the details.

For more information about the various settings, check out the PostgreSQL Connection Setup 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 restart services, to ensure they’re picking up the new settings and the database related errors in their logs should be gone.