How to Install and Secure phpMyAdmin on Ubuntu 22.04 on GCP

Video tutorial of How to Install and Secure phpMyAdmin on Ubuntu 22.04

Introduction

While many users need the functionality of a database management system like MySQL, they may not feel comfortable interacting with the system solely from the MySQL prompt.

phpMyAdmin was created so that users can interact with MySQL through a web interface. In this guide, I will show you how to install and secure phpMyAdmin so that you can safely use it to manage your databases on an Ubuntu 22.04 system.

Prerequisite

Complete tutorial: How to setup a compute engine VM on GCP and install LAMP stack on the server.

Install needed packages and phpMyAdmin

SSH into the server
Update packages sudo apt-get update
Install necessary dependencies
sudo apt-get install phpmyadmin

sudo apt-get update
sudo apt-get install php-bz2 php-gd php-curl php-mbstring php-zip php-json
sudo apt-get install phpmyadmin

php-mbstring: A module for managing non-ASCII strings and convert strings to different encodings
php-zip: This extension supports uploading .zip files to phpMyAdmin
php-gd: Enables support for the GD Graphics Library
php-json: Provides PHP with support for JSON serialization
php-curl: Allows PHP to interact with different kinds of servers using different protocols

During the installation, configure phpMyAdmin as follows:

Use the spacebar to select apache2 and the tab key to move the cursor.
Select Yes to use dbconfig-common for database setup.
Enter a password for the phpMyAdmin application, and make a note of the password.

After installation, complete the configuration:

5. Uncomment the following line in your server’s php.ini file by removing the leading ; character:

;extension=mysqli

sudo nano /etc/php/8.1/apache2/php.ini
;extension=mysqli

6. Include the phpMyAdmin configuration in your apache2.conf file by adding the following line:

Include /etc/phpmyadmin/apache.conf

In apache2.conf

sudo nano /etc/apache2/apache2.conf

7. Explicitly enable mbstring PHP extension by typing:

sudo phpenmod mbstring

8. Restart Apache:

sudo systemctl restart apache2

Test phpMyAdmin

Browse to phpMyAdmin:

http://[YOUR_EXTERNAL_IP_ADDRESS]/phpmyadmin

You should see the phpMyAdmin login page.

Configuring Password Access for a Dedicated MySQL User

Alternatively, some may find that it better suits their workflow to connect to phpMyAdmin with a dedicated user. To do this, open up the MySQL shell once again:

sudo mysql

If you have password authentication enabled for your root user, as described in the previous section, you will need to run the following command and enter your password when prompted in order to connect:

mysql -u root -p

From there, create a new user and give it a strong password:

CREATE USER ‘vincent’@’localhost’ IDENTIFIED BY ‘User@789dynamic!’;

Then, grant your new user appropriate privileges. For example, you could grant the user privileges to all tables within the database, as well as the power to add, change, and remove user privileges, with this command:

GRANT ALL PRIVILEGES ON *.* TO ‘vincent’@’localhost’ WITH GRANT OPTION;

Following that, exit the MySQL shell:

exit

Secure phpMyAdmin

To prevent unauthorized access to your instance, take steps to secure your phpMyAdmin installation, such as by serving phpMyAdmin only over HTTPS or using an authentication proxy.

Step 3 — Securing Your phpMyAdmin Instance

Because of its ubiquity, phpMyAdmin is a popular target for attackers, and you should take extra care to prevent unauthorized access. One way of doing this is to place a gateway in front of the entire application by using Apache’s built-in .htaccess authentication and authorization functionalities.

To do this, you must first enable the use of .htaccess file overrides by editing your phpMyAdmin installation’s Apache configuration file.

Use your preferred text editor to edit the phpmyadmin.conf file that has been placed in your Apache configuration directory. Here, we’ll use nano:

sudo nano /etc/phpmyadmin/apache.conf

Add an AllowOverride All directive within the <Directory /usr/share/phpmyadmin> section of the configuration file, like this:

<Directory /usr/share/phpmyadmin>

    Options SymLinksIfOwnerMatch

    DirectoryIndex index.php

    AllowOverride All

    . . .

When you have added this line, save and close the file. If you used nano to edit the file, do so by pressing CTRL + X, Y, and then ENTER.

To implement the changes you made, restart Apache:

sudo systemctl restart apache2

Now that you have enabled the use of .htaccess files for your application, you need to create one to actually implement some security.

In order for this to be successful, the file must be created within the application directory. You can create the necessary file and open it in your text editor with root privileges by typing:

sudo nano /usr/share/phpmyadmin/.htaccess

Within this file, enter the following information:

AuthType Basic

AuthName "Restricted Files"

AuthUserFile /etc/phpmyadmin/.htpasswd

Require valid-user

Here is what each of these lines mean:

AuthType Basic: This line specifies the authentication type that you are implementing. This type will implement password authentication using a password file.

AuthName: This sets the message for the authentication dialog box. You should keep this generic so that unauthorized users won’t gain any information about what is being protected.

AuthUserFile: This sets the location of the password file that will be used for authentication. This should be outside of the directories that are being served. We will create this file shortly.

Require valid-user: This specifies that only authenticated users should be given access to this resource. This is what actually stops unauthorized users from entering.

When you are finished, save and close the file.

The location that you selected for your password file was /etc/phpmyadmin/.htpasswd. You can now create this file and pass it an initial user with the htpasswd utility:

sudo htpasswd -c /etc/phpmyadmin/.htpasswd USERNAME

You will be prompted to select and confirm a password for the user you are creating. Afterwards, the file is created with the hashed password that you entered.

Then restart Apache to put .htaccess authentication into effect:

sudo systemctl restart apache2

Now, when you access your phpMyAdmin subdirectory, you will be prompted for the additional account name and password that you just configured:

https://domain_name_or_IP/phpmyadmin

phpMyAdmin apache password

After entering the Apache authentication, you’ll be taken to the regular phpMyAdmin authentication page to enter your MySQL credentials. By adding an extra set of non-MySQL credentials, you’re providing your database with an additional layer of security. This is desirable, since phpMyAdmin has been vulnerable to security threats in the past.

Conclusion

You should now have phpMyAdmin configured and ready to use on your Ubuntu 22.04 server. Using this interface, you can create databases, users, and tables, as well as perform the usual operations like deleting and modifying structures and data.

References