Updating from PostgreSQL 11 to PostgreSQL 15

Knowledge Base Supported
, , ,
1

Upgrade PostgreSQL from 11 to 15

PostgreSQL 15 is the default for UCS 5.2 and by now also for UCS 5.0. But PostgreSQL 11 has been the default for UCS 5.0 for a long time.

There is no automatic migration from 11 to 15 before or after the update to UCS 5.2, the installed PostgreSQL version is retained.

The following steps are necessary on a UCS 5.0 or 5.2 system for updating PostgreSQL 11 to PostgreSQL 15

:bulb:
The following commands are one-liners. You can simply triple-click on a line to completely select it for easy copy-and-paste. But still: Always double-check your command prompt before issueing a command!

if dpkg -l univention-postgresql-11 | grep ^ii; then
  [ -f /usr/sbin/univention-pkgdb-scan ] && chmod -x /usr/sbin/univention-pkgdb-scan
  service postgresql stop
  rm -rf /etc/postgresql/15
  apt-get install --reinstall postgresql-15
  ucr set postgres15/autostart='yes'
  systemctl unmask postgresql@15-main.service
  pg_dropcluster 15 main --stop
  service postgresql start
  [ -e /var/lib/postgresql/15/main ] && mv /var/lib/postgresql/15/main /var/lib/postgresql/15/main.old
  pg_upgradecluster 11 main
  univention-install --yes univention-postgresql-15
  ucr commit /etc/postgresql/15/main/*
  chown -R postgres:postgres /var/lib/postgresql/15
  [ ! -e /etc/postgresql/15/main/conf.d/ ] && mkdir /etc/postgresql/15/main/conf.d/ && chown postgres:postgres /etc/postgresql/15/main/conf.d/
  service postgresql restart
  [ -f /usr/sbin/univention-pkgdb-scan ] && chmod +x /usr/sbin/univention-pkgdb-scan
else
  echo "PostgreSQL 11 not installed. Nothing to migrate"
fi

Then check the actual version that is used:

$ pg_lsclusters -h
15 main 5432 online postgres /var/lib/postgresql/15/main /var/log/postgresql/postgresql-15-main.log

Once the new database / data inventory has been verified, the PostgreSQL 11 data can be removed / the software package uninstalled:

pg_dropcluster 11 main --stop
service postgresql restart
apt-get purge postgresql-11

Upgrade password encryption from md5 to scram-sha-256

Upgrading the password hash from m̀d5 to scram-sha-256 is only supported from UCS 5.2-0 onwards

PostgreSQL-15 in UCS 5.0-10 does not support any password hash other than m̀d5. The following Howto applies to UCS 5.2 only!
As md5 is considered insecure, it is recommended to switch to scram-sha-256 after updating to UCS 5.2.

UCS 5.2 ships with the new password encryption method scram-sha-256.
scram-sha-256 is the default for new installations since UCS 5.2-0. But if you upgraded from UCS 5.0 to 5.2, the package univention-postgresql explicitly configures postgresql-15 so that the old password encryption method md5 continues to be used. In this case you may want to update the password hashes in your PostgreSQL database by following the steps described here.

Before switching to scram-sha-256, you have to be sure that all PostgreSQL clients you use actually support scram-sha-256.

Change the password_encryption parameter

To change the password_encryption parameter, you have to set the UCR variable postgres15/password-encryption to scram-sha-256:

ucr set postgres15/password-encryption=scram-sha-256

Afterwards, you have to restart the PostgreSQL service:

systemctl restart postgresql.service

After the restart, you can check the parameter with the following command:

sudo -u postgres psql -c "SHOW password_encryption;"

Set all passwords again

All password authenticated users have to change their password. In psql, a superuser can change any user’s password with 
\password user_name

You can get all users, you can use the following command:

sudo -u postgres psql -c "\du"

Get current passwords of users

Many services use password files to store the current password. The following password files are used for the following users:
User Passwordfile
admindiary /etc/admin-diary.secret
horde /etc/horde.secret
keycloak /etc/postgresql-keycloak.secret
postgres /etc/postgresql.secret
selfservice /etc/self-service-db.secret (or a different on from UCR variable umc/self-service/postgresql/password-file)
importhttpapi /etc/ucsschool-import/postgres.secret
pkgdbu /etc/postgresql/pkgdb.secret
postgres This user doesn’t have a password by default, but uses peer authentication
pykotauser and pykotaadmin univention-printquota has been removed in UCS 5.0. Unused accounts. /etc/pykota/pykota.secret

Change the authentication method

The authentication method has to be changed from ‘md5’ to ‘scram-sha-256’, you can do it setting the UCR variable ‘postgres15/pg_hba/password-encryption’ to ‘scram-sha-256’:

ucr set postgres15/pg_hba/password-encryption=scram-sha-256

Afterwards, you have to restart the PostgreSQL service:

systemctl restart postgresql.service
4 Likes