How to install phpMyAdmin on AWS

Introduction

Getting started

Spin up an EC2 instance

Connecting to the instance

Installing prerequisites

Install & secure MariaDB

Install a webserver

Installing phpMyAdmin

Running phpMyAdmin with nginx

Running phpMyAdmin in Apache

Test phpMyAdmin on your browser

Harden your setup

Installing a Letsencrypt certificate

Prerequisites

Create certificate for nginx

Letsencrypt Certificate for Apache

Browse your site in HTTPS

Generating a self-signed certificate

Install certificate on nginx

Install on Apache

Test HTTPS

Block HTTP access

Enable two-factor authentication

Common issues

Cant create Letsencrypt certificate

The site certificate doesn’t work

Can’t find correct Fedora image

Database connection fails

Access denied to the database

Remove two-factor authentication

References

Conclusion

Introduction

phpMyAdmin is a popular web-based free and open-source application that is used to manage MySQL and MariaDB databases. It is mainly written in PHP and has quickly become a popular tool to manage web services.

It is often thought that setting up a secure phpMyAdmin is a tough task. However, we’re here to sort that out for you.

This article will guide you on how to install the prerequisites and the application. We will start by spinning up an EC2 server in AWS. Then we’ll move on to a MariaDB database installation, a webserver, and phpMyAdmin. Finally, we’ll harden the system with SSL and two-factor authentication to keep the bad guys out.

Getting started

For this article, you’ll need:

  • a free-tier AWS account
  • an ssh client
  • a web browser

Spin up an EC2 instance

Login to the AWS website, pick your preferred datacenter (usually the closest to you or your users), and open the EC2 console.

On the sidebar, under Instances, click on Launch Instance:

EC2 provides a wide variety of images. In the Linux arena, the free tier includes the most popular distributions. Under the Community AMIs tab, we’ll select a Fedora image. On the right, select the Fedora checkbox, and under the Search box, write “cloud-base gp2 x86,” and select the most current Fedora version available (in this case it’s version 29).

Next, we’ll be asked to select the machine type. There is only one free option for the free-tier accounts: t2.micro, so we’ll choose that. Fortunately, it’s more than enough to support phpMyAdmin and a small database. If you have higher requirements, pick a better (paid) machine:

If you’re in a hurry, you can just click on Review and launch. But by continuing with the wizard at this point, we will be able to save us some work that would have to be done later.

Next, we can configure the instance and network type. There’s no need to change the defaults:

Post that, we can set how much storage to assign:

For a basic phpMyAdmin installation, 6 GB is more than enough. However, if we were to install additional software or a bigger database, we would need to add more disk space:

We’ll fly past the Add Tags section since we won’t be using tags:

On the security group tab, we’ll add inbound firewall rules. We want our instance to be reachable on ports 22 (ssh), 80 (http) and 443 (https).

Click on the Add Rule button and select HTTP from the dropdown menu. Hit Add Rule again and select HTTPS. If you want to limit access from certain IPs, you can type the IP ranges in Source. Otherwise, you can leave it at 0.0.0.0/0 to allow all connections. Take note of the Security group name as we’ll need that later.

Finally, we are presented with a summary page for our new instance. After checking that everything is in order, we can Launch it.

Only one more step is left. We need a method of accessing the instance once it is running. For this, AWS presents us the option to generate an ssh key. If you have already generated one, you can choose that from the dropdown menu. Otherwise, you can just generate a new pair and download it.

Save the file in a secure place and don’t lose it. The file can only be downloaded once.

Connecting to the instance

Head back to the EC2 console. Under Instances, you’ll see the new instance starting up. After a few minutes, it should read as Running:

Select the checkbox for the instance and hit the Connect button. You’ll be presented with an overlay with connection instructions. Take note of the server IP address and full name (FQDN). In this case, the full name we got was “ec2-18-225-10-198.us-east-2.compute.amazonaws.com”.

Following the instructions, we open a terminal and execute the steps as described:

# attention: the correct use to login is "fedora" (not root) 
$ chmod 400 my_ec2_key.pem 
$ ssh -i "my_ec2_key.pem" [email protected] 
The authenticity of host 'ec2-18-225-10-198.us-east-2.compute.amazonaws.com (18.225.10.198)' can't be established. 
ECDSA key fingerprint is SHA256:aB5Nfbi6pYHop9XiiNXm05On0eeszQ4DjyLBIk/H+G0. 
Are you sure you want to continue connecting (yes/no)? yes

Welcome to your new instance. The fedora user has sudo permissions, we’ll use it to setup our system.

The first thing to do on this new instance is to update it. It’s the perfect opportunity since we don’t have any services running yet.

# update and reboot 
$ sudo dnf update -y 
$ sudo dnf install nano -y 
$ sudo systemctl reboot

After rebooting the instance, wait for a few seconds and log in again.

Installing prerequisites

We’ll start by installing the rest of the components of a LAMP stack: the database, a web server, and php.

Install & secure MariaDB

Install MariaDB and start the service:

$ sudo dnf install mariadb-server.x86_64 mariadb -y 
$ sudo systemctl enable --now mariadb

Right now the database is unsecured; the root user doesn’t have a password. Use a strong password for root and create an additional admin user for phpMyAdmin:

$ mysql -u root 
Welcome to the MariaDB monitor. Commands end with ; or \g. 
Your MariaDB connection id is 398 
Server version: 10.3.11-MariaDB MariaDB Server 

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others. 

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. 

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'SuperSecretPassword'; 
Query OK, 0 rows affected (0.000 sec) 

MariaDB [(none)]> ALTER USER 'root'@'127.0.0.1' IDENTIFIED BY 'SuperSecretPassword'; 
Query OK, 0 rows affected (0.000 sec) 

MariaDB [(none)]> ALTER USER 'root'@'::1' IDENTIFIED BY 'SuperSecretPassword'; 
Query OK, 0 rows affected (0.000 sec) 

MariaDB [(none)]> CREATE USER 'phpmyadmin'@'localhost' IDENTIFIED BY 'SuperPassword1'; 
Query OK, 0 rows affected (0.000 sec) 

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'localhost' WITH GRANT OPTION; 
Query OK, 0 rows affected (0.000 sec)

Choose good passwords and save them at a secure place; we’ll need them later.

Install a webserver

We’ll need a web server to host our application. To keep things simple, we’ll only cover two of the most popular options: Apache and nginx.

You’ll have to decide which one to install. You need only one webserver.

To install nginx:

$ sudo dnf install nginx -y 
$ sudo systemctl enable --now nginx

Altenatively, you can install Apache using:

$ sudo dnf install httpd -y 
$ sudo systemctl enable --now httpd

We’ll circle back to the webserver configuration soon. For now, it’s good enough that it is running.

Open a browser and navigate to the server address. If everything is ok, we should see a test page:

Installing phpMyAdmin

Since phpMyAdmin is included in Fedora’s repositories, we can install it directly and it will automatically pull php and all other required packages.

$ sudo dnf install phpmyadmin -y

The following steps depend on your earlier choice for webserver.

Running phpMyAdmin with nginx

We’ll only need to link the phpMyAdmin directory to the nginx root.

$ sudo ln -s /usr/share/phpMyAdmin /usr/share/nginx/html/phpmyadmin 
$ sudo systemctl restart nginx

Restart nginx:

$ sudo systemctl restart nginx

Running phpMyAdmin in Apache

phpMyAdmin package ships with a configuration file for Apache. However, you won’t be able to access phpMyAdmin from your browser yet. The site will only allow local connections by default. Since we are running an instance on the cloud, this isn’t of much use to us.

To allow connections from the outside, edit the phpMyAdmin config file:

$ sudo nano /etc/httpd/conf.d/phpMyAdmin.conf

Look for all the blocks with Require ip 127.0.0.1. On each of those blocks, add a line that reads: Require all granted.

Next, find all lines with Deny from all. Replace those lines with Allow from all

The end result should be:

<Directory /usr/share/phpMyAdmin/setup/> 
<IfModule mod_authz_core.c> 
# Apache 2.4 
<RequireAny> 
# <- Add next line 
Require all granted 
# Require ip 127.0.0.1 
# Require ip ::1 
</RequireAny> 
</IfModule> 
<IfModule !mod_authz_core.c> 
# Apache 2.2 
Order Deny,Allow 
# Deny from All <- replace this line 
Allow from all 
....

You will need to add each line twice.

Restart Apache:

$ sudo systemctl restart httpd

Test phpMyAdmin on your browser

You should be able to access phpMyAdmin from your browser on http://<YOUR_SERVER_ADDRESS>/phpmyadmin

We now have a basic yet fully working phpMyAdmin installation. You may be tempted to start using it now but don’t. The current setup is quite unsafe as it is. There’s still work ahead before we can claim our system is secured.

Harden your setup

Our phpMyAdmin is working. However, it has some serious security issues:

  • No encryption: communication between the browser and phpMyAdmin is unsecured. Everything (that includes passwords) is sent in clear text.
  • Password authentication: there is only one password between an attacker and the database.

Installing a Letsencrypt certificate

If you already own a domain, Letsencrypt can provide free certificates for it. certbot makes the process of acquiring one quick and easy.

Prerequisites

  • A domain: Unfortunately, Letsencrypt doesn’t support AWS domains (amazonaws.com). You’ll need to get your own domain name from a registrar.
  • DNS Records: You’ll need to be able to configure your domain type A records.

To set up your DNS records:

  1. Go to your registrar or DNS provider website.
  2. Create an A type record: for example if your domain is example.com, add A record such as www (www.example.com)
  3. Point the new record to your EC2 public IP

The rest of the steps depend on the webserver you’re running.

Create certificate for nginx

Edit the nginx configuration:

$ sudo nano /etc/nginx/nginx.conf

Inside the server block, locate the line that reads server_name _;. Write your domain address:

server { 
#server_name _; # <- replace the _ with your site address 
server_name www.example.com;

Restart nginx:

$ sudo systemctl restart nginx

Install certbot for nginx:

$ sudo dnf install certbot certbot-nginx -y

Run certbot to create the certificate. It’ll pick the correct domain name automatically from the nginx config. Follow these instructions:

$ sudo certbot --nginx 

Saving debug log to /var/log/letsencrypt/letsencrypt.log 
Plugins selected: Authenticator nginx, Installer nginx 

Which names would you like to activate HTTPS for? 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
1: www.example.com 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
Select the appropriate numbers separated by commas and/or spaces, or leave input 
blank to select all options shown (Enter 'c' to cancel): 
Deploying Certificate to VirtualHost /etc/nginx/nginx.conf 

Please choose whether or not to redirect HTTP traffic to HTTPS, removing HTTP access. 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
1: No redirect - Make no further changes to the webserver configuration. 
2: Redirect - Make all requests redirect to secure HTTPS access. Choose this for 
new sites, or if you're confident your site works on HTTPS. You can undo this 
change by editing your web server's configuration. 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
Select the appropriate number [1-2] then [enter] (press 'c' to cancel): 2 
Redirecting all traffic on port 80 to ssl in /etc/nginx/nginx.conf 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
Congratulations! You have successfully enabled https://www.example.com 

You should test your configuration at: 
https://www.ssllabs.com/ssltest/analyze.html?d=admin.tomfern.com 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

IMPORTANT NOTES: 
- Congratulations! Your certificate and chain have been saved at: 
/etc/letsencrypt/live/admin.tomfern.com/fullchain.pem 
Your key file has been saved at: 
/etc/letsencrypt/live/admin.tomfern.com/privkey.pem 
Your cert will expire on 2019-05-23. To obtain a new or tweaked 
version of this certificate in the future, simply run certbot again 
with the "certonly" option. To non-interactively renew *all* of 
your certificates, run "certbot renew" 
- If you like Certbot, please consider supporting our work by: 

Donating to ISRG / Let's Encrypt: https://letsencrypt.org/donate 
Donating to EFF: https://eff.org/donate-le

Letsencrypt Certificate for Apache

Installing a Letsencrypt certificate for Apache is also very easy:

First, create a config file for Apache:

sudo nano /etc/httpd/conf.d/00-mydomain.conf

Add the following lines to the empty file. Make sure you set your own domain name in

ServerName and ServerAlias:

<VirtualHost *:80> 
ServerName www.example.com 
ServerAlias *.example.com 
</VirtualHost>

Restart Apache:

sudo systemctl restart httpd

Install certbot for Apache:

sudo dnf install certbot certbot-apache -y

Certbot will automatically get the domain name from Apache:

$ sudo certbot --apache 
Saving debug log to /var/log/letsencrypt/letsencrypt.log 
Plugins selected: Authenticator apache, Installer apache 

Which names would you like to activate HTTPS for? 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
1: www.example.com 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
Select the appropriate numbers separated by commas and/or spaces, or leave input 
blank to select all options shown (Enter 'c' to cancel): 
Obtaining a new certificate 
Performing the following challenges: 
http-01 challenge for www.example.com 
Waiting for verification... 
Cleaning up challenges 
Created an SSL vhost at /etc/httpd/conf.d/00-mydomain-le-ssl.conf 
Deploying Certificate to VirtualHost /etc/httpd/conf.d/00-mydomain-le-ssl.conf 

Please choose whether or not to redirect HTTP traffic to HTTPS, removing HTTP access. 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
1: No redirect - Make no further changes to the webserver configuration. 
2: Redirect - Make all requests redirect to secure HTTPS access. Choose this for 
new sites, or if you're confident your site works on HTTPS. You can undo this 
change by editing your web server's configuration. 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
Select the appropriate number [1-2] then [enter] (press 'c' to cancel): 2 
Redirecting vhost in /etc/httpd/conf.d/00-mydomain.conf to ssl vhost in /etc/httpd/conf.d/00-mydomain-le-ssl.conf 

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 
Congratulations! You have successfully enabled https://www.example.com 

You should test your configuration at: 
https://www.ssllabs.com/ssltest/analyze.html?d=www.example.com 
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 

IMPORTANT NOTES: 
- Congratulations! Your certificate and chain have been saved at: 
/etc/letsencrypt/live/www.example.com/fullchain.pem 
Your key file has been saved at: 
/etc/letsencrypt/live/www.example.com/privkey.pem 
Your cert will expire on 2019-05-23. To obtain a new or tweaked 
version of this certificate in the future, simply run certbot again 
with the "certonly" option. To non-interactively renew *all* of 
your certificates, run "certbot renew" 
- If you like Certbot, please consider supporting our work by: 

Donating to ISRG / Let's Encrypt: https://letsencrypt.org/donate 
Donating to EFF: https://eff.org/donate-le

Browse your site in HTTPS

Once done, test your new certificate. Navigate to the HTTPS version of your site: https://www.example.com/phpmyadmin

Verify the validity of the site certificate.

Generating a self-signed certificate

If you don’t have your own domain, or are not interested in using it on this website, you may use a self-signed certificate. You’ll get a security warning from your browser, but still this is far better than not using any encryption at all.

Install openssl:

$ sudo dnf install openssl -y

Generate an SSL key. We’ll use a 2048 bit key:

$ openssl genrsa -out localhost.key 2048

To create a certificate, the tool will ask for some information. The most important thing to get right is the Common Name. It must match your server address (FQDN) exactly, e.g.: ec2-18-225-10-198.us-east-2.compute.amazonaws.com

$ openssl req -new -key localhost.key -out localhost.csr -sha512 

You are about to be asked to enter information that will be incorporated 
into your certificate request. 
What you are about to enter is what is called a Distinguished Name or a DN. 
There are quite a few fields but you can leave some blank 
For some fields there will be a default value, 
If you enter '.', the field will be left blank. 
----- 
Country Name (2 letter code) [AU]:US 
State or Province Name (full name) [Some-State]:California 
Locality Name (eg, city) []:Los Angeles 
Organization Name (eg, company) [Internet Widgits Pty Ltd]:SuperMegaStore 
Organizational Unit Name (eg, section) []:Financial Services 
Common Name (e.g. server FQDN or YOUR name) []:

ec2-18-225-10-198.us-east-2.compute.amazonaws.com

 
Email Address []:[email protected] 

Please enter the following 'extra' attributes 
to be sent with your certificate request 
A challenge password []: 
An optional company name []:

Sign the certificate. It will be valid for the next 1000 days:

$ openssl x509 -req -days 1000 -in localhost.csr -signkey localhost.key -out localhost.crt -sha512

Install certificate on nginx

Copy the keys into the nginx config directory:

$ sudo mkdir -p /etc/nginx/ssl 
$ sudo cp localhost.crt /etc/nginx/ssl 
$ sudo cp localhost.key /etc/nginx/ssl 

$ sudo chown root:root /etc/nginx/ssl/localhost.key 
$ sudo chown root:root /etc/nginx/ssl/localhost.crt 

$ sudo chmod 0600 /etc/nginx/ssl/localhost.key 
$ sudo chmod 0600 /etc/nginx/ssl/localhost.crt 

$ sudo restorecon -RvF /etc/nginx/ssl

Edit nginx config and locate the server { … } block. Add the following lines inside the block:

listen 443 ssl; 
ssl_certificate /etc/nginx/ssl/localhost.crt; 
ssl_certificate_key /etc/nginx/ssl/localhost.key;

Restart nginx:

$ sudo systemctl restart nginx

Install on Apache

Install the Apache SSL module:

$ sudo dnf install mod_ssl -y

Copy the key and certificate files to the Apache directories:

$ sudo cp localhost.crt /etc/pki/tls/certs 
$ sudo cp localhost.key /etc/pki/tls/private 

$ sudo chown root:root /etc/pki/tls/private/localhost.key 
$ sudo chown root:root /etc/pki/tls/certs/localhost.crt 

$ sudo chmod 0600 /etc/pki/tls/private/localhost.key 
$ sudo chmod 0600 /etc/pki/tls/certs/localhost.crt 

$ sudo restorecon -RvF /etc/pki

Restart Apache service:

$ sudo systemctl restart httpd

Test HTTPS

Direct your browser to the HTTPS version of the website, e.g.: https://ec2-18-225-10-198.us-east-2.compute.amazonaws.com/phpmyadmin

You’ll get a security warning on the browser:

Once accepted, you should get the phpMyAdmin login page.

Block HTTP access

One you’ve tested that HTTPS is working, you should block HTTP connections to your server. Head to the EC2 console, on the sidebar and click on Security Groups under the Network & Security section:

From the security group list, select the entry that corresponds to this instance:

Click on the Inbound and hit the Edit button:

Remove the http entry and save:

Enable two-factor authentication

We can strengthen the phpMyAdmin with two-factor authentication. This enhances the security of the login process so even if an attacker knows the password, they won’t be able to access the system.

In order to use two-factor authentication (2FA), you’ll need to install an authenticator app on your phone such as Google Authenticator, Microsoft Authenticator, etc.

To enable 2FA on phpMyAdmin, go to Settings -> Warnings. Disable the option “Missing phpMyAdmin configuration storage tables” and hit Apply:

Go to Databases and create a new database called phpmyadmin:

Select the new database from the sidebar. Go to Operations and click on the “Find Out Why” link:

Click on the “Create” link. It will initialize the phpmyadmin database with special tables.

Go back to Settings and click on Two Factor Authentication. Choose Authentication application (2FA):

Open your authenticator app on the phone and set it up by taking a picture of the QR code or typing the secret key. Write the numbers shown on the phone to continue:

Try logging out and then logging in. After typing the username and password, you will be asked to type the security token on your phone.

Common issues

Can’t create Letsencrypt certificate

If you get an error similar to:

Failed authorization procedure. www.example.com (http-01): urn:ietf:params:acme:error:connection 
The server could not connect to the client to verify the domain :: dns :: DNS problem: NXDOMAIN

It means that your domain A records are not correctly set on the DNS. If you have recently updated them, wait for a few minutes and try again. Some DNS providers can take up to a few hours to make the changes available.

The site certificate doesn’t work

Make sure that the permissions and security context for the certificate files are correct.

# for nginx 
$ sudo chown root:root /etc/nginx/ssl/localhost.key 
$ sudo chown root:root /etc/nginx/ssl/localhost.crt 

$ sudo chmod 0600 /etc/nginx/ssl/localhost.key 
$ sudo chmod 0600 /etc/nginx/ssl/localhost.crt 

$ sudo restorecon -RvF /etc/nginx/ssl 

# for Apache 
$ chown root:root /etc/pki/tls/private/mycert.key 
$ chown root:root /etc/pki/tls/certs/mycert.crt 

$ chmod 0600 /etc/pki/tls/private/mycert.key 
$ chmod 0600 /etc/pki/tls/certs/mycert.crt 

$ restorecon -RvF /etc/pki

Review your Apache or nginx configuration files. Both of them should have an SSL certificate entry that should point to the correct key and certificate files.

Can’t find correct Fedora image

AWS provides a lot of Linux images to use. Finding the best one isn’t always so simple. Fortunately, the Fedora project has a site that links to the latest images that you can use directly on AWS: https://alt.fedoraproject.org/cloud/

Scroll down to Fedora cloud images for Amazon Public Cloud and pick GP2 HVM AMIs

You’ll get a list of the available datacenters. Choose the best one for you:

Database connection fails

If phpMyAdmin fails to connect to the database, edit the phpMyAdmin config:

$ sudo nano /etc/phpMyAdmin/config.inc.php

You’ll find that the configuration is written in PHP. Locate the lines that deal with the server address:

$cfg['Servers'][$i]['host'] = 'localhost'; // MySQL hostname or IP address 
$cfg['Servers'][$i]['port'] = ''; // MySQL port - leave blank for default port 
$cfg['Servers'][$i]['socket'] = ''; // Path to the socket - leave blank for default socket

Check that host, port, and socket are correct.

Restart Apache after changing the config:

$ sudo systemctl restart httpd

Access denied to the database

Open a connection to your database:

MariaDB [(none)]> select User,host from mysql.user; 
+-------------+---------------------------------------------+ 
| User | host | 
+-------------+---------------------------------------------+ 
| root | 127.0.0.1 | 
| root | ::1 | 
| root | ip-172-31-38-102.us-east-2.compute.internal | 
| phpmyadmin | localhost | 
| root | localhost | 
+-------------+---------------------------------------------+ 
5 rows in set (0.000 sec)

This is the list of users with access to the database. Not only must the user and password be correct, but also the incoming connection must be initiated from the listed host.

If phpMyAdmin and the database are located on different servers, you’ll have to create a user on the database that is allowed to connect from the remote IP.

For instance, if your phpMyAdmin machine has the IP address of 1.2.3.4, the user must be created as follows:

MariaDB [(none)]> CREATE USER 'phpmyadmin'@'1.2.3.4' IDENTIFIED BY 'SuperPassword1'; 
Query OK, 0 rows affected (0.000 sec) 

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'phpmyadmin'@'1.2.3.4' WITH GRANT OPTION; 
Query OK, 0 rows affected (0.000 sec)

Remove two-factor authentication

If you ever lose your phone, you won’t be able to access the website. The easiest way to remove two-factor setup is to delete the phpMyAdmin schema from the database.

Login to the server using ssh, connect to your mysql database, and drop the schema:

MariaDB [(none)]> drop schema phpmyadmin;

References

Conclusion

While it does take some effort, through this method, you can successfully setup a secure phpMyAdmin website working. You’ll also be able to manage your MySQL and MariaDB databases with ease.

If you have any doubts pertaining to the process mentioned above, let us know in the comments.