Setting up MySQL and PostgreSQL web interfaces

Webmin has interfaces already to gain access to MySQL and PostgreSQL, and you could do everything you wanted to from the command line, or Command Shell, or Custom Commands, or SSH Login or Text Login, or even the super slick ">_" Terminal (Alt-K) in the new Authentic Theme of Webmin, but I have found the eye-candy and feature richness of the phpMyAdmin and phpPgAdmin far better for managing, structure, automation, info and users within them.

10-30-2017 Update: How to upgrade to (or install) phpMyAdmin 4.7.4: (video shows 4.5.4)

I upgraded my system recently and the new best way is to install is with Composer. See https://docs.phpmyadmin.net/en/latest/setup.html#installing-using-composer for all the details, its my original source. Also note that I have handy instructions about how to better install Composer on your machine back a few pages in this guide on the Web Server Preparations page.

If you installed the phpMyAdmin the Ubuntu way as I showed in the video below, uninstall it before installing with composer. If your doing a fresh install, you need to make a directory like /var/www/html/phpmyadmin with your login ID as the owner using chown; if you're upgrading, just make yourself the owner of the current phpmyadmin directory because it's already there. If you're doing a new install, or even if you plan on hosting separate sites with other friends/users, it's better to have your phpmyadmin directory as a sub-directory of your website's main folder. If you're upgrading and don't want to lose the existing protected web directories level of security that I showed at the 13:45 time mark (and detailed below under "Protected Web Directories layer of security"), you have to move the ".htaccess" and ".htpasswd" files to somewhere temporarily then move them back after you're all done.

The new best way to install phpMyAdmin (if you installed Composer like I wrote about on the Web Server Preparations page), is going to be similar to this:

  • php ~/bin/composer create-project phpmyadmin/phpmyadmin /var/www/html/phpmyadmin

The screen messages at the end of a "php ~/bin/composer..." command will show a set of suggested install's. If the suggestion is of the form creator/package-name, like:

  • symfony/cache suggests installing symfony/polyfill-apcu (For using ApcuAdapter on HHVM)

where symfony/polyfill-apcu is the polyfill-apcu package created by symfony then from inside the directory of that main software "/usr/share/phpmyadmin" or "/var/www/html/phpmyadmin" you can install them with the commands:

  • php ~/bin/composer require symfony/polyfill-apcu
  • php ~/bin/composer require symfony/event-dispatcher
  • ...and so on ending with ...
  • php ~/bin/composer update

You're nearly done. Before you can run phpMyAdmin as a webpage, you may need to do an Apache linking. If it's a sub-directory of your website, no worry's about altering Apache files, just after your ".com" put "/phpmyadmin/setup" and go. Because I was just upgrading when I first did this, the phpMyAdmin 4.7.4 that I installed didn't have a Apache.conf file but my web-server needed to know where it was, so I added info to one of my main servers configuration files; a-site-I-made.com.conf in /etc/apache2/sites-available like so:

  • ## START phpMyAdmin default Apache configuration
  • Alias /phpmyadmin /usr/share/phpmyadmin

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

  • ## Authorize for setup and remove after phpMyAdmin is working
  • <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
  • ## uncomment the following 9 lines after setup of yoursite.com/phpmyadmin/setup
  • #<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>
  • ## END phpMyAdmin default Apache configuration

For me, that went right above the </VirtualHost> tag in the file. I'm not sure if the Authorize for setup section needs to be there anymore, but if you finish up below with the security stuff, you'll definitely delete it later. If you are doing a first time install in some website root directory, you don't need to do that whole Apache editing thing, especially if you are using the /phpmyadmin/setup to finish installing.

Restart that Apache server and you're now ready to open your-site.com/phpmyadmin/setup page. When the Setup first launched I saw a complaint like "Bzip2 compression and decompression requires functions (bzopen, bzcompress) which are unavailable on this system." This was remedied by installing the bzip2 program with sudo apt install ... or whatever similar method you like. (Special note 5/23/2018: Ubuntu changed something so installing php7.0-bz2 is what's needed now) The setup program wouldn't recognize that it was installed till I rebooted my server then went back to the your-site.com/phpmyadmin/setup page. I spent a lot of time determining what settings to change for my specific purpose, and you should too.

After that you downloading the config.inc.php file made by the setup page, then you move it into the /var/www/html/phpmyadmin folder (or wherever you put your phpmyadmin files), you'll need to make an appropriate MySQL user account to log into the phpmyadmin page because the latest MySQL in Ubuntu is blocking root from remote login's. Also, if you don't like the way phpMyAdmin runs, delete the config.inc.php file and make a better one with setup.

Another great quirk since phpMyAdmin 4.5.4 is the new version want's a higher security Blowfish passphrase. It may have been automatically made while you were running the setup, but if you see an error message about that, ... make up 32 random characters and put them in the file /usr/share/phpmyadmin/config.inc.php in the $cfg['blowfish_secret'] = ''; statement right between the '' marks. If you don't trust yourself to make a random string the right length, try the linux command "pwgen 32 1"; 32 is the number of characters, and 1 is the quantity of 32 character outputs.

Finish up with these suggestions; https://docs.phpmyadmin.net/en/latest/setup.html#securing-your-phpmyadmin-installation

  • If while reading the punch-list there you wonder where the "display_errors" variable is, look in your three different php.ini files that I discussed on the page Install Your Distro to Build Your Base Server in subheading "PHP Configuration Screw-up for PHP 7.0+:".
  • If you delete the test and setup directories like instructed, be sure to remove the erroneous directory reference in that Apache .conf file mentioned above; look for <Directory /usr/share/phpmyadmin/setup/lib> Require all denied </Directory> and delete it cause /setup/lib will be gone too. Also delete that section called # Authorize for setup, then reload Apache.

phpMyAdmin:

The instructions that guided me while installing phpMyAdmin for the MySQL system can be found on Ubuntu here, and also on Digital Ocean here. The Digital Ocean is mainly for closing a known hole in security, but they give a command line method, where I will demonstrate a Webmin method of securing the directory.

Copy and paste these as seen in the video while you are installing phpMyAdmin on your system:

  • System => Software Packages => Package from APT
  • In the "Alt-K" Terminal Interface (it assumes sudo)
    • phpenmod mcrypt mbstring gettext
    • ln -s /etc/phpmyadmin/apache.conf /etc/apache2/conf-available/phpmyadmin.conf
    • a2enconf phpmyadmin.conf
    • service apache2 reload

The following is a directive you need to manually add to apache.conf

  • Others => File Manager
    • Navigate to /etc/phpmyadmin
    • find and click the Edit tool in the "Actions" column for apache.conf
    • Alter the contents to reflect AllowOverride All

Then theirs the Protected Web Directories layer of security

  • Others => Protected Web Directories => Add protection for a new directory
    • Directory path: /usr/share/phpmyadmin
    • Authentication realm: Type in something clever here

For more information about MySQL go to the source: https://www.mysql.com/why-mysql/white-papers/whats-new-mysql-5-7/.

phpPgAdmin:

The instructions that guided me while installing phpPgAdmin for the PostgreSQL system can be found on Ubuntu here, and also on How-To forge here. The How To forge page is VERY similar to the Ubuntu, but has lots of screenshots. I'm also adding a layer of security that I learned from the Digital Ocean instructions linked above in the phpMyAdmin instructions; I'll proactively close a potential hole in security by Protecting Web Directories with a Webmin module.

Copy and paste these as seen in the video while you are installing phpPgAdmin on your system:

  • System => Software Packages => Package from APT
  • Servers => PostgreSQL Database Server => User, Groups and Permissions => PostgreSQL Users
    • Click on the entry for the root user "postgres" and in the next window type in your desired password for that SUPER-USER!
  • Others => File Manager => /etc/apache2/conf-available/phppgadmin.conf find and edit/add the following lines
    • Wrong: Require local
    • Right: # Require local
    • Add below that: Allow From All
    • In order to have protected web directories to work right for this site
      • Wrong: AllowOverride None
      • Right: AllowOverride All
  • Others => File Manager => /etc/phppgadmin/config.inc.php find and edit the following line
    • Wrong: $conf['extra_login_security'] = true;
    • Right: $conf['extra_login_security'] = false;
  • Others => Protected Web Directories => Add protection for a new directory
    • Directory path: /usr/share/phppgadmin
    • Authentication realm: Type in something clever here

For more information about PostgreSQL go to the source: https://www.postgresql.org/about/