Database backup command failed with "Access Denied when using LOCK TABLES" error

[waddyvic]

I'm running Passbolt CE 5.4.1 (installed from DEB package) on Raspberry Pi OS 64bit (Debian 12) and configured a cron job to backup database nightly. A few days ago I realized the nightly backup started failing, and I couldn't resolve the issue after some investigation. Hoping to get some help.

I used the following command documented on this page: https://www.passbolt.com/docs/hosting/backup/from-packages/#1-the-database

sudo su -s /bin/bash -c "/usr/share/php/passbolt/bin/cake passbolt sql_export" www-data

The command failed with the following output:

---

Saving backup file: /var/lib/passbolt/tmp/cache/database/backup_1757128060.sql
mariadb-dump: Got error: 1045: "Access denied for user 'passboltadmin'@'localhost' (using password: YES)" when using LOCK TABLES
There was an error running the dump.
Please ensure on MySQL that the user has PROCESS privileges.
Database passboltdb
User passboltadmin
Something went wrong!

I've confirmed the username, password and database name are correct in /etc/passbolt/passbolt.php. I'm able to use Passbolt Web UI and browser plugin without issue, and I also can connect to the database via command line using that credential.

I then checked the grants of the database user. It has all privileges on the database, but only USAGE privilege globally:

MariaDB [(none)]> show grants for passboltadmin@localhost;
+----------------------------------------------------------------------------------------------------------------------+
| Grants for passboltadmin@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON . TO passboltadmin@localhost IDENTIFIED BY PASSWORD 'masked' |
| GRANT ALL PRIVILEGES ON passboltdb.* TO passboltadmin@localhost |
+----------------------------------------------------------------------------------------------------------------------+

Since the error output said to ensure the user has PROCESS privileges, I then granted the privilege to the user globally:

+----------------------------------------------------------------------------------------------------------------------+
| Grants for passboltadmin@localhost |
+----------------------------------------------------------------------------------------------------------------------+
| GRANT PROCESS ON . TO passboltadmin@localhost IDENTIFIED BY PASSWORD 'masked' |
| GRANT ALL PRIVILEGES ON passboltdb.* TO passboltadmin@localhost |
+----------------------------------------------------------------------------------------------------------------------+

After granting the privilege and flushing privileges, the sql_dump command still return the same error.

I read the source code of the command and ran the mariadb-dump command directly. It gives the same access denied when using lock tables error. Searching on Google found some threads suggesting adding --single-transaction to the command, which dumped the database without issue:

mariadb-dump --single-transaction -hlocalhost -upassboltadmin -pmasked passboltdb > pbdump.sql

This somewhat indicates it's a privilege issue, but I do not know what the correct sets of privilege should be. Can someone shed some lights on this issue? Much appreciated.