DB-유

phpMyAdmin on Synology

· synology, phpmyadmin, mariadb

Synology packages both MariaDB and phpMyAdmin, so a database with a web front end takes about five minutes. Then it gets messy: there are two MariaDB packages on different ports, phpMyAdmin only talks to one of them by default, and everything is wide open until you lock it down. I've seen more than one NAS with phpMyAdmin reachable from the internet and a root password of root, which is why I do the lockdown part every time.

Here's the install on DSM 6.1, wiring phpMyAdmin to MariaDB 10, and then restricting who can reach it. Web Station and a PHP package (7.0 is fine) need to be there first; Package Center pulls in the dependency for you.

MariaDB 10 first

Package Center → MariaDB 10 → Install. The installer asks for a root password; set a real one. When it finishes, open the package from the main menu and note two things:

  • The TCP port is 3307, not 3306. Port 3306 belongs to the older MariaDB 5 package, which may also be present if the NAS has been upgraded from DSM 5.
  • "Enable TCP/IP connection" is unticked by default. Leave it off unless something outside the NAS must connect; phpMyAdmin uses the Unix socket.

The data directory is /volume1/@database/mariadb10/ and the binaries live under /usr/local/mariadb10/bin/. The command-line client is therefore:

/usr/local/mariadb10/bin/mysql -uroot -p

If you have both packages installed, the MariaDB 10 package offers a one-time migration from MariaDB 5 in its settings dialog. Run it before pointing any application at the new server.

Then phpMyAdmin

Package Center → phpMyAdmin → Install. It deploys into /volume1/web/phpMyAdmin and becomes reachable at http://nas-ip/phpMyAdmin/. Log in as root with the MariaDB password.

By default it connects to MariaDB 5 on 3306. If only MariaDB 10 is installed the login fails with #2002 - No such file or directory. Fix the server definition in the phpMyAdmin configuration:

sudo vi /volume1/web/phpMyAdmin/config.inc.php

Find the $cfg['Servers'][$i] block and set it to the MariaDB 10 socket:

$cfg['Servers'][$i]['host'] = 'localhost';
$cfg['Servers'][$i]['socket'] = '/run/mysqld/mysqld10.sock';
$cfg['Servers'][$i]['connect_type'] = 'socket';
$cfg['Servers'][$i]['auth_type'] = 'cookie';
$cfg['Servers'][$i]['AllowNoPassword'] = false;

Keeping AllowNoPassword false is important; the default MariaDB 5 install has an empty root password on some older boxes.

Also set a blowfish_secret of 32 random characters if the file has a placeholder, otherwise phpMyAdmin nags on every login about cookie encryption.

An application user

Log in to phpMyAdmin as root and go to User accounts → Add user account:

CREATE DATABASE appdb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'strong-password';
GRANT ALL PRIVILEGES ON appdb.* TO 'appuser'@'localhost';
FLUSH PRIVILEGES;

The SQL tab runs exactly this. Applications on the NAS then connect as appuser over the socket, and root is never used by anything but you.

Locking it down

Web Station puts phpMyAdmin under the default site, so it inherits whatever exposure that site has. I use three layers:

  1. DSM firewall: Control Panel → Security → Firewall → Edit Rules. Make sure ports 80 and 443 are only allowed from the LAN, or not at all if Web Station is internal.
  2. Move it off the default site. Web Station → Virtual Host → Create a name-based host dbadmin.nas.local on HTTPS with document root web/phpMyAdmin, then delete or block the /phpMyAdmin alias by removing it from the default document root. Since it is now a virtual host, it needs to be reached by that name, which outsiders do not know.
  3. Restrict by address in phpMyAdmin itself. In config.inc.php:
$cfg['Servers'][$i]['AllowDeny']['order'] = 'deny,allow';
$cfg['Servers'][$i]['AllowDeny']['rules'] = array(
    'deny root from all',
    'allow root from 192.168.1.0/24',
    'allow % from 192.168.1.0/24',
);

This denies every login from outside the LAN even if someone reaches the page.

Checking it

  • Open https://dbadmin.nas.local/ from the LAN and log in as appuser. The left pane should show only appdb and information_schema.
  • Home page → "Server: Localhost via UNIX socket" and "Server version: 10.0.x-MariaDB" or 10.1.x, depending on the package build. If it says 5.5.x you are still connected to the old package.
  • From a phone on mobile data, the hostname should not resolve, and the NAS IP on 443 should be refused by the firewall.

From the shell, confirm the server version and the socket path phpMyAdmin is using:

/usr/local/mariadb10/bin/mysql -uroot -p -e "SELECT VERSION(); SHOW VARIABLES LIKE 'socket';"

Errors you'll probably see

  • #1045 - Access denied for user 'root'@'localhost' (using password: YES) right after installing MariaDB 10: the password typed in the package wizard did not stick, which happens when the wizard is closed too fast. Reset it from the MariaDB 10 package window, which has a "Reset Password" button that requires the DSM administrator password.
  • #2002 - Connection refused with host set to 127.0.0.1: TCP/IP is disabled in the package. Either enable it or, better, switch back to the socket as shown above.
  • phpMyAdmin displays "The configuration file now needs a secret passphrase (blowfish_secret)". Add a 32-character string to $cfg['blowfish_secret'] in config.inc.php.
  • "The mbstring extension is missing" after a PHP package update. Enable mbstring in Web Station → PHP Settings for the profile that serves phpMyAdmin.

One habit worth having: a Package Center update of phpMyAdmin can overwrite config.inc.php, so I keep a copy of my edits outside /volume1/web. I found that out after an update silently put the login page back to defaults.