Resolved upgrade bug

[jezisvole]

Plesk Obsidian 18.0.79 update deletes the main remote database
administrator when the legacy APSC account shares the same username.

The remote Plesk database uses plesk_db_admin for all DSN sections.

The upgrade function drop_user_database_apsc() reads the username from:

SELECT val FROM misc WHERE param = 'aps_login'

It then executes db_drop_user() for that username, which drops all matching
MySQL/MariaDB accounts.

Because misc.aps_login contained plesk_db_admin, the updater deleted the main
Plesk database administrator. The following FLUSH PRIVILEGES and all subsequent
post-install actions failed with ERROR 1045.

The upgrade cannot recover because deletion of misc.aps_login occurs only after
db_drop_user(), but database access is already lost at that point.

Workaround:
UPDATE misc SET val='nonexistent_dummy_user' WHERE param='aps_login';
restore plesk_db_admin;
run Plesk repair again.

Expected behavior:
The updater must not drop the APSC username if it is also used as the main Plesk
database administrator, or it must remove the misc metadata before dropping the
account.

 

[Sebahat.hadzhi]

Hi, @jezisvole . Could you please confirm the OS of the server and provide step-by-step instructions on how to replicate the behavior. It's not entirely clear to me what exactly is the current configuration.

 

[jezisvole]

Hi, @jezisvole . Could you please confirm the OS of the server and provide step-by-step instructions on how to replicate the behavior. It's not entirely clear to me what exactly is the current configuration.

OS: Ubuntu 22.04 LTS

Plesk version before upgrade: 18.0.78
Target version: 18.0.79

The Plesk system databases are hosted on a remote MariaDB server.

All DSN sections in /etc/psa/private/dsn.ini used the same database account:

[plesk]
dbname = remote_psa
host = db-plesk
username = plesk_db_admin

[apsc]
dbname = remote_apsc
host = db-plesk
username = plesk_db_admin

[roundcube]
dbname = remote_roundcubemail
host = db-plesk
username = plesk_db_admin

Before the upgrade, the misc table in the main Plesk database contained:

SELECT val FROM misc WHERE param = 'aps_login';

Result:

plesk_db_admin

Steps to reproduce:

1. Configure Plesk to use a remote MariaDB server.
2. Use the same account, plesk_db_admin, for the main Plesk database and the APSC database.
3. Ensure that the misc table contains aps_login=plesk_db_admin.
4. Upgrade from Plesk 18.0.78 to Plesk 18.0.79.
5. During drop_user_database_apsc(), the updater reads the APSC username from the misc table.
6. db_drop_user() removes all MariaDB accounts matching that username.
7. The main plesk_db_admin account is deleted.
8. Subsequent post-install actions fail with ERROR 1045.

The relevant code from bootstrapper.sh is:

drop_user_database_apsc()
{
local inten="drop APSC database"
echo_try "$inten"
local apsc_db_name=`db_read_misc_attr "aps_database"`
if db_test_database "$apsc_db_name"; then
db_backup "mysql.plesk.apsc.prerm.${product_version}" "$apsc_db_name"
db_do "DROP DATABASE $apsc_db_name"
db_do "DELETE FROM misc WHERE param IN ('aps_database', 'aps_host', 'aps_port')"
fi
suc

inten="drop APSC user"
echo_try "$inten"
local apsc_db_user=`db_read_misc_attr "aps_login"`
if [ -n "$apsc_db_user" ]; then
db_drop_user "$apsc_db_user"
db_do "DELETE FROM misc WHERE param IN ('aps_login', 'aps_password', 'aps_secure_passwords')"
fi
suc
}

The db_drop_user() function removes all accounts with the specified username:

db_drop_user()
{
local user="$1"
local mysql_db_name="mysql"

db_select "(SELECT DISTINCT Host FROM user WHERE User = '$user')
UNION (SELECT DISTINCT Host FROM db WHERE User = '$user')
UNION (SELECT DISTINCT Host FROM tables_priv WHERE User = '$user')
UNION (SELECT DISTINCT Host FROM columns_priv WHERE User = '$user')
UNION (SELECT DISTINCT Host FROM procs_priv WHERE User = '$user')
ORDER BY Host"

[ -n "$db_select_output" ] || return 0

for host in $db_select_output; do
db_do "DROP USER '$user'@'$host'"
done

db_do "FLUSH PRIVILEGES"
}

Because misc.aps_login contained plesk_db_admin, the updater deleted the active database administrator account used by the Plesk installation.

The subsequent FLUSH PRIVILEGES and all following database operations failed because the updater had already removed its own database account.

The deletion of the obsolete misc entries was therefore never reached, which also caused every subsequent Repair attempt to repeat the same failure.

Workaround used:

UPDATE misc
SET val = 'plesk_apsc_dummy'
WHERE param = 'aps_login';

I then restored the plesk_db_admin account and ran Plesk Repair again. The repair completed successfully.

Using one shared database account for all Plesk system databases may not be considered best practice, and separate accounts with restricted privileges may be preferable.

However, the upgrade logic should still protect the primary Plesk database account. It should not unconditionally delete the account referenced by aps_login without checking whether the same username is also used for the main Plesk database connection.

 

[Sebahat.hadzhi]

Thank you for the clarification. The behavior is recognized as a bug with ID PPPM-15443. Our team already created a fix which will validate against the currently used DB/user for Plesk. The same will be introduced in one of the upcoming Plesk updates. Thank you for bringing our attention to the issue.

 

[Sebahat.hadzhi]

@jezisvole , the behavior you reported with the upgrade failure has been fixed as of Plesk Obsidian 18.0.80:

Fixed an issue where updating to Plesk Obsidian 18.0.79 would fail mid update if the Plesk server’s database was hosted remotely, and if the Plesk server and/or the remote database server ran on Ubuntu 22.04. (PPPM-15443)

Please let us know in case you face any further issues.