After messing up manually with a lot of components, libraries and files on my server, i ended up screwing the default credentials of MySQL. I didn't know which credentials i set to the admin user, and i was unable to access MySQL as admin in both Plesk and through the command line. Initially, because i didn't know that the original password for the admin user was stored on the /etc/psa/.psa.shadow file and you shouldn't mess with it. After reading a while, the only solution for my trouble was to reset the admin password of the MySQL server, thing that can be easily achieved with an automated script provided by the Plesk team.
In this article, we will explain you how to solve this problem easily.
1. Download Plesk admin user injector PHP Script
As specified in this official article of Plesk, the solution is to update the value of the password of the admin user with the string specified in the /etc/psa/.psa.shadow file. This solution is applicable to the specific situation, where the password of the 'admin'@'localhost' user has been manually changed.
Download the file in your server using wget or curl (whatever tool you prefer) the gzipped file with the script:
wget https://plesk.zendesk.com/hc/article_attachments/360042955514/213364309-restore-admin-user.php.tar.gzThis tar contains a single php file namely 213364309-restore-admin-user.php that contains the code that will be used in the next step. You can extract the content of the tar with the following command:
tar xf 213364309-restore-admin-user.php.tar.gzAlternatively, you can obtain the code from this article as well (we used the code that is available on the described file), so you can create the php file (213364309-restore-admin-user.php) and paste the following code inside:
<?php
/**
* Plesk admin user injector
*
* Restores 'admin'@'localhost' user to the local MySQL/MariaDB server
* Refer to https://support.plesk.com/hc/en-us/articles/213364309
*
* Tested with: MySQL 5.1+ (up to 8.0), MariaDB 5.5+ (up to 10.4)
* Requires: PHP 5.4+, PHP 7.0+
* Maintainer: Aleksandr Bashurov
*
* Copyright 1999-2019. Plesk International GmbH. All rights reserved.
*/
/**
* Class that interacts with the init service
*/
class Service
{
/**
* @var string $init Either 'legacy' or 'systemd'
*/
private $init;
/**
* @var string $serviceName
*/
private $serviceName;
const KNOWN_SERVICES = ['mysqld', 'mysql', 'mariadb'];
/**
* @var string|null $serviceWrapper
*/
private $serviceWrapper;
const KNOWN_SERVICE_WRAPPERS = ['/usr/sbin/service', '/sbin/service'];
public function __construct()
{
if (file_exists('/etc/os-release')) {
/* This file is required by systemd 43+, so it must be present in any supported OS */
$this->init = 'systemd';
} else {
$this->init = 'legacy';
}
$this->serviceName = $this->extractServiceName();
Logger::info('Found init ' . $this->init);
}
/**
* Restarts MySQL server
*
* @return int exit code of the init management utility
*/
public function restartServer()
{
$result = [];
$exitCode = 0;
Logger::write('Restarting MySQL server');
switch ($this->init) {
case 'systemd':
exec('/bin/systemctl restart ' . $this->serviceName, $result, $exitCode);
break;
case 'legacy':
exec($this->getServiceWrapperName() . ' ' . $this->serviceName . ' restart', $result, $exitCode);
break;
}
Logger::info('Restart completed with exit code ' . $exitCode);
return $exitCode;
}
/**
* Searches for the service with the provided name in the init
*
* @throws \Exception if there was no match
*
* @return string
*/
private function extractServiceName()
{
$result = [];
switch ($this->init) {
case 'systemd':
exec('/bin/systemctl list-units --type service', $result);
foreach (self::KNOWN_SERVICES as $name) {
foreach ($result as $service) {
$nameLength = strlen($name);
if (substr(trim($service), 0, 8 + $nameLength) === $name . '.service') {
Logger::info('Determined service name: ' . $name);
return $name;
}
}
}
break;
case 'legacy':
exec($this->getServiceWrapperName() . ' --status-all', $result);
foreach (self::KNOWN_SERVICES as $name) {
foreach ($result as $service) {
$nameLength = strlen($name);
/* This is due to the 'mysql'/'mysqld' */
if (substr(ltrim($service), 0, $nameLength + 1) === $name . ' ') {
Logger::info('Determined service name: ' . $name);
return $name;
}
}
}
break;
}
throw new \Exception('Cannot determine MySQL service name');
}
/**
* Checks SysV/upstart wrapper location
*
* @throws \Exception is none of the default locations is correct
*
* @return string
*/
private function getServiceWrapperName()
{
if ($this->serviceWrapper === null) {
foreach (self::KNOWN_SERVICE_WRAPPERS as $wrapper) {
Logger::debug('Trying legacy wrapper in ' . $wrapper);
if (file_exists($wrapper)) {
Logger::info('Found legacy wrapper in ' . $wrapper);
$this->serviceWrapper = $wrapper;
return $wrapper;
}
}
throw new \Exception('Could not determine service management wrapper name');
}
return $this->serviceWrapper;
}
}
class Connection
{
/**
* @var mysqli $instance
*/
private $instance;
/**
* @var ServerConfigurator $configurator
*/
private $configurator;
/**
* @param ServerConfigurator $configurator Instance of the configurator
*
* @throws \Exception if connection could not be established for some reason
*/
public function __construct($configurator)
{
$this->configurator = $configurator;
$password = $this->configurator->getPassword();
$mysql = mysqli_init();
if ($mysql->options(MYSQLI_OPT_CONNECT_TIMEOUT, 10) === false) {
throw new \Exception('Failed to configure connection timeout!');
}
/* @ here is for Plesk with upgraded MySQL/MariaDB instances */
if (false === @$mysql->real_connect(
Configuration::getHostname(),
Configuration::getUsername(),
$password,
'mysql',
3306,
$this->configurator->getSocket()
)) {
throw new \Exception('Connection to Plesk database failed: ' . mysqli_connect_error());
}
if (!$mysql->set_charset('UTF8')) {
throw new \Exception('Failed to set charset to UTF8: ' . $mysql->error);
}
$this->instance = $mysql;
}
/**
* Checks if the current user has the 'GRANT ALL' privilege on all tables
*
* @return bool
*/
public function hasAllPrivileges()
{
$grantQuery = $this->instance->query('SHOW GRANTS');
if (! $grantQuery instanceof \mysqli_result) {
return false;
}
$grants = $grantQuery->fetch_all()[0][0];
$grantQuery->free();
return substr($grants, 0, 27) === 'GRANT ALL PRIVILEGES ON *.*';
}
/**
* Returns internal mysqli instance
*
* @return \mysqli
*/
public function getConnection()
{
return $this->instance;
}
}
class Server
{
/**
* @var ServerConfigurator $configurator
*/
private $configurator;
/**
* @var Service $service
*/
private $service;
/**
* @var int[]|null $version For example, [5, 5]
*/
private $version;
/**
* @var string|null $flavor Either 'MariaDB', or 'MySQL'
*/
private $flavor;
/**
* @param ServerConfigurator $configurator
* @param Service $service
*/
public function __construct($configurator, $service)
{
$this->configurator = $configurator;
$this->service = $service;
}
/**
* Creates the 'admin'@'localhost' user in the database
*
* @return void
*/
public function injectAdmin()
{
Logger::write('Disabling ACL tables in configuration');
$this->configurator->setSkipGrants(true);
$this->service->restartServer();
try {
$connection = new Connection($this->configurator);
$this->guessVersionAndFlavor($connection);
Logger::debug(
'Determined server version: '
. $this->version[0] . '.'
. $this->version[1]
);
Logger::debug('Determined server flavor: ' . $this->flavor);
$this->runQueries($this->generateSqlQueries(), $connection);
} catch (\Exception $e) {
Logger::error('Could not inject admin user: ' . $e->getMessage());
} finally {
Logger::write('Restoring previous configuration');
$this->configurator->setSkipGrants(false);
$this->service->restartServer();
}
}
/**
* Tries to guess what MySQL version and flavor we have
*
* @param Connection $handle MySQL connection wrapper instance
*
* @return void
*/
private function guessVersionAndFlavor($handle)
{
$connection = $handle->getConnection();
$versionQuery = $connection->query('SHOW VARIABLES LIKE "version"');
$versionValue = $versionQuery->fetch_all()[0][1];
$versionQuery->free();
$versionArray = explode('.', $versionValue);
$this->version = [
intval($versionArray[0]),
intval($versionArray[1]),
];
if (strpos(strtolower($versionValue), 'mariadb') !== false) {
$this->flavor = 'MariaDB';
return;
}
/* Continue checking whether it's MariaDB by looking for AriaDB */
$flavorQuery = $connection->query('SHOW VARIABLES LIKE "aria%"');
if ($flavorQuery->num_rows > 0) {
$this->flavor = 'MariaDB';
$flavorQuery->free();
return;
}
$this->flavor = 'MySQL';
}
/**
* Runs an array of provided queries
*
* @param string[] $queries SQL queries generated by generateSqlQueries
* @param Connection $handle MySQL connection wrapper instance
*
* @return void
*/
private function runQueries($queries, $handle)
{
$connection = $handle->getConnection();
Logger::write('Reloading ACL tables');
$connection->query('FLUSH PRIVILEGES');
foreach ($queries as $query) {
Logger::write('Executing query: `' . $query . '`');
$connection->query($query);
}
}
/**
* Builds an SQL query to add the user to the list
*
* @return string[] Array with commands that should be executed
*/
private function generateSqlQueries()
{
$user = Configuration::getUsername();
$host = Configuration::getHostname();
$password = $this->configurator->getPassword();
/* MySQL after 5.7 enforces plugin authentication */
if ($this->flavor === 'MySQL'
&& $this->version[0] === 5
&& $this->version[1] >= 7
) {
return [
$this->getDeleteQuery($user, $host),
$this->getModernMysqlCreateUser($user, $host, $password),
$this->getGrantQuery($user, $host),
];
}
/* Other versions do not, though */
return [
$this->getDeleteQuery($user, $host),
$this->getCreateUser($user, $host, $password),
$this->getGrantQuery($user, $host),
];
}
/**
* Creates a generic DELETE query
*
* @param string $name MySQL username
* @param string $host MySQL user's hostname
*
* @return string
*/
private function getDeleteQuery($name, $host)
{
return "DROP USER \"{$name}\"@\"{$host}\";";
}
/**
* Creates a generic GRANT ALL query
*
* @param string $user MySQL username
* @param string $host MySQL user's hostname
*
* @return string
*/
private function getGrantQuery($user, $host)
{
return "GRANT ALL ON *.* TO \"{$user}\"@\"{$host}\" WITH GRANT OPTION";
}
/**
* Creates a legacy CREATE USER query (MariaDB 5.5 to 10.4, MySQL 5.1 to 5.6)
*
* @param string $user MySQL username
* @param string $host MySQL user's hostname
* @param string $password Target user password
*
* @return string
*/
private function getCreateUser($user, $host, $password)
{
return "CREATE USER \"{$user}\"@\"{$host}\" IDENTIFIED BY \"{$password}\"";
}
/**
* Creates a modern (plugin-aware) CREATE USER query compatible with MySQL 5.7 and above
*
* @param string $user MySQL username
* @param string $host MySQL user's hostname
* @param string $password Target user password
*
* @return string
*/
private function getModernMysqlCreateUser($user, $host, $password)
{
return "CREATE USER \"{$user}\"@\"{$host}\" IDENTIFIED WITH \"mysql_native_password\" BY \"{$password}\"";
}
}
class ServerConfigurator
{
/**
* @var string $socket Socket file location
*/
private $socket;
/**
* @var string $configFile Main config file location
*/
private $configFile;
const DEFAULT_LOCATIONS = ['/etc/my.cnf', '/etc/mysql/my.cnf'];
/**
* @var string[] $configContent Config file contents
*/
private $configContent;
/**
* @var string[] $configBackup Original config backup
*/
private $configBackup;
/**
* @var string $password Database admin password
*/
private $password;
public function __construct()
{
$this->socket = $this->findSocket();
Logger::debug('Found socket: ' . $this->socket);
$this->configFile = $this->findConfig();
Logger::debug('Found configuration file: ' . $this->configFile);
$this->configContent = $this->configBackup = $this->loadConfig();
$this->password = $this->loadPassword();
}
/**
* Modifies configuration to remove authentication from the server
*
* @param bool $enable
*
* @return void
*/
public function setSkipGrants($enable)
{
if ($enable) {
$this->injectOverride();
} else {
$this->revertConfig();
}
}
/**
* Extracts Plesk 'admin'@'localhost' password
*
* @return string
*/
public function getPassword()
{
return $this->password;
}
/**
* Extracts MySQL socket location
*
* @return string
*/
public function getSocket()
{
return $this->socket;
}
/**
* Checks MySQL socket location in Plesk configuration file
*
* @throws \Exception if the value could not be found
*
* @return string
*/
private function findSocket()
{
$psaConfig = explode(PHP_EOL, file_get_contents('/etc/psa/psa.conf'));
$socketList = array_filter($psaConfig, function ($line) {
return substr(trim($line), 0, 12) === 'MYSQL_SOCKET';
});
if (count($socketList) === 0) {
throw new \Exception('Configuration file `/etc/psa/psa.conf` does not have a socket file specified');
}
$socketEntry = array_values($socketList)[0];
return trim(explode(' ', $socketEntry)[1]);
}
/**
* Searches for the main MySQL configuration file
*
* @throws \Exception if none of the default locations matched
*
* @return string
*/
private function findConfig()
{
foreach(self::DEFAULT_LOCATIONS as $location) {
if (file_exists($location)) {
return $location;
}
}
throw new \Exception('Could not find main configuration file for the database server!');
}
/**
* Loads admin password from the filesystem
*
* @throws \Exception if the file with password does not exist
*
* @return string
*/
private function loadPassword()
{
if (!file_exists('/etc/psa/.psa.shadow')) {
throw new \Exception('Could not locate /etc/psa/.psa.shadow file');
}
return file_get_contents('/etc/psa/.psa.shadow');
}
/**
* Reads the configuration file
*
* @return string[]
*/
private function loadConfig()
{
return explode(PHP_EOL, file_get_contents($this->configFile));
}
/**
* Injects skip-grant-tables and skip-networking to the config
*
* @return void
*/
private function injectOverride()
{
$sectionPosition = array_search('[mysqld]', $this->configContent);
if ($sectionPosition === false) {
$this->configContent = array_merge(
$this->configContent,
[
'',
'[mysqld]',
'skip-grant-tables',
'skip-networking',
]
);
} else {
array_splice(
$this->configContent,
$sectionPosition + 1,
0,
[
'skip-grant-tables',
'skip-networking',
]
);
}
$this->storeConfig($this->configContent);
}
/**
* Saves configuration back to the file
*
* @param string[] $config Target configuration
*
* @return void
*/
private function storeConfig($config)
{
file_put_contents($this->configFile, implode(PHP_EOL, $config));
}
/**
* Reverts configuration to the one used before script was executed
*
* @return void
*/
private function revertConfig()
{
$this->storeConfig($this->configBackup);
}
}
final class Configuration
{
/**
* @var int $verbosity
*/
private static $verbosity = 0;
/**
* @var string $username
*/
private static $username = 'admin';
/**
* @var string $hostname
*/
private static $hostname = 'localhost';
/**
* Changes verbosity for the Logger
*
* @param int $newLevel
*
* @return void
*/
public static function setVerbosity($newLevel)
{
if (!is_int($newLevel)) {
throw new \Exception("Type Error: expected integer, received " . gettype($newLevel));
}
static::$verbosity = $newLevel;
}
/**
* Changes target username
*
* @param string $username
*
* @return void
*/
public static function setUsername($username)
{
static::$username = $username;
}
/**
* Changes target hostname
*
* @param string $hostname
*
* @return void
*/
public static function setHostname($hostname)
{
static::$hostname = $hostname;
}
/**
* Extracts current verbosity level
*
* @return int
*/
public static function getVerbosity()
{
return static::$verbosity;
}
/**
* Fetches target username for the restoration
*
* @return string
*/
public static function getUsername()
{
return static::$username;
}
/**
* Fetches target hostname for the restoration
*
* @return string
*/
public static function getHostname()
{
return static::$hostname;
}
}
final class Logger
{
private function __construct() {}
/**
* Logs if the global verbosity level is debug
*
* @param string $value
*
* @return void
*/
public static function debug($value)
{
if (Configuration::getVerbosity() >= 2) {
fwrite(STDERR, 'DEBUG: ' . $value . PHP_EOL);
}
}
/**
* Logs if the global verbosity level is verbose
*
* @param string $value
*
* @return void
*/
public static function info($value)
{
if (Configuration::getVerbosity() >= 1) {
fwrite(STDOUT, 'INFO: ' . $value . PHP_EOL);
}
}
/**
* Logs to the console stderr
*
* @param string $value
*
* @return void
*/
public static function error($value)
{
fwrite(STDERR, '!ERROR: ' . $value . PHP_EOL);
}
/**
* Logs to the console stdout
*
* @param string $value
*
* @return void
*/
public static function write($value)
{
fwrite(STDOUT, $value . PHP_EOL);
}
}
/**
* @return void
*/
function helpCommand()
{
$script_name = basename(__FILE__);
Logger::write(<<<HELP
$script_name: Inject Plesk administrator user into the MySQL server
Version: 1.0
Usage:
$script_name -v -d -y
Options:
-v Enables verbose output
-d Enables debug output
-y Disables user pre-check
-h Shows this help message
HELP
);
}
/**
* Parses args and returns an associative array
*
* @return (bool|int)[]
*/
function saneOpts()
{
$options = getopt('vdh');
if ($options === false) {
return [];
}
return [
'verbosityLevel' => (
array_key_exists('d', $options) ? 2 : (
array_key_exists('v', $options) ? 1 : (
0
))),
'helpMessage' => array_key_exists('h', $options),
'ignorePrecheck' => array_key_exists('y', $options),
];
}
/**
* Checks that basic requirements are met
*
* @throws \Exception if user does not have root access
* @throws \Exception if script is executed on Windows
*
* @return void
*/
function checkRequirements()
{
if (strtoupper(substr(PHP_OS, 0, 3)) === 'WIN') {
/* On Windows, */
throw new \Exception('This script must not be run on Windows');
}
if (posix_geteuid() !== 0) {
throw new \Exception('This script must be executed with root privileges');
}
}
/**
* Checks that user does not actually exist or has improper rights
*
* @param ServerConfigurator $config
*
* @return bool should the script be executed
*/
function checkUser($config)
{
try {
$connection = new Connection($config);
return !$connection->hasAllPrivileges();
} catch (\Exception $e) {
return true;
}
}
/**
* Main function of the script
*
* @return void
*/
function main()
{
$script_name = basename(__FILE__);
$options = saneOpts();
if ($options['helpMessage']) {
helpCommand();
exit(0);
}
checkRequirements();
Configuration::setVerbosity($options['verbosityLevel']);
$dbConf = new ServerConfigurator();
$service = new Service();
Logger::write('Checking if administrator exists');
if (checkUser($dbConf) === false && $options['ignorePrecheck'] === false) {
Logger::error(
'User \'admin\'@\'localhost\' already exists on the system and has required privileges.'
. 'Run the script with \'-y\' option to skip this check'
);
exit (1);
}
$server = new Server($dbConf, $service);
$server->injectAdmin();
Logger::write('Execution has completed');
}
main();Now that you have the PHP script that will be used, you can proceed with the next step.
2. Run PHP script through the Plesk CLI
Now what you only need to do, is to run the PHP script using the plesk php command, providing the filepath as argument:
// Run PHP file through plesk in the command line
plesk php 213364309-restore-admin-user.phpAfter running the script, if everything did run properly, you should be able now to login as admin in MySQL, for example through the command line:
MYSQL_PWD=`cat /etc/psa/.psa.shadow` mysql -u adminHappy coding ❤️!
