One place for hosting & domains

      phpMyAdmin

      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:

      Note: MariaDB is a community-developed fork of MySQL, and although the two programs are closely related, they are not completely interchangeable. While phpMyAdmin was designed specifically for managing MySQL databases and makes reference to MySQL in various dialogue boxes, rest assured that your installation of MariaDB will work correctly with phpMyAdmin.

      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.

      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:

      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.0.1.

      Note: On this Downloads page, you will notice that there are download links labeled all-languages and english. The all-languages links will download a version of phpMyAdmin that will allow you to select one of 72 available languages, while the english links will only allow you to use phpMyAdmin in English.

      This guide will use the all-languages package to illustrate how to install phpMyAdmin, but if you plan to use phpMyAdmin in English, you can install the english package. Just be sure to replace the links and file names as necessary in the following commands.

      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.0.1/phpMyAdmin-4.9.0.1-all-languages.tar.gz

      Then extract the tarball:

      • tar xvf phpMyAdmin-4.9.0.1-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.0.1-all-languages.

      Then run the following command. This will move the phpMyAdmin-4.9.0.1-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.0.1-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:

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

      . . .
      $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:

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

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

      Note: If the passphrase you enter here is shorter than 32 characters in length, it will result in the encrypted cookies being less secure. Entering a string longer than 32 characters, though, won't cause any harm.

      To generate a truly random string of characters, you can install and use the pwgen program:

      By default, pwgen creates easily pronounceable, though less secure, passwords. However, by including the -s flag, as in the following command, you can create a completely random, difficult-to-memorize password. Note the final two arguments to this command: 32, which dictates how long the password string pwgen will generate should be; and 1 which tells pwgen how many strings it should generate:

      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:

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

      . . .
      /* 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:

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

      . . .
      /* 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:

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

      . . .
      $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:

      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:

      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

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

      # 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:

      phpMyAdmin 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:

      phpMyAdmin user interface

      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:

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

      <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:

      /usr/share/phpmyadmin/.htaccess

      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
      

      phpMyAdmin apache password

      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.



      Source link

      How to Install and Secure phpMyAdmin with Nginx on a Debian 9 server


      Introduction

      While many users need the functionality of a database system like MySQL, interacting with the system solely from the MySQL command-line client requires familiarity with the SQL language, so it may not be the preferred interface for some.

      phpMyAdmin was created so that users can interact with MySQL through an intuitive web interface, running alongside a PHP development environment. In this guide, we’ll discuss how to install phpMyAdmin on top of an Nginx server, and how to configure the server for increased security.

      Note: There are important security considerations when using software like phpMyAdmin, since it runs on the database server, it deals with database credentials, and it enables a user to easily execute arbitrary SQL queries into your database. Because phpMyAdmin is a widely-deployed PHP application, it is frequently targeted for attack. We will go over some security measures you can take in this tutorial so that you can make informed decisions.

      Prerequisites

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

      Because phpMyAdmin handles authentication using MySQL credentials, it is strongly advisable to install an SSL/TLS certificate to enable encrypted traffic between server and client. If you don’t have an existing domain configured with a valid certificate, you can follow the guide on How to Secure Nginx with Let’s Encrypt on Debian 9.

      Warning: If you don’t have an SSL/TLS certificate installed on the server and you still want to proceed, please consider enforcing access via SSH Tunnels as explained in Step 5 of this guide.

      Once you have met these prerequisites, you can go ahead with the rest of the guide.

      Step 1 — Installing phpMyAdmin

      The first thing we need to do is install phpMyAdmin on the LEMP server. We’re going to use the default Debian repositories to achieve this goal.

      Let’s start by updating the server’s package index with:

      Now you can install phpMyAdmin with:

      • sudo apt install phpmyadmin

      During the installation process, you will be prompted to choose the web server (either Apache or Lighthttp) to configure. Because we are using Nginx as web server, we shouldn't make a choice here. Press tab and then OK to advance to the next step.

      Next, you’ll be prompted whether to use dbconfig-common for configuring the application database. Select Yes. This will set up the internal database and administrative user for phpMyAdmin. You will be asked to define a new password for the phpmyadmin MySQL user. You can also leave it blank and let phpMyAdmin randomly create a password.

      The installation will now finish. For the Nginx web server to find and serve the phpMyAdmin files correctly, we’ll need to create a symbolic link from the installation files to Nginx's document root directory:

      • sudo ln -s /usr/share/phpmyadmin /var/www/html

      Your phpMyAdmin installation is now operational. To access the interface, go to your server's domain name or public IP address followed by /phpmyadmin in your web browser:

      https://server_domain_or_IP/phpmyadmin
      

      phpMyAdmin login screen

      As mentioned before, phpMyAdmin handles authentication using MySQL credentials, which means you should use the same username and password you would normally use to connect to the database via console or via an API. If you need help creating MySQL users, check this guide on How To Manage an SQL Database.

      Note: Logging into phpMyAdmin as the root MySQL user is discouraged because it represents a significant security risk. We'll see how to disable root login in a subsequent step of this guide.

      Your phpMyAdmin installation should be completely functional at this point. However, by installing a web interface, we've exposed our MySQL database server to the outside world. Because of phpMyAdmin's popularity, and the large amounts of data it may provide access to, installations like these are common targets for attacks. In the following sections of this guide, we'll see a few different ways in which we can make our phpMyAdmin installation more secure.

      Step 2 — Changing phpMyAdmin's Default Location

      One of the most basic ways to protect your phpMyAdmin installation is by making it harder to find. Bots will scan for common paths, like /phpmyadmin, /pma, /admin, /mysql and such. Changing the interface's URL from /phpmyadmin to something non-standard will make it much harder for automated scripts to find your phpMyAdmin installation and attempt brute-force attacks.

      With our phpMyAdmin installation, we've created a symbolic link pointing to /usr/share/phpmyadmin, where the actual application files are located. To change phpMyAdmin's interface URL, we will rename this symbolic link.

      First, let's navigate to the Nginx document root directory and list the files it contains to get a better sense of the change we'll make:

      You’ll receive the following output:

      Output

      total 8 -rw-r--r-- 1 root root 612 Apr 8 13:30 index.nginx-debian.html lrwxrwxrwx 1 root root 21 Apr 8 15:36 phpmyadmin -> /usr/share/phpmyadmin

      The output shows that we have a symbolic link called phpmyadmin in this directory. We can change this link name to whatever we'd like. This will in turn change phpMyAdmin's access URL, which can help obscure the endpoint from bots hardcoded to search common endpoint names.

      Choose a name that obscures the purpose of the endpoint. In this guide, we'll name our endpoint /nothingtosee, but you should choose an alternate name. To accomplish this, we'll rename the link:

      • sudo mv phpmyadmin nothingtosee
      • ls -l

      After running the above commands, you’ll receive this output:

      Output

      total 8 -rw-r--r-- 1 root root 612 Apr 8 13:30 index.nginx-debian.html lrwxrwxrwx 1 root root 21 Apr 8 15:36 nothingtosee -> /usr/share/phpmyadmin

      Now, if you go to the old URL, you'll get a 404 error:

      https://server_domain_or_IP/phpmyadmin
      

      phpMyAdmin 404 error

      Your phpMyAdmin interface will now be available at the new URL we just configured:

      https://server_domain_or_IP/nothingtosee
      

      phpMyAdmin login screen

      By obfuscating phpMyAdmin's real location on the server, you're securing its interface against automated scans and manual brute-force attempts.

      Step 3 — Disabling Root Login

      On MySQL as well as within regular Linux systems, the root account is a special administrative account with unrestricted access to the system. In addition to being a privileged account, it's a known login name, which makes it an obvious target for brute-force attacks. To minimize risks, we'll configure phpMyAdmin to deny any login attempts coming from the user root. This way, even if you provide valid credentials for the user root, you'll still get an "access denied" error and won't be allowed to log in.

      Because we chose to use dbconfig-common to configure and store phpMyAdmin settings, the default configuration is currently stored in the database. We'll need to create a new config.inc.php file to define our custom settings.

      Even though the PHP files for phpMyAdmin are located inside /usr/share/phpmyadmin, the application uses configuration files located at /etc/phpmyadmin. We will create a new custom settings file inside /etc/phpmyadmin/conf.d, and name it pma_secure.php:

      • sudo nano /etc/phpmyadmin/conf.d/pma_secure.php

      The following configuration file contains the necessary settings to disable passwordless logins (AllowNoPassword set to false) and root login (AllowRoot set to false):

      /etc/phpmyadmin/conf.d/pma_secure.php

      <?php
      
      # PhpMyAdmin Settings
      # This should be set to a random string of at least 32 chars
      $cfg['blowfish_secret'] = '3!#32@3sa(+=_4?),5XP_:U%%834sdfSdg43yH#{o';
      
      $i=0;
      $i++;
      
      $cfg['Servers'][$i]['auth_type'] = 'cookie';
      $cfg['Servers'][$i]['AllowNoPassword'] = false;
      $cfg['Servers'][$i]['AllowRoot'] = false;
      
      ?>
      

      Save the file when you're done editing by pressing CTRL + X then y to confirm changes and ENTER. The changes will apply automatically. If you reload the login page now and try to log in as root, you will get an Access Denied error:

      access denied

      Root login is now prohibited on your phpMyAdmin installation. This security measure will block brute-force scripts from trying to guess the root database password on your server. Moreover, it will enforce the usage of less-privileged MySQL accounts for accessing phpMyAdmin's web interface, which by itself is an important security practice.

      Step 4 — Creating an Authentication Gateway

      Hiding your phpMyAdmin installation on an unusual location might sidestep some automated bots scanning the network, but it's useless against targeted attacks. To better protect a web application with restricted access, it's generally more effective to stop attackers before they can even reach the application. This way, they'll be unable to use generic exploits and brute-force attacks to guess access credentials.

      In the specific case of phpMyAdmin, it's even more important to keep the login interface locked away. By keeping it open to the world, you're offering a brute-force platform for attackers to guess your database credentials.

      Adding an extra layer of authentication to your phpMyAdmin installation enables you to increase security. Users will be required to pass through an HTTP authentication prompt before ever seeing the phpMyAdmin login screen. Most web servers, including Nginx, provide this capability natively.

      To set this up, we first need to create a password file to store the authentication credentials. Nginx requires that passwords be encrypted using the crypt() function. The OpenSSL suite, which should already be installed on your server, includes this functionality.

      To create an encrypted password, type:

      You will be prompted to enter and confirm the password that you wish to use. The utility will then display an encrypted version of the password that will look something like this:

      Output

      O5az.RSPzd.HE

      Copy this value, as you will need to paste it into the authentication file we'll be creating.

      Now, create an authentication file. We'll call this file pma_pass and place it in the Nginx configuration directory:

      • sudo nano /etc/nginx/pma_pass

      In this file, you’ll specify the username you would like to use, followed by a colon (:), followed by the encrypted version of the password you received from the openssl passwd utility.

      We are going to name our user sammy, but you should choose a different username. The file should look like this:

      /etc/nginx/pma_pass

      sammy:O5az.RSPzd.HE
      

      Save and close the file when you're done.

      Now we're ready to modify the Nginx configuration file. For this guide, we'll use the configuration file located at /etc/nginx/sites-available/example.com. You should use the relevant Nginx configuration file for the web location where phpMyAdmin is currently hosted. Open this file in your text editor to get started:

      • sudo nano /etc/nginx/sites-available/example.com

      Locate the server block, and the location / section within it. We need to create a new location section within this block to match phpMyAdmin's current path on the server. In this guide, phpMyAdmin's location relative to the web root is /nothingtosee:

      /etc/nginx/sites-available/default

      server {
          . . .
      
              location / {
                      try_files $uri $uri/ =404;
              }
      
              location /nothingtosee {
                      # Settings for phpMyAdmin will go here
              }
      
          . . .
      }
      

      Within this block, we'll need to set up two different directives: auth_basic, which defines the message that will be displayed on the authentication prompt, and auth_basic_user_file, pointing to the file we just created. This is how your configuration file should look like when you're finished:

      /etc/nginx/sites-available/default

      server {
          . . .
      
              location /nothingtosee {
                      auth_basic "Admin Login";
                      auth_basic_user_file /etc/nginx/pma_pass;
              }
      
      
          . . .
      }
      

      Save and close the file when you're done. To check if the configuration file is valid, you can run:

      The following output is expected:

      Output

      nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful

      To activate the new authentication gate, you must reload the web server:

      • sudo systemctl reload nginx

      Now, if you visit the phpMyAdmin URL in your web browser, you should be prompted for the username and password you added to the pma_pass file:

      https://server_domain_or_IP/nothingtosee
      

      Nginx authentication page

      Once you enter your credentials, you'll be taken to the standard phpMyAdmin login page.

      Note: If refreshing the page does not work, you may have to clear your cache or use a different browser session if you've already been using phpMyAdmin.

      In addition to providing an extra layer of security, this gateway will help keep your MySQL logs clean of spammy authentication attempts.

      Step 5 — Setting Up Access via Encrypted Tunnels (Optional)

      For increased security, it is possible to lock down your phpMyAdmin installation to authorized hosts only. You can whitelist authorized hosts in your Nginx configuration file, so that any request coming from an IP address that is not on the list will be denied.

      Even though this feature alone can be enough in some use cases, it's not always the best long-term solution, mainly due to the fact that most people don't access the Internet from static IP addresses. As soon as you get a new IP address from your Internet provider, you'll be unable to get to the phpMyAdmin interface until you update the Nginx configuration file with your new IP address.

      For a more robust long-term solution, you can use IP-based access control to create a setup in which users will only have access to your phpMyAdmin interface if they're accessing from either an authorized IP address or localhost via SSH tunneling. We'll see how to set this up in the sections below.

      Combining IP-based access control with SSH tunneling greatly increases security because it fully blocks access coming from the public internet (except for authorized IPs), in addition to providing a secure channel between user and server through the use of encrypted tunnels.

      Setting Up IP-Based Access Control on Nginx

      On Nginx, IP-based access control can be defined in the corresponding location block of a given site, using the directives allow and deny. For instance, if we want to only allow requests coming from a given host, we should include the following two lines, in this order, inside the relevant location block for the site we would like to protect:

      allow hostname_or_IP;
      deny all;
      

      You can allow as many hosts as you want, you only need to include one allow line for each authorized host/IP inside the respective location block for the site you're protecting. The directives will be evaluated in the same order as they are listed, until a match is found or the request is finally denied due to the deny all directive.

      We'll now configure Nginx to only allow requests coming from localhost or your current IP address. First, you'll need to know the current public IP address your local machine is using to connect to the Internet. There are various ways to obtain this information; for simplicity, we're going to use the service provided by ipinfo.io. You can either open the URL https://ipinfo.io/ip in your browser, or run the following command from your local machine:

      • curl https://ipinfo.io/ip

      You should get a simple IP address as output, like this:

      Output

      203.0.113.111

      That is your current public IP address. We'll configure phpMyAdmin's location block to only allow requests coming from that IP, in addition to localhost. We'll need to edit once again the configuration block for phpMyAdmin inside /etc/nginx/sites-available/example.com.

      Open the Nginx configuration file using your command-line editor of choice:

      • sudo nano /etc/nginx/sites-available/example.com

      Because we already have an access rule within our current configuration, we need to combine it with IP-based access control using the directive satisfy all. This way, we can keep the current HTTP authentication prompt for increased security.

      This is how your phpMyAdmin Nginx configuration should look like after you're done editing:

      /etc/nginx/sites-available/example.com

      server {
          . . .
      
          location /nothingtosee {
              satisfy all; #requires both conditions
      
              allow 203.0.113.111; #allow your IP
              allow 127.0.0.1; #allow localhost via SSH tunnels
              deny all; #deny all other sources
      
              auth_basic "Admin Login";
              auth_basic_user_file /etc/nginx/pma_pass;
          }
      
          . . .
      }
      

      Remember to replace nothingtosee with the actual path where phpMyAdmin can be found, and the highlighted IP address with your current public IP address.

      Save and close the file when you're done. To check if the configuration file is valid, you can run:

      The following output is expected:

      Output

      nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful

      Now reload the web server so the changes take effect:

      • sudo systemctl reload nginx

      Because your IP address is explicitly listed as an authorized host, your access shouldn't be disturbed. Anyone else trying to access your phpMyAdmin installation will now get a 403 error (Forbidden):

      https://server_domain_or_IP/nothingtosee
      

      403 error

      In the next section, we'll see how to use SSH tunneling to access the web server through local requests. This way, you'll still be able to access phpMyAdmin's interface even when your IP address changes.

      Accessing phpMyAdmin Through an Encrypted Tunnel

      SSH tunneling works as a way of redirecting network traffic through encrypted channels. By running an ssh command similar to what you would use to log into a server, you can create a secure "tunnel" between your local machine and that server. All traffic coming in on a given local port can now be redirected through the encrypted tunnel and use the remote server as a proxy, before reaching out to the internet. It's similar to what happens when you use a VPN (Virtual Private Network), however SSH tunneling is much simpler to set up.

      We'll use SSH tunneling to proxy our requests to the remote web server running phpMyAdmin. By creating a tunnel between your local machine and the server where phpMyAdmin is installed, you can redirect local requests to the remote web server, and what's more important, traffic will be encrypted and requests will reach Nginx as if they're coming from localhost. This way, no matter what IP address you're connecting from, you'll be able to securely access phpMyAdmin's interface.

      Because the traffic between your local machine and the remote web server will be encrypted, this is a safe alternative for situations where you can't have an SSL/TLS certificate installed on the web server running phpMyAdmin.

      From your local machine, run this command whenever you need access to phpMyAdmin:

      • ssh user@server_domain_or_IP -L 8000:localhost:80 -L 8443:localhost:443 -N

      Let's examine each part of the command:

      • user: SSH user to connect to the server where phpMyAdmin is running
      • hostname_or_IP: SSH host where phpMyAdmin is running
      • -L 8000:localhost:80 redirects HTTP traffic on port 8000
      • -L 8443:localhost:443 redirects HTTPS traffic on port 8443
      • -N: do not execute remote commands

      Note: This command will block the terminal until interrupted with a CTRL+C, in which case it will end the SSH connection and stop the packet redirection. If you'd prefer to run this command in background mode, you can use the SSH option -f.

      Now, go to your browser and replace server_domain_or_IP with localhost:PORT, where PORT is either 8000 for HTTP or 8443 for HTTPS:

      http://localhost:8000/nothingtosee
      
      https://localhost:443/nothingtosee
      

      phpMyAdmin login screen

      Note: If you're accessing phpMyAdmin via https, you might get an alert message questioning the security of the SSL certificate. This happens because the domain name you're using (localhost) doesn't match the address registered within the certificate (domain where phpMyAdmin is actually being served). It is safe to proceed.

      All requests on localhost:8000 (HTTP) and localhost:8443 (HTTPS) are now being redirected through a secure tunnel to your remote phpMyAdmin application. Not only have you increased security by disabling public access to your phpMyAdmin, you also protected all traffic between your local computer and the remote server by using an encrypted tunnel to send and receive data.

      If you'd like to enforce the usage of SSH tunneling to anyone who wants access to your phpMyAdmin interface (including you), you can do that by removing any other authorized IPs from the Nginx configuration file, leaving 127.0.0.1 as the only allowed host to access that location. Considering nobody will be able to make direct requests to phpMyAdmin, it is safe to remove HTTP authentication in order to simplify your setup. This is how your configuration file would look like in such a scenario:

      /etc/nginx/sites-available/example.com

      server {
          . . .
      
          location /nothingtosee { 
              allow 127.0.0.1; #allow localhost only
              deny all; #deny all other sources
          }
      
          . . .
      }
      

      Once you reload Nginx's configuration with sudo systemctl reload nginx, your phpMyAdmin installation will be locked down and users will be required to use SSH tunnels in order to access phpMyAdmin's interface via redirected requests.

      Conclusion

      In this tutorial, we saw how to install phpMyAdmin on Ubuntu 18.04 running Nginx as the web server. We also covered advanced methods to secure a phpMyAdmin installation on Ubuntu, such as disabling root login, creating an extra layer of authentication, and using SSH tunneling to access a phpMyAdmin installation via local requests only.

      After completing this tutorial, you should be able to manage your MySQL databases from a reasonably secure web interface. This user interface exposes most of the functionality available via the MySQL command line. You can browse databases and schema, execute queries, and create new data sets and structures.



      Source link

      How to Install and Secure phpMyAdmin with Nginx on an Ubuntu 18.04 server


      Introduction

      While many users need the functionality of a database system like MySQL, interacting with the system solely from the MySQL command-line client requires familiarity with the SQL language, so it may not be the preferred interface for some.

      phpMyAdmin was created so that users can interact with MySQL through an intuitive web interface, running alongside a PHP development environment. In this guide, we’ll discuss how to install phpMyAdmin on top of an Nginx server, and how to configure the server for increased security.

      Note: There are important security considerations when using software like phpMyAdmin, since it runs on the database server, it deals with database credentials, and it enables a user to easily execute arbitrary SQL queries into your database. Because phpMyAdmin is a widely-deployed PHP application, it is frequently targeted for attack. We will go over some security measures you can take in this tutorial so that you can make informed decisions.

      Prerequisites

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

      Because phpMyAdmin handles authentication using MySQL credentials, it is strongly advisable to install an SSL/TLS certificate to enable encrypted traffic between server and client. If you do not have an existing domain configured with a valid certificate, you can follow this guide on securing Nginx with Let’s Encrypt on Ubuntu 18.04.

      Warning: If you don’t have an SSL/TLS certificate installed on the server and you still want to proceed, please consider enforcing access via SSH Tunnels as explained in Step 5 of this guide.

      Once you have met these prerequisites, you can go ahead with the rest of the guide.

      Step 1 — Installing phpMyAdmin

      The first thing we need to do is install phpMyAdmin on the LEMP server. We’re going to use the default Ubuntu repositories to achieve this goal.

      Let’s start by updating the server’s package index with:

      Now you can install phpMyAdmin with:

      • sudo apt install phpmyadmin

      During the installation process, you will be prompted to choose the web server (either Apache or Lighthttp) to configure. Because we are using Nginx as web server, we shouldn't make a choice here. Press tab and then OK to advance to the next step.

      Next, you’ll be prompted whether to use dbconfig-common for configuring the application database. Select Yes. This will set up the internal database and administrative user for phpMyAdmin. You will be asked to define a new password for the phpmyadmin MySQL user. You can also leave it blank and let phpMyAdmin randomly create a password.

      The installation will now finish. For the Nginx web server to find and serve the phpMyAdmin files correctly, we’ll need to create a symbolic link from the installation files to Nginx's document root directory:

      • sudo ln -s /usr/share/phpmyadmin /var/www/html

      Your phpMyAdmin installation is now operational. To access the interface, go to your server's domain name or public IP address followed by /phpmyadmin in your web browser:

      https://server_domain_or_IP/phpmyadmin
      

      phpMyAdmin login screen

      As mentioned before, phpMyAdmin handles authentication using MySQL credentials, which means you should use the same username and password you would normally use to connect to the database via console or via an API. If you need help creating MySQL users, check this guide on How To Manage an SQL Database.

      Note: Logging into phpMyAdmin as the root MySQL user is discouraged because it represents a significant security risk. We'll see how to disable root login in a subsequent step of this guide.

      Your phpMyAdmin installation should be completely functional at this point. However, by installing a web interface, we've exposed our MySQL database server to the outside world. Because of phpMyAdmin's popularity, and the large amounts of data it may provide access to, installations like these are common targets for attacks. In the following sections of this guide, we'll see a few different ways in which we can make our phpMyAdmin installation more secure.

      Step 2 — Changing phpMyAdmin's Default Location

      One of the most basic ways to protect your phpMyAdmin installation is by making it harder to find. Bots will scan for common paths, like phpmyadmin, pma, admin, mysql and such. Changing the interface's URL from /phpmyadmin to something non-standard will make it much harder for automated scripts to find your phpMyAdmin installation and attempt brute-force attacks.

      With our phpMyAdmin installation, we've created a symbolic link pointing to /usr/share/phpmyadmin, where the actual application files are located. To change phpMyAdmin's interface URL, we will rename this symbolic link.

      First, let's navigate to the Nginx document root directory and list the files it contains to get a better sense of the change we'll make:

      You’ll receive the following output:

      Output

      total 8 -rw-r--r-- 1 root root 612 Apr 8 13:30 index.nginx-debian.html lrwxrwxrwx 1 root root 21 Apr 8 15:36 phpmyadmin -> /usr/share/phpmyadmin

      The output shows that we have a symbolic link called phpmyadmin in this directory. We can change this link name to whatever we'd like. This will in turn change phpMyAdmin's access URL, which can help obscure the endpoint from bots hardcoded to search common endpoint names.

      Choose a name that obscures the purpose of the endpoint. In this guide, we'll name our endpoint /nothingtosee, but you should choose an alternate name. To accomplish this, we'll rename the link:

      • sudo mv phpmyadmin nothingtosee
      • ls -l

      After running the above commands, you’ll receive this output:

      Output

      total 8 -rw-r--r-- 1 root root 612 Apr 8 13:30 index.nginx-debian.html lrwxrwxrwx 1 root root 21 Apr 8 15:36 nothingtosee -> /usr/share/phpmyadmin

      Now, if you go to the old URL, you'll get a 404 error:

      https://server_domain_or_IP/phpmyadmin
      

      phpMyAdmin 404 error

      Your phpMyAdmin interface will now be available at the new URL we just configured:

      https://server_domain_or_IP/nothingtosee
      

      phpMyAdmin login screen

      By obfuscating phpMyAdmin's real location on the server, you're securing its interface against automated scans and manual brute-force attempts.

      Step 3 — Disabling Root Login

      On MySQL as well as within regular Linux systems, the root account is a special administrative account with unrestricted access to the system. In addition to being a privileged account, it's a known login name, which makes it an obvious target for brute-force attacks. To minimize risks, we'll configure phpMyAdmin to deny any login attempts coming from the user root. This way, even if you provide valid credentials for the user root, you'll still get an "access denied" error and won't be allowed to log in.

      Because we chose to use dbconfig-common to configure and store phpMyAdmin settings, the default configuration is currently stored in the database. We'll need to create a new config.inc.php file to define our custom settings.

      Even though the PHP files for phpMyAdmin are located inside /usr/share/phpmyadmin, the application uses configuration files located at /etc/phpmyadmin. We will create a new custom settings file inside /etc/phpmyadmin/conf.d, and name it pma_secure.php:

      • sudo nano /etc/phpmyadmin/conf.d/pma_secure.php

      The following configuration file contains the necessary settings to disable passwordless logins (AllowNoPassword set to false) and root login (AllowRoot set to false):

      /etc/phpmyadmin/conf.d/pma_secure.php

      <?php
      
      # PhpMyAdmin Settings
      # This should be set to a random string of at least 32 chars
      $cfg['blowfish_secret'] = '3!#32@3sa(+=_4?),5XP_:U%%834sdfSdg43yH#{o';
      
      $i=0;
      $i++;
      
      $cfg['Servers'][$i]['auth_type'] = 'cookie';
      $cfg['Servers'][$i]['AllowNoPassword'] = false;
      $cfg['Servers'][$i]['AllowRoot'] = false;
      
      ?>
      

      Save the file when you're done editing by pressing CTRL + X then y to confirm changes and ENTER. The changes will apply automatically. If you reload the login page now and try to log in as root, you will get an Access Denied error:

      access denied

      Root login is now prohibited on your phpMyAdmin installation. This security measure will block brute-force scripts from trying to guess the root database password on your server. Moreover, it will enforce the usage of less-privileged MySQL accounts for accessing phpMyAdmin's web interface, which by itself is an important security practice.

      Step 4 — Creating an Authentication Gateway

      Hiding your phpMyAdmin installation on an unusual location might sidestep some automated bots scanning the network, but it's useless against targeted attacks. To better protect a web application with restricted access, it's generally more effective to stop attackers before they can even reach the application. This way, they'll be unable to use generic exploits and brute-force attacks to guess access credentials.

      In the specific case of phpMyAdmin, it's even more important to keep the login interface locked away. By keeping it open to the world, you're offering a brute-force platform for attackers to guess your database credentials.

      Adding an extra layer of authentication to your phpMyAdmin installation enables you to increase security. Users will be required to pass through an HTTP authentication prompt before ever seeing the phpMyAdmin login screen. Most web servers, including Nginx, provide this capability natively.

      To set this up, we first need to create a password file to store the authentication credentials. Nginx requires that passwords be encrypted using the crypt() function. The OpenSSL suite, which should already be installed on your server, includes this functionality.

      To create an encrypted password, type:

      You will be prompted to enter and confirm the password that you wish to use. The utility will then display an encrypted version of the password that will look something like this:

      Output

      O5az.RSPzd.HE

      Copy this value, as you will need to paste it into the authentication file we'll be creating.

      Now, create an authentication file. We'll call this file pma_pass and place it in the Nginx configuration directory:

      • sudo nano /etc/nginx/pma_pass

      In this file, you’ll specify the username you would like to use, followed by a colon (:), followed by the encrypted version of the password you received from the openssl passwd utility.

      We are going to name our user sammy, but you should choose a different username. The file should look like this:

      /etc/nginx/pma_pass

      sammy:O5az.RSPzd.HE
      

      Save and close the file when you're done.

      Now we're ready to modify the Nginx configuration file. For this guide, we'll use the configuration file located at /etc/nginx/sites-available/example.com. You should use the relevant Nginx configuration file for the web location where phpMyAdmin is currently hosted. Open this file in your text editor to get started:

      • sudo nano /etc/nginx/sites-available/example.com

      Locate the server block, and the location / section within it. We need to create a new location section within this block to match phpMyAdmin's current path on the server. In this guide, phpMyAdmin's location relative to the web root is /nothingtosee:

      /etc/nginx/sites-available/default

      server {
          . . .
      
              location / {
                      try_files $uri $uri/ =404;
              }
      
              location /nothingtosee {
                      # Settings for phpMyAdmin will go here
              }
      
          . . .
      }
      

      Within this block, we'll need to set up two different directives: auth_basic, which defines the message that will be displayed on the authentication prompt, and auth_basic_user_file, pointing to the file we just created. This is how your configuration file should look like when you're finished:

      /etc/nginx/sites-available/default

      server {
          . . .
      
              location /nothingtosee {
                      auth_basic "Admin Login";
                      auth_basic_user_file /etc/nginx/pma_pass;
              }
      
      
          . . .
      }
      

      Save and close the file when you're done. To check if the configuration file is valid, you can run:

      The following output is expected:

      Output

      nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful

      To activate the new authentication gate, you must reload the web server:

      • sudo systemctl reload nginx

      Now, if you visit the phpMyAdmin URL in your web browser, you should be prompted for the username and password you added to the pma_pass file:

      https://server_domain_or_IP/nothingtosee
      

      Nginx authentication page

      Once you enter your credentials, you'll be taken to the standard phpMyAdmin login page.

      Note: If refreshing the page does not work, you may have to clear your cache or use a different browser session if you've already been using phpMyAdmin.

      In addition to providing an extra layer of security, this gateway will help keep your MySQL logs clean of spammy authentication attempts.

      Step 5 — Setting Up Access via Encrypted Tunnels (Optional)

      For increased security, it is possible to lock down your phpMyAdmin installation to authorized hosts only. You can whitelist authorized hosts in your Nginx configuration file, so that any request coming from an IP address that is not on the list will be denied.

      Even though this feature alone can be enough in some use cases, it's not always the best long-term solution, mainly due to the fact that most people don't access the Internet from static IP addresses. As soon as you get a new IP address from your Internet provider, you'll be unable to get to the phpMyAdmin interface until you update the Nginx configuration file with your new IP address.

      For a more robust long-term solution, you can use IP-based access control to create a setup in which users will only have access to your phpMyAdmin interface if they're accessing from either an authorized IP address or localhost via SSH tunneling. We'll see how to set this up in the sections below.

      Combining IP-based access control with SSH tunneling greatly increases security because it fully blocks access coming from the public internet (except for authorized IPs), in addition to providing a secure channel between user and server through the use of encrypted tunnels.

      Setting Up IP-Based Access Control on Nginx

      On Nginx, IP-based access control can be defined in the corresponding location block of a given site, using the directives allow and deny. For instance, if we want to only allow requests coming from a given host, we should include the following two lines, in this order, inside the relevant location block for the site we would like to protect:

      allow hostname_or_IP;
      deny all;
      

      You can allow as many hosts as you want, you only need to include one allow line for each authorized host/IP inside the respective location block for the site you're protecting. The directives will be evaluated in the same order as they are listed, until a match is found or the request is finally denied due to the deny all directive.

      We'll now configure Nginx to only allow requests coming from localhost or your current IP address. First, you'll need to know the current public IP address your local machine is using to connect to the Internet. There are various ways to obtain this information; for simplicity, we're going to use the service provided by ipinfo.io. You can either open the URL https://ipinfo.io/ip in your browser, or run the following command from your local machine:

      • curl https://ipinfo.io/ip

      You should get a simple IP address as output, like this:

      Output

      203.0.113.111

      That is your current public IP address. We'll configure phpMyAdmin's location block to only allow requests coming from that IP, in addition to localhost. We'll need to edit once again the configuration block for phpMyAdmin inside /etc/nginx/sites-available/example.com.

      Open the Nginx configuration file using your command-line editor of choice:

      • sudo nano /etc/nginx/sites-available/example.com

      Because we already have an access rule within our current configuration, we need to combine it with IP-based access control using the directive satisfy all. This way, we can keep the current HTTP authentication prompt for increased security.

      This is how your phpMyAdmin Nginx configuration should look like after you're done editing:

      /etc/nginx/sites-available/example.com

      server {
          . . .
      
          location /nothingtosee {
              satisfy all; #requires both conditions
      
              allow 203.0.113.111; #allow your IP
              allow 127.0.0.1; #allow localhost via SSH tunnels
              deny all; #deny all other sources
      
              auth_basic "Admin Login";
              auth_basic_user_file /etc/nginx/pma_pass;
          }
      
          . . .
      }
      

      Remember to replace nothingtosee with the actual path where phpMyAdmin can be found, and the highlighted IP address with your current public IP address.

      Save and close the file when you're done. To check if the configuration file is valid, you can run:

      The following output is expected:

      Output

      nginx: the configuration file /etc/nginx/nginx.conf syntax is ok nginx: configuration file /etc/nginx/nginx.conf test is successful

      Now reload the web server so the changes take effect:

      • sudo systemctl reload nginx

      Because your IP address is explicitly listed as an authorized host, your access shouldn't be disturbed. Anyone else trying to access your phpMyAdmin installation will now get a 403 error (Forbidden):

      https://server_domain_or_IP/nothingtosee
      

      403 error

      In the next section, we'll see how to use SSH tunneling to access the web server through local requests. This way, you'll still be able to access phpMyAdmin's interface even when your IP address changes.

      Accessing phpMyAdmin Through an Encrypted Tunnel

      SSH tunneling works as a way of redirecting network traffic through encrypted channels. By running an ssh command similar to what you would use to log into a server, you can create a secure "tunnel" between your local machine and that server. All traffic coming in on a given local port can now be redirected through the encrypted tunnel and use the remote server as a proxy, before reaching out to the internet. It's similar to what happens when you use a VPN (Virtual Private Network), however SSH tunneling is much simpler to set up.

      We'll use SSH tunneling to proxy our requests to the remote web server running phpMyAdmin. By creating a tunnel between your local machine and the server where phpMyAdmin is installed, you can redirect local requests to the remote web server, and what's more important, traffic will be encrypted and requests will reach Nginx as if they're coming from localhost. This way, no matter what IP address you're connecting from, you'll be able to securely access phpMyAdmin's interface.

      Because the traffic between your local machine and the remote web server will be encrypted, this is a safe alternative for situations where you can't have an SSL/TLS certificate installed on the web server running phpMyAdmin.

      From your local machine, run this command whenever you need access to phpMyAdmin:

      • ssh user@server_domain_or_IP -L 8000:localhost:80 -L 8443:localhost:443 -N

      Let's examine each part of the command:

      • user: SSH user to connect to the server where phpMyAdmin is running
      • hostname_or_IP: SSH host where phpMyAdmin is running
      • -L 8000:localhost:80 redirects HTTP traffic on port 8000
      • -L 8443:localhost:443 redirects HTTPS traffic on port 8443
      • -N: do not execute remote commands

      Note: This command will block the terminal until interrupted with a CTRL+C, in which case it will end the SSH connection and stop the packet redirection. If you'd prefer to run this command in background mode, you can use the SSH option -f.

      Now, go to your browser and replace server_domain_or_IP with localhost:PORT, where PORT is either 8000 for HTTP or 8443 for HTTPS:

      http://localhost:8000/nothingtosee
      
      https://localhost:443/nothingtosee
      

      phpMyAdmin login screen

      Note: If you're accessing phpMyAdmin via https, you might get an alert message questioning the security of the SSL certificate. This happens because the domain name you're using (localhost) doesn't match the address registered within the certificate (domain where phpMyAdmin is actually being served). It is safe to proceed.

      All requests on localhost:8000 (HTTP) and localhost:8443 (HTTPS) are now being redirected through a secure tunnel to your remote phpMyAdmin application. Not only have you increased security by disabling public access to your phpMyAdmin, you also protected all traffic between your local computer and the remote server by using an encrypted tunnel to send and receive data.

      If you'd like to enforce the usage of SSH tunneling to anyone who wants access to your phpMyAdmin interface (including you), you can do that by removing any other authorized IPs from the Nginx configuration file, leaving 127.0.0.1 as the only allowed host to access that location. Considering nobody will be able to make direct requests to phpMyAdmin, it is safe to remove HTTP authentication in order to simplify your setup. This is how your configuration file would look like in such a scenario:

      /etc/nginx/sites-available/example.com

      server {
          . . .
      
          location /nothingtosee { 
              allow 127.0.0.1; #allow localhost only
              deny all; #deny all other sources
          }
      
          . . .
      }
      

      Once you reload Nginx's configuration with sudo systemctl reload nginx, your phpMyAdmin installation will be locked down and users will be required to use SSH tunnels in order to access phpMyAdmin's interface via redirected requests.

      Conclusion

      In this tutorial, we saw how to install phpMyAdmin on Ubuntu 18.04 running Nginx as the web server. We also covered advanced methods to secure a phpMyAdmin installation on Ubuntu, such as disabling root login, creating an extra layer of authentication, and using SSH tunneling to access a phpMyAdmin installation via local requests only.

      After completing this tutorial, you should be able to manage your MySQL databases from a reasonably secure web interface. This user interface exposes most of the functionality available via the MySQL command line. You can browse databases and schema, execute queries, and create new data sets and structures.



      Source link