How To Install phpMyAdmin From Source on Debian 10

Introduction

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

phpMyAdmin was created so that users can interact with MariaDB through a web interface. In this guide, we’ll discuss how to install and secure phpMyAdmin so that you can safely use it to manage your databases on a Debian 10 system.

Prerequisites

Before you get started with this guide, you’ll need the following:

• Access to a Debian 10 server. This server should have a non-root user with sudo privileges and a firewall configured with ufw. To set this up, follow our Initial Server Setup Guide for Debian 10.
• A LAMP (Linux, Apache, MariaDB, and PHP) stack installed on your Debian 10 server. Follow our guide on installing a LAMP stack on Debian 10 to set this up.

Finally, there are important security considerations when using software like phpMyAdmin, since it:

• Communicates directly with your MariaDB installation
• Handles authentication using MariaDB credentials
• Executes and returns results for arbitrary SQL queries

For these reasons, and because it is a widely-deployed PHP application which is frequently targeted for attack, you should never run phpMyAdmin on remote systems over a plain HTTP connection.

If you do not have an existing domain configured with an SSL/TLS certificate, you can follow this guide on securing Apache with Let’s Encrypt on Debian 10 to set one up. This will require you to register a domain name, create DNS records for your server, and set up an Apache Virtual Host.

Once you are finished with these steps, you’re ready to get started with this guide.

Step 1 — Installing phpMyAdmin and Recommended Packages

Before installing and configuring phpMyAdmin, the official documentation recommends that you install a few PHP extensions onto your server to enable certain functionalities and improve performance.

If you followed the prerequisite LAMP stack tutorial, several of these modules will have been installed along with the php package. However, it’s recommended that you also install these packages:

• php-mbstring: a PHP extension used to manage non-ASCII strings and convert strings to different encodings
• php-zip: a PHP module that supports uploading .zip files to phpMyAdmin
• php-gd: another PHP module, this one enables support for the GD Graphics Library

First, update your server’s package index if you’ve not done so recently:

sudo apt update

Then use apt to pull down the files and install them on your system:

sudo apt install php-mbstring php-zip php-gd

Next, we can install phpMyAdmin. As of this writing, phpMyAdmin is not available from the default Debian repositories, so you will need to download the source code to your server from the phpMyAdmin site.

In order to do that, navigate to the phpMyAdmin Downloads page, scroll down to the table with download links for the latest stable release, and copy the download link ending in tar.gz. This link points to an archive file known as a tarball that, when extracted, will create a number of files on your system. At the time of this writing, the latest release is version 4.9.7.

Replace the link in the following wget command with the download link you just copied, then press ENTER. This will run the command and download the tarball to your server:

wget https://files.phpmyadmin.net/phpMyAdmin/4.9.7/phpMyAdmin-4.9.7-all-languages.tar.gz

Then extract the tarball:

tar xvf phpMyAdmin-4.9.7-all-languages.tar.gz

This will create a number of new files and directories on your server under a parent directory named phpMyAdmin-4.9.7-all-languages.

Then run the following command. This will move the phpMyAdmin-4.9.7-all-languages directory and all its subdirectories to the /usr/share/ directory, the location where phpMyAdmin expects to find its configuration files by default. It will also rename the directory in place to just phpmyadmin:

sudo mv phpMyAdmin-4.9.7-all-languages/ /usr/share/phpmyadmin

With that, you’ve installed phpMyAdmin, but there are a number of configuration changes you must make in order to be able to access phpMyAdmin through a web browser.

Step 2 — Configuring phpMyAdmin Manually

When installing phpMyAdmin with a package manager, as one might in an Ubuntu environment, phpMyAdmin defaults to a “Zero Configuration” mode which performs several actions automatically to set up the program. Because we installed it from source in this guide, we will need to perform those steps manually.

To begin, make a new directory where phpMyAdmin will store its temporary files:

sudo mkdir -p /var/lib/phpmyadmin/tmp

Set www-data — the Linux user profile that web servers like Apache use by default for normal operations in Ubuntu and Debian systems — as the owner of this directory:

sudo chown -R www-data:www-data /var/lib/phpmyadmin

The files you extracted previously include a sample configuration file that you can use as your base configuration file. Make a copy of this file, keeping it in the /usr/share/phpmyadmin directory, and rename it config.inc.php:

sudo cp /usr/share/phpmyadmin/config.sample.inc.php /usr/share/phpmyadmin/config.inc.php

Open this file using your preferred text editor. Here, we’ll use nano:

sudo nano /usr/share/phpmyadmin/config.inc.php

phpMyAdmin uses the cookie authentication method by default, which allows you to log in to phpMyAdmin as any valid MariaDB user with the help of cookies. In this method, the MariaDB user password is stored and encrypted with the Advanced Encryption Standard (AES) algorithm in a temporary cookie.

Historically, phpMyAdmin instead used the Blowfish cipher for this purpose, and this is still reflected in its configuration file. Scroll down to the line that begins with $cfg['blowfish_secret']. It will look like this:

. . .
$cfg['blowfish_secret'] = ''; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
. . .

In between the single quotes, enter a string of 32 random characters. This isn’t a passphrase you need to remember, it will just be used internally by the AES algorithm:

. . .
$cfg['blowfish_secret'] = 'STRINGOFTHIRTYTWORANDOMCHARACTERS'; /* YOU MUST FILL IN THIS FOR COOKIE AUTH! */
. . .

sudo apt install pwgen

pwgen -s 32 1

Next, scroll down to the comment reading /* User used to manipulate with storage */. This section includes some directives that define a MariaDB database user named pma which performs certain administrative tasks within phpMyAdmin. According to the official documentation, this special user account isn’t necessary in cases where only one user will access phpMyAdmin, but it is recommended in multi-user scenarios.

Uncomment the controluser and controlpass directives by removing the preceding slashes. Then update the controlpass directive to point to a secure password of your choosing. If you don’t do this, the default password will remain in place and unknown users could easily gain access to your database through the phpMyAdmin interface.

After making these changes, this section of the file will look like this:

. . .
/* User used to manipulate with storage */
// $cfg['Servers'][$i]['controlhost'] = '';
// $cfg['Servers'][$i]['controlport'] = '';
$cfg['Servers'][$i]['controluser'] = 'pma';
$cfg['Servers'][$i]['controlpass'] = 'password';
. . .

Below this section, you’ll find another section preceded by a comment reading /* Storage database and tables */. This section includes a number of directives that define the phpMyAdmin configuration storage, a database and several tables used by the administrative pma database user. These tables enable a number of features in phpMyAdmin, including Bookmarks, comments, PDF generation, and more.

Uncomment each line in this section by removing the slashes at the beginning of each line so it looks like this:

. . .
/* Storage database and tables */
$cfg['Servers'][$i]['pmadb'] = 'phpmyadmin';
$cfg['Servers'][$i]['bookmarktable'] = 'pma__bookmark';
$cfg['Servers'][$i]['relation'] = 'pma__relation';
$cfg['Servers'][$i]['table_info'] = 'pma__table_info';
$cfg['Servers'][$i]['table_coords'] = 'pma__table_coords';
$cfg['Servers'][$i]['pdf_pages'] = 'pma__pdf_pages';
$cfg['Servers'][$i]['column_info'] = 'pma__column_info';
$cfg['Servers'][$i]['history'] = 'pma__history';
$cfg['Servers'][$i]['table_uiprefs'] = 'pma__table_uiprefs';
$cfg['Servers'][$i]['tracking'] = 'pma__tracking';
$cfg['Servers'][$i]['userconfig'] = 'pma__userconfig';
$cfg['Servers'][$i]['recent'] = 'pma__recent';
$cfg['Servers'][$i]['favorite'] = 'pma__favorite';
$cfg['Servers'][$i]['users'] = 'pma__users';
$cfg['Servers'][$i]['usergroups'] = 'pma__usergroups';
$cfg['Servers'][$i]['navigationhiding'] = 'pma__navigationhiding';
$cfg['Servers'][$i]['savedsearches'] = 'pma__savedsearches';
$cfg['Servers'][$i]['central_columns'] = 'pma__central_columns';
$cfg['Servers'][$i]['designer_settings'] = 'pma__designer_settings';
$cfg['Servers'][$i]['export_templates'] = 'pma__export_templates';
. . .

These tables don’t yet exist, but we will create them shortly.

Lastly, scroll down to the bottom of the file and add the following line. This will configure phpMyAdmin to use the /var/lib/phpmyadmin/tmp directory you created earlier as its temporary directory. phpMyAdmin will use this temporary directory as a templates cache which allows for faster page loading:

. . .
$cfg['TempDir'] = '/var/lib/phpmyadmin/tmp';

Save and close the file after adding this line. If you used nano, you can do so by pressing CTRL + X, Y, then ENTER.

Next, you’ll need to create the phpMyAdmin storage database and tables. When you installed phpMyAdmin in the previous step, it came with a file named create_tables.sql. This SQL file contains all the commands needed to create the configuration storage database and tables phpMyAdmin needs to function correctly.

Run the following command to use the create_tables.sql file to create the configuration storage database and tables:

sudo mariadb < /usr/share/phpmyadmin/sql/create_tables.sql

Following that, you’ll need to create the administrative pma user. Open up the MariaDB prompt:

sudo mariadb

From the prompt, run the following command to create the pma user and grant it the appropriate permissions. Be sure to change password to align with the password you defined in the config.inc.php file:

GRANT SELECT, INSERT, UPDATE, DELETE ON phpmyadmin.* TO 'pma'@'localhost' IDENTIFIED BY 'password';

If haven’t created one already, you should also create a regular MariaDB user for the purpose of managing databases through phpMyAdmin, as it’s recommended that you log in using another account than the pma user. You could create a user that has privileges to all tables within the database, as well as the power to add, change, and remove user privileges, with this command. Whatever privileges you assign to this user, be sure to give it a strong password as well:

GRANT ALL PRIVILEGES ON *.* TO 'sammy'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;

Following that, exit the MariaDB shell:

exit

phpMyAdmin is now fully installed and configured on your server. However, your Apache server does not yet know how to serve the application. To resolve this, we will create an Apache configuration file for it.

Step 3 — Configuring Apache to Serve phpMyAdmin

When installing phpMyAdmin from the default repositories, the installation process creates an Apache configuration file automatically and places it in the /etc/apache2/conf-enabled/ directory. Because we installed phpMyAdmin from source, however, we will need to create and enable this file manually.

Create a file named phpmyadmin.conf in the /etc/apache2/conf-available/ directory:

sudo nano /etc/apache2/conf-available/phpmyadmin.conf

Then add the following content to the file

# phpMyAdmin default Apache configuration

Alias /phpmyadmin /usr/share/phpmyadmin

<Directory /usr/share/phpmyadmin>
    Options SymLinksIfOwnerMatch
    DirectoryIndex index.php

    <IfModule mod_php5.c>
        <IfModule mod_mime.c>
            AddType application/x-httpd-php .php
        </IfModule>
        <FilesMatch ".+\.php$">
            SetHandler application/x-httpd-php
        </FilesMatch>

        php_value include_path .
        php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
        php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/php/php-php-gettext/:/usr/share/javascript/:/usr/share/php/tcpdf/:/usr/share/doc/phpmyadmin/:/usr/share/php/phpseclib/
        php_admin_value mbstring.func_overload 0
    </IfModule>
    <IfModule mod_php.c>
        <IfModule mod_mime.c>
            AddType application/x-httpd-php .php
        </IfModule>
        <FilesMatch ".+\.php$">
            SetHandler application/x-httpd-php
        </FilesMatch>

        php_value include_path .
        php_admin_value upload_tmp_dir /var/lib/phpmyadmin/tmp
        php_admin_value open_basedir /usr/share/phpmyadmin/:/etc/phpmyadmin/:/var/lib/phpmyadmin/:/usr/share/php/php-gettext/:/usr/share/php/php-php-gettext/:/usr/share/javascript/:/usr/share/php/tcpdf/:/usr/share/doc/phpmyadmin/:/usr/share/php/phpseclib/
        php_admin_value mbstring.func_overload 0
    </IfModule>

</Directory>

# Authorize for setup
<Directory /usr/share/phpmyadmin/setup>
    <IfModule mod_authz_core.c>
        <IfModule mod_authn_file.c>
            AuthType Basic
            AuthName "phpMyAdmin Setup"
            AuthUserFile /etc/phpmyadmin/htpasswd.setup
        </IfModule>
        Require valid-user
    </IfModule>
</Directory>

# Disallow web access to directories that don't need it
<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>

This is the default phpMyAdmin Apache configuration file found on Ubuntu installations, though it will be adequate for a Debian setup as well.

Save and close the file, then enable it by typing:

sudo a2enconf phpmyadmin.conf

Then reload the apache2 service to put the configuration changes into effect:

sudo systemctl reload apache2

Following that, you’ll be able to access the phpMyAdmin login screen by navigating to the following URL in your web browser:

https://your_domain/phpmyadmin

You’ll see the following login screen:

Log in to the interface with the MariaDB username and password you configured. After logging in, you’ll see the user interface, which will look something like this:

Now that you’re able to connect and interact with phpMyAdmin, all that’s left to do is harden your system’s security to protect it from attackers.

Step 4 — 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 of the easiest ways 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 Apache configuration file.

Edit the linked file that has been placed in your Apache configuration directory:

sudo nano /etc/apache2/conf-available/phpmyadmin.conf

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

<Directory /usr/share/phpmyadmin>
    Options FollowSymLinks
    DirectoryIndex index.php
    AllowOverride All

    <IfModule mod_php5.c>
    . . .

When you have added this line, save and close the file.

To implement the changes you made, restart Apache:

sudo systemctl restart apache2

Now that you have enabled .htaccess use 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 content:

AuthType Basic
AuthName "Restricted Files"
AuthUserFile /usr/share/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 /usr/share/phpmyadmin/.htpasswd. You can now create this file and pass it an initial user with the htpasswd utility:

sudo htpasswd -c /usr/share/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.

If you want to enter an additional user, you need to do so without the -c flag, like this:

sudo htpasswd /etc/phpmyadmin/.htpasswd additionaluser

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

https://your_domain_or_IP/phpmyadmin

After entering the Apache authentication, you’ll be taken to the regular phpMyAdmin authentication page to enter your MariaDB credentials. This setup adds an additional layer of security, which is desirable since phpMyAdmin has suffered from vulnerabilities in the past.

Conclusion

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