MySQL is an extremely popular, open-source relational database management system. Oracle acquired the RDBMS some years ago and releases value-added editions and tools to turn a profit. MySQL is fairly ubiquitous in the web hosting market, most PHP-driven applications having been designed primarily or exclusively for it. For this reason it is a good choice when implementing your mass virtual hosting platform as it is what the vast majority of users will expect.
phpMyAdmin is a fantastic PHP-based web application that allows for the easy management of MySQL databases and features through a user-friendly interface. It shares similar ubiquity in the hosting market, having virtually no competitors save custom solutions. Accordingly, phpMyAdmin has evolved to become very ISP/Webhost friendly in that many features important to security and integration are highly configurable.
As noted in an addendum to Part One of this series on Mass Virtual Hosting: Database-backed User Accounts and Authentication I had difficulties running mysqld on machines configured to use libnss-mysql, therefore for the purposes of this article we shall assume that we are working with a stand-alone SQL server and a stand-alone web server. I recommend this configuration whenever resources permit regardless of your choice in authentication schemes, as partitioning important services helps keep either one from overloading the other under stressful or unusual circumstances. As we’ll cover in the next part of this series, mysql-proxy can be used to give users the illusion of mysqld running on localhost while the actual server can be located (or relocated) anywhere transparently. This has the additional benefits of preventing information about the private network’s topology from leaking and opens the potential for load-balancing and most importantly: automatic fail over.
MySQL generally keeps its configuration files in the /etc/my directory. This is the default MySQL configuration file that comes on a Gentoo installation:
# cat /etc/mysql/my.cnf
# /etc/mysql/my.cnf: The global mysql configuration file.
# $Header: /var/cvsroot/gentoo-x86/dev-db/mysql/files/my.cnf-4.1,v 1.4 2008/11/14 02:16:25 robbat2 Exp $
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=utf8
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
# use [safe_mysqld] with mysql-3
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
# add a section [mysqld-4.1] or [mysqld-5.0] for specific configurations
[mysqld]
character-set-server = utf8
default-character-set = utf8
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
language = /usr/share/mysql/english
# security:
# using "localhost" in connects uses sockets by default
# skip-networking
bind-address = 0.0.0.0
log-bin
server-id = 1
# point the following paths to different dedicated disks
tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# you need the debug USE flag enabled to use the following directives,
# if needed, uncomment them, start the server and issue
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's happening in your server ;)
#log = /tmp/mysqld.sql
#gdb
#debug = d:t:i:o,/tmp/mysqld.trace
#one-thread
# uncomment the following directives if you are using BDB tables
#bdb_cache_size = 4M
#bdb_max_lock = 10000
# the following is the InnoDB configuration
# if you wish to disable innodb instead
# uncomment just the next line
#skip-innodb
#
# the rest of the innodb config follows:
# don't eat too much memory, we're trying to be safe on 64Mb boxes
# you might want to bump this up a bit on boxes with more RAM
innodb_buffer_pool_size = 16M
# this is the default, increase it if you have lots of tables
innodb_additional_mem_pool_size = 2M
#
# i'd like to use /var/lib/mysql/innodb, but that is seen as a database :-(
# and upstream wants things to be under /var/lib/mysql/, so that's the route
# we have to take for the moment
#innodb_data_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
#innodb_log_group_home_dir = /var/lib/mysql/
# you may wish to change this size to be more suitable for your system
# the max is there to avoid run-away growth on your machine
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
# we keep this at around 25% of of innodb_buffer_pool_size
# sensible values range from 1MB to (1/innodb_log_files_in_group*innodb_buffer_pool_size)
innodb_log_file_size = 5M
# this is the default, increase it if you have very large transactions going on
innodb_log_buffer_size = 8M
# this is the default and won't hurt you
# you shouldn't need to tweak it
set-variable = innodb_log_files_in_group=2
# see the innodb config docs, the other options are not always safe
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_file_per_table
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
# uncomment the next directive if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
A lot of tweaking can be done here but it exceeds the scope of this article and shall be the topic for another. Note I have set the bind-address variable to 0.0.0.0, this will allow MySQL to bind to any configured IP address on its host. The default is localhost, which restricts access to sockets connections. On a host with multiple IP addresses it may be mildy beneficial to security to bind mysqld to a specific IP address. I strongly recommend only allowing access to the server from the private network, the less services – particularly critical services that contain valuable data like databases – exposed to the wild the better in all cases. A thoughtfully configured phpMyAdmin installation is truly enough for any administrative task, there is most likely no good reason to give your clients remote MySQL access at all. If you do, make absolutely certain to take advantage of MySQL’s SSL capabilities and enforce some kind of brute force protection, such as can be configured using fail2ban.
Since phpMyAdmin will be a conduit for MySQL passwords, site user tables and other sensitive data, it is absolutely imperative that it be served over SSL/TLS. Install it somewhere that makes sense, perhaps an SSL-protected subdomain like sql. or phpmyadmin.yourmanagementdomain.com. If you only want to shell out for a regular single-host certificate a subdirectory of yourmanagementdomain.com like /sql/ will do. Download and extract the latest version of phpMyAdmin to the desired location and create then chmod 777 the temp/ directory. Navigate to the target location and append the /setup/ path, i.e.: http://sql.yourmanagementdomain.com/setup
If you get a blank page, go into your php.ini and enable error reporting. If PHP spits out an error shove it into google, I once had an obscure problem with the filter_var() function because I was missing filter support in my USE flags when it was compiled. Assuming all went well you can now go through the step-by-step configuration wizard; pay attention to detail and make sure the options are suited to your particular environment.
Consider if you really need SSL support when connecting to the server from phpMyAdmin; encryption consumes resources and increases latency but offers protection in environments prone to packet sniffing. In a switched and bridged Layer 2 network of real servers or virtual machines chances are good that if an intruder can sniff your SQL packets they already have your web server or sql server compromised and either is just as bad as the other. Or, unique to virtual machines, they have access to one or more privileged guests -in which case your problems are much, much bigger.
Compression is also probably more resources than it’s worth on a local network, but can be quite beneficial when the SQL server is in a remote location relative to the web server. Ideally we’d like Single-Sign-On with phpMyAdmin so users can access it by loging in to our custom web management software and not need to be prompted for their SQL password. We’re going to get to that later in the article, first we want to be able to log in and lock down features so we’ll go with cookie-based authentication rather than the signon option. Cookie-based authentication used the username and password of the user actually stored in MySQL, and we should already have our root account configured.
Under the Security tab there is a Force SSL Connection option. You are strongly encouraged to enable it, it will automatically redirect anyone attempting to access phpMyAdmin from an unsecured connection to a secure one. phpMyAdmin can use its own database to provide additional functionality; create a database and a user for phpMyAdmin in MySQL:
create database phpmyadmin;
grant all privileges on phpmyadmin.* to phpmyadmin@'%' identified by 'pass';
The table structure is located in /scripts/create_tables.sql, install it from the web server like so:
# mysql -hsql-server -uphpmyadmin -ppass phpmyadmin < scripts/create_tables.sql
Once you’re finished with the wizard save the file and copy config.inc.php from the /config/ directory. You may now delete the /config/ and /setup/ directories.
I like to remove some of the tabs phpMyAdmin comes with since they can pose a security risk or confuse users with needless information. Edit /libraries/server_links.php and comment-out the arrays for the tabs you would like to remove. Bear in mind that these changes will not stick after an upgrade.
We don’t want to allow root to log in from anywhere but the SQL server itself so create a database and user to test logging in with:
create database testdb;
grant all privileges on testdb.* to test@'%' identified by 'testpass';
Make sure your test account can be accessed from the address of the server offering phpMyAdmin. Use the SQL wildcard (%) character to allow access from any location. Now try logging in through phpMyAdmin with the test account.
phpMyAdmin comes with a sample single-sign-on script example in its scripts/ directory:
<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
* Single signon for phpMyAdmin
*
* This is just example how to use single signon with phpMyAdmin, it is
* not intended to be perfect code and look, only shows how you can
* integrate this functionality in your application.
*
* @version $Id$
* @package phpMyAdmin
* @subpackage Example
*/
/* Was data posted? */
if (isset($_POST['user'])) {
/* Need to have cookie visible from parent directory */
session_set_cookie_params(0, '/', '', 0);
/* Create signon session */
$session_name = 'SignonSession';
session_name($session_name);
session_start();
/* Store there credentials */
$_SESSION['PMA_single_signon_user'] = $_POST['user'];
$_SESSION['PMA_single_signon_password'] = $_POST['password'];
$_SESSION['PMA_single_signon_host'] = $_POST['host'];
$_SESSION['PMA_single_signon_port'] = $_POST['port'];
$id = session_id();
/* Close that session */
session_write_close();
/* Redirect to phpMyAdmin (should use absolute URL here!) */
header('Location: ../index.php');
} else {
/* Show simple form */
header('Content-Type: text/html; charset=utf-8');
echo '<?xml version="1.0" encoding="utf-8"?>' . "\n";
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en" dir="ltr">
<head>
<link rel="icon" href="../favicon.ico" type="image/x-icon" />
<link rel="shortcut icon" href="../favicon.ico" type="image/x-icon" />
<title>phpMyAdmin single signon example</title>
</head>
<body>
<form action="signon.php" method="post">
Username: <input type="text" name="user" /><br />
Password: <input type="password" name="password" /><br />
Host: (will use the one from config.inc.php by default) <input type="text" name="host" /><br />
Port: (will use the one from config.inc.php by default) <input type="text" name="port" /><br />
<input type="submit" />
</form>
</body>
</html>
<?php
}
?>
As you can see you will need to have your users’ passwords stored somewhere in cleartext. By popping the session code into your administration front-end (somewhere before headers are sent). I suggest that a user must click a button or take some other obvious action to activate the session rather than starting it as soon as they log into your interface, there is no sense in bestowing access where none is needed.
Open the phpMyAdmin config file and edit it to reflect:
$cfg['Servers'][$i]['auth_type'] = 'signon';
$cfg['Servers'][$i]['SignonSession'] = "SignonSession";
$cfg['Servers'][$i]['SignonURL'] = "https://yourdomain.com/login-page";
$cfg['Servers'][$i]['LogoutURL'] = "https://yourdomain.com/logout-page";
<?php
/* vim: set expandtab sw=4 ts=4 sts=4: */
/**
* Single signon for phpMyAdmin
*
* This is just example how to use single signon with phpMyAdmin, it is
* not intended to be perfect code and look, only shows how you can
* integrate this functionality in your application.
*
* @version $Id$
* @package phpMyAdmin
* @subpackage Example
*/
/* Was data posted? */
if (isset($_POST['user'])) {
/* Need to have cookie visible from parent directory */
session_set_cookie_params(0, ‘/’, ”, 0);
/* Create signon session */
$session_name = ‘SignonSession’;
session_name($session_name);
session_start();
/* Store there credentials */
$_SESSION['PMA_single_signon_user'] = $_POST['user'];
$_SESSION['PMA_single_signon_password'] = $_POST['password'];
$_SESSION['PMA_single_signon_host'] = $_POST['host'];
$_SESSION['PMA_single_signon_port'] = $_POST['port'];
$id = session_id();
/* Close that session */
session_write_close();
/* Redirect to phpMyAdmin (should use absolute URL here!) */
header(‘Location: ../index.php’);
} else {
/* Show simple form */
header(‘Content-Type: text/html; charset=utf-8′);
echo ‘<?xml version=”1.0″ encoding=”utf-8″?>’ . “\n”;
?>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN”
“http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html xmlns=”http://www.w3.org/1999/xhtml” xml:lang=”en” lang=”en” dir=”ltr”>
<head>
<link rel=”icon” href=”../favicon.ico” type=”image/x-icon” />
<link rel=”shortcut icon” href=”../favicon.ico” type=”image/x-icon” />
<title>phpMyAdmin single signon example</title>
</head>
<body>
<form action=”signon.php” method=”post”>
Username: <input type=”text” name=”user” /><br />
Password: <input type=”password” name=”password” /><br />
Host: (will use the one from config.inc.php by default) <input type=”text” name=”host” /><br />
Port: (will use the one from config.inc.php by default) <input type=”text” name=”port” /><br />
<input type=”submit” />
</form>
</body>
</html>
<?php
}
?>
