After upgrading from Mysql 5 to Mysql 8 (needed for one of my websites), ISPConfig is now throwing errors when I click on virtually any link in the ISPConfig web admin page. The error is: "Access denied; you need (at least one of) the SUPER, SYSTEM_VARIABLES_ADMIN or SESSION_VARIABLES_ADMIN privilege(s) for this operation" From what I've read online, it looks like the way that passwords are handled has changed from Mysql 5 to Mysql 8, and this has led to ISPConfig losing access to the database. Is there an easy fix to this? Do I need to downgrade to Mysql 5? Would changing to MariaDB solve the problem, and, if so, is there a way to gracefully do this? Attached is the test script results Code: ##### SERVER ##### IP-address (as per hostname): ***.***.***.*** [WARN] could not determine server's ip address by ifconfig [INFO] OS version is Ubuntu 18.04.4 LTS [INFO] uptime: 10:42:41 up 1 day, 10:22, 2 users, load average: 0.30, 0.21, 0.25 [INFO] memory: total used free shared buff/cache available Mem: 23G 2.6G 10G 91M 10G 20G Swap: 975M 4.5M 971M [INFO] ISPConfig is installed. ##### ISPCONFIG ##### ISPConfig version is 3.2.7p1 ##### VERSION CHECK ##### [INFO] php (cli) version is 7.4.25 [INFO] php-cgi (used for cgi php in default vhost!) is version 7.4.25 ##### PORT CHECK ##### ##### MAIL SERVER CHECK ##### ##### RUNNING SERVER PROCESSES ##### [INFO] I found the following web server(s): Apache 2 (PID 9765) [INFO] I found the following mail server(s): Postfix (PID 1987) [INFO] I found the following pop3 server(s): Dovecot (PID 2100) [INFO] I found the following imap server(s): Dovecot (PID 2100) [INFO] I found the following ftp server(s): PureFTP (PID 2241) ##### LISTENING PORTS ##### (only () Local (Address) [anywhere]:995 (2100/dovecot) [localhost]:10023 (2248/postgrey) [localhost]:10024 (2069/amavisd-new) [localhost]:10025 (1987/master) [localhost]:10026 (2069/amavisd-new) [localhost]:10027 (1987/master) [anywhere]:587 (1987/master) [localhost]:11211 (1561/memcached) [localhost]:6379 (1936/redis-server) [anywhere]:110 (2100/dovecot) [anywhere]:143 (2100/dovecot) [localhost]:45391 (1694/containerd) [anywhere]:465 (1987/master) ***.***.***.***:53 (2262/named) ***.***.***.***:53 (2262/named) [localhost]:53 (2262/named) [anywhere]:21 (2241/pure-ftpd) [anywhere]:22 (1986/sshd) [localhost]:953 (2262/named) [anywhere]:25 (1987/master) [localhost]:3551 (1805/apcupsd) [anywhere]:993 (2100/dovecot) *:*:*:*::*:995 (2100/dovecot) *:*:*:*::*:33060 (4371/mysqld) *:*:*:*::*:10023 (2248/postgrey) *:*:*:*::*:10024 (2069/amavisd-new) *:*:*:*::*:3306 (4371/mysqld) *:*:*:*::*:10026 (2069/amavisd-new) *:*:*:*::*:587 (1987/master) *:*:*:*::*:6379 (1936/redis-server) [localhost]10 (2100/dovecot) [localhost]43 (2100/dovecot) *:*:*:*::*:8080 (9765/apache2) *:*:*:*::*:80 (9765/apache2) *:*:*:*::*:8081 (9765/apache2) *:*:*:*::*:465 (1987/master) *:*:*:*::*:53 (2262/named) *:*:*:*::*:21 (2241/pure-ftpd) *:*:*:*::*:22 (1986/sshd) *:*:*:*::*:953 (2262/named) *:*:*:*::*:25 (1987/master) *:*:*:*::*:443 (9765/apache2) *:*:*:*::*:993 (2100/dovecot) ##### IPTABLES ##### Chain INPUT (policy DROP) target prot opt source destination f2b-sshd tcp -- [anywhere]/0 [anywhere]/0 multiport dports 22 ufw-before-logging-input all -- [anywhere]/0 [anywhere]/0 ufw-before-input all -- [anywhere]/0 [anywhere]/0 ufw-after-input all -- [anywhere]/0 [anywhere]/0 ufw-after-logging-input all -- [anywhere]/0 [anywhere]/0 ufw-reject-input all -- [anywhere]/0 [anywhere]/0 ufw-track-input all -- [anywhere]/0 [anywhere]/0 Chain FORWARD (policy DROP) target prot opt source destination DOCKER-USER all -- [anywhere]/0 [anywhere]/0 DOCKER-ISOLATION-STAGE-1 all -- [anywhere]/0 [anywhere]/0 ACCEPT all -- [anywhere]/0 [anywhere]/0 ctstate RELATED,ESTABLISHED DOCKER all -- [anywhere]/0 [anywhere]/0 ACCEPT all -- [anywhere]/0 [anywhere]/0 ACCEPT all -- [anywhere]/0 [anywhere]/0 ufw-before-logging-forward all -- [anywhere]/0 [anywhere]/0 ufw-before-forward all -- [anywhere]/0 [anywhere]/0 ufw-after-forward all -- [anywhere]/0 [anywhere]/0 ufw-after-logging-forward all -- [anywhere]/0 [anywhere]/0 ufw-reject-forward all -- [anywhere]/0 [anywhere]/0 ufw-track-forward all -- [anywhere]/0 [anywhere]/0 Chain OUTPUT (policy ACCEPT) target prot opt source destination ufw-before-logging-output all -- [anywhere]/0 [anywhere]/0 ufw-before-output all -- [anywhere]/0 [anywhere]/0 ufw-after-output all -- [anywhere]/0 [anywhere]/0 ufw-after-logging-output all -- [anywhere]/0 [anywhere]/0 ufw-reject-output all -- [anywhere]/0 [anywhere]/0 ufw-track-output all -- [anywhere]/0 [anywhere]/0 Chain DOCKER (1 references) target prot opt source destination Chain DOCKER-ISOLATION-STAGE-1 (1 references) target prot opt source destination DOCKER-ISOLATION-STAGE-2 all -- [anywhere]/0 [anywhere]/0 RETURN all -- [anywhere]/0 [anywhere]/0 Chain DOCKER-ISOLATION-STAGE-2 (1 references) target prot opt source destination DROP all -- [anywhere]/0 [anywhere]/0 RETURN all -- [anywhere]/0 [anywhere]/0 Chain DOCKER-USER (1 references) target prot opt source destination RETURN all -- [anywhere]/0 [anywhere]/0 Chain f2b-sshd (1 references) target prot opt source destination RETURN all -- [anywhere]/0 [anywhere]/0 Chain ufw-after-forward (1 references) target prot opt source destination Chain ufw-after-input (1 references) target prot opt source destination ufw-skip-to-policy-input udp -- [anywhere]/0 [anywhere]/0 udp dpt:137 ufw-skip-to-policy-input udp -- [anywhere]/0 [anywhere]/0 udp dpt:138 ufw-skip-to-policy-input tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:139 ufw-skip-to-policy-input tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:445 ufw-skip-to-policy-input udp -- [anywhere]/0 [anywhere]/0 udp dpt:67 ufw-skip-to-policy-input udp -- [anywhere]/0 [anywhere]/0 udp dpt:68 ufw-skip-to-policy-input all -- [anywhere]/0 [anywhere]/0 ADDRTYPE match dst-type BROADCAST Chain ufw-after-logging-forward (1 references) target prot opt source destination LOG all -- [anywhere]/0 [anywhere]/0 limit: avg 3/min burst 10 LOG flags 0 level 4 prefix "[UFW BLOCK] " Chain ufw-after-logging-input (1 references) target prot opt source destination LOG all -- [anywhere]/0 [anywhere]/0 limit: avg 3/min burst 10 LOG flags 0 level 4 prefix "[UFW BLOCK] " Chain ufw-after-logging-output (1 references) target prot opt source destination Chain ufw-after-output (1 references) target prot opt source destination Chain ufw-before-forward (1 references) target prot opt source destination ACCEPT all -- [anywhere]/0 [anywhere]/0 ctstate RELATED,ESTABLISHED ACCEPT icmp -- [anywhere]/0 [anywhere]/0 icmptype 3 ACCEPT icmp -- [anywhere]/0 [anywhere]/0 icmptype 11 ACCEPT icmp -- [anywhere]/0 [anywhere]/0 icmptype 12 ACCEPT icmp -- [anywhere]/0 [anywhere]/0 icmptype 8 ufw-user-forward all -- [anywhere]/0 [anywhere]/0 Chain ufw-before-input (1 references) target prot opt source destination ACCEPT all -- [anywhere]/0 [anywhere]/0 ACCEPT all -- [anywhere]/0 [anywhere]/0 ctstate RELATED,ESTABLISHED ufw-logging-deny all -- [anywhere]/0 [anywhere]/0 ctstate INVALID DROP all -- [anywhere]/0 [anywhere]/0 ctstate INVALID ACCEPT icmp -- [anywhere]/0 [anywhere]/0 icmptype 3 ACCEPT icmp -- [anywhere]/0 [anywhere]/0 icmptype 11 ACCEPT icmp -- [anywhere]/0 [anywhere]/0 icmptype 12 ACCEPT icmp -- [anywhere]/0 [anywhere]/0 icmptype 8 ACCEPT udp -- [anywhere]/0 [anywhere]/0 udp spt:67 dpt:68 ufw-not-local all -- [anywhere]/0 [anywhere]/0 ACCEPT udp -- [anywhere]/0 ***.***.***.*** udp dpt:5353 ACCEPT udp -- [anywhere]/0 ***.***.***.*** udp dpt:1900 ufw-user-input all -- [anywhere]/0 [anywhere]/0 Chain ufw-before-logging-forward (1 references) target prot opt source destination Chain ufw-before-logging-input (1 references) target prot opt source destination Chain ufw-before-logging-output (1 references) target prot opt source destination Chain ufw-before-output (1 references) target prot opt source destination ACCEPT all -- [anywhere]/0 [anywhere]/0 ACCEPT all -- [anywhere]/0 [anywhere]/0 ctstate RELATED,ESTABLISHED ufw-user-output all -- [anywhere]/0 [anywhere]/0 Chain ufw-logging-allow (0 references) target prot opt source destination LOG all -- [anywhere]/0 [anywhere]/0 limit: avg 3/min burst 10 LOG flags 0 level 4 prefix "[UFW ALLOW] " Chain ufw-logging-deny (2 references) target prot opt source destination RETURN all -- [anywhere]/0 [anywhere]/0 ctstate INVALID limit: avg 3/min burst 10 LOG all -- [anywhere]/0 [anywhere]/0 limit: avg 3/min burst 10 LOG flags 0 level 4 prefix "[UFW BLOCK] " Chain ufw-not-local (1 references) target prot opt source destination RETURN all -- [anywhere]/0 [anywhere]/0 ADDRTYPE match dst-type LOCAL RETURN all -- [anywhere]/0 [anywhere]/0 ADDRTYPE match dst-type MULTICAST RETURN all -- [anywhere]/0 [anywhere]/0 ADDRTYPE match dst-type BROADCAST ufw-logging-deny all -- [anywhere]/0 [anywhere]/0 limit: avg 3/min burst 10 DROP all -- [anywhere]/0 [anywhere]/0 Chain ufw-reject-forward (1 references) target prot opt source destination Chain ufw-reject-input (1 references) target prot opt source destination Chain ufw-reject-output (1 references) target prot opt source destination Chain ufw-skip-to-policy-forward (0 references) target prot opt source destination DROP all -- [anywhere]/0 [anywhere]/0 Chain ufw-skip-to-policy-input (7 references) target prot opt source destination DROP all -- [anywhere]/0 [anywhere]/0 Chain ufw-skip-to-policy-output (0 references) target prot opt source destination ACCEPT all -- [anywhere]/0 [anywhere]/0 Chain ufw-track-forward (1 references) target prot opt source destination Chain ufw-track-input (1 references) target prot opt source destination Chain ufw-track-output (1 references) target prot opt source destination ACCEPT tcp -- [anywhere]/0 [anywhere]/0 ctstate NEW ACCEPT udp -- [anywhere]/0 [anywhere]/0 ctstate NEW Chain ufw-user-forward (1 references) target prot opt source destination Chain ufw-user-input (1 references) target prot opt source destination ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:20 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:21 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:22 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:25 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:53 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:80 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:110 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:143 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:443 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:587 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:993 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:995 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:3306 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:8080 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:8081 ACCEPT tcp -- [anywhere]/0 [anywhere]/0 tcp dpt:10000 ACCEPT udp -- [anywhere]/0 [anywhere]/0 udp dpt:53 ACCEPT udp -- [anywhere]/0 [anywhere]/0 udp dpt:3306 ACCEPT tcp -- [localhost] [localhost] tcp dpt:3306 Chain ufw-user-limit (0 references) target prot opt source destination LOG all -- [anywhere]/0 [anywhere]/0 limit: avg 3/min burst 5 LOG flags 0 level 4 prefix "[UFW LIMIT BLOCK] " REJECT all -- [anywhere]/0 [anywhere]/0 reject-with icmp-port-unreachable Chain ufw-user-limit-accept (0 references) target prot opt source destination ACCEPT all -- [anywhere]/0 [anywhere]/0 Chain ufw-user-logging-forward (0 references) target prot opt source destination Chain ufw-user-logging-input (0 references) target prot opt source destination Chain ufw-user-logging-output (0 references) target prot opt source destination Chain ufw-user-output (1 references) target prot opt source destination ##### LET'S ENCRYPT ##### Certbot is installed in /usr/bin/letsencrypt
The issue is not related to the password and you don't have to downgrade MySQL. Login to phpmyadmin as MySQL root user and there you edit the ispconfig database user and grant him one of the permissions mentioned in the error message. Then do flush privileges or restart mysql.
I logged into phpmyadmin and tried to add the SUPER privilege to the ispconfig user. However, phpmyadmin complained about SQL Syntax. When I edited the query, I see: GRANT SUPER ON *.* TO 'ispconfiguser'@'localhost' REQUIRE NONE WITH MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0; I truncated the query to GRANT SUPER ON *.* TO 'ispconfig'@'localhost'; It seemed to take that query, but now I'm getting Error 500 when I try to load ISPConfig. I'm sure that was a boneheaded mistake on my part, but I'm wondering how I can fix this. Thank you for the reply and your assistance!!
Ok. So went ahead and granted global privileges to the ispconfig db user. That seems to at least bring the web interface back and functioning properly. However, I am now concerned that I have given too many privileges to this user and created a security threat. What are the minimum privileges that this user should have?
not sure about the 'require none' being needed at all, but it'll complain about the sql syntax because you were missing 'grant option' ie: Code: GRANT SUPER ON *.* TO 'ispconfiguser'@'localhost' REQUIRE NONE WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0;
