Web served
- Web served, part 3: Bolting on PHP with PHP-FPM
- Web served, part 2: Securing things with SSL/TLS
- How to set up a safe and secure Web server
For new readers just joining us, this is the fourth in a series of articles on getting your hands dirty by setting up a personal Web server and some popular Web applications. We've chosen a Linux server and Nginx as our operating system and Web server, respectively; we've given it the capability to serve encrypted pages; and we've added the capability to serve PHP content via PHP-FPM. Most popular Web apps, though, require a database to store some or all of their content, and so the next step is to get one spun up.
But which database? There are many, and every single one of them has its advantages and disadvantages. Ultimately we're going to go with the MySQL-compatible replacement MariaDB, but understanding why we're selecting this is important.
To SQL or NoSQL, that is the question
In most cases these days, when someone says "database" they're talking about a relational database, which is a collection of different sets of data, organized into tables. An individual record in a database is stored as a row in a table of similar records—for example, a table in a business's database might contain all of that business's customers, with each record consisting of the customer's first name, last name, and a customer identification number. Another table in this database might contain the states where the customers live, with each row consisting of a customer's ID number and the state associated with it. A third table might contain all the items every customer has ordered in the past, with each record consisting of a unique order number, the ID of the customer who ordered it, and the date of the order. In each example, the rows of the table are the records, and the columns of the table are the fields each record is made of.
A relational database is called such because each table contains like items—items with a relationship to each other. Each record in each table must contain some way of uniquely identifying it, too—in our customer name table, there might be several different customers named "John Smith," but they'll each have a unique customer ID number. This unique thing is called the primary key and every table has a column designated as such. (Database admins will understand that I'm greatly simplifying, because otherwise we will be here all day!)
Relational databases are mostly managed with a programming language called SQL, for "Structured Query Language" (and there's a never-ending holy war over whether "SQL" is pronounced as "sequel" or as three letters, "ess-queue-ell"—I favor the former, but plenty of folks prefer the latter). SQL contains language elements which enable you to manipulate a relational database's structure and contents, and to find and bring together the things you're looking for in a database, which may be scattered through many different tables. For example, in the made-up database of the previous few paragraphs, a SQL query could be written to find and display the last names of all customers in Oregon who've ordered something within the past month, even though that information is spread between three different tables.
Relational databases are a fairly old concept, having been around for more than 40 years, and they get the job done. However, they're not the only game in town. There's a broad class of databases referred to as NoSQL databases, which eschew tables and primary keys and Structured Query Language, instead using alternate kinds of storage, organization, or language. The NoSQL landscape is vast, but one of the more common NoSQL databases is MongoDB, which has some SQL-like properties but which stores its data in JSON format. Another is Redis, which is a very fast database (it lives entirely in RAM, though it backs itself up to disk periodically) that stores its contents as key-value pairs instead of in tables.
NoSQL is exciting, especially to developers tired of dealing with the limitations of traditional databases. But we run into a fundamental problem when we look at it: none of the popular Web apps we're going to talk about installing work well with a single NoSQL database system—that is, there's no single NoSQL database that we can use for all the popular Web apps we want to spin up and try. If you're a developer and you want to build something on Redis or MongoDB or any of the other interesting NoSQL database systems, then good for you and have fun exploring, but we must part ways here. For the sake of compatibility, we're going to go with a SQL database for our setup.
SQL it is, but which?
We've settled on SQL, but which SQL database should we install? There are many potential choices, and in a perfect world I'd like to pick PostgreSQL. It's fast, relatively secure, and easy to manage; unfortunately, it's also more complicated to make it work with everything we might want to install—for instance, WordPress doesn't work out of the box with PostgreSQL, requiring a plug-in to make it compatible. That plug-in then might break other WordPress plugins that require direct database access, a future WordPress update might break the PostgreSQL plugin. Some forum applications (like phpBB) support PostgreSQL, but others (like Vanilla) don't. MediaWiki, the 800-pound wiki application, has volunteer-maintained support for PostgreSQL, but it isn't official.
If you want to install PostgreSQL, you should. It's got its advantages, not the least of which is a bit of security-through-obscurity (it's popular, but nowhere near as pervasive as MySQL). In order to maximize compatibility, we're going to go with the popular choice and pick MySQL.
Actually, that's not quite true. We're not going to pick MySQL—we're going to go with a binary-compatible replacement for MySQL called MariaDB.
MySQL versus MariaDB
MySQL is far and away the most popular relational database for running web applications, and it pairs nicely with our choice of PHP as our scripting language. It's an open source application, currently owned by Oracle, and freely available. It's supported by just about any web application you'd want to run, and it's not terribly difficult to install.
And we're not going to use it, either. We're going to use an alternative called MariaDB.
There are several reasons for doing this. MariaDB's lead developer is a fellow named Michael Widenius, who is one of the original developers of MySQL. "Monty", as he is known, sold MySQL to Sun (who in turn passed it to Oracle when Oracle purchased Sun), but forked MariaDB off of the MySQL codebase and continued development as a separate project. MariaDB remains free and independent of any potential license changes from Oracle.
More tangibly, MariaDB has a great number of performance improvements and bug-fixes over vanilla MySQL. At the same time, it remains completely compatible with MySQL, to the point that applications don't know the difference between the two. MariaDB looks and acts exactly like MySQL (and it should, being a direct fork), even down to using the same binary names and same installation locations for all the files.
So it's faster than MySQL and has fewer bugs, but still acts exactly like MySQL as far as applications are concerned. What's not to like? Let's get it!
Installing MariaDB
The MariaDB folks have their own set of repositories for different Linux distros. If you've been following along with the guide then you're using Ubuntu Server 12.04, and so we need to tell Ubuntu Server where the correct MariaDB repository lives and then tell it to install MariaDB from there.
This requires several steps, but they only need to be done once. After this, the repository will be added and MariaDB can be updated with aptitude update and aptitude upgrade just like any other installed application.
The first step is that we need to add the developer's public key to our local keystore, so that our computer will be able to determine the authenticity of the MariaDB repository and its contents. This is for our protection, so that we know for certain that the repository is owned by who it's supposed to be owned by. To do this, pop open a terminal window and type the following:
sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
Once the key has been imported, we can actually add the MariaDB repository to our sources list. Open the file /etc/apt/sources.list with the text editor of your choice, and add the following lines:
# MariaDB 5.5 repository list - created 2012-12-07 13:36 UTC # http://downloads.mariadb.org/mariadb/repositories/ deb http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu oneiric main deb-src http://ftp.osuosl.org/pub/mariadb/repo/5.5/ubuntu oneiric main
If you receive an error about sources.list being a read-only file, make sure to edit it with root privilege, either by running your text editor as root (sudo vim /etc/apt/sources.list) or by launching a root shell and working in there.
After the repository has been added, refresh your sources list with a quick aptitude update, and then run the following command in order to install MariaDB and the PHP MySQL connector:
sudo aptitude install mariadb-server php5-mysql
During the installation of MariaDB, you will be prompted to select a password for the MariaDB root user. The database root user will have all privileges on all of your databases, and so it's recommended that you pick a suitably complex password to keep the account protected.
After this, the installation process will complete and you'll be returned to your prompt.
Listing image by Lee Hutchinson / stock.xchng
A bit of configuration
Just as with PHP in the previous example, by default MariaDB prefers to talk to other applications via a TCP port. This ensures greater compatibility, but it's not as fast as direct communication using Unix sockets. In fact, we want to go ahead and disable network communication all together for our database—we'd need it if we were running the database on a separate box from the Web server, but we're not. Leaving it active is an unnecessary security risk. We want to ensure that MariaDB is listening only on our local Unix socket.
First, pop open /etc/mysql/my.cnf as root. Locate the "Basic Settings" section and add the following line at the bottom:
skip-networking
Save and exit, then restart MariaDB with sudo /etc/init.d/mysql restart.
Next, we need to tell PHP to use MariaDB's Unix socket for communication. Edit /etc/php5/fpm/php.ini as root and locate the mysql.default_socket setting. Change it as follows:
mysql.default_socket = /var/run/mysqld/mysqld.sock
Save and exit, then restart PHP with sudo /etc/init.d/php5-fpm restart.
To ensure that PHP is actually connecting to MariaDB, we can use the PHP info page we configured in the previous article, at http://yourwebserver/phpinfo.php. If it's working correctly, you should see a couple of new sections, including "mysql" and "mysqli", which make reference to the MariaDB Unix socket:
Tuning
Whatever Web applications you wind up needing to run, you'll need to alter MariaDB's settings in order to run them more efficiently. However, until you actually know what apps you're going to run, there's no way to really predict what you'll need to tweak. There are plenty of MySQL tuning guides on the Web (which are applicable to MariaDB, because it uses the same settings and the same configuration file layout), but until we've got an idea of what we're doing, it's impossible to say how helpful they'll be.
There are scripts that you can run to give recommendations, though. One is the MySQL Performance Tuning Primer Script, which has some incompatibilities with MariaDB but which can provide valuable information—you can download it and run it directly from your home directory. Another is MySQLTuner-perl, which as the name suggests is a Perl script that you can run for additional tuning advice.
Running either of these right now won't yield enough advice to be really useful, since they both gather recommendations by looking at MySQL performance counters and stats, judging them against some basic rules-of-thumb. However, they're good resources to have available.
A word on running scripts against your database: be sure that you're downloading what you think you're downloading. By running scripts directly against your database locally, you're potentially bypassing your system's protections and letting the script do whatever it wants, with root or system maintenance account privilege. For performance tuning, this is fine, but be cognizant of the source of any scripts you choose to run.
Hardening tips
MySQL is, frankly, a common attack vector. The database's popularity ensures that it has more than its share of hacks targeted directly at it. Most MySQL vulnerabilities (and by extension most MariaDB vulnerabilities) are quickly patched, but it's impossible to protect against everything. Adding MySQL (or any database, really) to your system means potentially opening another vector for attack, though risk can be minimized through some basic common sense.
In its default configuration, MariaDB is not insecure; there are no gaping vulnerabilities or unpatched holes. You can potentially improve on its default state by renaming the "root" account to something non-obvious, since "root" will attract the most unsavory attention. This can be done with some simple command line magic. First, log into the MariaDB Monitor using this command:
mysql -u root -p
You'll be prompted for the root password you set during MariaDB's setup.
Once you're authenticated and sitting at the MariaDB [(none)]> prompt, issue the following command to rename the root user:
rename user root@localhost to bob@localhost;
Here we've renamed "root" to "bob", though you can obviously use whatever you want.
One tip that does come up often, and which we won't be using, is to disable the use of the LOAD DATA LOCAL INFILE SQL command. Though disabling this command's usage will indeed stop some attacks, some Web applications require this command in order to function. If you know for certain you won't be using anything that requires it, you can disable it in the /etc/mysql/my.cnf file.
The rest of the most common MariaDB security tips read like a list of standard sysadmin guidelines. Keep current with updates, never assign more privilege than necessary to any account, and remove obsolete accounts when you're done with them. As we get to installing and configuring Web applications in the subsequent articles, we'll make sure to hew close to these guidelines.
Admin tools—phpMyAdmin or other
We could stop at this point, since we have a functional database subsystem and the ability to manage it via the command line. However, there are some other management options available, and MariaDB (and its contemporaries, too!) are complex enough that a graphical interface might be helpful to some folks.
The most popular graphical admin tool for MySQL and its forks is phpMyAdmin. It's incredibly powerful and has a huge and rich feature set, but it has a significant potential downside: its overwhelming popularity makes it a huge target for attacks. If you use it, or any other graphical admin tool, it's recommended that you take significant precautions—including forcing it only talk over HTTPS and preventing it from being accessed outside your LAN. Optionally, you might also consider locking it behind Web server-based authentication to add an additional user name/password layer of protection.
There are alternatives to phpMyAdmin, including the light and quick SQL Buddy, which I prefer to use. However, the alternatives are vulnerable to most of the same kinds of misconfiguration problems as phpMyAdmin, and ultimately any window into your database that can be accessed via a Web browser brings with it inherent compromises in security.
Installing SQL Buddy
That being said, we're going to install and configure SQL Buddy because we don't really need to huge feature list phpMyAdmin brings with it.
There's no package for SQL Buddy, but that's okay—the application is prêt-à-porter. We simply need to download it, unzip it, and then configure Nginx to serve it. Technically, we don't even need to configure Nginx, since SQL Buddy is a PHP application and Nginx will already serve PHP applications, but we can improve on our default configuration and make it more secure.
First, install unzip, so that we can use it to unzip SQL Buddy:
sudo aptitude install unzip
Then, navigate to the Nginx web root and actually download SQL Buddy:
cd /usr/share/nginx/html/ sudo wget --content-disposition https://github.com/calvinlough/sqlbuddy/zipball/master
We're using wget above to follow the download link on the SQL Buddy home page, which includes a redirect to the actual file's location (hence the --content-disposition flag, which enables wget to name the downloaded file correctly). Once the file is downloaded, unzip it in place:
sudo unzip calvinlough-sqlbuddy-207c6fc.zip
This will produce a directory with the same name as the zip file. We want to rename that directory, then remove the zip file:
sudo mv calvinlough-sqlbuddy-207c6fc sqlbuddy sudo rm calvinlough-sqlbuddy-207c6fc.zip
Finally, we want to change the ownership of the directory to the Nginx www-data user. This ensure that Nginx has the access it needs, and it's a good idea to keep your Web root and its contents owned by the Web server's account anyway.
sudo chown -R www-data:www-data /usr/share/nginx/html/sqlbuddy
(As an aside, be careful with chown -R, which changes ownership on the directory you specify and on all its files and subdirectories. A typo can leave you accidentally changing the ownership on things you didn't intend to change! Don't ask me how I know this. I...uh, read about it once.)
Configuring Nginx to serve SQL Buddy
We need to define an Nginx location for SQL Buddy; also, while we're in the www virtual host file, we're going to make sure that we only serve out SQL Buddy's pages via HTTPS. Since we'll have to authenticate to SQL Buddy using a MariaDB user name and password, forcing SQL Buddy to HTTPS means we won't be sharing that user name and password in the clear!
To make this happen, we're going to add a bit of code in the regular, non-HTTPS server part of our www virtual host file, which will look for HTTP requests to the SQL Buddy directory and flip them to HTTPS. Open the www file (which, remember, is located at /etc/nginx/sites-available/www and add the following in the top server section, below your existing locations:
location ^~ /sqlbuddy/ { rewrite ^ https://$server_name$request_uri? permanent; } This location will match all non-HTTP requests to /sqlbuddy/ and redirect them to HTTPS, keeping the rest of the URL the same.
Now we'll add another location to the HTTPS server section. We already have one for executing PHP everywhere, but we're going to remove that at some point; having a distinct location defined for SQL Buddy and its PHP files will ensure that it functions as intended no matter what else is going on in other locations.
Add the following location to the HTTPS server block:
location ~ /sqlbuddy/.*\.php$ { allow 192.168.1.0/24; allow 127.0.0.1; deny all; try_files $uri =404; include fastcgi_params; fastcgi_pass php5-fpm-sock; fastcgi_param HTTPS on; fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name; fastcgi_intercept_errors on; } Replace the first allow netblock with your own LAN's IP address scope. Also, if you don't have your Web server configured for SSL/TLS, then you can ignore the bit about the HTTPS redirect and just put the above block in your regular server section—just make sure to remove the fastcgi_param HTTPS on; line.
The last thing we need to do is adjust the Web server so that it knows to seek out a PHP index file in each directory if it doesn't find a regular HTML index file. Most PHP applications have index.php files in their directories to catch your Web server's attention, and we want to make sure Nginx knows to look for them. To do this, modify the index directive in the HTTPS server section as follows, adding index.php onto the end:
index index.html index.htm index.php;
Then, tell Nginx to reload its config files to make all of your changes live:
sudo /etc/init.d/nginx reload
Finally, you should be able to navigate to http://yourserver/sqlbuddy and see the following:
Feed in your root username and password, and you'll be sitting at the SQL Buddy main menu, with the default list of databases at the left. We haven't added anything yet, so there's not much to see here. We'll be coming back in here throughout subsequent articles to build databases, add users, and set permissions.
All right! What's next?
We've got a Web server, some PHP, and a database. What ever shall we do with it? Run a forum? Start a blog? Build a wiki?
Stay tuned—we're going to do all three. Next time, we dive head-first into applications, starting with self-hosted WordPress.
No comments:
Post a Comment