Question - Database update (Docker)

Hi team,

I’d like to get some context about how automated updates are performed on Eramba.
I’m using Eramba within a Docker’s container, to apply updates I can rebuild the image with the new version of the code-base, but I may need to run queries in order to updates the DB schema as well.
I have not found any db_schema_updates.sql within the Eramba source code. Can you please explain me how the database schema is updated during normal updates?
I will manage to make it works with Docker as well.

Thank you in advance,

I cant speak much about Docker because to be honest we know next to nothing about it. We dont know anyone using it with eramba either (some people did experiment with it tough).

eramba “updater” (system / settings / updates) does many things … the updater will: download a compressed file, untar it, modify php files, re-run cache processes, run db schema changes, move data around, run some specific processes and logout users.

there is no cli command that does all that

In case instead of applying updated in this way, I would manually untar the new archive (that happen during the rebuild of the docker image) into a new webroot… can I update the db schema changes by running a dedicated sql file, and expect the new version works properly?
Which is the file designate to apply the db schema changes?

Here is my dockerfile.

nope - there is no cli that can do what the web-interface does

i noticed a few things:

  • your cron settings need to be run by the apache process not by root
  • im not sure were you deploy the code from (latest…), but if you are in version 2.5.0 and you are attempting an update to 2.5.1 you need 2.5.0 of course…and when you do 2.5.2 you will need 2.5.1
  • the script misses the concept of updater as i said, there is no cli for that

i frankly speaking dont see the point of doing things this way on this type of application. perhaps is very useful when an app code is independent of the data and you have many nodes, but this is clearly not the case.

Hi team,

Thanks for your quick reply.

You are right, it was run from www-data user, you can see it from this previous commit. I then changed it to make some tests. I will revert it to www-data, thanks.

I put the code into the directory called enterprise. Of course I do that only on my side, since I don’t want to store the Eramba enterprise code-base in any public place.

That’s the point, I need to develop a script which manages to perform all the changes required to update Eramba to the next version other than untar the archive, since this is done at built time (as you can see from here)

As requirement from our side we have to run this application within a kubernetes cluster… that’s the reason why I want to proper dockerize it. I saw docker images done by other people, but I in order to meet all the requirements from the Eramba documentation (like php memory_limit, post_max_size, etc.) I opt to create my own image… As you can verify, all the packages requirement are installed here while all the php and apache requirements here.

  • Is the update process defined anywhere?
  • Is this process change between different updates?

I understand what you want to achieve, but there is currently not technically feasible. The updater is complicated and there is no CLI for it.

Understood. Thank you anyway for your time.

We run eramba on top of a kubernetes cluster. The approach we’ve taken is to mount the html root as a persistent volume claim and just allow upgrades to the code to happen using the web interface without redeploying the application container.
See Questions - eramba Docker for the Dockerfile we are using.

BTW we are setting php settings by having a php.ini file in the application root rather than in the container so its simple to update that file when parameters need to be changed in an upgrade.

1 Like

If you go to app/upgrade you can run sudo -u www-data bin/cake migrations migrate
This will call the PHP Cake migration code to update the database schema if necessary.

You shouldn’t normally need this, but with containers it can be useful to run it on start.
We actually do the following - some of that is historical and not necessarily required now, sometimes in the past Eramba would stuff its ACLs (access control) info, and the extra commands would clean that up and resync it to a functional state.
Clearing various caches at container start can also be a good idea.
Mind that you shouldn’t just cut&paste any of this - take a look at what the different bits do and whether you might need it, and then adjust to your own situation.

in case of code/schema upgrades, we can use PHP Cake’s migration stuff

so we just run this, just in case, any time a container first fires up

cd /var/www/site/app/upgrade
sudo -u www-data bin/cake cache clear_all
sudo -u www-data bin/cake orm_cache clear
sudo -u www-data bin/cake migrations migrate
sudo -u www-data bin/cake orm_cache build
cd /var/www/site/app

sync ACL and delete cache

sudo -u www-data Console/cake update cleanup

put back missing ACLs

sudo -u www-data Console/cake AclExtras aco_update

Having the code automatically update is likely to break ISO 27001 and other compliance, as you’re not applying any particular change management to that process, nor any pre-deployment verification of the code or changed functionality. That introduces a significant risk.
Of course you can document around it, regard the Eramba code as an intrinsically trusted source hoping it’ll never cause any problems on upgrade, and have your CISO sign off on that risk as acceptable.

But speaking as a CISO myself, I wouldn’t sign off on that.
So we can’t and won’t just let updates happen. That wouldn’t be good security practice.

Instead of sudo -u www-data bin/cake migrations migrate I would suggest to run
sudo -u www-data Console/cake system migrate_db
and do not run these:
sudo -u www-data bin/cake cache clear_all
sudo -u www-data bin/cake orm_cache clear
sudo -u www-data bin/cake migrations migrate
sudo -u www-data bin/cake orm_cache build

I’ll chime in because I’ve been using a container/image I put together earlier this year. I’ll share what I did.

My Dockerfile is big but kind of standard for what you’d attempt with Eramba in a container:

FROM ubuntu:bionic
RUN apt-get update && apt-get install -y locales && locale-gen ${LOCALE}
ENV DEBIAN_FRONTEND=noninteractive
ENV APACHE_CONF_DIR=/etc/apache2
ENV PHP_CONF_DIR=/etc/php/7.3
ENV PHP_DATA_DIR=/var/lib/php
ENV WWW_DIR=/var/www/html
ENV ERAMBA_APP_DIR=${WWW_DIR}/eramba_v2/app


ENV GRC_HOME=/opt/grc

RUN apt-get install -y vim && apt-get install -y net-tools && apt-get install mysql-client -y && apt-get install -y wget 

   BUILD_DEPS='software-properties-common' \
   && dpkg-reconfigure locales \
   && apt-get install --no-install-recommends -y $BUILD_DEPS \
   && add-apt-repository -y ppa:ondrej/php \
   && add-apt-repository -y ppa:ondrej/apache2 \
   && apt-get update \
   && apt-get install mysql-client -y \
   && apt-get install -y curl apache2 libapache2-mod-php7.3 php7.3-cli php7.3-readline php7.3-mbstring php7.3-zip php7.3-intl php7.3-xml php7.3-json php7.3-curl php7.3-gd php7.3-pgsql php7.3-mysql php7.3-ldap php-pear \
   && apt-get install php-ldap -y \
   # Apache settings
   && cp /dev/null ${APACHE_CONF_DIR}/conf-available/other-vhosts-access-log.conf \
   && rm ${APACHE_CONF_DIR}/sites-enabled/000-default.conf ${APACHE_CONF_DIR}/sites-available/000-default.conf \
   && a2enmod rewrite php7.3 \
   # Install composer
   && curl -sS | php -- --version=1.8.4 --install-dir=/usr/local/bin --filename=composer \
   # Cleaning
   && apt-get purge -y --auto-remove $BUILD_DEPS \
   && apt-get autoremove -y \
   && rm -rf /var/lib/apt/lists/* \
   # Forward request and error logs to docker log collector
   && ln -sf /dev/stdout /var/log/apache2/access.log \
   && ln -sf /dev/stderr /var/log/apache2/error.log \
   && chown www-data:www-data ${PHP_DATA_DIR} -Rf

#RUN wget -O /tmp/wkhtmltox.deb && yes | apt-get update | dpkg -i /tmp/wkhtmltox.deb; apt-get install -y -f; apt --fix-broken install
COPY ./app/wkhtmltox_0.12.5-1.bionic_amd64.deb /tmp/wkhtmltox.deb
RUN apt-get update | dpkg -i /tmp/wkhtmltox.deb; apt-get install -y -f; apt --fix-broken install

# eramba app import
COPY ./eramba_latest.tar /opt/grc/app/
COPY ./updates /opt/grc/updates
RUN mkdir -p ${WWW_DIR} && tar -xvf /opt/grc/app/eramba_latest.tar -C ${WWW_DIR}
RUN rm -rf /opt/grc/app

# all the eramba config bits that make this work
COPY ./conf/database.php.template ${ERAMBA_APP_DIR}/Config/
COPY ./conf/app_local.php ${ERAMBA_APP_DIR}/Config/
COPY ./conf/CLIENT_KEY ${ERAMBA_APP_DIR}/Vendor/other/
COPY ./conf/eramba.conf ${APACHE_CONF_DIR}/sites-enabled/eramba.conf
COPY ./conf/php.ini  ${PHP_CONF_DIR}/apache2/conf.d/custom.ini
#COPY ./conf/apache2.conf ${APACHE_CONF_DIR}/apache2.conf

EXPOSE 80 443
COPY /opt/grc/bin/
RUN chmod 755 /opt/grc/bin/
RUN chown -R www-data:www-data ${WWW_DIR}/eramba_v2/*
CMD ["/opt/grc/bin/"]

But this only gets you the “latest” e2.5.5. You’ll notice I COPY ./updates to /opt/grc/updates, that’s used in the which I’ll snip a bit of below:


# default marida DB port 

mkdir -p ${GRC_HOME}/conf/

# update the eramba db conf at runtime in case we want to change

# test that the eramba db exists at the db location
echo "trying to connect to the db and check if the eramba schema is loaded..."
for TRY in $(seq 1 $TRIES); do
   sleep $WAIT
   TABLES=$(MYSQL_PWD=$ERAMBA_DB_PASS mysql -u $ERAMBA_DB_LOGIN -h $ERAMBA_DB_HOST --port=$ERAMBA_DB_PORT $ERAMBA_DB_NAME -e "show tables" 2>/tmp/sqlout)
   if [ $? -ne 0 ]; then
      if [ $TRY -eq $TRIES ]; then
         echo "tried $TRY times to reach the configured db unsuccessfully, the required database, or the credentials provided are invalid"
         echo "double check the env variables defined and ensure that the specified database has been created"
         echo "check and make sure the database container/service is running and restart this container"
         cat /tmp/sqlout
         exit -1

if [ ${#TABLES} -eq 0 ]; then
   echo "db $ERAMBA_DB_NAME is empty, attempting to load the eramba schema"
   SLOAD=$(MYSQL_PWD=$ERAMBA_DB_PASS mysql -u $ERAMBA_DB_LOGIN -h $ERAMBA_DB_HOST --port=3306 $ERAMBA_DB_NAME < $ERAMBA_APP_DIR/Config/db_schema/e2.5.5.sql)
   if [ $? -eq 0 ]; then
      echo "eramba schema is present in the db"
      echo "loading the eramba db schema seems to have failed, check yout db settings and restart the container"
      exit -1

echo "trying to fire off all eramba updates"
SQL="SELECT value FROM settings WHERE variable = 'DB_SCHEMA_VERSION';"
echo "schema is at version $SCHEMA_LVL"
UPDATES=($(ls $GRC_HOME/updates | sort --version-sort | paste -sd' '))
echo "UPDATES: ${UPDATES[@]}"
if [[ -f $ERAMBA_VER ]]; then
   echo "this isn't a new install, performing a version check..."
   for (( i=0; i<$LENGTH; i++ )); do
      echo "inspecting update $FF"
      if [[ "$FF" == "$SCHEMA_LVL" ]]; then
         echo "found update $FF at our current schema level"
   echo "this is a base install, we'll install all versions"
if [ $LIDX -eq $LENGTH ]; then
   echo "we're already up to date"
   echo "for (( i=$LIDX; i<$LENGTH; i++ )); do"
   for (( i=$LIDX; i<$LENGTH; i++ )); do
      echo "attempting to apply update $FILE"
      for TRY in $(seq 1 $TRIES); do
         yes | Console/cake update update ${GRC_HOME}/updates/$FILE
         if [ $? -ne 0 ]; then
            if [ $TRY -eq $TRIES ]; then
               echo "tried $TRY times to update unsuccessfully, exiting..."
               exit -1
            echo "update $FILE failed to install, this can happen sometimes, retrying $(($TRIES-$TRY)) more times"
            echo "update $FILE complete"
            echo "$FF" > $ERAMBA_VER
chown -R www-data:www-data ${WWW_DIR}/eramba_v2/*

. /etc/apache2/envvars
exec apache2 -D FOREGROUND

1/3 of the shell code is addressing the issue of what this deployment’s version is. When you do the initial deployment, the entrypoint will check what the update level is at and apply those updates (I was thinking you could volume mount an updates directory and when you start the container it’ll check and apply any that are missing).

However this approach still has a major limitation I haven’t had time to work out yet! (but I think I can)

The updates will modify both container files and apply DB migrations when they run. The 1st time the container runs, it’ll update the service and DB to the latest, and run fine. If you stick any other updates in there and restart, they’ll get applied.

BUT when you re-create the container, (down/up) the container files are destroyed and you’ll need to naturally re-apply the updates to get the initial container files 2.5.5 to the latest, but your DB schema is already at the latest.

This is primarily a headache because Eramba doesn’t ship a clean install of their code that is the latest, just updates from an old version to the latest.

If Eramba started shipping releases as a clean install, initial DB schema, and upgrade DB schema (from the last version), they could approach new installs as getting the latest and loading the initial DB schema, and upgrades as coping the latest over the previous and running the upgrade DB schema. (or some variant of this like shipping a clean install, and an upgrade for every release)

Without the ability to perform a clean install of the latest, it means that any containers will need to be treated as a precious snapshot that cannot be lost. You cannot re-create the container because the DB migrations can’t be re-run when all you want is the latest version of the files in the container.

Also waiting for all those updates to install when starting a container for the first time is a pain.

Finally, I have a 2nd container that runs once to apply “post” configuration, like setup LDAP authentication, etc… which you can’t do until after Eramba and its updates are running. I do this via some careful DB inserts and a wrote a web client to interact with the Web UI in a limited way.

Can you separate the migrate_db from the actual local file updates by calling a different Cake system command?! That might really help with dockerization. In my last post with my Dockerfile/entrypoint I mention that the biggest initial challenge is caused by being unable to separate the DB migration from the local file updates. If I already have a DB at the latest and I need to get my install from 2.5.5 to the latest without performing the DB migrations, can this be done? Thanks!

Actually migrate_db is separate command. In theory if you run migratedb on the latest version of db it will just do nothing. But correct procedure will be update from 2.5.5 to the latest version with empty db and after that restore db with the latest version.

Thanks Sam. Your last sentence confused me a bit. Do you mean that a typical upgrade should entail a db backup, drop/create db, new clean install from 2.5.5 to the latest, then restore the backup to a schema that’s 1 version newer than the backup?

How do you guys test upgrades? Do you do a clean install with backup/restore like this always? Do you test upgrade with data already in the database?

Sorry for confusion. What I ment was, if you have db with the latest version and you doing clean install, you should first update app to correct version and then restore database.This way app and db version are still the same. If you have for example app version 2.5.5 and db 2.19.1 daily cron gives you an error.
Updates are tested in a normal way, similar to customer. We have running instance with data and do normal update process in GUI.

Thanks Sam. This procedure won’t work well with a Docker/container approach. Backing up/restoring the DB isn’t an operation a container with Eramba in it can do alone. Ideally the upgrades would be smart enough to skip db/file upgrades if they’re already there, or skip based on a version marker.

Is there anyway to upgrade only the local files and skip the db migrations? I can and do check the db schema version in the database. The local files and their version can be handled separately. So, it’s possible to have a container fire up, check the db schema version, any local file version marker, and any available upgrade files, and decide if its necessary to upgrade either. The use case is, db is at 2.19, local files are 2.5.5, and there are upgrade files up to 2.19. In this case I only want to update the files, not the db.

If it’s possible this would solve 1 of 2 container challenges with Eramba. Thanks.