mysql_install_db fails with libpam-tmpdir

On Debian GNU/Linux, when the package libpam-tmpdir is installed, mysql_install_db script fails during post install setup. As a result, mariadb daemon fails to start. The following error message is shown:

rm -rf /var/lib/mysql ; mysql_install_db --rpm --cross-bootstrap --user=mysql --disable-log-bin --skip-test-db

2022-10-28 19:33:00 0 [ERROR] mariadbd: Can't create/write to file '/tmp/user/0/ib2C7oNS' (Errcode: 13 "Permission denied")

2022-10-28 19:33:00 0 [ERROR] InnoDB: Unable to create temporary file; errno: 13

2022-10-28 19:33:00 0 [ERROR] mariadbd: Can't create/write to file '/tmp/user/0/ibykVtxz' (Errcode: 13 "Permission denied")

2022-10-28 19:33:00 0 [ERROR] InnoDB: Unable to create temporary file; errno: 13

2022-10-28 19:33:00 0 [ERROR] InnoDB: Database creation was aborted with error Generic error. You may need to delete the ibdata1 file before trying to start up again.

2022-10-28 19:33:00 0 [ERROR] Plugin 'InnoDB' init function returned error.

2022-10-28 19:33:00 0 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2022-10-28 19:33:00 0 [ERROR] Unknown/unsupported storage engine: InnoDB

2022-10-28 19:33:00 0 [ERROR] Aborting

Installation of system tables failed!  Examine the logs in

/var/lib/mysql for more information.

Environment

On FreedomBox (a pure blend of Debian), several applications that depend on mariadb fail to install when running on Debian testing/unstable. This is due to mariadb not running soon after installation. FreedomBox installs that package libpam-tmpdir by default. If this package is removed, mariadb server is running successfully after install.

This bug was reproduced on Debian unstable (as of 2022-10-28) with mariadb-server package version 1:10.6.10-1+b1.

Workarounds

1. If libpam-tmpdir package is removed, the installation and daemon start succeed.
2. When the environment variable TMPDIR is set to empty value, the mysql_install_db command succeeds. Example:

   rm -rf /var/lib/mysql ; TMPDIR= mysql_install_db --rpm --cross-bootstrap --user=mysql --disable-log-bin --skip-test-db

3. When mysql_install_db is not run are root, the problem is not observed. Example:

   rm -rf /var/lib/mysql ; mkdir /var/lib/mysql; chown mysql:mysql /var/lib/mysql/ ; sudo -u mysql mysql_install_db --rpm --cross-bootstrap --user=mysql --disable-log-bin --skip-test-db

Regression:

This error does not occur on Debian stable (bullseye) where mariadb package version is 1:10.5.15-0+deb11u1. Hence this is a regression since that version.

Analysis

According to pam-tmpdir: "Many programs use $TMPDIR for storing temporary files. Not all of them are good at securing the permissions of those files. libpam-tmpdir sets $TMPDIR and $TMP for PAM sessions and sets the permissions quite tight. This helps system security by having an extra layer of security, making such symlink attacks and other /tmp based attacks harder or impossible".

Errors like the one being reported are typically seen when directories/files are created by root user in the $TMPDIR and later a non-root user tries to access those files without any further permission changes. libpam-tmpdir tries to ensure that temporary files created by one user are not accidentally accessible to unauthorized users.

During 10.6.x release cycle a change was introduced that makes this mistake. It creates files as 'root' and then tries to access them as 'mysql' user. The problem can be fixed by:

1. Copying the files temporarily created by 'root' user to a location accessible to the 'mysql' user and then setting proper ownership, or by
2. Creating all the temporary files with 'mysql' user to start with.