SQL Default Schema

Use case

The backend of the application creates the SQL tables in the database public schema by default. If you want to use a different schema, you need to set it up.

Configuration

You need to setup the DB_SCHEMA variable in /app/instance/config.py:

# Create a file `config.py` in this directory to store specific configuration
# (eg: debug options, database access...)

DEBUG = False

SQLALCHEMY_DATABASE_URI = 'postgresql://aileron:pass@database/aileron'
SQLALCHEMY_ECHO = False
# SQL DB schema to use for all tables (default to 'public' otherwise)
DB_SCHEMA = "deseasion"

API_TOKEN_SECRET_KEY = 'mysecretkey'  # salt for the JWT encryption
API_TOKEN_VALIDITY = 300  # number of seconds
API_REFRESH_VALIDITY = 604800  # validity in seconds (7 days)
API_BASIC_AUTHENTICATION_HEADER = 'Authorization'
API_JWT_AUTHENTICATION_HEADER = 'Authorization'

# External requests settings (e.g for fetching WMS/WFS streams)
#EXTERNAL_REQUEST_TIMEOUT = 10  # timeout in seconds for external requests
#HTTP_PROXY = None  # proxy for external HTTP requests
#HTTPS_PROXY = None  # proxy for external HTTPS requests

CELERY_BROKER_URL = 'redis://redis:6379/0'
CELERY_RESULT_BACKEND = 'redis://redis:6379/0'

PROFILE = False  # activates the profiling of the execution
PROFILE_DIR = '/home/<user>/aileron/profiler'  # directory to save the pstats dump files

FEATURES_CACHE_LIMIT = 200000  # the limit of the features cache table

# path for the ogr2ogr executable
# the application use a slower python fallback if this option is not set
OGR2OGR_BIN = '/usr/bin/ogr2ogr'

# directory for the temporary files
# use the default system directories if this option is not set
#TEMP_DIR = '/var/tmp'

Now you need simply to mount your configuration file in the backend container. Here is how to do it in the docker-compose.yml:

services:
  web:
    image: registry.gitlab.com/decide.imt-atlantique/deseasion/frontend:latest
    restart: always
    ports:
      - 80:80
    depends_on:
      - backend
  backend:
    image: registry.gitlab.com/decide.imt-atlantique/deseasion/backend:latest
    restart: always
    environment:
      - DB_URI=postgresql://aileron:pass@database/aileron
      - CELERY_BROKER_URL=redis://redis:6379/0
    volumes:
      - ./config.py:/app/instance/config.py
    depends_on:
      - database
      - worker
  database:
    image: registry.gitlab.com/decide.imt-atlantique/deseasion/database:latest
    restart: always
    volumes:
      - postgres_data_prod:/var/lib/postgresql/data/
    environment:
      - POSTGRES_USER=aileron
      - POSTGRES_PASSWORD=pass
      - POSTGRES_DB=aileron
  redis:
    image: redis:7.4.2
    restart: always
  worker:
    image: registry.gitlab.com/decide.imt-atlantique/deseasion/worker:latest
    restart: always
    environment:
      - DOCKER_HOST=tcp://docker:2376
      - DOCKER_CERT_PATH=/certs/client
      - DOCKER_TLS_VERIFY=1
    volumes:
      - ./config.py:/app/instance/config.py
      - docker-certs:/certs/client:ro
    depends_on:
      - redis
      - docker
  change-vol-ownership:
    # We can use any image we want as long as we can chown
    image: ubuntu:24.04
    # Need a user priviliged enough to chown
    user: "root"
    volumes:
      # The volume to chown
      - docker-certs:/tmp/change-ownership
    command: chown -R 1000:1000 /tmp/change-ownership
  docker:
    image: docker:28.0.4-dind-rootless
    restart: always
    privileged: true
    environment:
      - DOCKER_TLS_CERTDIR=/certs
    volumes:
      - docker-certs:/certs/client
    depends_on:
      change-vol-ownership:
        # Wait for the ownership to change
        condition: service_completed_successfully
volumes:
  postgres_data_prod:
  docker-certs:

Warning

If you mount the instance/config.py to /app/instance/config.py this way when initializing your application the first time, it will use it verbatim, bypassing the configuration from environment variables. So you better initialize it first with the default installation method, then copy it on your host system and override the proxy config, before changing the docker-compose.yml. Otherwise you’ll use the default placeholder security settings which is not ideal in a production environment.

One way to do it is to simply run the backend container alone with the configuration script:

docker run --rm --entrypoint /app/configure-backend.sh \
  -e DB_URI=postgresql://aileron:pass@database/aileron \
  -e CELERY_BROKER_URL=redis://redis:6379/0 \
  -v ./instance:/app/instance \
  registry.gitlab.com/decide.imt-atlantique/deseasion/backend:latest

This will create the file instance/config.py on your system. You can provide the environment variables for the configuration script with -e options.

Warning

If you already deployed and started using the application stack on the wrong database schema (e.g the default public schema), you need to migrate the tables in the new schema before changing the configuration. For that, with your original configuration intact, run the following command:

docker compose exec backend flask db migrate-schema <new schema name>

Then, modify the /app/instance/config.py file to use your new SQL schema, and restart the whole application stack (or at least the backend and worker containers).