PHPMyAdmin¶
phpMyAdmin is a free software tool written in PHP, intended to handle the administration of MySQL over the Web. phpMyAdmin supports a wide range of operations on MySQL and MariaDB. Frequently used operations (managing databases, tables, columns, relations, indexes, users, permissions, etc) can be performed via the user interface, while you still have the ability to directly execute any SQL statement.
Installing PHPMyAdmin¶
Enter a sudo shell for ease
sudo -s
Install the required dependencies:
apt update && apt install php7.4-{bz2,json,mbstring,zip,gd,curl,xml,common,opcache,imagick,mysql}
Download the latest version of PHPMyAdmin to the /opt directory. Yoy can find the current version from https://www.phpmyadmin.net/downloads/
cd /opt && wget https://files.phpmyadmin.net/phpMyAdmin/xxx/phpMyAdmin-xxx-english.tar.gz
mkdir /usr/share/phpmyadmin
Extract the files to the installation directory:
tar xzf phpMyAdmin-xxx-english.tar.gz -C /usr/share/phpmyadmin --strip-components=1
Configuration¶
Setting the folder permissions¶
We'll create a dedicated user and group to run PHPMyAdmin:
groupadd www-phpmyadmin
useradd -g www-phpmyadmin www-phpmyadmin
Now we'll create a tmp directory and apply the correct permissions to the directory structure:
mkdir /usr/share/phpmyadmin/tmp
chown -R www-phpmyadmin:www-data /usr/share/phpmyadmin
chown -R www-phpmyadmin:www-phpmyadmin /usr/share/phpmyadmin/tmp
chmod 770 /usr/share/phpmyadmin/tmp
chmod g+s /usr/share/phpmyadmin
chmod g+s /usr/share/phpmyadmin/tmp
find /usr/share/phpmyadmin -type f -name '*.php' -exec chmod 600 {} \;
Creating the Apache2 virtualhost¶
Now we'll create an Apache virtual host:
nano /etc/apache2/sites-available/pma2.example.com.conf
<VirtualHost 127.0.0.1:8080>
ServerAdmin admin@example.com
ServerName pma2.example.com
DocumentRoot /usr/share/phpmyadmin
<Directory /usr/share/phpmyadmin/templates>
Require all denied
</Directory>
<Directory /usr/share/phpmyadmin/libraries>
Require all denied
</Directory>
<Directory /usr/share/phpmyadmin/setup/lib>
Require all denied
</Directory>
<Directory /usr/share/phpmyadmin>
Options SymLinksIfOwnerMatch
DirectoryIndex index.php
</Directory>
<FilesMatch \.php$>
SetHandler "proxy:unix:/var/run/php/php7.4-fpm-www-phpmyadmin.sock|fcgi://localhost/"
</FilesMatch>
SetEnvIf HTTPS on HTTPS=on
RemoteIPHeader X-Forwarded-For
RemoteIPTrustedProxy 127.0.0.1
ErrorLog ${APACHE_LOG_DIR}/error.log
CustomLog ${APACHE_LOG_DIR}/access.log combined
</VirtualHost>
Enable the virtualhost:
a2ensite pma2.example.com.conf
Creating the Nginx server block¶
Now we'll create the Nginx server block. As an example of usage, we'll also enable modsecurity on the block and disable some rules that cause false-positives:
nano /etc/nginx/sites-available/pma2.example.com
server {
listen 99 ssl http2;
listen [::]:99 ssl http2;
server_name pma2.example.com;
client_max_body_size 100M;
modsecurity on;
modsecurity_rules_file /etc/nginx/modsec/main.conf;
ssl_certificate /etc/letsencrypt/ecc-certs/pma2.example.com/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/ecc-certs/pma2.example.com/privkey.pem;
ssl_certificate /etc/letsencrypt/rsa-certs/pma2.example.com/fullchain.pem;
ssl_certificate_key /etc/letsencrypt/rsa-certs/pma2.example.com/privkey.pem;
ssl_trusted_certificate /etc/letsencrypt/ecc-certs/pma2.example.com/chain.pem;
include /etc/nginx/custom-config/ssl.conf;
include /etc/nginx/custom-config/header.conf;
location / {
include /etc/nginx/custom-config/proxy.conf;
proxy_pass http://127.0.0.1:8080;
client_max_body_size 100M;
modsecurity_rules '
SecRuleRemoveById 942100
SecRuleRemoveById 941120
SecRuleRemoveById 942360
';
}
}
In this server block, we're restricting the site to port 99. In IPTables (see tutorial) we only allow your own personal static IP address (and possibly also a dynamic DNS IP) to this port.
Issuing the SSL certificates¶
We now create the SSL certificates:
Issue an RSA certificate and install to a custom location
~/.acme.sh/acme.sh --issue --dns dns_cloudns -d pma2.example.com --keylength 4096 --key-file /etc/letsencrypt/rsa-certs/pma2.example.com/privkey.pem --ca-file /etc/letsencrypt/rsa-certs/pma2.example.com/chain.pem --cert-file /etc/letsencrypt/rsa-certs/pma2.example.com/cert.pem --fullchain-file /etc/letsencrypt/rsa-certs/pma2.example.com/fullchain.pem --pre-hook "mkdir -p /etc/letsencrypt/rsa-certs/pma2.example.com" --post-hook "find /etc/letsencrypt/rsa-certs/pma2.example.com/ -name '*.pem' -type f -exec chmod 600 {} \;" --renew-hook "find /etc/letsencrypt/rsa-certs/pma2.example.com/ -name '*.pem' -type f -exec chmod 600 {} \; -exec service nginx reload \;"
and issue an ECC certificate
~/.acme.sh/acme.sh --issue --dns dns_cloudns -d pma2.example.com --keylength ec-384 --key-file /etc/letsencrypt/ecc-certs/pma2.example.com/privkey.pem --ca-file /etc/letsencrypt/ecc-certs/pma2.example.com/chain.pem --cert-file /etc/letsencrypt/ecc-certs/pma2.example.com/cert.pem --fullchain-file /etc/letsencrypt/ecc-certs/pma2.example.com/fullchain.pem --pre-hook "mkdir -p /etc/letsencrypt/ecc-certs/pma2.example.com" --post-hook "find /etc/letsencrypt/ecc-certs/pma2.example.com/ -name '*.pem' -type f -exec chmod 600 {} \;" --renew-hook "find /etc/letsencrypt/ecc-certs/pma2.example.com/ -name '*.pem' -type f -exec chmod 600 {} \; -exec service nginx reload \;"
Now we enable the site, check the config, and reload:
ln -s /etc/nginx/sites-available/pma2.example.com /etc/nginx/sites-enabled/
nginx -t
service nginx reload
Setting the PHPMyAdmin blowfish key¶
Now we rename the sample PHPMyAdmin config file:
cp /usr/share/phpmyadmin/config{.sample,}.inc.php
chown www-phpmyadmin:www-data /usr/share/phpmyadmin/config.inc.php
And edit:
nano /usr/share/phpmyadmin/config.inc.php
We need to set the blowfish secret. A blowfish key can be obtained from https://phpsolved.com/phpmyadmin-blowfish-secret-generator/
...
/**
* This is needed for cookie based authentication to encrypt password in
* cookie. Needs to be 32 chars long.
*/
$cfg['blowfish_secret'] = 'plSW/=Ky24yD4gH2}=},zFn48Vx1rml5'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
...
Creating the PHP-FPM Pool¶
Now we create our PHP-FPM pool:
nano /etc/php/7.4/fpm/pool.d/www-phpmyadmin.conf
; pool name
[www-phpmyadmin]
; Unix user/group of processes
; will be used.
user = www-phpmyadmin
group = www-phpmyadmin
listen.owner = www-data
listen.group = www-data
listen.mode = 0660
; Socket to which the Apache will connect
listen = /run/php/php7.4-fpm-www-phpmyadmin.sock
pm = ondemand
pm.max_children = 5
pm.process_idle_timeout = 10s
pm.max_requests = 500
php_admin_value[allow_url_fopen] = On
php_admin_value[allow_url_include] = On
php_admin_value[memory_limit] = 512M
php_admin_flag[output_buffering] = Off
php_admin_value[max_execution_time] = 1800
php_admin_value[max_input_time] = 3600
php_admin_value[post_max_size] = 10M
php_admin_value[upload_max_filesize] = 10M
php_admin_value[upload_tmp_dir] = /usr/share/phpmyadmin/tmp
php_admin_value[max_file_uploads] = 100
php_admin_flag[session.cookie_secure] = True
php_admin_value[disable_functions] = exec,passthru,shell_exec,system,proc_open,popen,curl_exec,curl_multi_exec,parse_ini_file,show_source
php_admin_value[open_basedir] = /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/javascript/:/usr/share/php/tcpdf/:/usr/share/doc/phpmyadmin/:/usr/share/php/phpseclib/
After running the site for a while, feel free to come back and tune the settings according to the instructions in the earlier PHP-FPM guide
Now check the Apache2 and Nginx configs:
apache2ctl configtest
nginx -t
And restart the services:
service php7.4-fpm restart && service apache2 restart && service nginx reload
You should now be able to log in to PHPMyAdmin at https://pma2.example.com:99
Login with MySQL root and password. After logging in, you may get a warning that some features are disabled because configuration isn't complete. Following the link should take you to another page where another link allows you to automatically set up the phpmyadmin database.
Securing with Fail2Ban (Optional)¶
If you've restricted your PHPMyAdmin server to a static IP address then you probably don't need to do this.
These steps will configure Fail2Ban to ban IP Addresses attempting (and hopefully failing) to login to your PHPMyAdmin page.
PHPMyAdmin unfortunately uses mod_php in order to create the authentication error message in Apache2. We aren't using mod_php, we're using php-fpm. The only way I've found of creating the error message is by hacking one of the php files. Not ideal!
Edit the Authentication Plugin php file:
sudo nano /usr/share/phpmyadmin/libraries/classes/Plugins/AuthenticationPlugin.php
Within the getErrorMessage function, add the line error_log('phpmyadmin: authentification failed'); just before the final return:
public function getErrorMessage($failure)
{
...
error_log('phpmyadmin: authentification failed');
return __('Cannot log in to the MySQL server');
}
Restart Apache2:
sudo service apache2 restart
Now when we test by intentionally entering an incorrect password, and then open the /var/log/apache2/error.log file, we'll see a line that looks like the following:
[Fri Sep 11 16:37:14.598234 2020] [proxy_fcgi:error] [pid 3468701:tid 139855040329472] [client 48.12.117.14:0] AH01071: Got error 'PHP message: phpmyadmin: authentification failed'
We can now create a regex to capture this IP address. Create a Fail2Ban filter:
sudo nano /etc/fail2ban/filter.d/phpmyadmin-fpm.conf
[INCLUDES]
before = common.conf
[Definition]
_daemon = phpMyAdmin
failregex = ^.*\[client <HOST>:[0-9]+\].* phpmyadmin: authentification failed.*$
ignoreregex =
And then create a Fail2Ban jail:
sudo nano /etc/fail2ban/jail.d/phpmyadmin-fpm.local
[phpmyadmin-fpm]
enabled = true
filter = phpmyadmin-fpm
port = 99
logpath = %(apache_error_log)s
maxretry = 1
findtime = 36000
bantime = 86400
action = iptables-allports
Reload Fail2Ban:
sudo fail2ban-client reload
Setting up 2FA¶
Two factor authentication can be set up using HOTP and TOTP (software based), or by FIDO U2F )hardware based). I use the former and use the android app andOTP to manage all my Two-Factor Authentications.
Log in to PHPMyAdmin, then go to Settings, then Two-Factor Authentication.
Select 'Authentication Application (2FA)' then click the 'Configure two-factor authentication' button.
It will then display a QR code which you can scan with your andOTP app. Once scanned, an entry for PHPMyAdmin will have appeared on the list. Use the generated code and enter it in the 'Authorisation Code' field in PHPMyAdmin.
Two Factor Authentication is now configured.