The dynamic, database-driven, secure Web site… It’s a lot to ask for and can cost a lot of money. However, you can create such a Web site inexpensively using a specific set of open source utilities. Here, I’ll explain which open source utilities you will need, how to install and configure them, and how to set things up.

The list
There are six components you will need to create your Web site. Table A describes each component of this arsenal.
Table A

Table A:
The pieces of the open-source, dynamic, database-driven Web site
Component Where to get it Version Description
Apache www.apache.org 1.3.22 This is the Web server component, generally considered required material for a Web site.
PHP www.php4.net 4.1.1 PHP is a server-side scripting language that sits between a database and a Web server and allows the Web server to talk back and forth to the database.
PostgreSQL www.postgresql.org 7.1.3 PostgreSQL is an open source database with advanced functionality such as referential integrity and views.
OpenSSL www.openssl.org 0.9.6c Open Source Implementation of the SSL protocol
ModSSL www.modssl.org 2.8.5-1.3.22 This provides strong cryptography for an Apache 1.3.22 Web server and OpenSSL.
RSARef http://packages.debian.org/
stable/non-us/rsaref2.html 		2 This package was required for U.S. users until September of 2000. I am including it in this installation because of its widespread use.

I will explain how to install and configure a basic installation of Apache, PHP, PostgreSQL, and SSL (specifically using OpenSSL).

Some preliminaries
For this article, I will explain how to install this suite of software on a Red Hat Linux 7.1 server. Before the installation, you should download all of the files listed above and put them in your home directory (mine would be /home/slowe/pg). Then, expand these packages into directories under /usr/local, which will make it easier to reference them during particular steps in the installation process, because some packages have dependencies on others. Also, log in as root for the installation to prevent permission problems.

What’s first?
Since the database product has no dependencies connected to the other modules, install it first. Follow the steps listed in Table B.
Table B


Installing the database
Command Description
/usr/sbin/groupadd database Add a ‘database’ group to the server.
/usr/sbin/useradd -g database postgres -d /usr/local/postgres Add the ‘postgres’ user.
passwd postgres Change the password for the ‘postgres’ user.
cd /usr/local Switch to the directory you’ll use to expand the PostgreSQL distribution.
gunzip -dc /home/slowe/pg/
postgresql-7.1.3.tar.gz | tar xvf – 		Expand the PostgreSQL source distribution.
cd postgresql-7.1.3 Switch to the directory created by the source distribution.
./configure -prefix=/
usr/local/postgres 		This sets up the PostgreSQL configuration environment and sets the installation directory to /usr/local/postgres.
make This compiles the PostgreSQL distribution based on the criteria in the last step.
make install This command installs the PostgreSQL software.
mkdir /usr/local/
postgres/data 		This creates a directory that will hold all databases managed by PostgreSQL.
pico /etc/profile Edit the /etc/profile file and add the following lines below the last line that starts with export:

LD_LIBRARY_PATH=/usr/local/postgres/lib
PATH=$PATH:/usr/local/postgres/bin
MANPATH=$MANPATH:/usr/local/pgsql/man
PGDATA=/usr/local/postgres/data

export PATH LD_LIBRARY_PATH MANPATH PGDATA

These are environment variables used by PostgreSQL.

source /etc/profile This re-reads the environment variable information set up in /etc/profile.
chown postgres.database -R postgres This gives the Postgres user ownership of the PostgreSQL installation. Since PostgreSQL cannot be run as root, this is an important step.
su – postgres Log in as the Postgres user, because the default template database cannot be created as root.
/usr/local/postgres/
bin/initdb -D /usr/
local/postgres/
data 		In a default PostgreSQL installation, no databases are created automatically. However, upon connection to PostgreSQL, a database is required, which creates a sort of catch-22. This script creates the template database that is used to create all other PostgreSQL databases. The -D parameter indicates the path to the PostgreSQL data directory.
/usr/local/postgres/
bin/postmaster -i -D/
usr/local/postgres/
data & 		This starts the PostgreSQL database server in the background using /usr/local/postgres/data as the database directory, which allows connections from the Internet.
exit Be the root user again. PostgreSQL will stay running.

You’ll then have a complete, working PostgreSQL database server running as the Postgres user on port 5432 on your Linux server. I’ll revisit this installation a little later in this article.

How about a scripting language?
For this particular PHP installation, you’ll need access to the Apache source distribution, because the PHP configuration will use it to build PHP as an Apache module.

Table C lists the steps for installing PHP on your machine.
Table C


Installing PHP
Command Description
cd /usr/local Switch to the /usr/local directory.
gunzip -dc /home/slowe/pg/apache_1.3.22.tar.gz | tar xvf – Expand the Apache source distribution.
cd /usr/local/apache_1.3.22 Switch to the Apache source directory.
./configure –prefix=/usr/local/apache Specify the very basic Apache installation parameters.
cd /usr/local Switch back to the /usr/local directory.
gunzip -dc /home/slowe/pg/php-4.1.1.tar.gz | tar xvf – Expand the PHP distribution.
cd php-4.1.1 Switch to the PHP directory.
./configure –with-pgsql=/usr/local/postgres –with-xml –with-apache=/usr/local/apache_1.3.22 –enable-track-vars –enable-magic-quotes –enable-trans-sid Compile PHP 4.11 with PostgreSQL support. Also include support for XML, session tracking variables, transparent session ID propagation, and magic quotes, all of which are extremely valuable during PHP development.
make Compile PHP.
make install Install PHP.
cp php.ini-dist /usr/local/lib/php.ini Copy the sample Php.ini file from the distribution to the appropriate place on your server.

Later, I’ll put together a sample PHP script to test the PHP installation.

Now for the SSL components
This is where things start to get a bit convoluted, so pay careful attention to the order of the steps below.

OpenSSL
OpenSSL provides strong cryptography services, while the component, Mod_SSL, provides an interface between Apache and OpenSSL. Table D lists the steps for installing OpenSSL.
Table D


Installing OpenSSL
Command Description
cd /usr/local Switch to /usr/local.
gunzip -dc /home/slowe/pg/rsaref2_19940415.orig.tar.gz | tar xvf – Expand the rsaref distribution.
cp -rp install/unix local Copy the UNIX makefile to a directory named local.
cd local Switch to the local directory.
make Build rsaref.
cp rsaref.a librsaref.a Make a copy of the rsaref.a library using a different filename.
cd /usr/local Switch back to this directory.
gunzip -dc /home/slowe/pg/openssl-0.9.6c.tar.gz | tar xvf – Expand the OpenSSL distribution.
cd /usr/local/openssl-0.9.6c Switch to the OpenSSL directory.
./config –prefix=/usr/local/ssl -L/usr/local/
rsaref2-19940415.orig/local/rsaref -fPIC 		This command builds the OpenSSL distribution using the rsaref directory that was built before as the location to find rsaref.a.
make Compile it all.
make install Install it.

Compiling OpenSSL is actually composed of compiling rsaref and OpenSSL and then installing them together.

Mod_SSL
To support SSL communication, Apache needs to make use of Mod_SSL. Mod_SSL has one requirement for U.S. citizens–the use of the rsaref library mentioned earlier in this document. Table E describes the steps to expand the rsaref distribution.
Table E


Expanding the rsaref library
Command Description
cd /usr/local Switch to /usr/local.
gunzip -dc /home/slowe/pg/mod_ssl-2.8.5-1.3.22.tar.gz | tar xvf – Expand the Mod_SSL distribution for this specific version of Apache.
cd /usr/local/mod_ssl-2.8.5-1.3.22 Switch to the newly created directory.
./configure –with-apache=/usr/local/apache_1.3.22 This configures the Mod_SSL modules that will be built into Apache in the next step.

Apache
Next, you must install the Apache 1.3.22 Web server, but with a few additional options that are not present in an Apache installation without SSL. Table F explains the steps for installing Apache with the additional options.
Table F


Installing Apache with the necessary options
Command Description
cd /usr/local/apache_1.3.22 The Apache distribution was unpacked into this directory in previous steps, so just switch to it to begin the installation.
SSL_BASE=/usr/local/openssl-0.9.6c \RSA_BASE=/usr/local/rsaref2-19940415.orig/local \./configure \
–prefix=/ usr/local/apache \
–enable-module=ssl \
–enable-shared=ssl \
–activate-module=src/modules/
php4/libphp4.a \
–enable-module=php4 		This command gives the Apache installer all of the information necessary to build Apache with SSL and RSA support, to enable the SSL services as an Apache module, and to enable the PHP services. This command completes very quickly.
make The next step is to actually compile the Apache distribution with the above options. This takes considerably longer than the previous step.
make certificate type=custom Next, create a certificate. For this example, I installed a custom certificate with generic information. For the name of the server, I used localhost.localdomain since the server was in my lab and not connected to the Internet or any DNS servers. When entering the pass phrase at the end of the procedure, remember it, because you’ll need it when Apache initially starts up.
make install This installs the Apache binaries to their appropriate location.
pico /usr/local/apache/conf/httpd.conf You need to tell Apache how to handle files with a .php extension. For PHP 4, that means that you’ll uncomment the lines in the httpd.conf file beginning with the words AddType in the And for PHP 4.x use section of the file.
/usr/local/apache/bin/apachectl start Start the Apache Web server.

Testing 1, 2, 3…
For this exercise, testing is extremely important. For my installation, which resides on my lab Linux server, I browsed to http://192.168.65.129 to see if Apache was properly set up, and I received the results shown in Figure A.

Figure A
If things were properly set up, you’ll see the famous Apache default index screen.

The results in Figure A are not secure, as there was no certificate used and neither https nor port 443 (the secure HTTP port) was used. To start Apache using SSL, stop the Apache server by typing /usr/local/apache/bin/apachectl, stop, and then restart it with /usr/local/apache/bin/apachectl startssl. You must provide the pass phrase configured during the certificate setup.

Browsing to https://192.168.65.129 will prompt you with untrusted certificate information, but since you know the origin of the certificate, click Yes when greeted with a certificate warning.

Once you get past this step, you’ll see the same screen that you saw below, which means SSL is working properly on this server.

Next, you must test the PHP to make sure it’s running and properly integrated with Apache. The easiest way to do this is to create a file named about.php in the /usr/local/apache/htdocs folder with one line in it:
<? phpinfo(); ?>

The phpinfo function tells the PHP scripting engine to display all PHP variables and other system information. When you browse to https://192.168.65.129/about.php, you’ll be greeted with the page shown in Figure B.

Figure B
The PHP Info pages even give you information about how PHP was installed on the server.

Finally, test to make sure PHP can pull data out of the PostgreSQL database you set up. For this step, you’ll manually insert data into a PostgreSQL database table and then write a PHP script to pull it out of the database and display it in a Web browser. Table G explains the necessary steps.
Table G


Creating a table to be used to test PHP’s ability to display data via the Web
Command Description
su – postgres Switch to the Postgres user. Only this user can directly manage the PostgreSQL installation.
cd /usr/local/postgres/bin Switch to the PostgreSQL bin directory.
./psql template1 Psql is the PostgreSQL database management utility. To start the utility, it must be able to make a connection to a database. Template1 was installed during the phase of the PostgreSQL installation where you initialized the databases.
create database example; This command creates a database in PostgreSQL named Example.
CREATE TABLE “person_table” ( “person_id” int2 NOT NULL, “person_name” varchar(40) NOT NULL ); This small script creates a table named “person-table” with the two fields listed.
INSERT INTO person_table values (1, ‘Scott Lowe’);
INSERT INTO person_table values (2, ‘Fred Flintstone’);
INSERT INTO person_table values (3, ‘Barney Rubble’);
INSERT INTO person_table values (4, ‘The Tick’);
INSERT INTO person_table values (5, ‘Buffy TV Slayer’); 		Add some fake data to the table so it can be properly tested.
\q Quit Psql and return to a command prompt.
exit Exit back to the root user login.

Now you have working a working database with real data that can be served up on the Web. Serve up the data by creating a file like the one shown in Table H.
Table H


Your test file will display the database via PHP
Command/Code Description
cd /usr/local/apache/htdocs Switch to the Apache doc root.
pico dblookup.php Create the file dblookup.php.
<html>
<body>

<?
$db = pg_connect(“dbname=example user=postgres”);
$query = “SELECT * from person_table;”;
$result = pg_exec($db, $query);
if (!$result)

{
uprintf (“ERROR”);
uexit;
}

$numrows = pg_numrows($result);

$row=0;

do
{
$myrow = pg_fetch_array($result, $row);
printf(“ID: %s – Name: %s<br>”,$myrow[‘person_id’],$myrow[‘person_name’]);
$row++;
}
while ($row < $numrows);

pg_close($db);

?>

</body>
</html>

Insert these contents into the file you just created. This is a PHO script, which will read through all of the records in the table specified and display them on the screen.

When you browse to http://192.168.65.129/dblookup.php, you’ll receive the contents similar to the ones shown in Figure C.

Figure C
Although this output is simplistic, you can see how powerful this system can be.

Conclusion
After following the steps in this article, where there was nothing, there is now a dynamic, secure Web site with a scripting language in the middle that handles the translation between the Apache Web server and the PostgreSQL database server. The testing examples in the last section show how it all works.

This combination of software can provide a powerful, scalable Web site for almost any organization. And best of all, the software is free. All of the components, especially PHP, Apache, and PostgreSQL, have an excellent user following, and when you run into a problem, a simple search on Google will yield an explanation of the problem and how to correct it.