PostgreSQL 8.3 w/ OpenSSL Support (64-bit)
November 13, 2008

Operating System	Platform	Application(s)	Database(s)	Other
		N / A

I. Abstract

This document describes the process of setting up a PostgreSQL database server with Sun SPARC hardware and Solaris 10. For this task, we will compile PostgreSQL from source (so we can have the latest, compiled to our taste and 64-bit support).

Keep in mind that this document does not cover hardening the system. That process is outlined in other documents from myself or others. This machine needs to be well protected. It might be in a very vulnerable position.

Also, you will need root access to do most of these tasks. For the most part, this guide assumes access to the system through a terminal or SSH, with no attached keyboard or monitor.

II. Setup the Operating Environment

Follow my guide titled Configuring a Solaris 10 JumpStart Server to lay the foundation for what we will be doing here. This guide has an example JumpStart profile with the required clusters and packages needed for development.

You may want to nudge up the size on the /var partition, depending on your estimated database needs. This guide uses /var/db as the home for the database(s).

I also suggest you add the following to your system's PATH:

/usr/local/bin:/usr/sfw/bin:/usr/ccs/bin:/usr/local/pgsql/bin

This will allow easier access to some programs we will need later.

III. Download and Install Source

Most of my compilation activities occur within the /usr/src directory. This guide assumes that location.

PostgreSQL:

If the URL used here is too slow, check here for others to try.

IV. Configure PostgreSQL Database Cluster Location and Settings

Now we need to initialize the database cluster. An account called postgres for use with PostgreSQL is automatically created for you. It is locked for normal access, so to perform actions as this user, you must, as the root user, su - postgres to become that user. The following gives an example of how to initialize a database cluster in a directory called /var/db:

Once this is done, we need to configure it for remote access. These examples are simple, but will give you a running start on the methods of customization and securing your installation.

The /var/db/postgresql.conf file contains many options. To allow remote access to your database(s) you need to uncomment and edit a few lines. First, to allow all hosts access to the server, uncomment this line:

#listen_addresses = 'localhost'

and change 'localhost' to '*'. Not the most secure, but good enough for our purposes. Further down in the file, we want to change some settings to enhance security for administration. To allow SSL, uncomment the following line:

#ssl = off

and change off to on. Just below that, we want to uncomment the line:

#password_encryption = on

to allow encrypted passwords. You also need to edit the /var/db/pg_hba.conf file to allow remote connections. Read through the file and add your network and/or hosts you wish to have access (change "trust" to "md5" to use encrypted passwords). That's all we are concerned with for now. The other features are for fine tuning and such.

V. Create Keys/Certificates for SSL Connection Support

Since we've turned on the SSL feature in postgresql.conf, we need to create the private server key and certificate for PostgreSQL to use when starting up. This is rather simple if you don't involve any signing authorities... and we're not here. As the postgres user, perform the following:

VI. Start and Use PostgreSQL

To start the database, enter postgres -D /var/db as the postgres user.

IX. Helpful Links

• PostgreSQL Configure/Compile Flag Descriptions
• Secure TCP/IP Connections with SSL
• PostgreSQL Database Administration