Add config to Kolibri's `options.ini` for PostgreSQL with SSL

[bjester]

❌ This issue is not open for contribution. Visit Contributing guidelines to learn about the contributing process and how to find suitable issues.

Target branch: release-v0.19.x

Current behavior

Currently, you can configure various settings for Kolibri through its options.ini configuration. This includes some settings for choosing its database backend (SQLite vs Postgres). These settings mostly passthrough to Django's settings. The options.ini therefore provides a simple interface for common configuration use cases for Kolibri's end users.

For more advanced use cases, Kolibri's Django settings can be overridden. This is preferred for optimal flexibility in more advanced use cases. It is applied through use of the DJANGO_SETTINGS_MODULE environment variable, set with a module path to an importable settings file like:

from kolibri.deployment.default.settings.base import *

# custom settings or modifications

With the aforementioned framing, it is more common now to configure Postgres with secure (SSL) connections. Currently, this is accomplished with the latter approach of overridding Django settings.

Desired behavior

Since it very common now to configure Postgres with a secure connection, we'd like to add a couple options to allow configuration of SSL connections for Kolibri's Postgres database connections.

New options

Under the [Database] section, the following options should be added:
a. DATABASE_SSL_MODE

• Type: option
• Allows: disable, allow, prefer, require, verify-ca, or verify-full
• Default: disable
• Description: The sslmode for the connection, Postgresql only.

b. DATABASE_SSL_ROOT_CERT

• Type: path
• Allows: A file path
• Default: ""
• Description: Path to the SSL certificate authority (CA) file for verifying the server certificate (maps to sslrootcert), Postgresql only.

Configuration

In Kolibri's Django settings for a postgres database connection, it should:

• Build an OPTIONS dictionary for SSL settings when DATABASE_SSL_MODE is not disable
  • Set sslmode in OPTIONS to the value of DATABASE_SSL_MODE
  • Set sslrootcert in OPTIONS to the value of DATABASE_SSL_ROOT_CERT if not empty (with proper whitespace stripping)
• Apply SSL OPTIONS to both database connections:
  • "default" connection: Apply SSL OPTIONS directly
  • "default-serializable" connection: Merge SSL OPTIONS with the existing {"isolation_level": isolation_level} dict, preserving the isolation_level setting
• Handle empty/whitespace-only certificate paths gracefully (skip setting sslrootcert if the path is empty or contains only whitespace)

Testing

Appropriate coverage should be implemented in alignment with existing test coverage on options.py and how those are handled.
https://github.com/learningequality/kolibri/blob/develop/kolibri/utils/tests/test_options.py

CI/CD Testing

Add a new testing config to tox.ini:

[testenv:postgres-ssl]
# Same as postgres but with SSL env vars set
passenv =
    TOX_ENV
    INTEGRATION_TEST
setenv =
    {[testenv:postgres]setenv}
    KOLIBRI_DATABASE_SSL_MODE = require
basepython =
    postgres-ssl: python3.9
deps = {[testenv:postgres]deps}
commands =
    # smoke test, runs Morango serialization
    python -O -m pytest kolibri/core/public/test/test_api.py -k SyncQueueViewSetDeletedUsersTestCase --color=no

Also add postgres-ssl to the envlist.

Then add a new test job to the .github/workflows/tox.yml:

   postgres_ssl:
     name: Python postgres SSL smoke tests
     needs: pre_job
     runs-on: ubuntu-latest
     services:
       postgres:
         image: postgres:12
         env:
           POSTGRES_USER: postgres
           POSTGRES_PASSWORD: postgres
           POSTGRES_DB: test
         options: >-
           --health-cmd pg_isready
           --health-interval 10s
           --health-timeout 5s
           --health-retries 5
         ports:
           - 5432:5432
         command: >-
           -c ssl=on
           -c ssl_cert_file=/etc/ssl/certs/ssl-cert-snakeoil.pem
           -c ssl_key_file=/etc/ssl/private/ssl-cert-snakeoil.key
    steps:
    - uses: actions/checkout@v6
    - name: Set up Python 3.9 for Postgres
      if: ${{ needs.pre_job.outputs.should_skip != 'true' }}
      uses: actions/setup-python@v6
      with:
        python-version: 3.9
    - name: Install tox
      if: ${{ needs.pre_job.outputs.should_skip != 'true' }}
      run: |
        python -m pip install --upgrade pip
        pip install "tox<4"
    - name: tox env cache
      if: ${{ needs.pre_job.outputs.should_skip != 'true' }}
      uses: actions/cache@v5
      with:
        path: ${{ github.workspace }}/.tox/py3.9
        key: ${{ runner.os }}-tox-py3.9-${{ hashFiles('requirements/*.txt') }}
     - name: Test with SSL enabled (require mode)
       run: tox -e postgres-ssl

Value add

Allows users to configure a secure postgres database connection in Kolibri's options.ini without overridding the Django settings, which has some challenges in making sure Django can import the overridden settings.

Possible tradeoffs

An arbitrary setting for any OPTIONS was considered, but if users really need more flexibility, we should direct them to the more advanced DJANGO_SETTINGS_MODULE approach.

AI usage

AI was used to review this plan for its accuracy, and planned most of the CI/CD testing integration.

[jaltekruse]

@bjester I would be up for working on this, the combination of context from our slack discussion and the thorough task description I think give me a reasonably likelihood of success.