Docker Image needs root access to database

[Thoralf Rickert-Wendt]

The start script explicity checks the root access to the mysql database. But I think, this isn’t necessary.

In case of docker-compose the mysql-service can be defined with a user MYSQL_USER and MYSQL_PASSWORD that has full access to the database MYSQL_DATABASE.

The only thing here to do is - rely on that environment vars instead of using root to ceate and init the database. So replace all root and hardcoded “piler” user and “piler” database with that VARs.

In case of security both methods are not “completly” nice. A special user without CREATE/DROP rights is good at runtime, but must docker images ignore that. Your approach with a seperate root-access has disadvantages - f.e. when you try to use a central / infrastructure database cluster. In that case, the root-access would be “horrible” for any database admin.

I’d like to help here (I’ve already startet to build that for 1.3.9 but today I saw, you already pushed 1.3.10). So, maybe we can work together on that (I’m only know the processes in github).

[Thoralf Rickert-Wendt]

If’m right, this changes to the start script should be enough

line 25-33
line 79-84
line 100-111
line 131-133
line 144-147

maybe the complete /root/my.cnf is not necessary, because we use the same SQL user

also - do not mix the unix PILER_USER with the MYSQL_USER

‌

#!/bin/bash

set -o errexit
set -o pipefail
set -o nounset

CONFIG_DIR="/etc/piler"
VOLUME_DIR="/var/piler"
PILER_CONF="${CONFIG_DIR}/piler.conf"
PILER_KEY="${CONFIG_DIR}/piler.key"
PILER_PEM="${CONFIG_DIR}/piler.pem"
PILER_NGINX_CONF="${CONFIG_DIR}/piler-nginx.conf"
SPHINX_CONF="${CONFIG_DIR}/sphinx.conf"
CONFIG_SITE_PHP="${CONFIG_DIR}/config-site.php"
PILER_MY_CNF="${CONFIG_DIR}/.my.cnf"
# ROOT_MY_CNF="/root/.my.cnf"


error() {
   echo "ERROR:" "$*" 1>&2
   exit 1
}


log() {
   echo "DEBUG:" "$*"
}


pre_flight_check() {
   [[ -v MYSQL_HOST ]] || error "Missing MYSQL_HOST env variable"
   [[ -v MYSQL_DATABASE ]] || error "Missing MYSQL_DATABASE env variable"
   [[ -v MYSQL_USER ]] || error "Missing MYSQL_USER env variable"
   [[ -v MYSQL_PASSWORD ]] || error "Missing MYSQL_PASSWORD env variable"

   [[ -v PILER_HOSTNAME ]] || error "Missing PILER_HOSTNAME env variable"
#   [[ -v MYSQL_HOSTNAME ]] || error "Missing MYSQL_HOSTNAME env variable"
#   [[ -v MYSQL_PILER_PASSWORD ]] || error "Missing MYSQL_PILER_PASSWORD env variable"
#   [[ -v MYSQL_ROOT_PASSWORD ]] || error "Missing MYSQL_ROOT_PASSWORD env variable"
}


give_it_to_piler() {
   local f="$1"

   [[ -f "$f" ]] || error "${f} does not exist, aborting"

   chown "${PILER_USER}:${PILER_USER}" "$f"
   chmod 600 "$f"
}


make_certificate() {
   local f="$1"
   local crt="/tmp/1.cert"
   local SSL_CERT_DATA="/C=US/ST=Denial/L=Springfield/O=Dis/CN=www.example.com"

   log "Making an ssl certificate"

   openssl req -new -newkey rsa:4096 -days 3650 -nodes -x509 -subj "$SSL_CERT_DATA" -keyout "$f" -out "$crt" -sha1 2>/dev/null
   cat "$crt" >> "$f"
   rm -f "$crt"

   give_it_to_piler "$f"
}


make_piler_key() {
   local f="$1"

   log "Generating piler.key"

   dd if=/dev/urandom bs=56 count=1 of="$f" 2>/dev/null
   [[ $(stat -c '%s' "$f") -eq 56 ]] || error "could not read 56 bytes from /dev/urandom to ${f}"

   give_it_to_piler "$f"
}


fix_configs() {
   [[ -f "$PILER_KEY" ]] || make_piler_key "$PILER_KEY"
   [[ -f "$PILER_PEM" ]] || make_certificate "$PILER_PEM"

   if [[ ! -f "$PILER_NGINX_CONF" ]]; then
      log "Writing ${PILER_NGINX_CONF}"

      cp "${PILER_NGINX_CONF}.dist" "$PILER_NGINX_CONF"
      sed -i "s%PILER_HOST%${PILER_HOSTNAME}%" "$PILER_NGINX_CONF"
   fi

   if [[ ! -f "$PILER_CONF" ]]; then
      log "Writing ${PILER_CONF}"

      sed \
         -e "s/verystrongpassword/$MYSQL_PASSWORD/g" \
         -e "s/mysqluser=piler/mysqldb=$MYSQL_USER/g" \
         -e "s/mysqldb=piler/mysqldb=$MYSQL_DATABASE/g" \
         -e "s/hostid=.*/hostid=${PILER_HOSTNAME}/g" \
         -e "s/tls_enable=.*/tls_enable=1/g" \
         -e "s/mysqlsocket=.*/mysqlsocket=/g" "${PILER_CONF}.dist" > "$PILER_CONF"

      {
         echo "mysqlhost=${MYSQL_HOST}"
      } >> "$PILER_CONF"

      give_it_to_piler "$PILER_CONF"
   fi

   if [[ ! -f "$CONFIG_SITE_PHP" ]]; then
      log "Writing ${CONFIG_SITE_PHP}"

      cp "${CONFIG_DIR}/config-site.dist.php" "$CONFIG_SITE_PHP"

      sed -i "s%HOSTNAME%${PILER_HOSTNAME}%" "$CONFIG_SITE_PHP"

      {
         echo "\$config['DECRYPT_BINARY'] = '/usr/bin/pilerget';"
         echo "\$config['DECRYPT_ATTACHMENT_BINARY'] = '/usr/bin/pileraget';"
         echo "\$config['PILER_BINARY'] = '/usr/sbin/piler';"
         echo "\$config['DB_HOSTNAME'] = '$MYSQL_HOST';"
         echo "\$config['DB_DATABASE'] = '$MYSQL_DATABASE';"
         echo "\$config['DB_USERNAME'] = '$MYSQL_USER';"
         echo "\$config['DB_PASSWORD'] = '$MYSQL_PASSWORD';"
         echo "\$config['ENABLE_MEMCACHED'] = 1;"
         echo "\$memcached_server = ['memcached', 11211];"
      } >> "$CONFIG_SITE_PHP"
   fi

   sed -e "s%MYSQL_HOSTNAME%${MYSQL_HOST}%" \
       -e "s%MYSQL_DATABASE%${MYSQL_DATABASE}%" \
       -e "s%MYSQL_USERNAME%${MYSQL_USER}%" \
       -e "s%MYSQL_PASSWORD%${MYSQL_PASSWORD}%" \
       -i "$SPHINX_CONF"
}


wait_until_mysql_server_is_ready() {
   while true; do if mysql "--defaults-file=${ROOT_MY_CNF}" <<< "show databases"; then break; fi; log "${MYSQL_HOST} is not ready"; sleep 5; done

   log "${MYSQL_HOST} is ready"
}


init_database() {
   local db
   local has_piler_db=0

   wait_until_mysql_server_is_ready

   while read -r db; do
      if [[ "$db" == "$MYSQL_DATABASE" ]]; then has_piler_db=1; fi
   done < <(mysql "--defaults-file=${ROOT_MY_CNF}" <<< 'show databases')

   if [[ $has_piler_db -eq 0 ]]; then
      log "no ${MYSQL_DATABASE} database, creating"

      #mysql "--defaults-file=${ROOT_MY_CNF}" <<< "create database ${MYSQL_DATABASE} character set utf8mb4"
      #mysql "--defaults-file=${ROOT_MY_CNF}" <<< "grant all privileges on ${MYSQL_DATABASE}.* to ${MYSQL_USER} identified by '${MYSQL_PASSWORD}'"
      #mysql "--defaults-file=${ROOT_MY_CNF}" <<< "flush privileges"

      mysql "--defaults-file=${PILER_MY_CNF}" "$MYSQL_DATABASE" < /usr/share/piler/db-mysql.sql
   else
      log "${MYSQL_DATABASE} db exists"
   fi

   if [[ -v ADMIN_USER_PASSWORD_HASH ]]; then
      mysql "--defaults-file=${PILER_MY_CNF}" "$MYSQL_DATABASE" <<< "update user set password='${ADMIN_USER_PASSWORD_HASH}' where uid=0"
   fi
}


create_my_cnf_files() {
   printf "[client]\nhost = %s\nuser = %s\npassword = %s\n[mysqldump]\nhost = %s\nuser = %s\npassword = %s\n" \
      "$MYSQL_HOST" "$MYSQL_USER" "$MYSQL_PASSWORD" "$MYSQL_HOST" "$MYSQL_USER" "$MYSQL_PASSWORD" \
      > "$PILER_MY_CNF"
   printf "[client]\nhost = %s\nuser = %s\npassword = %s\n" "$MYSQL_HOST" "$MYSQL_USER" "$MYSQL_PASSWORD" > "$ROOT_MY_CNF"

   give_it_to_piler "$PILER_MY_CNF"
}


start_services() {
   service rsyslog start
   service cron start
   service php7.4-fpm start
   service nginx start
}


start_piler() {
   if [[ ! -f "${VOLUME_DIR}/sphinx/main1.spp" ]]; then
      log "main1.spp does not exist, creating index files"
      su -c "indexer --all --config ${SPHINX_CONF}" piler
   fi

   # No pid file should exist for piler
   rm -f /var/run/piler/*pid

   /etc/init.d/rc.searchd start
   /etc/init.d/rc.piler start
}


pre_flight_check
fix_configs
create_my_cnf_files
init_database
start_services
start_piler

while true; do sleep 3600; done

‌

[Janos SUTO]

You have a valid point. The “init” script can be improved. At the time I wrote it, I assumed that the user wants the simplest possible solution. To have such you need the mysql root account, otherwise you just can’t create a new database and add a new mysql user. However, your use case having a central mysql cluster is more advanced, however, it can be solved provided that you create the piler mysql user and database before starting the containers.

How about if we introduce a SKIP_DB_INIT variable? If set, then we’ll use the mysql piler user (whatever we call it) to try connecting the mysql daemon. Otherwise go with the current logic.

Btw. I think it makes sense to use piler for both unix and mysql account name. But I listen to your argument, if there’s any on the matter.

[Thoralf Rickert-Wendt]

Ok, “the simplest possible solution”

• let the docker image for Maria/Mysql DB do the job. If you have a look into https://hub.docker.com/_/mariadb in the section Environment variable to MYSQL_DATABASE you see, that Maria (and MySQL too) can automatically create a database for you. The MYSQL_ROOT_PASSWORD is in that case a random password - that you never need, because you always use MYSQL_USER and MYSQL_PASSWORD to connect to the database
• The SKIP_DB_INIT would not work - the vars MYSQL_USER and MYSQL_DATABASE must be set too (you’re using hardcoded “piler”).
• Of course - you need to define MYSQL_USER and MYSQL_DATABASE (as a user of docker-compose or what ever), but you code make defaults to it, that could be overwritten by the user.

I think, the only changes are in the start.sh script above. Of course some minor README and docker-compose changes.

[Janos SUTO]

Well, there are several use cases:

• using mariadb running docker. In this case the auto creating of the database and mysql account may work
• using an external cluster. In this case you should create the mysql database and user for piler, and provide their values to the docker compose file

‌

[Janos SUTO]

Check out this commit: https://bitbucket.org/jsuto/piler/commits/5aa8d62701a01d8d8457667e32553a1bc55160f3 and let me know if you think there’s something else to fix.

I’ve also rebuilt the docker image and pushed to docker hub.

[Janos SUTO]

The issue has been fixed on the latest master branch.

[Thoralf Rickert-Wendt]

Good morning - sorry for the late response. Our own software bothered me the last days.

After removing all old config (incl. .my.cnf) the image works as expected with

• a local docker-compose database
• a remote preconfigured database
• different username, password then default
• without mysql-root user

Thanks a lot.

‌