fix Bug 77433 - Fix duplicate tables in public when using DB_SCHEMA

Co-authored-by: Vladimir Ischenko <vladimir.ischenko@onlyoffice.com>
Co-committed-by: Vladimir Ischenko <vladimir.ischenko@onlyoffice.com>

Dockerfile

@@ -121,6 +121,8 @@ RUN PACKAGE_FILE="${COMPANY_NAME}-${PRODUCT_NAME}${PRODUCT_EDITION}${PACKAGE_VER
     apt-get -y update && \
     service postgresql start && \
     apt-get -yq install /tmp/$PACKAGE_FILE && \
+    PGPASSWORD=$ONLYOFFICE_VALUE dropdb -h localhost -p 5432 -U $ONLYOFFICE_VALUE $ONLYOFFICE_VALUE && \
+    sudo -u postgres psql -c "DROP ROLE onlyoffice;" && \
     service postgresql stop && \
     chmod 755 /etc/init.d/supervisor && \
     sed "s/COMPANY_NAME/${COMPANY_NAME}/g" -i /etc/supervisor/conf.d/*.conf && \

README.md

@@ -26,7 +26,7 @@ Starting from version 6.0, Document Server is distributed as ONLYOFFICE Docs. It
 
 ONLYOFFICE Docs can be used as a part of [ONLYOFFICE DocSpace](https://www.onlyoffice.com/docspace.aspx) and ONLYOFFICE Workspace, or with [third-party sync&share solutions](https://www.onlyoffice.com/all-connectors.aspx) (e.g. Odoo, Moodle, Nextcloud, ownCloud, Seafile, etc.) to enable collaborative editing within their interface.
 
-***Important*** Please update `docker-engine` to latest version (`20.10.21` as of writing this doc) before using it. We use `ubuntu:22.04` as base image and it older versions of docker have compatibility problems with it
+***Important*** Please update `docker-engine` to latest version (`20.10.21` as of writing this doc) before using it. We use `ubuntu:24.04` as base image and it older versions of docker have compatibility problems with it
 
 ## Functionality ##
 
@@ -191,13 +191,19 @@ Below is the complete list of parameters that can be set using environment varia
 - **DB_NAME**: The name of a database to use. Should be existing on container startup.
 - **DB_USER**: The new user name with superuser permissions for the database account.
 - **DB_PWD**: The password set for the database account.
+- **DB_SCHEMA**: Database schema name (optional).  
+  - **PostgreSQL** — schema for [search_path](https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH), default `public`.  
+  - **MSSQL** — schema to set as [DEFAULT_SCHEMA](https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-user-transact-sql?view=sql-server-ver17#default_schema---schema_name--null-), default `dbo`.  
 - **AMQP_URI**: The [AMQP URI](https://www.rabbitmq.com/uri-spec.html "RabbitMQ URI Specification") to connect to message broker server.
 - **AMQP_TYPE**: The message broker type. Supported values are `rabbitmq` or `activemq`. Defaults to `rabbitmq`.
 - **REDIS_SERVER_HOST**: The IP address or the name of the host where the Redis server is running.
 - **REDIS_SERVER_PORT**:  The Redis server port number.
+- **REDIS_SERVER_USER**: The Redis server username. The username is not set by default.
 - **REDIS_SERVER_PASS**: The Redis server password. The password is not set by default.
+- **REDIS_SERVER_DB**: The Redis database index number to select. Defaults to `0`.  
 - **NGINX_WORKER_PROCESSES**: Defines the number of nginx worker processes.
 - **NGINX_WORKER_CONNECTIONS**: Sets the maximum number of simultaneous connections that can be opened by a nginx worker process.
+- **NGINX_ACCESS_LOG**: Defines whether access logging is enabled. Defaults to `false`.
 - **SECURE_LINK_SECRET**: Defines secret for the nginx config directive [secure_link_md5](https://nginx.org/en/docs/http/ngx_http_secure_link_module.html#secure_link_md5). Defaults to `random string`.
 - **JWT_ENABLED**: Specifies the enabling the JSON Web Token validation by the ONLYOFFICE Document Server. Defaults to `true`.
 - **JWT_SECRET**: Defines the secret key to validate the JSON Web Token in the request to the ONLYOFFICE Document Server. Defaults to random value.

config/supervisor/ds/ds-adminpanel.conf

@@ -0,0 +1,13 @@
+[program:adminpanel]
+command=/var/www/COMPANY_NAME/documentserver/server/AdminPanel/server/adminpanel
+directory=/var/www/COMPANY_NAME/documentserver/server/AdminPanel
+user=ds
+environment=NODE_ENV=production-linux,NODE_CONFIG_DIR=/etc/COMPANY_NAME/documentserver,NODE_DISABLE_COLORS=1,APPLICATION_NAME=COMPANY_NAME
+stdout_logfile=/var/log/COMPANY_NAME/documentserver/adminpanel/out.log
+stdout_logfile_backups=0
+stdout_logfile_maxbytes=0
+stderr_logfile=/var/log/COMPANY_NAME/documentserver/adminpanel/err.log
+stderr_logfile_backups=0
+stderr_logfile_maxbytes=0
+autostart=false
+autorestart=false

config/supervisor/ds/ds.conf

@@ -1,2 +1,2 @@
 [group:ds]
-programs=docservice,converter,metrics,example
+programs=docservice,converter,metrics,example,adminpanel

run-document-server.sh

@@ -103,6 +103,7 @@ NGINX_ONLYOFFICE_EXAMPLE_CONF="${NGINX_ONLYOFFICE_EXAMPLE_PATH}/includes/ds-exam
 
 NGINX_CONFIG_PATH="/etc/nginx/nginx.conf"
 NGINX_WORKER_PROCESSES=${NGINX_WORKER_PROCESSES:-1}
+NGINX_ACCESS_LOG=${NGINX_ACCESS_LOG:-false}
 # Limiting the maximum number of simultaneous connections due to possible memory shortage
 LIMIT=$(ulimit -n); [ $LIMIT -gt 1048576 ] && LIMIT=1048576
 NGINX_WORKER_CONNECTIONS=${NGINX_WORKER_CONNECTIONS:-$LIMIT}
@@ -374,10 +375,11 @@ update_redis_settings(){
   ${JSON} -I -e "this.services.CoAuthoring.redis.host = '${REDIS_SERVER_HOST}'"
   ${JSON} -I -e "this.services.CoAuthoring.redis.port = '${REDIS_SERVER_PORT}'"
 
-  if [ -n "${REDIS_SERVER_PASS}" ]; then
-    ${JSON} -I -e "this.services.CoAuthoring.redis.options = {'password':'${REDIS_SERVER_PASS}'}"
-  fi
-
+  ${JSON} -I -e  "this.services.CoAuthoring.redis.options = {
+    ${REDIS_SERVER_USER:+username: '${REDIS_SERVER_USER}',}
+    ${REDIS_SERVER_PASS:+password: '${REDIS_SERVER_PASS}',}
+    ${REDIS_SERVER_DB:+database: '${REDIS_SERVER_DB}',}
+  }"
 }
 
 update_ds_settings(){
@@ -388,6 +390,7 @@ update_ds_settings(){
   ${JSON} -I -e "this.services.CoAuthoring.secret.inbox.string = '${JWT_SECRET}'"
   ${JSON} -I -e "this.services.CoAuthoring.secret.outbox.string = '${JWT_SECRET}'"
   ${JSON} -I -e "this.services.CoAuthoring.secret.session.string = '${JWT_SECRET}'"
+  ${JSON} -I -e "this.services.CoAuthoring.secret.browser.string = '${JWT_SECRET}'"
 
   ${JSON} -I -e "this.services.CoAuthoring.token.inbox.header = '${JWT_HEADER}'"
   ${JSON} -I -e "this.services.CoAuthoring.token.outbox.header = '${JWT_HEADER}'"
@@ -449,9 +452,7 @@ create_postgresql_db(){
 }
 
 create_mssql_db(){
-  MSSQL="/opt/mssql-tools18/bin/sqlcmd -S $DB_HOST,$DB_PORT"
-
-  $MSSQL -U $DB_USER -P "$DB_PWD" -C -Q "IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '$DB_NAME') BEGIN CREATE DATABASE $DB_NAME; END"
+  ${MSSQL/ -d $DB_NAME/} -b -Q "IF NOT EXISTS (SELECT * FROM sys.databases WHERE name = '$DB_NAME') BEGIN CREATE DATABASE [$DB_NAME]; END"
 }
 
 create_db_tbl() {
@@ -488,6 +489,22 @@ upgrade_db_tbl() {
   esac
 }
 
+postgresql_check_schema(){
+    DB_SCHEMA=${DB_SCHEMA:-$(${JSON} services.CoAuthoring.sql.pgPoolExtraOptions.options 2>/dev/null | sed -n 's/.*search_path=\([^, ]*\).*/\1/p')}
+    if [ -n "${DB_SCHEMA}" ]; then
+      export PGOPTIONS="-c search_path=${DB_SCHEMA}"
+      $PSQL -c "CREATE SCHEMA IF NOT EXISTS ${DB_SCHEMA};" >/dev/null 2>&1
+      ${JSON} -I -e "this.services.CoAuthoring.sql.pgPoolExtraOptions ||= {}; this.services.CoAuthoring.sql.pgPoolExtraOptions.options = '${PGOPTIONS}'"
+    fi
+}
+
+mssql_check_schema(){
+  if [ -n "${DB_SCHEMA}" ]; then
+    ${MSSQL} -b -Q "DECLARE @s sysname=N'${DB_SCHEMA}'; IF SCHEMA_ID(@s) IS NULL BEGIN DECLARE @sql nvarchar(max); SET @sql=N'CREATE SCHEMA '+QUOTENAME(@s)+N' AUTHORIZATION '+QUOTENAME(N'${DB_USER}'); EXEC(@sql); END"
+    ${MSSQL} -b -Q "DECLARE @s sysname=N'${DB_SCHEMA}'; DECLARE @u sysname=N'${DB_USER}'; IF USER_ID(@u) IS NOT NULL BEGIN DECLARE @sql nvarchar(max); SET @sql=N'ALTER USER '+QUOTENAME(@u)+N' WITH DEFAULT_SCHEMA = '+QUOTENAME(@s); EXEC(@sql); END"
+  fi
+}
+
 upgrade_postgresql_tbl() {
   if [ -n "$DB_PWD" ]; then
     export PGPASSWORD=$DB_PWD
@@ -495,6 +512,7 @@ upgrade_postgresql_tbl() {
 
   PSQL="psql -q -h$DB_HOST -p$DB_PORT -d$DB_NAME -U$DB_USER -w"
 
+  postgresql_check_schema
   $PSQL -f "$APP_DIR/server/schema/postgresql/removetbl.sql"
   $PSQL -f "$APP_DIR/server/schema/postgresql/createdb.sql"
 }
@@ -508,9 +526,13 @@ upgrade_mysql_tbl() {
 }
 
 upgrade_mssql_tbl() {
-  CONN_PARAMS="-d $DB_NAME -U $DB_USER -P "$DB_PWD" -C"
-  MSSQL="/opt/mssql-tools18/bin/sqlcmd -S $DB_HOST,$DB_PORT $CONN_PARAMS"
+  if [ -n "$DB_PWD" ]; then
+    export SQLCMDPASSWORD=$DB_PWD
+  fi
 
+  MSSQL="/opt/mssql-tools18/bin/sqlcmd -S $DB_HOST,$DB_PORT -d $DB_NAME -U $DB_USER -C"
+
+  mssql_check_schema
   $MSSQL < "$APP_DIR/server/schema/mssql/removetbl.sql" >/dev/null 2>&1
   $MSSQL < "$APP_DIR/server/schema/mssql/createdb.sql" >/dev/null 2>&1
 }
@@ -528,6 +550,8 @@ create_postgresql_tbl() {
   fi
 
   PSQL="psql -q -h$DB_HOST -p$DB_PORT -d$DB_NAME -U$DB_USER -w"
+
+  postgresql_check_schema
   $PSQL -f "$APP_DIR/server/schema/postgresql/createdb.sql"
 }
 
@@ -542,11 +566,14 @@ create_mysql_tbl() {
 }
 
 create_mssql_tbl() {  
+  if [ -n "$DB_PWD" ]; then
+    export SQLCMDPASSWORD=$DB_PWD
+  fi
+
+  MSSQL="/opt/mssql-tools18/bin/sqlcmd -S $DB_HOST,$DB_PORT -d $DB_NAME -U $DB_USER -C"
+
   create_mssql_db
-
-  CONN_PARAMS="-d $DB_NAME -U $DB_USER -P "$DB_PWD" -C"
-  MSSQL="/opt/mssql-tools18/bin/sqlcmd -S $DB_HOST,$DB_PORT $CONN_PARAMS"
-
+  mssql_check_schema
   $MSSQL < "$APP_DIR/server/schema/mssql/createdb.sql" >/dev/null 2>&1
 }
 
@@ -578,7 +605,13 @@ update_nginx_settings(){
   # Set up nginx
   sed 's/^worker_processes.*/'"worker_processes ${NGINX_WORKER_PROCESSES};"'/' -i ${NGINX_CONFIG_PATH}
   sed 's/worker_connections.*/'"worker_connections ${NGINX_WORKER_CONNECTIONS};"'/' -i ${NGINX_CONFIG_PATH}
-  sed 's/access_log.*/'"access_log off;"'/' -i ${NGINX_CONFIG_PATH}
+
+  if [ "${NGINX_ACCESS_LOG}" = "true" ]; then
+    touch "${DS_LOG_DIR}/nginx.access.log"
+    sed -ri 's|^\s*access_log\b.*;|access_log '"${DS_LOG_DIR}"'/nginx.access.log;|' "${NGINX_CONFIG_PATH}" "${NGINX_ONLYOFFICE_PATH}/includes/ds-common.conf" 2>/dev/null
+  else
+    sed -ri 's|^\s*access_log\b.*;|access_log off;|' "${NGINX_CONFIG_PATH}"
+  fi
 
   # setup HTTPS
   if [ -f "${SSL_CERTIFICATE_PATH}" -a -f "${SSL_KEY_PATH}" ]; then
@@ -639,11 +672,11 @@ update_release_date(){
 }
 
 # create base folders
-for i in converter docservice metrics; do
-  mkdir -p "${DS_LOG_DIR}/$i"
+for i in converter docservice metrics adminpanel; do
+  mkdir -p "$DS_LOG_DIR/$i" && touch "$DS_LOG_DIR/$i"/{out,err}.log
 done
 
-mkdir -p ${DS_LOG_DIR}-example
+mkdir -p "${DS_LOG_DIR}-example" && touch "${DS_LOG_DIR}-example"/{out,err}.log
 
 # create app folders
 for i in ${DS_LIB_DIR}/App_Data/cache/files ${DS_LIB_DIR}/App_Data/docbuilder ${DS_LIB_DIR}-example/files; do
@@ -651,15 +684,15 @@ for i in ${DS_LIB_DIR}/App_Data/cache/files ${DS_LIB_DIR}/App_Data/docbuilder ${
 done
 
 # change folder rights
-chown ds:onlyoffice "${DATA_DIR}"
+chown ds:ds "${DATA_DIR}"
 for i in ${DS_LOG_DIR} ${DS_LOG_DIR}-example ${LIB_DIR}; do
-  chown -R ds:onlyoffice "$i"
+  chown -R ds:ds "$i"
   chmod -R 755 "$i"
 done
 
 # Bug 75324 - Update permissions for runtime.json
 AI_CONFIG_FILE="${DATA_DIR}/runtime.json"
-[ -f "${AI_CONFIG_FILE}" ] && { chown ds:onlyoffice "${AI_CONFIG_FILE}" && chmod 644 "${AI_CONFIG_FILE}"; }
+[ -f "${AI_CONFIG_FILE}" ] && { chown ds:ds "${AI_CONFIG_FILE}" && chmod 644 "${AI_CONFIG_FILE}"; }
 
 if [ ${ONLYOFFICE_DATA_CONTAINER_HOST} = "localhost" ]; then
 
@@ -732,14 +765,15 @@ else
   update_welcome_page
 fi
 
-find /etc/${COMPANY_NAME} ! -path '*logrotate*' -exec chown ds:onlyoffice {} \;
+find /etc/${COMPANY_NAME} ! -path '*logrotate*' -exec chown ds:ds {} \;
 
 #start needed local services
 for i in ${LOCAL_SERVICES[@]}; do
   service $i start
 done
 
-if [ ${PG_NEW_CLUSTER} = "true" ]; then
+PG_DB_EXISTS=$(PGPASSWORD="$DB_PWD" psql -h ${DB_HOST} -p${DB_PORT} -U "${DB_USER}" -tAc "SELECT 1 FROM pg_database WHERE datname='${DB_NAME}';" 2>/dev/null)
+if [ ${PG_NEW_CLUSTER} = "true" ] || [ "${PG_DB_EXISTS}" != "1" ]; then
   create_postgresql_db
   create_postgresql_tbl
 fi
@@ -758,6 +792,12 @@ if [ ${ONLYOFFICE_DATA_CONTAINER} != "true" ]; then
 
   update_nginx_settings
   
+  if [ "${PLUGINS_ENABLED}" = "true" ]; then
+    echo -n Installing plugins, please wait...
+    start_process documentserver-pluginsmanager.sh -r false --update=\"${APP_DIR}/sdkjs-plugins/plugin-list-default.json\" >/dev/null
+    echo Done
+  fi
+
   service supervisor start
   
   # start cron to enable log rotating
@@ -783,14 +823,8 @@ if [ "${GENERATE_FONTS}" == "true" ]; then
   start_process documentserver-generate-allfonts.sh ${ONLYOFFICE_DATA_CONTAINER}
 fi
 
-if [ "${PLUGINS_ENABLED}" = "true" ]; then
-  echo -n Installing plugins, please wait...
-  start_process documentserver-pluginsmanager.sh -r false --update=\"${APP_DIR}/sdkjs-plugins/plugin-list-default.json\" >/dev/null
-  echo Done
-fi
-
 start_process documentserver-static-gzip.sh ${ONLYOFFICE_DATA_CONTAINER}
 
 echo "${JWT_MESSAGE}" 
 
-start_process find "$DS_LOG_DIR" "$DS_LOG_DIR-example" -type f -name "*.log" | xargs tail -f
+start_process find "$DS_LOG_DIR" "$DS_LOG_DIR-example" -type f -name "*.log" | xargs tail -F