We have a master and 4 slave servers that have been updated to 20.04. After the update, and going through the 20.04 install to grab missing packages, etc., everything appeared to be functioning. Changes were being sent to the master from the slaves. However, when testing the ispconfig_update.sh script, it fails when we provide the root credentials with an Access denied... error. The script will run with the credentials that are stored in the config.inc.php file. Also, cannot log into mysql on master from slave with root creds. Can login with the slave account credentials, which is consistent with above. I am not sure how to allow the remote root access without jeopardizing security. Any suggestions?
Which version did you upgrade from? Are you sure the credentials are correct? How did you set up the accounts? According to the multiserver guide?
Yes, followed the guide for 18.04. Several years ago, originally installed on Debian 6 but decided to move to Ubuntu and reconfigured everything following install for 18.04. We've had this setup running for a couple of years on Ubuntu and did not have an issue with updates until the upgrade to 20.04. We can log into mysql on master using the root credentials, so they are still as originally created. It's the slaves that are not able to login with root credentials remotely.
Any recent data centre or server or ip or port or firewall migration or change that might cause this, (just a very wild guess), since permission to remotely access the database are related to them.
Thanks for the reply. I may not have made myself clear. The slave accounts are fine and always have been, the root account is fine, too until a remote connection is attempted. As mentioned, this system with the accounts that were created during the original install have worked until the master was upgraded from Ubuntu 18.04 to 20.04. Any changes that are made through the Control Panel are written to the master database and the slave servers. For instance, a DNS change made though the control panel are written to the DNS master and replicated to the DNS slaves. The job queue processes without any issue. So, all the slave server accounts are still working properly, as far as I can tell. The issue occurs when I run the ispconfig_update.sh script. If I do that and specify root (which has always worked in the past), I get an Access denied error. If, instead of using root, I use the slave server's credentials, the update goes through. After all the updates/upgrades were completed, I tested the update script by selecting the "nightly' option, as the servers were already running the latest version of ISPConfig. Again, if I specified root and the root password, I'd receive the Access denied error. If I specified the ispcsrv** user and password, the update would apply and it would be reflected in System State. I then re-ran the script and selected "stable" and it ran if i used the ispcsrv** user/password. It failed when I tried using root/password. Trying to connect to the master db from any slave via ssh fails using root but succeeds when using the slave's ispcsrv** user. I do not know if the upgrade from UBUNTU 16.04 to 20.04 changed root permissions within mysql but that's what appears may have happened. Hope this clarifies
Thanks ahrasis. No changes to infrastructure or networking. Strictly an upgrade from 18.04 to 20.04 on all servers. All slaves were updated before the master and updates using the ipsconfig_update.sh script worked flawlessly with root until the master was upgraded to 20.04. After the master was updated, the Access denied errors occurred. That's what makes me think it may be related to an update to mysql during the 18.04 to 20.04 upgrade. If it were networking/firewall, none of the updates using the script would work. Port 3306 is listening on all IPs, as evidenced by the fact that a connection can be made from slave to master via ssh with the ispcsrv** user.
I think this is a correct behaviour as slave should not be able to access master db as root unless a permission is given beforehand in master db. Do check if there is any such permission in master db for root@slave-ip.
That behavior changed with the Ubuntu upgrade. I will verity mysql.user table for permissions. Thank you.
It's the same as before in ISPConfig. The root user account is added normally at install time, and ispconfig uses it only for the installation and updates.
Thanks for your reply Till. As mentioned, this was only an Ubuntu upgrade to an existing Ubuntu 18.04, not an update to ISPConfig. IspConfig was on the latest version prior to the Ubuntu upgrade and was updating without errors using mysql root during the update. Prior to the upgrade, using mysql credentials for an update to ISPConfig through the ispconfig_update.sh script worked flawlessly. After the upgrade to 20.04, root can no longer access mysql with the mysql root user. My guess is that the upgrade to 20.04 mysql (actually, mariadb) may have locked down the root permissions when accessing mysql remotely. I have no way of telling whether the privileges for root are different after the upgrade compared to before. Should mysql root have remote access privileges? If so, what mysql command should be used to provide remote access? Will "CREATE USER 'root'@'%' IDENTIFIED BY 'a password';: work? Does this present a security concern?
Just guessing, try this from the ubuntu 20 perfect server guide: Code: echo "update mysql.user set plugin = 'mysql_native_password' where user='root';" | mysql -u root Yes, remote root access is needed for the slave servers during (and only during) ISPConfig installation and updates. You could actually disable those remote root users the rest of the time. Refer to a multi-server guide, eg. see the "CREATE USER" statements in https://www.howtoforge.com/multiser...se-servers-on-debian-squeeze-with-ispconfig-3 Yes, and yes; it allows root to login from any host with that password, you want to limit it to only your slave server hostnames/ip addrs. Normally you can also firewall access to port 3306 from those same ip addrs and not leave mysql open to the world.
Thank you for your reply Jesse. I added root@% user and everything is working as it was prior to upgrade to 20.04. I still believe the upgrade of mariadb probably removed the remote access for root, or I may have inadvertently removed it during the upgrade process. I have removed all privileges and will re-enable next time an ISPConfig update is released.
Note, you asked if that would work, that's the first "Yes", then you asked if that's a security concern, and that is my second "yes" - ie. it would be better not to do this for security reasons. Although I guess if you disabled that in the interim, it pretty well mitigates that conern.
Understood the "yes and yes..." I had actually created the user and tested before your response came back. After I read it, I removed the privs. Although root can still login remotely, access to databases is denied. If there's a better solution, I'm happy to take suggestions. DB administration is not my forte.
Agreed with @Jesse Norell on his security points, even to turn off slave root access after install and update i.e. root@slave-ip to master db. To be noted that in the manual and tutorial for multiple dedicated server the suggested access for slave root is via root@slave-ip, with further note that, if all servers are in one LAN, slave root access to master db should further be limited to root@slave-lan-ip as it is the most secure. Your personal preference may be for easiness in adding-up and allowing root acces for all of your servers, then later removed the privileges but do note negligence to the later happens, however, his advise as well as the tutorial and the manual, stand for all other server admins, so that connection to their master db server will be best secured.
Hi @ahrasis. Thanks for the explanation. Maybe I've misunderstood all along. When we did the original installs several years ago on Debian 6, we created all of the root@slave-ip users per instructions: CREATE USER 'root'@'192.168.0.106' IDENTIFIED BY 'myrootpassword'; GRANT ALL PRIVILEGES ON * . * TO 'root'@'192.168.0.106' IDENTIFIED BY 'myrootpassword' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ; Those users exist for each slave server. It was my understanding that the root@slave-ip user is used when there are communications between the master and slaves for changes that are made through the Control Panel or synchronization between all machines. However, when updating ISPConfig to a new version, the process needs to login to mysql with the mysql root user on the master db. You need to provide the master's mysql root credentials. When you run ispconfig_update.sh, you are prompted for the root password, not the slaves' root. I believe that is what Till was confirming but could be wrong. From the prompts during the upgrade: MySQL master server hostname []: <-- web.example.tld MySQL master server root username [root]: <-- root MySQL master server root password []: <-- Enter the root password of the master server here MySQL master server database name [dbispconfig]: <-- dbispconfig This does not use the root@slave-ip user created above. So, I think the two uses of root are being confused. Or else my understanding is incorrect. I was running into issues with the ISPConfig update, not communications between servers for operations conducted through Console changes or cron jobs. Is that a misunderstanding and, f so, how do you force ispconfig_update.sh to use the root@slave-ip? Again, thanks to all for the comments
ISPConfig creates a user for each slave that's used for connecting. The root password is a root account on the master server that allows access from your IP. It's used for updating the server, not for regular communication.
maybe during the update process, mysql_secure_installation was run, and all the default options used as answers? this would remove/disable root remote access from mysql.
