Configuring PostgreSQL 8.2 in OS X Leopard via Fink (Z1R0)

I spent a bit of time trying to figure this out, and instead of having to relearn this painful lesson in the future, I thought I would preserve my tribulations for prosperity. There were a number of resources that provide partial guidance in discovering the solution (which I've included here), but here is the solution laid out in full:

1. Install Fink

If you haven't already, install Fink. As per the Fink install instructions, you will also need the Apple Developer Tools. The package tools which come with Fink are second to none. To pull down the latest version of a package, simply install it using FinkCommander from source or binaries.

2. Install PostgreSQL

At the time of writing this, the latest version of PostgreSQL is 8.2.6. It is highly recommended that you use the latest available stable release of PostgreSQL as it usually includes a number of security and stability fixes which are a prerequisite for maintaining a stable, secure installation. To install PostgreSQL from the command line, you could also run the following command:

$ /sw/bin/fink install postgres

Simply follow the prompts to complete the installation.

3. Create PostgreSQL account

I found that previous versions of PostgreSQL left some stranded permissions on my system. Unfortunately, none of these mechanisms allowed me to sudo into the postgres account. Given the change in user management tools in Leopard, it is recommended that you use the new Directory Service commands provided in 10.5.x to add the PostgreSQL user to the system. If you have a previous version of PostgreSQL installed that you are not currently using, I recommend removing it and any associated users from the system. Exercise caution doing this if you have an existing installation as it has the potential to loose your data if you are not careful. As the PostgreSQL account used to manage the database should behave like other system accounts, you will need to associate with an unused user and group ID which is less than 500. This will prevent it from being displayed in the Login Screen. I modified Pat Maddox's instructions to determine which whether or not a particular ID is currently in use by issuing the following commands:

$ dscl . -list /Groups PrimaryGroupID | awk '{print $2}' | grep user_id
$ dscl . -list /Users UniqueID | awk '{print $2}' | grep group_id

where user_id and group_id are the IDs you would like to check. If the command returns a value, then the ID is already in use. In most cases, user/group ID 499 is available for use. The rest of this example uses this for the PostgreSQL user/group ID. To create the PostgreSQL group account issue the following command:

$ sudo dseditgroup -o create -i 499 -r "Postgres Admin User" pgsql

To create the PostgreSQL user account issue the following commands:

$ sudo dscl .
> create /Users/pgsql
> create /Users/pgsql UniqueID 102
> create /Users/pgsql UserShell /bin/bash
> create /Users/pgsql RealName "Postgres Administrator"
> create /Users/pgsql NFSHomeDirectory /usr/local/pgsql
> create /Users/pgsql PrimaryGroupID 102
> create /Users/pgsql Password *
> quit

4. Configure directories

This is where Maddox's instructions (based on MacPorts) and mine diverge. Fink typically installs packages under the /sw directory. The main PostgreSQL binaries should be located in the bin subdirectory with the database files installed in the var subdirectory. The database directory usually has a version suffix. Once you've located the database directory, you will need to reassign it's ownership to the newly created pgsql user. I usually provide a symbolic link which points to the most recent version installed on the system to provide a short-cut as well as indicate which version is being used (when more than one version is installed).

$ sudo chown -R pgsql:pgsql /sw/var/postgresql-8.2
$ cd /sw/var
$ sudo ln -s postgresql-8.2 postgresql

You will notice that PostgreSQL was not installed in /usr/local by Fink. This avoids potential conflicts which might arise with other 3rd party packages installed on the system. You will also notice that when we created the PostgreSQL account, the NFSHomeDirectory attribute was set to /usr/local/pgsql. This was intentional. By creating a symbolic link from this directory to the one created by Fink, we can integrate the Fink installation seemlessly in with the rest of the system without introducing conflicts or dependencies. I usually prefer relative paths in this circumstance:

$ cd /usr/local
$ sudo ln -s ../../sw/var/postgresql ./pgsql

By pointing to the original symbolic link, simply changing that link will automatically update the NFSHomeDirectory attribute associated with the PostgreSQL user.

5. Access caveats

Resist the urge to associate a password with the pgsql user. Using sudo to assume privileges will allow you to limit who can access your PostgreSQL installation locally. If you require remote administrative privileges, configure accounts through PostgreSQL directly instead of delegating privileges to system accounts.

6. Configure database

At this point, you should be able to switch to the pgsql user. Check your identity and privileges:

$ sudo su - pgsql
$ whoami
$ id pgsql

To initialize the database issue the following commands:

$ /sw/bin/initdb -D /usr/local/pgsql/data -E utf8

This will create the initial postgres database which will support UTF8 (for international character sets). Once the database is initialized, you can start it by issuing the following command:

$ /sw/bin/pg_ctl -D /usr/local/pgsql/data -l /usr/local/pgsql/logfile start

To stop the server:

$ /sw/bin/pg_ctl -D /usr/local/pgsql/data stop

To enable the procedural language for all databases derived from template1:

$ /sw/bin/createlang-8.2 plpgsql template1

You should now be able to connect to the postgres database:

$ psql -d postgres

7. Security

At this point you might want to spend some time securing your PostgreSQL installation. There are a few pointers in the Installing PostgreSQL on Mac OS X article, but as with all things Postgres, please refer to the Manual for definitive answers regarding security. One of the few things I did to secure my installation was to modify the data/pg_hba.conf file to support a default administrative user that required password authentication. This prevents a local user from automatically gaining access to the database by compromising the PostgreSQL user account. This required first assigning a password to the PostgreSQL user account. This is accomplished by connecting to the newly created postgres database and executing a SQL statement which assigns the user a password:

$ psql -d postgres
#postgres=# ALTER USER pgsql PASSWORD 'secret';
#postgres=# \q

where secret is the password that you've selected. The password itself will be stored as an MD5 checksum so that when you authenticate against the PostgreSQL server, it is never transmitted in plain text. Next, the data/pg_hba.conf file needs to be modified to support authentication for the PostgreSQL user account. Use an editor to add the following lines:

local    all         pgsql                             md5
# IPv4 local connections:
host    all         pgsql     127.0.0.1/32             md5
# IPv6 local connections:
host    all         pgsql        ::1/128               md5

After these changes, a server restart will be required:

$ pgctl -D /sw/var/postgres/data reload

When logging into the default database you should now be prompted for a password. Be careful if lines such as the above already exist in your configuration. You may need to replace them in order for this to work.

8. Tuning

As this database is going to be used for development purposes, a lot of cruft will accumulate in the database as tables are create and destroyed and data is modified. To save the trouble of having to automatically clean up the database, you can modify the data/postgresql.conf file to support auto-vacuuming to recover space in the background. The following lines should be placed in the file:

stats_start_collector = on
stats_row_level = on
autovacuum = on

As the postgresql.conf file is installed with defaults on a fresh PostgreSQL install, you may need to simply comment out these lines.

9. System service configuration

The last step requires configuring the PostgreSQL install to start and stop as other OS X system services do. This will require creating a StartupItem entry for PostgreSQL. These basis of these instructions are courtesy of Robert Crews and wmaus. First create a directory to store the initialization script for PostgreSQL:

$ sudo mkdir /Library/StartupItems/PostgreSQL

Create the PostgreSQL script under this directory with the following code:

#!/bin/sh

# Source common setup, including /etc/hostconfig
. /etc/rc.common

FINK_BIN='/sw/bin';
PGSQL_HOME='/sw/var/postgresql';
PGSQL_USER='pgsql';

StartService ( ) {
    # Don't start unless PostgreSQL is enabled in /etc/hostconfig
    if [ "${POSTGRESQL:-NO-}" = "-YES-" ]; then
        ConsoleMessage "Starting PostgreSQL"
        sudo -u ${PGSQL_USER} ${FINK_BIN}/pg_ctl \
            -D ${PGSQL_HOME}/data \
            -l ${PGSQL_HOME}/logfile start
    fi
}

StopService ( ) {
    ConsoleMessage "Stopping PostgreSQL"
    sudo -u ${PGSQL_USER} ${FINK_BIN}/pg_ctl -D ${PGSQL_HOME}/data stop
}

RestartService ( ) {
    if [ "${POSTGRESQL:-NO-}" = "-YES-" ]; then
        ConsoleMessage "Restarting PostgreSQL"
        StopService
        StartService
    else
        StopService
    fi
}

RunService "$1"

Obviously, replace the constants at the beginning of the script to match your installation if they are different from the ones here. There is one step that is not described in the Crew's instructions which is required to allow the SystemStarter to manage the service. A PostgreSQL.loc file in the same directory as the script which contains the path to the postgres binary and provide the execution context for pg_ctl must be created. In the case of a Fink installed PostgreSQL installation, this file should contain the following line:

/sw/bin

Without this .loc file, the SystemStarter will throw an error when invoked.

The last step requires adding a PostgreSQL entry in the /etc/hostconfig file. It's always a good idea to save a copy of the original config file before modifying it. Add the following entry to the file:

POSTGRESQL=-YES-

This will ensure that PostgreSQL is started when you boot your machine. You can also manage the service using the following commands:

$ sudo SystemStarter stop PostgreSQL
$ sudo SystemStarter start PostgreSQL
$ sudo SystemStarter restart PostgreSQL

Drop me a line if you find this useful or if you find any mistakes in the method outlined above.

All your base are belong to us!