PostgreSQL Setup

At the time I wrote this article, I was running the latest 9.0.1 version of PostgreSQL, but earlier versions of the database should work as well.

Please refer to the documentation linked from the References section for information about how to install PostgreSQL. There's plenty of good information out there, so I'm not going to replicate that here.

Copy the files found here to your PostgreSQL database server somewhere. I wrote the script to hopefully simplify the process of pumping the DDL statements required to construct this application into the database. You'll still need to do a little preliminary setup before this will work. You'll have to compile and install the PostgreSQL pgcrypto contrib module. This module contains the cryptographic functions we'll need to construct random numbers and our password hash values. You'll find the pgcrypto source in the contrib/pgcrypto directory of your PostgreSQL source directory. The pgcrypto module presumes you have the libossp uuid and ssl development libraries installed. These are provided by libossp-uuid-dev and libssl-dev on Debian. Type 'make' and 'make install' to build and install this module into your local installation.

As previously mentioned, the setup script presumes it will be run on the database server itself. The file configures a handful of environment variables used by the setup script. One of these indicates that the global database administrator is named 'postgres'. If that's wrong, edit to match. This user must have ident priviledge to connect to the local database. In other words, your pg_hba.conf file must have a line like:

local all all ident map=mymapname

and, assuming you are running the script as user 'auser' (modify to match your actual username) your pg_ident.conf file must have a matching map line like:

mymapname auser postgres

We also presume the database is listening on the standard port 5432 on the localhost interface. We have to ensure that we can connect to the database using md5 authentication in order to setup and use the application. A simple catch-all rule to allow you to connect to any database as any username on the same host might look like:

hostnossl all all md5

pg_hba.conf will also need words about ::1/128 for localhost connections if IPv6 is enabled, e.g.

hostnossl all all ::1/128 md5

If you have more particular security requirements, you are hopefully in a position to know how to modify these configuration files as appropriate.

With these preliminaries in place, run the script called ''. It will prompt for three passwords. The first password is for the database owner. The database owner will own all of the tables and functions in our example database. The second password is for the user we will use to access the public interface functions. This user does not have permission to access any other part of the database. The last password is for a user internal to our application, not a PostgreSQL user like the first two users. This user is an administrative user (called 'admin') who is given the ability to change other user's passwords. Try logging in as 'admin' to the XForms application to see how that affects the values of the usernames available to the change password function.

If you want to start over, run the '' script to delete the database and the associated roles.